ReplacingMergeTree
table engine with append_only_mode
offers the best real-time data performance for large datasets.
ReplacingMergeTree engine is used for all sink tables by default. If you don’t want to use a ReplacingMergeTree, you can pre-create the table with any data engine you’d like. If you don’t want to use a ReplacingMergeTree, you can disable append_only_mode
.
Full configuration details for Clickhouse sink is available in the reference page.
http
interface (often port 8443
), rather than the tcp
interface (often port 9000
).ReplacingMergeTree
tables, we can write, overwrite, and flag rows with the same primary key for deletes without actually mutating. As a result, the actual raw data in the table may contain duplicates.
ClickHouse allows you to clean up duplicates and deletes from the table by running
FINAL
keyword after the table name for queries.
append_only_mode=true
(v2: appendOnlyMode=true
), the pipeline may hit ClickHouse mutation flush limits. Write speed will also be slower due to mutations.append_only_mode
(v2: appendOnlyMode
) is true
(default and recommended for ReplacingMergeTrees), the sink behaves the following way:
is_deleted
column is automatically added to a table. It contains 1
in case of deletes, 0
otherwise.versionColumnName
is specified, it’s used as a version number column for deduplication. If it’s not specified, insert_time
column is automatically added to a table. It contains insertion time and is used for deduplication.ORDER BY
clause.append_only_mode
(v2: appendOnlyMode
) is false
:
PRIMARY KEY
clause.