Hivemallを使ってSQLで機械学習

by babahr

Slide 1

Slide 1 text

)JWFNBMMΛ࢖ͬͯ  42-Ͱػցֶश ϒϨΠϯύουഅ৔͸Δ͔ ೥݄೔  %BUB(BUFXBZ5BMLWPM  ෼-5 1% |❚ | 01/22

Slide 2

Slide 2 text

ࠓ೔ͷ಺༰ ͓఻͍͑ͨ͜͠ͱ w )JWFNBMMͱ͸ԿͰɺͲ͜ͰͲͷΑ͏ʹ࢖͏ͷ͔ w )JWFNBMMΛ࢖͏ͱԿ͕خ͍͠ͷ͔ ࿩͞ͳ͍͜ͱ w )JWFNBMMͰ࣮૷͞Ε͍ͯΔΞϧΰϦζϜͷৄࡉ w ࣮૷͢Δͱ͖ͷৄࡉͳ࢖͍ํ 9% |❚❚ | 02/22

Slide 3

Slide 3 text

ࠓ೔ͷ಺༰ ͓఻͍͑ͨ͜͠ͱ w )JWFNBMM͸Ͳ͜ͰͲͷΑ͏ʹ࢖͏ͷ͔ w )JWFNMMΛ࢖͏ͱԿ͕خ͍͠ͷ͔ ࿩͞ͳ͍͜ͱ w )JWFNBMMͰ࣮૷͞Ε͍ͯΔΞϧΰϦζϜͷৄࡉ w )JWFNBMM EJHEBHͷৄࡉͳ࢖͍ํ ɹ)JWFNBMM ΍EJHEBH ͷ࢖͍ํʹؔͯ͠͸ ɹɾެࣜυΩϡϝϯτ ɹɾެࣜHJUIVCͷ࣮૷ྫ ɹɾ։ൃऀͷ༉Ҫ͞Μͷॻ͔Ε͍ͯΔRJJUB ɹɹˢݸਓతʹ͔ͳΓΦεεϝ ɹ౳Λ࢝Ίɺ༗༻ͳهࣄ͕͋Γ·͢ͷͰͦͪΒޚཡ͍ͩ͘͞ 14% |❚❚❚ | 03/22

Slide 4

Slide 4 text

ࣗݾ঺հ ˗ ໊લɿഅ৔͸Δ͔ ˗ ग़਎ɿ૯߹ݚڀେֶӃେֶఱจՊֶઐ߈  ଠཅܥ֎࿭੕ ׊৭ᛙ੕ ࿭੕࣭ྔఱମ ˗ σʔλαΠΤϯςΟετΛ͍ͯ͠Δ  ΫϥΠΞϯτاۀͷΦ΢ϯυϝσ ΟΞͷձһσʔλ΍Ξϯέʔτ σʔλͷ෼ੳͳͲ 18% |❚❚❚❚ | 04/22 גࣜձࣾϒϨΠϯύουʮ"*ʢϓϥεΤʔΞΠʣʯ΢ΣϒαΠτΑΓ

Slide 5

Slide 5 text

એ఻͍ͤͯͩ͘͞͞ 23% |❚❚❚❚❚ | 05/22

Slide 6

Slide 6 text

ϒϨΠϯύου നۚ୆ΦϑΟε ͜͜ 27% |❚❚❚❚❚❚ | 06/22

Slide 7

Slide 7 text

ϒϨΠϯύου നۚ୆ΦϑΟε ͜͜ 27% |❚❚❚❚❚❚ | 06/22 ςʔϚ ෺ཧֶʷػցֶश ಺༰ ɾػցֶशΛ࢖ͬͨ෺ཧֶ෼໺ͷݚڀ঺հ ɾ෺ཧग़਎σʔλαΠΤϯςΟετͷ͓࢓ࣄͷ࿩ ɾ࿦จ঺հ࿮ ର৅ऀ ɾ෺ཧ͕޷͖ͳσʔλΞφϦετ σʔλαΠΤϯςΟετ .-ΤϯδχΞ ɾ෺ཧ෼໺ͷݚڀऀ ɾ෺ཧֶઐ߈ͷֶੜ

Slide 8

Slide 8 text

)JWFNBMM 32% |❚❚❚❚❚❚❚ | 07/22

Slide 9

Slide 9 text

)JWF )JWF2- ˗ )BEPPQɿϏοάσʔλΛѻ͏ͨΊͷϛυϧ΢ΣΞͰ  ɹɹɹɹฒྻ෼ࢄॲཧ͕Ͱ͖Δ ˗ )JWFɹɿ)BEPPQ্Ͱ%#.4ͷػೳΛఏڙ ˗ )JWF2-ɿ)JWFͰ࢖ΘΕΔ42-νοΫͳσʔλૢ࡞ݴޠ )BEPPQͷ ϩΰ )JWFͷϩΰ 36% |❚❚❚❚❚❚❚❚ | 08/22

Slide 10

Slide 10 text

)JWFNBMM ˗ ػցֶशϥΠϒϥϦ 5SFBTVSF%BUB͕ࣾ։ൃ ˗ )JWF2-ΫΤϦΛ࢖ֶͬͯश͔Β༧ଌ·ͰػցֶशͷҰ࿈ ͷॲཧΛ࣮ߦ͢Δ͜ͱ͕Մೳ ˗ ϥΠϒϥϦͷத਎͸KBWBͰهड़ 41% |❚❚❚❚❚❚❚❚❚ | 09/22

Slide 11

Slide 11 text

42- 45% |❚❚❚❚❚❚❚❚❚❚ | 10/22

Slide 12

Slide 12 text

SELECT item_id ,order_id FROM order LIMIT 3 ; 50% |❚❚❚❚❚❚❚❚❚❚❚ | 11/22

Slide 13

Slide 13 text

+----------+----------+ | item_id | order_id | +----------+----------+ | 1 | 1 | | 2 | 1 | | 3 | 2 | +----------+----------+ 55% |❚❚❚❚❚❚❚❚❚❚❚❚ | 12/22

Slide 14

Slide 14 text

)JWFNBMMͰ9(#PPTU 59% |❚❚❚❚❚❚❚❚❚❚❚❚❚ | 13/22 /&8೥݄ʙ

Slide 15

Slide 15 text

SELECT train_xgboost(features,target) FROM train_table ; ֶश 60% |❚❚❚❚❚❚❚❚❚❚❚❚❚❚ | 14/22

Slide 16

Slide 16 text

+----------+----------------------+ | model_id | model | +----------+----------------------+ | 1 |L?jrjk,O5v%zkfspel…… | +----------+----------------------+ 68% |❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚ | 15/22

Slide 17

Slide 17 text

SELECT xgboost_predict  (row_id,features,model_id,model) FROM xgb_model ; ༧ଌ 73% |❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚ | 16/22

Slide 18

Slide 18 text

σʔλϚʔτ xgb_input ֶश༻ΫΤϦ SELECT train_xgboost(features, label) as (model_id, model) FROM (SELECT features ,label FROM xgb_input cluster by rand(43) -- shuffle ) shuffled; xgb_model Ϟσϧ model_id Model 0 L?jrjk,O5v%z|kfspel…… 1 L?jrjk>PS;fkjsa^8~6|…… 2 lsa;p0”>02qJOel~2=*…… ộ ộ xgb_pred ༧ଌ݁Ռ ༧ଌ༻ΫΤϦ SELECT rowid, majority_vote(cast(predicted as int)) as label FROM ( SELECT xgboost_predict_one(rowid, dense_features, model_id, model) as (rowid, predicted) FROM xgb_model l LEFT OUTER JOIN xgb_input r ) t GROUP BY rowid; row_id Label 0 0 1 2 2 1 ộ ộ Ϟσϧ΍༧ଌ݁Ռ͸ςʔϒϧͰ؅ཧ 77% |❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚ | 17/22

Slide 19

Slide 19 text

)JWFNBMM͕༗ޮͳ৔໘ 82% |❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚ | 18/22

Slide 20

Slide 20 text

ྫ σδλϧϚʔέςΟϯάྖҬʹ͓͚Δ  ػցֶशγεςϜ૊ΈࠐΈ ֤ج൫γεςϜ σʔλϕʔε &$ ސ٬৘ใ 8FC ෼ੳ༻؀ڥ Ճ޻ɾసૹ લॲཧɹˠɹֶशɹˠɹਪ࿦ Ճ޻ɾసૹ &$ ׆༻ઌ ؀ڥΛ༻ҙ͢ΔϦιʔε Ճ޻ɾసૹͷ࣮૷ ӡ༻ͷίετ 91% |❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚ | 20/22

Slide 21

Slide 21 text

֤ج൫γεςϜ σʔλϕʔε &$ ސ٬৘ใ 8FC લॲཧɹˠɹֶशɹˠɹਪ࿦ &$ ׆༻ઌ ✓σʔλϕʔε಺෦ͰػցֶशͷҰ࿈ͷϑϩʔ͕׬݁ ✓σʔλ࿈ܞͷखؒΛলུՄೳ ӡ༻໨ઢͰ͸େ͖ͳϝϦοτ 95% |❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚ | 21/22 )JWF͕࢖͑Δ ৔߹ ෼ੳ༻؀ڥ͕ෆཁ

Slide 22

Slide 22 text

·ͱΊ w )JWFNBMMͱ͍͏)JWF2-ϕʔεͰॻ͚ΔػցֶशϥΠϒϥϦ͕͋Γɺ σʔλసૹϓϩηε͕ෆཁͳ఺ɺࠓ͋Δ؀ڥͷ··࣮૷Ͱ͖Δ఺͕େ ͖ͳϝϦοτ w )JWF͕࢖͑Δ؀ڥͰػցֶशγεςϜΛ؆୯ʹࢼ͍ͨ͠৔߹ɺ )JWFNBMM͕Φεεϝ w ৮Ε·ͤΜͰ͕ͨ͠ ࢖͑ΔΞϧΰϦζϜ΋ൺֱతଟذʹΘͨΔ )JWF͕࢖͑Δ؀ڥPS5SFBTVSF%BUBಋೖاۀͰ  ͓࢓ࣄ͞Ε͍ͯΔ%4ͷํ͸ͥͻ͓ࢼ͠Λʂ 100% |❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚❚| 22/22