Slide 1
Slide 1 text
ը૾ࣗಈอଘɾӾཡγεςϜ
ʮٹ͍ʯʹΑͬͯͨΒ͞ΕΔޮೳ
ࢮʹΏ͘Twitter Streaming APIͷผΕͷݴ༿Λఴ͑ͯ
य़߹॓ߨ࠲2018 @utgwkk
Slide 2
Slide 2 text
No content
Slide 3
Slide 3 text
ٹΘΕͯ
͍·͔͢
Slide 4
Slide 4 text
ٹ͍
Slide 5
Slide 5 text
IUUQTUXJUUFSDPNNPLBYTUBUVT
Slide 6
Slide 6 text
͍͍Ͷ❤
γΣΞ
ը૾Λอଘ
Slide 7
Slide 7 text
อଘํ๏ˠόϥόϥ
อଘॴˠόϥόϥ
γΣΞˠखͰΔʁ
Slide 8
Slide 8 text
No content
Slide 9
Slide 9 text
͍͍Ͷ❤
Slide 10
Slide 10 text
$POP)B714
͍͍Ͷ❤
͍͍Ͷ❤
͞ΕͨͰ
γΣΞ
ը૾Λอଘ
ϝλσʔλΛ
อଘ
༏উ
Slide 11
Slide 11 text
JNBHFT
JNBHFTEBZ
JNBHFTNPOUI
Slide 12
Slide 12 text
4USFBNJOH"1*
Slide 13
Slide 13 text
πΠʔτ
͍͍Ͷͨ͠͞Εͨ
ϑΥϩʔͨ͠͞Εͨ
ͳͲͳͲ
Slide 14
Slide 14 text
*ODPNJOH8FCIPPL
Slide 15
Slide 15 text
import json
import requests
payload = {
"username": "hogefuga",
"text": "yo"
}
requests.post(
"https://hooks.slack.com/services/hoge/fuga/waowaowao",
data=json.dumps(payload)
)
Slide 16
Slide 16 text
1PTUPOMZ
/PDPNQMJDBUFEDPEF
+VTU1045QBZMPBE
Slide 17
Slide 17 text
No content
Slide 18
Slide 18 text
images
id
filename
created_at
image_info
id
image_id
description
source
created_at
Slide 19
Slide 19 text
ը૾ͷอଘ͚ͩͰμϝ
ݕࡧ͕͍ͨ͠ʂʂʂ
Slide 20
Slide 20 text
No content
Slide 21
Slide 21 text
IUUQTKQWVFKTPSHJOEFYIUNM
Slide 22
Slide 22 text
ૉͳϑϩϯτΤϯυ
ੈք؍
TDSJQUTSDKTWVFNJOKTTDSJQU
Slide 23
Slide 23 text
+40/ฦ͢"1*αʔό
ͳͥ͜ͷઃܭʹ͔ͨ͠
Εͨ
Slide 24
Slide 24 text
ʮٹ͍ʯΛࢧ͑Δ42-
Slide 25
Slide 25 text
ϖʔδωʔγϣϯ
Slide 26
Slide 26 text
ૉͳϖʔδωʔγϣϯ
Slide 27
Slide 27 text
select *
from image_info
order by id desc
limit 200 offset page*200
Slide 28
Slide 28 text
0''4&5-*.*5
͍
Slide 29
Slide 29 text
select *
from image_info
order by id desc
limit 2
offset 500000000
Slide 30
Slide 30 text
σʔλ1
σʔλ2
σʔλ500000001
ʜʜ
σʔλ500000002
Slide 31
Slide 31 text
σʔλ1
σʔλ2
σʔλ500000001
ʜʜ
σʔλ500000002
Slide 32
Slide 32 text
σʔλ1
σʔλ2
σʔλ500000001
ʜʜ
σʔλ500000002
Slide 33
Slide 33 text
σʔλ1
σʔλ2
σʔλ500000001
ʜʜ
σʔλ500000002
ສޙ
Slide 34
Slide 34 text
σʔλ1
σʔλ2
σʔλ500000001
ʜʜ
σʔλ500000002
Slide 35
Slide 35 text
σʔλ1
σʔλ2
σʔλ500000001
ʜʜ
σʔλ500000002
Slide 36
Slide 36 text
select *
from image_info
where (something)
order by id desc
limit 200
offset 500000000
Slide 37
Slide 37 text
select *
from image_info
where (something)
order by id desc
limit 200
offset 500000000
൪Ͳ͜ʁ
Slide 38
Slide 38 text
TJODF@JENBY@JE
ྫ5XJUUFS3&45"1*
Slide 39
Slide 39 text
ϖʔδࢦఆΛఘΊΔ
ߴͳOFYUQSFWऔಘ
JE͕ࢄతͰ͑Δ
Slide 40
Slide 40 text
select *
from image_info
order by id desc
limit k
Slide 41
Slide 41 text
M
N+1
N
M+1
N-1
ʜʜ
ʜ ʜ
QSFW
OFYU
PSEFSCZJEEFTD
Slide 42
Slide 42 text
select *
from image_info
where id < N
order by id desc
limit k
Slide 43
Slide 43 text
M+1
M
N-1
M-1
ʜʜ
ʜ
N
PSEFSCZJEEFTD
ʜ
Slide 44
Slide 44 text
select *
from image_info
where id > M
order by id desc
limit k
Slide 45
Slide 45 text
ΊͰͨ͠
Slide 46
Slide 46 text
ΊͰͨ͠Ͱͳ͍
Slide 47
Slide 47 text
select *
from image_info
where id > M
order by id desc
limit k
Slide 48
Slide 48 text
max-1
max-k
max
M+1
ʜʜ
ʜ
PSEFSCZJEEFTD
Slide 49
Slide 49 text
select *
from image_info
where id > M
order by id asc
limit k
Slide 50
Slide 50 text
M+2
L
M+1
L+1
ʜʜ
ʜ
M
PSEFSCZJEBTD
ʜ
Slide 51
Slide 51 text
select * from (
select *
from image_info
where id > M
order by id asc
limit k
) as t
order by id desc
Slide 52
Slide 52 text
M+2
M+1
L
M
ʜʜ
ʜ
L+1
PSEFSCZJEEFTD
ʜ
Slide 53
Slide 53 text
ΊͰͨ͠ ຊʹ
Slide 54
Slide 54 text
ແݶͷϖʔδωʔγϣϯ
Λఏڙ͠ͳ͍ͱ͍͏ख
(PPHMF
5XJUUFS
Slide 55
Slide 55 text
શจݕࡧ
Slide 56
Slide 56 text
select *
from image_info
where description like
'%༫ਫࢠ%'
Slide 57
Slide 57 text
-*,&શจݕࡧ͍
શEFTDSJQUJPOʹର͢Δ
ਖ਼نදݱϚον
Slide 58
Slide 58 text
ΠϯσοΫε͕ޮ͔ͳ͍
ˠਓݖ͕ͳ͍
Slide 59
Slide 59 text
6TFUIF*OEFY
-VLF
ΛಡΈ·͠ΐ͏
IUUQTVTFUIFJOEFYMVLFDPNKB
Slide 60
Slide 60 text
IUUQJTVDPOOFU
Slide 61
Slide 61 text
ΠϯσοΫε͕ޮ͔ͳ͍ͳΒ
ΠϯσοΫε͕ޮ͘Α͏ʹ͢
Ε͍͍͡Όͳ͍
Slide 62
Slide 62 text
✨OHSBNJOEFY✨
ྫO
Slide 63
Slide 63 text
จষ
ˣ
จࣈͷจࣈྻू߹
Slide 64
Slide 64 text
จࣈͷจࣈྻ
ˣ
จষͷू߹
Slide 65
Slide 65 text
༫ਫࢠ
Slide 66
Slide 66 text
༫ਫਫࢠ
Slide 67
Slide 67 text
༫ਫ ༫ਫࢠ ……
ਫ ༫ਫࢠ ……
ࢠ ༫ਫࢠ ……
…… ……
Slide 68
Slide 68 text
༫ਫ
ਫ
ࢠ
N-gram
index
༫ਫࢠ
Slide 69
Slide 69 text
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
Slide 70
Slide 70 text
select *
from image_info
where match (description) against
('༫ਫࢠ' in natural language mode)
Slide 71
Slide 71 text
ΊͰͨ͠
Slide 72
Slide 72 text
ΊͰͨ͠Ͱͳ͍
Slide 73
Slide 73 text
ྫΈΓ͋
Slide 74
Slide 74 text
ΈΓΓ͋
Slide 75
Slide 75 text
ʮΈΓʯʮΓ͋ʯ
ΛؚΉจষ͍͘ΒͰ
͋Γͦ͏
ʮΈΓ͋ʯ͕ඞཁ
Slide 76
Slide 76 text
ͦ͜ʹ-*,&
Slide 77
Slide 77 text
ΈΓ
Γ͋
N-gram
index
LIKE ΈΓ͋
Slide 78
Slide 78 text
select *
from image_info
where match (description) against
('ΈΓ͋' in natural language mode)
and description like '%ΈΓ͋%'
Slide 79
Slide 79 text
ΊͰͨ͠ ຊʹ
͜ͷํ๏͕٢ͱग़Δ͔ڟͱग़Δ͔ʹॾઆ͋Δ
Slide 80
Slide 80 text
"/%03&9ݕࡧ
Slide 81
Slide 81 text
χʔζ͍Ζ͍Ζ
ΈΓ͋ਔಸ
ٱอ03೫ʑ
ΈΓ͋ΕΈΓ͋
Slide 82
Slide 82 text
ҰํQJYJW
Slide 83
Slide 83 text
Δͧʙʙʙ
Slide 84
Slide 84 text
No content
Slide 85
Slide 85 text
શؚͯΉ ΈΓ͋
͍ͣΕؚ͔Ή ʹͳ ͪ͑
ͲΕؚ·ͳ͍ ΕΈΓ͋
Slide 86
Slide 86 text
select *
from image_info
where (FILL IN HERE)
order by created_at desc
limit 200
શؚͯΉ ΈΓ͋
͍ͣΕؚ͔Ή ʹͳ ͪ͑
ͲΕؚ·ͳ͍ ΕΈΓ͋
Slide 87
Slide 87 text
select *
from image_info
where true
order by created_at desc
limit 200
ͱΓ͋͑ͣUSVF
શؚͯΉ ΈΓ͋
͍ͣΕؚ͔Ή ʹͳ ͪ͑
ͲΕؚ·ͳ͍ ΕΈΓ͋
Slide 88
Slide 88 text
select *
from image_info
where true and
match (description) against
('ΈΓ͋' in natural language mode)
and description like '%ΈΓ͋%'
order by created_at desc
limit 200
"/%ݕࡧ
શؚͯΉ ΈΓ͋
͍ͣΕؚ͔Ή ʹͳ ͪ͑
ͲΕؚ·ͳ͍ ΕΈΓ͋
Slide 89
Slide 89 text
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ݕࡧ
શؚͯΉ ΈΓ͋
͍ͣΕؚ͔Ή ʹͳ ͪ͑
ͲΕؚ·ͳ͍ ΕΈΓ͋
Slide 90
Slide 90 text
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ݕࡧ
શؚͯΉ ΈΓ͋
͍ͣΕؚ͔Ή ʹͳ ͪ͑
ͲΕؚ·ͳ͍ ΕΈΓ͋
Slide 91
Slide 91 text
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
શؚͯΉ ΈΓ͋
͍ͣΕؚ͔Ή ʹͳ ͪ͑
ͲΕؚ·ͳ͍ ΕΈΓ͋
Slide 92
Slide 92 text
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
͔ͤͬ͘ͳͷͰ͍ؑͩ͘͝͞
Slide 93
Slide 93 text
ΊͰͨ͠ ຊʹ
Slide 94
Slide 94 text
ؔ࿈ݚڀ
Slide 95
Slide 95 text
IUUQTTQFBLFSEFDLDPNLND@KQHPPHMFQIPUPTOJOFUVUPEFTIJ
UVUBJSBTVUPXPMJVNFJQNV
Slide 96
Slide 96 text
(PPHMFͷਓೳʹΑΔ
ࣗಈλά͚ศརͦ͏
పఈͨࣗ͠ಈԽͰ͢Ͷ
Slide 97
Slide 97 text
શ෦(PͰॻ͍ͯΔͷ͍͢͝
ը૾ݕࡧͩͱΈͱͷϚο
νϯά͚ͬ͜͏େมͦ͏
Slide 98
Slide 98 text
IUUQTGBWDMJQQFSBQQTQPUDPN
Slide 99
Slide 99 text
͍͍Ͷͨ͠ը૾ҰཡΛ
खܰʹݟΕͯศར
Slide 100
Slide 100 text
3&45"1*ͷ༷ʹ
റΒΕͯ͠·͏
ͦΕͦ͏
Slide 101
Slide 101 text
ࠓޙͷ՝
Slide 102
Slide 102 text
ݕࡧͰ͖ΔΠϥετ
ݕࡧͰ͖ͳ͍Πϥετ
Slide 103
Slide 103 text
IUUQTUXJUUFSDPNTIJOLBOTFO7T0METUBUVT
Slide 104
Slide 104 text
IUUQTUXJUUFSDPNTIJOLBOTFO7T0METUBUVT
ຊจ͕ͳ͍ͱ
ݕࡧͰ͖ͳ͍
Slide 105
Slide 105 text
ਓྗλά৬ਓ
Slide 106
Slide 106 text
σΟʔϓϥʔχϯά
ͬͯͭͰ
ͳΜͱ͔ͳΒͳ͍͔
Slide 107
Slide 107 text
ࣸਅอଘͨ͘͠ͳ͍
߹ʹΑΔ
Slide 108
Slide 108 text
৭ͷݟ͍͍ͯײ͡
ʹͰ͖ͳ͍͔
ʮσδλϧ৴߸ॲཧʯऔͬͯͳ͍
Slide 109
Slide 109 text
σΟʔϓϥʔχϯά
ͬͯͭͰ
ͳΜͱ͔ͳΒͳ͍͔
Slide 110
Slide 110 text
(PPHMF$MPVE7JTJPO
ͬͯͭͰ
ͳΜͱ͔ͳΒͳ͍͔
Slide 111
Slide 111 text
՝ۚʂʂʂʂ
·ͩ՝ۚͯ͠ͳ͍
݄͙Β͍ͰͰ͖ͳ͍͔
Slide 112
Slide 112 text
ݕࡧͰ͖ͳ͍จࣈ
OHSBNͩͱग़ͳ͍
-*,&ͩͱग़Δ
Slide 113
Slide 113 text
ΞυϗοΫʹճආ
JG/(ϫʔυΛؚΉ
-*,&ͷΈͰΫΤϦΛΈཱͯͯฦ͢
FMTF
OHSBNͱ-*,&ͰΫΤϦΛΈཱͯͯฦ͢
FOE
Slide 114
Slide 114 text
จࣈԞ͕ਂ͍
ҋਂ͍
ͦΕҎ্͍͚ͳ͍
ϫʔο
Slide 115
Slide 115 text
શจݕࡧΫΤϦ͕͍͠
Slide 116
Slide 116 text
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
͔ͤͬ͘ͳͷͰ͍ؑͩ͘͝͞ ࠶ܝ
Slide 117
Slide 117 text
Ұํ1PTUHSF42-
QH@CJHN
-*,&͚ͩͰ0,
֦ுೖΕΔඞཁ͋Γ
τϨʔυΦϑ
Slide 118
Slide 118 text
"1*ͷഇࢭ
ਓؒͷྲྀग़
Slide 119
Slide 119 text
5XJUUFS4USFBNJOH"1*
Slide 120
Slide 120 text
@ਓਓਓਓ@
ʼಥવͷࢮʻ
:?:?:?:
Slide 121
Slide 121 text
ʁʁʁ
ʮετϦʔϛϯάͳΜͯ
πΠഇ͔͠ΘΜΖXʯ
Slide 122
Slide 122 text
4USFBNJOH"1*ͰऔΕΔใ
㱠3&45"1*ͰऔΕΔใ
ྫ͍͍Ͷͨ͠πΠʔτ
࣌ܥྻ͕ڰ͏
ࢮ׆ʂʂʂʂʂʂʂ
Slide 123
Slide 123 text
IUUQTUXJUUFSDPNXBLBNFTPCBTUBUVT
Slide 124
Slide 124 text
"DDPVOU"DUJWJUZ"1*
㲈0VUHPJOH8FCIPPL
Slide 125
Slide 125 text
֎͔Βݟ͑Δ)551αʔό
͕ඞཁ
ଟ͘ͷ5XJUUFSΫϥΠΞϯτ
͕ϦΞϧλΠϜऔಘΛఘΊ
͟ΔΛಘͳ͍
Slide 126
Slide 126 text
No content
Slide 127
Slide 127 text
ֆඳ͖
ूஂౚ݁૽ಈ
ݱࡏ
Slide 128
Slide 128 text
IUUQTQBXPPOFUBCPVU
Slide 129
Slide 129 text
"1*ʁ
Slide 130
Slide 130 text
͍ͭͷؒʹ͔
4USFBNJOH"1*
࣮͞ΕͯΔʂʂʂ
IUUQTHJUIVCDPNUPPUTVJUFEPDVNFOUBUJPOCMPCNBTUFS6TJOH
UIF"1*4USFBNJOH"1*NE
Slide 131
Slide 131 text
1BXPP4USFBNJOH
"1*ʹରԠͯ͠Δ
Ϡολʔʂʂʂ
$ curl https://pawoo.net/api/v1/streaming/public
4FSWFS4FOU&WFOUTͰ߱ͬͯ͘Δ༷ࢠ͕ݟΕΔ
Slide 132
Slide 132 text
కΊͷϙΤϜ
Slide 133
Slide 133 text
αʔϏεʹण໋͕͋Δ
એݴతࣝ
Slide 134
Slide 134 text
ͷ৴པΛஔ͚Δ
αʔϏεଘࡏ͠ͳ͍
͍Ζ͍Ζͳࣄ͕͋Δʜʜ
Slide 135
Slide 135 text
զʑຊ࣭తʹ
ΠϯλʔωοτͰͷ
༡Λ͍͔ͬͯ͘͠ͳ͍
Slide 136
Slide 136 text
࣭
Slide 137
Slide 137 text
͔͜͜Βઌ
ࢀߟʹͨ͠ϖʔδͳͲ
Slide 138
Slide 138 text
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