Connect Apache NiFi to ClickHouse

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

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.

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

1. Visit the ClickHouse JDBC driver release page 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

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

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

   

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

   

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

   Property	Value	Remark
   Record Reader	JSONTreeReader	Select the appropriate record reader
   Database Type	Generic	Leave as default
   Statement Type	INSERT
   Database Connection Pooling Service	ClickHouse JDBC	Select the ClickHouse controller service
   Table Name	tbl	Input your table name here
   Translate Field Names	false	Set to “false” so that field names inserted must match the column name
   Maximum Batch Size	1000	Maximum number of rows per insert. This value shouldn’t be lower than the value of “Minimum Number of Records” in MergeRecord processor

5. To confirm that each insert contains multiple rows, check that the row count in the table is incrementing by at least the value of “Minimum Number of Records” defined in MergeRecord.

   

6. Congratulations - you have successfully loaded your data into ClickHouse using Apache NiFi !