LISA18: Apache Kafka and KSQL in Action : Let’s Build a Streaming Data Pipeline!

Have you ever thought that you needed to be a programmer to do stream processing and build streaming data pipelines? Think again!

Apache Kafka is a distributed, scalable, and fault-tolerant streaming platform, providing low-latency pub-sub messaging coupled with native storage and stream processing capabilities. Integrating Kafka with RDBMS, NoSQL, and object stores is simple with the Kafka Connect API, which is part of Apache Kafka. KSQL is the open-source SQL streaming engine for Apache Kafka, and makes it possible to build stream processing applications at scale, written using a familiar SQL interface.

In this talk we’ll explain the architectural reasoning for Apache Kafka and the benefits of real-time integration, and we’ll build a streaming data pipeline using nothing but our bare hands, the Kafka Connect API, and KSQL.

Gasp as we filter events in real time! Be amazed at how we can enrich streams of data with data from RDBMS! Be astonished at the power of streaming aggregates for anomaly detection!

This will be a practical talk, after which attendees will have a clear idea of the power of stream processing, and how to get started with it using the open-source Apache Kafka and KSQL projects.

Transcript

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

   View Slide

2. 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
   

   View Slide

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

   View Slide

4. 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
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

8. 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
   

   View Slide

9. 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
   

   View Slide

10. 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
    

    View Slide

11. 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
    

    View Slide

12. 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
    

    View Slide

13. 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
    

    View Slide

14. @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!
    

    View Slide

15. @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!
    

    View Slide

16. @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
    

    View Slide

17. 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
    

    View Slide

18. 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
    

    View Slide

19. 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
    

    View Slide

20. 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
    

    View Slide

21. 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
    

    View Slide

22. 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
    

    View Slide

23. 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
    

    View Slide

24. @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
    

    View Slide

25. @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!
    

    View Slide

26. @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
    

    View Slide

27. @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
    

    View Slide

28. @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
    

    View Slide

29. @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
    

    View Slide

30. @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
    

    View Slide

31. @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
    

    View Slide

32. @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
    

    View Slide

33. @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
    

    View Slide

34. @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...”
    

    View Slide

35. @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!
    

    View Slide

36. 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
    

    View Slide

37. 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?
    

    View Slide

38. 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
    

    View Slide

39. 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
    

    View Slide

40. 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": "mdoughartie1@dedecms.com",
    "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;
    

    View Slide

41. 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": "mdoughartie1@dedecms.com",
    "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
    

    View Slide

42. 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
    

    View Slide

43. 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)
    

    View Slide

44. 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
    

    View Slide

45. 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
    

    View Slide

46. @rmoff
    robin@confluent.io
    https://www.confluent.io/ksql
    http://cnfl.io/slack
    

    View Slide

47. 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
    

    View Slide

48. 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 / partners@confluent.io) can help with introductions on a given sales op
    Resources
    #EOF
    

    View Slide