データ ウェアハウス テーブルの作成

• 10 分

リレーショナル データ ウェアハウス スキーマの基本的なアーキテクチャ原則を理解したので、次にデータ ウェアハウスを作成する方法について説明します。

専用 SQL プールの作成

Azure Synapse Analytics でリレーショナル データ ウェアハウスを作成するには、専用 SQL プールを作成する必要があります。 既存の Azure Synapse Analytics ワークスペースでこれを行う最も簡単な方法は、次に示すように、Azure Synapse Studio の [管理] ページを使用することです。

専用 SQL プールをプロビジョニングするときには、次の構成設定を指定できます。

• 専用 SQL プールの一意の名前。
• SQL プールのパフォーマンス レベル。DW100c から DW30000c までの範囲で、プールの実行時の 1 時間あたりのコストを決定します。
• 空のプールから開始するか、バックアップから既存のデータベースを復元するか。
• SQL プールの "照合順序"。データベースの並べ替え順序と文字列比較規則を決定します。 ("作成後に照合順序を変更することはできません")。

専用 SQL プールを作成した後で、Synapse Studio の [管理] ページでその実行状態を制御できます。必要ない場合は一時停止して、不要なコストを防ぎます。

プールが実行中である場合は、[データ] ページでプールを探索し、そこで実行する SQL スクリプトを作成することができます。

テーブルの作成に関する注意事項

専用 SQL プールにテーブルを作成するには、CREATE TABLE (または場合によっては CREATE EXTERNAL TABLE) Transact-SQL ステートメントを使用します。 ステートメントで使用される個別のオプションは、作成するテーブルの種類によって異なります。これには、次の種類が含まれます。

• ファクト テーブル
• ディメンション テーブル
• ステージング テーブル

小規模または中規模のデータセットのスター スキーマ モデルを設計するときは、Azure SQL などの好みのデータベースを使用できます。 大規模なデータセットの場合は、SQL Server ではなく Azure Synapse Analytics にデータウェア ハウスを実装するとメリットがあります。 Synapse Analytics でテーブルを作成するときには、いくつかの主要な違いを理解しておくことが重要です。

データ整合性の制約

Synapse Analytics 内の専用 SQL プールでは、SQL Server などの他のリレーショナル データベース システムで見られる "外部キー" と "一意" 制約はサポートされていません。 つまり、データの読み込みに使用されるジョブは、データベース内のテーブル定義に依存することなく、キーの一意性と参照整合性を維持する必要があります。

インデックス

Synapse Analytics の専用 SQL プールでは、SQL Server で見られる "クラスター化" インデックスがサポートされますが、既定のインデックスの種類は "クラスター化列ストア" です。 このインデックスの種類は、一般的なデータ ウェアハウス スキーマ内の大量のデータに対してクエリを実行する場合にパフォーマンス上の大きな利点があり、可能な限り使用する必要があります。 ただし、一部のテーブルには、クラスター化列ストア インデックスに含めることができないデータ型 (VARBINARY(MAX) など) が含まれている場合があります。その場合は、クラスター化インデックスを代わりに使用できます。

Distribution

ほとんどの OLTP データベース システムでは、対称型マルチプロセッシング (SMP) アーキテクチャが使用されるのに対して、Azure Synapse Analytics の専用 SQL プールでは、超並列処理 (MPP) アーキテクチャが使用されます。 MPP システムでは、テーブル内のデータは、プールの複数のノード全体で処理するために分散されます。 Synapse Analytics では、次の種類の分散がサポートされています。

• ハッシュ: 指定された列に対して、決定論的ハッシュ値が計算され、コンピューティング ノードに行を割り当てるために使用されます。
• ラウンド ロビン: すべてのコンピューティング ノードに行が均等に分散されます。
• レプリケート: 各コンピューティング ノードにテーブルのコピーが保存されます。

テーブルの種類は、多くの場合、テーブルの分散について選択するオプションを決定します。

テーブルの種類です。	推奨される分散オプション
Dimension	小さいテーブルの場合は、レプリケート分散を使用して、分散されたファクト テーブルに結合するときのデータ シャッフルを回避します。 各コンピューティング ノードに保存するにはテーブルが大きすぎる場合は、ハッシュ分散を使用します。
ファクト	クラスター化列ストア インデックスと共にハッシュ分散を使用して、コンピューティング ノード間でファクト テーブルを分散します。
ステージング	ステージング テーブルに対してラウンド ロビン分散を使用して、コンピューティング ノード間でデータを均等に分散します。

ディメンション テーブルの作成

ディメンション テーブルを作成するときには、テーブル定義に、代理キーと代替キー、および集計のグループ化に使用するディメンションの属性の列が含まれるようにします。 多くの場合、IDENTITY 列を使用して増分代理キーを自動生成するのが最も簡単です (それ以外の場合は、データを読み込むたびに一意キーを生成する必要があります)。 次の例は、架空の DimCustomer ディメンション テーブルの CREATE TABLE ステートメントを示しています。

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

ディメンション テーブルが相互に関連付けられる snowflake スキーマを使用する場合は、"親" ディメンションのキーを "子" ディメンション テーブルの定義に含める必要があります。 たとえば、次の SQL コードを使用して、DimCustomer テーブルから別の DimGeography ディメンション テーブルに地理的アドレスの詳細を移動できます。

CREATE TABLE dbo.DimGeography
(
    GeographyKey INT IDENTITY NOT NULL,
    GeographyAlternateKey NVARCHAR(10) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    GeographyKey INT NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

時間ディメンション テーブル

ほとんどのデータ ウェアハウスには、複数の階層レベルの時間間隔によってデータを集計できる "時間" ディメンション テーブルが含まれています。 たとえば、次の例では、特定の日付に関連する属性を持つ DimDate テーブルを作成します。

CREATE TABLE dbo.DimDate
( 
    DateKey INT NOT NULL,
    DateAltKey DATETIME NOT NULL,
    DayOfMonth INT NOT NULL,
    DayOfWeek INT NOT NULL,
    DayName NVARCHAR(15) NOT NULL,
    MonthOfYear INT NOT NULL,
    MonthName NVARCHAR(15) NOT NULL,
    CalendarQuarter INT  NOT NULL,
    CalendarYear INT NOT NULL,
    FiscalQuarter INT NOT NULL,
    FiscalYear INT NOT NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

ファクト テーブルの作成

ファクト テーブルには、テーブルが関連する各ディメンションのキーと、分析する特定のイベントまたは観測結果の属性と数値メジャーが含まれます。

次のコード例では、主要な列 (日付、顧客、製品、およびストア) を通じて複数のディメンションに関連する FactSales という名前の架空のファクト テーブルを作成します。

CREATE TABLE dbo.FactSales
(
    OrderDateKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    ProductKey INT NOT NULL,
    StoreKey INT NOT NULL,
    OrderNumber NVARCHAR(10) NOT NULL,
    OrderLineItem INT NOT NULL,
    OrderQuantity SMALLINT NOT NULL,
    UnitPrice DECIMAL NOT NULL,
    Discount DECIMAL NOT NULL,
    Tax DECIMAL NOT NULL,
    SalesAmount DECIMAL NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(OrderNumber),
    CLUSTERED COLUMNSTORE INDEX
);

ステージング テーブルの作成

ステージング テーブルは、データがデータ ウェアハウスに読み込まれるときの一時ストレージとして使用されます。 一般的なパターンの 1 つは、外部ソース (多くの場合、データ レイク内のファイル) からリレーショナル データベースにデータを取り込み、次に SQL ステートメントを使用してステージング テーブルからディメンション テーブルとファクト テーブルにデータを読み込む目的で、可能な限り効率的になるようにテーブルを構造化することです。

次のコード例では、最終的にディメンション テーブルに読み込まれる製品データ用のステージング テーブルを作成します。

CREATE TABLE dbo.StageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

外部テーブルの使用

読み込まれるデータが適切な構造を持つファイル内にある場合は、ファイルの場所を参照する外部テーブルを作成する方が効果的である可能性があります。 これにより、データをリレーショナル ストアに読み込むことなく、ソース ファイルから直接読み取ることができます。 次の例は、Synapse ワークスペースに関連付けられているデータ レイク内のファイルを参照する外部テーブルを作成する方法を示しています。

-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO

-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DATA_SOURCE = StagedFiles,
    LOCATION = 'products/*.parquet',
    FILE_FORMAT = ParquetFormat
);
GO