CREATE MATERIALIZED VIEW

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

Поддерживаемые типы датасорсов
Представления на основе прокси-таблиц и standalone-таблиц
Результат выполнения
Как работает запрос
Синтаксис
Варианты ответа
Ограничения
Примеры

Поддерживается в версиях: 7.3 / 7.2 / 7.1 / 7.0 / 6.12 / 6.11 / 6.10 / 6.9 / 6.8 / 6.7 / 6.6 / 6.5 / 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.

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

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

Поддерживаемые типы датасорсов

Возможные комбинации источника и приемников данных материализованного представления (источник слева от стрелки, приемники — справа):

внешняя система → представление в датасорсах любых типов;
сущности логических БД → представление в том же датасорсе:
- ADB → ADB;
- ADP → ADP;
- ADG → ADG;
сущности логических БД → представление в других датасорсах:
- ADB → ADP, ADQM и (или) ADG;
- ADP → ADP и (или) ADG.

Представления на основе прокси-таблиц и standalone-таблиц

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

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

Результат выполнения

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

Созданное представление начинает синхронизацию (если она не отключена) в первом доступном цикле синхронизации. Статус синхронизации доступен с помощью запроса CHECK_MATERIALIZED_VIEW.

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

Как работает запрос

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

При ошибке исполнения корректного запроса система блокирует все последующие DDL-запросы в логической БД. О снятии такой блокировки см. в разделе Снятие блокировки DDL-запросов.

Синтаксис

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] [db_name.]materialized_view_name (
  column_name data_type [ NULL | NOT NULL ],
  [ ... , ]
  PRIMARY KEY (column_name[, ... ])
) 
[ DISTRIBUTED BY (column_name[, ... ]) ]
[ DATASOURCE_TYPE ('destination_datasource'[, ... ]) ]
AS { SELECT query | EXEC('ext_system_name://ext_db_name.ext_query_alias') }
[ DATASOURCE_TYPE = 'source_datasource' ]
[ OPTIONS ('
    matview.sync.period.ms=sync_period_integer;
    matview.sync.snapshot.enable=snapshot_boolean;
    agent.query.timeout.ms=ext_timeout_integer;
') ]
[ LOGICAL_ONLY ]

Столбцы с ограничением NOT NULL не предполагают вставку NULL-значений. Вставка NULL в такие столбцы приведет к ошибке синхронизации представления.

Параметры:

db_name

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

materialized_view_name

Имя создаваемого материализованного представления, уникальное среди логических сущностей логической БД.

column_name

Имя столбца представления. Должно соответствовать соглашения об именах.

Если подзапрос query выбирает системные столбцы sys_from, sys_to и sys_op, имена соответствующих столбцов материализованного представления не могут начинаться с префикса sys_ (см. пример ниже). Полный список зарезервированных слов см. в разделе Зарезервированные слова.

data_type

Тип данных столбца column_name. Возможные значения см. в разделе Логические типы данных.

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

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

Ключевое слово `PRIMARY KEY (column_name[, ... ])`

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

Ключевое слово `DISTRIBUTED BY (column_name[, ... ])`

Задает список столбцов, входящих в ключ шардирования представления. Обязательно для датасорсов типа ADQM и ADG, опционально — для ADP и ADB.

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

Ключевое слово `DATASOURCE_TYPE ('destination_datasource'[, ... ])`

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

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

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

Задает обычный или инкрементальный SELECT-подзапрос, на котором строится представление.

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

Формат инкрементального подзапроса

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

Инкрементальный подзапрос может содержать именованные параметры в следующем формате (где entity_name — имя сущности-источника, logical_db_name — имя ее логической БД):

<logical_db_name>_<entity_name>_cn0 — номер операции записи, до которой представление синхронизировалось в предыдущем цикле синхронизации;
<logical_db_name>_<entity_name>_cn1— номер операции записи, с которой представление должно синхронизироваться в текущем цикле синхронизации (равен cn0 + 1);
<logical_db_name>_<entity_name>_cn2— номер операции записи, до которой представление должно синхронизироваться в текущем цикле синхронизации.

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

Пример инкрементального подзапроса

Пример подзапроса, выбирающего все изменения по одной логической таблице:

SELECT * FROM (
    -- добавление/обновление записей, добавленных/обновленных в источнике
    SELECT *, 0 as sys_op 
      FROM marketing.sales
      FOR SYSTEM_TIME STARTED CN (:marketing_sales_cn1, :marketing_sales_cn2)
    UNION ALL
    -- удаление записей, удаленных из источника
    SELECT *, 1 as sys_op 
      FROM marketing.sales 
      FOR SYSTEM_TIME FINISHED CN (:marketing_sales_cn1, :marketing_sales_cn2)
) as sales_query

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

Ключевое слово `AS EXEC('ext_system_name://ext_db_name.ext_query_alias')`

Задает запрос получения данных из внешнего источника:

ext_system_name — имя внешней системы;
ext_db_name — имя логической БД во внешней системе;
ext_query_alias — имя запроса к внешней системе для синхронизации представления. Внешняя система определяет перечень запросов, доступных для исполнения, и их имена.

Ключевое слово `DATASOURCE_TYPE = 'source_datasource'`

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

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

Ключевое слово `OPTIONS ('option=value[; ... ]')`

Задает дополнительные параметры (option) и их значения (value).

Доступные параметры:

matview.sync.period.ms — интервал синхронизации представления. Переопределяет значение по умолчанию (MATERIALIZED_VIEWS_SYNC_PERIOD_MS), задается в миллисекундах;
matview.sync.snapshot.enable — признак загрузки снимка состояния данных из источника при первой синхронизации представления. Возможные значения:
- true (по умолчанию) — загрузить снимок состояния данных;
- false — пропустить загрузку снимка состояния данных;
agent.query.timeout.ms — время ожидания ответа от внешней системы по запросу получения данных AS EXEC. Переопределяет значение по умолчанию (AGENT_ENDPOINT_QUERY_TIMEOUT_MS), задается в миллисекундах.

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

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

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

Варианты ответа

В ответе возвращается:

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

Ограничения

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

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

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

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

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

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

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

Подзапрос не может содержать:
- системные столбцы sys_op, sys_from и sys_to в списке выбираемых столбцов без указания псевдонимов;
- ключевое слово FOR SYSTEM_TIME (кроме инкрементальных подзапросов);
- системные представления INFORMATION_SCHEMA;
- ключевое слово ORDER BY;
- ключевое слово LIMIT.
Система не проверяет корректность подзапроса при создании представления.

Перед созданием представления рекомендуется проверить его SELECT-подзапрос, выполнив как самостоятельный запрос. Альтернативно можно использовать CHECK_MATERIALIZED_VIEW: если представление синхронизировано без ошибок, подзапрос корректен.

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

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

Примеры

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

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

CREATE MATERIALIZED VIEW matview_db.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'

Представление с системными столбцами таблицы-источника

Создание представления, содержащего системные значения sys_from и sys_to таблицы-источника (столбцы version_from и version_to соответственно):

CREATE MATERIALIZED VIEW matview_db.sales_matview_with_sys_columns (
  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),
  version_from BIGINT,
  version_to BIGINT,
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp')
AS SELECT *, sys_from as version_from, sys_to as version_to FROM marketing.sales
DATASOURCE_TYPE = 'adp'
OPTIONS ('
  matview.sync.period.ms=60000;
')

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

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

CREATE MATERIALIZED VIEW matview_db.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'
OPTIONS ('
    matview.sync.period.ms=10000;
    matview.sync.snapshot.enable=false;
')

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

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

CREATE MATERIALIZED VIEW matview_db.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 matview_db.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 matview_db.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'

Представления на основе данных внешней системы

Создание представления со снимком данных источника (отсутствие опции matview.sync.snapshot.enable равнозначно matview.sync.snapshot.enable=true):

CREATE MATERIALIZED VIEW matviews_for_ext_source.matview_with_snapshot (
  id BIGINT NOT NULL,
  varchar_col VARCHAR(36),
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE('adp')
AS EXEC('agent://datamart_1.rz1_mnemonic')

Создание представления без снимка данных источника (matview.sync.snapshot.enable=false), но с указанием опций matview.sync.period.ms и agent.query.timeout.ms:

CREATE MATERIALIZED VIEW matviews_for_ext_source.matview_no_snapshot (
  id BIGINT NOT NULL,
  varchar_col VARCHAR(36),
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE('adp')
AS EXEC('agent://datamart_1.rz1_mnemonic')
OPTIONS ('
  matview.sync.period.ms=60000;
  matview.sync.snapshot.enable=false;
  agent.query.timeout.ms=180000;
')

Представления на основе инкрементального подзапроса

Создание представления на основе инкрементального подзапроса, выбирающего все изменения (новые, обновленные и удаленные записи) из одной таблицы:

CREATE MATERIALIZED VIEW matview_db.sales_incremental_matview (
  id BIGINT NOT NULL,
  transaction_date TIMESTAMP(6),
  product_code VARCHAR(256),
  product_units BIGINT,
  store_id BIGINT,
  description VARCHAR(256),
  PRIMARY KEY (id)
)
DATASOURCE_TYPE ('adp')
AS SELECT * FROM (
  SELECT *, 0 AS sys_op 
  FROM marketing.sales
    FOR SYSTEM_TIME STARTED CN (:marketing_sales_cn1, :marketing_sales_cn2)
  UNION ALL
  SELECT *, 1 AS sys_op 
  FROM marketing.sales 
    FOR SYSTEM_TIME FINISHED CN (:marketing_sales_cn1, :marketing_sales_cn2)
) AS sales_query

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

CREATE MATERIALIZED VIEW matview_db.addresses_incremental_matview (
  id BIGINT NOT NULL, 
  street_line VARCHAR(256), 
  additional_street_line VARCHAR(256),
  city VARCHAR(256), 
  postal_code VARCHAR(20), 
  region VARCHAR(256), 
  country VARCHAR(256),
  PRIMARY KEY (id, region)
)
DATASOURCE_TYPE ('adp', 'adp2')
AS SELECT * FROM (
  SELECT 
    ad.id, 
    ad.street_line, 
    ad.additional_street_line, 
    ad.city, 
    ad.postal_code, 
    re.name, 
    re.country
  FROM marketing.addresses FOR SYSTEM_TIME AS OF CN :marketing_addresses_cn2 AS ad
  JOIN marketing.regions 
    FOR SYSTEM_TIME STARTED CN (:marketing_regions_cn1, :marketing_regions_cn2) AS re
    ON ad.region_id = re.id
  UNION ALL
  SELECT 
    ad.id, 
    ad.street_line, 
    ad.additional_street_line, 
    ad.city, 
    ad.postal_code, 
    re.name, 
    re.country
  FROM marketing.addresses 
    FOR SYSTEM_TIME STARTED CN (:marketing_addresses_cn1, :marketing_addresses_cn2) AS ad
  JOIN marketing.regions FOR SYSTEM_TIME AS OF CN :marketing_regions_cn2 AS re
    ON ad.region_id = re.id
) AS addresses_query

Создание представления на основе инкрементального подзапроса, выбирающего все изменения (новые, обновленные и удаленные записи) из соединения двух таблиц:

CREATE MATERIALIZED VIEW matview_db.sales_with_store_address_incremental_matview (
  id BIGINT NOT NULL,
  transaction_date TIMESTAMP(6),
  product_code VARCHAR(256),
  product_units BIGINT,
  store_id BIGINT,
  store_address VARCHAR(256),
  PRIMARY KEY (id)
)
DATASOURCE_TYPE ('adp')
AS SELECT * FROM (
  SELECT 
    id, 
    transaction_date, 
    product_code, 
    product_units, 
    store_id, 
    region || ', ' || address, 
    0 AS sys_op 
  FROM (
    (SELECT * FROM marketing.sales FOR SYSTEM_TIME AS OF CN :marketing_sales_cn2 AS sa
    JOIN marketing.stores FOR SYSTEM_TIME AS OF CN :marketing_stores_cn2 AS st
      ON sa.store_id = st.id)
    EXCEPT
    (SELECT * FROM marketing.sales FOR SYSTEM_TIME AS OF CN :marketing_sales_cn0 AS sa
    JOIN marketing.stores FOR SYSTEM_TIME AS OF CN :marketing_stores_cn0 AS st
      ON sa.store_id = st.id)
  ) AS added_updated_records
  UNION ALL
  SELECT 
    id, 
    transaction_date, 
    product_code, 
    product_units, 
    store_id, 
    region || ', ' || address, 
    1 AS sys_op 
  FROM (
    (SELECT * FROM marketing.sales FOR SYSTEM_TIME AS OF CN :marketing_sales_cn0 AS sa
    JOIN marketing.stores FOR SYSTEM_TIME AS OF CN :marketing_stores_cn0 AS st
      ON sa.store_id = st.id)
    EXCEPT
    (SELECT * FROM marketing.sales FOR SYSTEM_TIME AS OF CN :marketing_sales_cn2 AS sa
    JOIN marketing.stores FOR SYSTEM_TIME AS OF CN :marketing_stores_cn2 AS st
      ON sa.store_id = st.id)
  ) AS deleted_records
) AS sales_stores_query;