https://cnfl.io/qcon-london-workshop Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! ® @rmoff [email protected]
Slide 1
Slide 2
@rmoff https://cnfl.io/qcon-london-workshop • Make sure you allocate Docker >=8GB memory docker system info | grep Memory • Clone the repo • Pull the git images as instructed in the doc https://cnfl.io/start-ksql-workshop 3. Start Confluent Platform Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 3
@rmoff What is an Event Streaming Platform? Producer Connectors Consumer The Log Connectors Streaming Engine Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 4
@rmoff Immutable Event Log Old New Messages are added at the end of the log Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 5
@rmoff Consumers have a position all of their own New Old Sally is here Scan Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 6
@rmoff Consumers have a position all of their own New Old Fred is here Scan Sally is here Scan Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 7
@rmoff Consumers have a position all of their own George is here Scan New Old Fred is here Scan Sally is here Scan Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 8
@rmoff The Connect API Producer Connectors Consumer The Log Connectors Streaming Engine Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 9
@rmoff Streaming Integration with Kafka Connect syslog flat file CSV JSON Sources MQTT Tasks Workers Kafka Connect Kafka Brokers Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 10
@rmoff Streaming Integration with Kafka Connect Amazon S3 Sinks MQTT Tasks Workers Kafka Connect Kafka Brokers Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 11
@rmoff Streaming Integration with Kafka Connect Amazon S3 syslog flat file CSV JSON Sources Sinks MQTT MQTT Tasks Workers Kafka Connect Kafka Brokers Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 12
Stream Processing in Kafka Producer Connectors @rmoff Consumer The Log Connectors Streaming Engine Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 13
@rmoff Kafka Streams API final StreamsBuilder builder = new StreamsBuilder() .stream(“orders”, Consumed.with(stringSerde, ordersSerde)) .filter( (key, order) -> order.getStatus().equals(“COMPLETE”) ) .to(“complete_orders”, Produced.with(stringSerde, ordersSerde)); Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 14
Stream Processing with KSQL @rmoff CREATE STREAM completedOrders AS SELECT * FROM orders WHERE status=’COMPLETE’; Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 15
@rmoff A bit of a mess… App App App App cache monitoring cache MQ DWH security MQ search Hadoop Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 16
@rmoff Kafka is a Streaming Platform App App App App request-response changelogs App App KAFKA App App DWH Hadoop messaging OR stream processing streaming data pipelines Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 17
@rmoff Analytics - Database Offload RDBMS CDC HDFS / S3 / BigQuery etc Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 18
@rmoff Stream Processing with Apache Kafka and KSQL order events CDC RDBMS customer orders customer Stream Processing Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 19
Real-time Event Stream Enrichment @rmoff order events customer orders C D C RDBMS <y> customer Stream Processing Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 20
@rmoff Transform Once, Use Many order events customer orders C D C RDBMS <y> customer Stream Processing New App <x> Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 21
@rmoff Transform Once, Use Many order events customer orders C D C RDBMS <y> HDFS / S3 / etc customer Stream Processing New App <x> Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 22
@rmoff Let’s Build It! Rating events App a k f a K t c e n n o C App u s n o C uc e rA PI Kafka Connect a fk t Ka ec n RDBMS I P A r e m Operational Dashboard Elasticsearch n Co User data Pro d Push notification KSQL Join events to users, and filter Data Lake SnowflakeDB/ S3/HDFS/etc Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 23
@rmoff Confluent Community Components Apache Kafka with a bunch of cool stuff! For free! Log Events Database Changes loT Data Web Events … Confluent Platform Data Integration Real-time Applications Monitoring & Administration Confluent Control Center | Security Confluent Platform Transformations Hadoop Operations Replicator | Auto Data Balancing Custom Apps Database Data Compatibility Schema Registry SQL Stream Processing KSQL Data Warehouse Development and Connectivity Clients | Connectors | REST Proxy | CLI CRM Monitoring Apache Kafka® Core | Connect API | Streams API … CUSTOMER SELF-MANAGED Datacenter Public Cloud Analytics … CONFLUENT FULLY-MANAGED Confluent Cloud Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 24
KSQL Push notification to Slack Rating events App Kafka Connect a fk t Ka ec n RDBMS u s n o C uc e rA PI a k f a K t c e n n o C ratings App Operational Dashboard Elasticsearch n Co User data Pro d I P A r e m @rmoff poor_ratings Data KSQL Filter events Lake S3/HDFS/ SnowflakeDB etc Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 25
@rmoff KSQL is the Streaming SQL Engine for Apache Kafka Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 26
Filter messages with KSQL @rmoff completedOrders orders → → → → → → → → → → → 01, £10.00, 05, £10.00, 06, £24.00, 02, £12.33, 04, £5.50, → COMPLETE COMPLETE COMPLETE PENDING COMPLETE CREATE STREAM completedOrders AS SELECT * FROM orders WHERE status=’COMPLETE’; → → → → → → → → → → → 01, £10.00, 06, £24.00, 02, £12.33, 04, £5.50, → COMPLETE COMPLETE COMPLETE COMPLETE Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 27
@rmoff Drop columns with KSQL customer → → → → → → → → → → →→ {“id”:1, {“id”:2, {“id”:3, “name”:”Dana Lidgerton”, “name”:”Milo Wellsman”, “name”:”Dolph Cleeton”, “card”:”5048370182840140} “card”:”3557977885537506} “card”:”3586303633007251} CREATE STREAM customerNoCC AS SELECT ID, NAME customerNoCC FROM customer; → → → → → → → → → → →→ {“id”:1, {“id”:2, {“id”:3, “name”:”Dana Lidgerton”} “name”:”Milo Wellsman”} “name”:”Dolph Cleeton”} Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 28
Stateful aggregation with KSQL @rmoff customersByCountry customer → → → → → → → → → → →→ {“id”:1, {“id”:2, {“id”:3, “name”:”Dana Lidgerton”, “name”:”Milo Wellsman”, “name”:”Dolph Cleeton”, “country”:”UK”} “country”:”UK”} “country”:”Germany”} CREATE STREAM customersByCountry AS SELECT country, COUNT(*) AS customerCount FROM customer WINDOW TUMBLING (SIZE 1 HOUR) GROUP BY country; → → → → → → → → → → →→ {“country”:”UK”, {“country”:”Germany”, “customerCount”:2} “customerCount”:1} Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 29
KSQL for Anomaly Detection @rmoff Identifying patterns or anomalies in real-time data, surfaced in milliseconds CREATE TABLE possible_fraud AS SELECT card_number, count() FROM authorization_attempts WINDOW TUMBLING (SIZE 5 SECONDS) GROUP BY card_number HAVING count() > 3; Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 30
KSQL for Data Transformation @rmoff Make simple derivations of existing topics from the command line CREATE STREAM pageviews WITH (PARTITIONS=4, VALUE_FORMAT=’AVRO’) AS SELECT * FROM pageviews_json; Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 31
KSQL for Streaming ETL @rmoff Joining, filtering, and aggregating streams of event data CREATE STREAM vip_actions AS SELECT userid, page, action FROM clickstream c LEFT JOIN users u ON c.userid = u.user_id WHERE u.level = ‘Platinum’; Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 32
@rmoff KSQL in Development and Production Interactive KSQL for development and testing Headless KSQL for Production REST Desired KSQL queries have been identified “Hmm, let me try out this idea…” Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 33
{ “rating_id”: 5313, “user_id”: 3, “stars”: 4, “route_id”: 6975, “rating_time”: 1519304105213, “channel”: “web”, “message”: “worst. flight. ever. #neveragain” Filter all ratings where STARS<3 @rmoff POOR_RATINGS } Producer API CREATE STREAM POOR_RATINGS AS SELECT * FROM ratings WHERE STARS <3 Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 34
@rmoff https://cnfl.io/start-ksql-workshop 4. KSQL 5. Querying and filtering streams of data 6. Creating a Kafka topic populated by a filtered stream Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 35
Let’s Build It! Rating events App a k f a K t c e n n o C App u s n o C uc e rA PI Kafka Connect a fk t Ka ec n RDBMS I P A r e m Operational Dashboard Elasticsearch n Co User data Pro d Push notification to Slack @rmoff Join events to users, and filter Data Lake SnowflakeDB/ S3/HDFS/etc Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 36
Kafka Connect Rating events App a k f a K t c e n n o C App u s n o C uc e rA PI Kafka Connect a fk t Ka ec n RDBMS I P A r e m Operational Dashboard Elasticsearch n Co User data Pro d Push notification to Slack @rmoff Join events to users, and filter Data Lake SnowflakeDB/ S3/HDFS/etc Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 37
@rmoff Streaming Integration with Kafka Connect Amazon S3 syslog flat file CSV JSON Sources Sinks MQTT MQTT Tasks Workers Kafka Connect Kafka Brokers Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 38
@rmoff Kafka Connect Reliable and scalable integration of Kafka with other systems – no coding required. ✓ Centralized management and configuration ✓ Fault tolerant and automatically load balanced ✓ Support for hundreds of technologies ✓ Extensible API including RDBMS, Elasticsearch, HDFS, S3 ✓ Supports CDC ingest of events from RDBMS ✓ Preserves data schema ✓ Single Message Transforms ✓ Part of Apache Kafka, included in Confluent Open Source { “connector.class”: “io.confluent.connect.jdbc.JdbcSourceConnector”, “connection.url”: “jdbc:mysql://localhost:3306/demo?user=rmoff&password=foo”, “table.whitelist”: “sales,orders,customers” } https://docs.confluent.io/current/connect/ Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 39
@rmoff Kafka Connect + Schema Registry = WIN Avro Schema Schema Registry Elasticsearch RDBMS Kafka Connect Avro Message Kafka Connect Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 40
@rmoff Kafka Connect + Schema Registry = WIN Avro Schema Schema Registry Elasticsearch RDBMS Kafka Connect Avro Message Kafka Connect Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 41
Confluent Hub @rmoff • One-stop place to discover and download : • Connectors • Transformations • Converters hub.confluent.io Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 42
@rmoff Demo Time! Producer API MySQL t c e n n o C a k f Ka m u i z e b e D Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 43
@rmoff Do you think that’s a table you are querying? Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 44
Time The Table Stream Duality Stream Account ID Amount 12345 + €50 12345
- €25 12345 -€60 @rmoff Account ID Balance Table 12345 €50 Account ID Balance 12345 €75 Account ID Balance 12345 €15 Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 45
The truth is the log. The database is a cache of a subset of the log. —Pat Helland Immutability Changes Everything http://cidrdb.org/cidr2015/Papers/CIDR15_Paper16.pdf Photo by Bobby Burch on Unsplash
Slide 46
@rmoff { “rating_id”: 5313, “user_id”: 3, “stars”: 4, “route_id”: 6975, “rating_time”: 1519304105213, “channel”: “web”, “message”: “worst. flight. ever. #neveragain” } Producer API Join each rating to customer data RATINGS_WITH_CUSTOMER_DATA t c e n n o C a k f a K { “id”: 3, “first_name”: “Merilyn”, “last_name”: “Doughartie”, “email”: “[email protected]”, “gender”: “Female”, “club_status”: “platinum”, “comments”: “none” CREATE STREAM RATINGS_WITH_CUSTOMER_DATA AS SELECT * FROM RATINGS LEFT JOIN CUSTOMERS ON R.ID=C.ID; } Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 47
@rmoff { “rating_id”: 5313, “user_id”: 3, “stars”: 4, “route_id”: 6975, “rating_time”: 1519304105213, “channel”: “web”, “message”: “worst. flight. ever. #neveragain” } Producer API t c e n n o C a k f a K Join each rating to customer data RATINGS_WITH_CUSTOMER_DATA Filter for just PLATINUM customers UNHAPPY_PLATINUM_CUSTOMERS { “id”: 3, “first_name”: “Merilyn”, “last_name”: “Doughartie”, “email”: “[email protected]”, “gender”: “Female”, “club_status”: “platinum”, “comments”: “none” CREATE STREAM UNHAPPY_PLATINUM_CUSTOMERS AS SELECT * FROM RATINGS_WITH_CUSTOMER_DATA WHERE STARS < 3 } Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 48
{ “rating_id”: 5313, “user_id”: 3, “stars”: 4, “route_id”: 6975, “rating_time”: 1519304105213, “channel”: “web”, “message”: “worst. flight. ever. #neveragain” @rmoff CREATE TABLE RATINGS_BY_CLUB_STATUS AS SELECT CLUB_STATUS, COUNT(*) Join each rating to customer data FROM RATINGS_WITH_CUSTOMER_DATA Producer API RATINGS_WITH_CUSTOMER_DATA WINDOW TUMBLING (SIZE 1 MINUTES) GROUP BY CLUB_STATUS; } t c e n n o C a k f a K { “id”: 3, “first_name”: “Merilyn”, “last_name”: “Doughartie”, “email”: “[email protected]”, “gender”: “Female”, “club_status”: “platinum”, “comments”: “none” } Aggregate per-minute by CLUB_STATUS RATINGS_BY_CLUB_STATUS_1MIN Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 49
Stream to Elasticsearch @rmoff Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 50
@rmoff https://cnfl.io/start-ksql-workshop 7. Kafka Connect / Integrating Kafka with a database 8. The Stream/Table duality 9. Joining Data in KSQL 10. Streaming Aggregates 11. Optional: Stream data to Elasticsearch Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 51
@rmoff http://cnfl.io/book-bundle Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 52
https://www.confluent.io/ksql http://cnfl.io/demo-scene http://cnfl.io/book-bundle http://cnfl.io/slack @rmoff @rmoff Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 53
@rmoff Related Talks •The Changing Face of ETL: Event-Driven Architectures for Data Engineers 📖 Slides •ATM Fraud detection with Kafka and KSQL 📖 Slides 👾 Code 📽 Recording (live @ Milan Apache Kafka Meetup) •Embrace the Anarchy: Apache Kafka’s Role in Modern Data Architectures 📖 Slides 📽 Recording Devoxx Belgium •Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! 📖 Slides 👾 Code 📽 Recording Devoxx Belgium •No More Silos: Integrating Databases and Apache Kafka 📖 Slides 👾 Code (MySQL) 👾 Code (Oracle) Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
Slide 54
@rmoff Resources • CDC Spreadsheet #EOF • Blog: No More Silos: How to Integrate your Databases with Apache Kafka and CDC • #partner-engineering on Slack for questions • BD team (#partners / [email protected]) can help with introductions on a given sales op Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!
