Google BigQuery の話 #yapcasia

(PPHMF#JH2VFSZͷ࿩ /BPZB*UP ,"*;&/QMBUGPSN*OD :"1$"TJB5PLZP ˞(PPHMFࣾͷճ͠ ऀͰ͸͋Γ·ͤΜ

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

#JH2VFSZ֓؍

(PPHMF#JH2VFSZ

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

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

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

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

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

ଞͷྨࣅ࣮૷ͱͷϙδγϣχϯά •  -BSHF#BUDI –  ҆ఆͯ͠ڊେͳόονΛ࣮ߦͰ͖Δ –  ΫΤϦ࣮ߦ࣌ͷΦʔόʔϔου͕େ͖͍ ਺ेඵʙ਺े෼ – 
.BQ3FEVDFɺ)BEPPQ )JWF •  4IPSU#BUDI –  ΫΤϦ࣮ߦ࣌ͷΦʔόʔϔου͕਺ඵ –  ΞυϗοΫΫΤϦʹ޲͍͍ͯΔ –  .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

Ձ֨ •  ྉۚ – σʔλอ؅(#݄ – ΫΤϦ5# εΩϟϯͨ͠σʔλͷαΠ ζ "NB[PO4ΑΓ࣮
͸͍҆ ($1ؔ܎ͷษڧձ͍͘ͱ ͷແྉνέοτ΋Β ͑ͨΓ͠·͢

#JH2VFSZͷ಺෦ ͚ͩ͢͜͠

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

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

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

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

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 ෼ࢄ

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Ͱಛ ఆྻͷσʔλ͕Ұ෦ฦͬ ͯ͘Δ ෼ࢄ ू໿

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

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 ͷߜΓࠐΈͳͲ͢Δ ෼ࢄ ू໿

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ʹฦ͢ ෼ࢄ ू໿

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

ׂͱ਎΋֖΋ͳ͍ŋŋŋ

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

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

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

ΞΫηεϩά

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

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

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

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

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

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

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

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

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

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

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

5IBOLT ֆCZ͋ΘΏ͖

Google BigQuery の話 #yapcasia

Google BigQuery の話 #yapcasia

Transcript

More Decks by Naoya Ito

Other Decks in Technology

Featured