CDPM #2

先日のCDPMで発表した資料です

Transcript

1. ï ¿´ºwv ClickHouse Cross Data Platforms Meetup ²´¹öü´¿ Îo}wClickHouse {ttvt}Ïw~yÿ÷ü¿¸ó¸û²úóÀ~^}wìo²Ö±vt»s~ âÛ{ttv1¿´º_wvë²ý±vt}~y2úùó¿¹wv1/ÿ{ƒxz|¹²±vt}~w ·v2
2. ClickHouse Japan Team ~}Ï ¶oß// ¹º½ þµø Account Executive ClickHouse w{Digital Naitve 1Web µüó¹ÿ~ÿû²ç _2Dell 1RedHat 1Elastic ²}vClickHouse ~ooqNr{ Ó24 /~w²rº1¯wü÷³
   ´¿w±6 w¸
   {š v2 þŸÿ_/ ²º·ð#ô¹òý######Account Executive ClickHouse w{ý°1{m1/ú1{vzyÿû²ç_2 JRC 1Dell 1Medidata 1Elastic 1Domo zy²}vClickHouse x /2oo~~Àýüðû{»Ïϲ/ÿ{_º~w·vÿ o / þôø ÿ½ Senior Support Engineer /kz}¸ó¸ó~Ÿ~'Ûo²}v1Elastic zy~IT O mw÷ýõ·ó·÷úûµüó¹²o?xwmÛ{ßÏ2ÿ 1^/o÷´âÛµýüøzy1_ßtâÛVß²ç_2 ³}Y: japan_clickhouse@clickhouse.com
3. w ClickHouse ~ßoVßy¼ÿ ur1g_~_Üwy2ClickHouse xtv]_²Þtsx|r»o²1_±v~o²1~zÿo{¹úwvt}~w·v2k~øþ¢~o{ ¹1ClickHouse |g²ßoxwvt»Vß²ø³w€ut2 1 O~tãw~µýüø 2 ú²û¿´ðV÷DB 3 ßorAI 4 Excel ~ïÿ
4. w ClickHouse ~ßoVßy¼ÿ ur1g_~_Üwy2ClickHouse xtv]_²Þtsx|r»o²1_±v~o²1~zÿo {¹úwvt}~w·v2k~øþ¢~o{¹1ClickHouse |g²ßoxwvt»Vß²ø³w €ut2 1 O~tãw~µýüø OPEN HOUSE wrº~{³ 2 ú²û¿´ðV÷DB ¿Ÿ÷ü¿÷v~±~^¯âÛ 3 ßorAI ¿ú¸´ö³özVßwy|Õt~y 4 Excel ~ïÿ èŠÿ½õøxw{wy  x 2. ú²û¿´ðV÷DB ClickHouse 1/k÷ü¿²ú²û¿´ðwV÷y »±{¯Šu¼WgU÷ü¿ùü¹ûv·¹öð wy2SQL {þâw1¿w~w¹øüü¸û}w}~ yÿ^}wg²/~r»OSS ~ú²û¿´ðV÷÷ ýÀ¿øwrº1^}gŸ~÷ü¿¶·²ÿ¶¹xw v1ºó÷ü1SaaS 1BYOC zy߯ôÛ_¿z1W oµüó¹²]Ÿu¼vt~y2
5. w ClickHouse ~{ÿözý?ÿ ClickHouse ~ï1}~ñõ¹üþó¹{rº~y2w1ClickHouse |ctg²síözý?_ww·v {ÿ÷ü¿ùü¹~^}w1÷vŸþ|}2}~»ù{¹ƒxvvput2 A u?U¯²uûw֊ B ײ¯×ÿ» C ÿlw× D øù󶿷÷óûv
6. w ClickHouse ~{ÿözý?ÿ ClickHouse ~ï1}~ñõ¹üþó¹{rº~y2w1ClickHouse |ctg²síözý?_ww·v{ÿ÷ü¿ùü¹ ~^}w1÷vŸþ|}2}~»ù{¹ƒxvv€ut2 A u?U¯²uûw֊ óß²x»÷vŸþ²ßÿ B ײ¯×ÿ» þûññ÷³²÷vwrº~{³ C ÿlw× ¸³wy|1}¼{ÿwztwy D øù󶿷÷óûv OLTP wz1OLAP ~ú²û¿´ðV÷|ßowy x: A u?U¯²uûw֊w}»¿Ÿ÷vý? ClickHouse WgU¹øüü¸xù¿øûWu¼¿¸ú߯¸ó¸ó{¸º1ßg~÷ ü¿ùü¹wuV{¹uf—{{»÷v²1¿z{uûwßÎu{»sx|w}~y2 s¼1ú²û¿´ðÀó·õüüù´²ùûó¿V÷{ztv1wRöz²ùðóöü ¸xzº~y2
7. w ClickHouse y³zo”w¿¼vt»ÿ ú²û¿´ðV÷ øÿ / GenAI ÷ü¿¶·²ÿ¶¹ ºö¶ü
   óúö³
8. w ClickHouse y³zo”w¿¼vt»ÿ ß1ClickHouse ~oÿöÚ{yßt³wy2ïO~yyv|këz³wyÿ ú²û¿´ðV÷ Àó·õüüù´ÖŠ÷vwú²û¿´ð{÷ü¿²ÿŸW2öü¶ü ~¯×²}~|wV÷w1sÿ{o}~Û{{{~y2 øÿ / GenAI ù¿øû}´{ÿ߸ó¸û²úóÀ{o2/kzÿ÷ü¿~ _÷v²¿Ÿ{߯w}~y2 ÷ü¿¶·²ÿ¶¹ BI ôüû´üýüö³óÀ~ÿöxwv2ßg~DWH xyvwRöz ³¹ø
   õ¹üþó¹²ßÿw~y2 ºö¶ü
   óúö³ ýÀ1øüü¹1ñøú¿¹~}\ûv2·¹öð~uyg²~f÷Ÿ w1_ܲsÿ{}÷w}~y2 x: E. yyvkëÿ ï ClickHouse ~÷ßgxgý~¿u|1s¼{y[ùzo”²ÿý{wvt~y2
9. ' |r›xv{wtsx ¿´º²šxv1ClickHouse ~ÿo²tvët€qww·v{2ssw1Îo~²´¹öü´¿w{{›xvztv{wtý´óø²~x±~y2s¼¹1Îo~LT »ó·÷ów²~ºßw |y»Ý‰z½üÿüùwy2 ClickHouse = Ÿú²û¿´ðV÷ DB 1SQL 1÷ü¿w~ WgU¹øüü¸xù¿øûW߯{¸º1ßg~RDBMS ²wRy»Ÿþ²ßÿ yß÷ü¿²" ûw" sx» u?U<uW¯~÷ü¿²1µó÷úóÀzww¿ŸÖŠ |ÿý ^}o~DN/EC/ šï/ ²üð/ ¯/ ý °w¿¼vt» Fortune 500 Om{¹õŠ¹¿üø²ó÷~w1_ßtm} wãoß~rº  Îo~LT ²s~o‰wûw~yÿ s¼{¹~»ó·÷ów1ߟ~^/ÏÏ´w_özñõ¹üþó¹ùóñþü¿1²ü½ö¿ñó~û}zy1¸ºýtÕû{¿³wt}~y2ClickHouse |z|s¼{yìou¼v t»~{1}~vu²u¹{ûºOrvt}~w·v2þÙttwy{ÿ
10. }·v~´üûÿ‰ù
    oo<³z´uw=~³ó»÷ø{ÿsv1BigQuery xClickHouse ~q[ñ¿ üó²zûw~y2âÛöxßýöz²÷ýüñ²ðùó¹¸z^qw~y2 01 z|" yß" |û~{ µó÷úóÀ_ó~½xw²vëw1 õûõ³÷úö³÷ü¿~ït²Ýú w~y2ü²žû~}û{¹\bóW~ w1¿“{zt·Àúû²kx»Ý‰g ²ë®w~y2 02 BigQuery xClickHouse ~q[ ñ¿üó ÿwzo~Ÿùw1}¼~¼~ ²{y²ü½ö¿ñó²}Ïw~ y2÷õ²û·ó¿1Hot-Cold ³~z y1ßýöz3 t~ñ¿üó²s~y2 03 O11y× ó¸ý¹g{~ßÍ´ñü¸ SLO xKPI ²ö}u{»w_öz¯Š?ý²ë®w~y2trace_id {¹session_id 1 order_id ~w~¹ö³óñ¯Šx1ïtùü¹~²ùüøßͲ}Ïw~y2
11. yß÷ü¿V÷wz»O11y Cross Data Platforms Meetup #2 ÿBigQuery {Ö
    | 15 V LT
12. ÌoÿoowBigQuery |V÷ ~/o} BigQuery ~ BigQuery DWH xwv~÷ßzSQL ÷vý?1 ý/¹±üûx~ÿß?1}wvõûþýü¸ ù~Ûog|g/~ß?wy2÷ü¿¶·²ÿ¶ ¹~ÿöxwv1u~÷ü¿½ü¹²}\w1 ÿözV÷ÿö²ëûw}~y2 /ow1Oü´öó·~㿸ú´1¿» þ'yßöüñøú~sf÷v{1w~²÷ ýüñ|ûÿöz|¯²[w~y2 ÿ´öúóù~ït
    óñ÷v1ãöV÷1s fg~‰n44 s¼¹²/t~ ·¹öðwgùWy»~ö ÷wy2 rVç{¸svït²g/ Wy»sxw1}¼~¼~÷ ùóøõ¹üð~²{ w1y_xwv~ÿ»ng² UNu{»sx|w}~y2
13. z|" yß" ÿÿFull-fidelity ~ït
    µó÷úóÀû÷öwy|1݉z·Àúû²¿“yú¹¿²t~y2yß÷ü¿² ßcy»sxw1ïO~¸vzït²ßÿw}~y2 ü²žû~}û ~o»þ|Otžû´1{Û~}\ ¿{ÿOS× wß× öúóø
    w~~o y»_ܲ׺“|w~{³2µó÷ úóÀw¿xzt\bözóW²k ú{wkw}~y2 ó¸ý¹´óñ¿ø~sfþ · SLO ¸±xúNÿÿCVR 1ARPU 1ö ±÷
    ²1 Vþþwþ·V÷w}~ y2âÛöz_Ü|ó¸ý¹KPI {yv ÿy»{²1ú²û¿´ð{êãw }~y2 ÷ÿxÝÿg~çß Ïßë÷1³ó÷ù´²ó¹÷ÿ1øÿò÷û~Ýÿ{ztv1ßyz÷ü¿ »óø|߉wy2yßßc{¸º1Þû~ÿo~fù²kú{Ýÿw}~y2
14. rVç~ƒxo ÿComplementary
    BigQuery xClickHouse þ\wz1þÞÜßöz·Ï{rº~y2}¼~¼~² vëw1ùW{tVq»sxw1y_gù²ßÿw~y2 BigQuery ÿDWH
    DWH/ELT ~ÿöÿÿß[x[oözV÷ÿöxwvý »þóö³ó¿|ÿ÷ü¿ò÷úóÀxó¸ý¹ý¸ó¿~Ö} BI/ML ¸³·¹öðÿLooker 1Vertex AI zyx~·üðü¹z³~ /k
    óñ֊ÿú¿ð´ø¹Áüû~÷ü¿÷vx³¹øgùW ClickHouse ÿReal-time Analytics
    ¿Ÿ´ó¸·¹øÿýÀ1ñøú¿¹1øüü¹²û[Ow׺¿ Oü´öó·ãÿ²ùû󿿸ú|uûwßÎ yßßc× ¿w~ÿ÷ÿxRCA ÿyoßöV÷
    {gùW sfþ·V÷ÿO11y ÷ü¿xó¸ý¹´ùóø²ú²û¿´ðw}\
15. BigQuery×ClickHouse ÿo²ü½ ö¿ñó3 ñ¿üó ßÛow1÷ü¿~{gx‰þ{ßxvïO~3 t~ñ¿üó²tVq~y2÷ü¿ k1³¹ø1ü´öó·‰þ{¸svgùzøþ|Y¿º~y2 ñ¿üó1 ÿ÷õ²û'·ó¿ Kafka/OpenTelemetry ³ü¿¿ü{¹1BigQuery xClickHouse x\f{÷ü¿² ÷/w~y2ú²û¿´ðg|g²¿1o·¹öðwþw¿¸ú|ÿýw y2÷ü¿ß|[t|\³¹ø{ìo|߉wy2 ñ¿üó2 ÿHot ³Cold ³~ ClickHouse wöß~ûóø÷ü¿²¿Ÿ÷vw1Ûö{GCS x¸¿¹ýüø ß1BigQuery xýüùw~y2ClickHouse uo<u±—~ûóø÷ü¿1 BigQuery ÿ²ü»´öxjíV÷²ç_w~y2 ñ¿üó3 ÿDWH ^ÿµþúÝï BigQuery w֊'ò/wµþú÷ü¿~²ClickHouse xÝïw~y2Àó· õüüùo~Ï_֊÷ü¿²ClickHouse w½óó·õw1Oü´öó·wóß w~y2÷ü¿î‘ß²g/Ww}~y2
16. O11y× ó¸ý¹~½ü¯ŠÿID ¹ö³óÁ
    }\ID ~¯Š ÿ»ng÷ü¿xó¸ý¹´ùóø²}stq»±{1qš~½ü¯Š|Oÿowy2 ‰zjoin ½üÿ trace_id 4 Vgøüü·óÀ~Ûw/ session_id 4 öü¶ü»ó·÷ó user_id 4 öü¶üÛw/ order_id 4 ìoøù󶿷÷ó tenant_id 4 þûñöúóøôÛ ts 4 ¿´ð¹¿ó÷ÿ1 Vþþw~}\
    ]W'þ¹½óÀ PII ÿ[/Ûwåy
    Vööüöûwûvw1g/kÖ~ßW{ÿutv²¿»¹²v ÿw~y2 1 Vþþ~þöú²ù´ºùóõü SLO×KPI ²~fŠÿw1Grafana 1Looker 1HyperDX zy~Àó·õüüùxú²û¿ ´ðÝïw~y2
17. ïtùü¹~²ùüøÿNoise ²Z»
    âÛöz¿t€qw²ùüø²ûyx1þ´º|[1݉z_ܲ¿“w~y2ó¸ý¹´óñ¿øxâÛg{²}\¿{»sxw1ßÛowû²ùüø²ßÿw~y2 1 gþA ÿâÛö¿t p95 ü´öó· > 1200ms {t ¸ùü÷ > 2% ñõ¹üþó¹óW²}÷w~y|1s¼€qwó¸ý¹x~ÿOnwy2 2 gþB ÿó¸ý¹¿t ²¿ö³ö»üøu > 500 ~ úN/ V > 10 O× ó¸ý¹¿úö³»ûzöí²vÛw~y2øùõ³ó¿|]ztf—~_Ü{ Yþ|Otÿýg|rº~y2 3 š÷ÿA AND B oo~gþ²îw|\~1PagerDuty ´Slack xš÷w~y2 ®}÷²90% Zw1ñüð~÷_²¶~~y2
18. µó÷ûSQL ÿIncident ~" úNÿ" ²1 Vþþwêã ClickHouse w1žû—o~ñõ¹üþó¹ñøú¿¹xó¸ý¹KPI ²1 V[Owþ·V÷y»¿¸úwy2p95 ü´öó·1¸ùüu1ñ·ó¿²¶øŸÛu1 rßu1úN1CVR ²/þ{×ßw}~y2 WITH incident AS ( SELECT parseDateTimeBestEffort('2025-10-01 18:00') AS t_start, parseDateTimeBestEffort('2025-10-01 18:15') AS t_end ), perf AS ( SELECT toStartOfMinute(ts) AS m, quantilesExactWeighted(0.5,0.95)(latency_ms,1)[2] AS p95_ms, countIf(status>=500) AS err, count() AS req FROM o11y_requests WHERE service='checkout' AND ts BETWEEN (SELECT t_start FROM incident) AND (SELECT t_end FROM incident) GROUP BY m ), biz AS ( SELECT toStartOfMinute(event_time) AS m, countIf(event='checkout_start') AS starts, countIf(event='checkout_success') AS success, sumIf(amount, event='checkout_success') AS revenue FROM app_events WHERE event_time BETWEEN (SELECT t_start FROM incident) AND (SELECT t_end FROM incident) GROUP BY m ) SELECT p.m, p.p95_ms, p.err, p.req, b.starts, b.success, b.revenue, (b.success / nullIf(b.starts,0)) AS cvr FROM perf p LEFT JOIN biz b USING (m) ORDER BY p.m; s~¿¸ú{¸º1 <18:05 <18:10 ~—1p95 |2 û{ÿ}N|º1CVR |65% ³45% {OOw15 V—wëÛ30 O×~_my=xtsw_özV÷|ÿý{z º~y2
19. ³¹ø×SLA ~ÿßë yß÷ü¿ßc¿³¹ø{¿x~y|1w~âÛ1¹½üþ¯Š1TTL/ ž|W²ùW{}\¿{»sxw1ßoöz³¹ø{óx¹¼~y2 ûóø|ÿClickHouse
    öß7 <30 o~yß÷ü¿²ßc2û[O~ 㿸ú{ÿßw1ú²û¿´ðÀó· õüüùx²ùüø²ox~y2 ¶¹üð/ ³üûù|ÿBigQuery
    }¼ï_~÷ü¿1GCS }uwBigQuery xû¯2jíV÷1Ýÿ1³ó÷ù´² ó¹÷ÿ{ÿßw~y2 w~×TTL gùW ClickHouse ~Codec xñüö³·÷ó¯Š{ ¸º110:1 <50:1 ~w~÷²ßÿ2O‰z÷ ü¿¯×Zöw~y2 ïtß×~_›¯Š <¿»ït|r»÷ü¿²1¿tx} {1¿¹¼»=_›²1g/³¹øwßÿ w~y2
20. ^/¹öó÷ÿ~zss{¹
    yß÷ü¿V÷ÿö1/þ{yyv²ëûy»ß‰rº~{³2¿ïtz˜ß{¹ÿžö{^/w1 ´óñ¿ø²ÿŸWwz|¹ã/wvtsx|rß~÷wy2 Step 1 ÿ¿ïtñ¹{õ¹ü»¹ }³WÕû}³~nxts1úN{ö}y»öü¶üõýü²g_~¿ü²óø{w~y2 y_~20% ~ý|80% ~ó¸ý¹ït²o°±ü¹|[t±1û÷ö{rÿ²û{~y2 Step 2 ÿID ¹ö³óÁ~ú trace_id 1session_id 1order_id ~³~²úw1âÛ¹¿ó¿xó¸ý¹´ùóø²}Þq ~y2s~ÿö|ßÿ~yyv~V÷²ox~y2 Step 3 ÿ1 VMV w~fŠÿ SLO×KPI ~þöú²ù´ºùóõü²ëûw1Grafana 1Looker 1HyperDX zy~Àó·õ üüùxú²û¿´ðÝïw~y2?×֊{¹¯×Wxû¯w~y2 Step 4 ÿïtùü¹²ùüø÷/ g_1 opq1gþA AND B ß~²ùüø²^/w1±küýüøÛowûÿ²nÛ w~y2®}÷Zxÿßf—ÿ~²ÛßWw1j]Ÿ~yâxw~y2
21. ~x±ÿÿwz" oÿq" w ïtg/W r~núW BigQuery DWH/ »þóö³ó¿|xw vÿV÷²ç_2ClickHouse yßú ²û¿´ðãwsfg²óßw~y2 O11y× ó¸ý¹~\ âÛg{xó¸ý¹KPI ²þ·u{»sx w1 <úNÿùü¹=~SRE Ûox²W w~y2 /uÛ±v´óñ¿øÿŸW ñ·ó¿²¶ø˜ßzy1¿ït¸ú²{¹ŸÛw1rÿ²ÛßWwz|¹ÿžö{ã/ w~y2 tŠw|r¼p1PoC ~to÷´{wz}¾¿ü¸óøÿ —²tóßw~yÿ |rzÿ{zø|q€ut2zu~~÷ý¸·¿ø~ rß²µýüøu{vt€}~y2