Skip to main content
Apache NiFi 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.
1

Gather your connection details

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:
ClickHouse Cloud service connect button
Choose HTTPS. Connection details are displayed in an example curl command.
ClickHouse Cloud HTTPS connection details
If you’re using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.
2

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
3

Download the ClickHouse JDBC driver

  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
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
    NiFi Flow Configuration page with gear button highlighted
  2. Select the Controller Services tab and add a new Controller Service by clicking on the + button at the top right
    Controller Services tab with add button highlighted
  3. Search for DBCPConnectionPool and click on the “Add” button
    Controller Service selection dialog with DBCPConnectionPool highlighted
  4. The newly added DBCPConnectionPool will be in an Invalid state by default. Click on the “gear” button to start configuring
    Controller Services list showing invalid DBCPConnectionPool with gear button highlighted
  5. Under the “Properties” section, input the following values
  1. In the Settings section, change the name of the Controller Service to “ClickHouse JDBC” for easy reference
    DBCPConnectionPool configuration dialog showing properties filled in
  2. Activate the DBCPConnectionPool Controller Service by clicking on the “lightning” button and then the “Enable” button
    Controller Services list with lightning button highlighted

    Enable Controller Service confirmation dialog
  3. Check the Controller Services tab and ensure that the Controller Service is enabled
    Controller Services list showing enabled ClickHouse JDBC service
5

Read from a table using the ExecuteSQL processor

  1. Add an ​​ExecuteSQL processor, along with the appropriate upstream and downstream processors
    NiFi canvas showing ExecuteSQL processor in a workflow
  2. Under the “Properties” section of the ​​ExecuteSQL processor, input the following values
  3. Start the ​​ExecuteSQL processor
    ExecuteSQL processor configuration with properties filled in
  4. To confirm that the query has been processed successfully, inspect one of the FlowFile in the output queue
    List queue dialog showing flowfiles ready for inspection
  5. Switch view to “formatted” to view the result of the output FlowFile
    FlowFile content viewer showing query results in formatted view
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
  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
    MergeRecord processor input showing single records
    Output
    MergeRecord processor output showing merged array of records
  4. Under the “Properties” section of the PutDatabaseRecord processor, input the following values
  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.
    Query results showing row count in the destination table
  6. Congratulations - you have successfully loaded your data into ClickHouse using Apache NiFi !