CREATE VIEW
Создаёт новое представление. Представления могут быть normal, materialized, refreshable materialized и window (обновляемое материализованное представление и window-представление являются экспериментальными функциями).
Обычное представление
Синтаксис:
Обычные представления не хранят никаких данных. Они просто выполняют чтение из другой таблицы при каждом доступе. Другими словами, обычное представление — это не что иное, как сохранённый запрос. При чтении из представления этот сохранённый запрос используется как подзапрос в разделе FROM.
Например, предположим, что вы создали представление:
и написали запрос:
Этот запрос полностью эквивалентен использованию подзапроса:
Параметризированное представление
Параметризированные представления аналогичны обычным представлениям, но могут быть созданы с параметрами, которые не решаются сразу. Эти представления можно использовать с табличными функциями, которые указывают имя представления как имя функции, а значения параметров как его аргументы.
Выше создаётся представление для таблицы, которое может использоваться как табличная функция с подстановкой параметров, как показано ниже.
Материализованное представление
Вот пошаговое руководство по использованию материализованных представлений.
Материализованные представления хранят данные, преобразованные соответствующим SELECT запросом.
При создании материализованного представления без TO [db].[table], необходимо указать ENGINE – движок таблицы для хранения данных.
Если создаётся материализованное представление с TO [db].[table], вы не можете использовать POPULATE.
Материализованное представление выполняется следующим образом: при вставке данных в таблицу, указанную в SELECT, часть вставленных данных преобразуется этим запросом SELECT, а результат вставляется в представление.
Материализованные представления в ClickHouse используют имена столбцов вместо порядка столбцов во время вставки в целевую таблицу. Если некоторые имена столбцов отсутствуют в результате запроса SELECT, ClickHouse использует значение по умолчанию, даже если столбец не является Nullable. Безопасной практикой было бы добавлять псевдонимы для каждого столбца при использовании материализованных представлений.
Материализованные представления в ClickHouse реализованы больше как триггеры вставки. Если в запросе представления есть агрегация, она применяется только к партии только что вставленных данных. Любые изменения существующих данных исходной таблицы (например, обновление, удаление, удаление раздела и т. д.) не изменяют материализованное представление.
Материализованные представления в ClickHouse не имеют детерминированного поведения в случае ошибок. Это означает, что блоки, которые уже были записаны, будут сохранены в целевой таблице, но все блоки после ошибки не будут.
По умолчанию, если вставка в одно из представлений не удалась, то запрос INSERT также завершится с ошибкой, и некоторые блоки могут не быть записаны в целевую таблицу. Это можно изменить, используя настройку materialized_views_ignore_errors (её следует установить для запроса INSERT), если вы установите materialized_views_ignore_errors=true, тогда любые ошибки при вставке в представления будут игнорироваться, и все блоки будут записаны в целевую таблицу.
Также обратите внимание, что materialized_views_ignore_errors по умолчанию установлено в true для таблиц system.*_log.
Если вы укажете POPULATE, существующие данные таблицы будут вставлены в представление при его создании, как будто выполняется CREATE TABLE ... AS SELECT .... В противном случае в запросе содержатся только данные, вставленные в таблицу после создания представления. Мы не рекомендуем использовать POPULATE, так как данные, вставленные в таблицу во время создания представления, не будут в него вставлены.
Учитывая, что POPULATE работает как CREATE TABLE ... AS SELECT ..., у него есть ограничения:
- Он не поддерживается с реплицируемыми базами данных
- Он не поддерживается в ClickHouse cloud
Вместо этого можно использовать отдельный INSERT ... SELECT.
Запрос SELECT может содержать DISTINCT, GROUP BY, ORDER BY, LIMIT. Обратите внимание, что соответствующие преобразования выполняются независимо в каждом блоке вставленных данных. Например, если указан GROUP BY, данные агрегируются во время вставки, но только в пределах одного пакета вставленных данных. Данные не будут дополнительно агрегироваться. Исключением является использование ENGINE, который независимо выполняет агрегацию данных, как SummingMergeTree.
Выполнение запросов ALTER на материализованных представлениях имеет ограничения, например, нельзя обновить запрос SELECT, что может быть неудобно. Если материализованное представление использует конструкцию TO [db.]name, вы можете DETACH представление, выполнить ALTER для целевой таблицы, а затем ATTACH ранее отключенное (DETACH) представление.
Обратите внимание, что на материализованное представление влияет настройка optimize_on_insert. Данные объединяются перед вставкой в представление.
Представления выглядят так же, как обычные таблицы. Например, они перечислены в результате запроса SHOW TABLES.
Чтобы удалить представление, используйте DROP VIEW. Хотя DROP TABLE работает и для VIEW.
SQL безопасность
DEFINER и SQL SECURITY позволяют указать, какого пользователя ClickHouse использовать при выполнении базового запроса представления.
SQL SECURITY имеет три допустимых значения: DEFINER, INVOKER или NONE. Вы можете указать любого существующего пользователя или CURRENT_USER в предложении DEFINER.
Следующая таблица объяснит, какие права требуются для какого пользователя, чтобы выбрать из представления.
Обратите внимание, что независимо от опции SQL безопасности, в любом случае по-прежнему требуется иметь GRANT SELECT ON <view>, чтобы прочитать из него.
| Опция безопасности SQL | Представление | Материализованное представление |
|---|---|---|
DEFINER alice | alice должна иметь право SELECT для исходной таблицы представления. | alice должна иметь право SELECT для исходной таблицы представления и право INSERT для целевой таблицы представления. |
INVOKER | Пользователь должен иметь право SELECT для исходной таблицы представления. | Нельзя указать SQL SECURITY INVOKER для материализованных представлений. |
NONE | - | - |
SQL SECURITY NONE — это устаревшая опция. Любой пользователь с правами на создание представлений с SQL SECURITY NONE сможет выполнить любой произвольный запрос.
Таким образом, необходимо иметь GRANT ALLOW SQL SECURITY NONE TO <user>, чтобы создать представление с этой опцией.
Если DEFINER/SQL SECURITY не указаны, используются значения по умолчанию:
SQL SECURITY:INVOKERдля обычных представлений иDEFINERдля материализованных представлений (настраивается в настройках)DEFINER:CURRENT_USER(настраивается в настройках)
Если представление подключено без указания DEFINER/SQL SECURITY, по умолчанию используется SQL SECURITY NONE для материализованного представления и SQL SECURITY INVOKER для обычного представления.
Чтобы изменить SQL безопасность для существующего представления, используйте
Примеры
Live-представление
Эта функция устарела и будет удалена в будущем.
Для вашего удобства старую документацию можно найти здесь
Обновляемое материализованное представление
где interval — это последовательность простых интервалов:
Периодически выполняет соответствующий запрос и сохраняет его результат в таблице.
- Если запрос содержит
APPEND, каждая обновление вставляет строки в таблицу без удаления существующих строк. Вставка не является атомарной, как и обычная INSERT SELECT. - В противном случае каждое обновление атомарно заменяет предыдущие содержимое таблицы.
Отличия от обычных необновляемых материализованных представлений:
- Нет триггера вставки. То есть, когда новые данные вставляются в таблицу, указанную в SELECT, они не автоматически помещаются в обновляемое материализованное представление. Периодическая обновление выполняет весь запрос.
- Нет ограничений на запрос
SELECT. Табличные функции (например,url()), представления, UNION, JOIN все разрешены.
Настройки в части REFRESH ... SETTINGS запроса являются настройками обновления (например, refresh_retries), отличными от обычных настроек (например, max_threads). Обычные настройки можно указать, используя SETTINGS в конце запроса.
График обновления
Примеры графика обновления:
RANDOMIZE FOR случайным образом корректирует время каждого обновления, например:
В каждый момент времени может выполняться не более одного обновления для данного представления. Например, если представление с REFRESH EVERY 1 MINUTE обновляется за 2 минуты, оно будет обновляться каждые 2 минуты. Если оно затем станет быстрее и начнёт обновляться за 10 секунд, оно вернётся к обновлению каждую минуту. (В частности, оно не будет обновляться каждые 10 секунд, чтобы наверстать пропущенные обновления — такой очереди нет.)
Кроме того, обновление запускается сразу после создания материализованного представления, если в запросе создания не указано EMPTY. Если EMPTY указано, первое обновление происходит по расписанию.
В реплицируемой БД
Если обновляемое материализованное представление находится в реплицируемой базе данных, реплики координируются друг с другом, так что только одна реплика выполняет обновление в каждый запланированный момент времени. Требуется использование движка таблицы ReplicatedMergeTree, чтобы все реплики видели данные, создаваемые обновлением.
В режиме APPEND, координацию можно отключить, используя SETTINGS all_replicas = 1. Это заставляет реплики выполнять обновления независимо друг от друга. В этом случае ReplicatedMergeTree не требуется.
В не-APPEND режиме поддерживается только координированное обновление. Для некординированного обновления используйте атомарные базы данных и запрос CREATE ... ON CLUSTER, чтобы создать обновляемые материализованные представления на всех репликах.
Координация осуществляется через Keeper. Путь znode определяется настройкой сервера default_replica_path.
Зависимости
DEPENDS ON синхронизирует обновления разных таблиц. Например, предположим, что есть цепочка из двух обновляемых материализованных представлений:
Без DEPENDS ON оба представления начнут обновляться в полночь, и destination обычно будет видеть вчерашние данные в source. Если мы добавим зависимость:
то обновление destination начнётся только после завершения обновления source за этот день, так что destination будет основан на свежих данных.
Альтернативно, тот же результат можно достичь с:
где 1 HOUR может быть любой продолжительностью, меньшей, чем период обновления source. Зависимая таблица не будет обновляться чаще, чем любая из её зависимостей. Это допустимый способ настроить цепочку обновляемых представлений без указания фактического периода обновления более одного раза.
Ещё несколько примеров:
REFRESH EVERY 1 DAY OFFSET 10 MINUTE(destination) зависит отREFRESH EVERY 1 DAY(source)
Если обновлениеsourceзанимает более 10 минут,destinationбудет ждать его.REFRESH EVERY 1 DAY OFFSET 1 HOURзависит отREFRESH EVERY 1 DAY OFFSET 23 HOUR
Аналогично вышеприведенному, даже если соответствующие обновления происходят в разные календарные дни. Обновлениеdestinationна день X+1 будет ждать обновленияsourceна день X (если оно занимает более 2 часов).REFRESH EVERY 2 HOURзависит отREFRESH EVERY 1 HOUR
2-часовое обновление происходит после каждого другого часового обновления, например, после обновления в полночь, затем после обновления в 2 часа ночи и т. д.REFRESH EVERY 1 MINUTEзависит отREFRESH EVERY 2 HOUR
REFRESH AFTER 1 MINUTEзависит отREFRESH EVERY 2 HOUR
REFRESH AFTER 1 MINUTEзависит отREFRESH AFTER 2 HOUR
destinationобновляется один раз после каждого обновленияsource, то есть каждые 2 часа.1 MINUTEфактически игнорируется.REFRESH AFTER 1 HOURзависит отREFRESH AFTER 1 HOUR
В настоящее время это не рекомендуется.
DEPENDS ON работает только между обновляемыми материализованными представлениями. Указание обычной таблицы в списке DEPENDS ON предотвратит обновление представления (зависимости могут быть удалены с помощью ALTER, см. ниже).
Настройки
Доступные параметры обновления:
refresh_retries— сколько раз повторить попытку, если обновление завершилось исключением. Если все повторы не удались, перейдите к следующему запланированному времени обновления. 0 означает отсутствие повторов, -1 означает бесконечные повторы. По умолчанию: 0.refresh_retry_initial_backoff_ms— Задержка перед первой повторной попыткой, еслиrefresh_retriesне является равным нулю. Каждая последующая попытка удваивает задержку, вплоть доrefresh_retry_max_backoff_ms. По умолчанию: 100 мс.refresh_retry_max_backoff_ms— Предел на экспоненциальный рост задержки между попытками обновления. По умолчанию: 60000 мс (1 минута).
Изменение параметров обновления
Чтобы изменить параметры обновления:
Это заменяет все параметры обновления разом: расписание, зависимости, настройки и APPEND-ность. Например, если у таблицы была DEPENDS ON, выполнение MODIFY REFRESH без DEPENDS ON удалит зависимости.
Прочие операции
Статус всех обновляемых материализованных представлений доступен в таблице system.view_refreshes. В частности, она содержит прогресс обновления (если выполняется), время последнего и следующего обновления, сообщение об исключении, если обновление завершилось неудачно.
Чтобы вручную остановить, запустить, инициировать или отменить обновления, используйте SYSTEM STOP|START|REFRESH|WAIT|CANCEL VIEW.
Чтобы дождаться завершения обновления, используйте SYSTEM WAIT VIEW. Особенно полезно, чтобы дождаться первоначального обновления после создания представления.
Интересный факт: запрос на обновление может читать из представления, которое обновляется, видя предыдущее состояние данных. Это означает, что вы можете реализовать игру "Жизнь": https://pastila.nl/?00021a4b/d6156ff819c83d490ad2dcec05676865#O0LGWTO7maUQIA4AcGUtlA==
Window-представление
Это экспериментальная функция, которая в будущих версиях может измениться с нарушением обратной совместимости. Включите использование window-представлений и WATCH запроса, используя настройку allow_experimental_window_view. Введите команду set allow_experimental_window_view = 1.
Window-представление может агрегировать данные по временным окнам и выводить результаты, когда окно готово к срабатыванию. Оно хранит результаты частичной агрегации во внутренней (или указанной) таблице для уменьшения задержки и может отправлять результаты обработки в указанную таблицу или отправлять уведомления с помощью запроса WATCH.
Создание window-представления похоже на создание MATERIALIZED VIEW. Window-представление нуждается во внутреннем механизме хранения для хранения промежуточных данных. Внутреннее хранилище можно указать, используя предложение INNER ENGINE, в качестве внутреннего движка window-представление будет использовать AggregatingMergeTree по умолчанию.
При создании window-представления без TO [db].[table], вы должны указать ENGINE – движок таблицы для хранения данных.
Функции временных окон
Функции временных окон используются для получения нижней и верхней границы окон для записей. Window-представление должно использоваться с функцией временного окна.
Временные атрибуты
Window-представление поддерживает обработку времени обработки и времени события.
Время обработки позволяет window-представлению выводить результаты, основываясь на времени на локальной машине и используется по умолчанию. Это самое простое представление времени, но оно не обеспечивает детерминизма. Атрибут времени обработки можно определить, установив параметр time_attr функции временного окна на столбец таблицы или используя функцию now(). Следующий запрос создаёт window-представление с временем обработки.
Время события — это время, когда каждое отдельное событие произошло на устройстве, его создающем. Это время обычно встраивается в записи при их создании. Обработка времени события позволяет получить согласованные результаты даже в случае событий, пришедших не в том порядке или с опозданием. Window-представление поддерживает обработку времени события с помощью синтаксиса WATERMARK.
Window-представление предоставляет три стратегии для меток времени:
STRICTLY_ASCENDING: Выпускает метку времени, равную максимальному наблюдаемому временному штампу на данный момент. Строки, у которых временной штамп меньше максимального, не опаздывают.ASCENDING: Выпускает метку времени, равную максимальному наблюдаемому временному штампу на данный момент минус 1. Строки, у которых временной штамп отличается не больше чем на 1, не опаздывают.BOUNDED: WATERMARK=INTERVAL. Выпускает метки времени, которые равны максимальному наблюдаемому временному штампу минус указанная задержка.
Следующие запросы являются примерами создания window-представления с WATERMARK:
По умолчанию окно срабатывает, когда приходит метка времени, и элементы, пришедшие после метки времени, будут отброшены. Window-представление поддерживает обработку событий с задержкой, установив ALLOWED_LATENESS=INTERVAL. Пример обработки задержек:
Учтите, что элементы, выпускаемые при позднем срабатывании, должны рассматриваться как обновлённые результаты предыдущего вычисления. Вместо срабатывания в конце окон, window-представление сработает немедленно при поступлении события с задержкой. Таким образом, это приведёт к нескольким выводам для одного и того же окна. Пользователям необходимо учитывать эти дублированные результаты или выполнять их дедупликацию.
Вы можете изменить запрос SELECT, указанный в window-представлении, с помощью оператора ALTER TABLE ... MODIFY QUERY. Структура данных, полученная из нового запроса SELECT, должна совпадать с оригинальным запросом SELECT как с предложением TO [db.]name, так и без него. Обратите внимание, что данные в текущем окне будут потеряны, так как промежуточное состояние не может быть повторно использовано.
Мониторинг новых окон
Window-представление поддерживает запрос WATCH для мониторинга изменений или использование синтаксиса TO для вывода результатов в таблицу.
Запрос WATCH действует так же, как и в LIVE VIEW. Можно указать LIMIT, чтобы установить количество обновлений, которые необходимо получить, прежде чем завершить запрос. Оператор EVENTS может быть использован для получения короткой формы запроса WATCH, в котором вы получите только последнюю метку времени запроса вместо его результата.
Настройки
window_view_clean_interval: Интервал очистки window-представления в секундах для освобождения устаревших данных. Система сохранит окна, которые не были полностью обработаны в соответствии с системным временем или конфигурациейWATERMARK, и другие данные будут удалены.window_view_heartbeat_interval: Интервал сердцебиения в секундах, чтобы показать, что запрос на просмотр измененийWATCHактивен.wait_for_window_view_fire_signal_timeout: Тайм-аут ожидания сигнала срабатывания окна в процессе обработки времени события.
Пример
Предположим, что нам нужно подсчитать количество журналов кликов каждые 10 секунд в таблице журналов, называемой data, а ее структура таблицы:
Сначала мы создаем window-представление с работающим окном на интервале 10 секунд:
Затем мы используем запрос WATCH для получения результатов.
Когда журналы вставляются в таблицу data,
Запрос WATCH должен вывести результаты, как показано ниже:
Альтернативно, мы можем прикрепить вывод к другой таблице, используя синтаксис TO.
Дополнительные примеры можно найти среди стоимостных тестов ClickHouse (их названия содержат *window_view*).
Использование window-представления
Window-представление полезно в следующих сценариях:
- Мониторинг: Агрегировать и вычислять метрики журналов по времени и выводить результаты в целевую таблицу. Панель мониторинга может использовать целевую таблицу как исходную таблицу.
- Анализ: Автоматически агрегировать и предварительно обрабатывать данные во временном окне. Это может быть полезно при анализе большого количества журналов. Предварительная обработка устраняет повторные вычисления в нескольких запросах и уменьшает задержку запросов.