GCPではじめるスモールスタートなデータ活用

Transcript

1. 1 GCPͰ͸͡ΊΔ εϞʔϧελʔτͳσʔλ׆༻ #bq_sushi ver. bq_sushi #4 2016-09-06 Takashi Nishibayashi

2. 2 Takashi Nishibayashi Software Engineer Zucks AdNetwork, Zucks Inc. Data

   analysis team ݱࡏ͸഑৴ޮ཰ͷ࠷దԽ ೖࡳՁ֨ࣗಈௐ੔ϩδοΫɺ഑৴αʔ όʔͷ޿ࠂબ୒ϩδοΫͷ։ൃʹैࣄ @hagino3000

3. 3 3 ͜Ε͸Կ͔ ಉ೔ͷGCP NEXT TOKYOͷࣄྫ঺հηογϣ ϯͰൃදͨ͠಺༰ͷॖখ൛Ͱ͢

4. 4 4 Zucks AdNetwork ͷσʔλ׆༻ͷมભ

5. 5 5 5 ϓϩδΣΫτ։࢝࣌ͷཧ૝ͱݱ࣮

6. 6 6 6 ໨ࢦ͢ॴ(Ծ) ޿ࠂ഑৴αʔόʔͰΠϯϓϨογϣϯຖʹػցֶशϞσϧʹΑΔίϯ όʔδϣϯ༧ଌɺΫϦοΫ཰༧ଌΛߦͳ͍഑৴ޮ཰ΛΞοϓ ݱ࣮ େྔͷϩάϑΝΠϧ͕༷ʑͳϑΥʔϚοτͰAWS S3ʹஔ͔Ε͍ͯΔ Ϛελσʔλ͸MySQLʹ֨ೲ͞Ε͍ͯΔ

   Elastic SearchʹೖͬͯΔͷ͸௚ۙ2िؒ

7. 7 7 7

8. 8 8 8 ͍͖ͳΓ౸ୡ͸Ͱ͖ͳ͍

9. 9 1ظ: ·ͣ͸σʔλαΠΤϯςΟετ͕ར༻Ͱ͖ΔΑ͏ʹ ü  ωοτ޿ࠂۀքͰػցֶश͕ྲྀߦ͍ͬͯΔͱ͸͍͑ɺࣗαʔϏεͷ σʔλͰ΋ͦΕ͕Մೳͳͷ͔ݕূ͍ͨ͠ ü  ࣮ݧ΍ԾઆݕূͷͨΊʹਓ͕ؒσʔλΛखܰʹར༻͍ͨ͠ ü  ݶΒΕͨਓ͕ؒΫΤϦ΍ूܭΛ࣮ߦͰ͖Ε͹ྑ͍

   ü  ਺ඦϛϦඵͷԠ౴ੑೳ͸ٻΊͳ͍ ü  σʔλετΞͷ؅ཧʹख͕͔͔ؒΒͳ͍ࣄ͕ॏཁ ü  σʔλྔ͸ 600GByte/day ఔ౓͕ͩɺ·ͩ·ͩ૿͑ͦ͏

10. 10 1ظ: ·ͣ͸σʔλαΠΤϯςΟετ͕ར༻Ͱ͖ΔΑ͏ʹ ²  ޿ࠂͷ഑৴ϩάΛBigQueryʹྲྀ͠ࠐΜͩ ²  MySQLͷϚελσʔλ΋BigQueryʹಉظ ²  WebUI΍PandasɺBigQuery Pythonܦ༝Ͱར༻

    ²  BigQueryͰαϒαϯϓϦϯάͯ͠ϩʔΧϧϚγϯͰֶश ²  AWS EMRୀ໾ ²  Elastic Searchୀ໾ ²  Cloud Datalab betaʹඈͼ͍ͭͯരࢮ (2016೥1݄)

11. 11 2ظ: όονॲཧ͔Βར༻Ͱ͖ΔΑ͏ʹ ü  ܧଓతʹճ͍࣮ͨ͠ݧ΍ɺ༧ଌॲཧͷόονΛcronͰ૸Β͍ͤͨ ü  ෼ੳλεΫʹݶΒͣɺ഑৴γεςϜଆͷόονॲཧ΋࢖͍͍ͨ ü  ػೳຖͷ࢖༻ঢ়گ(ΫΤϦίετ౳)͸೺Ѳ͍ͨ͠

12. 12 2ظ: όονॲཧ͔Βར༻Ͱ͖ΔΑ͏ʹ ²  CloudLoggingͷઃఆͰBigQueryͷ؂ࠪϩάΛBigQueryʹΤΫεϙʔτ ²  ػೳຖʹαʔϏεΞΧ΢ϯτΛ෷͍ग़ͯ͠ɺ࢖༻ঢ়گΛ೺Ѳ ²  ίετ͕௓ͶͨΒ௨஌ ² 

    ೖࡳ୯Ձࣗಈௐ੔όονɺෆਖ਼ΫϦοΫ൑ఆόον͕Քಈ ²  ϧʔϧϕʔεɺҟৗݕ஌ϕʔεͷࣝผλεΫ͸SQLͰॻ͚Δ ²  ࣮ݧ݁Ռ͸Cloud Storage/BigQueryʹอଘ

13. 13

14. 14

15. 15 Audit Logͷ༻్ ²  ػೳຖͷΫΤϦίετ ²  ೔ຖͷΫΤϦίετ ²  ςετ༻ͷςʔϒϧ࡞੒ऀௐࠪ ² 

    ࢖ΘΕ͍ͯͳ͍ςʔϒϧௐࠪ

16. 16 3ظ: ͢΂ͯͷ৬छͷϝϯόʔ͕σʔλΛར༻Ͱ͖ΔΑ͏ʹ ü  ఆܕͷௐࠪλεΫ͸ΤϯδχΞ๊͕͑ͨ͘ͳ͍ ü  ίετ͕രൃ͠ͳ͍Α͏ʹར༻ऀΛ૿΍͍ͨ͠ ü  SQLॻ͚Δਓ͕૿͑Δͱྑ͍ײ͡ʹͳΔͷͰ͸

17. 17 3ظ: ͢΂ͯͷ৬छͷϝϯόʔ͕σʔλΛར༻Ͱ͖ΔΑ͏ʹ ²  re:dashͰΫΤϦͰ͖ΔΑ͏ʹͨ͠ ²  ΤϯδχΞ͕ཁ๬ΛݩʹςϯϓϨʔτͷΫΤϦΛ࡞੒ ²  Ϩϙʔτը໘ͷϓϩτλΠϓʹ΋ ² 

    ΫΤϦ୯ҐͷίετϦϛοτઃఆ(re:dashͷػೳ)ͰߴֹΫΤϦ࣮ߦ Λ཈ࢭ

18. 18 ཁٻ͞ΕΔσʔλ඼࣭Ϩϕϧ΋มΘΔ ü  Ϣʔεέʔε͕૿͑Δͱσʔλ඼࣭͕՝୊ʹ ü  23࣌୆ͷϩάऔΓࠐΈ͕ऴͬͨ௚ޙʹॲཧΛ૸Β͍ͤͨΜ͚ͩͲ? ²  Stream Insert, Batch

    Insert, ΫΤϦશͯϦτϥΠػߏ͸ඞਢ ²  ݄ʹ1౓͸BigQueryͷௐࢠͷѱ͍೔͕͋Δ ²  σʔλͷऔΓࠐΈ࿙ΕɺॏෳऔΓࠐΈνΣοΫͷόονΛՔಇ ²  σʔλͷऔΓࠐΈঢ়گ͕֎෦͔Β֬ೝͰ͖Δ࢓૊Έ

19. 19 ෭࣍త੒Ռ෺ •  ΤϯδχΞ͕͍ͭͰ΋഑৴ϩάͷௐ͕ࠪՄೳʹ •  MySQLͰѻ͑ͳ͔ͬͨαΠζͷσʔλΛݩʹͨ͠ҙࢥܾఆ͕Մೳʹ •  ༷ʑͳόονॲཧ͕σʔλΛར༻Մೳʹ •  SQLΛॻ͚ͩ͘ͰϨϙʔτ͕ࣗ༝ʹ࡞੒Մೳʹ

    •  ϓϩδΣΫτͷϝϯόʔશһ͕σʔλʹΞΫηεՄೳʹ

20. 20 ͦͷଞ •  ΦϯϥΠϯͰ౎౓σʔλΛࢀর͢ΔΑ͏ͳॲཧʹBigQuery͸޲͔ͳ͍ •  Key-ValueͰҾ͚ΔΑ͏ʹͯ͠BigtableΛ࢖ͬͨํ͕͍͍ •  BigQueryͷલʹΩϟογϡϨΠϠΛ༻ҙ͢Δࣄྫ΋ •  Cloud

    Dataproc or Cloud Dataflow…… •  Spotify͸Spark͸ෳࡶ͗ͯ͢࢖͑ͳ͍ͱͷࣄͰDataflowΛscala͔Βར༻ •  https://github.com/spotify/scio •  Cloud Datalab͕৽͘͠ͳͬͨͦ͏ͳͷͰظ଴ •  Jupyter NotebookͷΫϥ΢υ൛

21. 21 ·ͱΊ •  ͍͖ͳΓ೉͍͠ॴΛૂ͏ͱ੒Ռ͕ग़Δ·Ͱ͕͔͔࣌ؒΔͨΊɺ஍ͳΒ͠Λ͠ ͳ͕Βσʔλ׆༻ΛਐΊ͍ͯΔ •  SQLͰهड़Ͱ͖Δϧʔϧϕʔε΍ҟৗݕ஌ϕʔεͷॲཧ͸ػցֶशͱൺֱ͢ Δͱૣ͘੒Ռ͕ग़ͤΔ •  Cloud

    Storage, Cloud Logging, Cloud Dataprocͱͷ࿈ܞ͕ڧԽ͞Εɺ BigQueryͷϢʔεέʔε͕૿͑ͨ •  ਺ඦmsecͷԠ౴ੑೳɺಉ࣌ΫΤϦ࣮ߦ਺ɺ҆ఆੑΛٻΊͳ͚Ε͹BigQuery͸ Ϧʔζφϒϧʹ࢖͑Δ

22. 22 ิ଍ BigQueryͰ౷ܭྔΛग़࣌͢ʹ࢖͏ΫΤϦϝϞ http://qiita.com/hagino3000/items/e9ed62638ebe54391188

23. 23 23 Thank You