How to post INSERT into Clickhouse with NiFi - steps needed.

[pbuxtonccuk]

Hi,

I tried to look at this a few months back, and time and other priorities itervened.

Looking again, I have hit a dead end.

I would have used the JDBC driver, but I get MapMaker dependency errors that I cannot fix.

I have now tried the InvokeHTTP, and also the PostHTTP processors of Nifi, and just cannot seem to get them to work.

I am therefore looking for an idiot -proof implementation, if anyone out there can give me a step by step to succeed.....

i just need to break the back of it....

Regards. paul

[TH-HA]

Hello Here is a dummy implementation of using the http interface of clickhouse from nifi. The template in attachement must be configured with the proper clickhouse url ( variable chURL ) .

The process group is made of 3 processors.
    A generator which is generating fake flowfile every seconde.
    A mergeContent which is group flowfile per batch of 20 and generate the content to put in the http post. 
                    Note : I’m using the Demarcator and the header to generate fake data.  
                    Note : This is a key component when using clickhouse allowing to buffer data ( by size of temporally ) in a safe way at NIFI level to send batch insert instead of line by line.
    A postHTTP processor which is easy to setup ( look at the URL , the only burden is the escaping 😊 ). 

The template is in the zip file attached to this post. nifitoclickhouse.zip

To test it , here is the schema to use.

create DATABASE test

 CREATE TABLE test.demo (
  `id` Int32,
  `cell.name` Array(Nullable(String)),
  `cell.desc` Array(Nullable(String))
) ENGINE = MergeTree PARTITION BY id
ORDER BY
  id SETTINGS index_granularity = 8192

For the fun , I’m demonstrating how to push nested values.

[Stephane-Thales]

Hi, I had the same question and found the answer that I posted here :

https://stackoverflow.com/questions/61120771/how-to-configure-nifi-invokehttp-to-post-insert-in-clickhouse

[alexwirehead]

Hi! The solution is compiling clickhouse jdbc-driver with all dependencies:

• Clone Repo https://github.com/ClickHouse/clickhouse-jdbc
• Move to dir with source code if you use Linux cd clickhouse-jdbc
• Compile jdbc driver with dependencies by Maven: mvn package assembly:single -DskipTests=true
• Path to the jar file looks like ./clickhouse-jdbc/target/clickhouse-jdbc-0.2.4-jar-with-dependencies.jar
• in my installation, I create dir for custom NiFi libs $NIFI_HOME/custom_libs
• Copy clickhouse-jdbc-0.2.4-jar-with-dependencies.jar to custom NiFi lib dir,
• Add DBCPConnectionPool Controller
• Properties looks like:
• • Database Connection URL: jdbc:clickhouse://ip:port/db_name
• • Database Driver Class Name: ru.yandex.clickhouse.ClickHouseDriver
• • Database Driver Location(s): $NIFI_HOME/custom_libs/clickhouse-jdbc-0.2.4-jar-with-dependencies.jar

I hope the solution will help others and save their time:)

[den-crane]

https://github.com/ClickHouse/clickhouse-jdbc/releases

clickhouse-jdbc-0.2.4-shaded.jar --- 5.41 MB

[deniztek]

Hello, according to your directions I've tried to clickhouse input and output on the apache nifi program. However, I encountered some errors during to connecting the clickhouse. I've added DBCPConnection pool then in the configuration section I entered my clickhouse information but as you can see in the picture I got error in the database driver section. In order to solve this problem I compiled JDBC driver with maven but again I encountered an error. Do you have any idea/solution to handle this? Thank you

[DerekChia]

Hello, according to your directions I've tried to clickhouse input and output on the apache nifi program. However, I encountered some errors during to connecting the clickhouse. I've added DBCPConnection pool then in the configuration section I entered my clickhouse information but as you can see in the picture I got error in the database driver section. In order to solve this problem I compiled JDBC driver with maven but again I encountered an error. Do you have any idea/solution to handle this? Thank you

Seems like Nifi could not find the .jar file. Have you tried adding the extension or check that the permission granted is correct?

[Stephane-Thales]

Lately I'm just downloading the JDBC driver on the NiFi server already built with something like :

wget -O clickhouse-jdbc.jar https://github.com/ClickHouse/clickhouse-jdbc/releases/download/v0.2.6/clickhouse-jdbc-0.2.6-shaded.jar

check here the version you need, and get the clickhouse-jdbc-x.x.x-yy-shaded.jar