Getting started with message queues

Creating a real-time data pipeline

Message queues, such as Kafka, can be used to build [almost] real-time data pipelines to connect various applications.

For example, the relational database can publish the CDC log into the Kafka topic. The data integration flow can subscribe to the topic, so once the new CDC message is published it can be processed from the queue, parsed, transformed and sent to the data warehouse as a particular transaction (INSERT/UPDATE/DELETE).

Available connectors

At this time, Etlworks supports the following connectors:

• Kafka
• Azure Event Hubs

User cases

• Log-based CDC with a message queue
• Real-time change replication with Kafka and Demezium.
• Reading and processing messages from the queue
• Writing messages to the queue

Reading and processing messages from the queue

Step 1. Create a source connection for the message queue to read the messages. For example Kafka connection.

Step 2. Create a source format. The following formats are supported when reading messages from the queue:

• JSON
• XML
• CSV
• Fixed length
• Avro

Step 3. Create a destination connection, for example, a connection to the relational database.

Step 4. Optionally create a destination format.

Step 5. Create a flow where the source is a message queue, by typing in queue to in the flow selector popup:

Step 6. Continue by adding source-to-destination transformations where the source is a message queue connection created in step 1, source format created in step 2, and the destination connection and [optionally] format created in step 3 and 4.

Step 7. When configuring source-to-destination transformation, enter the message queue topic name in the FROM field. Connectors for some message queues, for example, Kafka, support wildcard and comma-separated topic names.

Step 8. Schedule the flow to stream data in real-time or to be executed periodically.

when scheduling the flow to be executed periodically, it is recommended to use short intervals for micro-batching.

Writing messages to the queue

Step 1. Create a source connection.

Step 2. Optionally create a source format.

Step 3. Create a destination connection for the message queue to write the messages to. For example Kafka connection.

Step 4. Create a destination format. The following formats are supported when writing messages to the queue:

• JSON
• XML
• CSV
• Fixed length
• Avro

Step 5. Create a flow where the destination is a message queue, by typing in to queue in the flow selector popup:

Step 6. Continue by adding source-to-destination transformations where the source connection and format are connection and format created in step 1 and 2, and the destination connection and format are connection and format created in step 3 and 4.

Step 7. Schedule the flow to be executed periodically.

Working with messages in the JSON, XML, and other text formats

To work with messages in these formats, when configuring the Kafka connection, select String for the Value serializer and Value deserializer.

Reading messages in the Avro format

Reading messages in Avro format created by Etlworks Integrator

When configuring the Kafka connection, select Avro for the Value deserializer field.

Reading messages in Avro format created by third-party application

When configuring the Kafka connection, select Avro Record for the Value deserializer field.

Copy the Avro schema in the JSON format into the Schema field. 

Writing messages in Avro format

When configuring the Kafka connection, select Avro for the Value serializer field.