CREATE MATERIALIZED VIEW

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

Поддерживается в версиях:  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, а данные представления — в датасорсе типа ADP, ADG и (или) ADQM,
  • источник данных расположен в датасорсе типа 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_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]

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

Параметры:

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

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

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

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

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

Ограничения

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

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

  • Данные материализованных представлений могут размещаться в СУБД ADP, ADQM и ADG.
  • Источником данных для материализованного представления может быть СУБД 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'

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

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