LoginSignup
4
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@masahiro-yamaguchi(Masahiro Yamaguchi)

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

Posted at

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
まずはSQL Serverのクラスター化列ストアインデックスについて簡単に解説します。
クラスター化列ストアインデックスはSQL Server2014から実装されている機能となります。
クラスター化列ストアインデックスにデータが格納されると、カラムストア（列ストア）と呼べれる領域か、デルタストアと呼ばれる領域にデータが格納されます。

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

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

カラムストアにデータが入るタイミング

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

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

パーティション毎の件数は以下の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

パーティションの注意点

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

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

クラスター化列ストアインデックスでのパーティションの注意点は__パーティションを細かくし過ぎない__という事になります。
image.png

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

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

パーティションの操作

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

パーティションテーブルの作成

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

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で作成したパーティションを確認する事が出来ます。

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

パーティションのスイッチ

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

--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）も可能です。

--パーティションの分割
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

4
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?