SQL DW 向けの CREATE TABLE のドキュメント (CREATE TABLE (Azure SQL Data Warehouse)) に書かれていますが、きちんと触っていなかったので軽くまとめてみたいと思います。
■基本的なテーブル構造
SQLDW のテーブルですが、
- 列ストア
- 行ストア (ヒープ / クラスター化インデックス)
- 一時テーブル
の 2 種類の構造があります。
現在、新規に作成したテーブルの構造は「クラスター化列ストアインデックス」が使用されます。
テーブル作成のためのクエリは以下のようになります。
「CLUSTERED COLUMNSTORE INDEX」 を省略した場合は、クラスター化列ストアインデックスとして作成されますが、テーブルの構造を直感的に把握できるようにするため、記載していもよいかなと。
-- 列ストア (クラスター化列ストアインデックス )
IF OBJECT_ID('SQLDWTestCCIX') IS NOT NULL
DROP TABLE SQLDWTestCCIX
GO
CREATE TABLE SQLDWTestCCIX(
Col1 int,
Col2 int
)WITH(
CLUSTERED COLUMNSTORE INDEX
)
GO
CREATE INDEX NCIX_SQLDWTestCCIX ON SQLDWTestCCIX (Col1)
-- 行ストア (ヒープ)
IF OBJECT_ID('SQLDWTestRowStore_Heap') IS NOT NULL
DROP TABLE SQLDWTestRowStore_Heap
GO
CREATE TABLE SQLDWTestRowStore_Heap(
Col1 int,
COl2 int
)WITH(
HEAP
)
GO
-- 行ストア (クラスター化インデックス)
IF OBJECT_ID('SQLDWTestRowStore_CIX') IS NOT NULL
DROP TABLE SQLDWTestRowStore_CIX
GO
CREATE TABLE SQLDWTestRowStore_CIX(
Col1 int,
COl2 int
)WITH(
CLUSTERED INDEX (Col1)
)
GO
-- 一時テーブル
IF OBJECT_ID('tempdb..#SQLDWTest') IS NOT NULL
DROP TABLE #SQLDWTest
GO
CREATE TABLE #SQLDWTest(
Col1 int,
Col2 int
)WITH(
DISTRIBUTION = HASH (Col1)
)
GO
列ストアの場合は、圧縮された列単位でのアクセスとなりますが、セグメントというまとまった単位に対して、データアクセスを行うため、頻繁に少数のデータアクセスが必要な場合には、行ストアの方がレスポンスがよいケースがあります。
行ストアの場合は、行単位でデータを格納しますので、少ないデータアクセスを行う場合に、ポインポイントのデータアクセスのみでデータ操作が完了しますので、必要とするデータ数によっては列ストアより効率的にアクセスが行えるケースがあります。
■インデックスの組み合わせ
現状の SQLDW は SQL Server 2012 の PDW ベースとなっているため、インデックスの組み合わせの制約については、SQL Database v12 ではなく、SQL Server 2012 に準拠した形となります。
そのため、インデックスの組み合わせについては、以下のどれかのパターンを利用する形になるかと。
- クラスター化列ストアインデックス
- ヒープ
- クラスター化インデックス + 非クラスター化インデックス (最大で 999 個)
現状の SQLDW では、列ストアと行ストアを組み合わせることができませんので、どちらかの方式を使用してデータを格納する形となります。
また、ヒープ / クラスター化インデックスのデータについては、ページ圧縮が行われている状態となります。
テーブルの構造を変更したい場合など、インデックス操作で対応することができますので、以下のようなクエリを用いることができます。
-- ヒープ → クラスター化列ストアインデックス
IF OBJECT_ID('SQLDWTest') IS NOT NULL
DROP TABLE SQLDWTest
GO
CREATE TABLE SQLDWTest(
Col1 int,
Col2 int
)WITH(
DISTRIBUTION = HASH (Col1),
HEAP
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_SQLDWTest ON SQLDWTest
ＧＯ
-- クラスター化インデックス → クラスター化列ストアインデックス
IF OBJECT_ID('SQLDWTest') IS NOT NULL
DROP TABLE SQLDWTest
GO
CREATE TABLE SQLDWTest(
Col1 int,
Col2 int
)WITH(
DISTRIBUTION = HASH (Col1),
CLUSTERED INDEX (Col1)
)
GO
INSERT INTO SQLDWTest VALUES(1,2)
GO
CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d86e83cd815241cb988ca653d1fd135a ON SQLDWTest WITH (DROP_EXISTING = ON)
GO
-- クラスター化列ストアインデックス → クラスター化インデックス
IF OBJECT_ID('SQLDWTest') IS NOT NULL
DROP TABLE SQLDWTest
GO
CREATE TABLE SQLDWTest(
Col1 int,
Col2 int
)WITH(
DISTRIBUTION = HASH (Col1),
CLUSTERED COLUMNSTORE INDEX
)
GO
INSERT INTO SQLDWTest VALUES(1,2)
GO
CREATE CLUSTERED INDEX ClusteredIndex_e0fc1df05acf477cbee7242073bb46d5 ON SQLDWTest (Col1) WITH (DROP_EXISTING = ON)
GO
CREATE TABLE でインデックスを作成した場合、自動設定されたインデックス名が使用されますので、インデックス名を指定したい場合には、ヒープで作成した後にインデックスを作成するというような手順を踏んでもよいかと思います。
-- クラスター化列インデックス → クラスター化列ストアインデックス
IF OBJECT_ID('SQLDWTest') IS NOT NULL
DROP TABLE SQLDWTest
GO
CREATE TABLE SQLDWTest(
Col1 int,
Col2 int
)WITH(
DISTRIBUTION = HASH (Col1),
HEAP
)
GO
INSERT INTO SQLDWTest VALUES(1,2)
GO
CREATE CLUSTERED INDEX CIX_SQLDWTest ON SQLDWTest (Col1)
DROP INDEX CIX_SQLDWTest ON SQLDWTest
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_SQLDWTest ON SQLDWTest
GO
SQLDW では、オブジェクト名の変更ができますので、既存のテーブルに対しての操作ではなく新規のテーブルにデータを移してからリネームする方法もとることができるかと。
SDL Data Warehouse での名前変更
RENAME OBJECT SQLDWTest TO NewSQLDWTest
■データ分散方法の種類
現状の SQLDW は、60 のデータストレージ (ディストリビューション) に対して、データの配置を行います。
データの分散方法としては、
- HASH
- ROUND_ROBIN
の 2 種類を使用することができます。
ハッシュについては、データ型も考慮されて、データが分散されますので、データを同一のディストリビューションに配置したい場合には、格納される値だけでなく、データ型についても同一にしておく必要があります。
ラウンドロビンは、特定の列は用いずに、できるだけ均等にデータが格納されるように分散します。
60 のディストリビューションに対して均等にデータが配置されないような場合は、ラウンドロビンを使用してデータを分散させることを検討します。
現状、各ディストリビューションに同一の内容をレプリケートするというデータ分散方法は提供がされていないため、
- 同一のハッシュを使用してデータを分散させていないテーブル間で結合
した場合は、自ディストリビューションに格納されていないデータが必要となりますので「データの移動」により、一時的にローカルにデータを持ってくる必要があります。
テーブルの作成を行う際には
- どのように分散されるデータが格納されるか
- 同一のディストリビューション内に配置されているデータで効率よくデータを操作できるか
がポイントになってくるかと。
■パーティションの設定
SQLDW ではパーティションを使用することが可能ですが、通常の SQL Server のパーティションと異なり、
- パーティション関数とパーティションスキーマを個別に作成する必要がない
のが特徴となります。
SQL Data Warehouse のテーブル パーティション
パーティションテーブルを作成する場合は以下のようなクエリとなります。
-- パーティションテーブル
IF OBJECT_ID('SQLDWTest_Partition') IS NOT NULL
DROP TABLE SQLDWTest_Partition
GO
CREATE TABLE SQLDWTest_Partition(
Col1 int,
Col2 int
)WITH(
DISTRIBUTION = HASH (Col1),
PARTITION ( Col1 RANGE RIGHT FOR VALUES (10, 20, 30, 40 ))
)
GO
パーティション関数とスキーマの作成は自動的に作成されるものに任せる形ですね。
パーティションのスイッチについては以下のように実行できます。
現状の、SQLDW では、パーティション単位の TRUCNATE TABLE は実行ができないようですので、データの削除についてはスイッチをうまく使っていく形になりそうです。
-- パーティションテーブル
IF OBJECT_ID('SQLDWTest_Partition') IS NOT NULL
DROP TABLE SQLDWTest_Partition
GO
CREATE TABLE SQLDWTest_Partition(
Col1 int,
Col2 int
)WITH(
DISTRIBUTION = HASH (Col1),
PARTITION ( Col1 RANGE RIGHT FOR VALUES (10, 20, 30, 40 ))
)
GO
-- パーティションテーブル (アーカイブ)
IF OBJECT_ID('SQLDWTest_Partition_Archive') IS NOT NULL
DROP TABLE SQLDWTest_Partition_Archive
GO
CREATE TABLE SQLDWTest_Partition_Archive(
Col1 int,
Col2 int
)WITH(
DISTRIBUTION = HASH (Col1),
PARTITION ( Col1 RANGE RIGHT FOR VALUES (10, 20, 30, 40 ))
)
GO
DECLARE @cnt int = 1
WHILE (@cnt <= 100)
BEGIN
INSERT INTO SQLDWTest_Partition VALUES(@cnt, 1)
SET @cnt += 1
END
SELECT * FROM SQLDWTest_Partition
SELECT * FROM SQLDWTest_Partition_Archive
ALTER TABLE SQLDWTest_Partition SWITCH PARTITION 1 TO SQLDWTest_Partition_Archive PARTITION 1
SELECT * FROM SQLDWTest_Partition
SELECT * FROM SQLDWTest_Partition_Archive
[/sourcecode]
</pre>
</div>
<p> </p>
<p>分割 (SPLIT) については、以下のようなクエリで実行することができます。</p>
<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:f028a861-9e02-4f0f-a604-a4c97d214e8e" 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' ]
ALTER TABLE SQLDWTest_Partition SPLIT RANGE(50)
注意点として、クラスター化インデックスとクラスター化列ストアインデックスのどちらを使用しているかによって、分割の条件が変わってきます。
クラスター化インデックスの場合は、分割先が空でなくても分割することができます。
クラスター化列ストアインデックスの場合は、既にデータが格納されているテーブルに対して分割しようとするとエラーとなります。
クラスター化列ストアインデックスでパーティショニングを使用する場合には、テーブル作成時に今後を考慮したパーティションの構成としておく必要があるようですね。
パーティションの結合 (MERGE) については、以下のようなクエリで実行することができます。
こちらについてもパーティション関数ではなく、テーブルに対して実行する形となります。
ALTER TABLE SQLDWTest_Partition MERGE RANGE(10)