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

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

KMC春合宿講座2018

9b95a10c83110dcab47b55fbe2f31edf?s=128

UTAGAWA Kiki

March 16, 2018
Tweet

Transcript

  1. ը૾ࣗಈอଘɾӾཡγεςϜ
 ʮٹ͍ʯʹΑͬͯ΋ͨΒ͞ΕΔޮೳ ࢮʹΏ͘Twitter Streaming API΁ͷผΕͷݴ༿Λఴ͑ͯ य़߹॓ߨ࠲2018 @utgwkk

  2. None
  3. ٹΘΕͯ ͍·͔͢

  4. ٹ͍

  5. IUUQTUXJUUFSDPNNPLBYTUBUVT

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

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

  8. None
  9. ͍͍Ͷ❤

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

  11.  JNBHFT JNBHFTEBZ  JNBHFTNPOUI

  12. 4USFBNJOH"1*

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

  14. *ODPNJOH8FCIPPL

  15. import json import requests payload = { "username": "hogefuga", "text":

    "yo" } requests.post( "https://hooks.slack.com/services/hoge/fuga/waowaowao", data=json.dumps(payload) )
  16. 1PTUPOMZ /PDPNQMJDBUFEDPEF +VTU1045QBZMPBE

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

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

  20. None
  21. IUUQTKQWVFKTPSHJOEFYIUNM

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

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

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

  25. ϖʔδωʔγϣϯ

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

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

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

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

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

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

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

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

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

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

  36. select * from image_info where (something) order by id desc

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

    limit 200 offset 500000000 ൪໨Ͳ͜ʁ
  38. TJODF@JENBY@JE ྫ5XJUUFS3&45"1*

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

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

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

    PSEFSCZJEEFTD
  42. select * from image_info where id < N order by

    id desc limit k
  43. M+1 M N-1 M-1 ʜʜ ʜ N PSEFSCZJEEFTD ʜ

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

    id desc limit k
  45. ΊͰͨ͠

  46. ΊͰͨ͠Ͱ͸ͳ͍

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

    id desc limit k
  48. max-1 max-k max M+1 ʜʜ ʜ PSEFSCZJEEFTD

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

    id asc limit k
  50. M+2 L M+1 L+1 ʜʜ ʜ M PSEFSCZJEBTD ʜ

  51. select * from ( select * from image_info where id

    > M order by id asc limit k ) as t order by id desc
  52. M+2 M+1 L M ʜʜ ʜ L+1 PSEFSCZJEEFTD ʜ

  53. ΊͰͨ͠ ຊ౰ʹ

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

  55. શจݕࡧ 

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

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

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

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

  60. IUUQJTVDPOOFU

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

  62. ✨OHSBNJOEFY✨ ྫO

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

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

  65. ༫ਫ޾ࢠ

  66. ༫ਫਫ޾޾ࢠ

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

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

  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
  70. select * from image_info where match (description) against ('༫ਫ޾ࢠ' in

    natural language mode)
  71. ΊͰͨ͠

  72. ΊͰͨ͠Ͱ͸ͳ͍

  73. ྫΈΓ͋

  74. ΈΓΓ͋

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

  76. ͦ͜ʹ-*,&

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

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

    natural language mode)
 and description like '%ΈΓ͋%'
  79. ΊͰͨ͠ ຊ౰ʹ  ͜ͷํ๏͕٢ͱग़Δ͔ڟͱग़Δ͔ʹ͸ॾઆ͋Δ

  80. "/%03&9ݕࡧ

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

  82. ҰํQJYJW

  83. ΍Δͧʙʙʙ

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

  86. select * from image_info where (FILL IN HERE) order by

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

    limit 200 ͱΓ͋͑ͣUSVF શؚͯΉ ΈΓ͋ ͍ͣΕؚ͔Ή ʹͳ ͪ͑ ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
  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 "/%ݕࡧ શؚͯΉ ΈΓ͋ ͍ͣΕؚ͔Ή ʹͳ ͪ͑ ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
  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ݕࡧ શؚͯΉ ΈΓ͋ ͍ͣΕؚ͔Ή ʹͳ ͪ͑ ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
  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ݕࡧ શؚͯΉ ΈΓ͋ ͍ͣΕؚ͔Ή ʹͳ ͪ͑ ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
  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 ׬੒ શؚͯΉ ΈΓ͋ ͍ͣΕؚ͔Ή ʹͳ ͪ͑ ͲΕ΋ؚ·ͳ͍ ΕΈΓ͋
  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 ͔ͤͬ͘ͳͷͰؑ͝৆͍ͩ͘͞
  93. ΊͰͨ͠ ຊ౰ʹ

  94. ؔ࿈ݚڀ

  95. IUUQTTQFBLFSEFDLDPNLND@KQHPPHMFQIPUPTOJOFUVUPEFTIJ UVUBJSBTVUPXPMJVNFJQNV

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

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

  98. IUUQTGBWDMJQQFSBQQTQPUDPN

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

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

  101. ࠓޙͷ՝୊

  102. ݕࡧͰ͖ΔΠϥετ
 ݕࡧͰ͖ͳ͍Πϥετ

  103. IUUQTUXJUUFSDPNTIJOLBOTFO7T0METUBUVT 

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

  105. ਓྗλά৬ਓ

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

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

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

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

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

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

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

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

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

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

  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 ͔ͤͬ͘ͳͷͰؑ͝৆͍ͩ͘͞ ࠶ܝ
  117. Ұํ1PTUHSF42- QH@CJHN -*,&͚ͩͰ0, ֦ுೖΕΔඞཁ͋Γ  τϨʔυΦϑ

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

  119. 5XJUUFS4USFBNJOH"1* 

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

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

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

  123. IUUQTUXJUUFSDPNXBLBNFTPCBTUBUVT

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

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

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

  128. IUUQTQBXPPOFUBCPVU

  129. "1*͸ʁ

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

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

  132. కΊͷϙΤϜ

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

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

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

  136. ࣭໰

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

  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