SQL Data Warehouse を使ってみる その 2 の続きです。
前回は、基本的な環境を調べてみました。
今回はテーブルを作成してみたいと思います。
SQLDW のデータベースはディストリビューションで構成されており、
- 一つのデータベースは複数のデータベースの集合で構成されている
ものとなっていることが前回の投稿で確認ができました。
テーブルについても各ディストリビューションに分散してデータが格納できるような形で自動的に作成が行われます。
以下は新規に作成したテーブルがどのように分割されているかを取得したものですが、一つのテーブルがディストリビューション数と同様に 60 のテーブルで構成されていることが確認できます。
# 「sys.pdw_index_mappings」というインデックスのマッピング用システムビューもあるようです。
select OBJECT_NAME(object_id),* from sys.pdw_table_mappings where object_id = OBJECT_ID('dbo.FactHash') select OBJECT_NAME(object_id),COUNT(*) from sys.pdw_table_mappings where object_id = OBJECT_ID('dbo.FactHash') GROUP BY object_id
SQLDW のデータ領域は、
- 一つのデータベースは複数のデータベースで構成
- 一つのテーブルは複数のデータベースに分散して配置される
という構成になっていることがデータから確認することができます。
SQLDW ではデータ分散の方法として、
- ラウンド ロビン分散
- ハッシュ分散
の 2 種類の分散方法でテーブルを作成することができるようになっています。
詳細については、SQL Data Warehouse でのテーブル デザイン で解説がされています。
SQLDW ではサポートされているデータ型の制限、主キー / 一意インデックス / チェック制約 / 外部キーが設定できないというような制限がありますので、テーブル作成時には注意しておく必要のある項目が多々あるかと思います。
■ラウンド ロビン分散
この分散方法は、すべてのディストリビューションにできるだけ均等に分散させる方式となります。
テーブルの分散方法を指定しなかった場合は、デフォルトの設定として、ラウンド ロビン分散が使用されます。
この分散方法は分散に使用するキー項目は指定しないため、特定項目によるデータ分布を気にすることなく、データを分散させることが可能です。
ただし、今後記載しようと思っている「データ移動」が発生する可能性が高くなりますので、複数のテーブルと結合するような場合にはオーバーヘッドが発生する可能性が高くなります。
ラウンド ロビンはデータを順に分散させる方式となるため、後述のハッシュ分散とことなり、どこにデータを格納するかという判断が簡略化されるため、データ分散のオーバーヘッドは低くなるかと思います。
一時テーブルへのデータ初期ロードをする場合などはラウンド ロビン分散の方が、適しているケースがありそうですね。
■ハッシュ分散
この分散方法は、ハッシュに使用するキー項目を指定して、ディストリビューションにデータを分散される方式となります。
ハッシュ値については以下のような記載があります。
ハッシュは、 データの値ではなく、ハッシュするデータの型に基づいていることに注意してください。
データ値も考慮がされているようですが、データ型についても基づくようなので、データの格納状況についてはこの仕様を意識しておく必要があるかと。
ハッシュ項目によって求められたハッシュ値をもとにデータを分散させるため、
- すべてのディストリビューションにデータが分散されるような項目をハッシュ項目として設定
- 各ディストリビューションにデータの偏りが少なくなる
ということを意識していく必要があるかと。
特定のディストリビューションにデータが偏ってしまうと、計算ノードを増やしても効率的に処理ができないというようなケースも出てくるかと思いますので、最低でも 60 種類以上の値が指定される項目を、ハッシュキーとして使用してデータを分散させる必要があるかと。
それでは、実際にテーブルを作成して分散の状況を見ていきたいと思います。
今回は以下のテーブルを使用しています。
IF OBJECT_ID('FactRoundRobin') IS NOT NULL DROP TABLE FactRoundRobin IF OBJECT_ID('FactHash') IS NOT NULL DROP TABLE FactHash CREATE TABLE FactRoundRobin ( keycol int, subkey int ) WITH ( DISTRIBUTION = ROUND_ROBIN ) GO CREATE TABLE FactHash ( keycol int, subkey int ) WITH ( DISTRIBUTION = HASH(keycol) ) GO
このテーブルに対して、以下のクエリでデータを格納してみます。
DECLARE @cnt int = 1, @key int = 1, @subkey int WHILE (@cnt <= 2000) BEGIN SET @subkey = @cnt % 100 INSERT INTO FactHash VALUES(@key, @subkey) INSERT INTO FactRoundRobin VALUES(@key, @subkey) SET @cnt += 1 END [/sourcecode] </pre> </div> <p> </p> <p>上記のクエリでは、ハッシュ分散で使用している項目については、すべて同一の値 (1) が指定されています。</p> <p>SQLDW では、テーブルのデータ分散状況については DBCC コマンドで確認をすることができます。 <br />テーブル内のデータがどのようにディストリビューションに分散されているかについては「DBCC PDW_SHOWSPACEUSED」で確認できます。</p> <div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:409838d8-436b-4efd-8bb4-2fadfae88036" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal> [sourcecode language='sql' ] DBCC PDW_SHOWSPACEUSED('dbo.FactRoundRobin') DBCC PDW_SHOWSPACEUSED('dbo.FactHash')
SQLDW で使用できる DBCC については、DBCC (SQL Data Warehouse) から確認することができます。
今回は、各テーブルに 2,000 件のデータを格納しています。
上がラウンド ロビン、下がハッシュのテーブルになるのですが、ラウンド ロビンで作成されたテーブルについてはデータが分散され、ハッシュで作成されたテーブルについては、同一の値が入っている項目を指定しているため、すべてのデータが同一のディストリビューションに格納され分散されていないことが確認できます。
IF OBJECT_ID('FactHash2') IS NOT NULL DROP TABLE FactHash2 CREATE TABLE FactHash2 ( keycol int, subkey int ) WITH ( DISTRIBUTION = HASH(keycol) ) GO DECLARE @cnt int = 1, @key int = 1, @subkey int WHILE (@cnt <= 2000) BEGIN SET @key = @key % 30 SET @subkey = @cnt % 100 INSERT INTO FactHash2 VALUES(@key, @subkey) SET @cnt += 1 SET @key +=1 END [/sourcecode]
のクエリを実行してディストリビューション数以下のデータ分散になるようなデータを格納した場合の、ハッシュ分散の状況が以下になります。
# キー項目が 30 種類になるデータでの分散となります。
ディストリビューションに対して、ハッシュキーの項目のデータ分布のバランスが悪いので、データが格納されていないディストリビューションが存在していることが確認できますね。
SQLDW でテーブルを作成する際には、どのようなデータが入るかを意識して分散方式を設定することが重要となるかと。
SQLDW のベースとなった PDW では、分散方式として「REPLICATE」を使用することができました。
この分散方式では、すべてのディストリビューションに対して同一のデータを格納することができるのですが、データ格納に必要となる領域が「データサイズ×ディストリビューション数」となり、データを格納するための領域が通常の分散方式と比較して大きくなります。
ただし、すべてのディストリビューションに同一のデータが格納されるため、JOIN をする際のオーバーヘッドが低くなるため、頻繁に使用するディメンションについては、この分散方式を使用して複製をすると効果的という面があります。
REPLICATE の詳細については、Parallel Data Warehouse (PDW) How-To: Avoid ShuffleMove and PartitionMove Operations が参考になるかと。
Join Considerations in Azure SQL Data Warehouse を見たところ、現状の SQLDW ではこの方式はサポートされていないようです。
ただし、SQL Data Warehouse のビュー では、「DISTRIBUTION = REPLICATE」が記載されていますが、このクエリを実行してもエラーとなってしまいます。