Google BigQuery の話 #gcpja

by Naoya Ito

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

#JH2VFSZ֓؍

Slide 4

Slide 4 text

(PPHMF#JH2VFSZ

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

#JH2VFSZͱ͸ •  ڊେͳσʔλ΁ͷ42- ͳͲ Λ਺ඵͰ࣮ߦ͢ΔΫϥ΢υαʔϏε –  ԯϨίʔυΛඵ ˞ –  8FCΠϯλʔϑΣʔε͓Αͼ3&45"1* •  (PPHMFࣾ಺Ͱ࢖ΘΕ͖ͯͨ%SFNFMΛαʔϏεԽ –  ೥݄$MPTFEϦϦʔε –  ೥݄Ұൠެ։ –  ܧଓతʹόʔδϣϯΞοϓ –  ೥݄#JH2VFSZ4USFBNJOH ˞(PPHMFͷދͷࢠʮ#JH2VFSZʯΛ'MVFOUEϢʔβʔ͕࢖Θͳ͍ཧ༝͕ͳ͘ͳͬͨཧ༝ IUUQRJJUBDPNLB[VOPSJJUFNTBDBDCCBBBG

Slide 7

Slide 7 text

ͲΜͳ͜ͱʹ࢖ΘΕΔ͔ •  Ϣʔεέʔε – ϩάղੳ – %BUBXBSF)PVTF –  •  ޲͍ͯͳ͍༻్ – ۀ຿%# ଎͍3%#.4Ͱ͸ ͳ͍Αɺͱ͍͏͜ͱ

Slide 8

Slide 8 text

#JH2VFSZ͸ͳͥ଎͍͔ •  جຊɺϑϧεΩϟϯͰ͕Μ͹Δ –  3%#.4ͷ#5SFFΠϯσοΫεͱ͔ͳ͍ •  42-Λ෼ࢄॲཧ –  .11 .BTTJWFMZ1BSBMMFM1SPDFTTJOH 2VFSZ&OHJOF %SFNFM •  ਺ઍ୆ͷσΟεΫͱߴ଎ωοτϫʔΫͰεέʔϧΞ΢τ –  5#ͷσʔλΛඵͰϦʔυ͢Δ*0

Slide 9

Slide 9 text

ͨͩ͠ •  ଎͍3%#.4Ͱ͸ͳ͍ •  େਓ਺ͰҰ౓ʹ࢖͏΋ͷͰ͸ͳ͍ – ओʹόονॲཧʹ࢖͏ •  εΩʔϚϨεͰ͸ͳ͍ 5#ن໛σʔλͰ΋ઢܗҎ ԼͰεέʔϧ͢Δ͕ɺٯ ʹখ͞ͳσʔλͰ΋਺ඵ ͷΦʔόʔϔου͕͋Δ ͷͰ

Slide 10

Slide 10 text

BigQuery読書会、@harukasan 資料より引用

Slide 11

Slide 11 text

ଞͷྨࣅ࣮૷ͱͷϙδγϣχϯά •  -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

Slide 12

Slide 12 text

Ձ֨ •  ྉۚ – σʔλอ؅(#݄ – ΫΤϦ5# εΩϟϯͨ͠σʔλͷαΠ ζ "NB[PO4ΑΓ࣮ ͸͍҆ νέοτ΋Β͍·ͨ͠

Slide 13

Slide 13 text

#JH2VFSZͷ಺෦ ͚ͩ͢͜͠

Slide 14

Slide 14 text

(PPHMF#JH%BUB4UBDL •  ʰ(PPHMFΛࢧ͑Δٕज़ʱ –  #JH%BUB4UBDL –  ('4ɺ#JH5BCMFɺ.BQ3FEVDFFUD •  #JH%BUB4UBDL –  #JH%BUB4UBDLͷ্ʹߏங͞Εͨɺͷ՝୊Λղফ͢Δ࣮૷܈ –  $PMPTTVT .FHBTUPSF 4QBOOFS 'MVNF+BWB %SFNFM طʹ(PPHMFࣾ಺͸ #JH%BUB4UBDLͩ ͱ͔͍͏࿩΋ͪΒ΄Β

Slide 15

Slide 15 text

#JH2VFSZͷٕज़ελοΫ (PPHMF'JMF4ZTUFN ('4 $PMPTTVT'JMF4ZTUFN $'4 $PMVNO*0 %SFNFM ෼ࢄ'4 ('4ͷվྑܕ'4 ৄࡉ͸ඇެ։ #JH2VFSZͷͨΊͷྻࢦ޲ϑΝΠϧ ϑΥʔϚοτ ฒྻ42-࣮ߦΤϯδϯ σʔληϯλʔΛ·͍ͨͰ ෼ࢄ͞ΕͯΔσʔλΛฒྻ ͔ͭߴ଎ʹऔಘͰ͖ΔΒ͠ ͍

Slide 16

Slide 16 text

$PMVNO*0 Dremel: InteracIve Analysis of Web-‐Scale Datasets h;p://research.google.com/pubs/archive/36632.pdf ߦͰ͸ͳ͘ྻ୯ҐͰɻಛ ఆྻΛγʔέϯγϟϧʹ ಡΊΔ׌ͭ$PMPTTVT ͰฒྻಡΈࠐΈ

Slide 17

Slide 17 text

%SFNFM Dremel: InteracIve Analysis of Web-‐Scale Datasets h;p://research.google.com/pubs/archive/36632.pdf

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

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&۟ͳ ͲΛݟͯඞཁͳߦͷΈ ʹߜΓϝϞϦͰอ࣋ ෼ࢄ ू໿

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

#JH2VFSZͷ͍͢͝ॴ •  ΧϥϜܕ*0ɺ42-ͷ෼ׂ౷࣏ – Ͱ΋͜Εɺ.11తʹ͸௝͘͠ͳ͍ •  ͡Ό͋ɺ#JH2VFSZͷԿ͕͍͔͢͝ – (PPHMFͷ௒Ͱ͔͍Πϯϑϥ

Slide 24

Slide 24 text

ׂͱ਎΋֖΋ͳ͍ŋŋŋ

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

ΞΫηεϩά

Slide 29

Slide 29 text

ΞΫηεϩά#JH2VFSZ •  /HJOYͷϩάΛqVFOUQMVHJOCJHRVFSZͰ ૹΓଓ͚Δ –  &&Ͱ҉߸Խ͞ΕͯΔΑ •  Կ͔༻͕͋ͬͨΒ42-Ͱղੳ –  %BJMZ8FFLMZ.POUIMZ17 –  ϓϩμΫγϣϯͷσόοά

Slide 30

Slide 30 text

qVFOUQMVHJOCJHRVFSZ •  CZUBHPNPSJT͞ΜɺZVHVJ͞Μଞ •  ઌ೔͔Β,"*;&/QMBUGPSN*OD͕ϝ ϯςφʹ – ࣮࣭ɺԶ QBUDIFTXFMDPNF Ͱ͢

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

ϩάΛඈ͹͢ܖػ •  ϦΫΤετຖ –  "QQMJDBUJPO$POUSPMMFS –  ϩάΠϯϢʔβͷଐੑΛඈ͹͢ˠ%"6΍."6ͷ ࢉग़ʹ •  Ϟσϧͷঢ়ଶมߋ࣌ –  "DUJWF3FDPSE0CTFSWFS –  Ϟσϧຖʹద౰ͳଐੑΛݟસͬͯඈ͹͢ –  #JH2VFSZ͸ෳࡶͳ42-Ͱ΋ී௨ʹԠ౴͢Δ㱺ϓ ϩμΫτϚωʔδϟ͕ؾܰʹ42-ॻ͍ͯΔ

Slide 34

Slide 34 text

ਖ਼نԽ͸͋·Γ͠ͳ͍ •  ελʔεΩʔϚ – %8)ͷఆ൪ͷϞσϦϯά •  ϑΝΫτςʔϒϧŋŋŋϩά •  ࣍ݩςʔϒϧŋŋŋϚελʔσʔλ ސ٬໊ͱ͔ – ਖ਼نԽ͠ͳ͍ͷ͕ηΦϦʔ

Slide 35

Slide 35 text

"#ςετ༗ҙࠩ൑ఆ •  "#ςετͷαʔϏεͳͷͰ͆ •  ৄࡉ͸ൿີ •  SFRTFDͱ͔qVUFOEͰૹͬͯΔ ͚Ͳ΁ͬͪΌΒ͞ –  ˞SFRTFDͷ)551SFRVFTUqVFOUE͕όοϑΝϦϯά͢ΔͷͰ #JH2VFSZͷ"1*ίʔϧ਺͸ͣͬͱগͳ͍

Slide 36

Slide 36 text

֎෦πʔϧͱͷ઀ଓ •  ΤΫηϧ –  #JH2VFSZ$POOFDUPSGPS&YDFMCZ(PPHMF –  ϐϘοτ෼ੳʹ •  %0.0 #* –  FYQFSJNFOUBMͳ#JH2VFSZΠϯλϑΣʔε ͋ͬͨ –  5BCMFBV౳ϝδϟʔͲ͜Ζ΋ରԠ࢝͠ΊͯΔ

Slide 37

Slide 37 text

໘౗ͳͱ͜Ζ •  qVFOUEQMVHJOCJHRVFSZ͕εΩʔϚϑΝΠϧΛཁٻ ͢Δ –  ͕͔ͩ͠͠IBLPCFSB͞Μ͕QBUDIΛॻ͍ͯ͘Εͨ –  W͔ΒGFUDI@TDIFNBػೳ͕࢖͑ΔΑ •  ࣍ݩςʔϒϧͷߋ৽ –  61%"5&Ͱ͖ͳ͍ͷͰ –  ໷ؒͱ͔ʹҰճফͯ͠࡞ΔɺΈ͍ͨͳ –  1SFTUPΈ͍ͨʹҧ͏σʔλιʔεΛ+0*/Ͱ͖ͨΓ͢Δͱخ ͍͠ͷ͕ͩŋŋŋ

Slide 38

Slide 38 text

࢛ํࢁͦͷ •  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

Slide 39

Slide 39 text

࢛ํࢁͦͷ •  61%"5&΍%&-&5&͸ͳ͍ – ཁΒͳ͍ΧϥϜʹ͸OVMM •  ΧϥϜܕ͔ͩΒOVMMͳΒ༰ྔ৯Θͳ͍ – εΩʔϚ͸௥Ճ͸؆୯ •  ߋ৽͸جຊ࡟আͯ͠࡞Γ௚͠

Slide 40

Slide 40 text

࢛ํࢁͦͷ •  (PPHMF"OBMZUJDT#JH2VFSZศརͦ͏ –  ("ͷੜϩάΛ#JH2VFSZͰղੳͰ͖ΔΦϓγϣϯ –  ͨͩ͠("ͷ༗ྉαʔϏε •  Ͱ͔͍σʔλͷΠϯϙʔτ –  (PPHMF%BUB4UPSFʹஔ͍͔ͯΒΠϯϙʔτ͢Δͱߴ଎ •  5BCMF%FDPSBUPST –  σʔλͷ࣌ؒൣғΛࢦఆͯ͠ΫΤϦɻεΩϟϯର৅ͷσʔλ͕খ͘͞ͳ ΔͷͰΫΤϦඅ༻Λઅ໿Ͱ͖Δ •  +0*/੍ݶ.#͸ੲͷ࿩ –  +0*/&"$)Λ࢖͏ͱ.BQ3FEVDFͷTIV⒐FΈ͍ͨͳॲཧͰڊ େͳ+0*/ ԯYԯͱ͔ŋŋŋ ͯ͘͠ΕΔΑ

Slide 41

Slide 41 text

·ͱΊ •  #JH2VFSZ͸ϑϧεΩϟϯͰͰ͔͍σʔλͷ 42-͕਺ඵͳαʔϏε •  ΫιΫΤϦ΋ྗۀͰॲཧͪ͠Ό͏ΧοίΠΠ •  ෼ׂ౷࣏(PPHMFͷ%$ن໛Ͱ਎΋֖΋ͳ͍ ฒྻॲཧܥ •  όονɺϩάղੳͳΜ͔ʹ࢖͑·͢ •  ࢲ͸(PPHMFࣾͷճ͠ऀͰ͸͍͟͝·ͤΜ

Slide 42

Slide 42 text

5IBOLT ֆCZ͋ΘΏ͖