Azure SQL Data Warehouse のチート シートCheat sheet for Azure SQL Data Warehouse

このチート シートは、Azure SQL Data Warehouse ソリューションを構築する場合に役立つヒントとベスト プラクティスを提供します。This cheat sheet provides helpful tips and best practices for building your Azure SQL Data Warehouse solutions. 開始する前に、SQL Data Warehouse とは何か、および SQL Data Warehouse でないものは何かを説明する「Azure SQL Data Warehouse Workload Patterns and Anti-Patterns」(Azure SQL Data Warehouse ワークロード パターンとアンチ パターン) を読んで、各手順の詳細を参照してください。Before you get started, learn more about each step in detail by reading Azure SQL Data Warehouse Workload Patterns and Anti-Patterns, which explains what SQL Data Warehouse is and what it is not.

次の図は、データ ウェアハウスを設計するプロセスを示しています。The following graphic shows the process of designing a data warehouse:

概要

複数のテーブルを対象とするクエリおよび操作Queries and operations across tables

データ ウェアハウスで実行する主要な操作とクエリが事前にわかっている場合は、それらの操作に合わせてデータ ウェアハウス アーキテクチャを優先度付けすることができます。When you know in advance the primary operations and queries to be run in your data warehouse, you can prioritize your data warehouse architecture for those operations. こうしたクエリや操作には次が含まれる可能性があります。These queries and operations might include:

  • 1 つまたは 2 つのファクト テーブルをディメンション テーブルと結合し、結合されたテーブルをフィルター処理して、結果をデータ マートに追加する。Joining one or two fact tables with dimension tables, filtering the combined table, and then appending the results into a data mart.
  • ファクト テーブルに対して大きな更新または小さな更新を行う。Making large or small updates into your fact sales.
  • テーブルにデータのみを追加する。Appending only data to your tables.

事前に操作の種類を知ることは、テーブルの設計を最適化するのに役立ちます。Knowing the types of operations in advance helps you optimize the design of your tables.

データ移行Data migration

まず、データを Azure Data Lake Store または Azure Blob Storage に読み込みます。First, load your data into Azure Data Lake Store or Azure Blob storage. 次に、PolyBase を使って SQL Data Warehouse のステージング テーブルにデータを読み込みます。Next, use PolyBase to load your data into SQL Data Warehouse in a staging table. 次の構成を使用します。Use the following configuration:

設計Design 推奨Recommendation
ディストリビューションDistribution ラウンド ロビンRound Robin
インデックス作成Indexing ヒープHeap
パーティション分割Partitioning なしNone
リソース クラスResource Class largerc または xlargerclargerc or xlargerc

データ移行データ読み込み、および抽出、読み込み、および変換 (ELT) プロセスの詳細を参照してください。Learn more about data migration, data loading, and the Extract, Load, and Transform (ELT) process.

分散テーブルまたはレプリケート テーブルDistributed or replicated tables

テーブルのプロパティに応じて、次の方法を使用します。Use the following strategies, depending on the table properties:

typeType 適しているプロパティGreat fit for... 条件Watch out if...
レプリケートReplicated • 圧縮 (最大 5 倍の圧縮) 後のストレージが 2 GB 未満である、スター スキーマの小さいディメンション テーブル• Small dimension tables in a star schema with less than 2 GB of storage after compression (~5x compression) • テーブルに対して多くの書き込みトランザクション (挿入、アップサート、削除、更新など) が行われる• Many write transactions are on table (such as insert, upsert, delete, update)
• Data Warehouse ユニット (DWU) のプロビジョニングを頻繁に変更する• You change Data Warehouse Units (DWU) provisioning frequently
• 使うのは 2 - 3 列だけであるがテーブルには多くの列がある• You only use 2-3 columns but your table has many columns
• レプリケート テーブルにインデックスを作成する• You index a replicated table
ラウンド ロビン (既定)Round Robin (default) • 一時/ステージング テーブル• Temporary/staging table
• 明白な結合キーまたは適切な候補列がない• No obvious joining key or good candidate column
• データ移動のためにパフォーマンスが低い• Performance is slow due to data movement
HashHash • ファクト テーブル• Fact tables
• 大きいディメンション テーブル• Large dimension tables
• ディストリビューション キーを更新できない• The distribution key cannot be updated

ヒント:Tips:

  • 最初はラウンド ロビンを使いますが、大規模な並列アーキテクチャを活用するにはハッシュ ディストリビューション方法を目指します。Start with Round Robin, but aspire to a hash distribution strategy to take advantage of a massively parallel architecture.
  • 共通ハッシュ キーが同じデータ形式であることを確認します。Make sure that common hash keys have the same data format.
  • 分散には varchar 形式を使わないようにします。Don’t distribute on varchar format.
  • 頻繁に結合操作が行われるファクト テーブルに対する共通ハッシュ キーを持つディメンション テーブルは、ハッシュが分散される可能性があります。Dimension tables with a common hash key to a fact table with frequent join operations can be hash distributed.
  • データの偏りを分析するには、sys.dm_pdw_nodes_db_partition_stats を使います。Use sys.dm_pdw_nodes_db_partition_stats to analyze any skewness in the data.
  • クエリの背後で行われているデータ移動を分析し、ブロードキャストおよびシャッフル操作にかかる時間を監視するには、sys.dm_pdw_request_steps を使います。Use sys.dm_pdw_request_steps to analyze data movements behind queries, monitor the time broadcast, and shuffle operations take. これはディストリビューション方法の検討に役立ちます。This is helpful to review your distribution strategy.

詳しくは、レプリケート テーブルおよび分散テーブルに関するページをご覧ください。Learn more about replicated tables and distributed tables.

テーブルのインデックス付けIndex your table

インデックスは、テーブルを迅速に読み取るために役立ちます。Indexing is helpful for reading tables quickly. ニーズに応じて、独自のテクノロジのセットを使うことができます。There is a unique set of technologies that you can use based on your needs:

typeType 適しているプロパティGreat fit for... 条件Watch out if...
ヒープHeap • ステージング/一時テーブル• Staging/temporary table
• 小さいテーブルと小さい参照• Small tables with small lookups
• すべての参照がテーブル全体をスキャンします• Any lookup scans the full table
クラスター化インデックスClustered index • 最大 1 億行を含むテーブル• Tables with up to 100 million rows
• 1 - 2 列のみが頻繁に使われる大規模なテーブル (1 億行以上)• Large tables (more than 100 million rows) with only 1-2 columns heavily used
• レプリケート テーブルで使われます• Used on a replicated table
• 複数の結合および Group By 操作を含む複雑なクエリがあります• You have complex queries involving multiple join and Group By operations
• インデックス付き列の更新を行います。これはメモリを消費します• You make updates on the indexed columns: it takes memory
クラスター化列ストア インデックス (CCI) (既定)Clustered columnstore index (CCI) (default) • 大規模なテーブル (1 億行以上)• Large tables (more than 100 million rows) • レプリケート テーブルで使われます• Used on a replicated table
• 大量のテーブル更新操作を行います• You make massive update operations on your table
• テーブルを過剰にパーティション分割しています。行グループは異なるディストリビューション ノードおよびパーティションにはまたがりません• You overpartition your table: row groups do not span across different distribution nodes and partitions

ヒント:Tips:

  • クラスター化インデックスに加えて、フィルターで使用頻度の高い列に非クラスター化インデックスを追加することが必要な場合があります。On top of a clustered index, you might want to add a nonclustered index to a column heavily used for filtering.
  • CCI を含むテーブルでのメモリの管理方法に注意する必要があります。Be careful how you manage the memory on a table with CCI. データを読み込むときに、大きいリソース クラスによってユーザー (またはクエリ) にメリットがあるようにします。When you load data, you want the user (or the query) to benefit from a large resource class. トリミングによって多くの小さい圧縮された行グループが作成されないようにします。Make sure to avoid trimming and creating many small compressed row groups.
  • Gen2 では、パフォーマンスを最大にするため、CCI テーブルはコンピューティング ノードにローカルにキャッシュされます。On Gen2, CCI tables are cached locally on the compute nodes to maximize performance.
  • CCI では、行グループの圧縮が不十分であるためにパフォーマンスが低下することがあります。For CCI, slow performance can happen due to poor compression of your row groups. これが発生した場合は、CCI を再構築または再編成します。If this occurs, rebuild or reorganize your CCI. 圧縮された行グループあたり 10 万行以上が必要です。You want at least 100,000 rows per compressed row groups. 理想は行グループあたり 100 万行です。The ideal is 1 million rows in a row group.
  • 増分読み込みの頻度とサイズに基づいて、インデックスを再編成または再構築するタイミングを自動化します。Based on the incremental load frequency and size, you want to automate when you reorganize or rebuild your indexes. 大掃除は常に役に立ちます。Spring cleaning is always helpful.
  • 行グループをトリミングする場合は、戦略的に行います。Be strategic when you want to trim a row group. 開いている行グループはどのくらいの大きさですか。How large are the open row groups? 今後、どれくらいのデータが読み込まれると予想されますか。How much data do you expect to load in the coming days?

詳しくは、インデックスに関するページをご覧ください。Learn more about indexes.

パーティション分割Partitioning

大きなファクト テーブル (10 億行以上) がある場合、テーブルをパーティション分割することがあります。You might partition your table when you have a large fact table (greater than 1 billion rows). ほとんどの場合、パーティション キーは日付に基づく必要があります。In 99 percent of cases, the partition key should be based on date. パーティション分割しすぎないように注意してください (特に、クラスター化列ストア インデックスの場合)。Be careful to not overpartition, especially when you have a clustered columnstore index.

ELT を必要とするステージング テーブルでは、パーティション分割によるメリットがあります。With staging tables that require ELT, you can benefit from partitioning. データのライフサイクル管理が容易になります。It facilitates data lifecycle management. データをパーティション分割しすぎないように注意してください (特に、クラスター化列ストア インデックスの場合)。Be careful not to overpartition your data, especially on a clustered columnstore index.

詳しくは、パーティションに関するページをご覧ください。Learn more about partitions.

段階的な読み込みIncremental load

データを段階的に読み込む場合、まず、データの読み込みに大きいリソース クラスを割り当てていることを確認します。If you're going to incrementally load your data, first make sure that you allocate larger resource classes to loading your data. SQL Data Warehouse への ELT パイプラインを自動化するために PolyBase および ADF V2 を使うことをお勧めします。We recommend using PolyBase and ADF V2 for automating your ELT pipelines into SQL Data Warehouse.

履歴データの更新の大きなバッチの場合は、最初に関係のあるデータを削除します。For a large batch of updates in your historical data, first delete the concerned data. その後、新しいデータを一括挿入します。Then make a bulk insert of the new data. この 2 段階のアプローチがより効率的です。This two-step approach is more efficient.

統計を管理するMaintain statistics

自動統計が一般公開されるまで、SQL Data Warehouse では、手動の統計のメンテナンスが必要です。Until auto-statistics are generally available, SQL Data Warehouse requires manual maintenance of statistics. データに大幅な変更が発生したときに統計を更新することが重要です。It's important to update statistics as significant changes happen to your data. これにより、クエリ プランを最適化できます。This helps optimize your query plans. すべての統計の管理に時間がかかりすぎる場合は、統計を作成する列を限定します。If you find that it takes too long to maintain all of your statistics, be more selective about which columns have statistics.

更新の頻度を定義することもできます。You can also define the frequency of the updates. たとえば、毎日新しい値が追加される可能性がある日付列を更新する場合があります。For example, you might want to update date columns, where new values might be added, on a daily basis. 結合に含まれる列、WHERE 句で使われている列、および GROUP BY に含まれている列に関する統計を作成すると、最も大きなメリットが得られます。You gain the most benefit by having statistics on columns involved in joins, columns used in the WHERE clause, and columns found in GROUP BY.

詳しくは、統計に関するページをご覧ください。Learn more about statistics.

リソース クラスResource class

SQL Data Warehouse では、クエリにメモリを割り当てる方法としてリソース グループを使用します。SQL Data Warehouse uses resource groups as a way to allocate memory to queries. クエリまたは読み込みの速度を向上させるために、より多くのメモリが必要な場合は、さらに高いリソース クラスを割り当てる必要があります。If you need more memory to improve query or loading speed, you should allocate higher resource classes. その一方で、使うリソース クラスを大きくするとコンカレンシーに影響があります。On the flip side, using larger resource classes impacts concurrency. すべてのユーザーを大きいリソース クラスに移行する前に、そのことを考慮する必要があります。You want to take that into consideration before moving all of your users to a large resource class.

クエリに時間がかかりすぎる場合は、ユーザーが大きいリソース クラスで実行していないことを確認します。If you notice that queries take too long, check that your users do not run in large resource classes. 大きいリソース クラスは、多くのコンカレンシー スロットを消費します。Large resource classes consume many concurrency slots. それにより、他のクエリが待機する可能性があります。They can cause other queries to queue up.

最後に、SQL Data Warehouse の Gen2 を使うことにより、各リソース クラスは Gen1 より 2.5 倍多いメモリを取得します。Finally, by using Gen2 of SQL Data Warehouse, each resource class gets 2.5 times more memory than Gen1.

詳しくは、リソース クラスとコンカレンシーの操作方法に関するページをご覧ください。Learn more how to work with resource classes and concurrency.

コストの削減Lower your cost

SQL Data Warehouse の重要な機能は、コンピューティング リソースを管理する機能です。A key feature of SQL Data Warehouse is the ability to manage compute resources. データ ウェアハウスを使用していないときは一時停止できます。そうすると、コンピューティング リソースの課金が停止されます。You can pause the data warehouse when you're not using it, which stops the billing of compute resources. パフォーマンスのニーズに合わせてリソースを拡大縮小することができます。You can scale resources to meet your performance demands. 一時停止するには、Azure Portal または PowerShell を使用します。To pause, use the Azure portal or PowerShell. 拡大縮小するには、Azure PortalPowershellT-SQL、または REST API を使用します。To scale, use the Azure portal, Powershell, T-SQL, or a REST API.

Azure Functions では自動スケールを利用できます。Autoscale now at the time you want with Azure Functions:

パフォーマンスのためのアーキテクチャの最適化Optimize your architecture for performance

SQL Database と Azure Analysis Services はハブとスポークのアーキテクチャにすることを検討するようお勧めします。We recommend considering SQL Database and Azure Analysis Services in a hub-and-spoke architecture. このソリューションは、異なるユーザー グループ間のワークロードを分離しながら、SQL Database と Azure Analysis Services の高度なセキュリティ機能も使用できます。This solution can provide workload isolation between different user groups while also using advanced security features from SQL Database and Azure Analysis Services. また、無制限のコンカレンシーをユーザーに提供することもできます。This is also a way to provide limitless concurrency to your users.

詳しくは、SQL Data Warehouse を利用する一般的なアーキテクチャに関するページをご覧ください。Learn more about typical architectures that take advantage of SQL Data Warehouse.

1 クリックで、SQL Data Warehouse から SQL Database にスポークをデプロイします。Deploy in one click your spokes in SQL databases from SQL Data Warehouse: