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

Google BigQuery の話 #gcpja

Naoya Ito
September 17, 2014

Google BigQuery の話 #gcpja

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

Naoya Ito

September 17, 2014
Tweet

More Decks by Naoya Ito

Other Decks in Technology

Transcript

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

    HDQKBOJHIU

    View full-size slide

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

    View full-size slide

  3. #JH2VFSZ֓؍

    View full-size slide

  4. (PPHMF#JH2VFSZ

    View full-size slide

  5. #JH2VFSZͱ͸
    •  ڊେͳσʔλ΁ͷ42- ͳͲ

    Λ਺ඵͰ࣮ߦ͢ΔΫϥ΢υαʔϏε
    –  ԯϨίʔυΛඵ ˞

    –  8FCΠϯλʔϑΣʔε͓Αͼ3&45"1*
    •  (PPHMFࣾ಺Ͱ࢖ΘΕ͖ͯͨ%SFNFMΛαʔϏεԽ
    –  ೥݄$MPTFEϦϦʔε
    –  ೥݄Ұൠެ։
    –  ܧଓతʹόʔδϣϯΞοϓ
    –  ೥݄#JH2VFSZ4USFBNJOH
    ˞(PPHMFͷދͷࢠʮ#JH2VFSZʯΛ'MVFOUEϢʔβʔ͕࢖Θͳ͍ཧ༝͕ͳ͘ͳͬͨཧ༝
    IUUQRJJUBDPNLB[VOPSJJUFNTBDBDCCBBBG

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  11. Ձ֨
    •  ྉۚ
    – σʔλอ؅(#݄
    – ΫΤϦ5# εΩϟϯͨ͠σʔλͷαΠ
    ζ


    "NB[PO4ΑΓ࣮
    ͸͍҆
    νέοτ΋Β͍·ͨ͠

    View full-size slide

  12. #JH2VFSZͷ಺෦ ͚ͩ͢͜͠


    View full-size slide

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

    View full-size slide

  14. #JH2VFSZͷٕज़ελοΫ
    (PPHMF'JMF4ZTUFN ('4

    $PMPTTVT'JMF4ZTUFN $'4

    $PMVNO*0
    %SFNFM
    ෼ࢄ'4
    ('4ͷվྑܕ'4
    ৄࡉ͸ඇެ։

    #JH2VFSZͷͨΊͷྻࢦ޲ϑΝΠϧ
    ϑΥʔϚοτ
    ฒྻ42-࣮ߦΤϯδϯ
    σʔληϯλʔΛ·͍ͨͰ
    ෼ࢄ͞ΕͯΔσʔλΛฒྻ
    ͔ͭߴ଎ʹऔಘͰ͖ΔΒ͠
    ͍

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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Ͱಛ
    ఆྻͷσʔλ͕Ұ෦ฦͬ
    ͯ͘Δ
    ྻΛॱ൪ʹಡΈߦ
    Λऔಘɻ8)&3&۟ͳ
    ͲΛݟͯඞཁͳߦͷΈ
    ʹߜΓϝϞϦͰอ࣋
    ෼ࢄ ू໿

    View full-size slide

  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&۟ͳ
    ͲΛݟͯඞཁͳߦͷΈ
    ʹߜΓϝϞϦͰอ࣋
    ֤TIBSE͔ΒσʔλΛू
    ໿ɻྫ͑͹ιʔτ΍-*.*5
    ͷߜΓࠐΈͳͲ͢Δ
    ෼ࢄ ू໿

    View full-size slide

  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
    ͷߜΓࠐΈͳͲ͢Δ
    ू໿ͨ݁͠Ռ
    ΛDBMMFSʹฦ͢
    ෼ࢄ ू໿

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  26. Ϣʔεέʔε
    •  ΞΫηεϩάͷอଘௐࠪ
    •  ΞϓϦέʔγϣϯϩάͷղੳ %BUBXBSF
    )PVTF

    •  "#ςετͷ༗ҙࠩ൑ఆ

    View full-size slide

  27. ΞΫηεϩά

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    – ਖ਼نԽ͠ͳ͍ͷ͕ηΦϦʔ

    View full-size slide

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

    View full-size slide

  35. ֎෦πʔϧͱͷ઀ଓ
    •  ΤΫηϧ
    –  #JH2VFSZ$POOFDUPSGPS&YDFMCZ(PPHMF
    –  ϐϘοτ෼ੳʹ
    •  %0.0 #*

    –  FYQFSJNFOUBMͳ#JH2VFSZΠϯλϑΣʔε
    ͋ͬͨ
    –  5BCMFBV౳ϝδϟʔͲ͜Ζ΋ରԠ࢝͠ΊͯΔ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  41. 5IBOLT
    ֆCZ͋ΘΏ͖

    View full-size slide