データ活用の際にハマってしまって辛かったこと/tsurami-of-data

2019年7月19日 データとML周辺エンジニアリングを考える会 #2 (https://data-engineering.connpass.com/event/136756/) でのLT資料です。

5分間の LT です。
データ活用の際にハマってしまって時間を溶かしたツラミ（自業自得）を共有します。誰かがログの設計やデータ活用の際に救われたら嬉しいです。

Transcript

1. ©2019 Wantedly, Inc.
   σʔλ׆༻ͷࡍʹϋϚͬͯ͠·ͬͯ
   ਏ͔ͬͨ͜ͱ
   σʔλͱMLपลΤϯδχΞϦϯάΛߟ͑Δձ #2
   19.July.2019 - Yuya Matsumura @yu-ya4
   

   View Slide

2. ©2019 Wantedly, Inc.
   ✓ Yuya Matsumura (দଜ ༏໵)
   ✓ Software Engineer (ݕࡧɾਪનΤϯδχΞ)
   ✓ Wantedly, Inc. Recommendation Team (since April 2018)
   ✓ Interested in Information Retrieval, Machine Learning
   Self-Introduction
   @yu-ya4
   @yu__ya4
   

   View Slide

3. ©2018 Wantedly, Inc.
   

   View Slide

4. ©2019 Wantedly, Inc.
   Wantedly ʹ͓͚Δੵۃతͳσʔλ׆༻
   㾎੔උ͞Εͨσʔλج൫
   w ͋ΒΏΔϩάσʔλ͸#JH2VFSZʹอଘ
   w ओཁͳػೳ͝ͱͷϩάʢMPHVTFS@TFBSDI@MPH::::..%%ʣ
   w EBJMZͰͷ3%#ͷεφοϓγϣοτʢSECVTFST::::..%%ʣ
   w EBJMZͰͷਪન݁ՌʢSFDPNNOFESBOLJOH::::..%%ʣ
   w ෼ੳ͠΍͍͢Α͏ʹɼલॲཧΛߦ͍ϩάσʔλΛ੔ܗͨ͠தؒςʔϒϧͷ
   ࡞੒ɾڞ༗
   

   View Slide

5. ©2019 Wantedly, Inc.
   Wantedly ʹ͓͚Δੵۃతͳσʔλ׆༻
   㾎σʔλ׆༻ͷຽओԽ
   w ϏδωεαΠυ΋ؚΊɼࣾһશһ͕#JH2VFSZͰσʔλʹΞΫηεͰ͖Δ؀ڥ
   w #JH2VFSZͷ࢖͍ํͱυϝΠϯ஌ࣝ֫ಘ͢ΔͨΊͷ৽ਓݚमͷ࣮ࢪ
   w ΞϓϦέʔγϣϯΤϯδχΞ͕ࣗ෼ͨͪͰσʔλ෼ੳΛߦͬͯ՝୊Λ೺Ѳͯ͠
   ࢪࡦΛ࣮ࢪ͠ɼͦͷ݁ՌͷධՁ·ͰΛߦ͏ମ੍
   IUUQTTQFBLFSEFDLDPNZVZBXBOUFEMZOFXDPNFSUSBJOJOHCJHRVFSZUSBJOJOH
   

   View Slide

6. ©2019 Wantedly, Inc.
   σʔλ׆༻ͷࡍʹϋϚͬͯ͠·ͬͯ
   ਏ͔ͬͨࣄྫΛ͍͔ͭ͘͝঺հ(´ʀТʀʆ)
   

   View Slide

7. ©2019 Wantedly, Inc.
   ਏ͔ͬͨ͜ͱᶃ
   ೔෇Λද͢ςʔϒϧsuffix
   ᶃ log.user_search_log20190718
   → 2019೥7݄18೔ͷ1೔෼ͷϩά͕ೖͬͨςʔϒϧ
   ᶄ rdb.users20190718
   →
   ᶅ recommend.ranking20190718
   →ɹ
   

   View Slide

8. ©2019 Wantedly, Inc.
   ਏ͔ͬͨ͜ͱᶃ
   ೔෇Λද͢ςʔϒϧsuffix
   ᶃ log.user_search_log20190718
   → 2019೥7݄18೔ͷ1೔෼ͷϩά͕ೖͬͨςʔϒϧ
   ᶄ rdb.users20190718
   → 2019೥7݄18೔24:00࣌఺ͰͷRDBͷεφοϓγϣοτ
   ᶅ recommend.ranking20190718
   → 2019೥7݄18೔ʹ༻͍ͨϥϯΩϯά
   

   View Slide

9. ©2019 Wantedly, Inc.
   ਏ͔ͬͨ͜ͱᶃ
   ҙຯͷҟͳΔ೔෇Λද͢ςʔϒϧsuffix
   ᶃ log.user_search_log20190718
   → 2019೥7݄18೔ͷ1೔෼ͷϩά͕ೖͬͨςʔϒϧ
   ᶄ rdb.users20190718
   → 2019೥7݄18೔24:00࣌఺ͰͷRDBͷεφοϓγϣοτ
   → 2019೥7݄18೔ʹ࡞੒͞Εͨ2019೥7݄17೔24:00࣌఺ͰͷRDBͷεφοϓγϣοτ
   ᶅ recommend.ranking20190718
   → 2019೥7݄18೔ʹ༻͍ͨϥϯΩϯά
   → 2019೥7݄18೔·ͰͷσʔλΛݩʹ࡞੒͞Εͨ2019೥7݄19೔ʹ༻͍ͨϥϯΩϯά
   

   View Slide

10. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶃ
    ҙຯͷҟͳΔ೔෇Λද͢ςʔϒϧsuffix
    ᶃ log.user_search_log20190718
    → 2019೥7݄18೔ͷ1೔෼ͷϩά͕ೖͬͨςʔϒϧ
    ᶄ rdb.users20190718
    → 2019೥7݄18೔24:00࣌఺ͰͷRDBͷεφοϓγϣοτ
    → 2019೥7݄18೔ʹ࡞੒͞Εͨ2019೥7݄17೔24:00࣌఺ͰͷRDBͷεφοϓγϣοτ
    ᶅ recommend.ranking20190718
    → 2019೥7݄18೔ʹ༻͍ͨϥϯΩϯά
    → 2019೥7݄18೔·ͰͷσʔλΛݩʹ࡞੒͞Εͨ2019೥7݄19೔ʹ༻͍ͨϥϯΩϯά
    ←͔͠΋΋ͷʹΑͬͯ͸
    ɹ2019೥7݄18೔ʹ༻͍ͨϥϯΩϯάΛද͢
    ʢೖࣾؒ΋ͳ͍΅͕͘࡞ͬͨɻɻɻʣ
    

    View Slide

11. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶃ
    ҙຯͷҟͳΔ೔෇Λද͢ςʔϒϧsuffix
    ᶃ log.user_search_log20190718
    → 2019೥7݄18೔ͷ1೔෼ͷϩά͕ೖͬͨςʔϒϧ
    ᶄ rdb.users20190718
    → 2019೥7݄18೔24:00࣌఺ͰͷRDBͷεφοϓγϣοτ
    → 2019೥7݄18೔ʹ࡞੒͞Εͨ2019೥7݄17೔24:00࣌఺ͰͷRDBͷεφοϓγϣοτ
    ᶅ recommend.ranking20190718
    → 2019೥7݄18೔ʹ༻͍ͨϥϯΩϯά
    → 2019೥7݄18೔·ͰͷσʔλΛݩʹ࡞੒͞Εͨ2019೥7݄19೔ʹ༻͍ͨϥϯΩϯά
    ͜ͷ͋ͱ໓஡ۤ஡υΩϡϝϯτ࡞ͬͨɻ
    

    View Slide

12. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶄ
    json ܗࣜͰهड़͞Εͨ STRING ͷΧϥϜ
    ϦΫΤετύϥϝλΈ͍ͨͳ΋ͷΛ json ܗࣜͷ STRING Ͱอଘ͍ͯ͠Δ
    ύϥϝλ͕૿͑ͨΓݮͬͨΓ͢Δ౎౓ɼςʔϒϧͷ Schema Λมߋ͢Δ
    ඞཁ͕ͳ͍ͷͰϕϯϦ
    →
    

    View Slide

13. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶄ
    json ܗࣜͰهड़͞Εͨ STRING ͷΧϥϜ
    • ಛఆͷ filter ͕࢖ΘΕͨͱ͖ͷ log ͚ͩूΊͯ෼ੳ͍ͨ͠ʂ
    • ͍ΖΜͳάϥϑΛ࡞ͬͯ BI πʔϧʹొ࿥ͩʂ
    • ͜ͷσʔλΛ࢖ͬͯ model Λ࡞੒ʂ
    

    View Slide

14. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶄ
    json ܗࣜͰهड़͞Εͨ STRING ͷΧϥϜʹ຋࿔͞ΕΔDS
    • ಛఆͷ filter ͕࢖ΘΕͨͱ͖ͷ log ͚ͩूΊͯ෼ੳ͍ͨ͠ʂ
    • ͍ΖΜͳάϥϑΛ࡞ͬͯ BI πʔϧʹొ࿥ͩʂ
    • ͜ͷσʔλΛ࢖ͬͯ model Λ࡞੒ʂ
    ύϥϝλ͕૿͑ͨΓݮͬͨΓ͢Δ౎౓ɼ෼ੳ༻ͷΫΤϦ΋BIπʔϧͷ
    άϥϑ΋ model ΋࢒೦ͳ͜ͱʹ…
    →
    

    View Slide

15. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶅ null
    

    View Slide

16. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶅ "null"
    

    View Slide

17. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶅ
    json ܗࣜͷ STRING ͷΧϥϜΛॲཧͯ͠෼ੳ༻ͷதؒςʔϒϧΛ࡞੒ͨ͠ࡍ
    ʹ͜͏ͳͬͯ͠·͍ͬͯͨɻ
    "null"
    

    View Slide

18. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶅ
    null Λআ͍ͨ ARRAY Λ࡞͍ͬͯΖ͍Ζॲཧͨ͠ޙʹɼͦͷ ARRAY ͷཁૉ
    ͷ਺Λ਺͑ΔʢARRAY_LENGTH() ʣͬͯॲཧΛͨ͠ͱ͖ʹ௧͍໨ʹ…
    "null"
    

    View Slide

19. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶅ
    json ܗࣜͰ݁ՌΛݟΕ͹ؾ͚ͮͨɻʢແཧͰ͸ʁʣ
    "null"
    

    View Slide

20. ©2019 Wantedly, Inc.
    ਏ͔ͬͨ͜ͱᶅ
    ৽UIͰ͸վળ͞Εͨ
    "null"
    

    View Slide

21. ©2019 Wantedly, Inc.
    ͓ΘΓ
    㾎ਏ͔ͬͨ͜ͱʢࣗۀࣗಘʣ
    w ҙຯͷҟͳΔ೔෇Λද͢ςʔϒϧTV⒏Y
    w KTPOܗࣜͰهड़͞Εͨ453*/(ͷΧϥϜʹ຋࿔͞ΕΔ%4
    w OVMM
    

    View Slide