ClickHouse output

Load rows into a ClickHouse table (append or replace; auto-creates a MergeTree).

Category: Output
Connection: ClickHouse

What it does

Loads the incoming rows into a ClickHouse table through a saved Connection. Rows are handed over in ClickHouse's columnar bulk-load format, the shape it ingests fastest, and the table is created automatically if it doesn't exist.

Inputs and outputs

Inputs	One.
Outputs	None. This node ends a pipeline.

When to use it

Land prepared data in ClickHouse for dashboards or customer-facing analytics
Refresh a reporting table on a schedule
Move data from a database, warehouse or file into ClickHouse

Examples

Load, then merge parts

Pre-SQL: TRUNCATE TABLE IF EXISTS stg_events · Post-SQL: OPTIMIZE TABLE events FINAL

OPTIMIZE after a large load merges the parts the insert created, so reads are fast again.

Append

default.events

Adds rows to whatever is already there.

Full refresh

Write mode: truncate

Empties the table first, then loads.

Sort key

event_time, user_id

Used when the table is auto-created.

Tips

Each pre- and post-SQL statement has its own ⤢, the SQL Studio, with the connection's tables and columns beside the statement. It has no Run button: a Test run executes none of these statements.
Pre-SQL and post-SQL run on this same connection, around the load. Pre-SQL runs and commits BEFORE the load starts; post-SQL runs only AFTER it has finished successfully, so a failed load never fires the merge.
One statement per row, run in order. If one fails the run stops there, and the message names which statement it was and what the database said.
A Test run (Run to here) writes nothing and executes none of them, so testing stays safe on a pipeline whose pre-SQL truncates a table.
The sort key is the single biggest lever on read speed. Set it to the columns you filter on most, usually a timestamp first. It only applies when this node creates the table.
There is no upsert mode, deliberately: ClickHouse has no single-statement merge, and its de-duplicating table engines only settle in the background, so an upsert here could not honestly promise unique rows. Load into a staging table and transform if you need that.
The load is not all-or-nothing: a failure part way through can leave the rows already sent. Use truncate mode for a clean full refresh.
An auto-created table gets nullable columns, except any column named in the sort key: a sort-key column cannot be nullable.
If the table already exists, its own column types win and the incoming values are converted to them.