Running SQL scripts in batch with clickhouse-client

Some jobs mean running the same statement many times with different arguments: OPTIMIZE one partition at a time, DROP PARTITION for each old month, ALTER ... MATERIALIZE per partition to spread the load. You do not have to write that script by hand. ClickHouse can generate it, and clickhouse-client can run it unattended.

The examples here use the demo analytics.metrics table, partitioned by month.

Step 1: generate the statements with SQL

The format function substitutes values into a template, one row at a time. The $$ ... $$ string syntax lets the template contain single quotes without escaping.

SELECT format($$OPTIMIZE TABLE analytics.metrics PARTITION ID '{0}' FINAL; SELECT sleep(1);$$, partition_id) AS sql
FROM (
    SELECT partition_id
    FROM system.parts
    WHERE database = 'analytics' AND `table` = 'metrics' AND active
    GROUP BY partition_id
    HAVING count() > 1
    ORDER BY count() DESC
    LIMIT 20
)

Each row is one line of the final script. The SELECT sleep(1) after each statement is a pause so the server is not hit by twenty heavy operations back to back; adjust or remove it. sleep accepts up to three seconds per call.

The selection drives the work: here only partitions with more than one active part, busiest first. Changing the WHERE or HAVING is how you choose the targets.

Step 2: write the script to a file

Add INTO OUTFILE and a raw output format so the file contains plain text, not table borders or quoting. TSVRaw writes each row on its own line without escaping.

SELECT format($$OPTIMIZE TABLE analytics.metrics PARTITION ID '{0}' FINAL; SELECT sleep(1);$$, partition_id) AS sql
FROM (
    SELECT partition_id
    FROM system.parts
    WHERE database = 'analytics' AND `table` = 'metrics' AND active
    GROUP BY partition_id
    HAVING count() > 1
    ORDER BY count() DESC
    LIMIT 20
)
INTO OUTFILE 'optimize.sql'
FORMAT TSVRaw

INTO OUTFILE is a client feature: the file is created on the machine where you run the client, not on the server, and the command fails if the file already exists. The result:

OPTIMIZE TABLE analytics.metrics PARTITION ID '202609' FINAL; SELECT sleep(1);
OPTIMIZE TABLE analytics.metrics PARTITION ID '202612' FINAL; SELECT sleep(1);
OPTIMIZE TABLE analytics.metrics PARTITION ID '202702' FINAL; SELECT sleep(1);

Read the file before you run it. Generating is cheap; running the wrong script is not.

Newer versions have a SQLInsert output format that writes a result set as INSERT statements, which covers the other common case, copying rows into another table.

Step 3: run it

clickhouse-client --echo --receive_timeout 900 --queries-file optimize.sql

• --queries-file reads statements from a file and runs them in order. A statement that fails stops the script unless you add --ignore-error.
• --echo prints each statement before running it, so the output shows where it is.
• --receive_timeout 900 raises the wait for a reply to 15 minutes. OPTIMIZE ... FINAL on a large partition is silent for a long time and the default 300 seconds can expire first.

You can check the whole flow in clickhouse local; the flags are the same:

clickhouse local --echo --queries-file optimize.sql

OPTIMIZE TABLE analytics.metrics PARTITION ID '202612' FINAL;
SELECT sleep(1);
0

Step 4: keep it running if your connection drops

If the script runs in a terminal over SSH and the connection breaks, the client dies with it, and the statement in flight keeps running on the server while the rest of the script never starts. Run it in something that survives the disconnect.

With tmux:

tmux new -s batch
clickhouse-client --echo --receive_timeout 900 --queries-file optimize.sql
# detach with Ctrl-b d, reattach later with: tmux attach -t batch

Or with nohup, logging to a file:

nohup clickhouse-client --echo --receive_timeout 900 --queries-file optimize.sql > optimize.log 2>&1 &
tail -f optimize.log

Checking progress and cleaning up

• SELECT query, elapsed FROM system.processes shows the statement running now.
• SELECT * FROM system.merges shows the merge behind an OPTIMIZE.
• To stop: KILL QUERY WHERE query_id = '...', then stop the client.
• For statements that need a cluster, add ON CLUSTER my_cluster in the template, or run the same script against each node.

Same idea, other jobs

Replace the template to get other batches, for instance dropping partitions older than a cutoff:

SELECT format($$ALTER TABLE analytics.metrics DROP PARTITION ID '{0}';$$, partition_id) AS sql
FROM (
    SELECT DISTINCT partition_id
    FROM system.parts
    WHERE database = 'analytics' AND `table` = 'metrics' AND active
      AND max_date < today() - 365
)
FORMAT TSVRaw

Always generate to a file and read it first: with DROP PARTITION there is no undo.