How to Schedule Queries in ClickHouse

Galaxy Glossary

How can I schedule recurring SQL queries directly inside ClickHouse?

CREATE TASK lets you run ClickHouse queries automatically on a cron-like schedule without external tools.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why schedule queries in ClickHouse?

Automated jobs refresh roll-ups, clean logs, and feed dashboards without manual effort. Native scheduling avoids external cron servers and keeps logic close to data.

What is the CREATE TASK command?

CREATE TASK stores a query in system.tasks and executes it per a cron expression.Tasks survive restarts and can be ENABLED or DISABLED.

How do I create a daily sales summary task?

Use CREATE TASK with ON SCHEDULE to insert aggregated data into a reporting table every night.

CREATE TASK daily_sales_rollup ON SCHEDULE '0 2 * * *' AS INSERT INTO sales_daily (order_date, total_amount) SELECT toDate(order_date), sum(total_amount) FROM Orders GROUP BY toDate(order_date);

How can I pause or resume a task?

ALTER TASK … DISABLE stops execution while keeping the definition.ALTER TASK … ENABLE resumes the schedule.

How do I change the schedule?

ALTER TASK with a new CRON string updates when the query runs. The task ID remains the same, so monitoring scripts stay intact.

How do I monitor task runs?

system.task_log records start time, duration, state, and errors. Query it to build alerts or dashboards.

Best practices for scheduling

1. Aggregate into new tables, not source tables. 2. Guard against duplicates with toStartOfInterval filters. 3.Log errors to Slack via Webhooks.

Common mistakes

Missing timezone: Cron strings run in server TZ. Set timezone parameter when servers span regions.

Heavy joins at peak: Schedule ETL during off-hours to prevent resource contention.

Need more complex workflows?

Chain tasks by appending INSERT … SELECT from yesterday’s roll-up, or orchestrate with Airflow when dependencies grow.

Why How to Schedule Queries in ClickHouse is important

How to Schedule Queries in ClickHouse Example Usage


-- Nightly inventory snapshot for ‘Products’ table
CREATE TASK nightly_inventory_snapshot
ON SCHEDULE '30 1 * * *'
AS INSERT INTO inventory_history
SELECT id, name, stock, now() AS snapshot_time
FROM Products;

How to Schedule Queries in ClickHouse Syntax


CREATE [OR REPLACE] TASK [IF NOT EXISTS] task_name
ON SCHEDULE '<cron_expression>' [SETTING schedule_timezone = 'UTC']
[ENABLE | DISABLE]
AS <query>;

ALTER TASK task_name
    ENABLE | DISABLE |
    ON SCHEDULE '<new_cron_expression>' |
    AS <new_query>;

DROP TASK [IF EXISTS] task_name;

Common Mistakes

Using a '*' in the seconds field. ClickHouse cron parser follows "minute hour dom month dow"; a six-field spec causes a syntax error. Remove the seconds slot.
Forgetting to ENABLE the task after creation with DISABLE flag. The task stays inactive and no data loads. Fix with ALTER TASK my_job ENABLE.