🤖Building a Telegram bot with Apache Kafka and ksqlDB

🤖Building a Telegram bot with Apache Kafka and ksqlDB @rmoff

How many spaces are available in this car park?

Where’s my nearest carpark with available spaces?

💡Tell me when a car park with spaces is available

📈How does occupancy vary over time?

@rmoff | @confluentinc

$ whoami > Robin Moffatt (@rmoff) > Senior Developer Advocate at Confluent (Apache Kafka, not Wikis 😉) > Working in data & analytics since 2001 > Oracle ACE Director (Alumnus) http://rmoff.dev/talks · http://rmoff.dev/blog · http://rmoff.dev/youtube @rmoff | @confluentinc

Telegram @rmoff | @confluentinc

Telegram Bot @rmoff | @confluentinc

Don’t just tell me… show me! Demo code: https://rmoff.dev/carparks

API all the things! ➜ http https:!//datahub.bradford.gov.uk/ebase/api/getData/v2/Council/CarParkCurrent HTTP/1.1 200 OK Access-Control-Allow-Origin: * Access-Control-Allow-Origin: * Cache-Control: private Content-Disposition: attachment; filename=”carParkCurrent.csv” Content-Encoding: gzip Content-Length: 381 Content-Type: “text/csv” Date: Tue, 28 Jul 2020 10:59:52 GMT Server: Microsoft-IIS/8.5 Set-Cookie: JSESSIONID=5433FDC610177C14204184E7218C8370; Path=/ebase/; Secure; HttpOnly ntCoent-Length: 1021 date,time,name,capacity,empty_places,status,latitude,longitude,directionsURL 2020-07-28,11:52,Westgate,116,74,Spaces,53.796291,-1.759143,”https:”//maps.google.com/?daddr=53.796291,-1.759143” 2020-07-28,11:52,Burnett St,122,110,Spaces,53.795739,-1.744756,”https:”//maps.google.com/?daddr=53.795739,-1.…” 2020-07-28,11:52,Crown Court,142,90,Spaces,53.792179,-1.748466,”https:”//maps.google.com/?daddr=53.792179,-…” 2020-07-28,11:52,Leisure Exchange,996,944,Spaces,53.79222,-1.746683,”https:”//maps.google.com/?daddr=53.79222,-…” 2020-07-28,11:52,NCP Hall Ings,526,490,Spaces,53.791838,-1.752201,”https:”//maps.google.com/?daddr=53.791838,-…” @rmoff | @confluentinc

Streaming Data Ingest carparks HTTP curl | kafkacat Kafka Unix pipes FTW 🙌 @rmoff | @confluentinc

Streaming Data Transformation carparks ksqlDB Kafka CARPARK_EVENTS CREATE STREAM CARPARK_EVENTS WITH (VALUE_FORMAT=’PROTOBUF’) AS SELECT *, STRUCT(“lat” := LATITUDE, “lon”:= LONGITUDE), ‘v2/CarParkCurrent’ AS SRC FROM CARPARK_SRC EMIT CHANGES; @rmoff | @confluentinc

K/V Lookups (materialised views) ksqlDB Kafka CARPARK_EVENTS CREATE TABLE CARPARK AS SELECT … @rmoff | @confluentinc

K/V Lookups (materialised views) How many spaces are free at “ Westgate carpark right now? ksqlDB ” Kafka CARPARK_EVENTS CREATE TABLE CARPARK AS SELECT … @rmoff | @confluentinc

K/V Lookups (materialised views) SELECT SPACES_AVAILABLE How many spaces are free at “ FROM CARPARK WHERE NAME=’WESTGATE’; Westgate carpark right now? ksqlDB ” Kafka CARPARK_EVENTS CREATE TABLE CARPARK AS SELECT … @rmoff | @confluentinc

K/V Lookups (materialised views) SELECT SPACES_AVAILABLE How many spaces are free at “ FROM CARPARK WHERE NAME=’WESTGATE’; Westgate carpark right now? ksqlDB ” Kafka CARPARK_EVENTS 42 CREATE TABLE CARPARK AS SELECT … @rmoff | @confluentinc

K/V Lookups (materialised views) SELECT SPACES_AVAILABLE How many spaces are free at “ FROM CARPARK WHERE NAME=’WESTGATE’; Westgate carpark right now? ksqlDB ” Kafka CARPARK_EVENTS 42 CREATE TABLE CARPARK AS SELECT … “ There are 42 spaces free @rmoff | ” @confluentinc

Stream Processing with ksqlDB Source stream @rmoff | #ConfluentVUG | @confluentinc

Stream Processing with ksqlDB Source stream @rmoff | #ConfluentVUG | @confluentinc

Stream Processing with ksqlDB Source stream @rmoff | #ConfluentVUG | @confluentinc

Stream Processing with ksqlDB Source stream Analytics @rmoff | #ConfluentVUG | @confluentinc

Stream Processing with ksqlDB Source stream Applications / Microservices @rmoff | #ConfluentVUG | @confluentinc

Stream Processing with ksqlDB …SUM(TXN_AMT) GROUP BY AC_ID Source stream Applications / Microservices @rmoff | #ConfluentVUG | @confluentinc

Stream Processing with ksqlDB …SUM(TXN_AMT) GROUP BY AC_ID AC _I D= 42 Source stream Applications / Microservices @rmoff | #ConfluentVUG | @confluentinc

Stream Processing with ksqlDB …SUM(TXN_AMT) GROUP BY AC_ID AC _I D= 42 BA LA NC AC E= _I 94 D= .0 42 0 Source stream Applications / Microservices @rmoff | #ConfluentVUG | @confluentinc

Streaming Analytics Kafka Connect Kafka @rmoff | @confluentinc

Streaming Integration with Kafka Connect Amazon S3 syslog Google BigQuery Kafka Connect @rmoff | Kafka Brokers #ConfluentVUG | @confluentinc

Streaming Analytics @rmoff | @confluentinc

on Photo by Want to learn more? CTAs, not CATs (sorry, not sorry) @rmoff | @confluentinc

60 DE VA DV $50 USD off your bill each calendar month for the first three months when you sign up https://rmoff.dev/ccloud Free money! (additional $60 towards your bill 😄 ) Fully Managed Kafka as a Service * Limited availability. Activate by 11th September 2020. Expires after 90 days of activation. Any unused promo value on the expiration date will be forfeited.

Learn Kafka. Start building with Apache Kafka at Confluent Developer. developer.confluent.io

Confluent Community Slack group cnfl.io/slack @rmoff | @confluentinc

Further reading / watching • Kafka as a Platform: the Ecosystem from the Ground Up • https://rmoff.dev/kafka101 • Apache Kafka and ksqlDB in Action: Let’s Build a Streaming Data Pipeline! • https://rmoff.dev/ljc-kafka-01 • From Zero to Hero with Kafka Connect • https://rmoff.dev/ljc-kafka-02 • Introduction to ksqlDB • https://rmoff.dev/ljc-kafka-03 • Integrating Oracle and Kafka • https://rmoff.dev/oracle-and-kafka • The Changing Face of ETL: Event-Driven Architectures for Data Engineers • https://rmoff.dev/oredev19-changing-face-of-etl • 🚂On Track with Apache Kafka: Building a Streaming Platform solution with Rail Data • https://rmoff.dev/oredev19-on-track-with-kafka @rmoff | @confluentinc

#EOF https://talks.rmoff.net @rmoff