CREATE VIEW
Создает новое представление. Представления могут быть обычными, материализованными, обновляемыми материализованными и оконными (обновляемые материализованные представления и оконные представления являются экспериментальными функциями).
Обычное представление
Синтаксис:
Обычные представления не хранят никаких данных. Они просто выполняют чтение из другой таблицы при каждом доступе. Другими словами, обычное представление не что иное, как сохраненный запрос. При чтении из представления используется этот сохраненный запрос в качестве подзапроса в 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 безопасность для существующего представления, используйте
Примеры
Обновляемое материализованное представление
где interval - последовательность простых интервалов:
Периодически выполняет соответствующий запрос и хранит его результат в таблице.
- Если в запросе указано
APPEND, каждое обновление вставляет строки в таблицу без удаления существующих строк. Вставка не атомарна, как и обычный INSERT SELECT. - В противном случае каждое обновление Atomically заменяет предыдущий содержимое таблицы.
Отличия от обычных не обновляемых материализованных представлений:
- Нет триггера вставки. Т.е. когда новые данные вставляются в таблицу, указанную в SELECT, они не автоматически отправляются в обновляемое материализованное представление. Периодическое обновление выполняет весь запрос.
- Нет ограничений для запроса SELECT. Табличные функции (например,
url()), представления, UNION, JOIN – все допустимо.
Настройки в части запроса REFRESH ... SETTINGS являются настройками обновления (например, refresh_retries), отличными от обычных настроек (например, max_threads). Обычные настройки могут быть указаны с помощью SETTINGS в конце запроса.
График обновлений
Примеры графиков обновлений:
RANDOMIZE FOR случайным образом регулирует время каждого обновления, например:
Не более одного обновления может выполняться одновременно для данного представления. Например, если представление с REFRESH EVERY 1 MINUTE занимает 2 минуты для обновления, оно будет обновляться каждые 2 минуты. Если затем оно становится быстрее и начинает обновляться за 10 секунд, оно вернется к обновлению каждую минуту. (В частности, оно не будет обновляться каждые 10 секунд, чтобы догнать накопившиеся обновления — такого накопления нет.)
Кроме того, обновление начинается сразу после создания материализованного представления, если в запросе CREATE не указано 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 HOUR происходит после обновления 1 HOUR каждый второй час, например, после обновления полуночи, а затем после обновления в 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==
Оконное представление
Это экспериментальная функция, которая может измениться в несовместимые с предыдущими способами в будущих выпусках. Включите использование оконных представлений и запроса WATCH, используя настройку allow_experimental_window_view. Введите команду set allow_experimental_window_view = 1.
Оконное представление может агрегировать данные по временным окнам и выводить результаты, когда окно готово к запуску. Оно хранит частичные результаты агрегации во внутренней (или указанной) таблице, чтобы уменьшить задержку, и может отправлять результат обработки в указанную таблицу или отправлять уведомления с помощью запроса WATCH.
Создание оконного представления похоже на создание MATERIALIZED VIEW. Оконное представление требует внутреннего движка хранения для хранения промежуточных данных. Внутреннее хранилище можно указать с помощью клаузе INNER ENGINE, по умолчанию оконное представление будет использовать AggregatingMergeTree как внутренний движок.
При создании оконного представления без TO [db].[table] вы должны указать ENGINE – движок таблицы для хранения данных.
Функции временного окна
Функции временного окна используются для получения нижней и верхней границы окна записей. Оконное представление должно использоваться с функцией временного окна.
ВРЕМЕННЫЕ АТРИБУТЫ
Оконное представление поддерживает время обработки и время событий.
Время обработки позволяет оконному представлению производить результаты на основе времени локальной машины и используется по умолчанию. Это наиболее простое поняние времени, но оно не обеспечивает детерминированности. Атрибут времени обработки может быть определен путем установки time_attr функции временного окна на столбец таблицы или с помощью функции now(). Следующий запрос создает оконное представление с временем обработки.
Время событий - это время, когда каждое индивидуальное событие произошло на устройстве-источнике. Это время обычно встроено в записи, когда оно создается. Обработка времени событий позволяет получать последовательные результаты, даже в случае событий вне порядка или поздних событий. Оконное представление поддерживает обработку времени событий, используя синтаксис WATERMARK.
Оконное представление предоставляет три стратегии водяного знака:
STRICTLY_ASCENDING: Выдает водяной знак максимальной наблюдаемой временной метки на данный момент. Строки, имеющие временную метку меньше максимальной временной метки, не являются поздними.ASCENDING: Выдает водяной знак максимальной наблюдаемой временной метки на данный момент минус 1. Строки, имеющие временную метку, равную и меньшую максимальной временной метке, не являются поздними.BOUNDED: WATERMARK=INTERVAL. Выдает водяные знаки, которые представляют собой максимальную наблюдаемую временную метку минус указанную задержку.
Следующие запросы являются примерами создания оконного представления с WATERMARK:
По умолчанию окно будет срабатывать, когда поступает водяной знак, и элементы, которые пришли за водяным знаком, будут отброшены. Оконное представление поддерживает обработку поздних событий, устанавливая ALLOWED_LATENESS=INTERVAL. Пример обработки задержки:
Обратите внимание, что элементы, выданные поздним срабатыванием, должны рассматриваться как обновленные результаты предыдущего вычисления. Вместо срабатывания в конце окон, оконное представление будет срабатывать немедленно, когда поступает позднее событие. Таким образом, это приведет к нескольким выходным данным для одного и того же окна. Пользователи должны учитывать эти дубликаты результатов или выполнять дедупликацию.
Вы можете изменить запрос SELECT, который был указан в оконном представлении, с помощью оператора ALTER TABLE ... MODIFY QUERY. Структура данных, результирующая из нового запроса SELECT, должна быть такой же, как у первоначального запроса SELECT, с или без клаузе TO [db.]name. Обратите внимание, что данные в текущем окне будут потеряны, поскольку промежуточное состояние не может быть повторно использовано.
Мониторинг новых окон
Оконное представление поддерживает запрос WATCH для мониторинга изменений, или используйте синтаксис TO, чтобы выводить результаты в таблицу.
Запрос WATCH действует аналогично запросу LIVE VIEW. Можно указать LIMIT, чтобы задать количество обновлений, которые следует получить перед завершением запроса. Клаузу EVENTS можно использовать, чтобы получить короткую форму запроса WATCH, где вместо результата запроса вы получите только наиболее свежий водяной знак запроса.
Настройки
window_view_clean_interval: Интервал очистки оконного представления в секундах для освобождения устаревших данных. Система сохранит окна, которые не были полностью активированы согласно системному времени или настройкеWATERMARK, а другие данные будут удалены.window_view_heartbeat_interval: Интервал опроса в секундах, указывающий, что запрос на просмотр активен.wait_for_window_view_fire_signal_timeout: Тайм-аут ожидания сигнала активации оконного представления в обработке времени событий.
Пример
Предположим, нам нужно подсчитать количество логов кликов за 10 секунд в таблице логов под названием data, и структура таблицы:
Сначала мы создаем оконное представление с временным окном в 10 секунд:
Затем мы используем запрос WATCH, чтобы получить результаты.
Когда логи вставляются в таблицу data,
Запрос WATCH должен вывести результаты следующим образом:
В качестве альтернативы, мы можем прикрепить вывод к другой таблице с использованием синтаксиса TO.
Дополнительные примеры можно найти среди состояний тестов ClickHouse (они называются *window_view* там).
Использование оконного представления
Оконное представление полезно в следующих сценариях:
- Мониторинг: Аггрегирование и подсчет метрик логов по времени, и вывод результатов в целевую таблицу. Дашборд может использовать целевую таблицу в качестве исходной таблицы.
- Анализ: Автоматическое агрегирование и предварительная обработка данных в временном окне. Это может быть полезно при анализе большого количества логов. Предварительная обработка устраняет повторные вычисления в нескольких запросах и снижает задержку запросов.