Connect Apache NiFi to ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/etl-tools/nifi-and-clickhouse.md)

Community Maintained

[Apache NiFi](https://nifi.apache.org/) is an open-source workflow management software designed to automate data flow between software systems. It allows the creation of ETL data pipelines and is shipped with more than 300 data processors. This step-by-step tutorial shows how to connect Apache NiFi to ClickHouse as both a source and destination, and to load a sample dataset.

Gather your connection details

## Gather your connection details[​](#1-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.

Download and run Apache NiFi

## Download and run Apache NiFi[​](#2-download-and-run-apache-nifi "Direct link to Download and run Apache NiFi")

For a new setup, download the binary from <https://nifi.apache.org/download.html> and start by running `./bin/nifi.sh start`

Download the ClickHouse JDBC driver

## Download the ClickHouse JDBC driver[​](#3-download-the-clickhouse-jdbc-driver "Direct link to Download the ClickHouse JDBC driver")

1. Visit the [ClickHouse JDBC driver release page](https://github.com/ClickHouse/clickhouse-java/releases) on GitHub and look for the latest JDBC release version
2. In the release version, click on "Show all xx assets" and look for the JAR file containing the keyword "shaded" or "all", for example, `clickhouse-jdbc-0.5.0-all.jar`
3. Place the JAR file in a folder accessible by Apache NiFi and take note of the absolute path

Add Controller Service and configure its properties

## Add `DBCPConnectionPool` Controller Service and configure its properties[​](#4-add-dbcpconnectionpool-controller-service-and-configure-its-properties "Direct link to 4-add-dbcpconnectionpool-controller-service-and-configure-its-properties")

1. To configure a Controller Service in Apache NiFi, visit the NiFi Flow Configuration page by clicking on the "gear" button
2. Select the Controller Services tab and add a new Controller Service by clicking on the `+` button at the top right
3. Search for `DBCPConnectionPool` and click on the "Add" button
4. The newly added `DBCPConnectionPool` will be in an Invalid state by default. Click on the "gear" button to start configuring
5. Under the "Properties" section, input the following values

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Property Value Remark|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Database Connection URL jdbc:ch:<https://HOSTNAME:8443/default?ssl=true> Replace HOSTNAME in the connection URL accordingly|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Database Driver Class Name com.clickhouse.jdbc.ClickHouseDriver |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Database Driver Locations /etc/nifi/nifi-X.XX.X/lib/clickhouse-jdbc-0.X.X-patchXX-shaded.jar Absolute path to the ClickHouse JDBC driver JAR file|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Database User default ClickHouse username|  |  |  | | --- | --- | --- | | Password password ClickHouse password | | | | | | | | | | | | | | | | | |

6. In the Settings section, change the name of the Controller Service to "ClickHouse JDBC" for easy reference
7. Activate the `DBCPConnectionPool` Controller Service by clicking on the "lightning" button and then the "Enable" button
8. Check the Controller Services tab and ensure that the Controller Service is enabled

Read from a table using the processor

## Read from a table using the `ExecuteSQL` processor[​](#5-read-from-a-table-using-the-executesql-processor "Direct link to 5-read-from-a-table-using-the-executesql-processor")

1. Add an ​`​ExecuteSQL` processor, along with the appropriate upstream and downstream processors
2. Under the "Properties" section of the ​`​ExecuteSQL` processor, input the following values

   |  |  |  |  |  |  |  |  |  |
   | --- | --- | --- | --- | --- | --- | --- | --- | --- |
   | Property Value Remark|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Database Connection Pooling Service ClickHouse JDBC Select the Controller Service configured for ClickHouse|  |  |  | | --- | --- | --- | | SQL select query SELECT \* FROM system.metrics Input your query here | | | | | | | | |
3. Start the `​​ExecuteSQL` processor
4. To confirm that the query has been processed successfully, inspect one of the `FlowFile` in the output queue
5. Switch view to "formatted" to view the result of the output `FlowFile`

Write to a table using and processor

## Write to a table using `MergeRecord` and `PutDatabaseRecord` processor[​](#6-write-to-a-table-using-mergerecord-and-putdatabaserecord-processor "Direct link to 6-write-to-a-table-using-mergerecord-and-putdatabaserecord-processor")

1. To write multiple rows in a single insert, we first need to merge multiple records into a single record. This can be done using the `MergeRecord` processor
2. Under the "Properties" section of the `MergeRecord` processor, input the following values

   |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
   | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
   | Property Value Remark|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Record Reader `JSONTreeReader` Select the appropriate record reader|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Record Writer `JSONReadSetWriter` Select the appropriate record writer|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Minimum Number of Records 1000 Change this to a higher number so that the minimum number of rows are merged to form a single record. Default to 1 row|  |  |  | | --- | --- | --- | | Maximum Number of Records 10000 Change this to a higher number than "Minimum Number of Records". Default to 1,000 rows | | | | | | | | | | | | | | |
3. To confirm that multiple records are merged into one, examine the input and output of the `MergeRecord` processor. Note that the output is an array of multiple input records

   Input

   Output
4. Under the "Properties" section of the `PutDatabaseRecord` processor, input the following values