CREATE VIEW

Создаёт представление. Представления бывают обычные, материализованные (MATERIALIZED) и LIVE.

Обычные представления

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster_name] 
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER | NONE }] 
AS SELECT ...

Обычные представления не хранят никаких данных, они выполняют чтение данных из другой таблицы при каждом доступе. Другими словами, обычное представление — это не что иное, как сохраненный запрос. При чтении данных из представления этот сохраненный запрос используется как подзапрос в секции FROM.

Для примера, пусть вы создали представление:

CREATE VIEW view AS SELECT ...

и написали запрос:

SELECT a, b, c FROM view

Этот запрос полностью эквивалентен использованию подзапроса:

SELECT a, b, c FROM (SELECT ...)

Материализованные представления

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster_name] [TO[db.]name] [ENGINE = engine] [POPULATE] 
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER | NONE }] 
AS SELECT ...

Материализованные (MATERIALIZED) представления хранят данные, преобразованные соответствующим запросом SELECT.

При создании материализованного представления без использования TO [db].[table], нужно обязательно указать ENGINE - движок таблицы для хранения данных.

При создании материализованного представления с использованием TO [db].[table], нельзя указывать POPULATE.

Материализованное представление устроено следующим образом: при вставке данных в таблицу, указанную в SELECT-е, кусок вставляемых данных преобразуется этим запросом SELECT, и полученный результат вставляется в представление.

Важно

Материализованные представления в ClickHouse используют имена столбцов вместо порядка следования столбцов при вставке в целевую таблицу. Если в результатах запроса SELECT некоторые имена столбцов отсутствуют, то ClickHouse использует значение по умолчанию, даже если столбец не является Nullable. Безопасной практикой при использовании материализованных представлений считается добавление псевдонимов для каждого столбца.

Материализованные представления в ClickHouse больше похожи на after insert триггеры. Если в запросе материализованного представления есть агрегирование, оно применяется только к вставляемому блоку записей. Любые изменения существующих данных исходной таблицы (например обновление, удаление, удаление раздела и т.д.) не изменяют материализованное представление.

Если указано POPULATE, то при создании представления в него будут добавлены данные, уже содержащиеся в исходной таблице, как если бы был сделан запрос CREATE TABLE ... AS SELECT ... . Если POPULATE не указано, представление будет содержать только данные, добавленные в таблицу после создания представления. Использовать POPULATE не рекомендуется, так как в представление не попадут данные, добавляемые в таблицу во время создания представления.

Запрос SELECT может содержать DISTINCT, GROUP BY, ORDER BY, LIMIT... Следует иметь ввиду, что соответствующие преобразования будут выполняться независимо, на каждый блок вставляемых данных. Например, при наличии GROUP BY, данные будут агрегироваться при вставке, но только в рамках одной пачки вставляемых данных. Далее, данные не будут доагрегированы. Исключение - использование ENGINE, производящего агрегацию данных самостоятельно, например, SummingMergeTree.

Выполнение запросов ALTER над материализованными представлениями имеет свои особенности, поэтому эти запросы могут быть неудобными для использования. Если материализованное представление использует конструкцию TO [db.]name, то можно выполнить DETACH представления, ALTER для целевой таблицы и последующий ATTACH ранее отсоединенного (DETACH) представления.

Обратите внимание, что работа материализованного представления находится под влиянием настройки optimize_on_insert. Перед вставкой данных в таблицу происходит их слияние.

Представления выглядят так же, как обычные таблицы. Например, они перечисляются в результате запроса SHOW TABLES.

Чтобы удалить представление, следует использовать DROP VIEW. Впрочем, DROP TABLE тоже работает для представлений.

SQL безопасность

Параметры DEFINER и SQL SECURITY позволяют задать правило от имени какого пользователя будут выполняться запросы к таблицам, на которые ссылается представление. Для SQL SECURITY допустимо три значения: DEFINER, INVOKER, или NONE. Для DEFINER можно указать имя любого существующего пользователя или же CURRENT_USER.

Далее приведена таблица, объясняющая какие права необходимы каким пользователям при заданных параметрах SQL безопасности. Обратите внимание, что, в независимости от заданных параметров SQL безопасности, у пользователя должно быть право GRANT SELECT ON <view> для чтения из представления.

SQL security option	View	Materialized View
DEFINER alice	У alice должно быть право SELECT на таблицу-источник.	У alice должны быть права SELECT на таблицу-источник и INSERT на таблицу-назначение.
INVOKER	У пользователя выполняющего запрос к представлению должно быть право SELECT на таблицу-источник.	Тип SQL SECURITY INVOKER не может быть указан для материализованных представлений.
NONE	-	-

Note

Тип SQL SECURITY NONE не безопасен для использования. Любой пользователь с правом создавать представления с SQL SECURITY NONE сможет исполнять любые запросы без проверки прав. По умолчанию, у пользователей нет прав указывать SQL SECURITY NONE, однако, при необходимости, это право можно выдать с помощью GRANT ALLOW SQL SECURITY NONE TO <user>.

Если DEFINER/SQL SECURITY не указан, будут использованы значения по умолчанию:

• SQL SECURITY: INVOKER для обычных представлений и DEFINER для материализованных (изменяется в настройках)
• DEFINER: CURRENT_USER (изменяется в настройках)

Если представление подключается с помощью ключевого слова ATTACH и настройки SQL безопасности не были заданы, то по умолчанию будет использоваться SQL SECURITY NONE для материализованных представлений и SQL SECURITY INVOKER для обычных.

Изменить параметры SQL безопасности возможно с помощью следующего запроса:

ALTER TABLE MODIFY SQL SECURITY { DEFINER | INVOKER | NONE } [DEFINER = { user | CURRENT_USER }]

Примеры представлений с SQL безопасностью

CREATE test_view
DEFINER = alice SQL SECURITY DEFINER
AS SELECT ...

CREATE test_view
SQL SECURITY INVOKER
AS SELECT ...

LIVE-представления [экспериментальный функционал]

Важно

Представления LIVE VIEW являются экспериментальной возможностью. Их использование может повлечь потерю совместимости в будущих версиях. Чтобы использовать LIVE VIEW и запросы WATCH, включите настройку allow_experimental_live_view.

CREATE LIVE VIEW [IF NOT EXISTS] [db.]table_name [WITH REFRESH [value_in_sec]] AS SELECT ...

LIVE VIEW хранит результат запроса SELECT, указанного при создании, и обновляется сразу же при изменении этого результата. Конечный результат запроса и промежуточные данные, из которых формируется результат, хранятся в оперативной памяти, и это обеспечивает высокую скорость обработки для повторяющихся запросов. LIVE-представления могут отправлять push-уведомления при изменении результата исходного запроса SELECT. Для этого используйте запрос WATCH.

Изменение LIVE VIEW запускается при вставке данных в таблицу, указанную в исходном запросе SELECT.

LIVE-представления работают по тому же принципу, что и распределенные таблицы. Но вместо объединения отдельных частей данных с разных серверов, LIVE-представления объединяют уже имеющийся результат с новыми данными. Если в исходном запросе LIVE-представления есть вложенный подзапрос, его результаты не кешируются, в кеше хранится только результат основного запроса.

Ограничения

• Табличные функции в основном запросе не поддерживаются.
• Таблицы, не поддерживающие изменение с помощью запроса INSERT, такие как словари и системные таблицы, а также нормальные представления или материализованные представления, не запускают обновление LIVE-представления.
• В LIVE-представлениях могут использоваться только такие запросы, которые объединяют результаты по старым и новым данным. LIVE-представления не работают с запросами, требующими полного пересчета данных или агрегирования с сохранением состояния.
• LIVE VIEW не работает для реплицируемых и распределенных таблиц, добавление данных в которые происходит на разных узлах.
• LIVE VIEW не обновляется, если в исходном запросе используются несколько таблиц.

В случаях, когда LIVE VIEW не обновляется автоматически, чтобы обновлять его принудительно с заданной периодичностью, используйте WITH REFRESH.

Отслеживание изменений LIVE-представлений

Для отслеживания изменений LIVE-представления используйте запрос WATCH.

Пример:

CREATE TABLE mt (x Int8) Engine = MergeTree ORDER BY x;
CREATE LIVE VIEW lv AS SELECT sum(x) FROM mt;

Отслеживаем изменения LIVE-представления при вставке данных в исходную таблицу.

WATCH lv;

┌─sum(x)─┬─_version─┐
│      1 │        1 │
└────────┴──────────┘
┌─sum(x)─┬─_version─┐
│      3 │        2 │
└────────┴──────────┘
┌─sum(x)─┬─_version─┐
│      6 │        3 │
└────────┴──────────┘

INSERT INTO mt VALUES (1);
INSERT INTO mt VALUES (2);
INSERT INTO mt VALUES (3);

Для получения списка изменений используйте ключевое слово EVENTS.

WATCH lv EVENTS;

┌─version─┐
│       1 │
└─────────┘
┌─version─┐
│       2 │
└─────────┘
┌─version─┐
│       3 │
└─────────┘
...

Для работы с LIVE-представлениями, как и с любыми другими, можно использовать запросы SELECT. Если результат запроса кеширован, он будет возвращен немедленно, без обращения к исходным таблицам представления.

SELECT * FROM [db.]live_view WHERE ...

Принудительное обновление LIVE-представлений

Чтобы принудительно обновить LIVE-представление, используйте запрос ALTER LIVE VIEW [db.]table_name REFRESH.

Секция WITH REFRESH

LIVE-представление, созданное с параметром WITH REFRESH, будет автоматически обновляться через указанные промежутки времени, начиная с момента последнего обновления.

CREATE LIVE VIEW [db.]table_name WITH REFRESH [value_in_sec] AS SELECT ...

Если значение временного промежутка не задано, используется значение periodic_live_view_refresh.

Пример:

CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
WATCH lv;

┌───────────────now()─┬─_version─┐
│ 2021-02-21 08:47:05 │        1 │
└─────────────────────┴──────────┘
┌───────────────now()─┬─_version─┐
│ 2021-02-21 08:47:10 │        2 │
└─────────────────────┴──────────┘
┌───────────────now()─┬─_version─┐
│ 2021-02-21 08:47:15 │        3 │
└─────────────────────┴──────────┘

WATCH lv;

Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.lv doesn't exist..

Использование LIVE-представлений

Наиболее частые случаи использования LIVE-представлений:

• Получение push-уведомлений об изменениях данных без дополнительных периодических запросов.
• Кеширование результатов часто используемых запросов для получения их без задержки.
• Отслеживание изменений таблицы для запуска других запросов SELECT.
• Отслеживание показателей из системных таблиц с помощью периодических обновлений.