Ingest data to and query data from ClickHouse

Run an end-to-end ClickHouse workflow that creates a database and table, loads sample rows, and queries the results into Kestra storage. This blueprint shows how to drive ClickHouse DDL and DML from a single declarative pipeline, so you can stand up analytics tables, seed them with data, and read them back without stitching together ad hoc scripts or cron jobs. It is a clean starting point for ingestion and reporting on ClickHouse, the column-oriented OLAP database built for fast aggregate queries.

How it works

The flow chains four io.kestra.plugin.jdbc.clickhouse.Query tasks that run in order:

• create_database issues CREATE DATABASE IF NOT EXISTS helloworld so the target database exists.
• create_table runs CREATE TABLE IF NOT EXISTS helloworld.my_first_table with a MergeTree() engine and a PRIMARY KEY (user_id, timestamp).
• insert_data performs an INSERT INTO helloworld.my_first_table to load several sample rows.
• query_and_store_as_json runs SELECT user_id, message FROM helloworld.my_first_table with fetchType: STORE, persisting the result set to Kestra internal storage for downstream tasks.

Connection settings are centralized in pluginDefaults for the ClickHouse Query type, setting the url (jdbc:clickhouse://host.docker.internal:8123/) and username once for every task.

What you get

• A repeatable, idempotent setup that creates the database and table only if they are missing.
• Seeded sample data demonstrating typed columns (String, DateTime, Float32).
• Query output stored as a file in Kestra internal storage, ready to pass to other tasks.
• A single source of connection config via pluginDefaults.

Who it's for

• Data engineers building ClickHouse ingestion and reporting pipelines.
• Analytics engineers prototyping MergeTree table designs.
• Platform teams who want ClickHouse operations expressed as version-controlled YAML.

Why orchestrate this with Kestra

ClickHouse executes SQL fast, but it has no built-in scheduler or dependency engine to sequence DDL, loads, and reads, retry transient failures, or track outputs across runs. With Kestra you get event and schedule triggers, automatic retries, run history and lineage, and a fully declarative YAML definition. The query result is captured as a durable artifact you can hand to the next step, something a raw SQL client cannot do on its own.

Prerequisites

• A running ClickHouse server reachable at the configured JDBC url.
• The Kestra ClickHouse (JDBC) plugin available in your instance.

Secrets

This blueprint uses inline pluginDefaults for connection settings and references no Kestra secrets. For production, replace the hardcoded username and add a password using {{ secret('CLICKHOUSE_PASSWORD') }}.

Quick start

1. Start ClickHouse locally in Docker: docker run -d -p 8123:8123 -p 9000:9000 --name myclickhouse --ulimit nofile=262144:262144 clickhouse/clickhouse-server.
2. Add this flow in the Kestra UI.
3. Execute it and confirm the database, table, and inserted rows.
4. Open the query_and_store_as_json output to inspect the stored result.

How to extend

• Point the url at a managed ClickHouse cluster and add credentials via secrets.
• Replace the sample INSERT with a load from object storage, a database, or an API.
• Add a schedule or event trigger to refresh tables automatically.
• Fan out queries and feed the stored results into dbt, BI exports, or alerts.

Links

• Kestra ClickHouse plugin