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
    Ͱͷσʔλ ͷ΍ΓͱΓΛߴ଎ԽͰ͖Δ