Azure Synapse Analytics での Synapse SQL を使用したテーブルの設計

[アーティクル]
06/01/2023

このドキュメントでは、専用 SQL プールとサーバーレス SQL プールによるテーブル設計の主要な概念について説明します。

サーバーレス SQL プールは、データレイク内のデータに対するクエリサービスです。これには、データインジェスト用のローカルストレージはありません。専用 SQL プールは、Synapse SQL を使用するときにプロビジョニングされる分析リソースのコレクションを表します。専用 SQL プールのサイズは、Data Warehouse ユニット (DWU) によって決まります。

次の表は、専用 SQL プールとサーバーレス SQL プールに関連するトピックを示しています。

トピック	専用 SQL プール	サーバーレス SQL プール
テーブルカテゴリを決定する	はい	いいえ
スキーマ名	はい	はい
テーブル名	はい	いいえ
テーブルの永続性	はい	いいえ
通常のテーブル	はい	いいえ
一時テーブル	はい	はい
外部テーブル	はい	はい
データ型	はい	はい
分散テーブル	はい	いいえ
ハッシュ分散テーブル	はい	いいえ
レプリケートテーブル	はい	いいえ
ラウンドロビンテーブル	はい	いいえ
テーブルの一般的な分散方法	はい	いいえ
パーティション	はい	はい
列ストアインデックス	はい	いいえ
統計	はい	はい
主キーと一意キー	はい	いいえ
テーブルを作成するためのコマンド	はい	いいえ
ソースデータをデータウェアハウスに配置する	はい	いいえ
サポートされていないテーブルの機能	はい	いいえ
テーブルサイズのクエリ	はい	いいえ

テーブルカテゴリを決定する

スタースキーマは、データをファクトテーブルとディメンションテーブルに編成します。一部のテーブルは、ファクトテーブルまたはディメンションテーブルに移動する前に統合またはステージングデータに使用されます。テーブルを設計する際には、テーブルのデータがファクト、ディメンション、統合のいずれのテーブルに属するかを決定します。この決定は、適切なテーブル構造体および配布を通知します。

ファクトテーブルには、一般にトランザクションシステムで生成された後、データウェアハウスに読み込まれる定量的データが含まれています。たとえば、小売業では販売トランザクションを毎日生成した後、そのデータを分析のためにデータウェアハウスファクトテーブルに読み込みます。
ディメンションテーブルには、変化する可能性はあるが通常は変更頻度が低い属性データが含まれます。たとえば、顧客の名前と住所はディメンションテーブルに格納され、その顧客のプロファイルが変更された場合にのみ更新されます。大規模なファクトテーブルのサイズを最小限に抑えるために、ファクトテーブルのすべての行に顧客の名前と住所を格納する必要はありません。代わりに、ファクトテーブルとディメンションテーブルで顧客 ID を共有できます。クエリで 2 つのテーブルを結合して、顧客のプロファイルとトランザクションに関連付けることができます。
統合テーブルは、統合またはステージングデータの場所を提供します。統合テーブルは、通常のテーブル、外部テーブル、または一時テーブルとして作成できます。たとえば、ステージングテーブルにデータを読み込み、ステージングでデータの変換を実行してから、データを運用環境テーブルに挿入できます。

スキーマ名

スキーマは、同様の方法で使用されるオブジェクトをグループ化するのに適しています。次のコードは、wwi と呼ばれるユーザー定義スキーマを作成します。

CREATE SCHEMA wwi;

テーブル名

複数のデータベースをオンプレミスソリューションから専用 SQL プールに移行する場合、ベストプラクティスとして、ファクトテーブル、ディメンションテーブル、および統合テーブルのすべてを 1 つの SQL プールスキーマに移行することをお勧めします。たとえば、すべてのテーブルを、wwi と呼ばれる 1 つのスキーマ内の WideWorldImportersDW サンプルデータウェアハウスに格納できます。

専用 SQL プール内のテーブルの構成を表示するには、テーブル名のプレフィックスとして fact、dim、および int を使用できます。次の表に、WideWorldImportersDW のスキーマ名とテーブル名の一部を示します。

WideWorldImportersDW テーブル	テーブルの種類です。	専用 SQL プール
City	Dimension	wwi.DimCity
Order	ファクト	wwi.FactOrder

テーブルの永続性

テーブルは、データを Azure Storage に永続的に格納するか、Azure Storage に一時的に格納するか、またはデータウェアハウスの外部にあるデータストアに格納します。

通常のテーブル

通常のテーブルは、データウェアハウスの一部として Azure Storage にデータを格納します。テーブルとデータは、セッションが開かれているかどうかにかかわらず保持されます。次の例では、2 つの列を含む通常のテーブルを作成します。

CREATE TABLE MyTable (col1 int, col2 int );

一時テーブル

一時テーブルは、セッション中のみ存在します。一時テーブルを使用して、一時的な結果が他のユーザーに表示されないようにすることができます。一時テーブルを使用すると、クリーンアップの必要性も低減されます。一時テーブルではローカルストレージが利用されるため、専用 SQL プールではパフォーマンスを向上させることができます。

サーバーレス SQL プールでは、一時テーブルがサポートされています。しかし、一時テーブルから選択することはできますが、ストレージ内のファイルと結合できないため、使用は制限されます。

詳しくは、一時テーブルに関する記事をご覧ください。

外部テーブル

外部テーブルは、Azure Storage BLOB または Azure Data Lake Store 内にあるデータを指します。

データを外部テーブルから専用 SQL プールにインポートするには、CREATE TABLE AS SELECT ステートメントを使用します。読み込みのチュートリアルについては、「PolyBase を使用して Azure Blob Storage から Azure SQL Data Warehouse にデータを読み込む」をご覧ください。

サーバーレス SQL プールでは、CETAS を使用してクエリの結果を Azure Storage 内の外部テーブルに保存できます。

データ型

専用 SQL プールでは、最も一般的に使用されるデータ型がサポートされています。サポートされるデータ型の一覧については、CREATE TABLE ステートメントの「data types in CREATE TABLE reference (CREATE TABLE 内のデータ型のリファレンス)」を参照してください。データ型の使用の詳細については、データ型に関するページをご覧ください。

分散テーブル

専用 SQL プールの基本的な特徴は、テーブルを複数のディストリビューションにわたって格納し、操作できる方法にあります。専用 SQL プールでは、データを分散させるための次の 3 つの方法がサポートされます。

ラウンドロビン (既定)
ハッシュインデックス
レプリケート

ハッシュ分散テーブル

ハッシュ分散テーブルは、ディストリビューション列内の値に基づいて行を分散します。ハッシュ分散テーブルは、大規模なテーブルのクエリでハイパフォーマンスを達成するように設計されています。ディストリビューション列を選択する際に検討すべきいくつかの要素があります。

詳細については、「Design guidance for distributed tables (分散テーブルの設計ガイダンス)」を参照してください。

レプリケートテーブル

レプリケートテーブルには、すべてのコンピューティングノードで使用可能なテーブルの完全なコピーが含まれています。レプリケートテーブルの結合ではデータ移動は必要ないため、レプリケートテーブルに対するクエリは高速に実行されます。ただし、レプリケーションには余分なストレージが必要であるため、大きなテーブルには適していません。

詳しくは、レプリケートテーブルを使用するための設計ガイダンスに関する記事をご覧ください。

ラウンドロビンテーブル

ラウンドロビンテーブルは、すべてのディストリビューションにわたって均等にテーブル行を分散させます。これらの行は、ランダムに分散されます。ラウンドロビンテーブルへのデータの読み込みは高速です。ただし、クエリは、他の分散方法より多くのデータ移動を必要とする可能性があります。

詳細については、「Design guidance for distributed tables (分散テーブルの設計ガイダンス)」を参照してください。

テーブルの一般的な分散方法

多くの場合、テーブルカテゴリによって、テーブルの最適な分散オプションが決定されます。

テーブルカテゴリ	推奨される分散オプション
ファクト	クラスター化列ストアインデックスによるハッシュ分散を使用します。 2 つのハッシュテーブルが同じディストリビューション列に結合される場合にパフォーマンスが向上します。
Dimension	小さなテーブルにはレプリケートを使用します。各コンピューティングノードに保存するにはテーブルが大きすぎる場合は、ハッシュ分散を使用します。
ステージング	ステージングテーブルにはラウンドロビンを使用します。 CTAS での読み込みが高速です。データがステージングテーブルに格納されたら、INSERT...SELECT を使用してデータを運用環境テーブルに移動します。

メジャーグループ

専用 SQL プールでは、パーティションテーブルにより、データ範囲に基づいてテーブル行が格納され、操作が実行されます。たとえば、day、month、または year でテーブルをパーティション分割できます。クエリスキャンをあるパーティション内のデータに制限するパーティション除外によってクエリパフォーマンスを向上させることができます。

パーティションを切り替えてデータを維持することもできます。専用 SQL プールのデータは既に分散されているため、パーティションが多すぎるとクエリパフォーマンスが低下することがあります。詳しくは、パーティション分割のガイダンスに関する記事をご覧ください。

ヒント

空でないテーブルパーティションにパーティションを切り替えるとき、既存のデータが切り詰められる場合は、ALTER TABLE ステートメントに TRUNCATE_TARGET オプションを使用することを検討してください。

次のコードでは、変換された日次データを SalesFact パーティションに切り替えて、既存のデータを上書きします。

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);

サーバーレス SQL プールでは、クエリで読み取られるファイルまたはフォルダー (パーティション) を制限できます。パスによるパーティション分割は、ストレージファイルに対するクエリに関するページで説明されている filepath 関数と fileinfo 関数を使用することでサポートされます。次の例では、2017 年のデータを含むフォルダーを読み取ります。

SELECT
    nyc.filepath(1) AS [year],
    payment_type,
    SUM(fare_amount) AS fare_total
FROM  
    OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS nyc
WHERE
    nyc.filepath(1) = 2017
GROUP BY
    nyc.filepath(1),
    payment_type
ORDER BY
    nyc.filepath(1),
    payment_type

列ストアインデックス

既定では、専用 SQL プールには、テーブルがクラスター化列ストアインデックスとして格納されます。この形式のデータストレージでは、大きなテーブルに対する高いデータ圧縮およびクエリパフォーマンスが実現されます。クラスター化列ストアインデックスは、通常は最適な選択肢ですが、場合によっては、クラスター化インデックスまたはヒープが適切なストレージ構造体の場合もあります。

ヒント

ヒープテーブルは、最終的なテーブルに変換されるステージングテーブルなどの一時的なデータを読み込むのに特に役立ちます。

列ストア機能の一覧については列ストアインデックスの新機能に関する記事をご覧ください。列ストアインデックスのパフォーマンスを向上させるには、列ストアインデックスの行グループの品質の最大化に関する記事をご覧ください。

統計

クエリオプティマイザーでは、クエリ実行のプランの作成時に列レベルの統計が使用されます。クエリのパフォーマンスを向上させるには、個々の列、特にクエリの結合で使用される列の統計を作成することが重要です。 Synapse SQL では、統計の自動作成がサポートされます。

統計の更新は自動的には行われません。大量の行が追加または変更された後に統計を更新します。たとえば、読み込みの後に統計を更新します。詳細については、統計のガイダンスに関する記事をご覧ください。

主キーと一意キー

専用 SQL プールでは、PRIMARY KEY は、NONCLUSTERED と NOT ENFORCED の両方が使用されている場合にのみサポートされます。 UNIQUE 制約は、NOT ENFORCED が使用されている場合にのみサポートされます。詳細については、専用 SQL プールのテーブル制約に関するページを参照してください。

テーブルを作成するためのコマンド

専用 SQL プールでは、テーブルは新しい空のテーブルとして作成することができます。テーブルを作成し、SELECT ステートメントの結果を使用して値を設定することもできます。テーブルを作成するための T-SQL コマンドを次に示します。

T-SQL ステートメント	説明
CREATE TABLE	すべてのテーブル列およびオプションを定義して空のテーブルを作成します。
CREATE EXTERNAL TABLE	外部テーブルを作成します。テーブルの定義は、専用 SQL プールに格納されます。テーブルデータは Azure Blob Storage または Azure Data Lake Storage に格納されます。
CREATE TABLE AS SELECT	SELECT ステートメントの結果を使用して新しいテーブルに値が設定されます。テーブルの列とデータ型は、SELECT ステートメントの結果に基づきます。データをインポートするには、このステートメントで外部テーブルから選択できます。
CREATE EXTERNAL TABLE AS SELECT	外部の場所に SELECT ステートメントの結果をエクスポートして、新しい外部テーブルを作成します。その場所は Azure Blob Storage または Azure Data Lake Storage のいずれかです。

ソースデータをデータウェアハウスに配置する

専用 SQL プールテーブルは、別のデータソースからデータを読み込むことで設定されます。読み込みを正常に完了させるには、ソースデータ内の列の数とデータ型が、データウェアハウス内のテーブル定義と合致している必要があります。

注意

配置するデータの取得は、テーブルの設計の最大の難関となる可能性があります。

データを複数のデータストアから読み込む場合、データをデータウェアハウスに移植して、統合テーブルに格納できます。データが統合テーブルに格納されたら、専用 SQL プールの機能を使用して変換操作を実行できます。データの準備ができたら、それを運用テーブルに挿入できます。

サポートされていないテーブルの機能

専用 SQL プールでは、他のデータベースで提供されるテーブル機能の多くがサポートされますが、すべてサポートされるわけではありません。次の一覧は、専用 SQL プールでサポートされていないテーブル機能の一部を示しています。

外部キー (テーブル制約に関するページを確認してください)
計算列
インデックス付きビュー
Sequence
スパース列
代理キー (IDENTITY を使用して実装)
シノニム
トリガー
一意のインデックス
ユーザー定義型

テーブルサイズのクエリ

専用 SQL プールにおいて、60 個の各ディストリビューション内のテーブルで使用される領域と行を簡単に識別する方法の 1 つは、DBCC PDW_SHOWSPACEUSED を使用することです。

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

ただし、DBCC コマンドの使用はかなり制限される可能性があることに注意してください。動的管理ビュー (DMV) には、DBCC コマンドよりも詳しい情報が表示されます。まず、次のビューを作成します。

CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
 GETDATE()                                                             AS  [execution_time]
, DB_NAME()                                                            AS  [database_name]
, s.name                                                               AS  [schema_name]
, t.name                                                               AS  [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name)                              AS  [two_part_name]
, nt.[name]                                                            AS  [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL))     AS  [node_table_name_seq]
, tp.[distribution_policy_desc]                                        AS  [distribution_policy_name]
, c.[name]                                                             AS  [distribution_column]
, nt.[distribution_id]                                                 AS  [distribution_id]
, i.[type]                                                             AS  [index_type]
, i.[type_desc]                                                        AS  [index_type_desc]
, nt.[pdw_node_id]                                                     AS  [pdw_node_id]
, pn.[type]                                                            AS  [pdw_node_type]
, pn.[name]                                                            AS  [pdw_node_name]
, di.name                                                              AS  [dist_name]
, di.position                                                          AS  [dist_position]
, nps.[partition_number]                                               AS  [partition_nmbr]
, nps.[reserved_page_count]                                            AS  [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count]                    AS  [unused_space_page_count]
, nps.[in_row_data_page_count]
    + nps.[row_overflow_used_page_count]
    + nps.[lob_used_page_count]                                        AS  [data_space_page_count]
, nps.[reserved_page_count]
 - (nps.[reserved_page_count] - nps.[used_page_count])
 - ([in_row_data_page_count]
         + [row_overflow_used_page_count]+[lob_used_page_count])       AS  [index_space_page_count]
, nps.[row_count]                                                      AS  [row_count]
from
    sys.schemas s
INNER JOIN sys.tables t
    ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
    ON  t.[object_id] = i.[object_id]
    AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
    ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
    ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
    ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
    ON  nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
    ON  nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
    ON nt.[object_id] = nps.[object_id]
    AND nt.[pdw_node_id] = nps.[pdw_node_id]
    AND nt.[distribution_id] = nps.[distribution_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
    ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
    ON cdp.[object_id] = c.[object_id]
    AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
   [execution_time]
,  [database_name]
,  [schema_name]
,  [table_name]
,  [two_part_name]
,  [node_table_name]
,  [node_table_name_seq]
,  [distribution_policy_name]
,  [distribution_column]
,  [distribution_id]
,  [index_type]
,  [index_type_desc]
,  [pdw_node_id]
,  [pdw_node_type]
,  [pdw_node_name]
,  [dist_name]
,  [dist_position]
,  [partition_nmbr]
,  [reserved_space_page_count]
,  [unused_space_page_count]
,  [data_space_page_count]
,  [index_space_page_count]
,  [row_count]
,  ([reserved_space_page_count] * 8.0)                                 AS [reserved_space_KB]
,  ([reserved_space_page_count] * 8.0)/1000                            AS [reserved_space_MB]
,  ([reserved_space_page_count] * 8.0)/1000000                         AS [reserved_space_GB]
,  ([reserved_space_page_count] * 8.0)/1000000000                      AS [reserved_space_TB]
,  ([unused_space_page_count]   * 8.0)                                 AS [unused_space_KB]
,  ([unused_space_page_count]   * 8.0)/1000                            AS [unused_space_MB]
,  ([unused_space_page_count]   * 8.0)/1000000                         AS [unused_space_GB]
,  ([unused_space_page_count]   * 8.0)/1000000000                      AS [unused_space_TB]
,  ([data_space_page_count]     * 8.0)                                 AS [data_space_KB]
,  ([data_space_page_count]     * 8.0)/1000                            AS [data_space_MB]
,  ([data_space_page_count]     * 8.0)/1000000                         AS [data_space_GB]
,  ([data_space_page_count]     * 8.0)/1000000000                      AS [data_space_TB]
,  ([index_space_page_count]  * 8.0)                                   AS [index_space_KB]
,  ([index_space_page_count]  * 8.0)/1000                              AS [index_space_MB]
,  ([index_space_page_count]  * 8.0)/1000000                           AS [index_space_GB]
,  ([index_space_page_count]  * 8.0)/1000000000                        AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;

テーブル領域の概要

次のクエリはテーブルごとに行と領域を返します。テーブル領域の概要では、どのテーブルが最大のテーブルであるかが表示されます。また、テーブルが、ラウンドロビン、レプリケート、またはハッシュ分散のいずれであるかも表示されます。ハッシュ分散テーブルの場合、クエリによってディストリビューション列が表示されます。

SELECT
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
,    COUNT(distinct partition_nmbr) as nbr_partitions
,    SUM(row_count)                 as table_row_count
,    SUM(reserved_space_GB)         as table_reserved_space_GB
,    SUM(data_space_GB)             as table_data_space_GB
,    SUM(index_space_GB)            as table_index_space_GB
,    SUM(unused_space_GB)           as table_unused_space_GB
FROM
    dbo.vTableSizes
GROUP BY
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
ORDER BY
    table_reserved_space_GB desc
;

ディストリビューションの種類別のテーブル領域

SELECT
     distribution_policy_name
,    SUM(row_count)                as table_type_row_count
,    SUM(reserved_space_GB)        as table_type_reserved_space_GB
,    SUM(data_space_GB)            as table_type_data_space_GB
,    SUM(index_space_GB)           as table_type_index_space_GB
,    SUM(unused_space_GB)          as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;

インデックスの種類別のテーブル領域

SELECT
     index_type_desc
,    SUM(row_count)                as table_type_row_count
,    SUM(reserved_space_GB)        as table_type_reserved_space_GB
,    SUM(data_space_GB)            as table_type_data_space_GB
,    SUM(index_space_GB)           as table_type_index_space_GB
,    SUM(unused_space_GB)          as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;

ディストリビューション領域の概要

SELECT
    distribution_id
,    SUM(row_count)                as total_node_distribution_row_count
,    SUM(reserved_space_MB)        as total_node_distribution_reserved_space_MB
,    SUM(data_space_MB)            as total_node_distribution_data_space_MB
,    SUM(index_space_MB)           as total_node_distribution_index_space_MB
,    SUM(unused_space_MB)          as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY     distribution_id
ORDER BY    distribution_id
;

次のステップ

データウェアハウスにテーブルを作成した後、次の手順はテーブルへのデータの読み込みです。読み込みのチュートリアルについては、専用 SQL プールへのデータの読み込みに関するページを参照してください。

次の方法で共有

Azure Synapse Analytics での Synapse SQL を使用したテーブルの設計

テーブルカテゴリを決定する

スキーマ名

テーブル名

テーブルの永続性

通常のテーブル

一時テーブル

外部テーブル

データ型

分散テーブル

ハッシュ分散テーブル

レプリケートテーブル

ラウンドロビンテーブル

テーブルの一般的な分散方法

メジャーグループ

列ストアインデックス

統計

主キーと一意キー

テーブルを作成するためのコマンド

ソースデータをデータウェアハウスに配置する

サポートされていないテーブルの機能

テーブルサイズのクエリ

テーブル領域の概要

ディストリビューションの種類別のテーブル領域

インデックスの種類別のテーブル領域

ディストリビューション領域の概要

次のステップ

フィードバック

その他のリソース

次の方法で共有

Azure Synapse Analytics での Synapse SQL を使用したテーブルの設計

テーブル カテゴリを決定する

スキーマ名

テーブル名

テーブルの永続性

通常のテーブル

一時テーブル

外部テーブル

データ型

分散テーブル

ハッシュ分散テーブル

レプリケート テーブル

ラウンドロビン テーブル

テーブルの一般的な分散方法

メジャー グループ

列ストア インデックス

統計

主キーと一意キー

テーブルを作成するためのコマンド

ソース データをデータ ウェアハウスに配置する

サポートされていないテーブルの機能

テーブル サイズのクエリ

テーブル領域の概要

ディストリビューションの種類別のテーブル領域

インデックスの種類別のテーブル領域

ディストリビューション領域の概要

次のステップ

フィードバック

その他のリソース

テーブルカテゴリを決定する

レプリケートテーブル

ラウンドロビンテーブル

メジャーグループ

列ストアインデックス

ソースデータをデータウェアハウスに配置する

テーブルサイズのクエリ