CREATE MATERIALIZED VIEW

Содержание раздела

Синтаксис
Ограничения
Примеры

Поддерживается в версиях: 6.4 / 6.3 / 6.2 / 6.1 / 6.0 / 5.8 / 5.7 / 5.6 / 5.5 / 5.4 / 5.3 / 5.2 / 5.1 / 5.0.

Запрос создает материализованное представление в логической базе данных.

Материализованное представление может содержать результаты SELECT-запроса из сущностей одной или нескольких логических БД, кроме следующих сущностей:

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

Возможны следующие варианты материализованных представлений:

источник данных расположен в датасорсе типа ADB, а данные представления — в датасорсе типа ADP, ADG и (или) ADQM,
источник данных расположен в датасорсе типа ADP, а данные представления — в том же или другом датасорсе типа ADP и (или) в датасорсе типа ADG.

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

Запрос попадает в очередь операций и обрабатывается в порядке его поступления в очередь. В ответе возвращается:

пустой объект ResultSet при успешном выполнении запроса;
исключение при неуспешном выполнении запроса.

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

В отличие от запроса на создание логической таблицы, запрос CREATE MATERIALIZED VIEW должен содержать ключевое слово DATASOURCE_TYPE со списком датасорсов для размещения данных представления. Требование связано с тем, что данные представлений (в отличие от данных логических таблиц) могут размещаться не во всех СУБД.

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

При использовании ограничения NOT NULL для столбцов представления следует учитывать, что вставка значений NULL в такие столбцы приведет к ошибке синхронизации представления.

Изменение материализованного представления недоступно. Чтобы заменить материализованное представления, удалите его и создайте новое.

При ошибке выполнения корректного запроса все последующие запросы в логической БД блокируются. В этом случае следует устранить причины сбоя и повторить сбойную операцию. Если повторение операции невозможно, удалите сбойную операцию запросом ERASE_CHANGE_OPERATION и вручную устраните несоответствия на физическом уровне (если такие есть).

Каждое создание представления записывается в журнал. Журнал можно посмотреть с помощью запроса GET_CHANGES.

Синтаксис

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name (
  column_name_1 datatype_1 [ NULL | NOT NULL ],
  column_name_2 datatype_2 [ NULL | NOT NULL ],
  column_name_3 datatype_3 [ NULL | NOT NULL ],
  PRIMARY KEY (column_list_1)
) DISTRIBUTED BY (column_list_2)
[DATASOURCE_TYPE (datasource_aliases)]
AS SELECT query
DATASOURCE_TYPE = origin_datasource_alias
[LOGICAL_ONLY]

Параметры:

db_name: Имя логической базы данных, в которой создается материализованное представление. Опционально, если выбрана логическая БД, используемая по умолчанию.
materialized_view_name: Имя создаваемого материализованного представления, уникальное среди логических сущностей логической БД.
column_name_N: Имя столбца представления.
datatype_N: Тип данных столбца column_name_N. Возможные значения см. в разделе Логические типы данных.
column_list_1: Список столбцов, входящих в первичный ключ представления.
column_list_2: Список столбцов, входящих в ключ шардирования представления. Столбцы должны быть из числа столбцов column_list_1.
datasource_aliases: Список имен датасорсов, в которых нужно разместить данные представления. Элементы списка указываются в одинарных кавычках (например, 'adg') и разделяются запятой.
Варианты, которые остаются доступными, но могут быть удалены в будущем: значения без кавычек (например, adg) или в двойных кавычках (например, "adg").
query: SELECT-подзапрос, на основе которого строится представление.
origin_datasource_alias: Имя датасорса, который служит источником данных. Значение указывается в одинарных кавычках.

Ключевое слово IF NOT EXISTS

Ключевое слово IF NOT EXISTS включает проверку наличия материализованного представления до попытки создания. Если ключевое слово указано в запросе, успешный ответ возвращается при наличии или успешном создании материализованного представления, иначе — только при успешном создании материализованного представления.

Ключевое слово PRIMARY KEY

Ключевое слово PRIMARY KEY задает список столбцов, входящих в первичный ключ представления.

Ключевое слово DISTRIBUTED BY

Ключевое слово DISTRIBUTED BY задает список столбцов, входящих в ключ шардирования представления. Все указываемые столбцы должны присутствовать в первичном ключе.

Ключевое слово DATASOURCE_TYPE

Первое ключевое слово DATASOURCE_TYPE задает список датасорсов для размещения данных материализованного представления, второе ключевое DATASOURCE_TYPE — датасорс, из которого выбираются данные. См. также параметры datasource_aliases и origin_datasource_alias.

Ключевое слово AS SELECT

Ключевое слово AS SELECT задает SELECT-подзапрос, на котором строится представление.

Ключевое слово LOGICAL_ONLY

Ключевое слово LOGICAL_ONLY позволяет создать материализованное представление только на логическом уровне (в логической схеме данных), без пересоздания связанных физических таблиц в хранилище данных.

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

Ограничения

Ограничения выполнения

Выполнение запроса недоступно, если есть незавершенная операция записи или установлен запрет на изменение сущностей.

Ограничения СУБД

Данные материализованных представлений могут размещаться в СУБД ADP, ADQM и ADG.
Источником данных для материализованного представления может быть СУБД ADB или ADP.

Ограничения имен

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

Ограничения столбцов

Порядок столбцов и их типы данных должны совпадать в SELECT-подзапросе и представлении.
Первичный ключ должен включать все столбцы ключа шардирования.

Ограничения SELECT-подзапроса

Подзапрос не может содержать:
- логические и материализованные представления,
- партиционированные таблицы,
- системные представления INFORMATION_SCHEMA,
- ключевое слово FOR SYSTEM_TIME,
- ключевое слово ORDER BY,
- ключевое слово LIMIT.
Если в подзапросе указана одна логическая таблица и нет группировки и агрегации данных, указанная таблица и материализованное представление должны находиться в одной логической БД.
Система не проверяет корректность подзапроса при создании представления.

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

Другие ограничения

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

Примеры

Представление на основе одной таблицы с условием

Создание представления с размещением в двух датасорсах (adg и adqm):

CREATE MATERIALIZED VIEW marketing.sales_august_2020 (
  id BIGINT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units BIGINT NOT NULL,
  store_id BIGINT NOT NULL,
  description VARCHAR(256),
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adg', 'adqm')
AS SELECT * FROM marketing.sales
   WHERE cast(transaction_date as date) BETWEEN '2020-08-01' AND '2020-08-31'
DATASOURCE_TYPE = 'adb'

Представление на основе одной таблицы с условием, агрегацией и группировкой

Создание представления с размещением в одном датасорсе типа ADP (adp1) и источником в другом датасорсе того же типа (adp2):

CREATE MATERIALIZED VIEW marketing.sales_by_stores (
  store_id BIGINT NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units BIGINT NOT NULL,
  PRIMARY KEY (store_id, product_code)
)
DISTRIBUTED BY (store_id)
DATASOURCE_TYPE ('adp2')
AS SELECT store_id, product_code, SUM(product_units) FROM marketing.sales
  WHERE product_code <> 'ABC0001'
  GROUP BY store_id, product_code
DATASOURCE_TYPE = 'adp1'

Представление на основе двух таблиц

Создание представления с размещением данных в том же датасорсе, где находится источник (adp):

CREATE MATERIALIZED VIEW marketing.sales_and_stores (
  id BIGINT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units BIGINT NOT NULL,
  description VARCHAR(256),
  store_id BIGINT NOT NULL,
  store_category VARCHAR(256) NOT NULL,
  region VARCHAR(256) NOT NULL,
  PRIMARY KEY (id, region)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp')
AS SELECT
  s.id, s.transaction_date, s.product_code, s.product_units, s.description,
  st.id AS store_id, st.category as store_category, st.region
  FROM marketing.sales AS s
  JOIN marketing.stores AS st
  ON s.store_id = st.id
DATASOURCE_TYPE = 'adp'

Представление только на логическом уровне

CREATE MATERIALIZED VIEW marketing.stores_by_sold_products_matview (
  store_id BIGINT NOT NULL,
  product_amount BIGINT NOT NULL,
  PRIMARY KEY (store_id)
)
DISTRIBUTED BY (store_id)
DATASOURCE_TYPE ('adg')
AS SELECT store_id, SUM(product_units) AS product_amount
  FROM marketing.sales
  GROUP BY store_id
DATASOURCE_TYPE = 'adb'
LOGICAL_ONLY

Представление на основе данных из двух логических баз данных

CREATE MATERIALIZED VIEW marketing.moscow_sales_by_store_category (
  store_id BIGINT,
  category VARCHAR(256),
  product_units BIGINT,
  PRIMARY KEY (store_id)
)
DISTRIBUTED BY (store_id)
DATASOURCE_TYPE ('adg', 'adqm', 'adp')
AS SELECT s.store_id, st.category, SUM(s.product_units) as product_units
  FROM moscow.sales as s
  JOIN marketing.stores as st
    ON s.store_id = st.id
  GROUP BY s.store_id, st.category
DATASOURCE_TYPE = 'adb'