CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

適用対象:Azure Synapse Analytics

この記事では、ソリューション開発用の Azure Synapse Analytics の CREATE MATERIALIZED VIEW AS SELECT T-SQL ステートメントについて説明します。 この記事には、コード例も記載されています。

具体化されたビューでは、ビュー定義クエリから返されるデータを保持し、基になるテーブルのデータが変更されると自動的に更新されます。 これによって、複雑なクエリ (一般に結合と集計を含むクエリ) のパフォーマンスが向上すると共に、メンテナンス操作が簡単になります。 実行プランの自動一致機能により、置換するビューをオプティマイザーで検討する際、具体化されたビューをクエリで参照する必要はありません。 この機能により、データ エンジニアは、クエリを変更せずにクエリの応答時間を短縮するメカニズムとして、具体化されたビューを実装できます。

Transact-SQL 構文表記規則

構文

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

Note

この構文は、Azure Synapse Analytics のサーバーレス SQL プールでサポートされていません。

引数

schema_name

ビューが所属するスキーマの名前を指定します。

materialized_view_name

ビューの名前です。 ビュー名は、識別子のルールに従っている必要があります。 ビューの所有者名の指定は省略可能です。

distribution option

サポートされる分布は、HASH および ROUND_ROBIN のみです。 分散オプションの詳細については、CREATE TABLE の「テーブル分散オプション」を参照してください。 実際の使用状況やサンプル クエリに基づきテーブルに対してどの分散が選択されるかに関する推奨事項については、「Azure Synapse SQL の分散アドバイザー」を参照してください。

DISTRIBUTION = HASH ( distribution_column_name )
1 つの列の値に基づいて行を分散します。

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) 最大 8 列のハッシュ値に基づいて行を分散し、具体化されたビュー データのより均等な分散を可能にし、時間の経過と共にデータ スキューを減らし、クエリのパフォーマンスを向上させます。

注意

• 複数列分散機能を有効にするには、このコマンドを使ってデータベースの互換レベルを 50 に変更します。 データベース互換レベルの設定について詳しくは、「ALTER DATABASE SCOPED CONFIGURATION」をご覧ください。 例: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• MCD を無効にするには、このコマンドを実行してデータベースの互換レベルを AUTO に変更します。 例: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; 既存の MCD の具体化されたビューはそのままですが、読み取り不能になります。
  • MCD の具体化されたビューに再びアクセスできるようにするには、もう一度機能を有効にします。

select_statement

具体化されたビューの定義の SELECT リストでは、以下の 2 つの条件の 1 つ以上が満たされている必要があります。

• SELECT リストに集計関数が含まれています。
• 具体化されたビューの定義で GROUP BY が使用されており、GROUP BY 内のすべての列が SELECT リストに含まれています。 GROUP BY 句では、最大 32 列使用できます。

具体化されたビューの定義の SELECT リストには、集計関数が必要です。 サポートされる集計には、MAX、MIN、AVG、COUNT、COUNT_BIG、SUM、VAR、STDEV が含まれます。

具体化されたビューの定義の SELECT リストで MIN/MAX 集計が使用される場合、以下の要件が適用されます。

• FOR_APPEND は必須です。 次に例を示します。

  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
  

• 参照されるベース テーブルで UPDATE または DELETE が実行されると、具体化されたビューが無効になります。  この制限は、INSERT には適用されません。  具体化されたビューを再度有効にするには、REBUILD を指定して ALTER MATERIALIZED VIEW を実行します。

注釈

Azure データ ウェアハウスの具体化されたビューは、SQL Server のインデックス付きビューに似ています。  具体化されたビューで集計関数がサポートされる点を除き、インデックス付きビューとほぼ同じ制限が共有されています (詳細については、「Create Indexed Views (インデックス付きビューを作成する)」 を参照してください)。  

注意

CREATE MATERIALIZED VIEW で COUNT、DISTINCT、COUNT (DISTINCT expression)、COUNT_BIG (DISTINCT expression) はサポートされていませんが、これらの関数を指定した SELECT クエリを使用すると、具体化されたビューの利点が得られ、パフォーマンスをより高速化することができます。これは、Synapse SQL オプティマイザーを使用すると、既存の具体化されたビューと一致するようにユーザー クエリの集計を自動的に再作成できるためです。 詳細については、この記事の例のセクションを確認してください。

CREATE MATERIALIZED VIEW AS SELECT で APPROX_COUNT_DISTINCT はサポートされません。

具体化されたビューでは、CLUSTERED COLUMNSTORE INDEX のみがサポートされます。

具体化されたビューでは、他のビューを参照できません。

DDM 列が具体化されたビューの一部でない場合でも、動的データ マスク (DDM) を使用するテーブルに具体化されたビューを作成することはできません。 テーブル列が、アクティブな具体化されたビューの一部であるか、無効な具体化されたビューの一部である場合、DDM をこの列に追加することはできません。

行レベルのセキュリティが有効になっているテーブルには具体化されたビューを作成できません。

具体化されたビューは、パーティション テーブル上で作成できます。  パーティションの SPLIT/MERGE は、具体化されたビューのベース テーブルでサポートされていますが、パーティションの SWITCH はサポートされていません。

具体化されたビューで参照されるテーブル上では、ALTER TABLE SWITCH はサポートされません。 ALTER TABLE SWITCH を使用する前に、具体化されたビューを無効にするか、ドロップします。 以下のシナリオでは、具体化されたビューを作成する際、このビューに新しい列を追加する必要があります。

シナリオ	具体化されたビューに追加する新しい列	コメント
具体化されたビューの定義の SELECT リストに COUNT_BIG() が欠落しています	COUNT_BIG (*)	具体化されたビューを作成する際に自動的に追加されます。 ユーザーによる操作は不要です。
具体化されたビューの定義の SELECT リストでユーザーが SUM(a) を指定しています。また、"a" は null 許容式です	COUNT_BIG (a)	ユーザーは、具体化されたビューの定義で式 "a" を手動で追加する必要があります。
具体化されたビューの定義の SELECT リストでユーザーが AVG(a) を指定しています。ここで "a" は式です。	SUM(a), COUNT_BIG(a)	具体化されたビューを作成する際に自動的に追加されます。 ユーザーによる操作は不要です。
具体化されたビューの定義の SELECT リストでユーザーが STDEV(a) を指定しています。ここで "a" は式です。	SUM(a), COUNT_BIG(a), SUM(square(a))	具体化されたビューを作成する際に自動的に追加されます。 ユーザーによる操作は不要です。

具体化されたビューは、一度作成されると、SQL Server Management Studio 内の Azure Synapse Analytics インスタンスのビュー フォルダーの下に表示されます。

ユーザーは SP_SPACEUSED および DBCC PDW_SHOWSPACEUSED を実行して、具体化されたビューによって使用されている領域を確認できます。 また、使用される領域と行を識別するための、よりカスタマイズ可能なクエリを提供する DMV もあります。 詳細については、「テーブル サイズのクエリ」を参照してください。

具体化されたビューは、DROP VIEW でドロップできます。 ALTER MATERIALIZED VIEW を使用して、具体化されたビューを無効にしたり、リビルドしたりできます。

具体化されたビューは、自動クエリ最適化メカニズムです。 ユーザーは、具体化されたビューに直接クエリを実行する必要はありません。 ユーザー クエリが送信されると、エンジンがクエリ オブジェクトに対するユーザーのアクセス許可を確認し、ユーザーがクエリでテーブルまたは通常のビューにアクセスできない場合は、クエリが実行することなく失敗します。 ユーザーのアクセス許可が確認された場合、オプティマイザーが、対応する具体化されたビューを自動的に使用してクエリを実行し、パフォーマンスを向上させることができます。 ユーザーは、クエリの実行がベース テーブルと具体化されたビューのどちらのクエリによって行われるかに関係なく、同じデータを返します。

SQL Server Management Studio の EXPLAIN プランとグラフィカルな推定実行プランでは、具現化されたビューがクエリ オプティマイザーによってクエリ実行のために考慮されるかどうかがわかります。また、SQL Server Management Studio のグラフィカルな推定実行プランでは、具現化されたビューがクエリ オプティマイザーによってクエリ実行のために考慮されるかどうかがわかります。

新しい具体化されたビューから SQL ステートメントでメリットが得られるかどうかを確認するには、WITH_RECOMMENDATIONS を指定して EXPLAIN コマンドを実行します。 詳細については、「EXPLAIN (Transact-SQL)」を参照してください。

所有権

• ベース テーブルの所有者と、作成する具体化されたビューが同じでない場合は具体化されたビューを作成できません。
• 具体化されたビューとそのベース テーブルは、異なるスキーマに配置できます。 具体化されたビューを作成すると、ビューのスキーマ所有者が自動的に具体化されたビューの所有者になり、このビューの所有権を変更することはできません。

アクセス許可

具体化されたビューを作成するには、オブジェクトの所有権の要件を満たすことに加えて、次の権限が必要です。

1. データベースの CREATE VIEW 権限
2. 具体化されたビューのベース テーブルに対する SELECT 権限
3. ベース テーブルを含むスキーマに対する REFERENCES 権限
4. 具体化されたビューを含むスキーマに対する ALTER 権限

例

A. この例には、COUNT(DISTINCT expression) など、CREATE MATERIALIZED VIEW でサポートされていない関数をクエリで使用する場合でも、具体化されたビューを Synapse SQL オプティマイザーで自動的に使用してクエリを実行し、パフォーマンスを向上させる方法が示されています。 これまで完了するのに数秒かかっていたクエリが、ユーザー クエリに変更を加えることなく 1 秒未満で終了するようになりました。

-- 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. この例では、User1 が所有するテーブルに User2 が具体化されたビューを作成します。 具体化されたビューは 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

関連項目

• ALTER MATERIALIZED VIEW (Transact-SQL)
• DROP VIEW
• EXPLAIN (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 と Analytics Platform System (PDW) カタログ ビュー
• Azure Azure Synapse Analytics でサポートされているシステム ビュー
• Azure Azure Synapse Analytics でサポートされている T-SQL ステートメント

次のステップ

• 具体化されたビューを使用したパフォーマンス チューニング