Partilhar via

CRIAR VISUALIZAÇÃO MATERIALIZADA COMO SELECT (Transact-SQL)

Aplica-se a:Azure Synapse Analytics

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

Uma Visualização Materializada mantém os dados devolvidos da consulta de definição de vista e é automaticamente atualizada à medida que os dados mudam nas tabelas subjacentes. Melhora o desempenho de consultas complexas (tipicamente consultas com joins e agregações) enquanto oferece operações de manutenção simples. Com a sua capacidade de auto-correspondência de planos de execução, uma vista materializada não precisa de ser referenciada na consulta para que o otimizador considere a vista para substituição. Esta capacidade permite aos engenheiros de dados implementar vistas materializadas como mecanismo para melhorar o tempo de resposta às consultas, sem necessidade de alterar as consultas.

Transact-SQL convenções de sintaxe

Sintaxe

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

Essa sintaxe não é suportada pelo pool SQL sem servidor no Azure Synapse Analytics.

Arguments

schema_name

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

materialized_view_name

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

Opção de distribuição

Apenas as distribuições HASH e ROUND_ROBIN são suportadas. Para mais informações sobre opções de distribuição, consulte as opções de distribuição CREATE TABLE Table. Para recomendações sobre que distribuição escolher para uma tabela com base no uso real ou consultas de exemplo, consulte Distribution Advisor no Azure Synapse SQL.

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, permitindo uma distribuição mais uniforme dos dados materializados, reduzindo o desvio de dados ao longo do tempo e melhorando o desempenho das consultas.

Observação

  • Para ativar a funcionalidade Multi-Column Distribution, altere o nível de compatibilidade da base de dados para 50 com este comando. Para mais informações sobre a definição do nível de compatibilidade da base de dados, consulte ALTERAR CONFIGURAÇÃO COM ÂMBITO DE BASE DE DADOS. Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Para desativar o MCD, execute este comando para alterar o nível de compatibilidade da base de dados para AUTO. Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; As visões existentes materializadas da MCD permanecerão, mas tornar-se-ão ilegíveis.
    • Para recuperar o acesso às visualizações materializadas do MCD, ative novamente a funcionalidade.

select_statement

A lista SELECT na definição da vista materializada precisa de cumprir pelo menos um destes dois critérios:

  • A lista SELECT contém uma função agregada.
  • GROUP BY é usado na definição da vista materializada e todas as colunas em GROUP BY estão incluídas na lista SELECT. Podem ser usadas até 32 colunas na cláusula GROUP BY.

As funções agregadas são exigidas na lista SELECT da definição da vista materializada. Agregações suportadas incluem MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Quando os agregados MIN/MAX são usados na lista SELECT de definições de vistas materializadas, aplicam-se os seguintes requisitos:

  • 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 visualização materializada será desativada quando ocorrer uma ATUALIZAÇÃO ou DELETE nas tabelas base referenciadas.  Esta restrição não se aplica aos INSERTs.  Para reativar a visualização materializada, execute ALTER MATERIALIZED VIEW com REBUILD.

Observações

Uma visualização materializada no Azure data warehouse é semelhante a uma visualização indexada no SQL Server.  Partilha quase as mesmas restrições que a visualização indexada (ver Criar Vistas Indexadas para detalhes), exceto que uma visualização materializada suporta funções agregadas.  

Observação

Embora a CREATE MATERIALIZED VIEW não suporte COUNT, DISTINCT, COUNT (expressão distinta) ou COUNT_BIG (expressão DISTINCT), as consultas SELECT com estas funções ainda podem beneficiar de visualizações materializadas para um desempenho mais rápido, pois o otimizador SQL da Synapse pode reescrever automaticamente essas agregações na consulta do utilizador para corresponder às visualizações materializadas existentes. Para mais detalhes, consulte a secção de exemplos deste artigo.

APPROX_COUNT_DISTINCT não é suportado em CRIAR VISUALIZAÇÃO MATERIALIZADA COMO SELECT.

Apenas o CLUSTERED COLUMNSTORE INDEX é suportado pela visualização materializada.

Uma visão materializada não pode referenciar outras opiniões.

Uma vista materializada não pode ser criada numa tabela com mascaramento dinâmico de dados (DDM), mesmo que a coluna DDM não faça parte da visualização materializada. Se uma coluna de tabela fizer parte de uma visualização materializada ativa ou de uma visualização materializada desativada, o DDM não pode ser adicionado a essa coluna.

Uma vista materializada não pode ser criada numa tabela com segurança ao nível da linha ativada.

As Vistas Materializadas podem ser criadas em tabelas particionadas.  Partition SPLIT/MERGE são suportados em tabelas base de vistas materializadas, o partition SWITCH não é suportado.

ALTER TABLE SWITCH não é suportado em tabelas referenciadas em vistas materializadas. Desative ou elimine as vistas materializadas antes de usar ALTERAR TABLE SWITCH. Nos cenários seguintes, a criação da vista materializada requer a adição de novas colunas à visualização materializada:

Scenario Novas colunas para acrescentar à vista materializada Comment
COUNT_BIG() está em falta na lista SELECT de uma definição de vista materializada COUNT_BIG (*) Adicionado automaticamente pela criação de visualização materializada. Nenhuma ação do usuário é necessária.
SUM(a) é especificado pelos utilizadores na lista SELECT de uma definição de vista materializada E 'a' é uma expressão anulável COUNT_BIG (a) Os utilizadores precisam de adicionar manualmente a expressão 'a' na definição da vista materializada.
O AVG(a) é especificado pelos utilizadores na lista SELECT de uma definição de vista materializada onde 'a' é uma expressão. SUM(a), COUNT_BIG(a) Adicionado automaticamente pela criação de visualização materializada. Nenhuma ação do usuário é necessária.
STDEV(a) é especificado pelos utilizadores na lista SELECT de uma definição de vista materializada onde 'a' é uma expressão. SUM(a), COUNT_BIG(a), SUM(quadrado(a)) Adicionado automaticamente pela criação de visualização materializada. Nenhuma ação do usuário é necessária.

Uma vez criadas, as vistas materializadas são visíveis no SQL Server Management Studio na pasta views da instância Azure Synapse Analytics.

Os utilizadores podem executar PDW_SHOWSPACEUSED SP_SPACEUSED e DBCC para determinar o espaço ocupado por uma vista materializada. Existem também DMVs para fornecer consultas mais personalizáveis para identificar o espaço e as linhas ocupadas. Para mais informações, consulte consultas sobre o tamanho da tabela.

Uma visualização materializada pode ser descartada através do DROP VIEW. Pode usar ALTERAR VISUALIZAÇÃO MATERIALIZADA para desativar ou reconstruir uma visualização materializada.

A visualização materializada é um mecanismo automático de otimização de consultas. Os utilizadores não precisam de consultar diretamente uma vista materializada. Quando uma consulta de utilizador é submetida, o motor verifica as permissões do utilizador para os objetos de consulta e falha a consulta sem execução se o utilizador não tiver acesso às tabelas ou às vistas normais da consulta. Se a permissão do utilizador tiver sido verificada, o otimizador utiliza automaticamente uma vista materializada correspondente para executar a consulta e garantir um desempenho mais rápido. Os utilizadores recuperam os mesmos dados, independentemente de a consulta ser servida através das consultas às tabelas base ou à vista materializada.

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

Para saber se uma instrução SQL pode beneficiar de uma nova visualização materializada, execute o EXPLAIN comando com WITH_RECOMMENDATIONS. Para mais detalhes, veja EXPLAIN (Transact-SQL).

Propriedade

  • Uma vista materializada não pode ser criada se os proprietários das mesas base e a visualização materializada to-be-criada não forem os mesmos.
  • Uma vista materializada e as suas tabelas base podem residir em diferentes esquemas. Quando a visualização materializada é criada, o proprietário do esquema da vista torna-se automaticamente o proprietário da visualização materializada e essa propriedade da vista não pode ser alterada.

Permissions

Um utilizador precisa das seguintes permissões para criar uma vista materializada, além de cumprir os requisitos de propriedade do objeto:

  1. Permissão CREATE VIEW na base de dados
  2. Permissão SELECT nas tabelas base da vista materializada
  3. REFERÊNCIAS permissão no esquema que contém as tabelas base
  4. Permissão ALTER sobre o esquema que contém a visualização materializada

Example

A. Este exemplo mostra como o otimizador Synapse SQL utiliza automaticamente vistas materializadas para executar uma consulta para melhor desempenho, mesmo quando a consulta utiliza funções não suportadas na CREATE MATERIALIZED VIEW, como COUNT(DISTINCT expression). Uma consulta que antes demorava vários segundos a ser concluída agora termina em menos de segundo sem qualquer alteração na consulta do utilizador.


-- 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 Utilizador2 cria uma vista materializada em tabelas pertencentes ao Utilizador1. A visualização materializada pertence ao Utilizador 1.

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

Consulte também

Próximos passos

  • Last updated on