CREATE MATERIALIZED VIEW

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

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

Поддерживается в версиях: 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 логических таблиц, и имеет ограничения, перечисленные ниже.

Ключевое слово `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 при успешном выполнении запроса;
исключение при неуспешном выполнении запроса.

Ограничения

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

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

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

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

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

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

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

Подзапрос не может содержать:
- системные представления INFORMATION_SCHEMA,
- ключевое слово FOR SYSTEM_TIME,
- ключевое слово 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;
')