ピクシブ流データ活用基盤のこれまでとこれから #pixivTECHSALON / 20190305

pixiv TECH SALON https://techsalon.pixiv.co.jp/
メインセッションで使用した発表資料です。

Transcript

1. ϐΫγϒྲྀσʔλ׆༻ج൫ͷ
   ͜Ε·Ͱͱ͜Ε͔Β
   
   pixiv.inc
   minamitary
   

   View Slide

2. σʔλ׆༻ج൫
   ࠓ೔࿩͢͜ͱ
   

   View Slide

3. @minamitary / ৿ా༸հ
   2015೥த్ೖࣾ
   pixivӡӦຊ෦ + σʔλۦಈਪਐࣨ
   ΤϯδχΞ + UX Ϧαʔνϟʔ
   ྖҬԣஅతͳಈ͖ํΛ͢Δ͜ͱ͕ଟ͍
   ࣗݾ঺հ
   

   View Slide

4. • 2015೥ɿೖࣾޙʮsenseiʯͷ্ཱͪ͛ʹϦʔυΤϯδχΞͱͯ͠ࢀՃ
   • 2016೥ɿsenseiͷϦϦʔεޙάϩʔεΛ୲౰ɺͦͷޙʮpixivʯ΁
   • Ҏ߱ΫϦΤΠλʔΛத৺ͱͨ͠UXϦαʔνΛओʹ୲౰
   • ఆྔతϦαʔνͰBigQueryΛୟ͖࢝Ίͯ໿̏೥
   ࣗݾ঺հ
   ʴ
   

   View Slide

5. ͓ֆ͔͖ྺ̏೥
   

   View Slide

6. σʔλ׆༻ج൫
   ࠓ೔࿩͢͜ͱ
   

   View Slide

7. ϐΫγϒʹ͓͚Δσʔλ׆༻ج൫
   ≒ BigQuery
   

   View Slide

8. • BigQueryʹσʔλΛूΊ͚ͨͩͰ͸μϝ
   • ࢖ΘΕͳ͚Ε͹σʔλʮ׆༻ʯج൫Ͱ͸ͳ͍
   ϐΫγϒʹ͓͚Δσʔλ׆༻ج൫
   

   View Slide

9. ʮ׆༻ʯʹͲ͏ཱͪ޲͔͏͔
   

   View Slide

10. • ʮ׆༻ʯΛ८ͬͯɺ̏೥ͷؒʹ৭ʑͳมԽ͕͋ͬͨ
    • ٕज़తͳมԽ͚ͩͰͳ͘ɺ૊৫తͳมԽ΋େ͖͔ͬͨ
    ϐΫγϒʹ͓͚Δσʔλ׆༻ج൫
    

    View Slide

11. • ͜ͷ̏೥Ͱσʔλ׆༻ج൫ʹੜͨ͡มԽ
    ‣ ٕज़తͳมԽ
    ‣ ૊৫తͳมԽ
    • ϐΫγϒʹ͓͚Δσʔλ׆༻ج൫ͷ͜Ε͔Β
    ࠓ೔࿩͢͜ͱ
    

    View Slide

12. ʮΦʔφʔγοϓʯ
    

    View Slide

13. Φʔφʔγοϓʁ
    ୲౰͢ΔϓϩμΫτʹର͢ΔΦʔφʔγοϓ
    

    View Slide

14. Φʔφʔγοϓʁ
    ʮԶ͕͜ͷϓϩμΫτΛੈքҰʹ͢ΔΜ΍ʂʯ
    

    View Slide

15. • શ৬छ͕ϢʔβʔΛத৺ʹΈͯಈ͘
    • Ϣʔβʔ࠷༏ઌ = ৬छؒͷ͕֞ࠜ௿͍
    • ϓϩμΫτѪɾΧϧνϟʔѪ͕ڧ͍ϝϯόʔ͕ଟ͍
    લఏɿϐΫγϒͷاۀจԽ
    

    View Slide

16. ʮΦʔφʔγοϓ͕ڧ͍ʯ
    ͱ͍͏ͷ͕ϐΫγϒʹ͓͚Δେલఏ
    

    View Slide

17. υϝΠϯཧղ͕σʔλ׆༻ͷਫ਼౓ΛߴΊΔ
    υϝΠϯཧղͷਂ͍ਓ͕σʔλΛ׆༻͍ͯ͘͠΂͖
    
    
    Φʔφʔγοϓ͸ͳͥॏཁʁ
    

    View Slide

18. Φʔφʔγοϓ͕υϝΠϯཧղΛਂΊΔ
    ͭ·ΓΦʔφʔγοϓ͕σʔλ׆༻ͷਫ਼౓ΛߴΊΔ
    Φʔφʔγοϓͷڧ͍։ൃऀ͕σʔλΛ׆༻͍ͯ͘͠΂͖
    
    
    Φʔφʔγοϓ͸ͳͥॏཁʁ
    

    View Slide

19. σʔλ׆༻͸
    Φʔφʔγοϓͱηοτ
    جຊݪଇɿ
    

    View Slide

20. ΋͏ҰͭͷϐΫγϒͷಛ௃
    

    View Slide

21. ଟछଟ༷ͳϓϩμΫτ
    

    View Slide

22. • υϝΠϯྖҬ͸͢΂ͯʮ૑࡞׆ಈʯ
    • αʔϏεܗଶ͸ҟͳΓͭͭ΋ɺϢʔβʔ૚͸ಉ͡
    • Ϣʔβʔ୯ҐͰͷ݁ͼ͚͕ͭେࣄʂ
    ϐΫγϒͷ๊͑ΔϓϩμΫτ
    

    View Slide

23. • ʮσʔλ׆༻͸Φʔφγοϓͱηοτʯ͸ҡ࣋
    • σʔλ׆༻ͷʮڮ౉͠ʯ΋ඞཁ
    ͦΕΏ͑ʹ……
    

    View Slide

24. ʮΦʔφʔγοϓʯ
    ʮڮ౉͠ʯ
    ೋͭͷϙΠϯτɿ
    

    View Slide

25. 1. ͜ͷ3೥Ͱੜͨ͡มԽ ʙٕज़ฤʙ
    2. ͜ͷ3೥Ͱੜͨ͡มԽ ʙ૊৫ฤʙ
    3. ͜Ε͔Βͷ࿩
    

    View Slide

26. ࣌͸ḪΔ͜ͱ3೥……
    

    View Slide

27. • σʔλ͸طʹBigQueryʹ৐ͬͯͨ
    • ར༻։࢝͸2014೥ʂ
    ٕज़ฤ -3೥લ-
    

    View Slide

28. • DBϨϓϦΧΛ౎౓ୟ͘
    • Google AnalyticsͷAPIΛୟ͍ͯूܭ
    • ൿ఻ͷεϓϨουγʔτͷཚཱ
    • σʔλ΋෦෼తʹ্͔͕͍ͬͯ͠ͳ͍ʂ
    ʮ׆༻ʯ͸ݶఆత……
    

    View Slide

29. 3೥ޙ
    

    View Slide

30. σʔλ͸BigQueryʹू໿
    JOIN͠์୊ʂ
    

    View Slide

31. • ൿ఻ͷεϓϨουγʔτɿ΄΅ফ໓ʂ
    • ൿ఻ͷूܭεΫϦϓτɿ΄΅ফ໓ʂ
    • ؅ཧը໘্ʹ఺ࡏ͍ͯͨ͠άϥϑ܈ɿ΄΅ফ໓ʂ
    • શͯͷσʔλॲཧ͕SQLͰهड़͞ΕΔੈքʹ
    ෼ੳखஈ΋BigQueryʹ౷Ұ
    

    View Slide

32. • WITH۟΍ϏϡʔͰલॲཧΛ؆ུԽ
    • ࢖͍ճ͠΍͍͢WITH۟Λࢿ࢈ͱͯ͠อଘɾγΣΞ
    • SQLͷڞ௨ࢿ࢈Խʂ
    શ͕ͯSQLͰهड़͞ΕΔੈք
    

    View Slide

33. • SQL֮͑͑͞Ε͹σʔλΛ׆༻Ͱ͖Δੈք
    • BigQuery্Ͱσʔλॲཧ͕׬݁
    • ϚγϯϦιʔεΛҙࣝ͢Δඞཁ͕ͳ͍
    શ͕ͯSQLͰهड़͞ΕΔੈք
    

    View Slide

34. • ʮSQLศརʯʮSQLΠέͯΔʯ
    • ݁ՌϏδωεଆͷਓؒʹ΋SQLར༻͕֦େ
    • ΦʔφʔγοϓͷԸܙʂ
    શ͕ͯSQLͰهड़͞ΕΔੈք
    

    View Slide

35. ʮڮ౉͠ʯ͠΍͍͢؀ڥ͕Ͱ͖ͨʂ
    

    View Slide

36. • ͢΂ͯͷσʔλ͕BigQueryʹू໿͞Εͨ
    • ෼ੳखஈ͕SQLʹ౷Ұ͞Εͨ
    • ʮڮ౉͠ʯ͕΍Γ΍͘͢ͳͬͨ
    ٕज़తͳมԽ·ͱΊ
    

    View Slide

37. 1. ͜ͷ3೥Ͱੜͨ͡มԽ ʙٕज़ฤʙ
    2. ͜ͷ3೥Ͱੜͨ͡มԽ ʙ૊৫ฤʙ
    3. ͜Ε͔Βͷ࿩
    

    View Slide

38. ࠶ͼḪΔ͜ͱ3೥……
    

    View Slide

39. • ֤෦ॺ͕ͦΕͧΕͰؤுΔ
    • Ϗδωεܥͷ෦ॺΛத৺ʹσʔλ͕׆༻͞Εͯ͸͍ͨ
    • μογϡϘʔυɺ؅ཧը໘ɺεϓϨουγʔτͷཚཱ
    ૊৫ฤ -3೥લ-
    

    View Slide

40. എܠɿ૿͑Ώ͘ϓϩμΫτ
    9ݸ
    2016/01
    20ݸ
    2019/01
    ͜ͷ3೥ؒͰϓϩμΫτͷ਺͸2ഒҎ্ʹ
    

    View Slide

41. • ͦΕʹԠͯ͡νʔϜͷ਺΋૿Ճ
    • νʔϜʹσʔλಘҙϚϯ͕͍ͳ͍
    • ยखؒσʔλΤϯδχΞϦϯά
    എܠɿ૿͑Ώ͘ϓϩμΫτ
    

    View Slide

42. ෼ੳઐ໳ͷ෦ॺΛ࡞Δ΂͖ʁ
    

    View Slide

43. • جຊݪଇʮσʔλ׆༻͸ΦʔφʔγοϓͱηοτͰʯ
    • ʮσʔλ͸͋ͷ෦ॺͷਓ͕ݟͯ͘ΕΔʯͱ͍͏ҙࣝ͸
    ආ͚͍ͨ
    ౴͑͸NOʂ
    

    View Slide

44. ෼ੳ୲౰ऀ͸֤෦ॺ಺ʹ͍Δ΂͖
    ͱ͸͍͑ԣͷܨ͕Γ͸ඞཁ……
    

    View Slide

45. ରࡦ
    

    View Slide

46. ରࡦ
    SlackνϟϯωϧΛ࡞͚ͬͨͩ
    

    View Slide

47. • ࣾ಺ษڧձ༻ͷSlackνϟϯωϧΛ࡞Δ
    • ษڧձऴྃޙɺओ࠵ϝϯόʔ͕݁ୗ
    • ʮσʔλʹڵຯ͋Δਓ͸͜͜ʹདྷͯ͘ΕʂʯΞϐʔϧ
    ܦҢ
    

    View Slide

48. • νϟϯωϧʹਓ͕ू·Γ͸͡ΊΔ
    • ଟ਺ͷ૬ஊ͕دͤΒΕΔ
    • ଞ෦ॺͷৄ͍͠ਓ͕ͦΕʹԠ͡ΔจԽ
    • ʮ͜͜ʹߦ͚͹ղܾ͢Δʯ͞Βʹਓ͕૿͑Δʂ
    ܦҢ
    

    View Slide

49. • ि࣍Ͱ෼ੳ୲౰͕ू·ΔΑ͏ʹ
    ‣ ৽نσʔληοτ௥Ճͷ࿈བྷ
    ‣ ৽͍͠μογϡϘʔυͷڞ༗
    ‣ BigQueryͷ৽ػೳ঺հ
    ‣ ௕ظతͳํ਑ܾΊ
    ‣ ͳͲͳͲ
    
    
    ܦҢ
    

    View Slide

50. ઐ໳෦ॺۦಈͰ͸ͳ͍
    ࣗൃతͳʮ׆༻ʯج൫ͷվળ
    

    View Slide

51. 
    
    Why?
    

    View Slide

52. • ʮΦʔφʔγοϓʯͱ͍͏ϐΫγϒͷاۀจԽ͋Γ͖
    • ʮϓϩμΫτΛ੒௕ͤ͞Δ্Ͱɺඞཁ͔ͩΒ΍Δʯ
    Why?
    

    View Slide

53. • Φʔφʔγοϓ + ϕετϓϥΫςΟεͷಉظ
    • ʮ͜Ε͕ϕετϓϥΫςΟεʯ͕ಉظ͞ΕΔ͜ͱͰ……
    →ʮͪΐͬͱख͕ۭ͖ؾຯͳͷͰΨοͱվળ͓͖ͯ͠·͢ʯ
    →ʮߋʹϥΫʹͰ͖Δํ๏Λࢥ͍͖ͭ·ͨ͠ʯ
    →ʮ͜ͷBigQueryͷ৽ػೳ͸ศརͦ͏ͳͷͰ࢖ͬͯΈ·͠ΐ͏ʯ
    
    
    Why?
    

    View Slide

54. • ʮΦʔφʔγοϓʯͷൃشઌΛಉظ͢Δඞཁੑ
    • ͦͷͨΊͷɺSlack౳ʹ͓͚Δʮڮ౉͠ʯͷઃܭ
    
    
    Why?
    

    View Slide

55. • Slackνϟϯωϧ࡞੒͕มԽͷ͖͔͚ͬ
    • νϟϯωϧʹਓ͕ू·Γɺԣͷ࿈ܞ͕ڧԽ
    • ʮڮ౉͠ʯʹΑΔϕετϓϥΫςΟεͷಉظ
    • ʮΦʔφʔγοϓʯϕʔεͰԣஅతͳج൫վળ͕Մೳʹʂ
    
    
    ૊৫తͳมԽ·ͱΊ
    

    View Slide

56. 1. ͜ͷ3೥Ͱੜͨ͡มԽ ʙٕज़ฤʙ
    2. ͜ͷ3೥Ͱੜͨ͡มԽ ʙ૊৫ฤʙ
    3. ͜Ε͔Βͷ࿩
    

    View Slide

57. ͦͷલʹ
    

    View Slide

58. • ٕज़తͳมԽ͚ͩΛਐΊ͍ͯͨΒ……ʁ
    • ૊৫తͳมԽ͚ͩΛਐΊ͍ͯͨΒ……ʁ
    • ͲͪΒ΋ʮ׆༻ʯ͸੒Γཱͨͳ͍ʂ
    ৼΓฦͬͯΈΔͱ……
    

    View Slide

59. 
    
    ৼΓฦͬͯΈΔͱ…
    ٕज़తมԽ
    ૊৫తมԽ
    

    View Slide

60. σʔλ׆༻͸
    Φʔφʔγοϓͱηοτ
    جຊݪଇɿ
    

    View Slide

61. ٕज़తมԽ͸
    ૊৫తมԽͱηοτ
    ΋͏Ұͭͷجຊݪଇɿ
    

    View Slide

62. 1. ͜ͷ3೥Ͱੜͨ͡มԽ ʙٕज़ฤʙ
    2. ͜ͷ3೥Ͱੜͨ͡มԽ ʙ૊৫ฤʙ
    3. ͜Ε͔Βͷ࿩
    

    View Slide

63. ࡢ೥຤
    CTO
    ෦ॺ࡞ͬͪΌ͓ʂ
    

    View Slide

64. ࡢ೥຤
    ʮσʔλۦಈਪਐࣨʯͷ஀ੜ
    

    View Slide

65. • جຊݪଇʮσʔλ׆༻͸ΦʔφʔγοϓͱηοτͰʯ
    • ෼ੳͷ࣮຿͸୲౰͠ͳ͍ʂ
    • ֤෦ॺͷσʔλ׆༻Λαϙʔτɾਪਐ͢Δ໾ׂ
    • Ϗδωε໘ɾUX໘ɾٕज़໘͢΂͕ͯର৅ʂ
    σʔλۦಈʮਪਐʯࣨ
    

    View Slide

66. View Slide

67. ΑΓΞΫϩόςΟοΫʹ
    ʮಈ͚Δʯ
    Α͏ʹ෦ॺԽ
    

    View Slide

68. ۩ମతͳΞϓϩʔν
    https://inside.pixiv.blog/jaggy/6421
    ϢʔβʔʹՁ஋Λ࠷଎Ͱಧ͚ΔͨΊʹɻ
    ϐΫγϒͷʮσʔλຽओԽʯʹ޲͚ͨ௅ઓ
    

    View Slide

69. • ٕज़తͳτϐοΫ΋ͨ͘͞Μ
    ‣ BigQuery্Ͱେن໛ͳσʔλΛѻ͏ࡍͷϊ΢ϋ΢
    ‣ BigQueryपลͰར༻͍ͯ͠Δπʔϧ܈ɾ۩ମతͳ࣮૷಺༰
    ‣ ϐΫγϒͳΒͰ͸ͷσʔλ׆༻ࣄྫ͋Ε͜Ε
    ‣ ͳͲͳͲ……
    • ৄ͘͠ฉ͖͍ͨํ͸࠙਌ձͰʂ
    
    
    ۩ମతͳΞϓϩʔν
    

    View Slide

70. • σʔλ׆༻͸Φʔφʔγοϓͱηοτ
    • ٕज़తมԽ͸૊৫తมԽͱηοτ
    • σʔλۦಈਪਐ͕ࣨͰ͖ͨ
    ·ͱΊ
    

    View Slide

71. ࣾһ΋ϓϩμΫτ΋૿͑ଓ͚Δ͚Ͳɺ
    ʮ૑࡞׆ಈ͕΋ͬͱָ͘͠ͳΔ৔ॴΛ૑Δʯؾ࣋ͪ͸มΘΒͳ͍ɻ
    ϐΫγϒ͸σʔλͷྗͰ૑࡞׆ಈΛࢧ͍͑ͯ͘ɻ
    

    View Slide