CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

Область применения: Azure Synapse Analytics

В этой статье описывается инструкция CREATE MATERIALIZED VIEW AS SELECT T-SQL в Azure Synapse Analytics для разработки решений. Здесь также приведены примеры кодов.

Материализованное представление хранит данные, возвращенные запросом определения представления, и автоматически обновляется после изменений данных в базовых таблицах. Это повышает производительность сложных запросов (обычно запросы с объединениями и агрегатами), а также упрощает обслуживание. Благодаря возможности автоматического сопоставления плана выполнения материализованное представление не нужно указывать в запросе, чтобы оптимизатор учитывал его при подстановке. Эта возможность позволяет специалистам по обработке данных реализовать материализованные представления в виде механизма повышения времени отклика запроса без необходимости изменять запросы.

Соглашения о синтаксисе Transact-SQL

Синтаксис

CREATE MATERIALIZED VIEW [ schema_name. ] materialized_view_name
    WITH (  
      <distribution_option>
    )
    AS <select_statement>
[;]

<distribution_option> ::=
    {  
        DISTRIBUTION = HASH ( distribution_column_name )  
      | DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) 
      | DISTRIBUTION = ROUND_ROBIN  
    }

<select_statement> ::=
    SELECT select_criteria

Примечание.

Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Аргументы

schema_name

Имя схемы, которой принадлежит представление.

materialized_view_name

Имя представления. Имена представлений должны соответствовать требованиям, предъявляемым к идентификаторам. Указывать имя владельца представления не обязательно.

distribution option

Поддерживаются только распределения HASH и ROUND_ROBIN. Дополнительные сведения о параметрах распространения см. в разделе CREATE TABLE: параметры распространения таблицы. Рекомендации по выбору распределения для таблицы на основе фактического использования или примеры запросов см. в статье Помощник по распространению в Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name)
Распределяет строки на основе значений одного столбца.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Распределяет строки на основе хэш-значений до восьми столбцов, что позволяет более равномерно распределять материализованные данные представления, уменьшая количество данных с течением времени и повышая производительность запросов.

Примечание.

• Чтобы включить функцию распределения с несколькими столбцами, измените уровень совместимости базы данных на 50 с помощью этой команды. Дополнительные сведения о настройке уровня совместимости базы данных см. в разделе ALTER DATABASE SCOPED CONFIGURATION. Например: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Чтобы отключить MCD, выполните эту команду, чтобы изменить уровень совместимости базы данных на AUTO. Например: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; существующие материализованные представления MCD останутся нечитаемыми.
  • Чтобы восстановить доступ к материализованным представлениям MCD, снова включите эту функцию.

select_statement

Список SELECT в определении материализованного представления должен соответствовать как минимум одному из следующих двух условий:

• Список SELECT содержит агрегатную функцию.
• В определении материализованного представления используется предложение GROUP BY, и все столбцы в предложении GROUP BY включены в список SELECT. В предложении GROUP BY можно использовать до 32 столбцов.

В списке SELECT определения материализованного представления должны использоваться агрегатные функции. Поддерживаемые агрегаты: MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Если в списке SELECT определения материализованного представления используются агрегаты MIN или MAX, применяются следующие требования:

• FOR_APPEND является обязательным. Например:

  CREATE MATERIALIZED VIEW mv_test2  
  WITH (distribution = hash(i_category_id), FOR_APPEND)  
  AS
  SELECT MAX(i.i_rec_start_date) as max_i_rec_start_date, MIN(i.i_rec_end_date) as min_i_rec_end_date, i.i_item_sk, i.i_item_id, i.i_category_id
  FROM syntheticworkload.item i  
  GROUP BY i.i_item_sk, i.i_item_id, i.i_category_id
  

• Если в связанных базовых таблицах используется инструкция UPDATE или DELETE, материализованное представление отключается.  Это ограничение не относится к инструкциям INSERT.  Чтобы повторно включить материализованное представление, выполните инструкцию ALTER MATERIALIZED VIEW с параметром REBUILD.

Замечания

Материализованное представление в хранилище данных Azure похоже на индексированное представление SQL Server.  Оно имеет практически те же ограничения, что и индексированное представление (подробности см. в статье Создание индексированных представлений) за исключением того, что материализованное представление поддерживает агрегатные функции.  

Примечание.

Несмотря на то, что CREATE MATERIALIZED VIEW не поддерживает COUNT, DISTINCT, COUNT (выражение DISTINCT) и COUNT_BIG (выражение DISTINCT), запросы SELECT с этими функциями могут по-прежнему пользоваться преимуществами материализованных представлений для повышения производительности, так как оптимизатор Synapse SQL может автоматически перезаписывать эти агрегаты в пользовательском запросе для сопоставления с существующими материализованными представлениями. Дополнительные сведения см. в разделе с примером этой статьи.

APPROX_COUNT_DISTINCT не поддерживается в CREATE MATERIALIZED VIEW AS SELECT.

Материализованное представление поддерживает только кластеризованный индекс columnstore.

Материализованное представление не может ссылаться на другие представления.

Материализованные представления невозможно создать для таблицы с динамическим маскированием данных (DDM), даже если столбец DDM не является частью материализованного представления. Если столбец таблицы является частью активного или отключенного материализованного представления, DDM невозможно добавить в этот столбец.

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

Материализованные представления можно создавать в секционированных таблицах.  Операции SPLIT и MERGE для секций поддерживаются для базовых таблиц материализованных представлений. Переключение (SWITCH) секций не поддерживается.

Таблицы, связанные с материализованными представлениями, не поддерживают инструкцию ALTER TABLE SWITCH. Перед использованием инструкции ALTER TABLE SWITCH отключите или удалите все материализованные представления. Ниже приведены сценарии, в которых для создания материализованного представления в него нужно добавить новые столбцы.

Сценарий	Новые столбцы, добавляемые к материализованному представлению	Комментарий
COUNT_BIG() отсутствует в списке SELECT определения материализованного представления	COUNT_BIG (*)	Автоматически добавляется во время создания материализованного представления. Вмешательство пользователя не требуется.
Пользователь указал функцию SUM(a) в списке SELECT определения материализованного представления, где "a" — это выражение, допускающее значение NULL	COUNT_BIG (a)	Пользователям необходимо добавить выражение "a" вручную в определении материализованного представления.
Пользователь указал функцию AVG(a) в списке SELECT определения материализованного представления, где "a" — это выражение.	SUM(a), COUNT_BIG(a)	Автоматически добавляется во время создания материализованного представления. Вмешательство пользователя не требуется.
Пользователь указал функцию STDEV(a) в списке SELECT определения материализованного представления, где "a" — это выражение.	SUM(a), COUNT_BIG(a), SUM(square(a))	Автоматически добавляется во время создания материализованного представления. Вмешательство пользователя не требуется.

После создания материализованные представления отображаются в SQL Server Management Studio в папке представлений экземпляра Azure Synapse Analytics.

Пользователи могут определить место, используемое материализованным представлением, с помощью инструкций SP_SPACEUSED и DBCC PDW_SHOWSPACEUSED. Существуют также динамические административные представления для предоставления более настраиваемых запросов для идентификации потребляемых пространства и строк. Дополнительные сведения см. в разделе Запросы размера таблицы.

Материализованное представление можно удалить с помощью инструкции DROP VIEW. Отключить или перестроить материализованное представление можно с помощью инструкции ALTER MATERIALIZED VIEW.

Материализованное представление — это автоматический механизм оптимизации запросов. Пользователям не требуется запрашивать материализованное представление напрямую. При отправке пользовательского запроса подсистема проверяет разрешения пользователя на объекты запроса и завершает запрос без выполнения, если у пользователя нет доступа к таблицам или обычным представлениям в запросе. Если разрешение пользователя подтверждено, оптимизатор автоматически использует для выполнения запроса соответствующее материализованное представление в целях повышения производительности. Пользователи получают одинаковые данные независимо от того, обрабатываются ли запросы путем обращения к базовым таблицам или материализованному представлению.

План EXPLAIN и графический предполагаемый план выполнения в SQL Server Management Studio могут показать, считается ли материализованное представление оптимизатором запросов для выполнения запросов, а графический план предполагаемого выполнения в SQL Server Management Studio может показать, считается ли материализованное представление оптимизатором запросов для выполнения запроса.

Чтобы узнать, поддерживает ли инструкция SQL новое материализованное представление, выполните команду EXPLAIN со свойством WITH_RECOMMENDATIONS. Дополнительные сведения см. в статье EXPLAIN (Transact-SQL).

Тип собственности

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

Разрешения

Для создания материализованного представления пользователю, кроме соблюдения требований к владению объектами, нужны следующие разрешения.

1. Разрешение CREATE VIEW в базе данных
2. Разрешение SELECT на базовые таблицы материализованного представления
3. Разрешение REFERENCES на схему, содержащую базовые таблицы
4. Разрешение ALTER на схему, содержащую материализованное представление

Пример

А. В этом примере показано, как оптимизатор Synapse SQL автоматически использует материализованные представления при выполнении запроса для лучшей производительности, даже если в запросе используются функции, которые не поддерживаются в CREATE MATERIALIZED VIEW, такие как COUNT(DISTINCT expression). Запрос, который раньше выполнялся несколько секунд, теперь выполняется за долю секунды без каких-либо изменений в пользовательском запросе.

-- Create a table with ~536 million rows
create table t(a int not null, b int not null, c int not null) with (distribution=hash(a), clustered columnstore index);

insert into t values(1,1,1);

declare @p int =1;
while (@P < 30)
    begin
    insert into t select a+1,b+2,c+3 from t;  
    select @p +=1;
end

-- A SELECT query with COUNT_BIG (DISTINCT expression) took multiple seconds to complete and it reads data directly from the base table a. 
select a, count_big(distinct b) from t group by a;

-- Create two materialized views, not using COUNT_BIG(DISTINCT expression).
create materialized view V1 with(distribution=hash(a)) as select a, b from dbo.t group by a, b;

-- Clear all cache.

DBCC DROPCLEANBUFFERS;
DBCC freeproccache;

-- Check the estimated execution plan in SQL Server Management Studio.  It shows the SELECT query is first step (GET operator) is to read data from the materialized view V1, not from base table a.
select a, count_big(distinct b) from t group by a;

-- Now execute this SELECT query.  This time it took sub-second to complete because Synapse SQL engine automatically matches the query with materialized view V1 and uses it for faster query execution.  There was no change in the user query.

DECLARE @timerstart datetime2, @timerend datetime2;
SET @timerstart = sysdatetime();

select a, count_big(distinct b) from t group by a;

SET @timerend = sysdatetime()
select DATEDIFF(ms,@timerstart,@timerend);

B. В этом примере пользователь User2 создает материализованное представление в таблицах, принадлежащих пользователю User1. Материализованное представление принадлежит пользователю User1.

/****************************************************************
Setup:
SchemaX owner = DBO
SchemaX.T1 owner = User1
SchemaX.T2 owner = User1
SchemaY owner = User1
*****************************************************************/
CREATE USER User1 WITHOUT LOGIN ;
CREATE USER User2 WITHOUT LOGIN ;
GO
CREATE SCHEMA SchemaX;
GO
CREATE SCHEMA SchemaY AUTHORIZATION User1;
GO
CREATE TABLE [SchemaX].[T1] (    [vendorID] [varchar](255) Not NULL, [totalAmount] [float] Not NULL,    [puYear] [int] NULL );
CREATE TABLE [SchemaX].[T2] (    [vendorID] [varchar](255) Not NULL,    [totalAmount] [float] Not NULL,    [puYear] [int] NULL);
GO
ALTER AUTHORIZATION ON OBJECT::SchemaX.[T1] TO User1;
ALTER AUTHORIZATION ON OBJECT::SchemaX.[T2] TO User1;

/*****************************************************************************
For user2 to create a MV in SchemaY on SchemaX.T1 and SchemaX.T2, user2 needs:
1. CREATE VIEW permission in the database
2. REFERENCES permission on the schema1
3. SELECT permission on base table T1, T2  
4. ALTER permission on SchemaY
******************************************************************************/
GRANT CREATE VIEW to User2;
GRANT REFERENCES ON SCHEMA::SchemaX to User2;  
GRANT SELECT ON OBJECT::SchemaX.T1 to User2; 
GRANT SELECT ON OBJECT::SchemaX.T2 to User2;
GRANT ALTER ON SCHEMA::SchemaY to User2; 
GO
EXECUTE AS USER = 'User2';  
GO
CREATE materialized VIEW [SchemaY].MV_by_User2 with(distribution=round_robin) 
as 
        select A.vendorID, sum(A.totalamount) as S, Count_Big(*) as T 
        from [SchemaX].[T1] A
        inner join [SchemaX].[T2] B on A.vendorID = B.vendorID group by A.vendorID ;
GO
revert;
GO

См. также

• ALTER MATERIALIZED VIEW (Transact-SQL)
• DROP VIEW
• EXPLAIN (Transact-SQL)
• sys.pdw_materialized_view_column_distribution_properties (Transact-SQL)
• sys.pdw_materialized_view_distribution_properties (Transact-SQL)
• sys.pdw_materialized_view_mappings (Transact-SQL)
• DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL)
• Представления каталога Azure Synapse Analytics и Analytics Platform System (PDW)
• Системные представления, поддерживаемые в Azure Synapse Analytics
• Инструкции T-SQL, поддерживаемые в Azure Synapse Analytics

Следующие шаги

• Настройка производительности с помощью материализованного представления