Slide 1

Slide 1 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff [email protected] confluent.io/ksql USENIX Large Installation System Administration Conference (LISA) October 31 2018

Slide 2

Slide 2 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql • Developer Advocate @ Confluent • Working in data & analytics since 2001 • Oracle Developer Champion • Blogging : http://rmoff.net & http://cnfl.io/rmoff • Twitter: • Geek stuff • Beer & Fried Breakfasts $ whoami https://speakerdeck.com/rmoff/ @rmoff

Slide 3

Slide 3 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Kafka

Slide 4

Slide 4 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Kafka is a Streaming Platform KAFKA DWH Hadoop App App App App App App App App request-response messaging OR stream processing streaming data pipelines changelogs

Slide 5

Slide 5 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Streaming is not just for realtime

Slide 6

Slide 6 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Streaming is for everyone

Slide 7

Slide 7 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! All data is events

Slide 8

Slide 8 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql A Dumb Pipeline HDFS / S3 / BigQuery etc Logs

Slide 9

Slide 9 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql A Dumb Pipeline HDFS / S3 / BigQuery etc Logs Logs

Slide 10

Slide 10 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Stream Processing with Apache Kafka and KSQL Stream Processing Logs HDFS / S3 / BigQuery etc All logs Errors

Slide 11

Slide 11 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Real-time Event Stream Enrichment order events customer Stream Processing customer orders RDBMS CDC

Slide 12

Slide 12 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Transform Once, Use Many order events customer Stream Processing customer orders RDBMS New App CDC

Slide 13

Slide 13 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Transform Once, Use Many order events customer Stream Processing customer orders RDBMS HDFS / S3 / etc New App CDC

Slide 14

Slide 14 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Rating events Join events to users, and filter Push notification Operational Dashboard Data Lake User data Let’s Build It!

Slide 15

Slide 15 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Rating events Join events to users, and filter Push notification Operational Dashboard Data Lake User data RDBMS S3/HDFS/ SnowflakeDB etc Elasticsearch App App Producer API Consumer API Let’s Build It!

Slide 16

Slide 16 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Rating events Join events to users, and filter Push notification Operational Dashboard Data Lake User data RDBMS S3/HDFS/ SnowflakeDB etc Elasticsearch App App Producer API Consumer API Kafka Connect Kafka Connect Kafka Connect Kafka Connect

Slide 17

Slide 17 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql An API of Apache Kafka, providing reliable and scalable integration of Kafka with other systems – no coding required. { "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/ Kafka Connect

Slide 18

Slide 18 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sources syslog flat file CSV JSON MQTT

Slide 19

Slide 19 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sinks Amazon S3 MQT

Slide 20

Slide 20 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sources Sinks Amazon S3 MQT syslog flat file CSV JSON MQTT

Slide 21

Slide 21 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Confluent Hub hub.confluent.io • One-stop place to discover and download : • Connectors • Transformations • Converters

Slide 22

Slide 22 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Kafka Connect + Schema Registry = WIN RDBMS Avro Message Elasticsearch Schema Registry Avro Schema Kafka Connect Kafka Connect

Slide 23

Slide 23 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Kafka Connect + Schema Registry = WIN RDBMS Elasticsearch Schema Registry Avro Schema Kafka Connect Kafka Connect Avro Message

Slide 24

Slide 24 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! curl -X "POST" "http://kafka-connect-cp:18083/connectors/" \ -H "Content-Type: application/json" \ -d '{ "name": "es_sink_lisa18", "config": { "connector.class": "io.confluent.connect.elasticsearch.ElasticsearchSinkConnector", "key.converter": "org.apache.kafka.connect.storage.StringConverter", "value.converter": "org.apache.kafka.connect.json.JsonConverter", "value.converter.schemas.enable": false, "topics": "lisa18", "key.ignore": "true", "schema.ignore": "true", "type.name": "type.name=kafkaconnect", "connection.url": "http://elasticsearch:9200" } }' Kafka → Elasticsearch

Slide 25

Slide 25 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! MySQL Debezium Kafka Connect Producer API Demo Time!

Slide 26

Slide 26 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Rating events Join events to users, and filter Push notification Operational Dashboard Data Lake User data RDBMS S3/HDFS/ SnowflakeDB etc Elasticsearch App App Producer API Consumer API Let’s Build It! Kafka Connect Kafka Connect Kafka Connect

Slide 27

Slide 27 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Rating events Join events to users, and filter Push notification Operational Dashboard Data Lake User data RDBMS S3/HDFS/ SnowflakeDB etc Elasticsearch App App Producer API Consumer API KSQL Kafka Connect Kafka Connect Kafka Connect KSQL

Slide 28

Slide 28 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! Declarative Stream Language Processing KSQL is a

Slide 29

Slide 29 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! KSQL is the Streaming SQL Engine for Apache Kafka

Slide 30

Slide 30 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! KSQL for Real-Time Monitoring • Log data monitoring, tracking and alerting • syslog data • Sensor / IoT data CREATE STREAM SYSLOG_INVALID_USERS AS SELECT HOST, MESSAGE FROM SYSLOG WHERE MESSAGE LIKE '%Invalid user%'; http://cnfl.io/syslogs-filtering / http://cnfl.io/syslog-alerting

Slide 31

Slide 31 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! KSQL for Streaming ETL 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'; Joining, filtering, and aggregating streams of event data

Slide 32

Slide 32 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! KSQL for Anomaly Detection CREATE TABLE possible_fraud AS
 SELECT card_number, count(*)
 FROM authorization_attempts 
 WINDOW TUMBLING (SIZE 5 SECONDS)
 GROUP BY card_number
 HAVING count(*) > 3; Identifying patterns or anomalies in real-time data, surfaced in milliseconds

Slide 33

Slide 33 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! CREATE STREAM pageviews WITH (PARTITIONS=4, VALUE_FORMAT='AVRO') AS 
 SELECT * FROM pageviews_json; KSQL for Data Transformation Make simple derivations of existing topics from the command line

Slide 34

Slide 34 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! KSQL in Development and Production Interactive KSQL
 for development and testing Headless KSQL
 for Production Desired KSQL queries have been identified REST “Hmm, let me try
 out this idea...”

Slide 35

Slide 35 text

@rmoff / http://cnfl.io/ksql Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! MySQL Debezium Kafka Connect Producer API Elasticsearch Kafka Connect Demo Time!

Slide 36

Slide 36 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Producer API { "rating_id": 5313, "user_id": 3, "stars": 4, "route_id": 6975, "rating_time": 1519304105213, "channel": "web", "message": "worst. flight. ever. #neveragain" } POOR_RATINGS Filter all ratings where STARS<3 CREATE STREAM POOR_RATINGS AS SELECT * FROM ratings WHERE STARS <3

Slide 37

Slide 37 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Do you think that’s a table you are querying?

Slide 38

Slide 38 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql The Stream Table Duality Account ID Balance 12345 €50 Account ID Amount 12345 + €50 12345 + €25 12345 -€60 Account ID Balance 12345 €75 Account ID Balance 12345 €15 Time Stream Table Read more: https://cnfl.io/stream-table-duality

Slide 39

Slide 39 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql 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 40

Slide 40 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Kafka Connect Producer API { "rating_id": 5313, "user_id": 3, "stars": 4, "route_id": 6975, "rating_time": 1519304105213, "channel": "web", "message": "worst. flight. ever. #neveragain" } { "id": 3, "first_name": "Merilyn", "last_name": "Doughartie", "email": "[email protected]", "gender": "Female", "club_status": "platinum", "comments": "none" } RATINGS_WITH_CUSTOMER_DATA Join each rating to customer data CREATE STREAM RATINGS_WITH_CUSTOMER_DATA AS SELECT * FROM RATINGS LEFT JOIN CUSTOMERS ON R.ID=C.ID;

Slide 41

Slide 41 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Kafka Connect Producer API { "rating_id": 5313, "user_id": 3, "stars": 4, "route_id": 6975, "rating_time": 1519304105213, "channel": "web", "message": "worst. flight. ever. #neveragain" } { "id": 3, "first_name": "Merilyn", "last_name": "Doughartie", "email": "[email protected]", "gender": "Female", "club_status": "platinum", "comments": "none" } RATINGS_WITH_CUSTOMER_DATA Join each rating to customer data UNHAPPY_PLATINUM_CUSTOMERS Filter for just PLATINUM customers CREATE STREAM UNHAPPY_PLATINUM_CUSTOMERS AS SELECT * FROM RATINGS_WITH_CUSTOMER_DATA WHERE STARS < 3

Slide 42

Slide 42 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Confluent Open Source : Apache Kafka with a bunch of cool stuff! For free! Database Changes Log Events loT Data Web Events … CRM Data Warehouse Database Hadoop Data
 Integration … Monitoring Analytics Custom Apps Transformations Real-time Applications … Confluent Platform Confluent Platform Apache Kafka® Core | Connect API | Streams API Data Compatibility Schema Registry Monitoring & Administration Confluent Control Center | Security Operations Replicator | Auto Data Balancing Development and Connectivity Clients | Connectors | REST Proxy | CLI SQL Stream Processing KSQL Datacenter Public Cloud Confluent Cloud CONFLUENT FULLY-MANAGED CUSTOMER SELF-MANAGED

Slide 43

Slide 43 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql •Kafka Connect • Integration between Kafka and other data stores •Kafka • Provides stream processing natively •KSQL • Build stream processing apps with just SQL If you remember one thing… (or three)

Slide 44

Slide 44 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Free Books! https://www.confluent.io/apache-kafka-stream-processing-book-bundle

Slide 45

Slide 45 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql Try it out! https://cnfl.io/kafka-ksql-elastic

Slide 46

Slide 46 text

@rmoff [email protected] https://www.confluent.io/ksql http://cnfl.io/slack

Slide 47

Slide 47 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql • Embrace the Anarchy : Apache Kafka's Role in Modern Data Architectures Recording & Slides • Look Ma, no Code! Building Streaming Data Pipelines with Apache Kafka and KSQL • Steps to Building a Streaming ETL Pipeline with Apache Kafka and KSQL Recording & Slides • https://www.confluent.io/blog/ksql-in-action-real-time-streaming-etl-from-oracle-transactional-data • https://github.com/confluentinc/ksql/ Useful links

Slide 48

Slide 48 text

Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline! @rmoff / http://cnfl.io/ksql • CDC Spreadsheet • 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 Resources #EOF