BigQuery と BigQuery ML

Transcript

1. #JH2VFSZ
   ͱ
   #JH2VFSZ
   .- ԭೄٓ໺࿷ΤϯδχΞษڧձ
   !KFXFM@Y

2. #JH2VFSZ
   #2 ͱ͸ wେن໛ͳσʔλʹ
   42-
   -JLF
   ͳΫΤϦΛ౤͛ͯߴ଎ʹ ݁ՌΛฦ͢ϓϥοτϑΥʔϜ
   w'JSFCBTF
   "OBMZUJDT
   ౳ͷσʔλ΋
   #2
   ΁ΤΫεϙʔ τ͠΍͘͢ͳͬͨ
   w࠷ۙ
   'JSFCBTF
   "#
   5FTUJOH
   ౳ͷσʔλ΋ΤΫεϙʔ τͰ͖ΔΑ͏ʹ
   'JSFCBTF
   4VNNJU
   

3. w#JH2VFSZ
   .-
   ͷ࿩Λ͍ͨ͠
   w#JH2VFSZ
   Λ৮ͬͨ͜ͱͳ͍ํ͕͍Δ͔΋͠ Εͳ͍ͷͰ
   #JH2VFSZ
   ͷ֓ཁͷઆ໌͔Β ࠓ೔ͷ࿩

4. #2
   ͷ༻్ͱྑ͍ͱ͜Ζ w༻్
   wϩάͷ෼ੳ
   wϨίϝϯυΤϯδϯΛ࡞ͬͨΓ
   wྑ͍ͱ͜Ζ
   wΦϯσϚϯυͷ෼ੳͰ଎͍
   wϑϧϚωʔδυ
   wσʔλ෼ੳج൫ͷͨΊͷετϨʔδ΍ܭࢉϦ ιʔεΛ༻ҙ͠ͳͯ͘ྑ͍

5. #2ར༻·Ͱͷงғؾ #JH2VFSZ (PPHMF
   $MPVE
   4UPSBHF 'JSFCBTF
   "OBMZUJDT -PDBM
   4UPSBHF
   FH
   $47

   'MVFOUE
   ɾ
   &NCVML 8FC
   $POTPMF (PPHMF
   4QSFBETIFFU (PPHMF
   %BUBTUVEJP +VQZUFS
   /PUFCPPL ʜ ʜ

6. #2͸ͳΜͰ଎͍ͷ wྻࢦ޲%#ͱΫΤϦͷ෼ׂ౷࣏
   wྻ͝ͱʹσʔλΛ࣋ͪɺѹ౗తϦιʔε ͰϑϧεΩϟϯ
   w.Z42-
   ͷΑ͏ͳ
   #USFF
   ΠϯσοΫε ͸ແ͍
   w΋͏গ͠ৄ͍͠આ໌͸ຊεϥΠυͷ࠷ޙͷ ͓·͚͔Β

7. ؾΛ͚͍ͭͨ͜ͱ wεΩϟϯྔʹԠͯ͡՝ۚ
   w4&-&$5
   
   '30.ʜ
   ͸શྻεΩϟϯ
   w#JH2VFSZͰສԁ༹͔ͨ͠ਓͷإ
   wΩϟογϡ͞Εͳ͍ߴֹΫΤϦΛ
   DSPO
   Ͱ̍෼ ʹ
   
   ճͯ͠͠·ͬͨΓ͢Δͱ੝Γ্͕Δ
   w΍ͬͨ͜ͱ͋Δʜ

8. #JH2VFSZ
   .-
   νϡʔτϦΞϧΛ΍ͬͯΈΑ͏

9. #JH2VFSZ
   .-
   .-
   .BDIJOF
   -FBSOJOH wྑ͍ͱ͜Ζ
   w#2
   ͰػցֶशϞσϧΛ࡞੒ɾ࣮ߦͰ͖Δ
   w#2
   Λطʹ࢖༻͍ͯ͠Δ৔߹͸ͦͷ··࢖͑Δ
   w42-
   ͷ஌ࣝΛར༻Ͱ͖Δ
   wαϙʔτ͞Ε͍ͯΔϞσϧͱ༻్ྫ
   wઢܗճؼ
   wച্༧ଌ
   w߲ϩδεςΟοΫճؼϞσϧ
   

   wϢʔβʔ͕঎඼Λങ͏͔ങΘͳ͍͔
   wଞΫϥε෼ྨϩδεςΟοΫճؼ
   wϢʔβʔͷᅂ޷ʢڵຯͷ͋Δ৯΂෺ͷδϟϯϧʣ

10. ྉۚ wϓϨϏϡʔྉۚ
    wIUUQTDMPVEHPPHMFDPNCJHRVFSZ
    wैྔ੍ͱఆ੍ֹ͕͋Δ
    wैྔܕ
    wτϨʔχϯάσʔλͷྔͰ՝ۚ
    w(#
    w(#݄·Ͱແྉ
    w༧ଌɾධՁΫΤϦར༻σʔλ͸
    5#
    wఆ੍ֹ
    

    w#JH2VFSZΛఆֹྉۚͰ࢖ͬͯΔਓ޲͚ʁ

11. νϡʔτϦΞϧ wIUUQTDMPVEHPPHMFDPNCJHRVFSZEPDT CJHRVFSZNMBOBMZTUTUBSU
    w(PPHMF
    $PMBC
    Λ࢖Θͳ͍ํͷνϡʔτϦΞϧ
    wΫΤϦ͚ͩͰਐΊΔ
    w(PPHMF
    ΞφϦςΟΫεαϯϓϧσʔληοτ
    wτϥϯβΫγϣϯʢܾࡁʣ͕׬ྃ͢Δ͔Ͳ͏͔Λ༧ଌ͢Δ
    wϩδεςΟοΫճؼ

12. ͬ͘͟Γ
    ϩδεςΟοΫճؼͷઆ໌ 8JLJQFEJB
    ϩδεςΟοΫճؼΑΓ wೖྗσʔλ
    9
    ͕͋ͬͨͱ͖ʹ͋ΔΫϥεʢ:ʣʹ෼ྨ͞ΕΔ֬཰
    wύϥϝʔλΛֶशσʔλ͔Βਪఆ͍ͯ͘͠
    wЋ
    Ќ
    ͸ύϥϝʔλͰɺ͜ΕΛมԽ͍ͤͯ͘͞
    wύϥϝʔλͷมԽ
    ˠ
    ༧ଌ݁Ռͱਖ਼ղͱͷࠩʢଛࣦʣग़͢ˠଛࣦΛখ ͍ͯ͘͘͞͠ͷ܁Γฦ͠

13. #2
    .-ͷྲྀΕ w$3&"5&
    .0%&-
    จͰϞσϧΛ࡞੒
    w4&-&$5
    Ͱࢦఆͨ͠σʔλΛֶशσʔλͱ͢Δ
    w.-&7"-6"5&
    ؔ਺ͰϞσϧͷධՁ
    w.-13&%*$5
    ؔ਺ͰϞσϧΛ༻͍ͨ༧ଌ

14. Ϟσϧ࡞੒ CREATE MODEL `bqml_tutorial.sample_model` OPTIONS(model_type='logistic_reg') AS SELECT IF(totals.transactions IS NULL,

    0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews FROM `bigquery-public- data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'

15. Ϟσϧ࡞੒ CREATE MODEL `bqml_tutorial.sample_model` OPTIONS(model_type='logistic_reg') AS SELECT IF(totals.transactions IS NULL,

    0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews FROM `bigquery-public- data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170630' ϩδεςΟοΫճؼͷϞσϧΛ࡞Δ ༧ଌͷϥϕϧʢܾࢉͨ͠
    PS
    ͯ͠ͳ͍ʣ
    ༧ଌϥϕϧ͸
    MBCFM
    ΧϥϜͰ΋
    015*0/4
    Ͱ΋ ࢦఆͰ͖Δ 4&-&$5ͨ͠σʔλͰֶश 4&-&$5
    ͍ͯ͠Δ֤ΧϥϜΛύϥϝʔλͱͯ͠νϡʔχϯά͢Δ

16. աֶशΛ๷͙ͨΊͷ
    σʔλ෼ׂ w#2
    ͕ࣗಈͰ΍ͬͯ͘ΕΔ
    wֶशσʔλͱݕূσʔλʹ෼ׂ
    wֶशͨ͠ϞσϧͰݕূσʔλΛ෼ྨ
    wֶशσʔλʹରͯ͠෼ྨ͢Δͱաֶशͯ͠͠·͏
    wաֶश
    wֶशσʔλʹϑΟοτ͗ͨ͢͠ϞσϧΛ࡞ͬͯ͠·͏͜ͱ
    w࣮ࡍͷσʔλʹద༻ͨ͠Β͏·͘ਫ਼౓͕Ͱͳ͍ͱ͍͏͜ ͱ΋

17. ϞσϧͷධՁ #standardSQL SELECT * FROM ML.EVALUATE(MODEL `bqml_tutorial.sample_model`, ( SELECT IF(totals.transactions

    IS NULL, 0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews FROM `bigquery-public- data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801')) .-&7"-6"5&
    ͰϞσϧΛධՁ͢Δ
    ධՁσʔλ͸Ϟσϧ࡞੒࣌ʹ࢖ͬͨ೔෇ͱ͸ҧ͏΋ͷΛར༻͍ͯ͠Δ
    ֤ࢦඪͷݟํ͸νϡʔτϦΞϧͷεςοϓ

18. ϞσϧΛ࢖ͬͯ༧ଌ #standardSQL SELECT country, SUM(predicted_label) as total_predicted_purchases FROM ML.PREDICT(MODEL `bqml_tutorial.sample_model`,

    ( SELECT IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(geoNetwork.country, "") AS country FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801')) GROUP BY country ORDER BY total_predicted_purchases DESC LIMIT 10 wࠃ͝ͱͷਫ਼ࢉ׬ྃ਺Λ༧ଌ
    w.-13&%*$5
    ؔ਺ͷ
    4&-&$5
    Ͱೖྗ
    w݁Ռ͸
    QSFEJDUFE@MBCFM
    ʹ
    
    PS
    
    ͰೖΔ
    wQSFEJDUFE@MBCFM@DPMVNO@OBNF
    ͱ͍͏໋໊نଇ

19. ·ͱΊ

20. w#JH2VFSZ
    ͷ֓ཁΛ஌Εͨ
    w#JH2VFSZ
    .-
    νϡʔτϦΞϧΛ΍ͬͯΈͨ
    w(PPHMF
    ΞφϦςΟΫεͷαϯϓϧσʔληοτ͔Βߪ ೖ༧ଌ͕Ͱ͖Δ
    wσʔλͷϩʔυ͑͢͞Ε͹ܭࢉϦιʔεΛ؅ཧ͠ͳ͍Ͱྑ ͍σʔλղੳج൫Λ࡞ΕΔ
    w·ͣ͸ؾܰʹݱঢ়೺Ѳ͔Β
    wࣗ෼ͨͪͷ
    8FC
    αʔϏεͷσʔλΛ࢖ͬͯ൑அͨ͠Γɺ Ձ஋ΛߴΊ͍ͯ͜͏
    wͦͷαʔϏεʹ͔͠ແ͍σʔλ͸وॏͳͷͰՄೳͳݶΓ

    ஷΊ͍ͯ͜͏

21. w.-
    ͸ෑډ͕௿͘ͳ͖͍ͬͯͯΔ
    w#JH2VFSZ
    .-
    ͰֶशͰ͖ΔϞσϧͷΑ͏ʹయܕతͳ΋ ͷͳΒखܰʹ࢖͑Δ
    w'JSFCBTF
    .-
    ,JU
    Ͱ࢖༻ࡁΈϞσϧΛ࢖༻Ͱ͖ͨΓ
    wͳΜ͔͍͍ײ͡ʹϞσϧΛ࡞Δ͜ͱ͕Ͱ͖ͯ΋ϞσϧΛ࢖ ͏ଆͱͯ͠ҙࣝ͢Δ͜ͱ͸͋Δ
    wධՁ݁Ռͷݟํ
    wαʔϏεಛੑͱরΒ͠߹Θͤͯඞཁͳਫ਼౓ͷೝࣝ
    wΞϓϦέʔγϣϯ΁ͷϞσϧͷ૊ΈࠐΈ
    wϞσϧͷߋ৽Մೳੑ
    

    wਫ਼౓ͷϞχλϦϯά
    wσʔλ͸ੜϞϊ
    wϞσϧ͕ݹ͘ͳͬͯਫ਼౓͕མ͖͍ͪͯͯͳ͍͔
    wࠓޙ͸͜ͷลΓΛѻ͑ΔΑ͏ʹͳΔͱڧͦ͏

22. ͓·͚

23. #2͸ͳΜͰ଎͍ͷʢଓ͖ʣ IUUQTDMPVEHPPHMFDPNCMPHQSPEVDUTHDQCJHRVFSZVOEFSUIFIPPE

24. w ೥ͷهࣄͳͷͰࠓ͸ҧ͏͔΋
    w IUUQTDMPVEHPPHMFDPNCMPHQSPEVDUTHDQCJHRVFSZ VOEFSUIFIPPE
    w ྻࢦ޲%#ͱΫΤϦͷ෼ׂ౷࣏
    w ԣʹ͢Ε͹͢Δ΄Ͳ଎͍ͷͰɺຊ౰ʹେྔͷϦιʔεΛ࢖͑Δ
    (PPHMF
    ͳΒͰ͸ͷαʔϏε
    

    w %SVNFM
    ΫΤϦ࣮ߦΤϯδϯ
    w ΫΤϦΛ෼ׂͯ͠ॲཧ͠ɺ࠷ऴతʹ૊Έ্͛Δ·Ͱ
    w $PMPTTVT
    ෼ࢄετϨʔδ
    w ΧϥϜ୯ҐͰσʔλΛѹॖ
    w #PSH
    ΫϥελϚωʔδϟ
    w ਺ेສ୆ͷϚγϯͰಈ࡞͢ΔΫϥελ
    w Ϛγϯ͕མͪͯ΋େৎ෉
    w +VQJUFS
    ωοτϫʔΫ
    w 
    1FUBCJUTFD
    ͷόϯυ෯Λ࣋ͭωοτϫʔΫ
    w ສ୆ͷϚγϯ͕(CTͰ΍ΓͱΓͰ͖Δ
    w %SFNFM
    ͷ֤֊૚ؒ΍
    -FBG
    /PEFT
    ͱ
    $PMPTTVT
    Ͱͷσʔλ ͷ΍ΓͱΓΛߴ଎ԽͰ͖Δ