Azure SQL Data Warehouse での分散テーブルの設計に関するガイダンスGuidance for designing distributed tables in Azure SQL Data Warehouse

Azure SQL Data Warehouse のハッシュ分散テーブルおよびラウンド ロビン分散テーブルを設計するための推奨事項。Recommendations for designing hash-distributed and round-robin distributed tables in Azure SQL Data Warehouse.

この記事では、読者が SQL Data Warehouse のデータ分散とデータ移動の概念を理解していることを前提としています。This article assumes you are familiar with data distribution and data movement concepts in SQL Data Warehouse.  詳細については、「Azure SQL Data Warehouse - 超並列処理 (MPP) アーキテクチャ」を参照してください。  For more information, see Azure SQL Data Warehouse - Massively Parallel Processing (MPP) architecture.

分散テーブルについてWhat is a distributed table?

分散テーブルは単一のテーブルとして表示されますが、実際には、行が 60 のディストリビューションにわたって格納されています。A distributed table appears as a single table, but the rows are actually stored across 60 distributions. 行はハッシュ アルゴリズムまたはラウンド ロビン アルゴリズムを使って、分散されます。The rows are distributed with a hash or round-robin algorithm.

ハッシュ分散テーブルは、この記事で取り上げる主題であり、大規模なファクト テーブルでのクエリ パフォーマンスを向上させます。Hash-distributed tables improve query performance on large fact tables, and are the focus of this article. ラウンド ロビン テーブルは、読み込み速度の向上に役立ちます。Round-robin tables are useful for improving loading speed. これらの設計の選択は、クエリおよび読み込みパフォーマンスの向上に大きな影響を与えます。These design choices have a significant impact on improving query and loading performance.

もう 1 つのテーブル ストレージの選択肢としては、すべての計算ノードにわたって小規模なテーブルをレプリケートする方法があります。Another table storage option is to replicate a small table across all the Compute nodes. 詳しくは、レプリケート テーブルを使用するための設計ガイダンスに関する記事をご覧ください。For more information, see Design guidance for replicated tables. 3 つの選択肢から簡単に選ぶには、テーブルの概要を示した記事の「分散テーブル」を参照してください。To quickly choose among the three options, see Distributed tables in the tables overview.

テーブル設計の一環として、ご利用のデータと、そのデータを照会する方法についてできる限り理解してください。As part of table design, understand as much as possible about your data and how the data is queried.  たとえば、次のような質問を考えてみます。  For example, consider these questions:

  • テーブルの大きさはどの程度か。How large is the table?  
  • どの程度の頻度でテーブルが更新されるか。How often is the table refreshed?  
  • データ ウェアハウス内にファクト テーブルとディメンション テーブルがあるか。Do I have fact and dimension tables in a data warehouse?  

ハッシュによる分散Hash distributed

ハッシュ分散テーブルでは、決定論的なハッシュ関数を使用して各行を 1 つのディストリビューションに割り当て、複数の計算ノードにわたってテーブル行を分散させます。A hash-distributed table distributes table rows across the Compute nodes by using a deterministic hash function to assign each row to one distribution.

分散テーブルDistributed table

同一値は常に同じディストリビューションにハッシュされるため、データ ウェアハウスには行の位置情報に関する組み込みのナレッジがあります。Since identical values always hash to the same distribution, the data warehouse has built-in knowledge of the row locations. SQL Data Warehouse ではこのナレッジを使用して、クエリ時のデータ移動を最小化し、クエリ パフォーマンスを向上させます。SQL Data Warehouse uses this knowledge to minimize data movement during queries, which improves query performance.

ハッシュ分散テーブルは、スター スキーマにある大規模なファクト テーブルに適しています。Hash-distributed tables work well for large fact tables in a star schema. 非常に多数の行を格納し、その上で高度なパフォーマンスを実現できます。They can have very large numbers of rows and still achieve high performance. もちろん、期待通りの分散システムのパフォーマンスを得るために役立つ設計上の考慮事項はいくつかあります。There are, of course, some design considerations that help you to get the performance the distributed system is designed to provide. 適切なディストリビューション列を選択することはそのような考慮事項の 1 つであり、この記事で説明されています。Choosing a good distribution column is one such consideration that is described in this article.

ハッシュ分散テーブルの使用は、次の場合に検討してください。Consider using a hash-distributed table when:

  • ディスク上のテーブル サイズが 2 GB を超えている。The table size on disk is more than 2 GB.
  • テーブルで、頻繁な挿入、更新、削除操作が行われる。The table has frequent insert, update, and delete operations.

ラウンド ロビンによる分散Round-robin distributed

ラウンド ロビン分散テーブルは、すべてのディストリビューションにわたって均等にテーブル行を分散させます。A round-robin distributed table distributes table rows evenly across all distributions. ディストリビューションに対する行の割り当てはランダムです。The assignment of rows to distributions is random. ハッシュ分散テーブルとは異なり、同じ値を持つ行が必ず同じディストリビューションに割り当てられるわけではありません。Unlike hash-distributed tables, rows with equal values are not guaranteed to be assigned to the same distribution.

その結果、クエリを解決するために、システムでデータの移動操作を呼び出して、データを整理することが必要になる場合があります。As a result, the system sometimes needs to invoke a data movement operation to better organize your data before it can resolve a query. この特別な手順のために、クエリが遅くなる可能性があります。This extra step can slow down your queries. たとえば、ラウンド ロビン テーブルを結合する場合、通常は行を再度シャッフルする必要があり、パフォーマンスの低下につながります。For example, joining a round-robin table usually requires reshuffling the rows, which is a performance hit.

次のシナリオでは、テーブルにラウンド ロビンによる分散を使用することを検討してください。Consider using the round-robin distribution for your table in the following scenarios:

  • 既定になっているので、作業開始時の単純な始点とする場合When getting started as a simple starting point since it is the default
  • 明確な結合キーが存在しない場合If there is no obvious joining key
  • テーブルをハッシュ分散するのに適した候補列がない場合If there is not good candidate column for hash distributing the table
  • テーブルが共通の結合キーを他のテーブルと共有していない場合If the table does not share a common join key with other tables
  • 結合がクエリの他の結合ほど重要ではない場合If the join is less significant than other joins in the query
  • テーブルが一時ステージング テーブルである場合When the table is a temporary staging table

Azure SQL Data Warehouse へのニューヨークのタクシー データの読み込みに関するチュートリアルでは、ラウンド ロビン ステージング テーブルにデータを読み込む例を示しています。The tutorial Load New York taxicab data to Azure SQL Data Warehouse gives an example of loading data into a round-robin staging table.

ディストリビューション列の選択Choosing a distribution column

ハッシュ分散テーブルには、ハッシュ キーであるディストリビューション列があります。A hash-distributed table has a distribution column that is the hash key. たとえば、次のコードは、ディストリビューション列として ProductKey を使ってハッシュ分散テーブルを作成します。For example, the following code creates a hash-distributed table with ProductKey as the distribution column.

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey])
)
;

この列の値によって行の分散方法が決まるため、ディストリビューション列の選択は、設計上の重要な決定事項です。Choosing a distribution column is an important design decision since the values in this column determine how the rows are distributed. 最適な選択肢は複数の要因によって決まり、多くの場合、トレードオフが生じます。The best choice depends on several factors, and usually involves tradeoffs. ただし、最初に最適な列を選択しなかった場合は、CREATE TABLE AS SELECT (CTAS) を使って別のディストリビューション列でテーブルを再作成できます。However, if you don't choose the best column the first time, you can use CREATE TABLE AS SELECT (CTAS) to re-create the table with a different distribution column.

更新を必要としないディストリビューション列を選択するChoose a distribution column that does not require updates

行を削除して、更新値を含む新しい行を挿入しない限り、ディストリビューション列を更新することはできません。You cannot update a distribution column unless you delete the row and insert a new row with the updated values. このため、静的な値を持つ列を選択します。Therefore, select a column with static values.

均等に分散したデータを含むディストリビューション列を選択するChoose a distribution column with data that distributes evenly

最適なパフォーマンスを得るために、すべてのディストリビューションでほぼ同じ行数を含むようにする必要があります。For best performance, all of the distributions should have approximately the same number of rows. 1 つまたは複数のディストリビューションに含まれる行数が不均衡な場合、並列クエリが一部のディストリビューションで他よりも先に終わります。When one or more distributions have a disproportionate number of rows, some distributions finish their portion of a parallel query before others. クエリは、すべてのディストリビューションで処理が終了するまで完了できないので、各クエリは単に最も処理が遅いディストリビューションと同じ速度になります。Since the query can't complete until all distributions have finished processing, each query is only as fast as the slowest distribution.

  • データ スキューとは、複数のディストリビューションにわたってデータが均等に分散されていないことを意味します。Data skew means the data is not distributed evenly across the distributions
  • 処理のスキューとは、クエリの並列実行を行うときに、一部のディストリビューションで他よりも処理が長引くことを意味します。Processing skew means that some distributions take longer than others when running parallel queries. これは、データがスキューしている場合に発生する可能性があります。This can happen when the data is skewed.

並列処理のバランスを得るには、以下のようなディストリビューション列を選択します。To balance the parallel processing, select a distribution column that:

  • 多数の一意の値を含む。Has many unique values. 列には、いくつかの重複値を含めることができます。The column can have some duplicate values. ただし、同じ値を持つすべての行が、同じディストリビューションに割り当てられます。However, all rows with the same value are assigned to the same distribution. 60 のディストリビューションがあるため、その列は少なくとも 60 個の一意の値を保持している必要があります。Since there are 60 distributions, the column should have at least 60 unique values. 通常、一意の値の数はそれよりもずっと多いです。Usually the number of unique values is much greater.
  • NULL がない、または少ししか NULL がない。Does not have NULLs, or has only a few NULLs. 極端な例として、列のすべての値が NULL の場合、すべての行が同じディストリビューションに割り当てられます。For an extreme example, if all values in the column are NULL, all the rows are assigned to the same distribution. その結果、クエリ処理は 1 つのディストリビューションにスキューされ、並列処理のメリットはなくなります。As a result, query processing is skewed to one distribution, and does not benefit from parallel processing.
  • 日付列ではない。Is not a date column. 同じ日付のデータはすべて、同じディストリビューションに格納されます。All data for the same date lands in the same distribution. 複数のユーザーがすべて、同じ日付でフィルター処理している場合、60 のディストリビューションのうち 1 つのみで、すべての処理操作が行われます。If several users are all filtering on the same date, then only 1 of the 60 distributions do all the processing work.

データ移動を最小化するディストリビューション列を選択するChoose a distribution column that minimizes data movement

正確なクエリ結果を得るために、クエリでは 1 つの計算ノードから別の計算ノードへとデータを移動させる場合があります。To get the correct query result queries might move data from one Compute node to another. データ移動は、一般的に、クエリに分散テーブルでの結合と集計が含まれる場合に発生します。Data movement commonly happens when queries have joins and aggregations on distributed tables. データ移動を最小化するディストリビューション列を選択することが、SQL Data Warehouse のパフォーマンスを最適化するための最も重要な戦略の 1 つです。Choosing a distribution column that helps minimize data movement is one of the most important strategies for optimizing performance of your SQL Data Warehouse.

データ移動を最小化するために、以下のようなディストリビューション列を選択します。To minimize data movement, select a distribution column that:

  • JOINGROUP BYDISTINCTOVER、および HAVING 句で使用されている。Is used in JOIN, GROUP BY, DISTINCT, OVER, and HAVING clauses. 2 つの大規模なファクト テーブルで頻繁に結合が生じる場合、両方のテーブルを結合列の 1 つに分散させると、クエリのパフォーマンスが向上します。When two large fact tables have frequent joins, query performance improves when you distribute both tables on one of the join columns. あるテーブルが結合に使用されない場合、GROUP BY 句に頻繁に出現する列でそのテーブルを分散させることを検討します。When a table is not used in joins, consider distributing the table on a column that is frequently in the GROUP BY clause.
  • WHERE 句で使用されていないIs not used in WHERE clauses. これによりクエリを絞り込み、すべてのディストリビューションでは実行されないようにすることができます。This could narrow the query to not run on all the distributions.
  • 日付列ではないIs not a date column. WHERE 句は多くの場合、日付別にフィルター処理されます。WHERE clauses often filter by date. この場合、すべての処理が、少数のディストリビューションのみで実行される可能性があります。When this happens, all the processing could run on only a few distributions.

ディストリビューション列として適切な列がない場合の対処方法What to do when none of the columns are a good distribution column

ディストリビューション列に適した個別の値を持つ列がない場合は、1 つまたは複数の値の複合として新しい列を作成できます。If none of your columns have enough distinct values for a distribution column, you can create a new column as a composite of one or more values. クエリ実行中のデータの移動を回避するために、クエリ内で複合ディストリビューション列を結合列として使用します。To avoid data movement during query execution, use the composite distribution column as a join column in queries.

ハッシュ分散テーブルを設計したら、次の手順として、そのテーブルにデータを読み込みます。Once you design a hash-distributed table, the next step is to load data into the table. 読み込みのガイダンスについては、読み込みの概要に関する記事をご覧ください。For loading guidance, see Loading overview.

お使いのディストリビューション列が適切な選択かどうかを判断する方法How to tell if your distribution column is a good choice

ハッシュ分散テーブルにデータを読み込んだ後は、60 のディストリビューションにどの程度均等に行が分散されているかを確認します。After data is loaded into a hash-distributed table, check to see how evenly the rows are distributed across the 60 distributions. ディストリビューションあたりの行数の変化が 10 % までであれば、パフォーマンスに顕著な影響はありません。The rows per distribution can vary up to 10% without a noticeable impact on performance.

テーブルにデータ スキューが発生しているかを判断するDetermine if the table has data skew

データ スキューをチェックする簡単な方法として、DBCC PDW_SHOWSPACEUSED を使用できます。A quick way to check for data skew is to use DBCC PDW_SHOWSPACEUSED. 次の SQL コードは、60 の各ディストリビューションに格納されているテーブル行の数を返します。The following SQL code returns the number of table rows that are stored in each of the 60 distributions. バランスの取れたパフォーマンスを実現するには、分散テーブルの行をディストリビューション全体で均等に広げる必要があることに注意してください。For balanced performance, the rows in your distributed table should be spread evenly across all the distributions.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

10 % を超えるデータ スキューが発生しているテーブルを特定するには、次の手順を実行します。To identify which tables have more than 10% data skew:

  1. テーブルの概要に関する記事に示されているビュー dbo.vTableSizes を作成します。Create the view dbo.vTableSizes that is shown in the Tables overview article.
  2. 次のクエリを実行します。Run the following query:
select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count
;

データ移動に関するクエリ計画をチェックするCheck query plans for data movement

適切なディストリビューション列は、結合および集計時のデータ移動を最小化することが可能です。A good distribution column enables joins and aggregations to have minimal data movement. このことは、結合の記述方法に影響を及ぼします。This affects the way joins should be written. 2 つのハッシュ分散テーブルで結合の際のデータ移動を最小化するには、結合列の 1 つをディストリビューション列にする必要があります。To get minimal data movement for a join on two hash-distributed tables, one of the join columns needs to be the distribution column. 2 つのハッシュ分散テーブルが同じデータ型のディストリビューション列で結合された場合、その結合はデータ移動を必要としません。When two hash-distributed tables join on a distribution column of the same data type, the join does not require data movement. 結合では、データ移動を発生させずに、追加の列を使用できます。Joins can use additional columns without incurring data movement.

結合中のデータ移動を回避するには、次の手順を実行します。To avoid data movement during a join:

  • 結合に関係するテーブルでは、結合に参加する列の いずれか でハッシュ分散する必要があります。The tables involved in the join must be hash distributed on one of the columns participating in the join.
  • 両方のテーブルで結合列のデータ型が一致する必要があります。The data types of the join columns must match between both tables.
  • 列を equal 演算子で結合する必要があります。The columns must be joined with an equals operator.
  • 結合の種類に CROSS JOINは許可されません。The join type may not be a CROSS JOIN.

クエリによってデータ移動が発生するかどうかを見極めるには、クエリ プランを確認できます。To see if queries are experiencing data movement, you can look at the query plan.

ディストリビューション列の問題を解決するResolve a distribution column problem

データ スキューのすべてのケースを解決する必要はありません。It is not necessary to resolve all cases of data skew. データの分散では、データ スキューとデータ移動の最小化において適切なバランスを見つけることが重要です。Distributing data is a matter of finding the right balance between minimizing data skew and data movement. 常にデータ スキューとデータ移動の両方を最小化することはできません。It is not always possible to minimize both data skew and data movement. 時には、データ移動を最小化するメリットが、データ スキューによる影響を上回る可能性もあります。Sometimes the benefit of having the minimal data movement might outweigh the impact of having data skew.

テーブルでデータ傾斜を解決する必要があるかを決定するには、ワークロード内のデータ ボリュームとクエリについて可能な限り理解する必要があります。To decide if you should resolve data skew in a table, you should understand as much as possible about the data volumes and queries in your workload. クエリの監視に関する記事に記載された手順を使用して、クエリ パフォーマンスのスキューの影響を監視できます。You can use the steps in the Query monitoring article to monitor the impact of skew on query performance. 具体的には、個々のディストリビューションで大規模なクエリの完了にかかる時間を探索します。Specifically, look for how long it takes large queries to complete on individual distributions.

既存のテーブル上のディストリビューション列は変更できないので、データ スキューの一般的な解決方法として、異なるディストリビューション列を持つテーブルを再作成します。Since you cannot change the distribution column on an existing table, the typical way to resolve data skew is to re-create the table with a different distribution column.

新しいディストリビューション列を含むテーブルを再作成するRe-create the table with a new distribution column

この例では、CREATE TABLE AS SELECT を使用して、他のハッシュ ディストリビューション列を含むテーブルを再作成します。This example uses CREATE TABLE AS SELECT to re-create a table with a different hash distribution column.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

次の手順Next steps

分散テーブルを作成するには、以下のいずれかのステートメントを使用します。To create a distributed table, use one of these statements: