JDBC connector

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/kafka/kafka-connect-jdbc.md)

Note

This connector should only be used if your data is simple and consists of primitive data types e.g., int. ClickHouse specific types such as maps aren't supported.

For our examples, we utilize the Confluent distribution of Kafka Connect.

Below we describe a simple installation, pulling messages from a single Kafka topic and inserting rows into a ClickHouse table. We recommend Confluent Cloud, which offers a generous free tier for those who don't have a Kafka environment.

Note that a schema is required for the JDBC Connector (You can't use plain JSON or CSV with the JDBC connector). Whilst the schema can be encoded in each message; it is [strongly advised to use the Confluent schema registry](https://www.confluent.io/blog/kafka-connect-deep-dive-converters-serialization-explained/#json-schemas)y to avoid the associated overhead. The insertion script provided automatically infers a schema from the messages and inserts this to the registry - this script can thus be reused for other datasets. Kafka's keys are assumed to be Strings. Further details on Kafka schemas can be found [here](https://docs.confluent.io/platform/current/schema-registry/index.html).

### License[​](#license "Direct link to License")

The JDBC Connector is distributed under the [Confluent Community License](https://www.confluent.io/confluent-community-license)

### Steps[​](#steps "Direct link to Steps")

#### Gather your connection details[​](#gather-your-connection-details "Direct link to Gather your connection details")

To connect to ClickHouse with HTTP(S) you need this information:

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter(s) Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `HOST` and `PORT` Typically, the port is 8443 when using TLS or 8123 when not using TLS.|  |  |  |  | | --- | --- | --- | --- | | `DATABASE NAME` Out of the box, there is a database named `default`, use the name of the database that you want to connect to.| `USERNAME` and `PASSWORD` Out of the box, the username is `default`. Use the username appropriate for your use case. | | | | | | | |

The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console.
Select a service and click **Connect**:

Choose **HTTPS**. Connection details are displayed in an example `curl` command.

If you're using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.

#### 1. Install Kafka Connect and Connector[​](#1-install-kafka-connect-and-connector "Direct link to 1. Install Kafka Connect and Connector")

We assume you have downloaded the Confluent package and installed it locally. Follow the installation instructions for installing the connector as documented [here](https://docs.confluent.io/kafka-connect-jdbc/current/#install-the-jdbc-connector).

If you use the confluent-hub installation method, your local configuration files will be updated.

For sending data to ClickHouse from Kafka, we use the Sink component of the connector.

#### 2. Download and install the JDBC Driver[​](#2-download-and-install-the-jdbc-driver "Direct link to 2. Download and install the JDBC Driver")

Download and install the ClickHouse JDBC driver `clickhouse-jdbc-<version>-shaded.jar` from [here](https://github.com/ClickHouse/clickhouse-java/releases). Install this into Kafka Connect following the details [here](https://docs.confluent.io/kafka-connect-jdbc/current/#installing-jdbc-drivers). Other drivers may work but haven't been tested.

Note

Common Issue: the docs suggest copying the jar to `share/java/kafka-connect-jdbc/`. If you experience issues with Connect finding the driver, copy the driver to `share/confluent-hub-components/confluentinc-kafka-connect-jdbc/lib/`. Or modify `plugin.path` to include the driver - see below.

#### 3. Prepare configuration[​](#3-prepare-configuration "Direct link to 3. Prepare configuration")

Follow [these instructions](https://docs.confluent.io/cloud/current/cp-component/connect-cloud-config.html#set-up-a-local-connect-worker-with-cp-install) for setting up a Connect relevant to your installation type, noting the differences between a standalone and distributed cluster. If using Confluent Cloud the distributed setup is relevant.

The following parameters are relevant to using the JDBC connector with ClickHouse. A full parameter list can be found [here](https://docs.confluent.io/kafka-connect-jdbc/current/sink-connector/index.html):

- `_connection.url_` - this should take the form of `jdbc:clickhouse://&lt;clickhouse host>:&lt;clickhouse http port>/&lt;target database>`
- `connection.user` - a user with write access to the target database
- `table.name.format`- ClickHouse table to insert data. This must exist.
- `batch.size` - The number of rows to send in a single batch. Ensure this set is to an appropriately large number. Per ClickHouse [recommendations](/docs/sql-reference/statements/insert-into#performance-considerations) a value of 1000 should be considered a minimum.
- `tasks.max` - The JDBC Sink connector supports running one or more tasks. This can be used to increase performance. Along with batch size this represents your primary means of improving performance.
- `value.converter.schemas.enable` - Set to false if using a schema registry, true if you embed your schemas in the messages.
- `value.converter` - Set according to your datatype e.g. for JSON, `io.confluent.connect.json.JsonSchemaConverter`.
- `key.converter` - Set to `org.apache.kafka.connect.storage.StringConverter`. We utilise String keys.
- `pk.mode` - Not relevant to ClickHouse. Set to none.
- `auto.create` - Not supported and must be false.
- `auto.evolve` - We recommend false for this setting although it may be supported in the future.
- `insert.mode` - Set to "insert". Other modes aren't currently supported.
- `key.converter` - Set according to the types of your keys.
- `value.converter` - Set based on the type of data on your topic. This data must have a supported schema - JSON, Avro or Protobuf formats.

If using our sample dataset for testing, ensure the following are set:

- `value.converter.schemas.enable` - Set to false as we utilize a schema registry. Set to true if you're embedding the schema in each message.
- `key.converter` - Set to "org.apache.kafka.connect.storage.StringConverter". We utilise String keys.
- `value.converter` - Set "io.confluent.connect.json.JsonSchemaConverter".
- `value.converter.schema.registry.url` - Set to the schema server url along with the credentials for the schema server via the parameter `value.converter.schema.registry.basic.auth.user.info`.

Example configuration files for the Github sample data can be found [here](https://github.com/ClickHouse/kafka-samples/tree/main/github_events/jdbc_sink), assuming Connect is run in standalone mode and Kafka is hosted in Confluent Cloud.

#### 4. Create the ClickHouse table[​](#4-create-the-clickhouse-table "Direct link to 4. Create the ClickHouse table")

Ensure the table has been created, dropping it if it already exists from previous examples. An example compatible with the reduced Github dataset is shown below. Not the absence of any Array or Map types that aren't currently not supported: