ClickHouse input

Read a table or SQL query from ClickHouse. Columns show without a run, even for a query you write yourself. A Bookmark can read only the rows added or changed since the last successful run.

Category

Input

Connection

ClickHouse

What it does

Reads from ClickHouse through a saved Connection and streams the result into the pipeline in batches. Pick a table with the live database and table browser, or write your own SQL. ClickHouse hands its columns over in the pipeline's own columnar format, so the builder shows them WITHOUT a run, for a table AND for a query you type yourself. Credentials live on the Connection.

Inputs and outputs

When to use it

• Pull event, log or clickstream data out of ClickHouse for downstream work
• Push filtering and aggregation down to ClickHouse so far less data crosses the wire
• Move a ClickHouse result into a warehouse, a file, or a spreadsheet

Examples

default.events

SELECT user_id, count() AS hits FROM events GROUP BY user_id

SELECT * FROM events WHERE event_time >= now() - INTERVAL 1 DAY

Bookmark column: updated_at · Start from: 2026-01-01 00:00:00

Tips

• Press ⤢ beside SQL query for the SQL Studio: a full-screen editor that browses the connection's schemas, tables and columns, shows the columns your query returns without running it, and runs it with Run to here without closing.
• Columns appear in the builder as soon as the query is valid, with no run needed. They are read with a zero-row probe, so it costs nothing.
• This query is ClickHouse SQL, the server's own dialect, which is different from Trinium's row expressions.
• Types come across natively: numbers, dates, timestamps and decimals stay typed rather than arriving as text.
• Batch size maps to the server's block size. Blank is almost always right.
• The default HTTP port is 8123; managed and TLS endpoints usually use 8443.
• Bookmark: each Live run reads only the rows whose bookmark column is past where the last SUCCESSFUL run stopped. A failed or cancelled run never moves it, so after an outage one run reads everything that was missed. Staging and Production keep their own; a Test run always reads from Start from and never moves it.
• The bookmark column must move forward whenever a row is added or changed: an updated_at stamp, or an ever-increasing id. A row whose bookmark column is empty is never read, and a row DELETED in the database is never seen, so reload the table now and then if deletes matter.
• With a hand-written query, write WHERE updated_at >= ${bookmark} where the value goes. Editing the table, query, connection or column keeps the bookmark, and the run's Messages say when it last read something else. A Bookmark column of another kind (a date where it was a number) is refused until you Set or Reset it in the node's Bookmark panel.
• Each value of a text parameter the pipeline uses keeps its own bookmark, so region = EU and region = US each read only their own new rows. A number or true/false parameter in the table name is refused, because one bookmark would serve every value; make it a text parameter with a list of choices. A date in the table name (events_${date:%Y%m%d}) carries the bookmark into each new day's table, so bookmark a date-time column there.
• When the pipeline uses a text parameter anywhere, such as ${region} in this query or in a Filter after it, each value keeps its own bookmark, so a run for one region never moves another's. A value's first run reads from Start from, and date, number and true/false parameters share one bookmark. Starting or stopping using a text parameter pauses the bookmark the same way, until you Set where the value starts or Reset the old bookmark.
• Lookback (date-time columns only) re-reads the N minutes before where the last successful run stopped, to catch rows a long transaction committed late. A first run and every Test run read exactly from Start from. Re-read rows load again, so use it with an Upsert output.

Related nodes