CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

A következőkre vonatkozik:Azure Synapse Analytics

Ez a cikk bemutatja az Azure Synapse Analytics CREATE MATERIALIZED VIEW AS SELECT T-SQL utasítást megoldások fejlesztéséhez. A cikk kód példákat is tartalmaz.

A Materializált Nézet megőrzi a nézet definíciós lekérdezésből visszaadott adatokat, és automatikusan frissül, ahogy az adatok változnak az alatta lévő táblákban. Javítja a bonyolult lekérdezések (jellemzően csatlakozásokkal és aggregációkkal rendelkező lekérdezések) teljesítményét, miközben egyszerű karbantartási műveleteket kínál. A végrehajtási terv automatikus párosítási képességével nem kell a lekérdezésben megjeleníteni a materializált nézetet, hogy az optimalizáló figyelembe vegye a nézetet helyettesítésre. Ez a képesség lehetővé teszi az adatmérnökök számára, hogy materializált nézeteket valósítsanak meg, mint a lekérdezési válaszidő javításának mechanizmusa, anélkül, hogy lekérdezéseket kellene változtatniuk.

Transact-SQL szintaxis konvenciók

Szemantika

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

Megjegyzés:

Ezt a szintaxist az Azure Synapse Analytics kiszolgáló nélküli SQL-készlete nem támogatja.

Arguments

schema_name

Annak a sémának a neve, amelyhez a nézet tartozik.

materialized_view_name

A nézet neve. A nézetneveknek az azonosítókra vonatkozó szabályokat kell követnie. A nézet tulajdonosának nevének megadása nem kötelező.

Terjesztési lehetőség

Csak a HASH és ROUND_ROBIN terjesztések támogatottak. További információért az elosztási opciókról lásd: CREATE TABLE Táblázat eloszlási opciók. Ajánlásokért a táblázat tényleges használat vagy mintalekérdezések alapján válasszuk a táblázathoz, lásd az Azure Synapse SQL Distribution Advisor című programot.

DISTRIBUTION = HASH ( distribution_column_name )
A sorokat egyetlen oszlop értékei alapján osztja el.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) A sorokat legfeljebb nyolc oszlop hash értékei alapján osztja el, lehetővé téve a materializált nézeti adatok egyenletesebb elosztását, csökkentve az adattorzítást az idővel és javítva a lekérdezések teljesítményét.

Megjegyzés:

• A Multi-Column Distribution funkció engedélyezéséhez módosítsuk az adatbázis kompatibilitási szintjét 50-re ezzel a parancsgal. További információért az adatbázis kompatibilitási szintjének beállításáról lásd: ALTER DATABASE SCOPED CONFIGURATION. Például: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Az MCD letiltásához futtatjuk ezt a parancsot, hogy az adatbázis kompatibilitási szintjét AUTO-ra változtasd. Például: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; a meglévő MCD-ben megvalósuló nézetek megmaradnak, de olvashatatlanná válnak.
  • Az MCD materializált nézetekhez való hozzáférés visszaszerzéséhez újra engedélyezze a funkciót.

select_statement

A materializált nézet definíciójában a SELECT listának legalább az egyik ilyen kritériumnak meg kell felelnie:

• A SELECT lista tartalmaz egy aggregált függvényt.
• A GROUP BY a Materializált nézet definíciójában szerepel, és a GROUP BY összes oszlopa szerepel a SELECT listában. Legfeljebb 32 oszlop használható a GROUP BY záradékban.

Az aggregált függvények szükségesek a materializált nézet definíciójának SELECT listájában. A támogatott aggregációk közé tartozik a MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Amikor a MIN/MAX aggregátumokat használják a materializált nézet definíciójának SELECT listájában, a következő követelmények érvényesek:

• A FOR_APPEND használata kötelező. Például:

  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
  

• A materializált nézet letiltásra kerül, amikor a hivatkozott alaptáblákban FRISSÍTÉS vagy TÖRLÉS történik.  Ez a korlátozás nem vonatkozik a INSERT-ekre.  A materializált nézet újraengedélyezéséhez futtasd az ALTER MATERIALIZED VIEW funkciót a REBUILD segítségével.

Megjegyzések

Az Azure adatraktárban található materializált nézet hasonló az SQL Server indexelt nézetéhez.  Majdnem ugyanazokat a korlátozásokat osztja meg, mint az indexelt nézet (részletekért lásd Indexelt nézetek létrehozása ), kivéve, hogy a materializált nézet támogatja az aggregált funkciókat.  

Megjegyzés:

Bár a CREATE MATERIALIZED VIEW nem támogatja a COUNT, DISTINCT, COUNT(DISTINCT expression) vagy COUNT_BIG (DISTINCT kifejezés) kifejezéseket, a SELECT lekérdezések ezekkel a funkciókkal még mindig profitálhatnak a materiallarizált nézetekből a gyorsabb teljesítmény érdekében, mivel a Synapse SQL optimalizáló automatikusan újraírja ezeket az aggregációkat a felhasználói lekérdezésben, hogy illeszkedjenek a meglévő materializált nézetekhez. A részletekért nézd meg ennek a cikknek a példaszekcióját.

APPROX_COUNT_DISTINCT nem támogatott a CREATE MATERIALIZED VIEW AS SELECT játékban.

Csak a CLUSTERED COLUMNSTORE INDEX támogatott a materializált nézet.

Egy materializált nézet nem hivatkozhat más nézetekre.

Materializált nézet nem hozható létre egy dinamikus adatmaszkolással (DDM) rendelkező táblán, még akkor sem, ha a DDM oszlop nem része a materializált nézetnek. Ha egy táblaoszlop része egy aktív materializált nézetnek vagy egy letiltott materializált nézetnek, akkor a DDM nem adható hozzá ebbe az oszlopba.

Materializált nézet nem hozható létre egy táblázaton, ahol sorszintű biztonság van engedélyezve.

Materializált nézetek felosztott táblákon is létrehozhatók.  A partition SPLIT/MERGE támogatott a materializált nézetek alaptábláin, a partition SWITCH nem támogatott.

AZ ALTER TABLE SWITCH nem támogatott olyan táblákon, amelyekre materializált nézetekben hivatkoznak. Kapcsold ki vagy hagyd el a materializált nézeteket, mielőtt használnád az ALTER table SWITCH-et. Az alábbi esetekben a materializált nézet létrehozásához új oszlopokat kell hozzáadni a materializált nézethez:

Scenario	Új oszlopok a materializált nézethez	Comment
COUNT_BIG() hiányzik a SELECT listából a materializált nézet definíciójában	COUNT_BIG (*)	Automatikusan hozzáadva a materializált nézet létrehozásával. Nincs szükség felhasználói beavatkozásra.
A SUM(a) a felhasználók által a materializált nézet definíciójának SELECT listájában van megadva, ÉS az 'a' egy nullable kifejezés	COUNT_BIG (a)	A felhasználóknak manuálisan kell hozzáadniuk az 'a' kifejezést a materializált nézet definíciójában.
Az AVG(a)-t a felhasználók a materializált nézet definíciójának SELECT listájában jelölik meg, ahol az 'a' kifejezés.	SUM(a), COUNT_BIG(a)	Automatikusan hozzáadva a materializált nézet létrehozásával. Nincs szükség felhasználói beavatkozásra.
A STDEV(a) a felhasználók által a materializált nézet definíciójának SELECT listáján van megadva, ahol az 'a' kifejezés.	SUM(a), COUNT_BIG(a), SUM(négyzet(a))	Automatikusan hozzáadva a materializált nézet létrehozásával. Nincs szükség felhasználói beavatkozásra.

Miután létrehozták, a materializált nézetek láthatóak az SQL Server Management Studio-ban az Azure Synapse Analytics instance views mappájában.

A felhasználók futtathatják a SP_SPACEUSED-t és a DBCC PDW_SHOWSPACEUSED-t , hogy meghatározzák, mennyi helyet foglal el egy materializált nézet. Vannak DMV-k is, amelyek testreszabhatóbb lekérdezéseket biztosítanak a felhasznált helyek és sorok azonosítására. További információért lásd: Táblázatméret-lekérdezések.

A materializált nézet a DROP VIEW segítségével is eldobható. Az ALTER MATERIALIZED VIEW segítségével kikapcsolhatod vagy újraépítheted a materializált nézetet.

A materializált nézet egy automatikus lekérdezésoptimalizálási mechanizmus. A felhasználóknak nem kell közvetlenül megkérdezniük a materializált nézetet. Amikor felhasználói lekérdezést küldenek, a motor ellenőrzi a felhasználó jogosultságait a lekérdezési objektumokhoz, és a lekérdezés végrehajtás nélkül bukja meg, ha a felhasználónak nincs hozzáférése a táblákhoz vagy a lekérdezés normál nézeteihez. Ha a felhasználó engedélyét ellenőrizték, az optimalizáló automatikusan egy megfelelő materializált nézetet használ a lekérdezés végrehajtására gyorsabb teljesítmény érdekében. A felhasználók ugyanazt az adatot kapják vissza, függetlenül attól, hogy a lekérdezést az alaptáblák vagy a materializált nézet lekérdezésével szolgálják.

A EXPLAIN terv és a grafikus Becsült Végrehajtási Terv az SQL Server Management Studio-ban megmutatja, hogy a lekérdezésoptimalizáló figyelembe veszi-e egy materializált nézetet a lekérdezés végrehajtásához, míg az SQL Server Management Studio grafikus becslés végrehajtási terve megmutatja, hogy a lekérdezésoptimalizáló figyelembe veszi-e egy materializált nézetet a lekérdezés végrehajtásához.

Annak megismeréséhez, hogy egy SQL utasítás profitálhat-e egy új materializált nézetből, futtasd le a EXPLAIN parancsot a .WITH_RECOMMENDATIONS Részletekért lásd: MAGYARÁZAT (Transact-SQL).

Tulajdonjog

• Materializált nézet nem hozható létre, ha az alaptáblák tulajdonosai és a -created to-bematerializált nézet nem ugyanazok.
• A materializált nézet és annak alaptáblái különböző sémáikban találhatók. Amikor a materializált nézet létrejött, a nézet séma tulajdonosa automatikusan a materializált nézet tulajdonosává válik, és ez a tekintet tulajdonjoga nem változtatható.

Permissions

A felhasználónak a következő jogosultságokra van szüksége a materializált nézet létrehozásához az objektum tulajdonjogának követelményei mellett:

1. CREATE VIEW engedély az adatbázisban
2. SELECT engedély a materializált nézet alaptábláin
3. HIVATKOZÁSI engedély az alaptáblákat tartalmazó sémához
4. ALTER engedély a sémára, amely tartalmazza a materializált nézetet

Example

A. Ez a példa bemutatja, hogyan használja a Synapse SQL optimalizáló automatikusan a materializált nézeteket a lekérdezés végrehajtására jobb teljesítmény érdekében, még akkor is, ha a lekérdezés olyan funkciókat használ, amelyeket a CREATE MATERIALIZED VIEW nem támogat, például COUNT(DISTINCT expression). Egy lekérdezés, amely korábban több másodpercet vett igénybe, most kevesebb másodperc alatt fejeződik be anélkül, hogy a felhasználói lekérdezés változást okozna.

-- 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. Ebben a példában a User2 egy materializált nézetet hoz létre a User1 tulajdonában lévő táblákon. A materializált nézet a User1 tulajdona.

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

Lásd még

• ALTER MATERIALIZÁLT NÉZET (Transact-SQL)
• LEDOBÓ NÉZET
• MAGYARÁZD EL (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

Következő lépések

• Teljesítményhangolás Materializált Nézettel