Connect to Kafka Data in CloverDX (formerly CloverETL)

The CData JDBC Driver for Kafka enables you to use the data transformation components in CloverDX (formerly CloverETL) to work with Kafka as sources and destinations. In this article, you will use the JDBC Driver for Kafka to set up a simple transfer into a flat file. The CData JDBC Driver for Kafka enables you to use the data transformation components in CloverDX (formerly CloverETL) to work with Kafka as sources and destinations. In this article, you will use the JDBC Driver for Kafka to set up a simple transfer into a flat file.

Connect to Kafka as a JDBC Data Source

1. Create the connection to Kafka data. In a new CloverDX graph, right-click the Connections node in the Outline pane and click Connections -> Create Connection. The Database Connection wizard is displayed.
2. Click the plus icon to load a driver from a JAR. Browse to the lib subfolder of the installation directory and select the cdata.jdbc.apachekafka.jar file.
3. Enter the JDBC URL.

   Set BootstrapServers and the Topic properties to specify the address of your Apache Kafka server, as well as the topic you would like to interact with.

   Authorization Mechanisms

   • SASL Plain: The User and Password properties should be specified. AuthScheme should be set to 'Plain'.
   • SASL SSL: The User and Password properties should be specified. AuthScheme should be set to 'Scram'. UseSSL should be set to true.
   • SSL: The SSLCert and SSLCertPassword properties should be specified. UseSSL should be set to true.
   • Kerberos: The User and Password properties should be specified. AuthScheme should be set to 'Kerberos'.

   You may be required to trust the server certificate. In such cases, specify the TrustStorePath and the TrustStorePassword if necessary.

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the Kafka JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

   java -jar cdata.jdbc.apachekafka.jar

   Fill in the connection properties and copy the connection string to the clipboard.

   A typical JDBC URL is below:

   jdbc:apachekafka:User=admin;Password=pass;BootStrapServers=https://localhost:9091;Topic=MyTopic;

Query Kafka Data with the DBInputTable Component

1. Drag a DBInputTable from the Readers selection of the Palette onto the job flow and double-click it to open the configuration editor.
2. In the DB connection property, select the Kafka JDBC data source from the drop-down menu.
3. Enter the SQL query. For example: SELECT Id, Column1 FROM SampleTable_1 WHERE Column2 = '100'

Write the Output of the Query to a UniversalDataWriter

1. Drag a UniversalDataWriter from the Writers selection onto the jobflow.
2. Double-click the UniversalDataWriter to open the configuration editor and add a file URL.
3. Right-click the DBInputTable and then click Extract Metadata.
4. Connect the output port of the DBInputTable to the UniversalDataWriter.
5. In the resulting Select Metadata menu for the UniversalDataWriter, choose the SampleTable_1 table. (You can also open this menu by right-clicking the input port for the UniversalDataWriter.)
6. Click Run to write to the file.