BigQueryで金を溶かさない知識

「【沖縄開催！】中国地方DB勉強会 in 沖縄」でLTしてきたスライド

https://dbstudychugoku.connpass.com/event/127067/

Transcript

1. தࠃ஍ํDBษڧձ in ԭೄ LT ΞΠύʔୂ௕
2. ࣗݾ঺հ
3. ࣗݾ঺հ • ΞΠύʔୂ௕(ࠓ೥ 29) • ͪΎΒσʔλגࣜձࣾ • PyData.OkinawaڞಉΦʔΨφΠβʔ • Python͸޷͖Ͱ͕͢SQL͸ΫιࡶڕͰ͢
4. ࠓ೔࿩͢͜ͱ
5. BigQueryͰML
6. υΩϡϝϯτݟʹߦ͘
7. υΩϡϝϯτݟʹߦ͘
8. ͔̎ͭ͠ͳ͍(´ɾωɾʆ)
9. ͔̎ͭ͠ͳ͍
 ʢे෼ͬͪΌे෼͚ͩͲʣ Ϟνϕʔγϣϯ͕Լ͕ͬͨ
10. վΊͯ ࠓ೔࿩͢͜ͱ
11. BigQueryͰ ۚΛ༹͔͞ͳ͍ςΫχοΫ
12. ʮBigQueryʯͰάάΔ
13. https://qiita.com/itkr/items/745d54c781badc148bb9 BigQuery ා͍
14. https://twitter.com/icoxfog417/status/821576129223872513 ΈΜͳ BigQuery ͕ා͍
15. https://twitter.com/icoxfog417/status/821576129223872513 ΈΜͳ BigQuery ͕ා͍
16. ڪාΛࠀ෰͍ͨ͠
17. ڪාΛࠀ෰͍ͨ͠ https://www.cocolabo.me/kyouhu1mechanism/
18. ڪාΛࠀ෰͍ͨ͠ https://www.cocolabo.me/kyouhu1mechanism/
19. ڪාΛࠀ෰͍ͨ͠ https://www.cocolabo.me/kyouhu1mechanism/
20. ڪාΛࠀ෰͍ͨ͠ • ԿʹڪΕ͍ͯΔͷ͔
 ˠ Ϋϥ΢υഁ࢈ • Ͳ͏΍ͬͨΒࠀ෰Ͱ͖Δͷ͔
 ˠ ֹۚΛݟੵ΋Γ͍ͨ
 ˠ ഁ࢈͠ͳ͍Α͏ɺద੾ͳ࢖͍ํ͕Ͱ͖Ε͹
21. BigQueryͷྉۚΛ஌Δ
22. BigQueryͷྉۚΛ஌Δ • ओʹ̎ͭʢ౦ژ asia-northeast1Ͱ঺հʣ • ετϨʔδྉۚ • σʔλૢ࡞ྉۚ
23. BigQueryͷྉۚΛ஌Δ • ετϨʔδྉۚ ྉۚ ৄࡉ ΞΫςΟϒ (# ຖ݄(#·Ͱແྉ ௕ظอଘ (# ຖ݄(#·Ͱແྉ ※ΞΫςΟϒ ŋŋŋ աڈ90೔ؒͰมߋ͞Εͨσʔλʹର͢Δֹ݄ྉۚ ※௕ظ ŋŋŋ աڈ90೔ؒͰมߋ͞Ε͍ͯͳ͍σʔλʹλ΢͢Δֹ݄ྉۚ
24. BigQueryͷྉۚΛ஌Δ • ετϨʔδྉۚ • 1ϲ݄100GB࢖ͬͯͯ΋ɺ$2.23 ! • ͋Βʁ݁ߏ͓ಘͰ͸
25. BigQueryͷྉۚΛ஌Δ • σʔλૢ࡞ྉۚ: σʔλͷಡΈࠐΈ • ओʹํ๏͕2ͭ • ᶃGCSʹΞοϓϩʔυͯ͠ → BigQuery΁อଘ • BigQueryͱͯ͠ͷྉۚ͸ແྉͳΜ͚ͩͲɺGCSͷετϨʔδྉۚͱσʔλ సૹྉ͕͔͔ۚΔ • ᶄετϦʔϛϯάΠϯαʔτ ໿$0.06/GB • ϦΞϧλΠϜੑΛٻΊΔͳΒ͜Ε͚ͩͲɺྲྀΕΔσʔλྔʹ஫ҙ • ଞͷํ๏ͱͯ͠ɺBigQueryͷ΢ΣϒUIΛ༻͍ͯJSON΍CSVܗࣜͰΞοϓϩʔυ͢ Δ͜ͱ͕Ͱ͖·͢ → ࢼ͙͢Β͍ͳΒͪ͜ΒΛ࠾༻ͨ͠΄͏͕ྑ͍ʢແྉʣ
26. BigQueryͷྉۚΛ஌Δ • ΫΤϦྉۚ • $8.55/TBʢຖ݄1TB·Ͱແྉʣ • ΫΤϦ࣮ߦͷྉۚ͸σʔλܕͷαΠζʹԠͯ͡ɺ MB୯Ґͷσʔλॲཧ༰ྔͰܾ·Δ • Τϥʔͱ͔Ωϟογϡͷ݁Ռʹ͸ྉۚ͸ൃੜ͠ͳ͍ • Ͱ͔͍σʔλʹΫΤϦ౤͛Δ࣌͸஫ҙ
27. BigQueryͷྉۚΛ஌Δ • ΫΤϦྉۚ • ݄ఆֹͷϓϥϯ΋͋Δ͕ɺ$48,000/݄ • ೋ౓ݟͨ͠ • Կ౓΋࢖͏ਓʹ͸͍҆ΜͩΖ͏ʢଟ෼ʣ
28. গ͠ڪා͕࿨Β͍Ͱ͖ͨ
29. Ͱ΋·ͩා͍
30. BigQueryͷඅ༻ͷ ཈͑ํΛ஌Δ
31. • ʮSELECT *ʯېࢭ • ʮLIMITʯʹ஫ҙ • ΫΤϦݕূ݁Ռͷ֬ೝ BigQueryͷඅ༻ͷ཈͑ํΛ஌Δ
32. • ʮSELECT *ʯېࢭ • BigQuery͸ΧϥϜܕσʔλߏ଄ͷͨΊɺʮSELECT *ʯΛར༻͢Δͱ
 ͢΂ͯͷྻΛεΩϟϯ͢ΔͨΊɺͦͷ෼՝ۚ͞ΕΔ • ඞཁͳྻ͚ͩࢦఆ͍ͯͩ͘͠͞ • Լ͸ʮbigquery-public-data.hacker_news.fullʯͷςʔϒϧͰ֬ೝͨ݁͠Ռ BigQueryͷඅ༻ͷ཈͑ํΛ஌Δ ɾSELECT * FROM xxx ɾSELECT title FROM xxx
33. • ʮLIMITʯʹ஫ҙ • ʮSELECT * FROM xxxʯͰ΋ɺʮLIMIT Nʯ͢Ε͹͍͍ͷͰ͸ɾɾɾɾʁ • !Ͱ͢ • ಡΈऔΔόΠτ਺͸มΘΒͣɺ݁Ռͷग़ྗߦ਺੍͕ݶ͞ΕΔ͚ͩ BigQueryͷඅ༻ͷ཈͑ํΛ஌Δ ɾSELECT * FROM xxx ɾSELECT * FROM xxx LIMIT 100
34. • ΫΤϦݕূ݁Ռͷ֬ೝ • ࣮ߦ͢ΔSQL΋
 νΣοΫͯ͘͠ΕΔ
 ศརͳԿ͔ • ॲཧ͢ΔόΠτ਺Λ
 ܭࢉͯ͘͠ΕΔͷͰ
 ࣮ߦ͢ΔΫΤϦͷྉۚ Λࣄલʹ֬ೝ͢Δ
 ͜ͱ͕Ͱ͖Δʢखಈʣ BigQueryͷඅ༻ͷ཈͑ํΛ஌Δ
35. BigQueryͷඅ༻ͷ཈͑ํΛ஌Δ https://cloud.google.com/products/calculator/ ྉۚܭࢉπʔϧ͸͋Δ
36. ʢ౎౓ܭࢉ͸ʣͩΔ͍
37. ศརͳPlugin
38. • BigQuery Mate • BigQueryͷWeb UI͔Βར༻͢Δͱ͖ʹͳʹ ͔ͱศར • ChromeͷΈ ศརͳPlugin https://chrome.google.com/webstore/detail/bigquery-mate/nepgdloeceldecnoaaegljlichnfognh/related
39. • ը໘͸͜Μͳײ͡ ศརͳPlugin
40. • SQLม͑ͯΈΔͱྉ͕ۚ࠶ܭࢉ͞ΕΔ ศརͳPlugin
41. ศར!!!
42. • Ҏલɺ৽͍͠UIʹͳͬͨΜ͚ͩͲɺͦ͜Ͱ͸ ࢖͑ͳͦ͞͏(´ɾωɾʆ) • ΞοϓσʔτΛ଴ͭ or GASͰܭࢉεΫϦϓτ Λ༻ҙ͢Δ͔ ͕͔ͩ͠͠…
43. ࡶײ
44. • BigQuery͜Θͳ͘ͳ͍Α • ࣄલʹγϡϛϨʔγϣϯͯ͠ྉۚܭࢉ͠Α͏ • ࣮ߦ͢ΔSQL͕εΩϟϯ͢Δσʔλྔ͸
 ࣄલʹ֬ೝͨ͠΄͏͕͍͍Α ࡶײ