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/

dproject21

July 27, 2018
Tweet

More Decks by dproject21

Other Decks in Technology

Transcript

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

    %FWFMPQFST4VNNJU4VNNFS
    $4FTTJPOEFWTVNJ$
    ా໺ޱେथ
    ϛʔΧϯύχʔגࣜձࣾ
    (PMBOH0VUTUBOEJOH%BUBFOHJOFFS (0%

    View Slide

  2. 2
    2018728
    lલॲཧ൛(PPHMF#JH2VFSZ .-z
    Λ.Z42-্ʹ(PݴޠͰ࣮૷ͯ͠
    ӡ༻·Ͱճ͍ͯ͠·ͨ͠

    %FWFMPQFST4VNNJU4VNNFS
    $4FTTJPOEFWTVNJ$
    ా໺ޱେथ
    ϛʔΧϯύχʔגࣜձࣾ
    (PMBOH0VUTUBOEJOH%BUBFOHJOFFS (0%

    mecompany Inc.

    View Slide

  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&
    ϛʔΧϯύχʔגࣜձࣾ

    View Slide

  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
    "[email protected]
    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
    [email protected]
    ?Ÿ51™ „
    U7DU? =Ÿ2H /
    /ˆž‰‚ž‡ /
    fl /Ÿ™‚ / ŸŸ;
    ŸŸŸ /• ˜” ‹
    ŸŸŸ 4OŸ?.<
    ŸŸŸ kbmdg Ž“ Œ
    ŸŸŸ kbmdgQsxzQhj
    2018 7)28(

    View Slide

  5. mecompany Inc. 5
    ҩྍػؔɾༀہɾհޢࣄۀॴͷࢪઃɾ๏ਓσʔλϕʔε
    ϥΠηϯεͷൢചɾϝϯςφϯεΛߦ͍ͬͯ·͢ɻ
    2018728
    ϓϩϑΟʔϧ

    View Slide

  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
    [email protected] ;[email protected]
    ;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,
    ϓϩϑΟʔϧ

    View Slide

  7. 5-K
    ࠓ೔ͷ࿩͸ʜ
    mecompany Inc. 7
    0
    7 IH8
    r2)p #:s
    r2)p #:s
    "M

    View Slide

  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[
    [email protected]
    [email protected]
    5iDiFYjdYj
    ii;
    ,>ehbW]
    D+
    WEB
    WEB
    WEB
    SCUEL Projectcj`aj
    _j\/2
    $%
    4JU
    HP
    20187(28'
    &_j\U 0
    PRMQNOVSST

    View Slide

  9. 2018728
    mecompany Inc.
    9
    σʔλ։ൃͷݱ৔

    View Slide


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



    ެతػ͔ؔΒ։ࣔ͞Ε͍ͯΔσʔλ
    &YDFM $47ͷଞ 1%' 8FCαΠτ ࢴʢ෺ཧʣ͕͋Δ



    2018728
    ࢴͱઓ͏ͨΊʹ0$3Λ࢖ͬͨ͋ͱ͸
    ޡೝࣝσʔλͱઓΘͳ͚Ε͹͍͚ͳ͍ɻ

    View Slide


  11. mecompany Inc. 11



    ެతػ͔ؔΒ։ࣔ͞Ε͍ͯΔσʔλ
    தʹ͸ zόουσʔλz΋ଘࡏ͢Δ



    2018728
    • ޡࣈ୤ࣈ
    • දهΏΕ
    • ҟମࣈ
    • ༣ศ൪߸ؒҧ͍
    • ి࿩൪߸ ͳ͔ͥ&ϝʔϧΞυϨε
    • ʙࢢʙ۠ ͷ͕۠ൈ͚͍ͯΔ
    σʔλ։ൃͷݱ৔

    View Slide

  12. !
    mecompany Inc. 12


    / -0
    / -0
    "
    2018728
    લॲཧର৅σʔλ ԯ௒߲໨
    ʢ਺ेສϨίʔυ º਺ສ߲໨ʣ
    %'+.*

    +.*,.)
    ࠃ಺๏ਓ ສϨίʔυ
    ެతػ͔ؔΒ։ࣔ͞Ε͍ͯΔσʔλΛ౷߹͠
    l࢖͑Δzσʔλʹ͢ΔͨΊɺલॲཧ͕ඞཁ
    #(
    $&
    +.*,.)
    ଞ͔ࣾΒߪೖͨ͠σʔλϕʔε ਺ԯϨίʔυ
    σʔλ։ൃͷݱ৔

    View Slide

  13. mecompany Inc. 13
    ެతͳެ։৘ใ͔Β z࢖͑Δzσʔλʹ࢓্ཱͯ͛ͨ
    ҩྍػؔɾༀہɾհޢࣄۀॴͷࢪઃɾ๏ਓσʔλϕʔε
    ϥΠηϯεͷൢചɾϝϯςφϯεΛߦ͍ͬͯ·͢ɻ
    2018728
    σʔλ։ൃͷݱ৔

    View Slide

  14. !
    mecompany Inc. 14


    / -0
    ਺ԯʙԯ݅௒ͷσʔλલॲཧ͕ఆৗۀ຿
    / -0
    "
    2018728
    %'+.*

    +.*,.)
    #(
    $&
    +.*,.)
    • ຖ೥มΘΔ੍౓มߋʹ௥ै
    • σʔλͷ෼ׂɺ౷߹ɺഇࢭ͕೔ৗతʹൃੜ
    • ໊শมߋɺࢢ۠ொଜ߹ซͳͲ
    ॅॴมߋ΋ൃੜ͢Δ
    σʔλ։ൃͷݱ৔

    View Slide

  15. !
    σʔλ։ൃͷ࣮ࡍ
    mecompany Inc. 15


    / -0
    ߴ଎ɺߴਫ਼౓ɺशಘ༰қͳલॲཧ͕ඞཁ
    / -0
    "
    2018728
    %'+.*

    +.*,.)
    #(
    $&
    +.*,.)
    • ࣄۀ͕֦େ͢Δͱѻ͏ྖҬ΍߲໨͕૿͑Δ
    • ৽ϝϯόʔͰ΋͙͢ʹ
    ΩϟονΞοϓͰ͖Δ࢓૊Έ͕ඞཁ
    • σʔλ෼ੳɺઃܭɺ࣮૷શͯͰ
    ಉ͡࢓૊ΈΛ࢖͍ੜ࢈ੑΛߴΊ͍ͨ

    View Slide

  16. 2018728
    mecompany Inc.
    16
    ͳͥ.Z42-6%'
    ʢϢʔβʔఆٛؔ਺ʣ
    ͳͷ͔

    View Slide


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



    42-͕ෆಘҙͳจࣈܕσʔλͷॲཧΛ
    Ͳ͏ʹ͔͍ͨ͠



    2018728
    • JGOVMM ΍ SFQMBDF͕Ԇʑͱଓ͘42-
    • ਺ઍߦͷετΞυϓϩγʔδϟ
    • ૿͑ΔҰํͷσʔλྔʹॲཧ͕௥͍෇͔ͳ͍

    View Slide


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



    42-͕ෆಘҙͳจࣈܕσʔλͷॲཧΛ
    6%'ʢϢʔβʔఆٛؔ਺ʣͰ࿫͏



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

    View Slide

  19. 2018728
    mecompany Inc.
    19
    ͳͥ
    (Pݴޠͳͷ͔

    View Slide

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

    View Slide

  21. ͳͥ(Pݴޠͳͷ͔
    mecompany Inc. 21
    $$ͱͷ਌࿨ੑ͕ߴ͘ɺॻ͖΍͍͢
    2018728
    • .Z42-6%'͸௨ৗ $$Ͱॻ͔ΕΔ
    • $$ͱͷม׵෦෼͕
    ଞݴޠͱൺ΂ͯൺֱతॻ͖΍͍͢
    ʢ'PSFJHOGVODUJPOJOUFSGBDF''*(PݴޠͰ͸DHPʣ
    • ϝϞϦ؅ཧ͸($͕΍ͬͯ͘ΕΔ
    • ϥΠϒϥϦ΋͍͍۩߹ʹἧͬͯΔ

    View Slide

  22. ͳͥ(Pݴޠͳͷ͔
    mecompany Inc. 22
    ѻ͏σʔλͱॲཧ଎౓ͷཁ݅ʹϚονͨ͠
    2018728
    • όΠφϦʹ࣮ͯ͠ߦͰ͖Δ
    • αϩήʔτϖΞͱ͔ؾʹ͠ͳͯ͘Α͍
    • $$ͱಉ౳ͷ଎౓Ͱɺ
    3VCZ1ZUIPOͱಉ౳ͷѻ͍΍͢͞
    ͕ಘΒΕΔͷ͕(Pݴޠ
    • จࣈܕσʔλͷॲཧ͕ओͳ໨తͳͷͰɺ
    1BOEBT΍/VNQZΛ࢖͏ඞཁੑ͕ͳ͔ͬͨ

    View Slide

  23. ͳͥ(Pݴޠͳͷ͔
    mecompany Inc. 23
    কདྷతͳ֦ுੑ
    2018728
    • $-*΍8FC"1*ϑϨʔϜϫʔΫͷ
    ϥΠϒϥϦ͕ἧ͍ͬͯΔ
    • $MFBO"SDIJUFDUVSFͰ࢓ཱͯΕ͹ɺ
    $-*΍8FC"1*ͳͲ΁ͷల։΋༰қ
    6%'΋&YUFSOBM*OUFSGBDFT
    ͱͯ͠ѻ͏͜ͱ͕Ͱ͖Δɻ

    View Slide

  24. 2018728
    mecompany Inc.
    24
    ։ൃ੒Ռ

    View Slide

  25. ։ൃ੒Ռ
    mecompany Inc. 25
    2018728
    ։ൃͨ͠൚༻จࣈྻॲཧ6%'
    [email protected] OVMM΋͍͍۩߹ʹѻ͑ΔDPODBU
    ● TQMJUΧϯϚ۠੾ΓσʔλͷY൪໨͚ͩΛऔΓग़͢
    ● 8FCαΠτεΫϨΠϐϯά݁Ռͷॲཧ
    ● ਺ࣈɾΞϧϑΝϕοτɾΧλΧφͷશ֯൒֯ม׵
    ● ׽ࣈɾه߸ͷҟମࣈม׵
    ● ͻΒ͕ͳΧλΧφ૬ޓม׵
    ͳͲଟ਺

    View Slide

  26. ։ൃ੒Ռ
    mecompany Inc. 26
    2018728
    ྫʣ OVMM΋͍͍۩߹ʹѻ͑ΔDPODBU
    [email protected] QMBDF
    σϒαϛՆ ޚ஡ϊਫιϥγςΟ
    σϒαϛౙ OVMM

    OVMM
    ϕϧαʔϧ౦ژ೔ຊڮ
    select concat(
    ifnull(event_name, ’’),
    ifnull(place, ‘’)
    ) from event;
    select concat_nullable(
    event_name,
    place
    ) from event;

    View Slide

  27. ։ൃ੒Ռ
    mecompany Inc. 27
    2018728
    ॲཧ଎౓
    8FCαΠτεΫϨΠϐϯά݁Ռͷॲཧ
    ● εΫϨΠϐϯά݁Ռͷ
    +40/σʔλԯ݅Λ࣌ؒͰॲཧ
    ਺ࣈɾΞϧϑΝϕοτɾΧλΧφͷશ֯൒֯ม׵
    ● 1P$ͷ࣌఺ͰετΞυϓϩγʔδϟ͔Β
    ഒߴ଎Խ ʢ೥݄ʣ
    ● ݱࡏ͸ετΞυϓϩγʔδϟ͔Β
    ഒߴ଎Խ

    View Slide

  28. 2018728
    mecompany Inc.
    28
    ߋͳΔ։ൃ੒Ռ

    View Slide

  29. ߋͳΔ։ൃ੒Ռ
    mecompany Inc. 29
    .Z42-6%'ͷཪଆͰ ී௨ͷγεςϜ͕ಈ͘
    2018728
    • ཪͰ 3FEJT ʹॲཧ݁ՌΩϟογϡΛ࣋ͨͤΔ
    • ଞ%#΁42-ΛྲྀͤΔ
    • ଞࣾ8FC"1*΋ୟ͚Δ
    6%' 3FEJT ଞࣾ8FC"1* ଞ%#
    શͯ &YUFSOBM*OUFSGBDFT
    $MFBO"SDIJUFDUVSF͸͍͍ͧ
    .Z42-6%'lͳͷʹz

    View Slide

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

    View Slide

  31. ߋͳΔ։ൃ੒Ռ
    mecompany Inc. 31
    2018728
    l߲໨zʹಛԽͨ͠จࣈྻॲཧ6%'
    ΄΅ਖ਼͍͠ి࿩൪߸Λநग़Մೳ
    • ࣌ؒ͋ͨΓສ݅ΛॲཧՄೳ
    • ࠃ಺࠷ߴͷิ׬ਫ਼౓Λୡ੒
    ˞ϥϯμϜʹநग़ͨ͠σʔλ݅Λଞ਺ࣾͷॅॴਖ਼نԽ݁Ռͱൺֱ
    ॅॴਖ਼نԽ
    ి࿩൪߸ਖ਼نԽ
    ͜͜·Ͱ঺հͨ͠ཁૉٕज़ͷ্ʹ੒Γཱ͍ͬͯΔ

    View Slide

  32. 2018728
    mecompany Inc.
    32
    ͜ΕΒΛ
    ؾܰʹѻ͑Δ

    View Slide

  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[
    [email protected]
    [email protected]
    5iDiFYjdYj
    ii;
    ,>ehbW]
    D+
    WEB
    WEB
    WEB
    SCUEL Projectcj`aj
    _j\/2
    $%
    4JU
    HP
    20187(28'
    &_j\U 0
    PRMQNOVSST

    View Slide