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

データが入っているパーティションの分割

前回、Azure Synapse Analytics SQLプールのパーティションに関して調べてみました。
Azure Synapse Analytics SQLプールのパーティションについて

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

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

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

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年以降のデータを別々のパーティションで管理できるようにメンテナンスするイメージです。

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

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

手順例

①準備

今回の例では、先ほど紹介したLINITEMテーブルを例にメンテナンスを行っていきます。LINEITEMテーブルには既にデータが以下の通り各パーティションに入っています。

また、LINEITEMテーブルのパーティションの構成も確認します。以下の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;

結果

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

--テーブルの分割(データがあるためエラー)
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を使ったテーブルの作成のみでデータの移行は行いません。

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

--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;

ここでもパーティションの確認をしますと以下の通りとなります。

③データの一時保管先のテーブルLINEITEM_1997にSWITCHを使ってデータ移行

②で作成したテーブルにデータを移行します。この時にはSWITCHを使って移行すると高速且つ、低コストな移行が可能です。

以下のSQLでSWITCHします。

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

④LINEITEMテーブルのSPLIT

③でLINEITEMテーブルからデータの移行が完了し、対象のパーティションからデータがなくなったので、①ではエラーになったSPLITがエラーにならずに成功するようになります。

以下のSQLでSPLITします。

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

SPLIT後のパーティション構造は以下の通りです。

⑤CTASを使って、パーティションの境界値を合わせたテーブルの作成

ここで再度CTASを使って、パーティション構造が合致するようなテーブルLINEITEM_1997_1998テーブルを作成します。
この時に、一時的なデータの退避先のテーブルであるLINEITEM_1997テーブルからデータの移行も実施します。

以下の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テーブルの構造はこのようになります。

⑥LINEITEMテーブルとLINEITEM_1997_1998テーブルをSWITCHしメンテナンスを完了

最後に、LINEITEMテーブルとLINEITEM_1997_1998テーブルをSWITCHし、パーティションメンテナンスを完了させます。

以下の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;

最後に

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

UPDATE STATISTICS LINEITEM;