Tech Blog

Loading Data from Azure Event Hubs Using Exasol Kafka Connector

Azure Event Hubs is a fully managed, real-time data ingestion service that allows simple, trusted and scalable data ingestion. It is integrated with Apache Kafka so that existing clients and applications seamlessly communicate with Event Hubs without any code changes. Azure Event Hubs provides managed Kafka experience without having to manage your own clusters.

In this tutorial, we are going to show how to import data into an Exasol table from Azure Event Hubs using our Kafka Connector Extension.

Prerequisite

  • You’ll need to setup Azure Event Hubs, you can follow the instructions on the “Create an Event Hub” document.
  • Create Event Hubs Namespace and Event Hub
  • Send data to Event Hub, you can follow “Send and Receive Events” instructions using your favorite programming language
  • You’ll need to setup an Exasol database
  • Deploy the latest version of kafka-connector-extension to Exasol BucketFS
  • Prepare kafka-connector-extension deployment scripts

Prepare Exasol Table

For this tutorial, I have sent string type data into the Event Hub we created. Similarly, we are going to create an Exasol table that corresponds to the Azure Event Hub data.

Create the following table:

CREATE OR REPLACE TABLE EVENT_HUBS_TOPIC (    BODY            VARCHAR(20000),    KAFKA_PARTITION DECIMAL(18, 0),    KAFKA_OFFSET    DECIMAL(36, 0));

The last two columns are required to keep the metadata information about the imported offsets.

Connection Object

To access Azure Event Hubs, we have to use a secure communication protocol, namely SASL SSL protocol. In Exasol, we store confidential parameters in the connection object. With this in mind, let’s create a connection object that encodes the required credentials.

Get your Event Hubs Namespace connection string. You can follow “Get an Event Hubs connection string” instruction document.

Connect to your Exasol database and create an connection object:

CREATE OR REPLACE CONNECTION EVENT_HUBS_SASL_CONNECTIONTO ''USER ''IDENTIFIED BY 'SASL_MECHANISM=PLAIN#SASL_USERNAME=$ConnectionString#SASL_PASSWORD=<EVENT_HUBS_NAMESPACE_CONNECTION_STRING>'

Please do not forget to substitute <EVENT_HUBS_NAMESPACE_CONNECTION_STRING> with your namespace connection string above.

Import Data From Event Hub

Now we are ready to import data from Azure Event Hub.

Run the following import SQL statement:

IMPORT INTO EVENT_HUBS_TOPICFROM SCRIPT KAFKA_EXTENSION.KAFKA_CONSUMER WITH    BOOTSTRAP_SERVERS    = '<EVENT_HUBS_NAMESPACE_HOST_NAME>.servicebus.windows.net:9093'    GROUP_ID             = 'exasol-consumers'    RECORD_VALUE_FORMAT  = 'STRING'    SECURITY_PROTOCOL    = 'SASL_SSL'    CONNECTION_NAME      = 'EVENT_HUBS_SASL_CONNECTION'    CONNECTION_SEPARATOR = '#'    TABLE_NAME           = 'EVENT_HUBS_TOPIC'    TOPIC_NAME           = '<EVENT_HUB_NAME>';

Please do not forget to replace with correct placeholders.

  • <EVENT_HUBS_NAMESPACE_HOST_NAME> is a name of your Event Hubs Namespace. You can find this on the overview page of your Event Hub Namespace.
  • <EVENT_HUB_NAME> is a name for the Event Hub (a topic in Apache Kafka terms) that holds data.

Conclusion

In this guide, we showed you how you can integrate Azure Event Hubs with Exasol database using our Kafka Connector Extension.

exa-Muhammet