Udostępnij za pomocą

UTWORZENIE WIDOKU MATERIALIZOWANEGO JAKO SELECT (Transact-SQL)

Dotyczy: Azure Synapse Analytics

Ten artykuł wyjaśnia polecenie CREATE MATERIALIZED VIEW AS SELECT T-SQL w Azure Synapse Analytics do tworzenia rozwiązań. Artykuł zawiera także przykłady kodów.

Widok Materializowany zachowuje dane zwrócone z zapytania definicji widoku i automatycznie aktualizuje się wraz ze zmianą danych w tabelach bazowych. Poprawia wydajność złożonych zapytań (zazwyczaj zapytań z połączeniami i agregacjami), oferując jednocześnie proste operacje konserwacyjne. Dzięki możliwości automatycznego dopasowywania planu wykonawczego, zmaterializowany widok nie musi być odwoływany w zapytaniu, aby optymalizator rozważył widok do podstawienia. Ta zdolność pozwala inżynierom danych implementować zmaterializowane widoki jako mechanizm poprawiający czas odpowiedzi na zapytania, bez konieczności zmiany zapytań.

Transact-SQL konwencje składni

Składnia

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

Uwaga / Notatka

Ta składnia nie jest obsługiwana przez bezserwerową pulę SQL w usłudze Azure Synapse Analytics.

Arguments

schema_name

To nazwa schematu, do którego należy widok.

materialized_view_name

To nazwa widoku. Nazwy widoków muszą być zgodne z regułami dotyczącymi identyfikatorów. Określanie nazwy właściciela widoku jest opcjonalne.

Opcja dystrybucji

Obsługiwane są tylko dystrybucje HASH i ROUND_ROBIN. Więcej informacji o opcjach dystrybucji można znaleźć w CREATE TABLE Opcje dystrybucji tabeli. Aby uzyskać rekomendacje, którą dystrybucję wybrać dla tabeli na podstawie rzeczywistego użycia lub przykładowych zapytań, zobacz Distribution Advisor w Azure Synapse SQL.

DISTRIBUTION = HASH ( distribution_column_name )
Rozdziela wiersze na podstawie wartości pojedynczej kolumny.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Rozdziela wiersze na podstawie wartości skrótu do ośmiu kolumn, co pozwala na bardziej równomierne rozłożenie zmaterializowanych danych widokowych, zmniejsza przesunięcie danych w czasie i poprawia wydajność zapytań.

Uwaga / Notatka

  • Aby włączyć funkcję Rozkładu Wielokolumnowego, należy zmienić poziom kompatybilności bazy danych na 50 za pomocą tego polecenia. Więcej informacji na temat ustawiania poziomu kompatybilności bazy danych można znaleźć w ALTER DATABASE SCOPED CONFIGURATION. Przykład: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Aby wyłączyć MCD, uruchom to polecenie, aby zmienić poziom kompatybilności bazy danych na AUTO. Na przykład: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Istniejące zmaterializowane widoki MCD pozostaną, ale staną się nieczytelne.
    • Aby odzyskać dostęp do widoków MCD materializowanych, ponownie włącz tę funkcję.

select_statement

Lista SELECT w definicji widoku materializowanego musi spełniać co najmniej jedno z tych dwóch kryteriów:

  • Lista SELECT zawiera funkcję agregacyjną.
  • W definicji widoku Materialized używa się GROUP BY, a wszystkie kolumny w GROUP BY są uwzględnione na liście SELECT. W klauzuli GROUP BY można użyć do 32 kolumn.

Funkcje agregujące są wymagane w liście SELECT definicji zmaterializowanego widoku. Obsługiwane agregacje obejmują MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Gdy agregaty MIN/MAX są używane w liście SELECT definicji widoku materializowanego, obowiązują następujące wymagania:

  • Ciąg FOR_APPEND jest wymagany. Przykład:

    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

  • Widok materializowany zostanie wyłączony, gdy w referencyjnych tabelach bazowych wystąpi AKTUALIZACJA lub DELETE.  To ograniczenie nie dotyczy INSERT-ów.  Aby ponownie włączyć widok materializowany, uruchom ALTER MATERIALIZED VIEW z REBUILD.

Uwagi

Widok materializowany w Azure Data Warehouse jest podobny do widoku indeksowanego w SQL Server.  Ma niemal te same ograniczenia co widok indeksowany (szczegóły zobacz Tworzenie widoków indeksowanych ), z tą różnicą, że widok materializowany obsługuje funkcje agregujące.  

Uwaga / Notatka

Chociaż CREATE MATERIALIZED VIEW nie obsługuje funkcji COUNT, DISTINCT, COUNT(DISTINCT expression) ani COUNT_BIG (DISTINCT expression), zapytania SELECT z tymi funkcjami mogą nadal korzystać z widoków materializowanych dla szybszej wydajności, ponieważ optymalizator SQL Synapse może automatycznie przepisać te agregacje w zapytaniu użytkownika, aby dopasowały istniejące zmaterializowane widoki. Szczegóły znajdziesz w przykładowej sekcji tego artykułu.

APPROX_COUNT_DISTINCT nie jest obsługiwane w CREATE MATERIALIZED VIEW AS SELECT.

Tylko CLUSTERED COLUMNSTORE INDEX jest obsługiwany przez widok materializowany.

Widok materializowany nie może odnosić się do innych widoków.

Widok materializowany nie może być utworzony na tabeli z dynamicznym maskowaniem danych (DDM), nawet jeśli kolumna DDM nie jest częścią widoku materializowanego. Jeśli kolumna tabeli jest częścią aktywnego widoku materializowanego lub wyłączonego widoku materializowanego, DDM nie może zostać dodany do tej kolumny.

Widok materializowany nie może być utworzony na tabeli z włączonym zabezpieczeniem na poziomie wiersza.

Widoki materializowane można tworzyć na tabelach partycjonowanych.  Partition SPLIT/MERGE są obsługiwane w tabelach bazowych widoków materializowanych, natomiast PARTITION SWITCH nie jest obsługiwany.

ZMIANA TABELI nie jest obsługiwana w tabelach odwoływanych w widokach materializowanych. Wyłącz lub usuń zmaterializowane widoki przed użyciem ALTER TABLE SWITCH. W następujących scenariuszach tworzenie zmaterializowanego widoku wymaga dodania nowych kolumn do zmaterializowanego widoku:

Scenario Nowe kolumny do dodania do widoku materializowanego Comment
COUNT_BIG() brakuje w liście SELECT definicji widoku materializowanego COUNT_BIG (*) Automatycznie dodawane przez materializację view creation. Nie jest wymagana żadna akcja ze strony użytkownika.
SUM(a) jest określane przez użytkowników w liście SELECT definicji widoku materializowanego, a 'a' jest wyrażeniem zerowym COUNT_BIG (a) Użytkownicy muszą ręcznie dodać wyrażenie 'a' w definicji widoku materializowanego.
AVG(a) jest określany przez użytkowników w liście SELECT definicji widoku materializowanego, gdzie 'a' jest wyrażeniem. SUM(a), COUNT_BIG(a) Automatycznie dodawane przez materializację view creation. Nie jest wymagana żadna akcja ze strony użytkownika.
STDEV(a) jest określany przez użytkowników w liście SELECT w definicji zmaterializowanego widoku, gdzie 'a' jest wyrażeniem. SUM(a), COUNT_BIG(a), SUM(square(a)) Automatycznie dodawane przez materializację view creation. Nie jest wymagana żadna akcja ze strony użytkownika.

Po utworzeniu zmaterializowane widoki są widoczne w SQL Server Management Studio w folderze widoków instancji Azure Synapse Analytics.

Użytkownicy mogą uruchomić SP_SPACEUSED i PDW_SHOWSPACEUSED DBCC , aby określić przestrzeń zajmowaną przez widok materializowany. Są też DMV, które oferują bardziej konfigurowalne zapytania do identyfikacji przestrzeni i zajętych wierszy. Więcej informacji można znaleźć w artykule Zapytania o rozmiar tabeli.

Widok materializowany można usunąć za pomocą DROP VIEW. Możesz użyć ALTER MATERIALIZED VIEW, aby wyłączyć lub odbudować widok materializowany.

Widok materializowany to automatyczny mechanizm optymalizacji zapytań. Użytkownicy nie muszą bezpośrednio zapytywać zmaterializowanego widoku. Gdy zapytanie użytkownika zostanie wysłane, silnik sprawdza uprawnienia użytkownika do obiektów zapytania i nie zalicza zapytania bez wykonania, jeśli użytkownik nie ma dostępu do tabel lub zwykłych widoków w zapytaniu. Jeśli uprawnienia użytkownika zostały zweryfikowane, optymalizator automatycznie używa dopasowanego zmaterializowanego widoku do wykonania zapytania dla szybszej wydajności. Użytkownicy otrzymują te same dane niezależnie od tego, czy zapytanie jest obsługiwane przez zapytanie do tabel bazowych, czy do widoku materializowanego.

Plan EXPLAIN oraz graficzny Estimated Execution Plan w SQL Server Management Studio mogą pokazać, czy zmaterializowany widok jest brany pod uwagę przez optymalizator zapytań do wykonania zapytania, a graficzny Estimated Execution Plan w SQL Server Management Studio może pokazać, czy zmaterializowany widok jest brany pod uwagę przez optymalizator zapytań do wykonania zapytania.

Aby sprawdzić, czy instrukcja SQL może skorzystać z nowego widoku materializowanego, uruchom polecenie EXPLAIN z .WITH_RECOMMENDATIONS Szczegóły znajdziesz w WYJAŚNIENIU (Transact-SQL).

Własność

  • Widok materializowany nie może zostać utworzony, jeśli właściciele tabel bazowych i zmaterializowany widok to-be-created nie są tym samym.
  • Widok materializowany i jego tabele bazowe mogą znajdować się w różnych schematach. Gdy obraz materializowany zostanie utworzony, właściciel schematu widoku automatycznie staje się właścicielem zmaterializowanego widoku i własność tego widoku nie może zostać zmieniona.

Permissions

Użytkownik potrzebuje następujących uprawnień, aby utworzyć widok materializowany, oprócz spełnienia wymagań dotyczących własności obiektu:

  1. CREATE VIEW uprawnienie do wyświetlania w bazie danych
  2. SELECT uprawnienia do tabel bazowych widoku materializowanego
  3. REFERENCJE na uprawnienia do schematu zawierającego tabele bazowe
  4. Pozwolenie ALTER na schemat zawierający zmaterializowany widok

Example

A. Ten przykład pokazuje, jak optymalizacja Synapse SQL automatycznie wykorzystuje zmaterializowane widoki do wykonania zapytania dla lepszej wydajności, nawet gdy zapytanie korzysta z funkcji nieobsługiwanych w CREATE MATERIALIZED VIEW, takich jak COUNT(DISTINCT expression). Zapytanie, które kiedyś trwało kilka sekund, teraz kończy się w mniej niż sekundę, bez żadnej zmiany zapytania użytkownika.


-- 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. W tym przykładzie User2 tworzy zmaterializowany widok na tabelach należących do User1. Widok materializowany należy do 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

Zobacz także

Dalsze kroki

  • Last updated on