Azure Synapse Analytics SQLプールのパーティションについて

#Azure Synapse Analyticsでのパーティションについて
巨大なテーブルなどで過去のデータを削除する時や、クエリの処理速度の向上などで一役買ってくれるパーティションの機能ですが、Azure Synapse Analytics SQLプールでもパーティションの機能は存在しています。

今回はAzure Synapse Analytics SQLプールのパーティションの機能について記載します。

##パーティションが作成可能なテーブル
Azure Synapse Analytics SQLプールではいくつかインデックス/テーブルの種類が存在します。クラスター化列ストアインデックス、ヒープ、クラスター化インデックス、一時テーブルなどありますが、基本的にこれらすべてのテーブルをパーティション化する事が可能です。

また、Azure Synapse Analytics SQLプールでは60のディストリビューションに分散してデータを配置します。分散方法には、HASH分散とRound Robin分散がありますが、いずれの分散方式でもパーティション化はサポートされています。

##パーティション作成の際の注意点
クラスター化列ストアインデックスをパーティション化する場合には注意が必要です。
Azure Synapse Analytics SQLプールでは60のディストリビューションに分散してデータを配置しますが、クラスター化列ストアインデックスをパーティショニングする事により、内部的にさらにデータを分割することになります。ここに実は注意が必要なポイントがあります。

このポイントを簡単に説明します。

###クラスター化列ストアインデックス
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/0baf2663-ddb3-1509-de6d-93d607145307.png)
まずはSQL Serverのクラスター化列ストアインデックスについて簡単に解説します。
クラスター化列ストアインデックスはSQL Server2014から実装されている機能となります。
クラスター化列ストアインデックスにデータが格納されると、カラムストア（列ストア）と呼べれる領域か、デルタストアと呼ばれる領域にデータが格納されます。

カラムストアとデルタストアの違いは、カラムストアは列指向でデータが格納されており、さらにデータが圧縮されています。
一方でデルタストアは行指向でデータが格納されており、データは非圧縮です。

Azure Synapse Analytics SQLプールではカラムストアにデータが格納されている事が重要で、デルタストアにデータが格納されている状態はデータは非圧縮で、さらに行指向でデータを格納しているのでパフォーマンスは非常に不利です。

###カラムストアにデータが入るタイミング
ではそのカラムストアにデータが入るタイミングとはどういうときかと言いますと、基本的にはこの３つとなります。
・10万件以上のデータを一括で格納した場合
・デルタストアに格納された件数が100万件に達した場合
・インデックスのメンテナンスをした場合

今回はパーティションについてなので、細かな話は一旦割愛とさせていただきますが、簡単に言いますとクラスター化列ストアインデックスでパフォーマンスが最適化されるのは100万件を超えるようなテーブルです。
100万件を下回るような場合は、デルタストアにデータが入り非圧縮、行指向になってしまう可能性がある為、性能の恩恵を受けられない可能性が高くなります。

パーティション毎の件数は以下のSQLで確認する事が可能です。

```SQL
--パーティション毎の件数
SELECT  QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
,       i.[name] as Index_name
,       p.partition_number as Partition_nmbr
,       p.[rows] as Row_count
,       p.[data_compression_desc] as Data_Compression_desc
FROM    sys.partitions p
JOIN    sys.tables     t    ON    p.[object_id]   = t.[object_id]
JOIN    sys.schemas    s    ON    t.[schema_id]   = s.[schema_id]
JOIN    sys.indexes    i    ON    p.[object_id]   = i.[object_Id]
                            AND   p.[index_Id]    = i.[index_Id]
WHERE t.[name] = '<テーブル名>'
;
```

###Azure Synapse Analytics SQLプールでのクラスター化列ストアインデックス
ここで話をAzure Synapse Analytics SQLプールに戻します。
Azure Synapse Analytics SQLプールではそもそもデータは60のディストリビューションに分散して配置されており、それぞれのディストリビューションでこのクラスター化列ストアインデックスを作っています。

なので、SQL Serverのクラスター化列ストアインデックスのパフォーマンスが最適化されるのは100万件を超えるようなテーブルでしたが、Azure Synapse Analytics SQLプールではこの100万件に60を掛けた6000万件が最適化されるという事になります。
逆に言いますと6000万件に満たない小さなテーブルはクラスター化列ストアインデックスではなく、HEAPテーブルを検討する事が望ましいです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/32e2ba5d-51fa-c7a9-09c7-22771c443ea5.png)

###パーティションの注意点
Azure Synapse Analytics SQLプールでパーティションニングを行うという事は、クラスター化列ストアインデックスを60のディストリビューションに分割し、さらに内部的にパーティションでも分割するという動きになります。
なので、パーティショニングする場合、Azure Synapse Analytics SQLプールでは1つのパーティションあたり最低でも6000万件以上のデータが格納されるように設計する事が望ましいという事になります。

例えば、年月でパーティションを作成する場合は、
1ヶ月で6000万件以上、
1年で7億2000万件以上、
3年で21億6000万件以上
のデータが格納されるようなテーブルでない限り、年月でパーティションを作成するのはやめた方がよく、年単位などもっと粒度の大きなパーティショニングにするか、そもそもパーティショニングしないかなどを検討した方がいい事になります。

クラスター化列ストアインデックスでのパーティションの注意点は__パーティションを細かくし過ぎない__という事になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/13028b91-63fd-022f-ec1e-ad81dadf5bab.png)

ディストリビューション/パーティション毎の件数の確認は以下のSQLで確認する事が可能です。

```SQL
--ディストリビューション/パーティション毎の件数の確認
DBCC PDW_SHOWPARTITIONSTATS('<スキーマ名>.<パーティション名>');
```

#パーティションの操作
パーティションの実際の操作方法を簡単に説明します。

##パーティションテーブルの作成
パーティションテーブルは以下の要領で作成します。

```SQL
CREATE TABLE PARTITION_LINEITEM
(
	L_ORDERKEY    INTEGER NOT NULL,
    L_PARTKEY     INTEGER NOT NULL,
	L_SUPPKEY     INTEGER NOT NULL,
	L_LINENUMBER  INTEGER NOT NULL,
	L_QUANTITY    DECIMAL(15,2) NOT NULL,
	L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
	L_DISCOUNT    DECIMAL(15,2) NOT NULL,
	L_TAX         DECIMAL(15,2) NOT NULL,
	L_RETURNFLAG  CHAR(1) NOT NULL,
	L_LINESTATUS  CHAR(1) NOT NULL,
	L_SHIPDATE    DATE NOT NULL,
	L_COMMITDATE  DATE NOT NULL,
	L_RECEIPTDATE DATE NOT NULL,
	L_SHIPINSTRUCT CHAR(25) NOT NULL,
	L_SHIPMODE     CHAR(10) NOT NULL,
	L_COMMENT      VARCHAR(44) NOT NULL
) WITH (
	DISTRIBUTION = HASH(L_ORDERKEY),
	CLUSTERED COLUMNSTORE INDEX,
	PARTITION (L_SHIPDATE RANGE RIGHT FOR VALUES
		(
			 '1993-01-01','1994-01-01','1995-01-01'
			,'1996-01-01','1997-01-01','1998-01-01'
		)
	)
);
```

また、以下のSQLで作成したパーティションを確認する事が出来ます。

```SQL
SELECT 
t.name AS TableName, 
i.name AS IndexName, 
p.partition_number,
p.partition_id,
f.type_desc,
c.name AS PartitioningColumnName,
r.value
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.partitions AS p
    ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN  sys.partition_schemes AS s
    ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
    ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number
JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
    AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
JOIN sys.columns AS c
    ON t.object_id = c.object_id
WHERE t.name = 'PARTITION_LINEITEM'
    AND c.column_id = ic.column_id
ORDER BY p.partition_number;
```
__結果__
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/ef77301b-8f31-c44e-4d0c-0137b508a9f3.png)

##パーティションのスイッチ
パーティションは、カラム構造、テーブルの種類、分散方式などが一致しているデータの入っていない、非パーティションテーブルや、別のテーブルのパーティションと入れ替えること（SWITCH）が可能です。

```SQL
--NOPARTITIONテーブルの作成
CREATE TABLE NOPARTITION_LINEITEM
(
	L_ORDERKEY    INTEGER NOT NULL,
    L_PARTKEY     INTEGER NOT NULL,
	L_SUPPKEY     INTEGER NOT NULL,
	L_LINENUMBER  INTEGER NOT NULL,
	L_QUANTITY    DECIMAL(15,2) NOT NULL,
	L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
	L_DISCOUNT    DECIMAL(15,2) NOT NULL,
	L_TAX         DECIMAL(15,2) NOT NULL,
	L_RETURNFLAG  CHAR(1) NOT NULL,
	L_LINESTATUS  CHAR(1) NOT NULL,
	L_SHIPDATE    DATE NOT NULL,
	L_COMMITDATE  DATE NOT NULL,
	L_RECEIPTDATE DATE NOT NULL,
	L_SHIPINSTRUCT CHAR(25) NOT NULL,
	L_SHIPMODE     CHAR(10) NOT NULL,
	L_COMMENT      VARCHAR(44) NOT NULL
) WITH (
	DISTRIBUTION = HASH(L_ORDERKEY),
	CLUSTERED COLUMNSTORE INDEX
);

--パーティションのスイッチ
ALTER TABLE PARTITION_LINEITEM SWITCH PARTITION 7 TO NOPARTITION_LINEITEM;
```

##パーティションのスプリット
パーティションは、パーティション内にデータが入っていなければ分割（SPLIT）も可能です。

```SQL
--パーティションの分割
ALTER TABLE PARTITION_LINEITEM SPLIT RANGE ('1999-01-01');

--パーティションの確認
SELECT 
t.name AS TableName, 
i.name AS IndexName, 
p.partition_number,
p.partition_id,
f.type_desc,
c.name AS PartitioningColumnName,
r.value
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.partitions AS p
    ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN  sys.partition_schemes AS s
    ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
    ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number
JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
    AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
JOIN sys.columns AS c
    ON t.object_id = c.object_id
WHERE t.name = 'PARTITION_LINEITEM'
    AND c.column_id = ic.column_id
ORDER BY p.partition_number;
```
__結果__
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/3fec768f-fc88-af43-20c9-e003c0715d62.png)