Integrating Databases and Apache Kafka #ukoug_tech18 @rmoff Robin Moffatt, Developer Advocate @ Confluent

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Photo by Emily Morter on Unsplash

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 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

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Analytics - Database Offload HDFS / S3 / BigQuery etc RDBMS

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Real-time Event Stream Enrichment order events customer Stream Processing customer orders RDBMS CDC

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Evolve processing from old systems to new Stream Processing RDBMS Existing App New App

“ @rmoff / No More Silos: Integrating Databases and Apache Kafka But streaming…I've just got data in a database…right?

“ @rmoff / No More Silos: Integrating Databases and Apache Kafka Bold claim: all your data is event streams

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 A Customer Experience

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 A Sale

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 A Sensor Reading

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 An Application Log Entry

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Databases

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Do you think that’s a table you are querying?

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 The Stream Table Duality Account ID Balance 12345 €50

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 The Stream Table Duality Account ID Balance 12345 €50 Account ID Amount 12345 + €50 Time

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 The Stream Table Duality Account ID Amount 12345 + €50 12345 + €25 Account ID Balance 12345 €75 Time

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 The Stream Table Duality Account ID Amount 12345 + €50 12345 + €25 12345 -€60 Account ID Balance 12345 €15 Time

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 The Stream Table Duality Account ID Amount 12345 + €50 12345 + €25 12345 -€60 Account ID Balance 12345 €15 Time Stream Table

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 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

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka KSQL is the Streaming SQL Engine for Apache Kafka

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka 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

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka 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

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka Oracle Debezium Kafka Connect Producer API Elasticsearch Kafka Connect Streaming ETL with Apache Kafka and KSQL

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka 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

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka Photo by Vadim Sherbakov on Unsplash

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sources syslog flat file CSV JSON MQTT

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sinks Amazon S3 MQTT

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Streaming Integration with Kafka Connect Kafka Brokers Kafka Connect Tasks Workers Sources Sinks Amazon S3 MQTT syslog flat file CSV JSON MQTT

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Kafka Connect basics Kafka Kafka Connect Source

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Connectors Kafka Kafka Connect Source Connector

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Converters Kafka Kafka Connect Source Connector Converter https://www.confluent.io/blog/kafka-connect-deep-dive-converters-serialization-explained

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Single Message Transforms Kafka Kafka Connect Source Connector Converter Transform(s)

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Extensible Kafka Kafka Connect Source Connector Converter Transform(s) https://docs.confluent.io/current/connect/javadocs/ Browse & download at hub.confluent.io

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Kafka Connect + Schema Registry = WIN RDBMS Avro Message Elasticsearch Schema Registry Avro Schema Kafka Connect Kafka Connect

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Kafka Connect + Schema Registry = WIN RDBMS Elasticsearch Schema Registry Avro Schema Kafka Connect Kafka Connect Avro Message

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 • CDC is a generic term referring to capturing changing data typically from a RDBMS. • Two general approaches: • Query-based CDC • Log-based CDC Change-Data-Capture (CDC) There are other options including hacks with Triggers, Flashback etc but these are system and/or technology-specific.

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 • Use a database query to try and identify new & changed rows
 
 
 • Implemented with the open source Kafka Connect JDBC connector • Can import based on table names, schema, or bespoke SQL query •Incremental ingest driven through incrementing ID column and/or timestamp column Query-based CDC SELECT * FROM my_table 
 WHERE col >

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 • Use the database's transaction log to identify every single change event • Various CDC tools available that integrate with Apache Kafka (more of this later…) Log-based CDC

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka Demo Time!

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka "Which one should I use?" Photo by Tyler Nix on Unsplash

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka It Depends! Photo by Trevor Cole on Unsplash

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Query-based vs Log-based CDC Photo by Matese Fields on Unsplash • Query-based +Usually easier to setup, and requires fewer permissions - Needs specific columns in source schema - Impact of polling the DB (or higher latencies tradeoff) - Can't track deletes, or multiple events between polling interval Read more: http://cnfl.io/kafka-cdc

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Query-based vs Log-based CDC Photo by Sebastian Pociecha on Unsplash Read more: http://cnfl.io/kafka-cdc • Log-based +Greater data fidelity +Lower latency +Lower impact on source - More setup steps - Higher system privileges required - For propriatory databases, usually $$$

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Considerations for Integration into Apache Kafka Photo by Matthew Smith on Unsplash • Chucking data over the fence into a Kafka topic is not enough • CDC tools should integrate with standard ways of building data pipelines in Kafka • Schema handling • Serialisation formats

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 •Oracle GoldenGate for Big Data—Requires the OGGBD licence, not just OGG •Debezium—Open source,Oracle support in Beta • currently uses XStream— which requires OGG licence •Attunity, IBM IIDR, HVR, SQData, StreamSets—all offer commerical CDC integration into Kafka with support for Schema Registry •DBVisit Replicate—no longer under development •JDBC Connector—Open source, but not "true" CDC Oracle and Kafka integration

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Which Log-Based CDC Tool? All these options integrate with Apache Kafka and Confluent Platform, including support for the Schema Registry ⓘ For query-based CDC, use the Confluent Kafka Connect JDBC connector • Open Source RDBMS, 
 e.g. MySQL, PostgreSQL • Debezium • (+ paid options) • Mainframe
 e.g. VSAM, IMS • Attunity • SQData • Proprietory RDBMS, 
 e.g. Oracle, MS SQL • Oracle GoldenGate • Debezium + XStream • Attunity • IBM InfoSphere Data Replication (IIDR) • SQData • HVR

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 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

No More Silos: Integrating Databases and Apache Kafka @rmoff #ukoug_tech18 Free Books! https://www.confluent.io/apache-kafka-stream-processing-book-bundle

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka @rmoff robin@confluent.io http://cnfl.io/slack https://www.confluent.io/download/ http://cnfl.io/kafka-cdc

@rmoff #ukoug_tech18 No More Silos: Integrating Databases and Apache Kafka #EOF