画像自動保存・閲覧システム「救い」によってもたらされる効能; 死にゆくTwitter Streaming APIへの別れの言葉を添えて / Sukui as a Service

KMC春合宿講座2018

Transcript

1. ը૾ࣗಈอଘɾӾཡγεςϜ

   ʮٹ͍ʯʹΑͬͯ΋ͨΒ͞ΕΔޮೳ
   ࢮʹΏ͘Twitter Streaming API΁ͷผΕͷݴ༿Λఴ͑ͯ
   य़߹॓ߨ࠲2018 @utgwkk
   

   View Slide

2. View Slide

3. ٹΘΕͯ
   ͍·͔͢
   

   View Slide

4. ٹ͍
   

   View Slide

5. IUUQTUXJUUFSDPNNPLBYTUBUVT
   

   View Slide

6. ͍͍Ͷ❤
   γΣΞ
   ը૾Λอଘ
   

   View Slide

7. อଘํ๏ˠόϥόϥ
   อଘ৔ॴˠόϥόϥ
   γΣΞˠखͰ΍Δʁ
   

   View Slide

8. View Slide

9. ͍͍Ͷ❤
   

   View Slide

10. $POP)B714
    ͍͍Ͷ❤
    ͍͍Ͷ❤
    ͞ΕͨͰ
    γΣΞ
    ը૾Λอଘ
    ϝλσʔλΛ
    อଘ
    ༏উ
    

    View Slide

11. JNBHFT
    JNBHFTEBZ
    JNBHFTNPOUI
    

    View Slide

12. 4USFBNJOH"1*
    

    View Slide

13. πΠʔτ
    ͍͍Ͷͨ͠͞Εͨ
    ϑΥϩʔͨ͠͞Εͨ
    ͳͲͳͲ
    

    View Slide

14. *ODPNJOH8FCIPPL
    

    View Slide

15. import json
    import requests
    payload = {
    "username": "hogefuga",
    "text": "yo"
    }
    requests.post(
    "https://hooks.slack.com/services/hoge/fuga/waowaowao",
    data=json.dumps(payload)
    )
    

    View Slide

16. 1PTUPOMZ
    /PDPNQMJDBUFEDPEF
    +VTU1045QBZMPBE
    

    View Slide

17. View Slide

18. images
    id
    filename
    created_at
    image_info
    id
    image_id
    description
    source
    created_at
    

    View Slide

19. ը૾ͷอଘ͚ͩͰ͸μϝ
    ݕࡧ͕͍ͨ͠ʂʂʂ
    

    View Slide

20. View Slide

21. IUUQTKQWVFKTPSHJOEFYIUNM
    

    View Slide

22. ૉ๿ͳϑϩϯτΤϯυ
    ੈք؍
    TDSJQUTSDKTWVFNJOKTTDSJQU
    

    View Slide

23. +40/ฦ͢"1*αʔό
    ͳͥ͜ͷઃܭʹ͔ͨ͠
    ͸๨Εͨ
    

    View Slide

24. ʮٹ͍ʯΛࢧ͑Δ42-
    

    View Slide

25. ϖʔδωʔγϣϯ
    

    View Slide

26. ૉ๿ͳϖʔδωʔγϣϯ
    

    View Slide

27. select *
    from image_info
    order by id desc
    limit 200 offset page*200
    

    View Slide

28. 0''4&5-*.*5͸
    ஗͍
    

    View Slide

29. select *
    from image_info
    order by id desc
    limit 2
    offset 500000000
    

    View Slide

30. σʔλ1
    σʔλ2
    σʔλ500000001
    ʜʜ
    σʔλ500000002
    

    View Slide

31. σʔλ1
    σʔλ2
    σʔλ500000001
    ʜʜ
    σʔλ500000002
    

    View Slide

32. σʔλ1
    σʔλ2
    σʔλ500000001
    ʜʜ
    σʔλ500000002
    

    View Slide

33. σʔλ1
    σʔλ2
    σʔλ500000001
    ʜʜ
    σʔλ500000002
    ສ೥ޙ
    

    View Slide

34. σʔλ1
    σʔλ2
    σʔλ500000001
    ʜʜ
    σʔλ500000002
    

    View Slide

35. σʔλ1
    σʔλ2
    σʔλ500000001
    ʜʜ
    σʔλ500000002
    

    View Slide

36. select *
    from image_info
    where (something)
    order by id desc
    limit 200
    offset 500000000
    

    View Slide

37. select *
    from image_info
    where (something)
    order by id desc
    limit 200
    offset 500000000
    ൪໨Ͳ͜ʁ
    

    View Slide

38. TJODF@JENBY@JE
    ྫ5XJUUFS3&45"1*
    
    

    View Slide

39. ϖʔδ਺ࢦఆΛఘΊΔ
    ߴ଎ͳOFYUQSFWऔಘ
    JE͕཭ࢄతͰ΋࢖͑Δ
    

    View Slide

40. select *
    from image_info
    order by id desc
    limit k
    

    View Slide

41. M
    N+1
    N
    M+1
    N-1
    ʜʜ
    ʜ ʜ
    QSFW
    OFYU
    PSEFSCZJEEFTD
    

    View Slide

42. select *
    from image_info
    where id < N
    order by id desc
    limit k
    

    View Slide

43. M+1
    M
    N-1
    M-1
    ʜʜ
    ʜ
    N
    PSEFSCZJEEFTD
    ʜ
    

    View Slide

44. select *
    from image_info
    where id > M
    order by id desc
    limit k
    

    View Slide

45. ΊͰͨ͠
    

    View Slide

46. ΊͰͨ͠Ͱ͸ͳ͍
    

    View Slide

47. select *
    from image_info
    where id > M
    order by id desc
    limit k
    

    View Slide

48. max-1
    max-k
    max
    M+1
    ʜʜ
    ʜ
    PSEFSCZJEEFTD
    

    View Slide

49. select *
    from image_info
    where id > M
    order by id asc
    limit k
    

    View Slide

50. M+2
    L
    M+1
    L+1
    ʜʜ
    ʜ
    M
    PSEFSCZJEBTD
    ʜ
    

    View Slide

51. select * from (
    select *
    from image_info
    where id > M
    order by id asc
    limit k
    ) as t
    order by id desc
    

    View Slide

52. M+2
    M+1
    L
    M
    ʜʜ
    ʜ
    L+1
    PSEFSCZJEEFTD
    ʜ
    

    View Slide

53. ΊͰͨ͠ ຊ౰ʹ
    
    

    View Slide

54. ແݶͷϖʔδωʔγϣϯ
    Λఏڙ͠ͳ͍ͱ͍͏ख
    (PPHMF 5XJUUFS
    
    

    View Slide

55. શจݕࡧ
    
    

    View Slide

56. select *
    from image_info
    where description like
    '%༫ਫ޾ࢠ%'
    

    View Slide

57. -*,&શจݕࡧ͸஗͍
    શEFTDSJQUJPOʹର͢Δ
    ਖ਼نදݱϚον
    
    

    View Slide

58. ΠϯσοΫε͕ޮ͔ͳ͍
    ˠਓݖ͕ͳ͍
    

    View Slide

59. 6TFUIF*OEFY -VLF
    ΛಡΈ·͠ΐ͏
    IUUQTVTFUIFJOEFYMVLFDPNKB
    

    View Slide

60. IUUQJTVDPOOFU
    

    View Slide

61. ΠϯσοΫε͕ޮ͔ͳ͍ͳΒ
    ΠϯσοΫε͕ޮ͘Α͏ʹ͢
    Ε͹͍͍͡Όͳ͍
    

    View Slide

62. ✨OHSBNJOEFY✨
    ྫO
    
    

    View Slide

63. จষ
    ˣ
    จࣈͷจࣈྻू߹
    

    View Slide

64. จࣈͷจࣈྻ
    ˣ
    จষͷू߹
    

    View Slide

65. ༫ਫ޾ࢠ
    

    View Slide

66. ༫ਫਫ޾޾ࢠ
    

    View Slide

67. ༫ਫ ༫ਫ޾ࢠ ……
    ਫ޾ ༫ਫ޾ࢠ ……
    ޾ࢠ ༫ਫ޾ࢠ ……
    …… ……
    

    View Slide

68. ༫ਫ
    ਫ޾
    ޾ࢠ
    N-gram
    index
    ༫ਫ޾ࢠ
    

    View Slide

69. CREATE TABLE `image_info` (
    `id` int(32) NOT NULL AUTO_INCREMENT,
    `image_id` int(32) DEFAULT NULL,
    `description` varchar(2048) DEFAULT NULL,
    `source` varchar(512) DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT
    CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `image_id` (`image_id`),
    KEY `source` (`source`) USING HASH,
    FULLTEXT KEY `description` (`description`) /*!
    50100 WITH PARSER `ngram` */ ,
    CONSTRAINT `image_info_ibfk_1` FOREIGN KEY
    (`image_id`) REFERENCES `images` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=121018 DEFAULT
    CHARSET=utf8mb4
    

    View Slide

70. select *
    from image_info
    where match (description) against
    ('༫ਫ޾ࢠ' in natural language mode)
    

    View Slide

71. ΊͰͨ͠
    

    View Slide

72. ΊͰͨ͠Ͱ͸ͳ͍
    

    View Slide

73. ྫΈΓ͋
    

    View Slide

74. ΈΓΓ͋
    

    View Slide

75. ʮΈΓʯʮΓ͋ʯ
    ΛؚΉจষ͍͘ΒͰ΋
    ͋Γͦ͏
    ʮΈΓ͋ʯ͕ඞཁ
    

    View Slide

76. ͦ͜ʹ-*,&
    

    View Slide

77. ΈΓ
    Γ͋
    N-gram
    index
    LIKE ΈΓ͋
    

    View Slide

78. select *
    from image_info
    where match (description) against
    ('ΈΓ͋' in natural language mode)

    and description like '%ΈΓ͋%'
    

    View Slide

79. ΊͰͨ͠ ຊ౰ʹ
    
    ͜ͷํ๏͕٢ͱग़Δ͔ڟͱग़Δ͔ʹ͸ॾઆ͋Δ
    

    View Slide

80. "/%03&9ݕࡧ
    

    View Slide

81. χʔζ͍Ζ͍Ζ
    ΈΓ͋ਔಸ
    ৿ٱอ03೫ʑ
    ΈΓ͋ΕΈΓ͋
    

    View Slide

82. ҰํQJYJW
    

    View Slide

83. ΍Δͧʙʙʙ
    

    View Slide

84. View Slide

85. શؚͯΉ ΈΓ͋
    ͍ͣΕؚ͔Ή ʹͳ ͪ͑
    ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
    

    View Slide

86. select *
    from image_info
    where (FILL IN HERE)
    order by created_at desc
    limit 200
    શؚͯΉ ΈΓ͋
    ͍ͣΕؚ͔Ή ʹͳ ͪ͑
    ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
    

    View Slide

87. select *
    from image_info
    where true
    order by created_at desc
    limit 200
    ͱΓ͋͑ͣUSVF
    શؚͯΉ ΈΓ͋
    ͍ͣΕؚ͔Ή ʹͳ ͪ͑
    ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
    

    View Slide

88. select *
    from image_info
    where true and
    match (description) against
    ('ΈΓ͋' in natural language mode)
    and description like '%ΈΓ͋%'
    order by created_at desc
    limit 200
    "/%ݕࡧ
    શؚͯΉ ΈΓ͋
    ͍ͣΕؚ͔Ή ʹͳ ͪ͑
    ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
    

    View Slide

89. select *
    from image_info
    where true and
    match (description) against
    ('ΈΓ͋' in natural language mode)
    and description like '%ΈΓ͋%'
    and ((match (description) against ('ʹͳ' in natural
    language mode and description like '%ʹͳ%') or
    (match (description) against ('ͪ͑' in natural
    language mode and description like '%ͪ͑%'))
    order by created_at desc
    limit 200
    03ݕࡧ
    શؚͯΉ ΈΓ͋
    ͍ͣΕؚ͔Ή ʹͳ ͪ͑
    ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
    

    View Slide

90. select *
    from image_info
    where true and
    match (description) against
    ('ΈΓ͋' in natural language mode)
    and description like '%ΈΓ͋%'
    and ((match (description) against ('ʹͳ' in natural
    language mode and description like '%ʹͳ%') or
    (match (description) against ('ͪ͑' in natural
    language mode and description like '%ͪ͑%'))
    and description not like '%ΕΈΓ͋%'
    order by created_at desc
    limit 200
    /05ݕࡧ
    શؚͯΉ ΈΓ͋
    ͍ͣΕؚ͔Ή ʹͳ ͪ͑
    ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
    

    View Slide

91. select *
    from image_info
    where true and
    match (description) against
    ('ΈΓ͋' in natural language mode)
    and description like '%ΈΓ͋%'
    and ((match (description) against ('ʹͳ' in natural
    language mode and description like '%ʹͳ%') or
    (match (description) against ('ͪ͑' in natural
    language mode and description like '%ͪ͑%'))
    and description not like '%ΕΈΓ͋%'
    order by created_at desc
    limit 200
    ׬੒
    શؚͯΉ ΈΓ͋
    ͍ͣΕؚ͔Ή ʹͳ ͪ͑
    ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
    

    View Slide

92. select *
    from image_info
    where true and
    match (description) against
    ('ΈΓ͋' in natural language mode)
    and description like '%ΈΓ͋%'
    and ((match (description) against ('ʹͳ' in natural
    language mode and description like '%ʹͳ%') or
    (match (description) against ('ͪ͑' in natural
    language mode and description like '%ͪ͑%'))
    and description not like '%ΕΈΓ͋%'
    order by created_at desc
    limit 200
    ͔ͤͬ͘ͳͷͰؑ͝৆͍ͩ͘͞
    

    View Slide

93. ΊͰͨ͠ ຊ౰ʹ
    
    

    View Slide

94. ؔ࿈ݚڀ
    

    View Slide

95. IUUQTTQFBLFSEFDLDPNLND@KQHPPHMFQIPUPTOJOFUVUPEFTIJ
    UVUBJSBTVUPXPMJVNFJQNV
    

    View Slide

96. (PPHMFͷਓ޻஌ೳʹΑΔ
    ࣗಈλά෇͚ศརͦ͏
    పఈͨࣗ͠ಈԽͰ͢Ͷ
    

    View Slide

97. શ෦(PͰॻ͍ͯΔͷ͍͢͝
    ը૾ݕࡧͩͱ޷ΈͱͷϚο
    νϯά͚ͬ͜͏େมͦ͏
    

    View Slide

98. IUUQTGBWDMJQQFSBQQTQPUDPN
    

    View Slide

99. ͍͍Ͷͨ͠ը૾ҰཡΛ
    खܰʹݟΕͯศར
    

    View Slide

100. 3&45"1*ͷ࢓༷ʹ
     റΒΕͯ͠·͏
     ͦΕ͸ͦ͏
     
     

     View Slide

101. ࠓޙͷ՝୊
     

     View Slide

102. ݕࡧͰ͖ΔΠϥετ

     ݕࡧͰ͖ͳ͍Πϥετ
     

     View Slide

103. IUUQTUXJUUFSDPNTIJOLBOTFO7T0METUBUVT
     
     

     View Slide

104. IUUQTUXJUUFSDPNTIJOLBOTFO7T0METUBUVT
     
     ຊจ͕ͳ͍ͱ
     ݕࡧͰ͖ͳ͍
     

     View Slide

105. ਓྗλά৬ਓ
     

     View Slide

106. σΟʔϓϥʔχϯά
     ͬͯ΍ͭͰ
     ͳΜͱ͔ͳΒͳ͍͔
     

     View Slide

107. ࣸਅ͸อଘͨ͘͠ͳ͍
     ৔߹ʹΑΔ
     
     

     View Slide

108. ৭ͷ෼෍ݟ͍͍ͯײ͡
     ʹͰ͖ͳ͍͔
     ʮσδλϧ৴߸ॲཧʯऔͬͯͳ͍
     

     View Slide

109. σΟʔϓϥʔχϯά
     ͬͯ΍ͭͰ
     ͳΜͱ͔ͳΒͳ͍͔
     

     View Slide

110. (PPHMF$MPVE7JTJPO
     ͬͯ΍ͭͰ
     ͳΜͱ͔ͳΒͳ͍͔
     

     View Slide

111. ՝ۚ΍ʂʂʂʂ
     ·ͩ՝ۚͯ͠ͳ͍
     
     ݄͙Β͍ͰͰ͖ͳ͍͔
     

     View Slide

112. ݕࡧͰ͖ͳ͍จࣈ
     OHSBNͩͱग़ͳ͍
     
     -*,&ͩͱग़Δ
     
     

     View Slide

113. ΞυϗοΫʹճආ
     JG/(ϫʔυΛؚΉ
     -*,&ͷΈͰΫΤϦΛ૊Έཱͯͯฦ͢
     FMTF
     OHSBNͱ-*,&ͰΫΤϦΛ૊Έཱͯͯฦ͢
     FOE
     

     View Slide

114. จࣈ͸Ԟ͕ਂ͍
     ҋ΋ਂ͍
     ͦΕҎ্͍͚ͳ͍
     ΢ϫʔο
     

     View Slide

115. શจݕࡧΫΤϦ͕೉͍͠
     

     View Slide

116. select *
     from image_info
     where true and
     match (description) against
     ('ΈΓ͋' in natural language mode)
     and description like '%ΈΓ͋%'
     and ((match (description) against ('ʹͳ' in natural
     language mode and description like '%ʹͳ%') or
     (match (description) against ('ͪ͑' in natural
     language mode and description like '%ͪ͑%'))
     and description not like '%ΕΈΓ͋%'
     order by created_at desc
     limit 200
     ͔ͤͬ͘ͳͷͰؑ͝৆͍ͩ͘͞ ࠶ܝ
     
     

     View Slide

117. Ұํ1PTUHSF42-
     QH@CJHN
     -*,&͚ͩͰ0,
     ֦ுೖΕΔඞཁ͋Γ
     
     τϨʔυΦϑ
     

     View Slide

118. "1*ͷഇࢭ
     ਓؒͷྲྀग़
     

     View Slide

119. 5XJUUFS4USFBNJOH"1*
     
     
     

     View Slide

120. @ਓਓਓਓ@
     ʼಥવͷࢮʻ
     :?:?:?:
     

     View Slide

121. ʁʁʁ
     ʮετϦʔϛϯάͳΜͯ
     πΠഇ͔͠࢖ΘΜ΍ΖXʯ
     

     View Slide

122. 4USFBNJOH"1*ͰऔΕΔ৘ใ
     㱠3&45"1*ͰऔΕΔ৘ใ
     ྫ͍͍Ͷͨ͠πΠʔτ
     
     ࣌ܥྻ͕ڰ͏
     
     ࢮ׆໰୊ʂʂʂʂʂʂʂ
     
     

     View Slide

123. IUUQTUXJUUFSDPNXBLBNFTPCBTUBUVT
     

     View Slide

124. "DDPVOU"DUJWJUZ"1*
     㲈0VUHPJOH8FCIPPL
     

     View Slide

125. ֎͔Βݟ͑Δ)551αʔό
     ͕ඞཁ
     ଟ͘ͷ5XJUUFSΫϥΠΞϯτ
     ͕ϦΞϧλΠϜऔಘΛఘΊ
     ͟ΔΛಘͳ͍
     

     View Slide

126. View Slide

127. ֆඳ͖
     ूஂౚ݁૽ಈ
     ݱࡏ
     
     

     View Slide

128. IUUQTQBXPPOFUBCPVU
     

     View Slide

129. "1*͸ʁ
     

     View Slide

130. ͍ͭͷؒʹ͔
     4USFBNJOH"1*
     ࣮૷͞ΕͯΔʂʂʂ
     IUUQTHJUIVCDPNUPPUTVJUFEPDVNFOUBUJPOCMPCNBTUFS6TJOH
     UIF"1*4USFBNJOH"1*NE
     

     View Slide

131. 1BXPP͸4USFBNJOH
     "1*ʹରԠͯ͠Δ
     Ϡολʔʂʂʂ
     $ curl https://pawoo.net/api/v1/streaming/public
     4FSWFS4FOU&WFOUTͰ߱ͬͯ͘Δ༷ࢠ͕ݟΕΔ
     

     View Slide

132. కΊͷϙΤϜ
     

     View Slide

133. αʔϏεʹ͸ण໋͕͋Δ
     એݴత஌ࣝ
     
     

     View Slide

134. ͷ৴པΛஔ͚Δ
     αʔϏε͸ଘࡏ͠ͳ͍
     ͍Ζ͍Ζͳࣄ͕͋Δʜʜ
     

     View Slide

135. զʑ͸ຊ࣭తʹ
     ΠϯλʔωοτͰͷ
     ༡຀Λ΍͍͔ͬͯ͘͠ͳ͍
     

     View Slide

136. ࣭໰
     

     View Slide

137. ͔͜͜Βઌ
     ࢀߟʹͨ͠ϖʔδͳͲ
     

     View Slide

138. w "1*PWFSWJFXUPPUTVJUFEPDVNFOUBUJPO
     w IUUQTHJUIVCDPNUPPUTVJUFEPDVNFOUBUJPOCMPCNBTUFS
     6TJOHUIF"1*"1*NE
     w 1BXPP
     w IUUQTQBXPPOFUBCPVU
     w 6TFUIF*OEFY -VLF
     w IUUQTVTFUIFJOEFYMVLFDPNKB
     w 0''4&5Λ࢖Θͳ͍ߴ଎ͳϖʔδωʔγϣϯͷ࣮ݱ
     w IUUQTRJJUBDPNNQZXJUFNTDDFFF
     

     View Slide