CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

  • Artikel

Gilt für:Azure Synapse Analytics

In diesem Artikel wird die T-SQL-Anweisung CREATE MATERIALIZED VIEW AS SELECT in Azure Synapse Analytics für Entwicklungslösungen erläutert. Der Artikel enthält auch Codebeispiele.

In einer materialisierten Sicht werden die von der Sichtdefinitionsabfrage zurückgegebenen Daten beibehalten, und die Sicht wird automatisch aktualisiert, wenn Daten in den zugrunde liegenden Tabellen geändert werden. Sie verbessert die Leistung komplexer Abfragen (in der Regel Abfragen mit Joins und Aggregationen) und bietet einfache Wartungsvorgänge. Dank der Funktion zum automatischen Abgleich ihres Ausführungsplans muss eine materialisierte Sicht nicht in der Abfrage referenziert werden, damit der Optimierer die Sicht für Ersetzungen berücksichtigt. Dadurch können Dateningenieure materialisierte Sichten als Mechanismus zur Verbesserung der Beantwortungszeit für Abfragen implementieren, ohne die Abfragen ändern zu müssen.

Transact-SQL-Syntaxkonventionen

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

Hinweis

Diese Syntax wird vom serverlosen SQL-Pool in Azure Synapse Analytics nicht unterstützt.

Argumente

schema_name

Ist der Name des Schemas, zu dem die Sicht gehört.

materialized_view_name

Der Name der Sicht. Sichtnamen müssen den Regeln für Bezeichner entsprechen. Das Angeben des Sichtbesitzernamens ist optional.

distribution_option

Es werden nur HASH- und ROUND_ROBIN-Distributionen unterstützt. Weitere Informationen zu Verteilungsoptionen finden Sie unter Tabellenverteilungsoptionen. Empfehlungen zur Auswahl der Verteilung für eine Tabelle auf Basis der tatsächlichen Verwendungs- oder Beispielabfragen finden Sie unter Verteilungsratgeber in Azure Synapse SQL.

DISTRIBUTION = HASH ( Name der Verteilungsspalte )
Verteilt die Zeilen basierend auf den Werten einer einzelnen Spalte.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) verteilt die Zeilen basierend auf den Hashwerten von bis zu acht Spalten, sodass die Daten der materialisierten Sicht noch gleichmäßiger verteilt werden, Datenschiefe im Laufe der Zeit verringert wird und die Abfrageleistung verbessert wird.

Hinweis

  • Wenn Sie das Feature für die mehrspaltige Verteilung aktivieren möchten, ändern Sie mit diesem Befehl den Kompatibilitätsgrad der Datenbank in 50. Weitere Informationen zum Festlegen des Datenbank-Kompatibilitätsgrads finden Sie unter ALTER DATABSE SCOPED CONFIGURATION. Beispiel: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Wenn Sie die MCD deaktivieren möchten, führen Sie diesen Befehl aus, um den Kompatibilitätsgrad der Datenbank in AUTO zu ändern. Beispiel: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; vorhandene materialisierte MCD-Sichten bleiben erhalten, werden aber unlesbar.
    • Aktivieren Sie das Feature erneut, um wieder auf materialisierte MCD-Sichten zugreifen zu können.

select_statement

Die SELECT-Liste in der Definition der materialisierten Sicht muss mindestens eines von diesen zwei Kriterien erfüllen:

  • Die SELECT-Liste enthält eine Aggregatfunktion.
  • GROUP BY wird in der Definition der materialisierten Sicht verwendet, und alle Spalten in GROUP BY sind in der SELECT-Liste enthalten. In der GROUP BY-Klausel können bis zu 32 Spalten verwendet werden.

Aggregatfunktionen sind in der SELECT-Liste der Definition der materialisierten Sicht erforderlich. Zu den unterstützten Aggregationen gehören MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Wenn MIN/MAX-Aggregate in der SELECT-Liste der Definition der materialisierten Sicht verwendet werden, gelten die folgenden Anforderungen:

  • FOR_APPEND ist erforderlich. Beispiel:

    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

  • Die materialisierte Sicht wird bei einem UPDATE oder DELETE in den referenzierten Basistabellen deaktiviert.  Diese Einschränkung gilt nicht für INSERT-Anweisungen.  Führen Sie ALTER MATERIALIZED VIEW mit REBUILD aus, um die materialisierte Sicht wieder zu aktivieren.

Bemerkungen

Eine materialisierte Sicht in Azure Data Warehouse ähnelt einer indizierten Sicht in SQL Server.  Es gelten fast die gleichen Einschränkungen wie für eine indizierte Sicht (Details siehe Erstellen indizierter Sichten) – abgesehen davon, dass eine materialisierte Sicht Aggregatfunktionen unterstützt.  

Hinweis

Obwohl CREATE MATERIALIZED VIEW die Anweisungen COUNT, DISTINCT, COUNT(DISTINCT-Ausdruck) oder COUNT_BIG (DISTINCT-Ausdruck) nicht unterstützt, können SELECT-Abfragen mit diesen Funktionen dennoch von materialisierten Sichten in Form einer schnelleren Leistung profitieren, da das Optimierungstool für Synapse SQL diese Aggregationen in der Benutzerabfrage automatisch neu schreiben kann, um für Übereinstimmung mit vorhandenen materialisierten Sichten zu sorgen. Details finden Sie im Abschnitt mit Beispielen dieses Artikels.

APPROX_COUNT_DISTINCT wird in CREATE MATERIALIZED VIEW AS SELECT nicht unterstützt.

Eine materialisierte Sicht unterstützt nur CLUSTERED COLUMNSTORE INDEX.

Eine materialisierte Sicht kann nicht auf andere Sichten verweisen.

Für Tabellen mit dynamischer Datenmaskierung (DDM) kann keine materialisierte Sicht erstellt werden, selbst wenn die DDM-Spalte nicht Teil der materialisierten Sicht ist. Wenn eine Tabellenspalte Teil einer aktiven oder deaktivierten materialisierten Sicht ist, kann bei dieser Spalte keine DDM hinzugefügt werden.

Für Tabellen mit aktivierter Sicherheit auf Zeilenebene kann keine materialisierte Sicht erstellt werden.

Materialisierte Sichten können für partitionierte Tabellen erstellt werden.  SPLIT/MERGE in Bezug auf Partitionen wird bei Basistabellen für materialisierte Sichten unterstützt. SWITCH in Bezug auf Partitionen wird nicht unterstützt.

ALTER TABLE SWITCH wird nicht für Tabellen unterstützt, die in materialisierten Sichten referenziert werden. Deaktivieren oder löschen Sie die materialisierte Sicht, bevor Sie ALTER TABLE SWITCH verwenden. In den folgenden Szenarien erfordert das Erstellen der materialisierten Sicht das Hinzufügen neuer Spalten zur materialisierten Sicht:

Szenario Der materialisierten Sicht neu hinzuzufügende Spalten Comment
COUNT_BIG() fehlt in der SELECT-Liste der Definition einer materialisierten Sicht. COUNT_BIG (*) Wird beim Erstellen der materialisierten Sicht automatisch hinzugefügt. Es ist keine Benutzeraktion erforderlich.
SUM(a) wird von Benutzern in der SELECT-Liste der Definition einer materialisierten Sicht angegeben, und „a“ ist ein Ausdruck, der Nullwerte zulässt. COUNT_BIG (a) Benutzer müssen der Definition der materialisierten Sicht den Ausdruck „a“ manuell hinzufügen.
AVG(a) wird von Benutzern in der SELECT-Liste der Definition einer materialisierten Sicht angegeben, wobei „a“ ein Ausdruck ist. SUM(a), COUNT_BIG(a) Wird beim Erstellen der materialisierten Sicht automatisch hinzugefügt. Es ist keine Benutzeraktion erforderlich.
STDEV(a) wird von Benutzern in der SELECT-Liste der Definition einer materialisierten Sicht angegeben, wobei „a“ ein Ausdruck ist. SUM(a), COUNT_BIG(a), SUM(square(a)) Wird beim Erstellen der materialisierten Sicht automatisch hinzugefügt. Es ist keine Benutzeraktion erforderlich.

Nach ihrer Erstellung werden materialisierte Sichten in SQL Server Management Studio im Ordner Ansicht der Azure Synapse Analytics-Instanz angezeigt.

Benutzer können SP_SPACEUSED und DBCC PDW_SHOWSPACEUSED ausführen, um den von einer materialisierten Sicht verbrauchten Speicherplatz zu ermitteln. Es gibt auch DMVs, die stärker anpassbare Abfragen bereitstellen können, um verbrauchten Platz und verbrauchte Zeilen zu identifizieren. Weitere Informationen finden Sie unter Tabellengrößenabfragen.

Eine materialisierte Sicht kann über DROP VIEW gelöscht werden. Sie können eine materialisierte Sicht mit ALTER MATERIALIZED VIEW deaktivieren oder neu erstellen.

Die materialisierte Sicht ist ein automatischer Mechanismus zur Abfrageoptimierung. Benutzer müssen eine materialisierte Sicht nicht direkt abfragen. Wenn eine Benutzerabfrage übermittelt wird, überprüft die Engine die Berechtigungen des Benutzers für die Abfrageobjekte. Wenn der Benutzer keine Zugriffsberechtigung zu den Tabellen oder regulären Sichten in der Abfrage hat, tritt ein Fehler auf, und die Abfrage wird nicht ausgeführt. Wenn die Berechtigung des Benutzers verifiziert wurde, verwendet der Optimierer automatisch eine passende materialisierte Sicht, um die Abfrage mit schnellerer Leistung auszuführen. Benutzern werden dieselben Daten zurückgegeben, unabhängig davon, ob die Abfrage über die Abfrage der Basistabellen oder über die materialisierte Sicht ausgeführt wird.

EXPLAIN-Plan und der grafische geschätzte Ausführungsplan in SQL Server Management Studio können anzeigen, ob eine materialisierte Ansicht vom Abfrageoptimierer für die Abfrageausführung berücksichtigt wird, und der grafische geschätzte Ausführungsplan in SQL Server Management Studio kann anzeigen, ob eine materialisierte Ansicht vom Abfrageoptimierer für die Abfrageausführung berücksichtigt wird.

Um herauszufinden, ob eine SQL-Anweisung von einer neuen materialisierten Sicht profitieren kann, führen Sie den EXPLAIN-Befehl mit WITH_RECOMMENDATIONS aus. Ausführliche Informationen finden Sie unter EXPLAIN (Transact-SQL).

Besitz

  • Eine materialisierte Sicht kann nicht erstellt werden, wenn die Besitzer der Basistabellen und der zu erstellenden materialisierten Sicht nicht übereinstimmen.
  • Eine materialisierte Sicht und ihre Basistabellen können sich in unterschiedlichen Schemas befinden. Wenn die materialisierte Sicht erstellt wurde, wird der Schemabesitzer der Sicht automatisch zum Besitzer der materialisierten Sicht, und es kann kein anderer Besitzer für die Sicht festgelegt werden.

Berechtigungen

Ein Benutzer muss die Anforderungen an Objektbesitzer erfüllen und benötigt zudem die folgenden Berechtigungen, um eine materialisierte Sicht zu erstellen:

  1. CREATE VIEW-Berechtigung in der Datenbank
  2. SELECT-Berechtigung für die Basistabellen der materialisierten Sicht
  3. REFERENCES-Berechtigung für das Schema, das die Basistabellen enthält
  4. ALTER-Berechtigung für das Schema, das die materialisierte Sicht enthält

Beispiel

A. Dieses Beispiel zeigt, wie das Optimierungstool für Synapse SQL materialisierte Sichten verwendet, um eine Abfrage mit verbesserter Leistung auszuführen, selbst wenn die Abfrage Funktionen verwendet, die in CREATE MATERIALIZED VIEW nicht unterstützt werden, wie z. B. COUNT(DISTINCT expression). Abfragen, deren Durchführung bisher mehrere Sekunden dauerte, werden nun im Bruchteil einer Sekunde abgeschlossen, ohne dass Änderungen der Benutzerabfrage erfolgen.


-- 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 diesem Beispiel erstellt User2 eine materialisierte Sicht für Tabellen im Besitz von User1. Besitzer der materialisierten Sicht ist 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

Weitere Informationen

Nächste Schritte