Use ksqlDB with Aiven for Apache Kafka®
Aiven provides a managed Apache Kafka® solution together with a number of auxiliary services like Apache Kafka Connect, Kafka REST and Schema Registry via Karapace. A managed ksqlDB service in Aiven is, however, not supported. If you want to define streaming data pipelines with SQL, you have two options:
- Use Aiven for Apache Flink®
- Run a self-hosted ksqlDB cluster.
Prerequisites
To connect ksqlDB to Aiven for Apache Kafka® you need to create a Java keystore and truststore containing the service SSL certificates.
Furthermore, you need to collect the following information:
APACHE_KAFKA_HOST
: The Aiven for Apache Kafka hostnameAPACHE_KAFKA_PORT
: The Aiven for Apache Kafka portSCHEMA_REGISTRY_PORT
: The Aiven for Apache Kafka schema registry port, if enabledSCHEMA_REGISTRY_PASSWORD
: The password associated with theavnadmin
user for Schema RegistryKEYSTORE_FILE_NAME
: The name of the Java keystore containing the Aiven for Apache Kafka SSL certificatesTRUSTSTORE_FILE_NAME
: The name of the Java truststore containing the Aiven for Apache Kafka SSL certificatesSSL_KEYSTORE_PASSWORD
: The password used to secure the Java keystoreSSL_KEY_PASSWORD
: The password used to secure the Java keySSL_TRUSTSTORE_PASSWORD
: The password used to secure the Java truststoreSSL_STORE_FOLDER
: The absolute path of the folder containing both the truststore and keystoreTRUSTSTORE_SCHEMA_REGISTRY_FILE_NAME
: The name of the Java truststore containing the schema registry (Karapace) certificateTRUSTSTORE_SCHEMA_REGISTRY_PASSWORD
: The password used to secure the Java truststore for the schema registry (Karapace) certificate
Create a keystore for schema registry's ca file
ksqlDB by default uses the ssl.truststore
settings for the Schema
Registry connection.
To have ksqlDB working with Aiven's Karapace Schema Registry you need to explicitly define a truststore that contains the commonly trusted root CA of Schema Registry server. To create such a truststore:
-
Obtain the root CA of the server with the following
openssl
command by replacing theAPACHE_KAFKA_HOST
andSCHEMA_REGISTRY_PORT
placeholders:openssl s_client -connect APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT \
-showcerts < /dev/null 2>/dev/null | \
awk '/BEGIN CERT/{s=1}; s{t=t "\n" $0};
/END CERT/ {last=t; t=""; s=0}; END{print last}' \
> ca_schema_registry.cert -
Create the truststore with the following
keytool
command by replacing theTRUSTSTORE_SCHEMA_REGISTRY_FILE_NAME
andTRUSTSTORE_SCHEMA_REGISTRY_PASSWORD
placeholders:keytool -import -file ca_schema_registry.cert \
-alias CA \
-keystore TRUSTSTORE_SCHEMA_REGISTRY_FILE_NAME \
-storepass TRUSTSTORE_SCHEMA_REGISTRY_PASSWORD \
-noprompt
The TRUSTSTORE_SCHEMA_REGISTRY_FILE_NAME
can be any name but the
extension should be .jks
Run ksqlDB on Docker
You can run ksqlDB on Docker with the following command, by replacing the placeholders:
SSL_STORE_FOLDER
APACHE_KAFKA_HOST
APACHE_KAFKA_PORT
KEYSTORE_FILE_NAME
SSL_KEYSTORE_PASSWORD
SSL_KEY_PASSWORD
TRUSTSTORE_FILE_NAME
SSL_TRUSTSTORE_PASSWORD
SCHEMA_REGISTRY_PORT
SCHEMA_REGISTRY_PASSWORD
TRUSTSTORE_SCHEMA_REGISTRY_FILE_NAME
TRUSTSTORE_SCHEMA_REGISTRY_PASSWORD
docker run -d --name ksql \
-v SSL_STORE_FOLDER/:/ssl_settings/ \
-p 127.0.0.1:8088:8088 \
-e KSQL_BOOTSTRAP_SERVERS=APACHE_KAFKA_HOST:APACHE_KAFKA_PORT \
-e KSQL_LISTENERS=http://0.0.0.0:8088/ \
-e KSQL_KSQL_SERVICE_ID=ksql_service_1_ \
-e KSQL_OPTS="-Dsecurity.protocol=SSL
-Dssl.keystore.type=PKCS12
-Dssl.keystore.location=/ssl_settings/KEYSTORE_FILE_NAME
-Dssl.keystore.password=SSL_KEYSTORE_PASSWORD
-Dssl.key.password=SSL_KEY_PASSWORD
-Dssl.truststore.type=JKS
-Dssl.truststore.location=/ssl_settings/TRUSTSTORE_FILE_NAME
-Dssl.truststore.password=SSL_TRUSTSTORE_PASSWORD
-Dksql.schema.registry.url=APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT
-Dksql.schema.registry.basic.auth.credentials.source=USER_INFO
-Dksql.schema.registry.basic.auth.user.info=avnadmin:SCHEMA_REGISTRY_PASSWORD
-Dksql.schema.registry.ssl.truststore.location=/ssl_settings/TRUSTSTORE_SCHEMA_REGISTRY_FILE_NAME
-Dksql.schema.registry.ssl.truststore.password=TRUSTSTORE_SCHEMA_REGISTRY_PASSWORD" \
confluentinc/ksqldb-server:0.23.1
USER_INFO
is not a placeholder, but rather a literal that
shouldn't be changed
Some docker setups have issues using the -v
mounting options. In those
cases copying the Keystore and Truststore in the container can be an
easier option. This can be achieved with the following:
docker container create --name ksql \
-p 127.0.0.1:8088:8088 \
-e KSQL_BOOTSTRAP_SERVERS=APACHE_KAFKA_HOST:APACHE_KAFKA_PORT \
-e KSQL_LISTENERS=http://0.0.0.0:8088/ \
-e KSQL_KSQL_SERVICE_ID=ksql_service_1_ \
-e KSQL_OPTS="-Dsecurity.protocol=SSL
-Dssl.keystore.type=PKCS12
-Dssl.keystore.location=/home/appuser/KEYSTORE_FILE_NAME
-Dssl.keystore.password=SSL_KEYSTORE_PASSWORD
-Dssl.key.password=SSL_KEY_PASSWORD
-Dssl.truststore.type=JKS
-Dssl.truststore.location=/home/appuser/TRUSTSTORE_FILE_NAME
-Dssl.truststore.password=SSL_TRUSTSTORE_PASSWORD
-Dksql.schema.registry.url=APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT
-Dksql.schema.registry.basic.auth.credentials.source=USER_INFO
-Dksql.schema.registry.basic.auth.user.info=avnadmin:SCHEMA_REGISTRY_PASSWORD
-Dksql.schema.registry.ssl.truststore.location=/home/appuser/TRUSTSTORE_SCHEMA_REGISTRY_FILE_NAME
-Dksql.schema.registry.ssl.truststore.password=TRUSTSTORE_SCHEMA_REGISTRY_PASSWORD" \
confluentinc/ksqldb-server:0.23.1
docker cp KEYSTORE_FILE_NAME ksql:/home/appuser/
docker cp TRUSTSTORE_FILE_NAME ksql:/home/appuser/
docker cp TRUSTSTORE_SCHEMA_REGISTRY_FILE_NAME ksql:/home/appuser/
docker start ksql
Once the Docker image is up and running you should be able to access
ksqlDB at localhost:8088
or connect via terminal with the following
command:
docker exec -it ksql ksql