Design Patterns for Collecting and Analyzing Schemaless Log

スキーマレスなログデータの収集と集計のためのデザインパターン

at http://www.zusaar.com/event/876003

Transcript

1. Crocos, Inc. Sotaro Karasawa @sotarok http://facebook.com/sotarok εΩʔϚϨεͳ ϩάσʔλͷ ऩूͱूܭͷͨΊͷ σβΠϯύλʔϯ #ds2013 ·ͨ͸ Treasure Data ϋΠύʔ׆༻ज़
2. ࣗݾ঺հ 4PUBSP
   ,BSBTBXB
   
   !TPUBSPL ฑ୔
   ૱ଠ࿠ EIBUFOBOFKQTPUBSPL גࣜձࣾΫϩίε
   
   $SPDPT
   *OD 1)1 3FE
   #VMM
3. ࣾһਓͰ૑ۀ ։ൃऀ౰࣌ਓ ೥݄ʹαʔϏεϩʔϯν ೥݄ʹ5%ಋೖ
4. ࠓ೔࿩͍ͨ͜͠ͱ ΞϓϦέʔγϣϯϩάΛͲ͏ू ΊΔ͔
   εΩʔϚϨεͳϩάͱ͸ جຊతͳϩάઃܭ ϩάऩूͷσβΠϯύλʔϯ
5. ࠓ೔ͷ໨త ৘ใڞ༗ɺ৘ใަ׵ ͏ͪͰ͸͜͏΍ͬͯΔΑɺͱ͍͏Ұྫ ܾͯ͠ߨࢣͱͯ͠ɺ͜͏΍Γ·͠ΐ͏ͱݴ ͍ʹདྷͨΘ͚Ͱ͸ͳ͘ɻ ࠓޙɺ͜͏͍͏ωλ͕σΟεΧογϣϯͰ ͖Ε͹͍͍ͳͱ
6. ओʹ 8FCΞϓϦέʔγϣϯ ͷ࿩ Ͱ͕͢ɺ8FCΞϓϦέʔγϣϯ΋ଟ༷Խ͠ ͍ͯ·͢ ޙ൒ͷσβΠϯύλʔϯͷͳ͔Ͱ͍͔ͭ͘ ৮ΕΒΕΔ͔ͳʁ
7. 2
   ϩάऩूΛ͍ͯ͠Δ 2
   qVFOUE
   Λ࢖͍ͬͯΔ 2
   5%
   Λ࢖͍ͬͯΔ
8. ͲΜͳϩάΛूΊͯΔʁ
9. 8FCαʔόͷϩά
10. ϩάͱ͍͑͹ 8FCαʔόʔͷϩά 5SFBTVSF
    %BUB
    ͷνϡʔτϦ Ξϧ΋
    "QBDIF
    ͷϩά http://docs.treasure-data.com/articles/quickstart
11. ͚ͩͲຊ౰ʹཉ͍͠ͷ͸
12. ͲΜͳϢʔβʔ͕ʁ ͲΜͳ୺຤ͰʁͲ͔͜Βʁ ͍ͭԿΛͨ͠ͷ͔ʁ ͲΜͳϘλϯΛΫϦοΫͨ͠ ͷ͔ʁλοϓͨ͠ͷ͔ʁ
13. ΞϓϦέʔγϣϯϩά
14. ͲΜͳϢʔβʔ͕ʁ ɹˠ
    Ϣʔβʔొ࿥৘ใ ͲΜͳ୺຤ͰʁͲ͔͜Βʁ ɹˠ
    6"
    
    (&0 ͍ͭԿΛͨ͠ͷ͔ʁ ɹˠ
    63*
    
    ΞΫγϣϯ
    
    
15. ΞϓϦέʔγϣϯϩάΛ Ͳ͏ूΊΔ͔
16. εΩʔϚϨεϩάͱ͸ʁ
17. εΩʔϚϨεϩάͱ͸ʁ 
    εΩʔϚͷແ͍ϩά
18. ϩάͷεΩʔϚ ͜Ε·Ͱ ˠ
    ྫ͑͹
    547
19. ΧϥϜ໨
    UJNF ΧϥϜ໨
    TUBUVT ΧϥϜ໨
    VSJ ΧϥϜ໨
    VTFS@JE  
    
    JOEFY
    
20. ΧϥϜ໨
    UJNF ΧϥϜ໨
    TUBUVT ΧϥϜ໨
    VSJ ΧϥϜ໨
    VTFS@JE  
    
    JOEFY
     εΩʔϚ
21. for line in open('app.log', 'r'): columns = line.split("\t") time = columns[0] ...
22. ߲໨ͷΘ͔ΓͮΒ͞ εΩʔϚมߋͷ೉͠͞ ෼ੳऀͱऩूऀͷೝࣝࠩҟʹ ΑΔࣄނ
23. 5%ͷϩά
    ͱ͍͏͔
    qVFOUE 
    +40/ { "time":1373876885, "status":200, "request_uri":"/52495/facebook", "session_id":"kn6avn2fuh21r25a65mgm3rjh3", "fb_id":"7c40c5dd2e55cde37a8c40ed80e1", ... }
24. Θ͔Γ΍͍͢ ߲໨Λ௥ՃͰ͖Δ σʔλྔ͸૿͑Δɾ৑௕
25. ΞϓϦέʔγϣϯϩάΛ Ͳ͏ूΊΔ͔
26. جຊతͳϩάઃܭ
27.  ΠϕϯτϨίʔυͱͳΔΑ ͏ʹه࿥͢Δ ˞8FCΞϓϦέʔγϣϯͷ৔߹ɺΞΫηε
28. Πϕϯτͱ͸ 8FCΞϓϦέʔγϣϯͳΒ ɾΞΫηε ωΠςΟϒΞϓϦͳΒ ɾΠϕϯτ
29. 
    جຊతͳεΩʔϚΛܾΊΔ
30. 
31. εΩʔϚϨεͱ͍ͬͯ΋ Ͳ͏͍͏ϩάΛѻ͍ͬͯΔͷ͔ ֤ϨίʔυͰҙຯ͕ҧͬͯ͸ҙ ຯ͕ແ͍
32. جຊతͳεΩʔϚΛܾΊΔ UJNF TUBUVT VSJ VB SFGFSSFS 
33. جຊతͳεΩʔϚΛܾΊΔ UJNF TUBUVT VSJ VB SFGFSSFS  LTSVͬΆ໊͍લʹ ߹Θ͓ͤͯ͘ͱΘ ͔Γ΍͍͔͢΋
34. 8FCαʔόʹ͋Δϩά ͚ͩͰͳ͘ BQQ SPVUF DPOUSPMMFS QSPDFTT@UJNF EFWJDF 
35. 8FCαʔόʹ͋Δϩά ͚ͩͰͳ͘ BQQ SPVUF DPOUSPMMFS QSPDFTT@UJNF EFWJDF  ϑϨʔϜϫʔΫ಺Ͱͷϧʔ ςΟϯά໊ͱ͔ɺίϯτ ϩʔϥ໊ͱ͔ (uri ʹϊΠζ͕͋ͬͯ΋ routing ໊ͰूܭͰ͖Δ)
36.  ΞϓϦέʔγϣϯͷ஌Γ͏Δ ଐੑΛඇਖ਼نԽͯ͠Ϩίʔυ ʹؚΊΔ
37. ඇਖ਼نԽ͞ΕͨϨίʔυ TFTTJPO@JE VTFS@JE HFOEFS BHF EFWJDF 
38. ͳͥඇਖ਼نԽ͔ͷϝϦοτ +0*/
    ͤͣʹूܭؔ਺ʹ͔ΔͨΊ
39. ͪͳΈʹ VTFS@JE TFTTJPO@JE ͳͲ͸
    IBTI
    Խ͓ͯ͘͠
40. ·ͱΊΔͱ 
    ΠϕϯτϨίʔυͱͳΔΑ͏ ʹه࿥͢Δ
    ूܭؔ਺ʹ͔ΔͨΊ 
    جຊతͳεΩʔϚΛܾΊΔ 
    ΞϓϦέʔγϣϯͷ஌Γ͏Δଐ ੑΛඇਖ਼نԽͯ͠ϨίʔυʹؚΊΔ
41. ͜͜·ͰདྷΔͱɺ΋͏෼ੳ͕Մೳ
42. ෼ੳͷྫ SELECT AVG(v[‘process_time’]) FROM access WHERE v[‘route’] = ‘crocos_index’
43. ෼ੳͷྫ SELECT v[‘gender’], COUNT(*) FROM access GROUP BY v[‘gender’] ඇਖ਼نԽ͓͍ͯ͠ ͯΑ͔ͬͨʂ
44. ෼ੳͷྫ SELECT v[‘gender’], COUNT(*) FROM access GROUP BY v[‘gender’]
45. ෼ੳͷྫ
    Τϥʔͷௐࠪʹ΋ SELECT v[‘route’], v[‘status’], v[‘ua’] FROM access WHERE v[‘user_id’] = ‘xxx’
46. ˞
    ௕͘ͳΔͷͰ೔෇ؔ࿈ͷॲཧ͸লུͯ͠·͢ ɹ
    ຊ౰͸೔ผʹ
    (3061
    #:
    ͨ͠Γ
    8&)&3
    ۟ͰߜͬͨΓ
47. εΩʔϚϨεͳ ΞϓϦέʔγϣϯϩά ͷͨΊͷ σβΠϯύλʔϯ Λߟ͑Δ
48. ͯ͞ جຊతͳεΩʔϚΛ࣋ͭ ϩά͕ͨ·Γ࢝Ί·ͨ͠
49. ͔͜͜Βઌ͸ ԿΛ෼ੳΛ͍ͨ͠৔߹ʹ ͲΜͳϩάΛೖΕ͓͚ͯ͹ྑ ͍͔ ύλʔϯʹ෼͚ͯߟ͑·͢
50. εΩʔϚϨεͷग़൪
51. جຊతͳεΩʔϚ UJNF TUBUVT VSJ VB SFGFSSFS  ͳΜͪΌΒ ͔ΜͪΌΒ
52. جຊతͳεΩʔϚ UJNF TUBUVT VSJ VB SFGFSSFS  ͳΜͪΌΒ ͔ΜͪΌΒ ಛఆͷϨίʔυʹɺಛ ผͳҙຯΛ΋ͨͤΔ͜ͱ ͕Ͱ͖Δʂ ͔͠΋ଞͷϨίʔυʹӨ ڹΛ͋ͨ͑Δ͜ͱͳ͘ɻ
53. ύλʔϯ τϥϯβΫγϣϯ
54. ಛผͳҙຯΛ࣋ͭ ΞΫγϣϯͷ੒ޭͳͲΛ ه࿥͍ͨ͠
55. τϥϯβΫγϣϯ uri ΍ route: ϦΫΤετ͕དྷͨ͜ͱ͸Θ͔Δ ͔͠͠ɺຊ౰ʹ੒ޭ͔ͨ͠͸ɺ ΞϓϦέʔγϣϯͰ͔͠Θ͔Β ͳ͍
56. τϥϯβΫγϣϯ key_action key_attr_*
57. τϥϯβΫγϣϯ key_action present:entry:completed ΞϓϦ:ಈ࡞:ঢ়گ ※͜ͷྫ͸ʮొ࿥׬ྃʯ
58. τϥϯβΫγϣϯ key_attr_* τϥϯβΫγϣϯʹؔΘΔ෇Ճ తͳ৘ใΛͭͬ͜Ή εΩʔϚ͸ɺkey_action ͝ͱʹ ҟͳΔ
59. τϥϯβΫγϣϯ
    ྫ key_action = shop:register:completed key_attr_user_id = xxxxx key_attr_ref = fb_share
60. τϥϯβΫγϣϯ෼ੳͷྫ SELECT v[‘key_attr_ref’], COUNT(*) FROM access WHERE v[‘key_action’] = ‘...’ GROUP BY v[‘key_attr_ref’]
61. τϥϯβΫγϣϯ෼ੳ ࠷ۙΑ͘ݟͯΔσʔλ ... Ͳͷࢪࡦ͕Ұ൪ޮ͍ͨͷ͔
62. ύλʔϯ Πϕϯτ
63. ΞΫηεʹґଘ͠ͳ͍ ΠϕϯτͷൃੜΛ஌Γ͍ͨ
64. ɾ+BWB4DSJQU
    ʹΑΔΠϕϯτ ɾϞʔμϧͷදࣔ ɾ5XJUUFS΍'BDFCPPL΁ͷ γΣΞ ɾωΠςΟϒΞϓϦ
65. Πϕϯτ tag = app:action:location & some attributes
66. Πϕϯτ
    ྫ tag = shop:tweet:shop_item item_id = 1234 tweet_id = xxxxx
67. Πϕϯτ෼ੳͷྫ SELECT v[‘item_id’], COUNT(*) FROM events WHERE v[‘tag’] = ‘shop:tweet:shop_item’ GROUP BY v[‘item_id’]
68. τϥϯβΫγϣϯͱ ࣮͸࢓૊Έ͸͔ΘΒͳ͍
69. εΩʔϚϨεϩάͷѻ͍ํͰ ࠷΋ॏཁͳͷ͸ ղऍͷϧʔϧΛܾΊΔ͜ͱ
70. ଟ෼͕࣌ؒແ͍ͷͰ ͜ͷ΁ΜͰ
71. ͜͏͍͏࣌ʹ͸ ͜͏͍͏෩ʹσʔλͷूΊͯ ͜͏ղੳ͠Α͏ ͱ͍͏ͷΛڞ༗͍ͨ͠
72. ஫ҙ͍ͨ͠ͱ͜Ζ
73. εΩʔϚϨεͱ͍͑Ͳ ࣄલͷϩάઃܭΛ͔ͬ͠Γ΍Δ ϩά͸Ұ౓ೖΕΔͱมߋ͕೉͍͠ ˠ
    ෼ੳ͍߲ͨ͠໨ͷ࿙Ε͕ແ͍͔ ϓϥΠόγʔ໰୊ʹ΋ؾΛ͚ͭΔ