New command: EVAL

[ramazanpolat]

Use case

We use CH to generate CH commands a lot. Then we again use CH to run those CH generated commands :)

Typical use case without run command:
Step 1: Generate SQL commands
E.g. I'd like to drop ignored parts and for this, I have to generate ALTER TABLE x DROP DETACHED PART y statements for relevant parts.

SELECT format($$ALTER TABLE {0}.{1} DROP DETACHED PART '{2}' SETTINGS allow_drop_detached=1;$$, database, table, name) as drop
FROM system.detached_parts
WHERE startsWith(name, 'ignored') INTO OUTFILE 'drop-ignored-parts-on-clickhouse-0.sql' FORMAT TSVRaw

Sample of generated SQL command:

ALTER TABLE default.abc DROP DETACHED PART 'ignored_20221001_27115_27115_0' SETTINGS allow_drop_detached=1;
ALTER TABLE default.abc DROP DETACHED PART 'ignored_20221001_27112_27112_0' SETTINGS allow_drop_detached=1;
ALTER TABLE default.abc DROP DETACHED PART 'ignored_20221001_27113_27113_0' SETTINGS allow_drop_detached=1;
ALTER TABLE default.abc DROP DETACHED PART 'ignored_20221001_27114_27114_0' SETTINGS allow_drop_detached=1;
ALTER TABLE default.abc DROP DETACHED PART 'ignored_20221001_27111_27111_0' SETTINGS allow_drop_detached=1;

Step 2: Quit clickhouse-client and use clickhouse-client to run the generated SQL commands:

clickhouse-client --echo --queries-file drop-ignored-parts-on-clickhouse-0.sql

Step 3: Do this for all nodes.

Describe the solution you'd like

A simple run command would be doing this in one line.

clickhouse :) run(SELECT format($$ALTER TABLE {0}.{1} DROP DETACHED PART '{2}' SETTINGS allow_drop_detached=1;$$, database, table, name) as drop
FROM system.detached_parts
WHERE startsWith(name, 'ignored'))

Describe alternatives you've considered

We may also have ON CLUSTER for run command which would be awesome:

run (SELECT format($$CREATE TABLE default.host_ingestion_stats_{0} ....$$, getMacro('host_index'))) ON CLUSTER my_cluster

Additional context

I'm not sure if the run command should take String as parameter or just the query, like in view function. It may run the parameter if it is the String and if the parameter is not a String, it may run the SQL and then run the result of the SQL.

[andrea-s]

I have also thought about this one many times. Maybe "eval" would be a better name.