Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

KMC春合宿講座2018

utagawa kiki

March 16, 2018
Tweet

More Decks by utagawa kiki

Other Decks in Programming

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