Как выбрать только нужные колонки по шаблону, не перечисляя их в SELECT

В прошлом посте я рассказывал про модификатор APPLY, с помощью которого можно применять функцию ко всем колонкам сразу. Сегодня расскажу про еще один мощный инструмент — динамический выбор столбцов с помощью выражения COLUMNS.

Иногда бывают ситуации, когда в таблице десятки или сотни полей, а нам нужны только те, которые, например, начинаются с utm_ или содержат в названии слово id.

В классических базах данных мы бы сидели и вручную перечисляли названия всех нужных колонок в SELECT. В ClickHouse это делается гораздо изящнее.

Чтобы не писать десятки колонок руками, можно использовать выражение COLUMNS('regexp'). Оно автоматически находит и подставляет в выборку все столбцы, названия которых подходят под регулярное выражение.

В качестве примера рассмотрим таблицу traffic, в которой хранятся метки рекламных кампаний и метрики:

┌─utm_source─┬─utm_medium─┬─utm_campaign─┬─clicks─┬─costs─┐
│     google │        cpc │      promo_1 │    150 │    10 │
│     yandex │        cpc │      promo_2 │    200 │    15 │
└────────────┴────────────┴──────────────┴────────┴───────┘

Чтобы выбрать только utm-метки, обычно мы привыкли писать так:

SELECT 
  utm_source, 
  utm_medium, 
  utm_campaign 
FROM traffic

С использованием COLUMNS можно написать гораздо короче:

SELECT COLUMNS('utm') 
FROM traffic

┌─utm_source─┬─utm_medium─┬─utm_campaign─┐
│     google │        cpc │      promo_1 │
│     yandex │        cpc │      promo_2 │
└────────────┴────────────┴──────────────┘

Несколько нюансов:

1. Внутри скобок пишется регулярное выражение (синтаксис re2). Например, COLUMNS('a') выберет все колонки, в названии которых есть хотя бы одна буква «a».

2. Выбранные столбцы возвращаются в том порядке, в котором лежат в таблице (а не по алфавиту).

3. Если регулярному выражению не соответствует ни один столбец, и кроме COLUMNS в SELECT больше ничего нет — ClickHouse выдаст ошибку.

Ссылка на доку.

Мои статьи по ClickHouse на Хабре.

P.S. Систематизировать знания и получить крепкую базу можно на моем бесплатном курсе «ClickHouse с нуля». А закрепить пройденный материал на его практическом продолжении «ClickHouse с нуля: практика».