Dynamic column selection (also known as a COLUMNS expression) allows you to match some columns in a result with a re2 regular expression.

#Matchers

Select all columns:

SELECT * FROM hits;

Select only specific subset of columns:

SELECT COLUMNS('URL.*ID') FROM hits;

┌─URLCategoryID─┬─URLRegionID─┐
│         15664 │         216 │
│             0 │           0 │
│             0 │           0 │
│             0 │           0 │
│             0 │           0 │
└───────────────┴─────────────┘

SELECT COLUMNS(WatchID, UserID) FROM hits;

┌─────────────WatchID─┬───────────────UserID─┐
│ 5365534025466744368 │ 2213399965098237040  │
│ 5287559196528052048 │ 2213399965098237040  │
│ 9057961221679572813 │ 2213399965098237040  │
│ 5520508008786474572 │ 9141107111321352513  │

#Column transformers

Apply transformations for selected columns or remove some columns from selections:

APPLY: Allows you to invoke some function for each row returned by an outer table expression of a query.

SELECT * APPLY toString FROM hits;
SELECT COLUMNS('URL.*ID') APPLY toString FROM hits;
SELECT COLUMNS('URL.*ID') APPLY x -> toString(x) FROM hits;

┌─toString(URLCategoryID)─┬─toString(URLRegionID)─┐
│ 15664                   │ 216                   │
│ 0                       │ 0                     │
│ 0                       │ 0                     │
│ 0                       │ 0                     │
│ 0                       │ 0                     │
└─────────────────────────┴───────────────────────┘

EXCEPT: exclude one or more columns from the result.

SELECT * EXCEPT (UserID, URLRegionID) FROM hits;
SELECT COLUMNS('URL.*ID') EXCEPT URLCategoryID FROM hits;

┌─URLRegionID─┐
│         216 │
│           0 │
│           0 │
│           0 │
│           0 │
└─────────────┘

REPLACE: Specifies one or more expression aliases

SELECT COLUMNS('URL.*ID') REPLACE (URLCategoryID * 10 AS URLCategoryID)
FROM hits;

┌─URLCategoryID─┬─URLRegionID─┐
│        156640 │         216 │
│             0 │           0 │
│             0 │           0 │
│             0 │           0 │
│             0 │           0 │
└───────────────┴─────────────┘

SELECT COLUMNS('URL.*ID') REPLACE (leftPad(toString(URLRegionID), 10, '*') AS URLRegionID)
FROM hits;

┌─URLCategoryID─┬─URLRegionID─┐
│         15664 │ *******216  │
│             0 │ *********0  │
│             0 │ *********0  │
│             0 │ *********0  │
│             0 │ *********0  │
└───────────────┴─────────────┘

We can also combine them:

SELECT COLUMNS('URL.*ID') APPLY(toString) APPLY(length) APPLY(max) FROM hits;

┌─max(length(toString(URLCategoryID)))─┬─max(length(toString(URLRegionID)))─┐
│                                    5 │                                  5 │
└──────────────────────────────────────┴────────────────────────────────────┘

#Select from multiple tables

merge() create a temporary Merge table with structure is taken from the first table encountered that matches the regular expression.

When upgrading ClickHouse usually rename system table if schema changed in a new release: system.query_log, system.query_log_0, system.query_log_1, ... The query below help querying from all them:

SELECT * FROM merge(system, '^query_log')