Delen via

MAAK EEN GEMATERIALISEERDE WEERGAVE AAN ALS SELECT (Transact-SQL)

van toepassing op:Azure Synapse Analytics-

Dit artikel legt de CREATE MATERIALIZED VIEW AS SELECT T-SQL instructie uit in Azure Synapse Analytics voor het ontwikkelen van oplossingen. Het artikel geeft ook voorbeelden van codes.

Een gematerialiseerde weergave bewaart de gegevens die uit de weergave-definitiequery worden teruggegeven en wordt automatisch bijgewerkt naarmate de gegevens in de onderliggende tabellen veranderen. Het verbetert de prestaties van complexe queries (meestal queries met joins en aggregaties) en biedt eenvoudige onderhoudsoperaties. Met de mogelijkheid om automatisch te matchen van uitvoeringsplan, hoeft een gematerialiseerd view niet in de query te worden geraadpleegd zodat de optimizer de view voor substitutie kan overwegen. Deze mogelijkheid stelt data engineers in staat om gematerialiseerde weergaven te implementeren als mechanisme om de responstijd van querys te verbeteren, zonder queries te hoeven wijzigen.

Transact-SQL syntaxis-conventies

Syntaxis

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

Opmerking

Deze syntaxis wordt niet ondersteund door een serverloze SQL-pool in Azure Synapse Analytics.

Arguments

schema_name

Is de naam van het schema waartoe de weergave behoort.

materialized_view_name

Is de naam van de weergave. Weergavenamen moeten de regels voor id's volgen. Het opgeven van de naam van de weergave-eigenaar is optioneel.

Distributieoptie

Alleen HASH- en ROUND_ROBIN distributies worden ondersteund. Voor meer informatie over distributieopties, zie CREATE TABLE Table distributieopties. Voor aanbevelingen over welke distributie te kiezen voor een tabel op basis van daadwerkelijk gebruik of voorbeeldzoeken, zie Distribution Advisor in Azure Synapse SQL.

DISTRIBUTION = HASH ( distribution_column_name )
Verdeelt de rijen op basis van de waarden van een enkele kolom.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Verdeelt de rijen op basis van de hashwaarden van maximaal acht kolommen, waardoor een gelijkmatigere verdeling van gematerialiseerde weergavegegevens mogelijk is, de datascheef in de loop van de tijd wordt verminderd en de queryprestaties verbetert.

Opmerking

  • Om de Multi-Column Distribution-functie in te schakelen, verander je het compatibiliteitsniveau van de database naar 50 met dit commando. Voor meer informatie over het instellen van het databasecompatibiliteitsniveau, zie ALTER DATABASE SCOPED CONFIGURATION. Bijvoorbeeld: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Om MCD uit te schakelen, voer je dit commando uit om het compatibiliteitsniveau van de database te wijzigen naar AUTO. Bijvoorbeeld: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; bestaande MCD-gematerialiseerde views blijven aanwezig maar worden onleesbaar.
    • Om weer toegang te krijgen tot MCD-gematerialiseerde weergaven, schakel de functie opnieuw in.

select_statement

De SELECT-lijst in de gematerialiseerde weergavedefinitie moet aan ten minste één van deze twee criteria voldoen:

  • De SELECT-lijst bevat een aggregaatfunctie.
  • GROUP BY wordt gebruikt in de definitie van de gematerialiseerde weergave en alle kolommen in GROUP BY zijn opgenomen in de SELECT-lijst. Er kunnen tot 32 kolommen worden gebruikt in de GROUP BY-clausule.

Aggregaatfuncties zijn vereist in de SELECT-lijst van de gematerialiseerde weergavedefinitie. Ondersteunde aggregaties zijn onder andere MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Wanneer MIN/MAX-aggregaten worden gebruikt in de SELECT-lijst van de definitie van gematerialiseerde weergaven, gelden de volgende vereisten:

  • FOR_APPEND is vereist. Voorbeeld:

    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

  • De gematerialiseerde weergave wordt uitgeschakeld wanneer een UPDATE of DELETE plaatsvindt in de verwezen basistabellen.  Deze beperking geldt niet voor INSERTs.  Om de gematerialiseerde weergave weer in te schakelen, voer je ALTER MATERIALIZED VIEW uit met REBUILD.

Opmerkingen

Een gematerialiseerde weergave in Azure Data Warehouse lijkt op een geïndexeerde weergave in SQL Server.  Het deelt bijna dezelfde beperkingen als geïndexeerde weergave (zie Geïndexeerde weergaven aanmaken voor details), behalve dat een gematerialiseerde weergave aggregaatfuncties ondersteunt.  

Opmerking

Hoewel CREATE MATERIALIZED VIEW geen COUNT, DISTINCT, COUNT(DISTINCT expressie) of COUNT_BIG (DISTINCT expressie) ondersteunt, kunnen SELECT-queries met deze functies nog steeds profiteren van gematerialiseerde weergaven voor snellere prestaties, omdat de Synapse SQL-optimizer die aggregaties in de gebruikersquery automatisch kan herschrijven om overeen te komen met bestaande gematerialiseerde views. Voor details, bekijk het voorbeeldgedeelte van dit artikel.

APPROX_COUNT_DISTINCT wordt niet ondersteund in CREATE MATERIALIZED VIEW AS SELECT.

Alleen CLUSTERED COLUMNSTORE INDEX wordt ondersteund door gematerialiseerde weergave.

Een gematerialiseerde visie kan geen verwijzing naar andere visies verwijzen.

Een gematerialiseerde weergave kan niet worden gemaakt op een tabel met dynamische datamaskering (DDM), zelfs niet als de DDM-kolom geen deel uitmaakt van de gematerialiseerde weergave. Als een tabelkolom deel uitmaakt van een actieve gematerialiseerde weergave of een uitgeschakelde gematerialiseerde weergave, kan DDM niet aan deze kolom worden toegevoegd.

Een gematerialiseerde weergave kan niet worden aangemaakt op een tabel met rijenbeveiliging ingeschakeld.

Gematerialiseerde weergaven kunnen worden aangemaakt op gepartitioneerde tabellen.  Partitie SPLIT/MERGE worden ondersteund op gematerialiseerde weergaven van basistabellen, partitie SWITCH wordt niet ondersteund.

ALTER TABLE SWITCH wordt niet ondersteund op tabellen die worden verwezen in gematerialiseerde weergaven. Schakel de gematerialiseerde weergaven uit of verwijder ze voordat je ALTER TABLE SWITCH gebruikt. In de volgende scenario's vereist het creëren van gematerialiseerde weergave dat nieuwe kolommen aan de gematerialiseerde weergave worden toegevoegd:

Scenario Nieuwe kolommen om toe te voegen aan het gematerialiseerde beeld Comment
COUNT_BIG() ontbreekt in de SELECT-lijst van een gematerialiseerde weergavedefinitie COUNT_BIG (*) Automatisch toegevoegd door het aanmaken van gematerialiseerde weergaven. De gebruiker hoeft verder niets te doen.
SUM(a) wordt door gebruikers gespecificeerd in de SELECT-lijst van een gematerialiseerde weergavedefinitie EN 'a' is een nulbare expressie COUNT_BIG (a) Gebruikers moeten de uitdrukking 'a' handmatig toevoegen in de gematerialiseerde weergavedefinitie.
AVG(a) wordt door gebruikers gespecificeerd in de SELECT-lijst van een gematerialiseerde weergavedefinitie waarbij 'a' een expressie is. SUM(a), COUNT_BIG(a) Automatisch toegevoegd door het aanmaken van gematerialiseerde weergaven. De gebruiker hoeft verder niets te doen.
STDEV(a) wordt door gebruikers gespecificeerd in de SELECT-lijst van een gematerialiseerde weergavedefinitie waarbij 'a' een expressie is. SUM(a), COUNT_BIG(a), SUM(vierkant(a)) Automatisch toegevoegd door het aanmaken van gematerialiseerde weergaven. De gebruiker hoeft verder niets te doen.

Eenmaal aangemaakt, zijn gematerialiseerde weergaven zichtbaar binnen SQL Server Management Studio onder de views-map van de Azure Synapse Analytics-instantie.

Gebruikers kunnen SP_SPACEUSED en DBCC PDW_SHOWSPACEUSED uitvoeren om te bepalen welke ruimte wordt ingenomen door een gematerialiseerd beeld. Er zijn ook DMV's die meer aanpasbare zoekopdrachten bieden om ruimte en rijen te identificeren. Zie query's voor tabelgroottenvoor meer informatie.

Een gematerialiseerde weergave kan worden verwijderd via DROP VIEW. Je kunt ALTER MATERIALIZED VIEW gebruiken om een gematerialiseerde view uit te schakelen of opnieuw op te bouwen.

Gematerialiseerde weergave is een automatisch queryoptimalisatiemechanisme. Gebruikers hoeven een gematerialiseerde weergave niet direct te zoeken. Wanneer een gebruikersquery wordt ingediend, controleert de engine de rechten van de gebruiker voor de queryobjecten en faalt de query zonder uitvoering als de gebruiker geen toegang heeft tot de tabellen of reguliere weergaven in de query. Als de toestemming van de gebruiker is geverifieerd, gebruikt de optimizer automatisch een bijpassende gematerialiseerde weergave om de query uit te voeren voor snellere prestaties. Gebruikers krijgen dezelfde data terug, ongeacht of de query wordt uitgevoerd door het bevragen van de basistabellen of de gematerialiseerde weergave.

EXPLAIN plan en het grafische Estimated Execution Plan in SQL Server Management Studio kunnen laten zien of een gematerialiseerde weergave door de queryoptimizer wordt overwogen voor query-uitvoering, en het grafische Estimated Execution Plan in SQL Server Management Studio kan laten zien of een gematerialiseerde view door de queryoptimizer wordt meegenomen voor query-uitvoering.

Om te achterhalen of een SQL-statement baat kan hebben bij een nieuw gematerialiseerd beeld, voer je het EXPLAIN commando uit met WITH_RECOMMENDATIONS. Voor details, zie EXPLAIN (Transact-SQL).

Eigendom

  • Een gematerialiseerde weergave kan niet worden gemaakt als de eigenaren van de basistabellen en de gematerialiseerde weergave to-be-gemaakt niet dezelfde zijn.
  • Een gematerialiseerde weergave en de basistabellen kunnen in verschillende schema's staan. Wanneer de gematerialiseerde view wordt aangemaakt, wordt de schema-eigenaar van de view automatisch de eigenaar van de gematerialiseerde view en kan dit view-eigendom niet worden gewijzigd.

Permissions

Een gebruiker heeft de volgende rechten nodig om een gematerialiseerde weergave te maken, naast het voldoen aan de eigendomsvereisten voor objecten:

  1. CREATE VIEW-toestemming in de database
  2. SELECT-toestemming op de basistabellen van de gematerialiseerde weergave
  3. REFERENCES-toestemming op het schema dat de basistabellen bevat
  4. ALTER-toestemming op het schema dat de gematerialiseerde weergave bevat

Example

Eén. Dit voorbeeld laat zien hoe Synapse SQL-optimizer automatisch gematerialiseerde weergaven gebruikt om een query uit te voeren voor betere prestaties, zelfs wanneer de query functies gebruikt die niet worden ondersteund in CREATE MATERIALIZED VIEW, zoals COUNT(DISTINCT expression). Een query die vroeger meerdere seconden kostte om te voltooien, wordt nu binnen een seconde voltooid zonder enige wijziging in de gebruikersquery.


-- 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. In dit voorbeeld maakt User2 een gematerialiseerde weergave aan op tabellen die eigendom zijn van User1. De gematerialiseerde weergave is eigendom van 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

Zie ook

Volgende stappen

  • Last updated on