Creating a real time analytical database using Kafka, CDC events and Kudu Niklas Nylund / PAF

Intro PAF ● ● ● ●

PAF or Ålands Penningautomatförening Founded in 1967, first internet business launched 1999 PAF provides online and offline gambling & casino services Main markets are Finland, Sweden, Estonia, Spain

Key figures ● ● ● ●

Revenue 113,5 MEUR (2016) Profit 15,2 MEUR (2016) Number of employees 376 Development office in Helsinki with ~90 employees

Data pipeline & Kafka?

Requirements Non functional requirements ● ●

Provide a near real time data platform that can serve current and future needs for analytics, reporting and machine learning Provide a setup with less spaghetti and improve development speed

Functional requirements ● ● ● ●

No hard real time requirements. Lag preferably < 10 seconds, temporary spikes allowed Reliably handle financial transactions, millions records / 24 hours. Expected to increase Handle logs, metrics, file imports etc from around 100 services Handle at least 10x more data compared to current DW. Including combining different data sets, such as financial transactions, click streams, data from both online and offline business, application metrics and logs

Key decisions Architectural choices ● ● ● ● ●

Exactly once semantics or deduplication Idempotent writes Lambda architecture - streaming / batch Late arriving events - event time vs processing time Event sourcing

Kafka specifics ● ● ● ● ● ●

Use Kafka-connect vs roll your own? No global ordering across topics Kafka Streams vs Kafka client library? Which serializer? AVRO, JSON, Protobuf, custom? Use Schema registry from Kafka stack? Reliable producers & consumers

Other things to consider Downstream database ● ● ● ●

Good UPSERT performance needed for maintaining snapshot tables We needed a JDBC interface for downstream reporting tools Columnar storage format preferred for fast analytical queries Apache Kudu vs AWS offering

Operational aspects ● ●

Monitoring Disaster recovery

Final solution Producing into Kafka ● ● ● ● ● ● ●

In-house micro service sets up triggers for CDC style tables Retry log kept in SQL DB in same transactional scope as the business logic Use Kafka’s schema registry and AVRO Convert SQL to AVRO schema using metadata from JDBC driver One topic per source view Multiple tables joined together into views where necessary One JVM thread per source view, run in docker container with X threads per container

Consuming from Kafka ● ● ● ● ●

In-house micro service that can read any AVRO topic and write it to Apache Kudu Convert AVRO schema to Kudu schema Sets up tables, hash & range columns, partitions etc Manual Kafka offset handling to deal with errors thrown from Apache Kudu Maintains a changelog table of all Kafka records and a snapshot/compacted table based on Kafka record key

The end