Using Materialize and Airbyte with MySQL and Redpanda/Kafka

Introduction

Diagram:

Prerequisites

Running the demo

docker-compose up -d

Airbyte

Adding a source

Adding a destination

Set up a connection

Check the Redpanda topic

docker-compose exec redpanda bash
rpk topic list
rpk topic consume orders_topic

Create a Materialize SOURCE

docker-compose run mzcli
psql -U materialize -h localhost -p 6875 materialize
CREATE SOURCE airbyte_source
FROM KAFKA BROKER 'redpanda:9092' TOPIC 'orders_topic'
FORMAT BYTES;
COPY (
TAIL (
SELECT
CAST(data->>'_airbyte_data' AS JSON) AS data
FROM (
SELECT CAST(data AS jsonb) AS data
FROM (
SELECT * FROM (
SELECT convert_from(data, 'utf8') AS data FROM airbyte_source
)
)
)
)
)
TO STDOUT;

Create a Materialized View

CREATE MATERIALIZED VIEW airbyte_view AS
SELECT
data->>'id' AS id,
data->>'user_id' AS user_id,
data->>'order_status' AS order_status,
data->>'price' AS price,
data->>'created_at' AS created_at,
data->>'updated_at' AS updated_at
FROM (
SELECT
CAST(data->>'_airbyte_data' AS JSON) AS data
FROM (
SELECT CAST(data AS jsonb) AS data
FROM (
SELECT * FROM (
SELECT convert_from(data, 'utf8') AS data FROM airbyte_source
)
)
)
);
SELECT * FROM airbyte_view;

Stop the demo

docker-compose down -v

Useful links

Community

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Bobby Iliev

I am a professional System Administrator with a demonstrated history of working in the internet industry. I am a Linux lover