Data Cookbook Kitchen

Many database systems offer the ability to evaluate an arbitrary string as SQL and run the resulting query - EXECUTE IMMEDIATE in Oracle, EXECUTE in Postgres. ClickHouse didn’t have this ability to run dynamic SQL - until recently. But this changed with version 26.7. Let’s take a look.

Parameters in queries

What ClickHouse has had for a long time is query parameters. In the client or in the SQL console, you could write

SET param_name = 'John Doe';
SET param_age = 25;
SET param_salary = 75000.50;

SELECT name, age, salary FROM users
WHERE name = {name: String}
  AND age >= {age: UInt8}
  AND salary <= {salary: Float64};

Note the convention: parameter names are prefixed with param_. This convention also holds when sending the query via the client CLI, or just via HTTPS using curl:

curl -sS "http://localhost:8123/?param_id=2&param_phrase=test" \
  -d "SELECT * FROM table WHERE int_column = {id:UInt8} and string_column = {phrase:String}"

This, in itself, is not very dynamic. But you can introduce a certain amount of dynamicism using the parameter type Identifier. For instance, you can use an Identifier parameter to stand in for

  • a database name
  • a table name
  • a column name, which allows for construction filter conditions dynamically.

This is legit:

SET param_col = 'postcode1';
SELECT {col: Identifier} FROM uk_price_paid;
SELECT * FROM uk_price_paid WHERE {col: Identifier} = 'B98';

But you cannot use this construct to replace functions, keywords, or whole SQL snippets. A short overview of the limitations is here.

The eval function

ClickHouse 26.7 introduces the experimental eval table function. This allows for dynamic construction of SELECT queries. Because of its experimental status, the function needs to be explicitly enabled with:

SET allow_experimental_eval_table_function = 1;

It also requires the analyzer (enable_analyzer, enabled by default).

Then you can write:

SELECT * FROM eval('SEL' || 'ECT 1 AS x');

Limitations

The main limitation of eval is this: the argument has to resolve to a single SELECT query without output clauses or other decorations. This is because eval is implemented as a table function - the result of the function is the result of the query that is passed to it. So, DDL commands are not possible.

There are a number of other limitations which are listed in the docs.

Queuing dynamic SQL: The QueryRunner table engine

A way to run arbitrary, dynamically defined SQL commands is the QueryRunner table engine.

In its simplest form, creating a QueryRunner table looks like this:

CREATE TABLE runner (
    query String
)
ENGINE = QueryRunner;

Additional columns can specify the database to run against, and the settings to be applied to each particular query.

Then insert a SQL command string:

INSERT INTO runner VALUES('CREATE TABLE t1 (i Int64)');

and the command will be executed.

With this method to run SQL, any output will be discarded; you need to INSERT ... SELECT the result into another table in order to keep it. For other limitations, see here.

There are a number of settings you can specify upon creating the QueryRunner table:

  • cluster and shard specify where to run the queries
  • mode is by default 'asynchronous', meaning queries are queued up and the INSERT returns immediately. In 'synchronous' mode, INSERT returns only after all queries of the inserted batch have finished.
  • threads: number of background threads executing the queries.
  • max_queue_size: Maximum number of queued queries

It is not possible to SELECT from a QueryRunner table.

Conclusion

We demonstrated two ways of running dynamic SQL in ClickHouse:

  • eval() table function:
    • supports only SELECT statements
    • returns the result set of the query that was executed
  • QueryRunnertable engine:
    • supports a wide set of SQL statements
    • queues up inserted queries
    • is “fire-and-forget”
    • discards the results.

Which on you use will depend on the use case. For SELECT queries the eval method has a number of advantages. Use QueryRunner when running dynamic DDL or for batches of queries that can be queued up.

“This image is taken from Page 377 of Praktisches Kochbuch für die gewöhnliche und feinere Küche” by Medical Heritage Library, Inc. is licensed under CC BY-NC-SA 2.0 .