Google BigQuery の話 #gcpja

B4afc7d853d7f1cb6a253d3c7183c05a?s=47 Naoya Ito
September 17, 2014

Google BigQuery の話 #gcpja

gcp ja night で話した BigQuery のスライド。YAPC::Asia のものに数枚だけスライドを追加したもので、ほぼ同じです。

B4afc7d853d7f1cb6a253d3c7183c05a?s=128

Naoya Ito

September 17, 2014
Tweet

Transcript

  1. (PPHMF#JH2VFSZͷ࿩ /BPZB*UP ,"*;&/QMBUGPSN*OD  HDQKBOJHIU

  2. ΞδΣϯμ •  #JH2VFSZ֓؍ •  #JH2VFSZͷ಺෦ •  ,"*;&/QMBUGPSN*ODͰͷ࢖͍Ͳ͜Ζ

  3. #JH2VFSZ֓؍

  4. (PPHMF#JH2VFSZ

  5. None
  6. #JH2VFSZͱ͸ •  ڊେͳσʔλ΁ͷ42- ͳͲ Λ਺ඵͰ࣮ߦ͢ΔΫϥ΢υαʔϏε –  ԯϨίʔυΛඵ ˞  – 

    8FCΠϯλʔϑΣʔε͓Αͼ3&45"1* •  (PPHMFࣾ಺Ͱ࢖ΘΕ͖ͯͨ%SFNFMΛαʔϏεԽ –  ೥݄$MPTFEϦϦʔε –  ೥݄Ұൠެ։ –  ܧଓతʹόʔδϣϯΞοϓ –  ೥݄#JH2VFSZ4USFBNJOH ˞(PPHMFͷދͷࢠʮ#JH2VFSZʯΛ'MVFOUEϢʔβʔ͕࢖Θͳ͍ཧ༝͕ͳ͘ͳͬͨཧ༝ IUUQRJJUBDPNLB[VOPSJJUFNTBDBDCCBBBG
  7. ͲΜͳ͜ͱʹ࢖ΘΕΔ͔ •  Ϣʔεέʔε – ϩάղੳ – %BUBXBSF)PVTF –  •  ޲͍ͯͳ͍༻్ – ۀ຿%# ଎͍3%#.4Ͱ͸

    ͳ͍Αɺͱ͍͏͜ͱ
  8. #JH2VFSZ͸ͳͥ଎͍͔ •  جຊɺϑϧεΩϟϯͰ͕Μ͹Δ –  3%#.4ͷ#5SFFΠϯσοΫεͱ͔ͳ͍ •  42-Λ෼ࢄॲཧ –  .11 .BTTJWFMZ1BSBMMFM1SPDFTTJOH

    2VFSZ&OHJOF %SFNFM •  ਺ઍ୆ͷσΟεΫͱߴ଎ωοτϫʔΫͰεέʔϧΞ΢τ –  5#ͷσʔλΛඵͰϦʔυ͢Δ*0
  9. ͨͩ͠ •  ଎͍3%#.4Ͱ͸ͳ͍ •  େਓ਺ͰҰ౓ʹ࢖͏΋ͷͰ͸ͳ͍ – ओʹόονॲཧʹ࢖͏ •  εΩʔϚϨεͰ͸ͳ͍ 5#ن໛σʔλͰ΋ઢܗҎ ԼͰεέʔϧ͢Δ͕ɺٯ

    ʹখ͞ͳσʔλͰ΋਺ඵ ͷΦʔόʔϔου͕͋Δ ͷͰ
  10. BigQuery読書会、@harukasan  資料より引用

  11. ଞͷྨࣅ࣮૷ͱͷϙδγϣχϯά •  -BSHF#BUDI –  ҆ఆͯ͠ڊେͳόονΛ࣮ߦͰ͖Δ –  ΫΤϦ࣮ߦ࣌ͷΦʔόʔϔου͕େ͖͍ ਺ेඵʙ਺े෼  – 

    .BQ3FEVDFɺ)BEPPQ )JWF  •  4IPSU#BUDI –  ΫΤϦ࣮ߦ࣌ͷΦʔόʔϔου͕NTʙ਺ඵ –  ΞυϗοΫΫΤϦʹ޲͍͍ͯΔ –  .112VFSZ&OHJOF1SFTUPɺ*NQBMBɺ#JH2VFSZ %SFNFM   •  4USFBN1SPDFTTJOH –  όον͸࣮ߦͰ͖ͳ͍͕ετϦʔϜʹରͯ͠ϦΞϧλΠϜॲཧͰ͖Δ –  /PSJLSBɺ"QBDIF,BGLBɺ5XJUUFS4UPSNFUD "NB[PO3FETIJGU΋ 4IPSU#BUDI  ৄ͘͠ ͳ͍ͷͰলུ  cf.  Batch  processing  and  Stream  processing  by  SQL   h;p://www.slideshare.net/tagomoris/hcj2014-­‐sql
  12. Ձ֨ •  ྉۚ – σʔλอ؅(#݄ – ΫΤϦ5# εΩϟϯͨ͠σʔλͷαΠ ζ   "NB[PO4ΑΓ࣮

    ͸͍҆ νέοτ΋Β͍·ͨ͠ 
  13. #JH2VFSZͷ಺෦ ͚ͩ͢͜͠ 

  14. (PPHMF#JH%BUB4UBDL •  ʰ(PPHMFΛࢧ͑Δٕज़ʱ –  #JH%BUB4UBDL –  ('4ɺ#JH5BCMFɺ.BQ3FEVDFFUD •  #JH%BUB4UBDL – 

    #JH%BUB4UBDLͷ্ʹߏங͞Εͨɺͷ՝୊Λղফ͢Δ࣮૷܈ –  $PMPTTVT .FHBTUPSF 4QBOOFS 'MVNF+BWB %SFNFM طʹ(PPHMFࣾ಺͸ #JH%BUB4UBDLͩ ͱ͔͍͏࿩΋ͪΒ΄Β
  15. #JH2VFSZͷٕज़ελοΫ (PPHMF'JMF4ZTUFN ('4  $PMPTTVT'JMF4ZTUFN $'4  $PMVNO*0 %SFNFM ෼ࢄ'4

    ('4ͷվྑܕ'4 ৄࡉ͸ඇެ։  #JH2VFSZͷͨΊͷྻࢦ޲ϑΝΠϧ ϑΥʔϚοτ ฒྻ42-࣮ߦΤϯδϯ σʔληϯλʔΛ·͍ͨͰ ෼ࢄ͞ΕͯΔσʔλΛฒྻ ͔ͭߴ଎ʹऔಘͰ͖ΔΒ͠ ͍
  16. $PMVNO*0 Dremel:  InteracIve  Analysis  of  Web-­‐Scale  Datasets   h;p://research.google.com/pubs/archive/36632.pdf ߦͰ͸ͳ͘ྻ୯ҐͰɻಛ

    ఆྻΛγʔέϯγϟϧʹ ಡΊΔ׌ͭ$PMPTTVT ͰฒྻಡΈࠐΈ
  17. %SFNFM Dremel:  InteracIve  Analysis  of  Web-­‐Scale  Datasets   h;p://research.google.com/pubs/archive/36632.pdf

  18. Root  Mixer Mixer  1   Shard  0-­‐8 Mixer  1  

    Shard  9-­‐16 Mixer  1   Shard  17-­‐24 Shard  0 Shard  10 Shard  12 Shard  20 Shard  24 Distributed  Storage  (e.g.,  CFS) Dremel  serving  tree Google  BigQuery  AnalyIcs  P.284   Chapter  9  Understanding  Query  ExecuIon ෼ࢄ
  19. Root  Mixer Mixer  1   Shard  0-­‐8 Mixer  1  

    Shard  9-­‐16 Mixer  1   Shard  17-­‐24 Shard  0 Shard  10 Shard  12 Shard  20 Shard  24 Distributed  Storage  (e.g.,  CFS) Dremel  serving  tree Google  BigQuery  AnalyIcs  P.284   Chapter  9  Understanding  Query  ExecuIon $'4 $PMVNO*0Ͱಛ ఆྻͷσʔλ͕Ұ෦ฦͬ ͯ͘Δ ෼ࢄ ू໿
  20. Root  Mixer Mixer  1   Shard  0-­‐8 Mixer  1  

    Shard  9-­‐16 Mixer  1   Shard  17-­‐24 Shard  0 Shard  10 Shard  12 Shard  20 Shard  24 Distributed  Storage  (e.g.,  CFS) Dremel  serving  tree Google  BigQuery  AnalyIcs  P.284   Chapter  9  Understanding  Query  ExecuIon $'4 $PMVNO*0Ͱಛ ఆྻͷσʔλ͕Ұ෦ฦͬ ͯ͘Δ ྻΛॱ൪ʹಡΈߦ Λऔಘɻ8)&3&۟ͳ ͲΛݟͯඞཁͳߦͷΈ ʹߜΓϝϞϦͰอ࣋ ෼ࢄ ू໿
  21. Root  Mixer Mixer  1   Shard  0-­‐8 Mixer  1  

    Shard  9-­‐16 Mixer  1   Shard  17-­‐24 Shard  0 Shard  10 Shard  12 Shard  20 Shard  24 Distributed  Storage  (e.g.,  CFS) Dremel  serving  tree Google  BigQuery  AnalyIcs  P.284   Chapter  9  Understanding  Query  ExecuIon $'4 $PMVNO*0Ͱಛ ఆྻͷσʔλ͕Ұ෦ฦͬ ͯ͘Δ ྻΛॱ൪ʹಡΈߦ Λऔಘɻ8)&3&۟ͳ ͲΛݟͯඞཁͳߦͷΈ ʹߜΓϝϞϦͰอ࣋ ֤TIBSE͔ΒσʔλΛू ໿ɻྫ͑͹ιʔτ΍-*.*5 ͷߜΓࠐΈͳͲ͢Δ ෼ࢄ ू໿
  22. Root  Mixer Mixer  1   Shard  0-­‐8 Mixer  1  

    Shard  9-­‐16 Mixer  1   Shard  17-­‐24 Shard  0 Shard  10 Shard  12 Shard  20 Shard  24 Distributed  Storage  (e.g.,  CFS) Dremel  serving  tree Google  BigQuery  AnalyIcs  P.284   Chapter  9  Understanding  Query  ExecuIon $'4 $PMVNO*0Ͱಛ ఆྻͷσʔλ͕Ұ෦ฦͬ ͯ͘Δ ྻΛॱ൪ʹಡΈߦ Λऔಘɻ8)&3&۟ͳ ͲΛݟͯඞཁͳߦͷΈ ʹߜΓϝϞϦͰอ࣋ ֤TIBSE͔ΒσʔλΛू ໿ɻྫ͑͹ιʔτ΍-*.*5 ͷߜΓࠐΈͳͲ͢Δ ू໿ͨ݁͠Ռ ΛDBMMFSʹฦ͢ ෼ࢄ ू໿
  23. #JH2VFSZͷ͍͢͝ॴ •  ΧϥϜܕ*0ɺ42-ͷ෼ׂ౷࣏ – Ͱ΋͜Εɺ.11తʹ͸௝͘͠ͳ͍ •  ͡Ό͋ɺ#JH2VFSZͷԿ͕͍͔͢͝ – (PPHMFͷ௒Ͱ͔͍Πϯϑϥ

  24. ׂͱ਎΋֖΋ͳ͍ŋŋŋ

  25. ͜ΜͳΫιΫΤϦͰ΋ඵɺ̐ඵͩ

  26. ,"*;&/QMBUGPSN*OD Ͱͷ࢖͍Ͳ͜Ζ

  27. Ϣʔεέʔε •  ΞΫηεϩάͷอଘௐࠪ •  ΞϓϦέʔγϣϯϩάͷղੳ %BUBXBSF )PVTF  •  "#ςετͷ༗ҙࠩ൑ఆ

  28. ΞΫηεϩά

  29. ΞΫηεϩά #JH2VFSZ •  /HJOYͷϩάΛqVFOUQMVHJOCJHRVFSZͰ ૹΓଓ͚Δ –  &&Ͱ҉߸Խ͞ΕͯΔΑ •  Կ͔༻͕͋ͬͨΒ42-Ͱղੳ – 

    %BJMZ8FFLMZ.POUIMZ17 –  ϓϩμΫγϣϯͷσόοά
  30. qVFOUQMVHJOCJHRVFSZ •  CZUBHPNPSJT͞ΜɺZVHVJ͞Μଞ •  ઌ೔͔Β,"*;&/QMBUGPSN*OD͕ϝ ϯςφʹ – ࣮࣭ɺԶ QBUDIFTXFMDPNF Ͱ͢

  31. ΞϓϦέʔγϣϯͷϩάղੳ

  32. ϩάΛඈ͹͢ •  3BJMT͔ΒUEMPHHFSSVCZͰqVFOUE΁ •  qVFOUEQMVHJOCJHRVFSZͰ#2ʹඈ͹͢

  33. ϩάΛඈ͹͢ܖػ •  ϦΫΤετຖ –  "QQMJDBUJPO$POUSPMMFS –  ϩάΠϯϢʔβͷଐੑΛඈ͹͢ˠ%"6΍."6ͷ ࢉग़ʹ •  Ϟσϧͷঢ়ଶมߋ࣌

    –  "DUJWF3FDPSE0CTFSWFS –  Ϟσϧຖʹద౰ͳଐੑΛݟસͬͯඈ͹͢ –  #JH2VFSZ͸ෳࡶͳ42-Ͱ΋ී௨ʹԠ౴͢Δ㱺ϓ ϩμΫτϚωʔδϟ͕ؾܰʹ42-ॻ͍ͯΔ
  34. ਖ਼نԽ͸͋·Γ͠ͳ͍ •  ελʔεΩʔϚ – %8)ͷఆ൪ͷϞσϦϯά •  ϑΝΫτςʔϒϧŋŋŋϩά •  ࣍ݩςʔϒϧŋŋŋϚελʔσʔλ ސ٬໊ͱ͔ 

    – ਖ਼نԽ͠ͳ͍ͷ͕ηΦϦʔ
  35. "#ςετ༗ҙࠩ൑ఆ •  "#ςετͷαʔϏεͳͷͰ͆ •  ৄࡉ͸ൿີ •  SFRTFDͱ͔qVUFOEͰૹͬͯΔ ͚Ͳ΁ͬͪΌΒ͞ –  ˞SFRTFDͷ)551SFRVFTUqVFOUE͕όοϑΝϦϯά͢ΔͷͰ

    #JH2VFSZͷ"1*ίʔϧ਺͸ͣͬͱগͳ͍
  36. ֎෦πʔϧͱͷ઀ଓ •  ΤΫηϧ –  #JH2VFSZ$POOFDUPSGPS&YDFMCZ(PPHMF –  ϐϘοτ෼ੳʹ •  %0.0 #*

     –  FYQFSJNFOUBMͳ#JH2VFSZΠϯλϑΣʔε ͋ͬͨ –  5BCMFBV౳ϝδϟʔͲ͜Ζ΋ରԠ࢝͠ΊͯΔ
  37. ໘౗ͳͱ͜Ζ •  qVFOUEQMVHJOCJHRVFSZ͕εΩʔϚϑΝΠϧΛཁٻ ͢Δ –  ͕͔ͩ͠͠IBLPCFSB͞Μ͕QBUDIΛॻ͍ͯ͘Εͨ –  W͔ΒGFUDI@TDIFNBػೳ͕࢖͑ΔΑ •  ࣍ݩςʔϒϧͷߋ৽

    –  61%"5&Ͱ͖ͳ͍ͷͰ –  ໷ؒͱ͔ʹҰճফͯ͠࡞ΔɺΈ͍ͨͳ –  1SFTUPΈ͍ͨʹҧ͏σʔλιʔεΛ+0*/Ͱ͖ͨΓ͢Δͱخ ͍͠ͷ͕ͩŋŋŋ
  38. ࢛ํࢁͦͷ •  42-ͱ͍ͬͯ΋ඪ४42-͡Όͳ͍Α –  3&(&91@."5$) ͱ͔3&(&91@&953"$5 ͱ͔+40/ ͱ ͔501 ͱ͔

    •  ʮͲ͏ͤϑϧεΩϟϯͯ͠Δ͠ʯͱ͍͏લఏʹཱͭͱΑ ͍ –  -&'5 '03."5@65$@64&$ UJNF   BTEBZ (3061#:EBZͱ͔ –  3&(&91@&953"$5 UJUMF S aX  BTGSBHNFOU(3061#: GSBHNFOU03%&3#:GSBHNFOU@DPVOUEFTDͱ͔ –  αϒΫΤϦ΍7JFX
  39. ࢛ํࢁͦͷ •  61%"5&΍%&-&5&͸ͳ͍ – ཁΒͳ͍ΧϥϜʹ͸OVMM •  ΧϥϜܕ͔ͩΒOVMMͳΒ༰ྔ৯Θͳ͍ – εΩʔϚ͸௥Ճ͸؆୯ •  ߋ৽͸جຊ࡟আͯ͠࡞Γ௚͠

  40. ࢛ํࢁͦͷ •  (PPHMF"OBMZUJDT #JH2VFSZศརͦ͏ –  ("ͷੜϩάΛ#JH2VFSZͰղੳͰ͖ΔΦϓγϣϯ –  ͨͩ͠("ͷ༗ྉαʔϏε •  Ͱ͔͍σʔλͷΠϯϙʔτ

    –  (PPHMF%BUB4UPSFʹஔ͍͔ͯΒΠϯϙʔτ͢Δͱߴ଎ •  5BCMF%FDPSBUPST –  σʔλͷ࣌ؒൣғΛࢦఆͯ͠ΫΤϦɻεΩϟϯର৅ͷσʔλ͕খ͘͞ͳ ΔͷͰΫΤϦඅ༻Λઅ໿Ͱ͖Δ •  +0*/੍ݶ.#͸ੲͷ࿩ –  +0*/&"$)Λ࢖͏ͱ.BQ3FEVDFͷTIV⒐FΈ͍ͨͳॲཧͰڊ େͳ+0*/ ԯYԯͱ͔ŋŋŋ ͯ͘͠ΕΔΑ
  41. ·ͱΊ •  #JH2VFSZ͸ϑϧεΩϟϯͰͰ͔͍σʔλͷ 42-͕਺ඵͳαʔϏε •  ΫιΫΤϦ΋ྗۀͰॲཧͪ͠Ό͏ΧοίΠΠ •  ෼ׂ౷࣏ (PPHMFͷ%$ن໛Ͱ਎΋֖΋ͳ͍ ฒྻॲཧܥ

    •  όονɺϩάղੳͳΜ͔ʹ࢖͑·͢ •  ࢲ͸(PPHMFࣾͷճ͠ऀͰ͸͍͟͝·ͤΜ
  42. 5IBOLT ֆCZ͋ΘΏ͖