Dela via

SKAPA MATERIALISERAD VY SOM SELECT (Transact-SQL)

gäller för:Azure Synapse Analytics

Denna artikel förklarar satsen CREATE MATERIALIZED VIEW AS SELECT T-SQL i Azure Synapse Analytics för att utveckla lösningar. Artikeln ger också exempel på kod.

En Materialiserad vy behåller de data som returneras från vydefinitionsfrågan och uppdateras automatiskt när data ändras i de underliggande tabellerna. Den förbättrar prestandan för komplexa frågor (vanligtvis frågor med joins och aggregeringar) samtidigt som den erbjuder enkla underhållsoperationer. Med sin funktion för automatisk exekveringsplan behöver en materialiserad vy inte refereras i frågan för att optimeraren ska kunna överväga vyn för substitution. Denna funktion gör det möjligt för dataingenjörer att implementera materialiserade vyer som en mekanism för att förbättra svarstiden på frågor, utan att behöva ändra frågor.

Transact-SQL syntaxkonventioner

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

Anmärkning

Den här syntaxen stöds inte av en serverlös SQL-pool i Azure Synapse Analytics.

Arguments

schema_name

Är namnet på schemat som vyn tillhör.

materialized_view_name

Är namnet på vyn. Visningsnamn måste följa reglerna för identifierare. Det är valfritt att ange visningsägarens namn.

Distributionsalternativ

Endast HASH- och ROUND_ROBIN distributioner stöds. För mer information om distributionsalternativ, se SKAPA TABELL Tabellfördelningsalternativ. För rekommendationer om vilken distribution man ska välja för en tabell baserat på faktisk användning eller exempelfrågor, se Distribution Advisor i Azure Synapse SQL.

DISTRIBUTION = HASH ( distribution_column_name )
Fördelar raderna baserat på värdena i en enda kolumn.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Distribuerar raderna baserat på hashvärden för upp till åtta kolumner, vilket möjliggör en jämnare fördelning av materialiserad vydata, minskar dataskevheten över tid och förbättrar frågeprestandan.

Anmärkning

  • För att aktivera funktionen Multi-Column Distribution, ändra databasens kompatibilitetsnivå till 50 med detta kommando. För mer information om hur du ställer in databaskompatibilitetsnivån, se ALTER DATABASE SCOPED CONFIGURATION. Till exempel: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • För att inaktivera MCD, kör detta kommando för att ändra databasens kompatibilitetsnivå till AUTO. Till exempel: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Befintliga MCD-materialiserade vyer kommer att finnas kvar men bli oläsliga.
    • För att återfå tillgång till MCD-materialiserade vyer, aktivera funktionen igen.

select_statement

SELECT-listan i den materialiserade vydefinitionen måste uppfylla minst ett av dessa två kriterier:

  • SELECT-listan innehåller en aggregerad funktion.
  • GROUP BY används i definitionen av materialiserad vy och alla kolumner i GROUP BY ingår i SELECT-listan. Upp till 32 kolumner kan användas i GROUP BY-klausulen.

Aggregerade funktioner krävs i SELECT-listan i den materialiserade vydefinitionen. Stödda aggregeringar inkluderar MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

När MIN/MAX-aggregat används i SELECT-listan för materialiserad vydefinition gäller följande krav:

  • FOR_APPEND måste anges. Till exempel:

    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

  • Den materialiserade vyn inaktiveras när en UPPDATERING eller BORTTAGNING sker i de refererade bastabellerna.  Denna begränsning gäller inte för INSERTs.  För att återaktivera den materialiserade vyn, kör ALTER MATERIALIZED VIEW med REBUILD.

Anmärkningar

En materialiserad vy i Azure Data Warehouse liknar en indexerad vy i SQL Server.  Den delar nästan samma begränsningar som indexerad vy (se Skapa indexerade vyer för detaljer) förutom att en materialiserad vy stöder aggregerade funktioner.  

Anmärkning

Även om CREATE MATERIALIZED VIEW inte stödjer COUNT, DISTINCT, COUNT(DISTINCT expression) eller COUNT_BIG (DISTINKT uttryck), kan SELECT-frågor med dessa funktioner ändå dra nytta av materialiserade vyer för snabbare prestanda eftersom Synapse SQL-optimeraren automatiskt kan skriva om dessa aggregeringar i användarfrågan för att matcha befintliga materialiserade vyer. För detaljer, se denna artikels exempelsektion.

APPROX_COUNT_DISTINCT stöds inte i CREATE MATERIALIZED VIEW AS SELECT.

Endast CLUSTERED COLUMNSTORE INDEX stöds av materialiserad vy.

En materialiserad syn kan inte referera till andra synsätt.

En materialiserad vy kan inte skapas på en tabell med dynamisk datamaskering (DDM), även om DDM-kolumnen inte är en del av den materialiserade vyn. Om en tabellkolumn är en del av en aktiv materialiserad vy eller en inaktiverad materialiserad vy kan DDM inte läggas till i denna kolumn.

En materialiserad vy kan inte skapas på en tabell med radnivåsäkerhet aktiverad.

Materialiserade vyer kan skapas på partitionerade tabeller.  Partition SPLIT/MERGE stöds på materialiserade vyer i bastabeller, partition SWITCH stöds inte.

ALTER TABLE SWITCH stöds inte på tabeller som refereras i materialiserade vyer. Inaktivera eller ta bort de materialiserade vyerna innan du använder ALTER TABLE SWITCH. I följande scenarier kräver skapandet av materialiserade vy att nya kolumner läggs till i den materialiserade vyn:

Scenario Nya kolumner att lägga till i materialiserad vy Comment
COUNT_BIG() saknas i SELECT-listan i en materialiserad vydefinition COUNT_BIG (*) Automatiskt tillagd genom materialiserad vyskapande. Användaren behöver inte göra någonting.
SUM(a) specificeras av användare i SELECT-listan för en materialiserad vydefinition OCH 'a' är ett nullbart uttryck COUNT_BIG (a) Användare behöver lägga till uttrycket 'a' manuellt i den materialiserade vydefinitionen.
AVG(a) specificeras av användare i SELECT-listan i en materialiserad vydefinition där 'a' är ett uttryck. SUM(a), COUNT_BIG(a) Automatiskt tillagd genom materialiserad vyskapande. Användaren behöver inte göra någonting.
STDEV(a) specificeras av användare i SELECT-listan för en materialiserad vydefinition där 'a' är ett uttryck. SUM(a), COUNT_BIG(a), SUM(kvadrat(a)) Automatiskt tillagd genom materialiserad vyskapande. Användaren behöver inte göra någonting.

När de har skapats är materialiserade vyer synliga i SQL Server Management Studio under vymappen i Azure Synapse Analytics-instansen.

Användare kan köra SP_SPACEUSED och DBCC PDW_SHOWSPACEUSED för att avgöra vilket utrymme en materialiserad vy förbrukar. Det finns också trafikkontor som erbjuder mer anpassningsbara frågor för att identifiera utrymme och rader som förbrukas. Mer information finns i Frågor om tabellstorlek.

En materialiserad vy kan tas bort via DROP VIEW. Du kan använda ALTER MATERIALIZED VIEW för att inaktivera eller bygga om en materialiserad vy.

Materialiserad vy är en automatisk mekanism för frågeoptimering. Användare behöver inte fråga en materialiserad vy direkt. När en användarfråga skickas in kontrollerar motorn användarens behörigheter till frågeobjekten och misslyckas med frågan utan att den körs om användaren inte har tillgång till tabellerna eller vanliga vyer i frågan. Om användarens behörighet har verifierats använder optimeraren automatiskt en matchande materialiserad vy för att köra frågan för snabbare prestanda. Användare får tillbaka samma data oavsett om frågan skickas genom att fråga bastabellerna eller den materialiserade vyn.

FÖRKLARA-planen och den grafiska Estimated Execution Plan i SQL Server Management Studio kan visa om en materialiserad vy beaktas av frågeoptimeraren för frågeexekvering, och den grafiska Estimated Execution Plan i SQL Server Management Studio kan visa om en materialiserad vy beaktas av frågeoptimeraren för frågeexekvering.

För att ta reda på om en SQL-sats kan dra nytta av en ny materialiserad vy, kör kommandot EXPLAIN med WITH_RECOMMENDATIONS. För detaljer, se FÖRKLARA (Transact-SQL).

Ägarskap

  • En materialiserad vy kan inte skapas om ägarna till bastabellerna och den materialiserade vyn to-be-skapade inte är desamma.
  • En materialiserad vy och dess bastabeller kan finnas i olika scheman. När den materialiserade vyn skapas blir vyns schemaägare automatiskt ägare till den materialiserade vyn och detta vyägarskap kan inte ändras.

Permissions

En användare behöver följande behörigheter för att skapa en materialiserad vy utöver att uppfylla objektägarkraven:

  1. CREATE VIEW-behörighet i databasen
  2. SELECT-behörighet på bastabellerna i den materialiserade vyn
  3. REFERENCES-behörighet på schemat som innehåller bastabellerna
  4. ALTER-behörighet på schemat som innehåller den materialiserade vyn

Example

A. Detta exempel visar hur Synapse SQL-optimeraren automatiskt använder materialiserade vyer för att köra en fråga för bättre prestanda även när frågan använder funktioner som inte stöds i CREATE MATERIALIZED VIEW, såsom COUNT(DISTINCT expression). En fråga som tidigare tog flera sekunder att slutföra slutförs nu på en subsekund utan någon förändring i användarfrågan.


-- 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. I detta exempel skapar Användare2 en materialiserad vy på tabeller som ägs av Användare. Den materialiserade vyn ägs av Användar1.

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

Se även

Nästa steg

  • Last updated on