VYTVOŘIT MATERIALIZOVANÝ POHLED JAKO SELECT (Transact-SQL)

platí pro:azure Synapse Analytics

Tento článek vysvětluje příkaz CREATE MATERIALIZED VIEW AS SELECT T-SQL v Azure Synapse Analytics pro vývoj řešení. Článek také uvádí příklady kódu.

Materializovaný pohled uchovává data vrácená z dotazu definice zobrazení a automaticky se aktualizuje s měněním dat v základních tabulkách. Zlepšuje výkon složitých dotazů (typicky dotazů s joiny a agregacemi) a zároveň nabízí jednoduché údržbové operace. Díky schopnosti automatického párování plánu vykonání nemusí být materializovaný pohled v dotazu uveden, aby optimalizátor mohl pohled zvážit pro substituci. Tato schopnost umožňuje datovým inženýrům implementovat materializované pohledy jako mechanismus pro zlepšení doby odpovědi na dotazy, aniž by bylo nutné dotazy měnit.

Transact-SQL konvence syntaxe

Syntaxe

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

Poznámka:

Tato syntaxe není podporována bezserverovým fondem SQL ve službě Azure Synapse Analytics.

Arguments

schema_name

Je název schématu, do kterého zobrazení patří.

materialized_view_name

Je název zobrazení. Názvy zobrazení musí dodržovat pravidla pro identifikátory. Zadání jména vlastníka zobrazení je volitelné.

Distribuční opce

Podporovány jsou pouze distribuce HASH a ROUND_ROBIN. Pro více informací o možnostech distribuce viz VYTVOŘIT TABULKU Možnosti rozdělení tabulky. Pro doporučení, kterou distribuci zvolit pro tabulku na základě skutečného využití nebo ukázkových dotazů, viz Distribution Advisor v Azure Synapse SQL.

DISTRIBUTION = HASH ( distribution_column_name )
Rozděluje řádky podle hodnot jednoho sloupce.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Rozděluje řádky na základě hashovacích hodnot až osmi sloupců, což umožňuje rovnoměrnější rozložení materializovaných dat z pohledů, snižuje časové zkreslení dat a zlepšuje výkon dotazů.

Poznámka:

• Pro povolení funkce Multi-Column Distribution změňte úroveň kompatibility databáze na 50 tímto příkazem. Pro více informací o nastavení úrovně kompatibility databáze viz ALTER DATABASE SCOPED CONFIGURATION. Příklad: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Pro deaktivaci MCD spusťte tento příkaz pro změnu úrovně kompatibility databáze na AUTO. Například: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Existující MCD materializované pohledy zůstanou, ale stanou se nečitelnými.
  • Pro znovuzískání přístupu k MCD materializovaným pohledům zapněte tuto funkci znovu.

select_statement

Seznam SELECT v definici materializovaného pohledu musí splňovat alespoň jedno z těchto dvou kritérií:

• Seznam SELECT obsahuje agregační funkci.
• GROUP BY se používá v definici zobrazení Materialized a všechny sloupce v GROUP BY jsou zahrnuty v seznamu SELECT. V klauzuli GROUP BY lze použít až 32 sloupců.

Agregované funkce jsou vyžadovány v seznamu SELECT definice materializovaného pohledu. Podporované agregace zahrnují MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Když jsou v seznamu SELECT v definici materializovaného pohledu použity agregáty MIN/MAX, platí následující požadavky:

• FOR_APPEND je povinné. Například:

  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
  

• Materializovaný pohled bude deaktivován, když dojde k AKTUALIZACI nebo DELETE v referenčních základních tabulkách.  Toto omezení se na INSERTy nevztahuje.  Pro opětovné povolení materializovaného pohledu spusťte ALTER MATERIALIZED VIEW s REBUILD.

Poznámky

Materializovaný pohled v Azure datovém skladu je podobný indexovanému pohledu v SQL Serveru.  Sdílí téměř stejné omezení jako indexovaný pohled (podrobnosti viz Vytvořit indexované pohledy ), s tím rozdílem, že materializovaný pohled podporuje agregované funkce.  

Poznámka:

Ačkoli CREATE MATERIALIZED VIEW nepodporuje COUNT, DISTINCT, COUNT(DISTINCT expression) ani COUNT_BIG (DISTINCT expression), dotazy SELECT s těmito funkcemi mohou stále těžit z materializovaných zobrazení pro rychlejší výkon, protože optimalizátor Synapse SQL může automaticky přepsat tyto agregace v uživatelském dotazu tak, aby odpovídaly existujícím materializovaným pohledům. Pro podrobnosti se podívejte na ukázkovou sekci tohoto článku.

APPROX_COUNT_DISTINCT není podporováno v CREATE MATERIALIZED VIEW AS SELECT.

Pouze CLUSTERED COLUMNSTORE INDEX je podporován v materializovaném pohledu.

Materializovaný pohled nemůže odkazovat na jiné pohledy.

Materializovaný pohled nelze vytvořit v tabulce s dynamickou datovou maskou (DDM), i když sloupec DDM není součástí materializovaného pohledu. Pokud je sloupec tabulky součástí aktivního materializovaného pohledu nebo deaktivovaného materializovaného pohledu, DDM nelze do tohoto sloupce přidat.

Materializovaný pohled nelze vytvořit na tabulce s povolenou bezpečností na úrovni řádku.

Materializované pohledy lze vytvářet na rozdělených tabulkách.  Partition SPLIT/MERGE jsou podporovány v základních tabulkách materialized views, partition SWITCH není podporován.

PŘEPÍNÁNÍ TABULKY ZMĚN není podporováno u tabulek, které jsou odkazovány v materializovaných pohledech. Před použitím ALTER TABLE SWITCH VYPNĚTE NEBO ODSTRAŇTE MATERIALIZOVANÉ POHLEDY. V následujících scénářích vyžaduje vytvoření materializovaného pohledu přidání nových sloupců do materializovaného pohledu:

Scenario	Nové sloupce k přidání do materializovaného pohledu	Comment
COUNT_BIG() chybí v seznamu SELECT definice materializovaného pohledu	COUNT_BIG (*)	Automaticky přidáno vytvářením materializovaného zobrazení. Není nutná žádná akce uživatele.
SUM(a) je specifikován uživateli v seznamu SELECT v definici materializovaného pohledu A 'a' je neplatný výraz	COUNT_BIG (a)	Uživatelé musí výraz 'a' přidat ručně do definice materializovaného pohledu.
AVG(a) je specifikován uživateli v seznamu SELECT v definici materializovaného pohledu, kde 'a' je výraz.	SUM(a), COUNT_BIG(a)	Automaticky přidáno vytvářením materializovaného zobrazení. Není nutná žádná akce uživatele.
STDEV(a) je specifikován uživateli v seznamu SELECT v definici materializovaného pohledu, kde 'a' je výraz.	SUM(a), COUNT_BIG(a), SUM(square(a))	Automaticky přidáno vytvářením materializovaného zobrazení. Není nutná žádná akce uživatele.

Po vytvoření jsou materializované pohledy viditelné v SQL Server Management Studio ve složce pohledů instance Azure Synapse Analytics.

Uživatelé mohou spustit SP_SPACEUSEDa DBCC PDW_SHOWSPACEUSED k určení místa, které materializovaný pohled zabírá. Existují také DMV, které poskytují přizpůsobitelnější dotazy pro identifikaci místa a obsazených řádků. Pro více informací viz dotazy na velikost tabulky.

Materializovaný pohled lze zobrazit pomocí DROP VIEW. Můžete použít ALTER MATERIALIZED VIEW k deaktivaci nebo obnovení materializovaného pohledu.

Materializovaný pohled je automatický mechanismus optimalizace dotazů. Uživatelé nemusí přímo dotazovat na materializovaný pohled. Když je uživatelský dotaz odeslán, engine zkontroluje uživatelova oprávnění k dotazovacím objektům a dotaz bez spuštění neprovede, pokud uživatel nemá přístup k tabulkám nebo běžným zobrazením v dotazu. Pokud bylo oprávnění uživatele ověřeno, optimalizátor automaticky použije odpovídající materializovaný pohled k provedení dotazu pro rychlejší výkon. Uživatelé dostávají stejná data zpět bez ohledu na to, zda je dotaz obsluhován dotazem do základních tabulek nebo do materializovaného pohledu.

EXPLAIN plán a grafický odhadovaný plán provedení v SQL Server Management Studio mohou ukázat, zda optimalizátor dotazu zvažuje materializovaný pohled pro provádění dotazu, a grafický odhadovaný plán provedení v SQL Server Management Studio může ukázat, zda je materializovaný pohled zohledněn optimalizátorem dotazu pro provádění dotazu.

Chcete-li zjistit, zda SQL příkaz může těžit z nového materializovaného pohledu, spusťte EXPLAIN příkaz s .WITH_RECOMMENDATIONS Podrobnosti viz VYSVĚTLIT (Transact-SQL).

Vlastnictví

• Materializovaný pohled nelze vytvořit, pokud majitelé základních tabulek a materializovaný pohled to-be-created nejsou totožní.
• Materializovaný pohled a jeho základní tabulky mohou být součástí různých schémat. Když je materializovaný pohled vytvořen, vlastník schématu se automaticky stává vlastníkem materializovaného pohledu a toto vlastnictví nelze změnit.

Povolení

Uživatel potřebuje následující oprávnění k vytvoření materializovaného pohledu kromě splnění požadavků na vlastnictví objektu:

1. Oprávnění VYTVOŘIT ZOBRAZIT v databázi
2. SELECT oprávnění v základních tabulkách materializovaného pohledu
3. ODKAZUJE na povolení ke schématu obsahujícímu základní tabulky
4. ALTER povolení ke schématu obsahujícímu materializovaný pohled

Example

A. Tento příklad ukazuje, jak Synapse SQL optimalizátor automaticky využívá materializované pohledy k provedení dotazu pro lepší výkon, i když dotaz používá funkce, které nejsou podporovány v CREATE MATERIALIZED VIEW, například COUNT(DISTINCT expression). Dotaz, který dříve trval několik sekund, nyní končí za méně než sekundu bez změny uživatelského dotazu.

-- 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. V tomto příkladu User2 vytváří materializovaný pohled na tabulkách vlastněných User1. Materializovaný pohled vlastní uživatel 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

Viz také

• ALTER MATERIALIZED VIEW (Transact-SQL)
• DROP VIEW
• VYSVĚTLI (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 and Analytics Platform System (PDW) Catalog Views
• System views supported in Azure Azure Synapse Analytics
• T-SQL statements supported in Azure Azure Synapse Analytics

Další kroky

• Ladění výkonu pomocí Materialized View