Automate Confluent ksqldb deployments with ksql-migrations

Confluent Platform is a full-scale data streaming managed cloud service that enables us to access, store, and manage data as a continuous, real-time stream. Confluent Cloud provides ksqlDB, a streaming SQL engine for Kafka. It is an easy-to-use yet powerful interactive SQL interface for stream processing on Kafka, In this article, we will see how to automate Confluent ksqlDB deployments using ksql-migrations.

Pre-requisites:
1. Confluent account with a confluent cluster and ksqlDB cluster.
2. Confluent platform installed locally using docker.
3. Confluent CLI installed locally.
4. A Basic understanding of Confluent platform and ksql.

Setup Github repository

First thing first!

1. Fork the git repository jzaralim/ksqldb-migrations-action. This repository has most of the things that we will need to implement CICD for ksqlDB.

zaralim/ksqldb-migrations-action github repository.

2. Clone the above repository locally using any client tools like VS Code.

zaralim/ksqldb-migrations-action github repository cloned using VS Code.

Create a Confluent ksqlDB cluster API key

Login to confluent.io and navigate to the confluent cluster and ksqlDB cluster. Click on CLI instructions as shown in the below image. These instructions are specific to your selected Kafka cluster and ksqlDB cluster, so you can copy and paste them into the Confluent CLI.

Confluent ksqlDB CLI instructions.

Follow the below steps to get the API key API secret and endpoint URL for ksqldb cluster.

1. Log in to the Confluent CLI,

confluent login

2. Set the context to your environment and cluster.

confluent environment use env-v7ymd0
confluent kafka cluster use lkc-0xyop2

3. Create an API key and secret for your ksqlDB cluster.

confluent api-key create --resource lksqld-pgx9ro

4. Get the ksqlDB cluster endpoint URL.

Confluent ksqlDB cluster Endpoint url.

Save this information somewhere as we will use them in the below steps.

Initialize ksql-migrations metadata

Confluent ksqldb-migrations tool uses MIGRATION_EVENTS & MIGRATION_SCHEMA_VERSIONS topics to maintain the deployment log for the deployments made through ksql-migrations. To create these topics in the confluent cluster, follow the below steps.

1. Connect to the local ksqldb-server docker container using bash. If you are using windows bash, you can execute the below command.

docker-compose exec ksqldb-server bash

The ksql-migrations tool is available with all ksqlDB versions starting from ksqlDB 0.17 or Confluent Platform 6.2.

2. Execute the below command on the local ksqldb-server container.

ksql-migrations new-project orders https://pksqld-zjyqd.westeurope.azure.confluent.cloud:443

Here, orders is the name of the project and https://pksqlc-zjyqd.westeurope.azure.confluent.cloud:443 is the ksqldb cluster endpoint.

The above command will create order\migrations\ksql-migrations.properties file under the home directory in ksqldb-server container as shown in the below image.

3. Edit ksql-migrations.properties file wth the following details.

ksql.server.url=https://pksqld-zjyqd.westeurope.azure.confluent.cloud:443
ksql.migrations.topic.replicas=3
ssl.alpn=true
ksql.auth.basic.username=T64Q4Q7TKdeNIBVNTV
ksql.auth.basic.password=IcTjKvdAA992NDELcFmJd35c69Hickw+yLzd5Uxx5KmvKUmAset0qcVPPor1y4Rn
Edit ksql-migrations.properties file with settings related to confluent ksqlDB cluster.

4. Now, run the below command to initiate migration metadata in the confluent cloud based on the configurations set in the above steps.

ksql-migrations initialize-metadata -c orders/ksql-migrations.properties

You should be able to see the below topic in the Confluent cloud.

Note: Github collaboration branch name and the name of the workflow trigger branch name should be the same.

Set up Github secrets for GitHub actions

Create GitHub secrets for the ksqlDB cluster endpoint, API key, and API secret respectively named:

  • CONFLUENT_KSQLDB_ENDPOINT
  • CONFLUENT_KSQLDB_API_KEY
  • CONFLUENT_KSQLDB_API_SECRET

You have retrieved these secrets in the above steps.

Set github secrets for confluent cloud.

We are all set to automate Confluent ksqlDB deployments

It’s time to verify our setup. Start committing changes to the migrations folder in the Github repository. For this demo, we will commit the 3 migrations files that are present inside ksqldb-migrations-action Github repository.

  1. After the initial commit, the migrate-cloud-ksqldb workflow will be triggered. It will deploy initial 3 ksql migration versions to the Confluent cloud cluster.
  2. After migration ORDER_EVENTS stream would look like in the below image.

Let’s say we need to add a column DESCRIPTION to the ORDER_EVENTS stream.

  1. Add a new version file V000004__add_column_to_order_events.sql.
  2. Commit the new version file, it will trigger a Github action as shown in the below image.
Automate ksqlDB deployments

Pro tips:
1. The steps mentioned in this article have to be done only once by a DevOps person. Individual developers need to only commit changes to the migrations folder.
2. Format of the migrations version file is Vxxxxxx__name_of_the_file.sql. That means ‘V’ followed by an incremental 6 digits number with leading 0s. E.g V000012__new_streams,sql.

Kunal Rathi

Been working on Microsoft data platforms for more than a decade. Helping customers transform their data into insights, Cloud & DevOps enthusiast.