Upgrade to Pro — share decks privately, control downloads, hide ads and more …

BigQueryで金を溶かさない知識

F0e950c6bf20815f3d169f063d3c5d4f?s=47 Aipa
April 22, 2019

 BigQueryで金を溶かさない知識

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

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

F0e950c6bf20815f3d169f063d3c5d4f?s=128

Aipa

April 22, 2019
Tweet

More Decks by Aipa

Other Decks in Technology

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͕εΩϟϯ͢Δσʔλྔ͸
 ࣄલʹ֬ೝͨ͠΄͏͕͍͍Α ࡶײ

  45. None