Clickhouse Query
CertifiedExecute a single SQL query against ClickHouse
Clickhouse Query
Execute a single SQL query against ClickHouse
Runs one SQL statement and fetches results from ClickHouse columnar database. Supports parameterized queries, transactions with afterSQL, and multiple fetch modes (FETCH, FETCH_ONE, STORE). Default fetchSize is 10,000 rows for STORE mode.
type: io.kestra.plugin.jdbc.clickhouse.QueryExamples
Query a Clickhouse database.
id: clickhouse_query
namespace: company.team
tasks:
- id: query
type: io.kestra.plugin.jdbc.clickhouse.Query
url: jdbc:clickhouse://127.0.0.1:56982/
username: "{{ secret('CLICKHOUSE_USERNAME') }}"
password: "{{ secret('CLICKHOUSE_PASSWORD') }}"
sql: SELECT * FROM clickhouse_types
fetchType: STORE
Ingest data to and query data from ClickHouse
id: query_clickhouse
namespace: company.team
tasks:
- id: create_database
type: io.kestra.plugin.jdbc.clickhouse.Query
sql: CREATE DATABASE IF NOT EXISTS helloworld
url: jdbc:clickhouse://host.docker.internal:8123/
username: default
- id: create_table
type: io.kestra.plugin.jdbc.clickhouse.Query
url: jdbc:clickhouse://host.docker.internal:8123/
username: default
sql: |
CREATE TABLE IF NOT EXISTS helloworld.my_first_table
(
user_id String,
message String,
timestamp DateTime,
metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp)
- id: insert_data
type: io.kestra.plugin.jdbc.clickhouse.Query
url: jdbc:clickhouse://host.docker.internal:8123/
username: default
sql: |
INSERT INTO helloworld.my_first_table (user_id, message, timestamp,
metric) VALUES
(101, 'Hello, ClickHouse!', now(), -1.0 ),
(102, 'Insert a lot of rows per batch', yesterday(), 1.41421 ),
(102, 'Sort your data based on your commonly-used queries', today(), 2.718 ),
(101, 'Granules are the smallest chunks of data read', now() + 5, 3.14159 )
- id: query_and_store_as_json
type: io.kestra.plugin.jdbc.clickhouse.Query
sql: SELECT user_id, message FROM helloworld.my_first_table
fetchType: STORE
url: jdbc:clickhouse://host.docker.internal:8123/
username: default
Properties
sql *string
SQL statement(s) to execute
Runs one or more SQL statements rendered with flow variables. Query tasks accept a single statement; Queries tasks can execute multiple statements separated by semicolons
url *string
The JDBC URL to connect to the database
afterSQL string
SQL to execute after main query in same transaction
Optional SQL executed in the same transaction after the main statement. Useful for marking rows as processed to avoid duplicates; only a single statement is allowed. Commit covers both sql and afterSQL
assets
Assets this task consumes as inputs or produces as outputs, for lineage tracking and the asset graph (Enterprise Edition). A flow declaring this property on a task is rejected in the open-source edition.
io.kestra.core.models.assets.AssetsDeclaration
IGNOREFAILWARNAsset failure behavior
Behavior applied to the task state when a declared asset fails to render, emit, or be persisted (e.g. a lock conflict): FAIL escalates it to FAILED, WARN (default) warns it if it would otherwise succeed, IGNORE leaves the state untouched.
Whether to auto-register assets referenced dynamically at runtime that are not statically declared in inputs or outputs.
The assets consumed as inputs.
io.kestra.core.models.assets.AssetIdentifier
1The assets produced as outputs.
io.kestra.plugin.ee.assets.Dataset
1150{}1150io.kestra.plugin.ee.assets.File
1150{}1150io.kestra.plugin.ee.assets.Table
1150{}1150io.kestra.plugin.ee.assets.VM
1150{}1150io.kestra.core.models.assets.External
1150{}1150io.kestra.core.models.assets.Custom
11501Custom asset type
{}1150connectionPoolSize integerstring
10Maximum number of pooled connections
Maximum connections held in the pool for a given URL and credentials. Default 10. Increase for flows that run many concurrent queries against the same database to avoid waiting for an available connection. Ignored when connectionPooling is false or for embedded drivers.
connectionPooling booleanstring
trueReuse database connections via a connection pool
When true (default), connections are pooled and reused across executions, keyed by URL and credentials, removing the connect and TLS-handshake cost on each run. Set to false if your SQL relies on session state persisting on the connection (for example SET search_path, session-scoped temp tables or variables), since pooled connections are reused. Embedded drivers (DuckDB, SQLite, MS Access) never pool regardless of this setting.
fetchSize integerstring
10000Number of rows to fetch per database round trip
Controls JDBC fetch size for STORE mode. Default: 10,000 rows; use Integer.MIN_VALUE for MySQL streaming. Ignored for FETCH and FETCH_ONE
fetchType string
NONESTOREFETCHFETCH_ONENONEResult fetching mode
FETCH returns all rows, FETCH_ONE returns the first row only, STORE streams rows to internal storage (ION), NONE returns no data. Default: NONE
outputFiles array
Output file names to capture after SQL execution
Creates named temporary files in the task working directory before the SQL runs, making their absolute paths available as {{ outputFiles.name }} Pebble variables in the SQL template. Only supported by embedded, in-process drivers (DuckDB, SQLite) where the database engine writes to the same filesystem as the Kestra worker. Remote database drivers (Postgres, MySQL, etc.) do not support this — they execute SQL on a separate server that cannot write to the Kestra worker filesystem.
parameters object
Named parameter bindings for SQL query
Map of parameter names to values. Use : name placeholders rendered then bound as prepared-statement parameters; supports nulls and typed values
password string
The database user's password
timeZoneId string
The time zone id to use for date/time manipulation. Default value is the worker's default time zone id
username string
The database user
Outputs
row object
First row of fetched data
Only populated when fetchType is FETCH_ONE
rows array
List of all fetched rows
Only populated when fetchType is FETCH
size integer
Number of rows fetched
Only populated when fetchType is FETCH or STORE
uri string
uriURI of stored results in internal storage
Only populated when fetchType is STORE; file is stored in internal storage using ION format
Metrics
fetch.size counter
rowsThe number of fetched rows.
