Downloaded 85 times
![What we want to do
COUNT PV,UU and others (daily/realtime)
COUNT Service metrics (daily/hourly/realtime)
FIND Surprising Errors [4xx,5xx] (immediately)
CHECK Response Times (immediately)
SERCH Logs in troubles (hourly/immediately)
VISUALIZE/NOTIFY App Status (realtime)
13年11月7日木曜日](https://image.slidesharecdn.com/clouderaworldtokyo2013-131107022710-phpapp02/85/Batch-and-Stream-processing-with-SQL-12-320.jpg)
![Norikra Queries: (5)
{“name”:”tagomoris”,
“user:{“age”:34, “corp”:”LINE”,
“address”:”Tokyo”},
“current”:”Meguro”,
“speaker”:true,
“attend”:[true,true,false, ...]
}
SELECT age, COUNT(*) as cnt
FROM events.win:time_batch(5 mins)
GROUP BY age
13年11月7日木曜日](https://image.slidesharecdn.com/clouderaworldtokyo2013-131107022710-phpapp02/85/Batch-and-Stream-processing-with-SQL-37-320.jpg)
![Norikra Queries: (5)
{“name”:”tagomoris”,
“user:{“age”:34, “corp”:”LINE”,
“address”:”Tokyo”},
“current”:”Meguro”,
“speaker”:true,
“attend”:[true,true,false, ...]
}
SELECT user.age, COUNT(*) as cnt
FROM events.win:time_batch(5 mins)
GROUP BY user.age
13年11月7日木曜日](https://image.slidesharecdn.com/clouderaworldtokyo2013-131107022710-phpapp02/85/Batch-and-Stream-processing-with-SQL-38-320.jpg)
![Norikra Queries: (5)
{“name”:”tagomoris”,
“user:{“age”:34, “corp”:”LINE”,
“address”:”Tokyo”},
“current”:”Meguro”,
“speaker”:true,
“attend”:[true,true,false, ...]
}
SELECT user.age, COUNT(*) as cnt
FROM events.win:time_batch(5 mins)
WHERE current=”Meguro” AND attend.$0 AND attend.$1
GROUP BY user.age
13年11月7日木曜日](https://image.slidesharecdn.com/clouderaworldtokyo2013-131107022710-phpapp02/85/Batch-and-Stream-processing-with-SQL-39-320.jpg)
![[db tech showcase Tokyo 2014] B22: Hadoop Rush!! HDFSからデータを自在に取得、加工するにはどうする？ ...](https://cdn.slidesharecdn.com/ss_thumbnails/dbtstokyo2014b22hadooprushhdfs-141119235245-conversion-gate01-thumbnail.jpg?width=640&height=640&fit=bounds)
Batch and Stream processing with SQL
- 1. Batch and Streamprocessing with SQL 2013/11/07 Cloudera World Tokyo 2013 TAGOMORI Satoshi @tagomoris LINE Corp. 13年11月7日木曜日
- 2. SQL、宇宙、すべての答え SELECT 42 FROManywhere 13年11月7日木曜日
- 3. TAGOMORI Satoshi (@tagomoris) LINECorp. Hadoop, Fluentd, Norikra, ... 13年11月7日木曜日
- 4.
- 5.
- 6.
- 7.
- 8.
- 9. How to writeM/R (or Storm app, or...) Java (or Scala, Clojure, JRuby, ...) Hadoop Streaming Pig Hive, Impala (SQL!) 13年11月7日木曜日
- 10. Our log traffic Daily 2.1+TB (non compressed) 6.8+ Billion lines / day Peak time 150,000+ lines / sec 380+ Mbps 13年11月7日木曜日
- 11. Our Hadoop cluster CDH4.2.0 Master Nodes: 3 (NameNodeHA+QJM) NameNode, JournalNode, JobTracker Slave Nodes: 20 13年11月7日木曜日
- 12. What we wantto do COUNT PV,UU and others (daily/realtime) COUNT Service metrics (daily/hourly/realtime) FIND Surprising Errors [4xx,5xx] (immediately) CHECK Response Times (immediately) SERCH Logs in troubles (hourly/immediately) VISUALIZE/NOTIFY App Status (realtime) 13年11月7日木曜日
- 13. Batches and Streams Hadoopis for batches High performance batch is important HDFS has good performance Stream log writing and calculations are also VERY VERY IMPORTANT Hybrid System: Stream processing + Batch 13年11月7日木曜日
- 14. System Overview Web Servers Archive Storage (scribed) Fluentd Cluster STREAM Notifications (IRC) Fluentd Watchers Graph Tools Norikra webhdfs Hadoop Cluster (HDFS,MR) 13年11月7日木曜日 hive server Huahin Manager BATCH SCHEDULED BATCH Shib ShibUI
- 15. Data analytics players PROGRAMMER RawLog Formats Application Logs Data Sizes Data Semantics SERVICE DIRECTOR SALES Whatever Metrics They Want Storages Hadoop Cluster Visualization Tools ADMINISTRATOR ........ BOARD MEMBER 13年11月7日木曜日
- 16. Data analytics players PROGRAMMER RawLog Formats Application Logs Data Sizes Data Semantics SERVICE DIRECTOR SALES WE NEED THE QUERY LANGUAGE Whatever Metrics They Want WHAT THEY ALL CAN RUN AND UNDERSTAND!!!!!!!!!! Storages Hadoop Cluster Visualization Tools ADMINISTRATOR ........ BOARD MEMBER 13年11月7日木曜日
- 17.
- 18.
- 19. Hive SQL: w/o compile,w/o deployment HiveServer: w/o server login Shib: Select only 13年11月7日木曜日
- 20.
- 21. Hive: Simplify versioning problems Hive0.10 of CDH 4.2.0 Upgrade CDH for only Hive version 13年11月7日木曜日
- 22. Hive: Pros Many Scheduled Queries Metrics OnDemandQueries 13年11月7日木曜日
- 23. Hive: Cons Too Many ScheduledQueries for short time window 13年11月7日木曜日
- 24. Stream processing Queries forfixed Window every 1hour, 10minutes, 1minutes, ... latest 10evens, ... all events Once query registered, Runs forever Results appear automatically NO MORE STORAGES 13年11月7日木曜日
- 25.
- 26. Norikra: Schema-less Stream Processing withSQL 13年11月7日木曜日
- 27. Norikra(1): Schema-less event stream: Add/Removedata fields whenever you want SQL: No more restarts to add/remove queries w/ JOINs, w/ SubQueries w/UDF Truly Complex events: Nested Hash/Array, accessible directly from SQL 13年11月7日木曜日
- 28. Norikra(2): Open source software: Licensedunder GPLv2 Based on Esper UDF plugins from rubygems.org Ultra-fast bootstrap & small start: 3mins to install/start 1 server 13年11月7日木曜日
- 29. Norikra Queries: (1) SELECTname, age FROM events 13年11月7日木曜日
- 30. Norikra Queries: (1) {“name”:”tagomoris”, “age”:34,“address”:”Tokyo”, “corp”:”LINE”, “current”:”Meguro”} SELECT name, age FROM events {“name”:”tagomoris”,”age”:34} 13年11月7日木曜日
- 31. Norikra Queries: (1) {“name”:”tagomoris”, “address”:”Tokyo”, “corp”:”LINE”,“current”:”Meguro”} SELECT name, age FROM events nothing 13年11月7日木曜日
- 32. Norikra Queries: (2) {“name”:”tagomoris”, “age”:34,“address”:”Tokyo”, “corp”:”LINE”, “current”:”Meguro”} SELECT name, age FROM events WHERE current=”Meguro” {“name”:”tagomoris”,”age”:34} 13年11月7日木曜日
- 33. Norikra Queries: (2) {“name”:”frsyuki”, “age”:25,“address”:”MountainView”, “corp”:”TD”, “current”:”BayArea”} SELECT name, age FROM events WHERE current=”Meguro” nothing 13年11月7日木曜日
- 34. Norikra Queries: (3) SELECTage, COUNT(*) as cnt FROM events.win:time_batch(5 mins) GROUP BY age 13年11月7日木曜日
- 35. Norikra Queries: (3) {“name”:”tagomoris”, “age”:34,“address”:”Tokyo”, “corp”:”LINE”, “current”:”Meguro”} SELECT age, COUNT(*) as cnt FROM events.win:time_batch(5 mins) GROUP BY age every 5 mins {”age”:34,”cnt”:3}, {“age”:33,”cnt”:1}, ... 13年11月7日木曜日
- 36. Norikra Queries: (4) {“name”:”tagomoris”, “age”:34,“address”:”Tokyo”, “corp”:”LINE”, “current”:”Meguro”} SELECT age, COUNT(*) as cnt FROM events.win:time_batch(5 mins) GROUP BY age SELECT max(age) as max FROM events.win:time_batch(5 mins) every 5 mins {”age”:34,”cnt”:3}, {“age”:33,”cnt”:1}, ... {“max”:51} 13年11月7日木曜日
- 37. Norikra Queries: (5) {“name”:”tagomoris”, “user:{“age”:34,“corp”:”LINE”, “address”:”Tokyo”}, “current”:”Meguro”, “speaker”:true, “attend”:[true,true,false, ...] } SELECT age, COUNT(*) as cnt FROM events.win:time_batch(5 mins) GROUP BY age 13年11月7日木曜日
- 38. Norikra Queries: (5) {“name”:”tagomoris”, “user:{“age”:34,“corp”:”LINE”, “address”:”Tokyo”}, “current”:”Meguro”, “speaker”:true, “attend”:[true,true,false, ...] } SELECT user.age, COUNT(*) as cnt FROM events.win:time_batch(5 mins) GROUP BY user.age 13年11月7日木曜日
- 39. Norikra Queries: (5) {“name”:”tagomoris”, “user:{“age”:34,“corp”:”LINE”, “address”:”Tokyo”}, “current”:”Meguro”, “speaker”:true, “attend”:[true,true,false, ...] } SELECT user.age, COUNT(*) as cnt FROM events.win:time_batch(5 mins) WHERE current=”Meguro” AND attend.$0 AND attend.$1 GROUP BY user.age 13年11月7日木曜日
- 40. Before: Hive EVERY HOUR! SELECT yyyymmdd,hh, campaign_id, region, lang, count(*) AS click, count(distinct member_id) AS uu FROM ( SELECT yyyymmdd, hh, get_json_object(log, '$.campaign.id') AS campaign_id, get_json_object(log, '$.member.region') AS region, get_json_object(log, '$.member.lang') AS lang, get_json_object(log, '$.member.id') AS member_id FROM applog WHERE service='myservice' AND yyyymmdd='20131101' AND hh='00' AND get_json_object(log, '$.type') = 'click' ) x GROUP BY yyyymmdd, hh, campaign_id, region, lang 13年11月7日木曜日
- 41. After: Norikra SELECT campaign.id AScampaign_id, member.region AS region, count(*) AS click, count(distinct member.id) AS uu FROM myservice.win:time_batch(1 hours) WHERE type="click" GROUP BY campaign.id, member.region 13年11月7日木曜日
- 42. Norikra: Current Status v0.1.0:Released at 2013/11/01 by tagomoris http://norikra.github.io/ Documents: under development Just started to use in production 13年11月7日木曜日
- 43. SQL Queries for batches forstreams 13年11月7日木曜日
- 44. 企画・開発 幅広く募集中 • データマーケティング •データベースエンジニア • BI企画・開発 • etc… 13年11月7日木曜日
- 45.
- 46.
- 47. See Also: Log analysissystem with Hadoop in livedoor 2013 http://www.slideshare.net/tagomoris/log-analysis-with-hadoop-in-livedoor-2013 Norikra http://norikra.github.io/ https://github.com/norikra Shib https://github.com/tagomoris/shib Fluentd http://fluentd.org/ https://github.com/fluent/fluentd 13年11月7日木曜日