CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

  • Artigo

Aplica-se a:Azure Synapse Analytics

Este artigo explica a instrução CREATE MATERIALIZED VIEW AS SELECT T-SQL no Azure Synapse Analytics para o desenvolvimento de soluções. O artigo também fornece exemplos de códigos.

Uma Exibição Materializada persiste os dados retornados da consulta de definição de exibição e é atualizada automaticamente conforme os dados são alterados nas tabelas subjacentes. Ela melhora o desempenho de consultas complexas (normalmente consultas com junções e agregações) e oferece operações simples de manutenção. Com seu recurso de correspondência automática do plano de execução, uma exibição materializada não precisa ser referenciada na consulta para o otimizador considerar a exibição na substituição. Essa capacidade permite que os engenheiros de dados implementem as exibições materializadas como um mecanismo para melhorar o tempo de resposta de consulta sem precisar alterar as consultas.

Convenções de sintaxe de Transact-SQL

Syntax

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

Observação

Não há suporte a essa sintaxe para o pool de SQL sem servidor no Azure Synapse Analytics.

Argumentos

schema_name

É o nome do esquema ao qual a exibição pertence.

materialized_view_name

É o nome da exibição. Os nomes de exibição devem seguir as regras para identificadores. A especificação do nome do proprietário da exibição é opcional.

opções de distribuição

Somente distribuições de HASH e ROUND_ROBIN são compatíveis. Para saber mais sobre opções de distribuição, confira as opções de distribuição de tabela CREATE TABLE. Para obter recomendações sobre qual distribuição escolher para uma tabela com base no uso real ou em consultas de exemplo, confira o Assistente de Distribuição no SQL do Azure Synapse.

DISTRIBUTION = HASH ( distribution_column_name )
Distribui as linhas com base nos valores de uma única coluna.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Distribui as linhas com base nos valores de hash de até oito colunas, o que permite uma distribuição mais uniforme dos dados da exibição materializada, reduz a distorção de dados ao longo do tempo e melhora o desempenho da consulta.

Observação

  • Para habilitar o recurso Distribuição de Várias Colunas, altere o nível de compatibilidade do banco de dados para 50 com esse comando. Para obter mais informações sobre como definir o nível de compatibilidade do banco de dados, confira ALTERAR A CONFIGURAÇÃO DO ESCOPO DO BANCO DE DADOS. Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Para desabilitar do MCD, execute o comando a seguir e altere o nível de compatibilidade do banco de dados para AUTO. Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; As exibições materializadas do MCD existentes serão mantidas, mas ficarão ilegíveis.
    • Para recuperar o acesso às exibições materializadas do MCD, habilite o recurso novamente.

select_statement

A lista SELECT na definição de exibição materializada precisa cumprir ao menos um desses dois critérios:

  • A lista SELECT contém uma função de agregação.
  • GROUP BY é usada na definição da Exibição materializada e todas as colunas em GROUP BY são incluídas na lista SELECT. Até 32 colunas podem ser usadas na cláusula GROUP BY.

As funções de agregação são necessárias na lista SELECT da definição da exibição materializada. As agregações com suporte incluem MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Quando as agregações MIN/MAX são usadas na lista SELECT da definição da exibição materializada, os seguintes requisitos se aplicam:

  • FOR_APPEND é obrigatório. Por exemplo:

    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

  • A exibição materializada será desabilitada quando UPDATE ou DELETE ocorrerem nas tabelas base referenciadas.  Essa restrição não se aplica a INSERTs.  Para habilitar novamente a exibição materializada, execute ALTER MATERIALIZED VIEW com REBUILD.

Comentários

Uma exibição materializada no data warehouse do Azure é semelhante a uma exibição indexada no SQL Server.  Ela compartilha quase as mesmas restrições que a exibição indexada (confira Criar exibições indexadas para obter detalhes), exceto pelo fato de que uma exibição materializada dá suporte a funções de agregação.  

Observação

Embora CREATE MATERIALIZED VIEW não dê suporte a COUNT, DISTINCT, COUNT(DISTINCT expression) ou COUNT_BIG (DISTINCT expression), as consultas SELECT com essas funções ainda podem se beneficiar das exibições materializadas para obter um desempenho mais rápido, uma vez que o otimizador do SQL do Synapse pode regravar automaticamente essas agregações na consulta do usuário para corresponder às exibições materializadas existentes. Para obter detalhes, confira a seção de exemplo deste artigo.

Não há suporte para APPROX_COUNT_DISTINCT em CREATE MATERIALIZED VIEW AS SELECT.

Somente o CLUSTERED COLUMNSTORE INDEX é compatível com a exibição materializada.

Uma exibição materializada não pode referenciar outras exibições.

Uma exibição materializada não pode ser criada em uma tabela DDM (máscara de dados dinâmicos), mesmo que a coluna DDM não faça parte da exibição materializada. Se uma coluna de tabela fizer parte de uma exibição materializada ativa ou de uma exibição materializada desabilitada, o DDM não poderá ser adicionado a essa coluna.

Uma exibição materializada não pode ser criada em uma tabela com segurança em nível de linha habilitada.

Só é possível criar Exibições Materializadas em tabela particionadas.  Há suporte para a partição SPLIT/MERGE em tabelas base de exibições materializadas; não há suporte para a partição SWITCH.

ALTER TABLE SWITCH não tem suporte em tabelas referenciadas em exibições materializadas. Desabilite ou descarte as exibições materializadas antes de usar ALTER TABLE SWITCH. Nos cenários a seguir, a criação de exibições materializadas requer novas colunas a adicionar à exibição materializada:

Cenário Novas colunas a adicionar à exibição materializada Comentário
COUNT_BIG() está ausente na lista SELECT de uma definição de exibição materializada COUNT_BIG (*) Adicionado automaticamente pela criação da exibição materializada. Não é necessária nenhuma ação do usuário.
SUM(a) é especificado por usuários na lista SELECT de uma definição de exibição materializada E 'a' é uma expressão anulável COUNT_BIG (a) Os usuários precisam adicionar a expressão 'a' manualmente na definição de exibição materializada.
AVG(a) é especificado por usuários na lista SELECT de uma definição de exibição materializada, em que 'a' é uma expressão. SUM(a), COUNT_BIG(a) Adicionado automaticamente pela criação da exibição materializada. Não é necessária nenhuma ação do usuário.
STDEV(a) é especificado por usuários na lista SELECT de uma definição de exibição materializada, em que 'a' é uma expressão. SUM(a), COUNT_BIG(a), SUM(square(a)) Adicionado automaticamente pela criação da exibição materializada. Não é necessária nenhuma ação do usuário.

Depois de criadas, as exibições materializadas ficam visíveis no SQL Server Management Studio na pasta de exibições da instância do Azure Synapse Analytics.

Os usuários podem executar SP_SPACEUSED e DBCC PDW_SHOWSPACEUSED para determinar o espaço que está sendo consumido por uma exibição materializada. Também há DMVs para fornecer consultas mais personalizáveis para identificar o espaço e as linhas consumidas. Para obter mais informações, consulte Consultas de tamanho de tabela.

Uma exibição materializada pode ser descartada por meio de DROP VIEW. Você pode usar ALTER MATERIALIZED VIEW para desabilitar ou recriar uma exibição materializada.

A exibição materializada é um mecanismo de otimização de consulta automático. Os usuários não precisam consultar uma exibição materializada diretamente. Quando uma consulta do usuário é enviada, o mecanismo verifica as permissões do usuário para os objetos de consulta e a interrompe sem execução se o usuário não tiver acesso às tabelas ou às exibições regulares na consulta. Se a permissão do usuário foi verificada, o otimizador usa automaticamente uma exibição materializada correspondente para executar a consulta e ter um desempenho mais rápido. Os usuários obtêm os mesmos dados de volta, independentemente de a consulta ser atendida por meio da consulta às tabelas base ou à exibição materializada.

O plano EXPLAIN e o Plano de Execução Estimada gráfico no SQL Server Management Studio podem mostrar se uma exibição materializada é considerada pelo otimizador de consulta para execução de consulta, e o Plano de Execução Estimado gráfico no SQL Server Management Studio pode mostrar se uma exibição materializada é considerada pelo otimizador de consulta para execução de consulta.

Para descobrir se uma instrução SQL pode se beneficiar da nova exibição materializada, execute o comando EXPLAIN com WITH_RECOMMENDATIONS. Para obter detalhes, confira EXPLAIN (Transact-SQL).

Propriedade

  • Uma exibição materializada não poderá ser criada se os proprietários das tabelas base e a exibição materializada a ser criada não forem iguais.
  • Uma exibição materializada e suas tabelas base podem residir em esquemas distintos. Quando a exibição materializada é criada, o proprietário do esquema da exibição se torna automaticamente o proprietário da exibição materializada e essa propriedade de exibição não pode ser alterada.

Permissões

Um usuário precisa das seguintes permissões para criar uma exibição materializada, além de atender aos requisitos de propriedade do objeto:

  1. Permissão CREATE VIEW no banco de dados
  2. Permissão SELECT nas tabelas base da exibição materializada
  3. Permissão REFERENCEs no esquema que contém as tabelas base
  4. Permissão ALTER no esquema que contém a exibição materializada

Exemplo

a. Este exemplo mostra como o otimizador do SQL do Synapse usa automaticamente exibições materializadas para executar uma consulta para obter melhor desempenho, mesmo quando a consulta usar funções sem suporte em CREATE MATERIALIZED VIEW, como COUNT(DISTINCT expression). Uma consulta que demorava vários segundos para ser concluída agora é concluída em milissegundos sem qualquer alteração na consulta do usuário.


-- 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. Neste exemplo, o Usuário2 cria uma exibição materializada em tabelas pertencentes ao Usuário1. A exibição materializada pertence ao Usuário1.

/****************************************************************
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

Confira também

Próximas etapas