Skip to main content
Version: Next

Clickhouse

Clickhouse sink connector

Support Those Engines

Spark
Flink
SeaTunnel Zeta

Key Features

The Clickhouse sink plug-in can achieve accuracy once by implementing idempotent writing, and needs to cooperate with aggregatingmergetree and other engines that support deduplication.

Description

Used to write data to Clickhouse.

Supported DataSource Info

In order to use the Clickhouse connector, the following dependencies are required. They can be downloaded via install-plugin.sh or from the Maven central repository.

DatasourceSupported VersionsDependency
ClickhouseuniversalDownload

Data Type Mapping

SeaTunnel Data TypeClickhouse Data Type
STRINGString / Int128 / UInt128 / Int256 / UInt256 / Point / Ring / Polygon MultiPolygon
INTInt8 / UInt8 / Int16 / UInt16 / Int32
BIGINTUInt64 / Int64 / IntervalYear / IntervalQuarter / IntervalMonth / IntervalWeek / IntervalDay / IntervalHour / IntervalMinute / IntervalSecond
DOUBLEFloat64
DECIMALDecimal
FLOATFloat32
DATEDate
TIMEDateTime
ARRAYArray
MAPMap

Sink Options

NameTypeRequiredDefaultDescription
hostStringYes-ClickHouse cluster address, the format is host:port , allowing multiple hosts to be specified. Such as "host1:8123,host2:8123".
databaseStringYes-The ClickHouse database.
tableStringYes-The table name.
usernameStringYes-ClickHouse user username.
passwordStringYes-ClickHouse user password.
clickhouse.configMapNoIn addition to the above mandatory parameters that must be specified by clickhouse-jdbc , users can also specify multiple optional parameters, which cover all the parameters provided by clickhouse-jdbc.
bulk_sizeStringNo20000The number of rows written through Clickhouse-jdbc each time, the default is 20000.
split_modeStringNofalseThis mode only support clickhouse table which engine is 'Distributed'.And internal_replication option-should be true.They will split distributed table data in seatunnel and perform write directly on each shard. The shard weight define is clickhouse will counted.
sharding_keyStringNo-When use split_mode, which node to send data to is a problem, the default is random selection, but the 'sharding_key' parameter can be used to specify the field for the sharding algorithm. This option only worked when 'split_mode' is true.
primary_keyStringNo-Mark the primary key column from clickhouse table, and based on primary key execute INSERT/UPDATE/DELETE to clickhouse table.
support_upsertBooleanNofalseSupport upsert row by query primary key.
allow_experimental_lightweight_deleteBooleanNofalseAllow experimental lightweight delete based on *MergeTree table engine.
common-optionsNo-Sink plugin common parameters, please refer to Sink Common Options for details.

How to Create a Clickhouse Data Synchronization Jobs

The following example demonstrates how to create a data synchronization job that writes randomly generated data to a Clickhouse database:

# Set the basic configuration of the task to be performed
env {
parallelism = 1
job.mode = "BATCH"
checkpoint.interval = 1000
}

source {
FakeSource {
row.num = 2
bigint.min = 0
bigint.max = 10000000
split.num = 1
split.read-interval = 300
schema {
fields {
c_bigint = bigint
}
}
}
}

sink {
Clickhouse {
host = "127.0.0.1:9092"
database = "default"
table = "test"
username = "xxxxx"
password = "xxxxx"
}
}

Tips

1.SeaTunnel Deployment Document.
2.The table to be written to needs to be created in advance before synchronization.
3.When sink is writing to the ClickHouse table, you don't need to set its schema because the connector will query ClickHouse for the current table's schema information before writing.

Clickhouse Sink Config

sink {
Clickhouse {
host = "localhost:8123"
database = "default"
table = "fake_all"
username = "xxxxx"
password = "xxxxx"
clickhouse.config = {
max_rows_to_read = "100"
read_overflow_mode = "throw"
}
}
}

Split Mode

sink {
Clickhouse {
host = "localhost:8123"
database = "default"
table = "fake_all"
username = "xxxxx"
password = "xxxxx"

# split mode options
split_mode = true
sharding_key = "age"
}
}

CDC(Change data capture) Sink

sink {
Clickhouse {
host = "localhost:8123"
database = "default"
table = "fake_all"
username = "xxxxx"
password = "xxxxx"

# cdc options
primary_key = "id"
support_upsert = true
}
}

CDC(Change data capture) for *MergeTree engine

sink {
Clickhouse {
host = "localhost:8123"
database = "default"
table = "fake_all"
username = "xxxxx"
password = "xxxxx"

# cdc options
primary_key = "id"
support_upsert = true
allow_experimental_lightweight_delete = true
}
}