CREATE MATERIALIZED VIEW

Содержание раздела
  1. Синтаксис
    1. Ключевое слово DATASOURCE_TYPE
    2. Ключевое слово LOGICAL_ONLY
  2. Ограничения
    1. Ограничения выполнения
    2. Ограничения СУБД
    3. Ограничения имен
    4. Ограничения столбцов
    5. Ограничения SELECT-подзапроса
    6. Другие ограничения
  3. Примеры
    1. Представление на основе одной таблицы с условием
    2. Представление на основе одной таблицы с условием, агрегацией и группировкой
    3. Представление на основе двух таблиц
    4. Представление только на логическом уровне
    5. Представление на основе данных из двух логических баз данных

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

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

Данные представлений можно размещать в ADG и (или) ADQM. Источником данных для представлений служит ADB.

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

Запрос попадает в очередь операций и обрабатывается в порядке его поступления в очередь. В ответе возвращается:

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

При успешном выполнении запроса система создает материализованное представление, а также подготавливает хранилище к размещению данных представления — создает физические таблицы, связанные с материализованным представлением и предназначенные для хранения его данных. Физические таблицы создаются в тех СУБД хранилища, которые указаны в запросе.

В отличие от запроса на создание логической таблицы, запрос CREATE MATERIALIZED VIEW должен содержать ключевое слово DATASOURCE_TYPE со списком СУБД для размещения данных представления. Требование связано с тем, что данные представлений (в отличие от данных логических таблиц) могут размещаться только в ADG и ADQM, а не во всех СУБД хранилища.

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

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

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

Ошибка выполнения корректного запроса блокирует исполнение последующих запросов в этой логической базе данных. В случае блокировки нужно устранить причины сбоя и повторить сбойную операцию. Если повторение операции невозможно, удалите сбойную операцию запросом ERASE_CHANGE_OPERATION и вручную устраните несоответствия на физическом уровне (если такие есть).

Каждое создание представления записывается в журнал. Журнал можно посмотреть с помощью запроса GET_CHANGES.

Синтаксис 

CREATE MATERIALIZED VIEW [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

Список псевдонимов СУБД хранилища, в которых нужно разместить данные представления. Элементы списка перечисляются через запятую. Возможные значения: adqm, adg. Значения можно указывать без кавычек (например, adg) или двойных кавычках (например, "adg").

query

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

origin_datasource_alias

Псевдоним СУБД, которая служит источником данных. Возможные значения: 'adb'. Значение указывается в одинарных кавычках.

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

Ключевое слово DATASOURCE_TYPE позволяет указать СУБД хранилища, в которых необходимо размещать данные материализованного представления. В текущей версии данные представления могут размещаться в ADG и (или) в ADQM.

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

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

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

Ограничения

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

  • Выполнение запроса недоступно при наличии любого из факторов:
    • горячей дельты,
    • запрета на изменение сущностей (см. раздел DENY_CHANGES).
  • Запрос не может быть выполнен в сервисной базе данных INFORMATION_SCHEMA.

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

  • Данные материализованных представлений не могут размещаться в ADB и ADP.
  • Источником данных для материализованного представления не могут быть ADQM, ADG и ADP.

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

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

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

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

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

Чтобы убедиться в корректности SELECT-подзапроса, рекомендуется выполнить его как самостоятельный запрос до создания представления.
Также после создания представления можно проверить статус его синхронизации с помощью запроса CHECK_MATERIALIZED_VIEW. Если представление успешно синхронизировалось, это означает, что SELECT-подзапрос был указан корректно.

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

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

Примеры

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

Создание представления с размещением в ADG и ADQM:

CREATE MATERIALIZED VIEW marketing.sales_december_2020 (
  id INT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units INT NOT NULL,
  store_id INT 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-12-01' AND '2020-12-31'
DATASOURCE_TYPE = 'adb'

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

Создание представления с размещением в ADQM:

CREATE MATERIALIZED VIEW marketing.sales_by_stores (
  store_id INT NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units INT NOT NULL,
  PRIMARY KEY (store_id, product_code)
)
DISTRIBUTED BY (store_id)
DATASOURCE_TYPE (adqm)
AS SELECT store_id, product_code, SUM(product_units) as product_units FROM marketing.sales
   WHERE product_code <> 'ABC0001'
   GROUP BY store_id, product_code
DATASOURCE_TYPE = 'adb'

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

CREATE MATERIALIZED VIEW marketing.sales_and_stores (
  id INT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units INT NOT NULL,
  description VARCHAR(256),
  store_id INT NOT NULL,
  store_category VARCHAR(256) NOT NULL,
  region VARCHAR(256) NOT NULL,
  PRIMARY KEY (id, region)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE (adg)
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 = 'adb'

Представление только на логическом уровне 

CREATE MATERIALIZED VIEW marketing.stores_by_sold_products_matview (
  store_id INT NOT NULL,
  product_amount INT 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 INT,
  category VARCHAR(256),
  product_units INT,
  PRIMARY KEY (store_id)
)
DISTRIBUTED BY (store_id)
DATASOURCE_TYPE (adg, adqm)
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'