Azure Synapse Analytics SQLプールのパーティションメンテナンス方法

#データが入っているパーティションの分割
前回、Azure Synapse Analytics SQLプールのパーティションに関して調べてみました。
[Azure Synapse Analytics SQLプールのパーティションについて](https://qiita.com/masahiro-yamaguchi/items/04449cc48370fa1e42fb)

今回はこのパーティションテーブルをメンテナンスする方法について記述します。

実際の運用のシーンでも例えばDATE型のカラムでパーティションを「年単位」で構成している場合、新しい年のデータが入ってきたのでパーティションを分割するというような事が考えられると思います。

例えば以下のような`LINEITEM`テーブルで`L_SHIPDATE`カラムで各年のデータを管理するようなパーティションテーブルを作成します。

```SQL
CREATE TABLE 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'
		)
	)
);
```

このテーブルに1997年のデータだけでなく、1998年のデータも入ってきた場合、最後のパーティションには1997年のデータと1998年のデータが混在します。最後のパーティションを分割し、1997年のデータと1998年以降のデータを別々のパーティションで管理できるようにメンテナンスするイメージです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/8eecd085-021c-6c74-912b-d27b1156ee89.png)





上記のような場合は既にパーティションの中にデータが入っている状態です。Azure Synapse Analytics SQLプールではパーティションの分割はSPLITの機能を使って分割しますが、SPLITの機能はデータが入っているパーティションで行うとエラーとなってしまいます。なので、一工夫必要になります。


このようにデータが入っているパーティションを分割したい場合は、`SWITCH`と`CTAS`、`SPLIT`を用いることで比較的コストを抑える形でパーティションのメンテナンスを行う事が可能です。

#手順例
##①準備
今回の例では、先ほど紹介した`LINITEM`テーブルを例にメンテナンスを行っていきます。`LINEITEM`テーブルには既にデータが以下の通り各パーティションに入っています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/60c29b4a-2e1d-c157-3415-901bdf876aa3.png)

また、`LINEITEM`テーブルのパーティションの構成も確認します。以下の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 = '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/e853c6a6-5f17-10f0-c89a-97c62278f6bc.png)

この時点で、以下のような`SPLIT`を行ってもエラーとなります。

```SQL
--テーブルの分割(データがあるためエラー)
ALTER TABLE LINEITEM SPLIT RANGE ('1998-01-01');
```
__結果__

```
メッセージ 35346、レベル 15、状態 1、行 81
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty.  Only empty partitions can be split in when a columnstore index exists on the table. Consider an ALTER TABLE SWITCH operation from one of the nonempty partitions on table 'Table_3832cd136c3b4f32bd6ef9eb961126e7_34' to a temporary staging table and then re-attempt the ALTER PARTITION SPLIT operation. Once completed, use ALTER TABLE SWITCH to move the staging table partition back to the original source table.

完了時刻: 2022-01-24T11:22:32.5640528+09:00
```

##②1997年以降のデータを一時的に保管するテーブルを`CTAS`で作成
パーティション内にデータがあるため`SPLIT`を実行しましたがエラーになりました。
なので1997以降データを一時的に退避させ、パーティション内のデータを空にします。
`CTAS`を使って、一時的に保管するテーブルを作成します。
※この時点では`CTAS`を使ったテーブルの作成のみでデータの移行は行いません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/7d5817e4-5350-9ecb-f9a2-58508cc8aeff.png)

以下のSQLを使って`CTAS`で`LINEITEM_1997`テーブルを作成します。

```SQL
--CTASを使ってデータを一時的に保持するテーブルを作成
CREATE TABLE lineitem_1997
WITH (
	DISTRIBUTION = HASH(L_ORDERKEY),
	CLUSTERED COLUMNSTORE INDEX,
	PARTITION (L_SHIPDATE RANGE RIGHT FOR VALUES
		(
			'1997-01-01'
		)
	)
)
as
SELECT * FROM LINEITEM WHERE 1=2;
```

ここでもパーティションの確認をしますと以下の通りとなります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/e29fb879-e5d4-3883-94b8-f5870c3a0c40.png)


##③データの一時保管先のテーブル`LINEITEM_1997`に`SWITCH`を使ってデータ移行
②で作成したテーブルにデータを移行します。この時には`SWITCH`を使って移行すると高速且つ、低コストな移行が可能です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/6979199e-c433-798c-27cb-502e5ffc8a90.png)

以下のSQLで`SWITCH`します。

```SQL
--パーティションをSWITCH
ALTER TABLE LINEITEM SWITCH PARTITION 6 TO LINEITEM_1997 PARTITION 2;
```

##④`LINEITEM`テーブルの`SPLIT`
③で`LINEITEM`テーブルからデータの移行が完了し、対象のパーティションからデータがなくなったので、①ではエラーになった`SPLIT`がエラーにならずに成功するようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/e7e085a9-9cd6-b2f2-f8cc-baa2a4dec734.png)

以下のSQLで`SPLIT`します。

```SQL
--テーブルの分割
ALTER TABLE LINEITEM SPLIT RANGE ('1998-01-01');
```

`SPLIT`後のパーティション構造は以下の通りです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/15cf4ba7-34e2-8703-8da7-a348f67b2971.png)


##⑤`CTAS`を使って、パーティションの境界値を合わせたテーブルの作成
ここで再度`CTAS`を使って、パーティション構造が合致するようなテーブル`LINEITEM_1997_1998`テーブルを作成します。
この時に、一時的なデータの退避先のテーブルである`LINEITEM_1997`テーブルからデータの移行も実施します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/f5ec0b50-9078-ea31-ad99-9566b7775a6f.png)

以下のSQLで行います。

```SQL
--CTASを使ってパーティションを合わせたテーブルを作成&データの移行
CREATE TABLE lineitem_1997_1998
WITH (
	DISTRIBUTION = HASH(L_ORDERKEY),
	CLUSTERED COLUMNSTORE INDEX,
	PARTITION (L_SHIPDATE RANGE RIGHT FOR VALUES
		(
			'1997-01-01','1998-01-01'
		)
	)
)
as
SELECT * FROM LINEITEM_1997;
```
作成した`LINEITEM_1997_1998`テーブルの構造はこのようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/fe6cb7bf-1899-df5e-7fe6-ec7a12efe437.png)


##⑥`LINEITEM`テーブルと`LINEITEM_1997_1998`テーブルをSWITCHしメンテナンスを完了
最後に、`LINEITEM`テーブルと`LINEITEM_1997_1998`テーブルをSWITCHし、パーティションメンテナンスを完了させます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/f347d3c9-ef2f-55a7-9cfd-ece73bf06da4.png)

以下のSQLで行います。

```SQL
ALTER TABLE LINEITEM_1997_1998 SWITCH PARTITION 2 TO LINEITEM PARTITION 6;
ALTER TABLE LINEITEM_1997_1998 SWITCH PARTITION 3 TO LINEITEM PARTITION 7;
```

##最後に
メンテナンスが終わったら統計情報は更新した方がよいです。

```SQL
UPDATE STATISTICS LINEITEM;
```