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

BigQuery と BigQuery ML

5248c7e88d8a46c1754c6762fcab3c3a?s=47 jewel12
November 20, 2018
56

BigQuery と BigQuery ML

5248c7e88d8a46c1754c6762fcab3c3a?s=128

jewel12

November 20, 2018
Tweet

Transcript

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

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

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