CREATE MATERIALIZED VIEW

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

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

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

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

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

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

источник данных расположен в СУБД ADB, а данные представления — в СУБД ADP, ADG и (или) ADQM,
источник данных и данные представления расположены в одной или разных СУБД ADP.

Будьте осторожны при построении материализованных представлений на основе соединения логических таблиц и 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").

Данные представления могут размещаться в СУБД ADG, ADQM и (или) ADP.

query: SELECT-подзапрос, на основе которого строится представление.
origin_datasource_alias: Имя датасорса, который служит источником данных. Значение указывается в одинарных кавычках.

Источником данных может быть СУБД ADB или ADP.

Ключевое слово 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'