CREATE MATERIALIZED VIEW

Содержание раздела
  1. Поддерживаемые датасорсы
  2. Представления на основе прокси-таблиц и standalone-таблиц
  3. Результат выполнения
  4. Как работает запрос
  5. Синтаксис
    1. Ключевое слово IF NOT EXISTS
    2. Ключевое слово PRIMARY KEY (column_name[, ... ])
    3. Ключевое слово DISTRIBUTED BY (column_name[, ... ])
    4. Ключевое слово DATASOURCE_TYPE ('destination_datasource'[, ... ])
    5. Ключевое слово AS SELECT query
    6. Ключевое слово DATASOURCE_TYPE = 'source_datasource'
    7. Ключевое слово LOGICAL_ONLY
  6. Варианты ответа
  7. Ограничения
    1. Ограничения выполнения
    2. Ограничения СУБД
    3. Ограничения имен
    4. Ограничения столбцов
    5. Ограничения SELECT-подзапроса
    6. Другие ограничения
  8. Примеры
    1. Представление на основе одной таблицы с условием
    2. Представление с системными столбцами таблицы-источника
    3. Представление на основе одной таблицы с условием, агрегацией и группировкой
    4. Представление на основе двух таблиц
    5. Представление только на логическом уровне
    6. Представление на основе данных из двух логических баз данных

Поддерживается в версиях: 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.

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

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

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

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

Для материализованных представлений поддерживаются следующие типы датасорсов в качестве источника (отмечен слева от стрелки) и приемника (отмечен справа от стрелки):

  • источник и приемник в одном датасорсе:
    • ADB → ADB,
    • ADP → ADP,
    • ADG → ADG,
  • источник и приемник в разных датасорсах:
    • ADB → ADP, ADQM и (или) ADG,
    • ADP → ADP и (или) ADG.

Датасорсы-приемники можно комбинировать в материализованном представлении с учетом поддерживаемых типов: например, можно создать представление на основе данных датасорса типа ADB в датасорсах типа ADP и ADQM.

Представления на основе прокси-таблиц и 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
[DATASOURCE_TYPE = 'source_datasource']
[LOGICAL_ONLY]

При использовании ограничения NOT 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[, ... ])

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ограничения

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

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

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

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

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

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

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

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

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

Чтобы проверить корректность 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'

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

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

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

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

Создание представления с источником в датасорсе типа 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'