Our task is to run a batch of ~20000 queries in ClickHouse and store results into a CSV file. Each query is a count() aggregation returning a single number.
We do it like this:
./generate_queries.js | clickhouse-client --multiquery | tr '\n' ',' >> metrics.csv
(Yes, trailing comma, we'll fix that.)
Query example:
SELECT count(*) FROM merged_data WHERE business_type = 22;
The problem is that if a query matches zero records, ClickHouse simply returns nothing, and the number of records in resulting CSV file is different from the number of queries.
This might be a standard behaviour for SQL, but how can we work that around and make ClickHouse count() return 0 in case of zero matches?