Лучшие практики использования асинхронных материализованных представлений

Асинхронные материализованные представления ускоряют запросы за счет предварительного вычисления и хранения их результатов, однако каждое обновление требует вычислительных ресурсов и операций ввода-вывода. В этой статье последовательно рассматриваются оценка сценариев, принципы использования, выбор стратегии обновления, практические примеры реализации и рекомендации по эксплуатации, которые помогут администраторам баз данных и разработчикам создавать эффективные асинхронные материализованные представления.

Принципы обновления материализованных представлений описаны в разделе Принципы обновления.

Краткий список вопросов для принятия решения

Перед созданием асинхронного материализованного представления ответьте на следующие вопросы:

• Содержит ли запрос JOIN нескольких таблиц, сложные агрегации или оконные функции?
• Обновляются ли данные базовой таблицы относительно редко (желательно избегать нескольких обновлений в минуту)?
• Допустима ли для бизнеса задержка данных в несколько минут или дольше (данные в реальном времени с задержкой от 1 до 5 минут не требуются)?
• Достаточно ли велик объем данных базовой таблицы (значительно больше нескольких сотен строк)?
• Можно ли разделить типовые SQL-запросы на группы без пересечений между ними?
• Является ли базовая таблица партиционированной и можно ли создать партиционированное материализованное представление?
• Достаточно ли ресурсов для периодического обновления?
• Можете ли вы периодически проверять использование материализованных представлений и своевременно удалять неиспользуемые?

Если на большинство вопросов вы ответили да, асинхронные материализованные представления подходят для вашей задачи.

---

1. Оценка сценариев

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

Краткий обзор сценариев

Категория	Сценарий	Основные характеристики	Рекомендуется
Сложность запросов	Сложные запросы с агрегацией	JOIN нескольких таблиц, SUM/AVG/COUNT, оконные функции	Да
Отчеты	Отчеты на основе согласованного снимка данных	Формируются в фиксированный момент времени (например, ежедневно в полночь)	Да
Вычислительная нагрузка	Ресурсоемкий анализ	Сложные математические вычисления, преобразование данных, прогнозные модели	Да
Моделирование хранилища данных	Схема «звезда» / «снежинка»	JOIN таблицы фактов с несколькими таблицами измерений	Да
Lakehouse	Ускорение lakehouse	Запросы к озеру данных ограничены пропускной способностью сети и объектного хранилища	Да
Уровни хранилища данных	Многоуровневая обработка ETL	Базовая таблица содержит исходные данные, требующие обработки на нескольких уровнях	Да
Обновления данных	Часто обновляемая базовая таблица	Несколько обновлений в минуту	Нет
Сложность запросов	Простые запросы	Сканирование одной таблицы или простая фильтрация	Нет
Актуальность	Данные, близкие к реальному времени (в пределах от 1 до 5 минут)	Бизнесу всегда нужны самые свежие данные	Нет
Объем данных	Очень маленькая исходная таблица	Всего несколько сотен строк	Нет

Рекомендуемые сценарии

Сложные запросы с агрегацией

• Описание: Запросы с соединениями нескольких таблиц, сложными агрегатными функциями (например, SUM, AVG, COUNT) или оконными функциями.
• Преимущество: Позволяют избежать повторного выполнения сложных вычислений при каждом запуске запроса.

Отчеты

• Описание: Отчеты, которым нужен согласованный снимок данных, сформированный в фиксированный момент времени (например, ежедневно в полночь).
• Преимущество: Все пользователи видят данные на один и тот же момент времени.

Ресурсоемкий анализ

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

Схема «звезда» / «снежинка» в хранилищах данных

• Описание: Сценарии, в которых таблица фактов соединяется с несколькими таблицами измерений, например таблица фактов продаж с измерениями товаров, времени и регионов.
• Преимущество: Результаты соединений материализуются заранее, ускоряя аналитические запросы.

Ускорение lakehouse

• Описание: Запросы к озеру данных могут выполняться медленно из-за сетевых задержек и ограничений пропускной способности объектного хранилища.
• Преимущество: Локальное хранение данных в Doris ускоряет аналитику по данным озера.

Уровни хранилища данных

• Описание: Базовая таблица содержит большой объем исходных данных, а запросам требуются сложные операции ETL.
• Преимущество: На основе этих данных можно построить несколько уровней асинхронных материализованных представлений и таким образом реализовать многоуровневое хранилище данных.

Нерекомендуемые сценарии

Часто обновляемые базовые таблицы

• Описание: Данные исходной таблицы изменяются очень часто (например, несколько раз в минуту).
• Проблема: Полное обновление или обновление партиций может оказаться слишком затратным. Если при каждом изменении затрагивается лишь небольшая доля строк таблицы или партиции, рассмотрите Incremental View Maintenance (IVM). IVM также выполняет обновления асинхронно и не обеспечивает согласованность в реальном времени.

Простые запросы

• Описание: Запросы, которые выполняют только сканирование одной таблицы или простую фильтрацию.
• Проблема: Выгода от асинхронного материализованного представления не компенсирует затраты на его обновление.

Сценарии, требующие данных в реальном времени (в пределах от 1 до 5 минут)

• Описание: Бизнесу всегда нужна самая свежая версия данных.
• Проблема: Данные асинхронных материализованных представлений обновляются с задержкой.

Очень маленькие исходные таблицы

• Описание: Базовая таблица содержит мало записей (например, несколько сотен строк).
• Проблема: Выигрыш от оптимизации с помощью асинхронного материализованного представления незначителен.

---

2. Принципы использования

2.1 Когда использовать асинхронные материализованные представления

Аспект	Описание
Актуальность	Подходят для сценариев с невысокими требованиями к актуальности данных (например, данные T+1). При высоких требованиях к актуальности используйте синхронные материализованные представления.
Ускорение и согласованность	Разделяйте типовые SQL-запросы на группы с минимальными пересечениями. Чем четче разделение, тем качественнее построенные представления.
Повторное использование	Один запрос может использовать несколько материализованных представлений, а одно материализованное представление может использоваться несколькими запросами.
Компромиссы	Совместно учитывайте время отклика при использовании материализованного представления (ускорение), затраты на его построение и требования к согласованности данных.

2.2 Компромисс между определением материализованного представления и затратами на построение

• Определение близко к исходному запросу: Значительное ускорение, но низкая универсальность и ограниченные возможности повторного использования при высоких затратах на построение.
• Более универсальное определение (например, без условий WHERE или с большим числом измерений агрегации): Меньшее ускорение, но более высокая универсальность и больше возможностей повторного использования при меньших затратах на построение.

Примечание

• Контролируйте количество материализованных представлений: Больше материализованных представлений не всегда означает лучше. Их построение и обновление потребляет ресурсы, а CBO также требуется время для выбора оптимального материализованного представления при прозрачном переписывании. Теоретически, чем больше материализованных представлений, тем дольше выполняется прозрачное переписывание.
• Периодически проверяйте использование: Своевременно удаляйте неиспользуемые материализованные представления.
• Частота обновления базовой таблицы: Частые обновления базовой таблицы приводят к частой потере актуальности материализованных представлений, из-за чего их нельзя использовать для прозрачного переписывания (прямые запросы по-прежнему возможны). Чтобы использовать прозрачное переписывание в этом сценарии, необходимо допустить некоторую задержку запрашиваемых данных. Ее можно настроить с помощью grace_period. Подробности см. в описании grace_period.

---

3. Выбор стратегии обновления

Для асинхронных материализованных представлений необходимо отдельно выбрать гранулярность вычислений и способ запуска. Доступны следующие варианты гранулярности вычислений: INCREMENTAL, PARTITIONS, COMPLETE и AUTO; способы запуска включают ручное обновление, обновление по расписанию и обновление по событию.

Гранулярность вычислений	Рекомендуемый сценарий
INCREMENTAL	Количество измененных строк значительно меньше объема всей таблицы или затронутых партиций, а определение соответствует требованиям IVM
PARTITIONS	Изменения сосредоточены в небольшом числе партиций, и затраты на их пересчет приемлемы
COMPLETE	Небольшой объем данных, первоначальное создание базового состояния или его восстановление
AUTO	Вы хотите, чтобы Doris автоматически выбрал доступный способ

IVM доступен начиная с Doris 5.0.0 и в настоящее время является экспериментальной функцией. Рекомендации по выбору, предварительные требования и ограничения приведены в разделе Incremental View Maintenance (IVM).

3.1 Предпочитайте партиционированные материализованные представления

Рекомендуется создавать партиционированное материализованное представление, если выполняются все следующие условия:

1. Базовая таблица материализованного представления содержит большой объем данных и является партиционированной.
2. Непартиционированные таблицы, на которые ссылается материализованное представление, изменяются редко.
3. SQL-определение материализованного представления и поля партиционирования соответствуют требованиям к выводу партиций (то есть требованиям к инкрементальному обновлению партиций). Подробные требования см. в разделе CREATE-ASYNC-MATERIALIZED-VIEW.
4. Материализованное представление содержит не слишком много партиций. Чрезмерное число партиций приводит к слишком долгому построению.

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

Если создать партиционированное материализованное представление невозможно, рассмотрите материализованное представление с полным обновлением.

3.2 Сравнение трех стратегий обновления

Стратегия обновления	Способ запуска	Актуальность данных	Уровень автоматизации	Основной риск
Ручное обновление	Явная команда пользователя или внешний планировщик	Низкая, зависит от расписания	Низкий	Необходимо самостоятельно управлять расписанием
Обновление по расписанию	Через фиксированные интервалы времени (минимум на уровне минут)	Средняя, предсказуемая задержка	Средний	Высокая частота приводит к постоянному потреблению ресурсов
Обновление по событию	Автоматически запускается при изменении данных базовой таблицы	Высокая	Высокий	Возможны лавинообразные запуски обновлений

3.3 Подробное описание стратегий обновления

Ручное обновление

• Принцип работы: Запускается пользователями с помощью явных команд или планировщиком внешней системы.
• Подходящие сценарии:
  • Системы отчетности с невысокими требованиями к работе в реальном времени
  • Анализ исторических данных в хранилищах данных
  • Сценарии, в которых обновление необходимо синхронизировать с определенными бизнес-процессами
  • Масштабные обновления данных, требующие согласованного использования системных ресурсов
• Плюсы: Полный контроль над моментом обновления, возможность избегать часов пиковой нагрузки.
• Минусы: Требуется дополнительное управление запуском обновлений и надежная обработка сбоев, чтобы внешние циклы не запускали обновления непрерывно.

Обновление по расписанию

• Принцип работы:
  • Автоматическое обновление через фиксированные интервалы времени
  • Минимальная единица времени соответствует минутному уровню
  • Можно указать время первого запуска задачи
• Подходящие сценарии:
  • Периодический мониторинг бизнес-показателей
  • Многоуровневые конвейеры обработки данных
  • Системы отчетности с разными уровнями требований к актуальности
  • Исходные данные с регулярными колебаниями
• Плюсы: Обработка данных по расписанию с предсказуемой задержкой данных.
• Минусы: Ограниченная актуальность данных; порядок обновления связанных представлений необходимо согласовывать вручную.
• Ограничения настройки: Не рекомендуется задавать всем материализованным представлениям частое обновление по расписанию в попытке приблизиться к реальному времени, поскольку это приводит к следующим последствиям:
  • Постоянное потребление системных ресурсов
  • Конкуренция задач обновления за ресурсы
  • Частое добавление и удаление партиций / tablets, создающее большую нагрузку на BE

Обновление по событию

• Принцип работы: Автоматически запускает обновление при изменении данных базовой таблицы.
• Подходящие сценарии:
  • Представления верхних уровней в многоуровневой архитектуре материализованных представлений
  • Сценарии с низкой частотой изменений базовой таблицы
• Плюсы: Высокая актуальность данных и высокий уровень автоматизации.
• Минусы: Возможны лавинообразные запуски обновлений, а системную нагрузку трудно предсказать.
• Ограничения настройки: Обновление по событию для материализованных представлений базового уровня не рекомендуется, кроме следующего случая:
  • Вы уверены, что базовая таблица обновляется редко (например, раз в несколько десятков минут)

3.4 Рекомендации по сочетанию стратегий обновления

По уровню хранилища данных

Уровень представления	Рекомендуемая стратегия обновления
Базовый уровень	Обновление по расписанию (например, ежечасно)
Промежуточный уровень	Обновление по расписанию или по событию
Уровень представления данных	Обновление по событию или вручную

По критичности для бизнеса

Категория бизнес-данных	Рекомендуемая стратегия
Критически важные бизнес-данные в реальном времени	Асинхронные материализованные представления не рекомендуются
Обычные аналитические данные	Обновление по расписанию (ежедневно / ежечасно)
Исторические / архивные данные	Ручное обновление

По частоте изменения данных

Частота изменений	Рекомендуемая стратегия
Частые изменения	Обновление по расписанию (с более длинным интервалом) или вручную
Редкие изменения	Обновление по событию или по расписанию с коротким интервалом
Пакетные изменения	Ручное обновление после изменений

3.5 Рекомендации по частоте обновления

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

Фактическая продолжительность обновления	Рекомендуемая частота обновления
Менее 15 секунд	Интервал не менее 5 минут
Менее 10 минут	Интервал не менее 1 часа
Менее 1 часа	Интервал не менее 1 дня

---

4. Практика работы с партиционированными материализованными представлениями

4.1 Сопоставление партиций

Партиции материализованного представления создаются путем сопоставления с партициями базовой таблицы, обычно в отношении 1:1 или 1:n к партициям базовой таблицы. Подробные требования к выводу партиций см. в разделах CREATE-ASYNC-MATERIALIZED-VIEW и Часто задаваемые вопросы об асинхронных материализованных представлениях, Q12.

4.2 Потеря актуальности партиций и поведение при обновлении

Событие	Результат	Действие
Изменение данных партиции базовой таблицы (вставка, удаление и т. д.)	Соответствующая партиция материализованного представления теряет актуальность. Неактуальные партиции нельзя использовать для прозрачного переписывания, но к ним по-прежнему можно обращаться напрямую. При прозрачном переписывании запрос к неактуальной партиции обрабатывается с привлечением базовой таблицы	Используйте SHOW PARTITIONS FROM mv_name для просмотра состояния партиций
Изменение данных используемой непартиционированной таблицы	Все партиции материализованного представления теряют актуальность, и его нельзя использовать для прозрачного переписывания	Выполните REFRESH MATERIALIZED VIEW mv1 AUTO;, чтобы обновить все партиции, в которых изменились данные
В используемые непартиционированные таблицы данные только вставляются и никогда не изменяются	По умолчанию все партиции теряют актуальность	При создании укажите excluded_trigger_tables = 'non_partitioned_table_name1,non_partitioned_table_name2'. При следующем обновлении будут обновлены только неактуальные партиции, соответствующие партиционированной таблице

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

4.3 Прозрачное переписывание на уровне партиций

Прозрачное переписывание для партиционированного материализованного представления выполняется на уровне партиций:

• Даже если некоторые партиции материализованного представления потеряли актуальность, оно по-прежнему может использоваться для прозрачного переписывания.
• Однако если запрос обращается только к одной партиции и она потеряла актуальность, материализованное представление нельзя использовать для прозрачного переписывания этого запроса.

4.4 Полный пример

Цель: Создать материализованное представление с дневными партициями для ускорения запросов с агрегацией по дням.

Шаг 1: Создайте базовую таблицу lineitem с дневными партициями и подготовьте таблицу измерений partsupp.

CREATE TABLE IF NOT EXISTS lineitem (
    l_orderkey INTEGER NOT NULL, 
    l_partkey INTEGER NOT NULL, 
    l_suppkey INTEGER NOT NULL, 
    l_linenumber INTEGER NOT NULL, 
    l_ordertime DATETIME NOT NULL, 
    l_quantity DECIMALV3(15, 2) NOT NULL, 
    l_extendedprice DECIMALV3(15, 2) NOT NULL, 
    l_discount DECIMALV3(15, 2) NOT NULL, 
    l_tax DECIMALV3(15, 2) NOT NULL, 
    l_returnflag CHAR(1) NOT NULL, 
    l_linestatus CHAR(1) NOT NULL, 
    l_shipdate DATE NOT NULL, 
    l_commitdate DATE NOT NULL, 
    l_receiptdate DATE NOT NULL, 
    l_shipinstruct CHAR(25) NOT NULL, 
    l_shipmode CHAR(10) NOT NULL, 
    l_comment VARCHAR(44) NOT NULL
) DUPLICATE KEY(
    l_orderkey, l_partkey, l_suppkey, 
    l_linenumber
) PARTITION BY RANGE(l_ordertime) (
    FROM 
      ('2024-05-01') TO ('2024-06-30') INTERVAL 1 DAY
)
DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3;

INSERT INTO lineitem VALUES      
(1, 2, 3, 4, '2024-05-01 01:45:05', 5.5, 6.5, 0.1, 8.5, 'o', 'k', '2024-05-01', '2024-05-01', '2024-05-01', 'a', 'b', 'yyyyyyyyy'),    
(1, 2, 3, 4, '2024-05-15 02:35:05', 5.5, 6.5, 0.15, 8.5, 'o', 'k', '2024-05-15', '2024-05-15', '2024-05-15', 'a', 'b', 'yyyyyyyyy'),     
(2, 2, 3, 5, '2024-05-25 08:30:06', 5.5, 6.5, 0.2, 8.5, 'o', 'k', '2024-05-25', '2024-05-25', '2024-05-25', 'a', 'b', 'yyyyyyyyy'),     
(3, 4, 3, 6, '2024-06-02 09:25:07', 5.5, 6.5, 0.3, 8.5, 'o', 'k', '2024-06-02', '2024-06-02', '2024-06-02', 'a', 'b', 'yyyyyyyyy');

CREATE TABLE IF NOT EXISTS partsupp (
    ps_partkey INTEGER NOT NULL, 
    ps_suppkey INTEGER NOT NULL, 
    ps_availqty INTEGER NOT NULL, 
    ps_supplycost DECIMALV3(15, 2) NOT NULL, 
    ps_comment VARCHAR(199) NOT NULL
)
DUPLICATE KEY(ps_partkey, ps_suppkey)
DISTRIBUTED BY HASH(ps_partkey) BUCKETS 3;

INSERT INTO partsupp VALUES     
(2, 3, 9, 10.01, 'supply1'),     
(4, 3, 9, 10.01, 'supply2'),     
(5, 6, 9, 10.01, 'supply3'),     
(6, 5, 10, 11.01, 'supply4');

Шаг 2: Типовой запрос с агрегацией по дням.

SELECT 
  l_linestatus, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  ps_partkey 
FROM 
  lineitem 
  LEFT JOIN partsupp ON l_partkey = ps_partkey 
  AND l_suppkey = ps_suppkey 
WHERE 
  date_trunc(l_ordertime, 'day') <= DATE '2024-05-25' 
  AND date_trunc(l_ordertime, 'day') >= DATE '2024-05-05' 
GROUP BY 
  l_linestatus, 
  ps_partkey;

Шаг 3: Создайте материализованное представление с дневными партициями, той же гранулярностью партиционирования, что и у базовой таблицы, и агрегацией данных по дням.

CREATE MATERIALIZED VIEW rollup_partition_mv 
BUILD IMMEDIATE REFRESH AUTO ON MANUAL 
partition by(order_date) 
DISTRIBUTED BY RANDOM BUCKETS 2 
AS 
SELECT 
  l_linestatus, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  ps_partkey, 
  date_trunc(l_ordertime, 'day') AS order_date 
FROM 
  lineitem 
  LEFT JOIN partsupp ON l_partkey = ps_partkey 
  AND l_suppkey = ps_suppkey 
GROUP BY 
  l_linestatus, 
  ps_partkey, 
  date_trunc(l_ordertime, 'day');

4.5 Хранение данных только самых последних партиций

Совет

Эта функция поддерживается начиная с Apache Doris 2.1.1.

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

Свойство	Описание
partition_sync_limit	Если поле партиционирования базовой таблицы имеет временной тип, задает диапазон партиций для синхронизации из базовой таблицы (используется вместе с partition_sync_time_unit). Например, значение 3 с единицей DAY означает, что из базовой таблицы синхронизируются только партиции и данные за последние 3 дня
partition_sync_time_unit	Единица времени для обновления партиций. Поддерживаются DAY / MONTH / YEAR, по умолчанию DAY
partition_date_format	Если поле партиционирования базовой таблицы имеет строковый тип, задает формат даты, необходимый для использования partition_sync_limit

Приведенное ниже материализованное представление хранит данные только за последние 3 дня. Если за последние 3 дня данных нет, прямой запрос к материализованному представлению не вернет данных.

CREATE MATERIALIZED VIEW latest_partition_mv 
BUILD IMMEDIATE REFRESH AUTO ON MANUAL 
partition by(order_date) 
DISTRIBUTED BY RANDOM BUCKETS 2 
PROPERTIES (
  "partition_sync_limit" = "3", 
  "partition_sync_time_unit" = "DAY", 
  "partition_date_format" = "yyyy-MM-dd"
)       
AS 
SELECT 
  l_linestatus, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  ps_partkey, 
  date_trunc(l_ordertime, 'day') AS order_date 
FROM 
  lineitem 
  LEFT JOIN partsupp ON l_partkey = ps_partkey 
  AND l_suppkey = ps_suppkey 
GROUP BY 
  l_linestatus, 
  ps_partkey, 
  date_trunc(l_ordertime, 'day');

---

5. Как использовать материализованные представления для ускорения запросов

5.1 Общий подход

Чтобы ускорить запросы с помощью материализованного представления, выполните следующие шаги:

1. Просмотрите файл профиля и найдите самую затратную по времени операцию запроса. Обычно узкое место находится в Join, Aggregate, Filter или вычисляемых выражениях.
2. Создайте материализованное представление для оператора, который является узким местом. Например, если Join потребляет много вычислительных ресурсов, а Aggregate относительно мало, создайте материализованное представление для Join.

5.2 Рекомендации по построению для четырех типов операций

5.2.1 Для Join

• Выделите общие схемы соединения таблиц, используемые в запросах, и создайте на их основе материализованное представление. Его использование позволяет избежать вычисления Join.
• Уберите фильтры из запроса, чтобы получить более универсальное материализованное представление для Join.

5.2.2 Для Aggregate

• Используйте поля с низкой кардинальностью в качестве измерений материализованного представления, чтобы минимизировать объем данных после агрегации.
• Гранулярность агрегации материализованного представления должна быть более детальной, чем у запроса (то есть измерения агрегации материализованного представления должны включать измерения агрегации запроса), а его агрегатные функции также должны включать функции, используемые в запросе.

Пример оценки кардинальности:

• Таблица t1 содержит 1,000,000 строк, а запрос включает GROUP BY a, b, c:
  • Если кардинальности a, b и c равны соответственно 100, 50 и 15, результат агрегации составит около 75,000 строк. Материализованное представление эффективно.
  • Если a, b и c коррелируют, объем данных после агрегации уменьшится еще сильнее.
  • Если кардинальность c равна 3,500, результат агрегации составит около 17,000,000 строк, что больше исходной таблицы. Материализованное представление не подходит.

5.2.3 Для Filter

• Если запросы часто фильтруют данные по одним и тем же полям, добавьте соответствующие фильтры в материализованное представление, чтобы уменьшить его объем данных.
• Фильтр материализованного представления должен быть менее строгим, чем фильтр запроса, а фильтр запроса должен включать фильтр материализованного представления.

Например, если запрос содержит условие a > 10 AND b > 5:

• Материализованное представление может вообще не содержать фильтра,
• Либо содержать фильтр с более широким диапазоном, например a > 5 AND b > 5 или a > 5.

5.2.4 Для вычисляемых выражений

• Предварительное вычисление затратных выражений, таких как CASE WHEN, или операций обработки строк может значительно повысить производительность запросов.
• Число столбцов в одном материализованном представлении не должно быть слишком большим. Группируйте SQL-запросы по типовым шаблонам и создавайте отдельные материализованные представления для каждой группы.

Полный пример ускорения запросов с агрегацией:

Запрос 1:

SELECT 
  l_linestatus, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  o_shippriority 
FROM 
  orders 
  LEFT JOIN lineitem ON l_orderkey = o_orderkey 
WHERE 
  o_orderdate <= DATE '2024-06-30' 
  AND o_orderdate >= DATE '2024-05-01' 
GROUP BY 
  l_linestatus, 
  o_shippriority,
  l_partkey;

Запрос 2:

SELECT 
  l_linestatus, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  o_shippriority 
FROM 
  orders 
  LEFT JOIN lineitem ON l_orderkey = o_orderkey 
WHERE 
  o_orderdate <= DATE '2024-06-30' 
  AND o_orderdate >= DATE '2024-05-01' 
GROUP BY 
  l_linestatus, 
  o_shippriority,
  l_suppkey;

Для этих запросов создайте более универсальное материализованное представление с агрегацией: включите и l_partkey, и l_suppkey в измерения агрегации, а o_orderdate используйте в условии фильтрации. Примечание: o_orderdate используется для компенсации условий материализованного представления и также должен входить в измерения агрегации. Тогда и запрос 1, и запрос 2 смогут использовать материализованное представление:

CREATE MATERIALIZED VIEW common_agg_mv
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
AS 
SELECT 
  l_linestatus, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  o_shippriority,
  l_suppkey,
  l_partkey,
  o_orderdate
FROM 
  orders 
  LEFT JOIN lineitem ON l_orderkey = o_orderkey 
GROUP BY 
  l_linestatus, 
  o_shippriority,
  l_suppkey,
  l_partkey,
  o_orderdate;

---

6. Типовые примеры использования

6.1 Сценарий 1: Ускорение запросов

Подходящие сценарии: BI-отчетность и другие задачи, чувствительные ко времени отклика запросов и требующие получения результатов за секунды. Соединения Join нескольких таблиц с последующей агрегацией потребляют значительные вычислительные ресурсы, что затрудняет соблюдение требований ко времени отклика. Асинхронные материализованные представления поддерживают как прямые запросы, так и прозрачное переписывание. Оптимизатор автоматически выбирает оптимальное материализованное представление на основе алгоритма переписывания и стоимостной модели.

Пример 1: Ускорение запросов с соединением нескольких таблиц и агрегацией

Создайте более универсальное материализованное представление для ускорения запросов с соединением нескольких таблиц и агрегацией.

Цель: Создать одно материализованное представление, которое подходит для всех трех запросов ниже.

Запрос 1:

SELECT 
  l_linestatus, 
  l_extendedprice * (1 - l_discount),
  o_shippriority 
FROM 
  orders 
  LEFT JOIN lineitem ON l_orderkey = o_orderkey 
WHERE 
  o_orderdate <= DATE '2024-06-30' 
  AND o_orderdate >= DATE '2024-05-01';

Запрос 2:

SELECT 
  l_linestatus, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  o_orderdate, 
  o_shippriority 
FROM 
  orders 
  LEFT JOIN lineitem ON l_orderkey = o_orderkey 
WHERE 
  o_orderdate <= DATE '2024-06-30' 
  AND o_orderdate >= DATE '2024-05-01' 
GROUP BY 
  l_linestatus, 
  o_orderdate, 
  o_shippriority;

Запрос 3:

SELECT 
  l_linestatus, 
  l_extendedprice * (1 - l_discount),
  o_orderdate, 
  o_shippriority 
FROM 
  orders 
  LEFT JOIN lineitem ON l_orderkey = o_orderkey;

Вариант построения 1: Универсальное материализованное представление для Join. Уберите условия фильтрации из запроса 1 и запроса 2 и заранее вычислите l_extendedprice * (1 - l_discount):

CREATE MATERIALIZED VIEW common_join_mv
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
AS 
SELECT 
  l_linestatus, 
  l_extendedprice * (1 - l_discount),
  o_orderdate, 
  o_shippriority 
FROM 
  orders 
  LEFT JOIN lineitem ON l_orderkey = o_orderkey;

Вариант построения 2: Если приведенное выше материализованное представление не обеспечивает требуемого ускорения запроса 2, создайте дополнительное материализованное представление с агрегацией. Уберите фильтр по o_orderdate, чтобы сохранить универсальность:

CREATE MATERIALIZED VIEW target_agg_mv
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
AS 
SELECT 
  l_linestatus, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  o_orderdate, 
  o_shippriority 
FROM 
  orders 
  LEFT JOIN lineitem ON l_orderkey = o_orderkey 
GROUP BY 
  l_linestatus, 
  o_orderdate, 
  o_shippriority;

Пример 2: Ускорение запросов к журналам

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

Шаг 1: Определение базовой таблицы.

CREATE TABLE IF NOT EXISTS test (
`app_name` VARCHAR(64) NULL COMMENT 'identifier', 
`event_id` VARCHAR(128) NULL COMMENT 'identifier', 
`decision` VARCHAR(32) NULL COMMENT 'enum value', 
`time` DATETIME NULL COMMENT 'query time', 
`id` VARCHAR(35) NOT NULL COMMENT 'id', 
`code` VARCHAR(64) NULL COMMENT 'identifier', 
`event_type` VARCHAR(32) NULL COMMENT 'event type' 
)
DUPLICATE KEY(app_name, event_id)
PARTITION BY RANGE(time)                                    
(                                                                                                                                      
    FROM ("2024-07-01 00:00:00") TO ("2024-07-15 00:00:00") INTERVAL 1 HOUR                                                                     
)     
DISTRIBUTED BY HASH(event_id)
BUCKETS 3;

Шаг 2: Создайте материализованное представление с агрегацией по минутам, чтобы достичь определенной степени агрегации.

CREATE MATERIALIZED VIEW sync_mv
AS
SELECT 
  decision,
  code, 
  app_name, 
  event_id, 
  event_type, 
  date_trunc(time, 'minute'), 
  DATE_FORMAT(
    `time`, '%Y-%m-%d'
  ), 
  cast(FLOOR(MINUTE(time) / 15) AS decimal(9, 0)),
  count(id) AS cnt
FROM 
  test 
GROUP BY 
  code, 
  app_name, 
  event_id, 
  event_type, 
  date_trunc(time, 'minute'), 
  decision, 
  DATE_FORMAT(time, '%Y-%m-%d'), 
  cast(FLOOR(MINUTE(`time`) / 15) AS decimal(9, 0));

Шаг 3: Типовой запрос.

SELECT 
    decision, 
    CONCAT(
        CONCAT(
          DATE_FORMAT(
            `time`, '%Y-%m-%d'
          ), 
          '', 
          LPAD(
            cast(FLOOR(MINUTE(`time`) / 15) AS decimal(9, 0)) * 15, 
            5, 
            '00'
          ), 
          ':00'
        )
      ) AS time, 
      count(id) AS cnt 
FROM 
  test 
WHERE 
  date_trunc(time, 'minute') BETWEEN '2024-07-02 18:00:00' 
  AND '2024-07-03 20:00:00' 
GROUP BY 
  decision, 
  DATE_FORMAT(
    `time`, "%Y-%m-%d"
  ), 
  cast(FLOOR(MINUTE(`time`) / 15) AS decimal(9, 0));

6.2 Сценарий 2: Моделирование данных (ETL)

Подходящие сценарии: Анализ данных часто требует соединения и агрегации нескольких таблиц, а сложные и повторяющиеся запросы приводят к высоким задержкам и значительному потреблению ресурсов. Используйте асинхронные материализованные представления для построения многоуровневой модели данных. На основе существующих материализованных представлений можно создавать представления более высокого уровня (поддерживается начиная с 2.1.3).

Выбор способов запуска для разных уровней:

• Обновление первого уровня по расписанию + обновление второго уровня по событию: После завершения обновления первого уровня автоматически запускается обновление второго.
• Обновление всех уровней по расписанию: При обновлении второго уровня не учитывается, синхронизирован ли первый уровень с базовой таблицей; выполняются только обработка данных первого уровня и их синхронизация со вторым.

В следующем примере набор данных TPC-H используется для анализа количества заказов и прибыли по регионам и странам за каждый месяц.

Исходный запрос (без материализованных представлений):

SELECT
n_name,
date_trunc(o.o_orderdate, 'month') AS month,
count(distinct o.o_orderkey) AS order_count,
sum(l.l_extendedprice * (1 - l.l_discount)) AS revenue
FROM orders o
JOIN lineitem l ON o.o_orderkey = l.l_orderkey
JOIN customer c ON o.o_custkey = c.c_custkey
JOIN nation n ON c.c_nationkey = n.n_nationkey
JOIN region r ON n.n_regionkey = r.r_regionkey
GROUP BY n_name, month;

Шаг 1: Создайте уровень DWD (детальные данные): широкую таблицу с деталями заказов.

CREATE MATERIALIZED VIEW dwd_order_detail
BUILD IMMEDIATE REFRESH AUTO ON COMMIT
DISTRIBUTED BY RANDOM BUCKETS 16
AS
SELECT
o.o_orderkey,
o.o_custkey,
o.o_orderstatus,
o.o_totalprice,
o.o_orderdate,
c.c_name,
c.c_nationkey,
n.n_name AS nation_name,
r.r_name AS region_name,
l.l_partkey,
l.l_quantity,
l.l_extendedprice,
l.l_discount,
l.l_tax
FROM orders o
JOIN customer c ON o.o_custkey = c.c_custkey
JOIN nation n ON c.c_nationkey = n.n_nationkey
JOIN region r ON n.n_regionkey = r.r_regionkey
JOIN lineitem l ON o.o_orderkey = l.l_orderkey;

Шаг 2: Создайте уровень DWS (сводные данные): ежедневную сводку заказов.

CREATE MATERIALIZED VIEW dws_daily_sales
BUILD IMMEDIATE REFRESH AUTO ON COMMIT
DISTRIBUTED BY RANDOM BUCKETS 16
AS
SELECT
date_trunc(o_orderdate, 'month') AS month,
nation_name,
region_name,
bitmap_union(to_bitmap(o_orderkey)) AS order_count,
sum(l_extendedprice * (1 - l_discount)) AS net_revenue
FROM dwd_order_detail
GROUP BY
date_trunc(o_orderdate, 'month'),
nation_name,
region_name;

Шаг 3: Используйте материализованное представление для оптимизации запроса.

SELECT
nation_name,
month,
bitmap_union_count(order_count),
sum(net_revenue) AS revenue
FROM dws_daily_sales
GROUP BY nation_name, month;

6.3 Сценарий 3: Федеративные запросы к данным lakehouse

Подходящие сценарии: Современные архитектуры данных часто используют подход lakehouse для баланса между стоимостью хранения и производительностью запросов. У этой архитектуры есть две основные сложности:

• Ограниченная производительность запросов: Частые запросы к озеру данных зависят от сетевых задержек и сторонних сервисов, что увеличивает время выполнения запросов.
• Сложное многоуровневое моделирование данных: Перенос и преобразование данных из озера в хранилище данных реального времени обычно требуют сложного ETL с высокими затратами на сопровождение.

Как асинхронные материализованные представления Doris решают эти проблемы:

• Прозрачное переписывание для ускорения запросов: Результаты часто используемых запросов к озеру данных материализуются во внутреннем хранилище Doris, а прозрачное переписывание повышает производительность запросов.
• Упрощенное многоуровневое моделирование: Поддерживается создание материализованных представлений на основе таблиц озера данных, что упрощает переход от озера данных к хранилищу данных реального времени.

В примере ниже используется Hive.

Шаг 1: Создайте Catalog на основе Hive (с набором данных TPC-H).

CREATE CATALOG hive_catalog PROPERTIES (
'type'='hms', -- hive meta store address
'hive.metastore.uris' = 'thrift://172.21.0.1:7004'
);

Шаг 2: Создайте материализованное представление на основе Hive Catalog.

-- Materialized views can only be created on the internal catalog. Switch to the internal catalog
SWITCH internal;
CREATE DATABASE hive_mv_db;
USE hive_mv_db;

CREATE MATERIALIZED VIEW external_hive_mv
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 12
AS
SELECT
n_name,
o_orderdate,
sum(l_extendedprice * (1 - l_discount)) AS revenue
FROM
customer,
orders,
lineitem,
supplier,
nation,
region
WHERE
c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND l_suppkey = s_suppkey
AND c_nationkey = s_nationkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA'
GROUP BY
n_name,
o_orderdate;

Шаг 3: Выполните запрос: он будет автоматически ускорен за счет прозрачного переписывания с использованием материализованного представления.

SELECT
n_name,
sum(l_extendedprice * (1 - l_discount)) AS revenue
FROM
customer,
orders,
lineitem,
supplier,
nation,
region
WHERE
c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND l_suppkey = s_suppkey
AND c_nationkey = s_nationkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA'
AND o_orderdate >= DATE '1994-01-01'
AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' YEAR
GROUP BY
n_name
ORDER BY
revenue DESC;

Совет

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

Переключатель прозрачного переписывания для внешних таблиц (по умолчанию false): Определяет, разрешено ли материализованным представлениям, участвующим в прозрачном переписывании, содержать внешние таблицы. Если вы допускаете несогласованность данных или можете обеспечить согласованность обновлением по расписанию, включите его:

SET materialized_view_rewrite_enable_contain_external_table = true;

Диагностика случая, когда переписанный план не выбран: Если материализованное представление находится в состоянии MaterializedViewRewriteSuccessButNotChose, переписывание выполнено успешно, но CBO не выбрал полученный план. Причиной может быть неполная статистика внешней таблицы.

Включите получение количества строк из файлов:

SET enable_get_row_count_from_file_list = true;

Просмотрите статистику внешней таблицы, чтобы проверить полноту ее сбора:

SHOW TABLE STATS external_table_name;

6.4 Сценарий 4: Повышение эффективности записи и снижение конкуренции за ресурсы

Подходящие сценарии: Запись данных с высокой пропускной способностью, требующая стабильной производительности системы и эффективной обработки данных. Гибкие стратегии обновления асинхронных материализованных представлений позволяют снизить нагрузку при записи и избежать конкуренции за ресурсы.

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

Пример: Стратегия обновления по расписанию с интервалом 2 часа. При загрузке данных в orders и lineitem обновление материализованного представления не запускается немедленно.

CREATE MATERIALIZED VIEW common_schedule_join_mv
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 2 HOUR
DISTRIBUTED BY RANDOM BUCKETS 16
AS
SELECT
l_linestatus,
l_extendedprice * (1 - l_discount),
o_orderdate,
o_shippriority
FROM
orders
LEFT JOIN lineitem ON l_orderkey = o_orderkey;

Прозрачное переписывание повышает эффективность загрузки

Прозрачное переписывание ускоряет запросы и также позволяет переписывать SQL-операторы загрузки, повышая ее эффективность. Начиная с версии 2.1.6, если материализованное представление строго согласовано с базовой таблицей, операции DML (например, INSERT INTO или INSERT OVERWRITE) могут прозрачно переписываться, обеспечивая значительный прирост производительности при загрузке данных.

Шаг 1: Создайте целевую таблицу для данных, загружаемых через INSERT INTO.

CREATE TABLE IF NOT EXISTS target_table  (
orderdate      DATE NOT NULL,
shippriority   INTEGER NOT NULL,
linestatus     CHAR(1) NOT NULL,
sale           DECIMALV3(15,2) NOT NULL
)
DUPLICATE KEY(orderdate, shippriority)
DISTRIBUTED BY HASH(shippriority) BUCKETS 3;

Шаг 2: Создайте материализованное представление common_schedule_join_mv.

CREATE MATERIALIZED VIEW common_schedule_join_mv
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 2 HOUR
DISTRIBUTED BY RANDOM BUCKETS 16
AS
SELECT
l_linestatus,
l_extendedprice * (1 - l_discount),
o_orderdate,
o_shippriority
FROM
orders
LEFT JOIN lineitem ON l_orderkey = o_orderkey;

Шаг 3: Оператор загрузки до переписывания.

INSERT INTO target_table
SELECT
o_orderdate,
o_shippriority,
l_linestatus,
l_extendedprice * (1 - l_discount)
FROM
orders
LEFT JOIN lineitem ON l_orderkey = o_orderkey;

Шаг 4: Эквивалентный оператор после прозрачного переписывания.

INSERT INTO target_table
SELECT *
FROM common_schedule_join_mv;

Примечание

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

Для DML этот переключатель определяет, разрешено ли прозрачное переписывание материализованного представления на основе структуры, если оно содержит внешнюю таблицу, изменения данных которой невозможно обнаружить в реальном времени (по умолчанию отключено):

SET enable_dml_materialized_view_rewrite_when_base_table_unawareness = true;

---

7. Рекомендации по эксплуатации

Асинхронные материализованные представления по сути являются расширенными вычислениями ETL и требуют постоянного сопровождения. Следующие три аспекта особенно важны в повседневной эксплуатации.

1. Мониторинг: После запуска материализованного представления своевременно отслеживайте состояние системы с помощью метрик. В будущем для асинхронных материализованных представлений станет доступно больше метрик мониторинга. Сейчас можно использовать tasks для просмотра количества задач, состояния выполнения, продолжительности задач и другой информации.
2. Планирование: Планируйте количество материализованных представлений, частоту обновления и предельную вычислительную мощность кластера. Не следует «просто создавать материализованные представления и не обслуживать их». Материализованное представление по сути является расширенным вычислением ETL и требует сопровождения так же, как традиционный ETL.
3. Изоляция ресурсов: Материализованное представление представляет собой задачу вычисления данных, поэтому при необходимости применяйте изоляцию ресурсов.

---

Часто задаваемые вопросы

Q1: Могут ли асинхронные материализованные представления полностью заменить запросы в реальном времени?

Нет. Асинхронные материализованные представления имеют задержку данных, которая зависит от способа запуска, ожидания в очереди и времени обновления. IVM снижает вычислительные затраты при частых изменениях небольшими пакетами, но также не обеспечивает согласованность в реальном времени. Для сценариев с высокими требованиями к актуальности рассмотрите синхронные материализованные представления.

Q2: Можно ли настроить частое обновление по расписанию для всех материализованных представлений, чтобы приблизиться к реальному времени?

Не рекомендуется. Это приводит к постоянному потреблению системных ресурсов, конкуренции задач обновления между собой и частому добавлению и удалению партиций / tablets, что создает большую нагрузку на BE.

Q3: Как выбрать стратегию обновления?

Сначала выберите гранулярность вычислений, затем способ запуска. Если количество измененных строк значительно меньше объема затронутых партиций, рассмотрите IVM; если изменения сосредоточены в небольшом числе партиций, сначала оцените возможность использования партиционированного материализованного представления. При выборе способа запуска обратитесь к разделам Сравнение трех стратегий обновления и Рекомендации по сочетанию стратегий обновления.

Q4: Нуждаются ли материализованные представления в сопровождении после создания?

Да. Материализованное представление по сути является расширенным вычислением ETL и требует мониторинга, планирования и изоляции ресурсов. Подробности см. в разделе Рекомендации по эксплуатации.

Q5: Можно ли использовать прозрачное переписывание, если базовая таблица часто обновляется?

Частые обновления базовой таблицы приводят к частой потере актуальности материализованного представления, из-за чего его нельзя использовать для прозрачного переписывания (прямые запросы по-прежнему возможны). Чтобы использовать прозрачное переписывание в этом сценарии, необходимо допустить некоторую задержку запрашиваемых данных, которую можно настроить с помощью grace_period.