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

Data preprocessing for MachineLearning/BI by Golang and MySQL UDF

Data preprocessing for MachineLearning/BI by Golang and MySQL UDF

世界初!Go言語とMySQL UDFで実現する超高速データ前処理/分析基盤

Google BigQuery MLと同じ発想で、MySQL上に文字列データ前処理関数群を作って運用まで回していた。という話をさせていただきました。

2018.7.27 Developers Summit 2018 Summer C-8 Session
https://event.shoeisha.jp/devsumi/20180727/session/1771/

7806b36df14baf48a05ea5471ac5662e?s=128

dproject21

July 27, 2018
Tweet

Transcript

  1. mecompany Inc. 1 2018728 ੈքॳʂ (Pݴޠͱ.Z42-6%'Ͱ࣮ݱ͢Δ ௒ߴ଎σʔλલॲཧ෼ੳج൫  %FWFMPQFST4VNNJU4VNNFS $4FTTJPOEFWTVNJ$

    ా໺ޱେथ ϛʔΧϯύχʔגࣜձࣾ (PMBOH0VUTUBOEJOH%BUBFOHJOFFS (0%
  2. 2 2018728 lલॲཧ൛(PPHMF#JH2VFSZ .-z Λ.Z42-্ʹ(PݴޠͰ࣮૷ͯ͠ ӡ༻·Ͱճ͍ͯ͠·ͨ͠  %FWFMPQFST4VNNJU4VNNFS $4FTTJPOEFWTVNJ$ ా໺ޱେथ

    ϛʔΧϯύχʔגࣜձࣾ (PMBOH0VUTUBOEJOH%BUBFOHJOFFS (0% mecompany Inc.
  3. 2018728 mecompany Inc. 3 ϓϩϑΟʔϧ ా໺ޱେथ %BJLJ5BOPHVDIJ !EQSPKFDU ϛʔΧϯύχʔגࣜձࣾ (PMBOH0VUTUBOEJOH%BUBFOHJOFFS

    (0% ྵࡉιϑτϋ΢ε 7#/&5 4*FS 8FCαʔϏεاۀ ྲྀ࿘ͷຽ ओݴޠ $+BWB ػցֶश *P5τϥΠΞϧࢀը 1ZUIPO (PMBOH લॲཧϥΠϒϥϦ։ൃͳͲ "LJCBHP (PMBOH5PLZP $PEFGPS+BQBO Ϧʔμʔक़ %FW-07& 1VC 50$G& #PPUDBNQ 8"$"5& ϛʔΧϯύχʔגࣜձࣾ
  4. ϓϩϑΟʔϧ mecompany Inc. 4 8 C: % # G+K $/&

    /0B — „ž’‘ -! 8 XVWV WX)Y( ,J'EZSYYSXQ – …šœ Œ†ƒXeQ 3 I ¡A>#¢ P* 9 ¡bci¢ WRVVV W_ 51NŸ?ŸD Ž“ Œ€ œš Š›žM6ŸF ldg ean mjg "L@ B" VYS\Z[]S^[]_ VYS\\\]SZ_XX t||y`UUvrpxvyow~Tvr t||y`UU{p}ruTvr t||y`UU{p}ruqo|oTvr t||y`UU{p}ruZvzTvr  L@ ?Ÿ51™ „ U7DU? =Ÿ2H / /ˆž‰‚ž‡ / fl /Ÿ™‚ / ŸŸ; ŸŸŸ /• ˜” ‹ ŸŸŸ 4OŸ?.< ŸŸŸ kbmdg Ž“ Œ ŸŸŸ kbmdgQsxzQhj 2018 7)28(
  5. mecompany Inc. 5 ҩྍػؔɾༀہɾհޢࣄۀॴͷࢪઃɾ๏ਓσʔλϕʔε ϥΠηϯεͷൢചɾϝϯςφϯεΛߦ͍ͬͯ·͢ɻ 2018728 ϓϩϑΟʔϧ

  6. <3S mecompany Inc. 6 7 > QP#? y!9/w (Az <[R'_Uw4KwF6_HX

    Z^\Wkxg* Y^]r`xmoic_ \'_%X y!9/w (Az 'U Cefjt@ ;B.E@ ;3uxbx ;3S MJ O2( SCUEL Projectpxlnx VL $=Ndxqf I8G &G ;wMwOdxqdx wwA 1Dsvoah M0 WEB WEB WEB SCUEL Projectpxlnx kxg58 )+ :T] HP 2018"7-28, ϓϩϑΟʔϧ
  7. 5-K ࠓ೔ͷ࿩͸ʜ mecompany Inc. 7 0 7 IH8 r2)p #:s

    r2)p #:s "M <abem9 4;(>9 4-nq_q 4-K EB G,# SCUEL Projectjqghq ND 6F`qkb A1@ !@ 4pEpG`qk`q pp: +=loi^d E* WEB  WEB  WEB  SCUEL Projectjqghq fqc.1 $% 3LZ HP 20187'28& J"]?Q\R[Y CWXSR fqc /YTPUZDVQO
  8. 6.I ݁࿦͸ʜ mecompany Inc. 8 1 8 HG9 k3*i #;l

    k3*i #;l "K =Z[^f: 5<)?: 5.gjXj 5.I DB F-# SCUEL Projectcj`aj LC 7EYjd[ A2@ !@ 5iDiFYjdYj ii; ,>ehbW] D+ WEB  WEB  WEB  SCUEL Projectcj`aj _j\/2 $% 4JU HP 20187(28' &_j\U 0 PRMQNOVSST
  9. 2018728 mecompany Inc. 9 σʔλ։ൃͷݱ৔

  10.  σʔλ։ൃͷݱ৔ mecompany Inc. 10     

    ެతػ͔ؔΒ։ࣔ͞Ε͍ͯΔσʔλ &YDFM $47ͷଞ 1%' 8FCαΠτ ࢴʢ෺ཧʣ͕͋Δ    2018 728 ࢴͱઓ͏ͨΊʹ0$3Λ࢖ͬͨ͋ͱ͸ ޡೝࣝσʔλͱઓΘͳ͚Ε͹͍͚ͳ͍ɻ
  11.  mecompany Inc. 11      ެతػ͔ؔΒ։ࣔ͞Ε͍ͯΔσʔλ

    தʹ͸ zόουσʔλz΋ଘࡏ͢Δ    2018 728 • ޡࣈ୤ࣈ • දهΏΕ • ҟମࣈ • ༣ศ൪߸ؒҧ͍ • ి࿩൪߸ ͳ͔ͥ&ϝʔϧΞυϨε • ʙࢢʙ۠ ͷ͕۠ൈ͚͍ͯΔ σʔλ։ൃͷݱ৔
  12. ! mecompany Inc. 12   / -0 / -0

     " 2018728 લॲཧର৅σʔλ ԯ௒߲໨ ʢ਺ेສϨίʔυ º਺ສ߲໨ʣ %'+.*  +.*,.) ࠃ಺๏ਓ ສϨίʔυ ެతػ͔ؔΒ։ࣔ͞Ε͍ͯΔσʔλΛ౷߹͠ l࢖͑Δzσʔλʹ͢ΔͨΊɺલॲཧ͕ඞཁ #( $& +.*,.) ଞ͔ࣾΒߪೖͨ͠σʔλϕʔε ਺ԯϨίʔυ σʔλ։ൃͷݱ৔
  13. mecompany Inc. 13 ެతͳެ։৘ใ͔Β z࢖͑Δzσʔλʹ࢓্ཱͯ͛ͨ ҩྍػؔɾༀہɾհޢࣄۀॴͷࢪઃɾ๏ਓσʔλϕʔε ϥΠηϯεͷൢചɾϝϯςφϯεΛߦ͍ͬͯ·͢ɻ 2018728 σʔλ։ൃͷݱ৔

  14. ! mecompany Inc. 14   / -0 ਺ԯʙԯ݅௒ͷσʔλલॲཧ͕ఆৗۀ຿ /

    -0  " 2018728 %'+.*  +.*,.) #( $& +.*,.) • ຖ೥มΘΔ੍౓มߋʹ௥ै • σʔλͷ෼ׂɺ౷߹ɺഇࢭ͕೔ৗతʹൃੜ • ໊শมߋɺࢢ۠ொଜ߹ซͳͲ ॅॴมߋ΋ൃੜ͢Δ σʔλ։ൃͷݱ৔
  15. ! σʔλ։ൃͷ࣮ࡍ mecompany Inc. 15   / -0 ߴ଎ɺߴਫ਼౓ɺशಘ༰қͳલॲཧ͕ඞཁ

    / -0  " 2018728 %'+.*  +.*,.) #( $& +.*,.) • ࣄۀ͕֦େ͢Δͱѻ͏ྖҬ΍߲໨͕૿͑Δ • ৽ϝϯόʔͰ΋͙͢ʹ ΩϟονΞοϓͰ͖Δ࢓૊Έ͕ඞཁ • σʔλ෼ੳɺઃܭɺ࣮૷શͯͰ ಉ͡࢓૊ΈΛ࢖͍ੜ࢈ੑΛߴΊ͍ͨ
  16. 2018728 mecompany Inc. 16 ͳͥ.Z42-6%' ʢϢʔβʔఆٛؔ਺ʣ ͳͷ͔

  17.  ͳͥ.Z42-6%'ͳͷ͔ mecompany Inc. 17     

    42-͕ෆಘҙͳจࣈܕσʔλͷॲཧΛ Ͳ͏ʹ͔͍ͨ͠    2018 728 • JGOVMM ΍ SFQMBDF͕Ԇʑͱଓ͘42- • ਺ઍߦͷετΞυϓϩγʔδϟ • ૿͑ΔҰํͷσʔλྔʹॲཧ͕௥͍෇͔ͳ͍
  18.  ͳͥ.Z42-6%'ͳͷ͔ mecompany Inc. 18     

    42-͕ෆಘҙͳจࣈܕσʔλͷॲཧΛ 6%'ʢϢʔβʔఆٛؔ਺ʣͰ࿫͏    2018 728 • ৽ϝϯόʔͰ΋͙͢ʹΩϟονΞοϓͰ͖Δ࢓૊Έ͕ඞཁ ˠࣾ಺શମͷڞ௨εΩϧ͕42- • σʔλ෼ੳɺઃܭɺ࣮૷શͯͰಉ͡࢓૊ΈΛ࢖͍͍ͨ ˠ6%'ͳΒ42-ͷͳ͔Ͱѻ͏͜ͱ͕Ͱ͖Δ • 42-͕ෆಘҙͳจࣈܕσʔλͷՃ޻ΛͲ͏ʹ͔͍ͨ͠ ˠจࣈܕσʔλॲཧ༻ͷ6%'Λఆٛ͢Δ
  19. 2018728 mecompany Inc. 19 ͳͥ (Pݴޠͳͷ͔

  20. ͳͥ(Pݴޠͳͷ͔ mecompany Inc. 20 ػցֶशͳΒɺࠓͷ࣌͝ੈͩͱ1ZUIPO ػցֶश #*σʔλ෼ੳͷ ͞ΒʹલʹདྷΔॲཧͳͷͰ 1ZUIPOͰͳͯ͘΋Α͍ 2018728

  21. ͳͥ(Pݴޠͳͷ͔ mecompany Inc. 21 $$ ͱͷ਌࿨ੑ͕ߴ͘ɺॻ͖΍͍͢ 2018728 • .Z42-6%'͸௨ৗ $$

    Ͱॻ͔ΕΔ • $$ ͱͷม׵෦෼͕ ଞݴޠͱൺ΂ͯൺֱతॻ͖΍͍͢ ʢ'PSFJHOGVODUJPOJOUFSGBDF''*(PݴޠͰ͸DHPʣ • ϝϞϦ؅ཧ͸($͕΍ͬͯ͘ΕΔ • ϥΠϒϥϦ΋͍͍۩߹ʹἧͬͯΔ
  22. ͳͥ(Pݴޠͳͷ͔ mecompany Inc. 22 ѻ͏σʔλͱॲཧ଎౓ͷཁ݅ʹϚονͨ͠ 2018728 • όΠφϦʹ࣮ͯ͠ߦͰ͖Δ • αϩήʔτϖΞͱ͔ؾʹ͠ͳͯ͘Α͍

    • $$ ͱಉ౳ͷ଎౓Ͱɺ 3VCZ1ZUIPOͱಉ౳ͷѻ͍΍͢͞ ͕ಘΒΕΔͷ͕(Pݴޠ • จࣈܕσʔλͷॲཧ͕ओͳ໨తͳͷͰɺ 1BOEBT΍/VNQZΛ࢖͏ඞཁੑ͕ͳ͔ͬͨ
  23. ͳͥ(Pݴޠͳͷ͔ mecompany Inc. 23 কདྷతͳ֦ுੑ 2018728 • $-*΍8FC"1*ϑϨʔϜϫʔΫͷ ϥΠϒϥϦ͕ἧ͍ͬͯΔ •

    $MFBO"SDIJUFDUVSFͰ࢓ཱͯΕ͹ɺ $-*΍8FC"1*ͳͲ΁ͷల։΋༰қ 6%'΋&YUFSOBM*OUFSGBDFT ͱͯ͠ѻ͏͜ͱ͕Ͱ͖Δɻ
  24. 2018728 mecompany Inc. 24 ։ൃ੒Ռ

  25. ։ൃ੒Ռ mecompany Inc. 25 2018728 ։ൃͨ͠൚༻จࣈྻॲཧ6%' • DPODBU@OVMMBCMF OVMM΋͍͍۩߹ʹѻ͑ΔDPODBU •

    TQMJUΧϯϚ۠੾ΓσʔλͷY൪໨͚ͩΛऔΓग़͢ • 8FCαΠτεΫϨΠϐϯά݁Ռͷॲཧ • ਺ࣈɾΞϧϑΝϕοτɾΧλΧφͷશ֯൒֯ม׵ • ׽ࣈɾه߸ͷҟମࣈม׵ • ͻΒ͕ͳΧλΧφ૬ޓม׵ ͳͲଟ਺
  26. ։ൃ੒Ռ mecompany Inc. 26 2018728 ྫʣ OVMM΋͍͍۩߹ʹѻ͑ΔDPODBU FWFOU@OBNF QMBDF σϒαϛՆ

    ޚ஡ϊਫιϥγςΟ σϒαϛౙ OVMM OVMM ϕϧαʔϧ౦ژ೔ຊڮ select concat( ifnull(event_name, ’’), ifnull(place, ‘’) ) from event; select concat_nullable( event_name, place ) from event;
  27. ։ൃ੒Ռ mecompany Inc. 27 2018728 ॲཧ଎౓ 8FCαΠτεΫϨΠϐϯά݁Ռͷॲཧ • εΫϨΠϐϯά݁Ռͷ +40/σʔλԯ݅Λ࣌ؒͰॲཧ

    ਺ࣈɾΞϧϑΝϕοτɾΧλΧφͷશ֯൒֯ม׵ • 1P$ͷ࣌఺ͰετΞυϓϩγʔδϟ͔Β ഒߴ଎Խ ʢ೥݄ʣ • ݱࡏ͸ετΞυϓϩγʔδϟ͔Β ഒߴ଎Խ
  28. 2018728 mecompany Inc. 28 ߋͳΔ։ൃ੒Ռ

  29. ߋͳΔ։ൃ੒Ռ mecompany Inc. 29 .Z42-6%'ͷཪଆͰ ී௨ͷγεςϜ͕ಈ͘ 2018728 • ཪͰ 3FEJT

    ʹॲཧ݁ՌΩϟογϡΛ࣋ͨͤΔ • ଞ%#΁42-ΛྲྀͤΔ • ଞࣾ8FC"1*΋ୟ͚Δ 6%' 3FEJT ଞࣾ8FC"1* ଞ%# શͯ &YUFSOBM*OUFSGBDFT $MFBO"SDIJUFDUVSF͸͍͍ͧ .Z42-6%'lͳͷʹz
  30. ߋͳΔ։ൃ੒Ռ mecompany Inc. 30 2018728 l߲໨zʹಛԽͨ͠จࣈྻॲཧ6%' • ి࿩൪߸ ͳ͔ͥ&ϝʔϧΞυϨε •

    ࢢ֎ہ൪͕ൈ͚͍ͯΔ ͳͲͷόουి࿩൪߸σʔλ͔Βਖ਼͍͠ి࿩൪߸Λநग़͢Δ ޡࣈ୤ࣈɺදهΏΕɺҟମࣈɺ༣ศ൪߸ؒҧ͍ɺ ʙࢢʙ۠ ͷ͕۠ൈ͚͍ͯΔ ͳͲͷόουॅॴσʔλΛͳΜͱ͔͍ͨ͠ ॅॴਖ਼نԽ ి࿩൪߸ਖ਼نԽ
  31. ߋͳΔ։ൃ੒Ռ mecompany Inc. 31 2018728 l߲໨zʹಛԽͨ͠จࣈྻॲཧ6%' ΄΅ਖ਼͍͠ి࿩൪߸Λநग़Մೳ • ࣌ؒ͋ͨΓສ݅ΛॲཧՄೳ •

    ࠃ಺࠷ߴͷิ׬ਫ਼౓Λୡ੒ ˞ϥϯμϜʹநग़ͨ͠σʔλ݅Λଞ਺ࣾͷॅॴਖ਼نԽ݁Ռͱൺֱ ॅॴਖ਼نԽ ి࿩൪߸ਖ਼نԽ ͜͜·Ͱ঺հͨ͠ཁૉٕज़ͷ্ʹ੒Γཱ͍ͬͯΔ
  32. 2018728 mecompany Inc. 32 ͜ΕΒΛ ؾܰʹѻ͑Δ

  33. 6.I ݁࿦ mecompany Inc. 33 1 8 HG9 k3*i #;l

    k3*i #;l "K =Z[^f: 5<)?: 5.gjXj 5.I DB F-# SCUEL Projectcj`aj LC 7EYjd[ A2@ !@ 5iDiFYjdYj ii; ,>ehbW] D+ WEB  WEB  WEB  SCUEL Projectcj`aj _j\/2 $% 4JU HP 20187(28' &_j\U 0 PRMQNOVSST