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

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

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

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

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

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

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

---

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

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

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

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

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

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

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

Отчёты

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

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

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

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

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

Ускорение запросов в Lakehouse

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

Многоуровневая организация хранилища данных

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

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

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

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

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

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

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

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

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

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

---

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

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

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

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

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

Примечание

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

---

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

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

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

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

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

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

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

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

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

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

Обновление вручную

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

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

• Принцип работы:
  • Автоматическое обновление через фиксированные интервалы времени
  • Минимальная единица времени соответствует минутам
  • Можно указать время первого запуска задачи
• Подходящие сценарии:
  • Периодический мониторинг бизнес-показателей
  • Многоуровневые конвейеры данных
  • Системы отчётности с разными уровнями требований к оперативности
  • Исходные данные с регулярными колебаниями
• Плюсы: Обработка данных по расписанию с предсказуемой задержкой данных.
• Минусы: Ограниченная актуальность данных; последовательность обновления связанных представлений необходимо согласовывать вручную.
• Ограничения настройки: Не рекомендуется задавать всем материализованным представлениям частое обновление по расписанию ради приближения к реальному времени, поскольку это приводит к следующим последствиям:
  • Постоянное занятие системных ресурсов
  • Конкуренция заданий обновления за ресурсы
  • Частое добавление и удаление секций / tablet, создающее высокую нагрузку на 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: Могут ли асинхронные материализованные представления полностью заменить запросы в реальном времени?

Нет. Данные в асинхронных материализованных представлениях обновляются с задержкой (зависящей от стратегии обновления), поэтому они не подходят для сценариев, в которых требуется актуальность данных в пределах от 1 до 5 минут. Для сценариев с высокими требованиями к оперативности рассмотрите синхронные материализованные представления.

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

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

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

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

Q4: Требуется ли сопровождение материализованных представлений после их создания?

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

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

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