LoginSignup
3
1

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へデータロードする時に一番早いテーブルはどれだ？

Posted at

ロードを最も高速に出来るテーブルはどれだ？

Azure Synapse Analyticsでは以下の種類のテーブルが存在しています。
・クラスター化列ストアインデックス（CCIX）
・ヒープテーブル（HEAP）
・クラスター化インデックス（CCI）

CCIXは列ストアとなっており、いわゆる列指向でのデータ格納形式です。
一方でHEAPとCCIに関しては行ストアでデータを管理します。

また、これらのテーブルの種類に加えて、さらにディストリビューションの分散方式を指定します。
ディストリビューションの分散方式は以下の３種類用意されています。
・HASH分散
・ラウンドロビン分散
・Replicated（ALL分散）

HASH分散は特定の１つのカラムの値からHASH値を計算し、HASH値に基づいて60あるディストリビューションの何処にデータを配置するか決定する方式。
ラウンドロビン分散はデータをランダムに60あるディストリビューションに配置する方式。
Replicatedは簡単に言うとすべてのディストリビューションに全てのデータのコピーを配置する方式です。

これらテーブルの種類や分散方式がある中で、どの組み合わせがデータをロードをするときに最も高速なのでしょうか？
ちなみに、公式なドキュメントでのおすすめは、悩んだときは「HEAP&ラウンドロビン分散」へのロードが推奨されています。

今回はこのポイントが気になりましたので検証してみました。

検証シナリオ①

70GBのCSVファイルのデータをPolyBaseにてDataLakeストレージからAzure Synapse Analytics SQLプールへロードします。
この時に最も高速なテーブルの種類と分散方式はどれになるのか検証してみました。

・テーブル：TPCHのlineitemテーブルを使用（600,037,902件）
・リソースクラス：xlargerc
・HASH分散時にはl_orderkeyと言うカラムを使用（l_orderkeyカラムは150,000,000種類の値が格納されており、非常に高いカーディナリティ）
※分散方式がReplicatedに関しては利用用途が限定的となるため今回の検証からは除外

検証シナリオ①の結果

CCIX_HASH(l_orderkey) CCIX_RoundRobin HEAP_HASH(l_orderkey) HEAP_RoundRobin CCI_HASH(l_orderkey) CCI_RoundRobin
DW500c 522,726ms 420,543ms 392,693ms 536,540ms 539,316ms 729,736ms
DW1000c 275,720ms 239,180ms 243,946ms 262,236ms 336,440ms 359,130ms

検証結果は上記の通りです。
予想としてはHEAPのRoundRobin分散が最も高速に処理できると思っていたのですが、CCI以外のCCIXやHEAPの間では大きな差はなさそうで、さらに分散方式も特に意識する必要はなさそうであるという結果になっています。

検証シナリオ②

検証シナリオ①の結果を踏まえて、もう少し検証を進めてみようと思います。
Azure Synapse Analyticsにデータのロードが終わった後、よくある処理としてはユーザへの公開用のテーブルへ再度データをロードするという処理を行う可能性があります。
ユーザに公開するテーブルなので、きちんとHASH分散を意識したCCIXのテーブルへロードすることにします。
PolyBaseでロードし終わったステージングテーブルからHASH分散（l_orderkey）したCCIXテーブルへロードする時間を検証シナリオ②では検証します。

検証シナリオ②の結果

CCIX_HASH(l_orderkey) CCIX_RoundRobin HEAP_HASH(l_orderkey) HEAP_RoundRobin CCI_HASH(l_orderkey) CCI_RoundRobin
DW500c 111,196ms 236,996ms 154,226ms 259,693ms 140,703ms 238,926ms
DW1000c 92,856ms 168,586ms 84,320ms 152,140ms 81,283ms 141,726ms

結果は上記の通りです。やはり、ロード元とロード先がHASH分散で分散キーが合致していると、RoundRobinに比べてロードが高速に完了出来ることが分かります。

考察

検証シナリオ①の結果から、Azure Synapse Analyticsへのデータの投入においては、「CCIXとHEAP」、「分散方式の違い」によって、ロードの時間に大差はなさそうです。よって、Azure Synapse Analyticsへのデータ投入については、CCI以外どのテーブルの種類、データ分散方式を使っても基本問題ないという事になります。

一方で検証シナリオ②ではHASH分散で分散キーが「ロード元のテーブル」と「ロード先のテーブル」で一致していた方が高速に処理できます。

よって、Azure Synapse Analyticsへデータロードする場合、HASH分散がある程度、検討がついている場合はHASH分散を指定しておくほうが後続の処理の関係で良いという事になります。一方、テーブルの種類のCCIXかHEAPかはどちらでも良さそうです。（CCIは使わない方が良さそう。）

おまけ

考察のように「HASH分散がある程度、検討がついている場合はHASH分散する事がのぞましく、CCIXかHEAPはどちらでもよい」と記述しましたが、HEAPの場合には実は「一時テーブル」という種類もあります。一時テーブルは作成すると接続が切れるまでは保持される動きとなり、接続が切れると失われる特性を持ちます。

この一時テーブルがもつ制限下でも問題ない場合は、一時テーブル（HEAP）でHASH分散しているテーブルをロード元のテーブルとしてしようするのがシナリオ①、②を通した動きとして最も高速です。
※シナリオ②の速度が非常に高速になります。

参考までに検証結果を記載します。

DW500c

TempTable_HEAP_HASH(l_orderkey)
シナリオ① 481,353ms
シナリオ② 92,190ms

DW1000c

TempTable_HEAP_HASH(l_orderkey)
シナリオ① 242,910ms
シナリオ② 54,096ms

ただし、TEMPテーブルは制限もあるので、ご利用は計画的に。

参考（検証コマンド）

検証テーブルの作成

--================================================================
--クラスター化列ストアインデックス
--================================================================
--テーブルの作成（クラスター化列ストアインデックス_HASH）
CREATE TABLE LINEITEM_CCIX_HASH
(
	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
);

----テーブルの作成（クラスター化列ストアインデックス_ROUND_ROBIN）
CREATE TABLE LINEITEM_CCIX_RR
(
	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 = ROUND_ROBIN,
	CLUSTERED COLUMNSTORE INDEX
);

--================================================================
--ヒープ
--================================================================
--テーブルの作成（ヒープ_HASH）
CREATE TABLE LINEITEM_HEAP_HASH
(
	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),
	HEAP
);

----テーブルの作成（ヒープ_ROUND_ROBIN）
CREATE TABLE LINEITEM_HEAP_RR
(
	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 = ROUND_ROBIN,
	HEAP
);

--================================================================
--クラスター化インデックス
--================================================================
--テーブルの作成（クラスター化インデックス_HASH)
CREATE TABLE LINEITEM_CCI_HASH
(
	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 INDEX (L_ORDERKEY)
);


--テーブルの作成（クラスター化インデックス_ROUND_ROBIN)
CREATE TABLE LINEITEM_CCI_RR
(
	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 = ROUND_ROBIN,
	CLUSTERED INDEX (L_ORDERKEY)
);

データロード

--事前作業としてPolyBaseが可能なように外部テーブル「EXT_LINEITEM_1」は作成済み
--クラスター化列ストアインデックスHASH分散へのロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_CCIX_HASH select * from EXT_LINEITEM_1
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

--クラスター化列ストアインデックスRoundRobin分散へのロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_CCIX_RR select * from EXT_LINEITEM_1;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

--ヒープHASH分散へのロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_HEAP_HASH select * from EXT_LINEITEM_1;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

--ヒープRoundRobin分散へのロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_HEAP_RR select * from EXT_LINEITEM_1;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

--クラスター化インデックスHASH分散へのロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_CCI_HASH select * from EXT_LINEITEM_1;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

--クラスター化インデックスRoundRobin分散へのロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_CCI_RR select * from EXT_LINEITEM_1;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

テーブル間データコピー用

--ロード先テーブルの作成
CREATE TABLE LINEITEM_IN
(
	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
);

truncate table LINEITEM_IN;
--クラスター化列ストアインデックスHASH分散からロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_IN select * from LINEITEM_CCIX_HASH;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

truncate table LINEITEM_IN;
--クラスター化列ストアインデックスRoundRobin分散からロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_IN select * from LINEITEM_CCIX_RR;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

truncate table LINEITEM_IN;
--ヒープHASH分散からロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_IN select * from LINEITEM_HEAP_HASH;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

truncate table LINEITEM_IN;
--ヒープRoundRobin分散からロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_IN select * from LINEITEM_HEAP_RR;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

truncate table LINEITEM_IN;
--クラスター化インデックスHASH分散からロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_IN select * from LINEITEM_CCI_HASH;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

truncate table LINEITEM_IN;
--クラスター化インデックスRoundRobin分散からロード
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
insert into LINEITEM_IN select * from LINEITEM_CCI_RR;
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

一時テーブルを使ったPolyBaseとテーブル間データコピー

--一時テーブルの作成
CREATE TABLE #LINEITEM_TMP_HASH
(
	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)
);

truncate table LINEITEM_IN;
--一時テーブルへのPolyBaseでのデータロードと一時テーブルから実テーブルへロード
DECLARE @startTime1 datetime2(1) = (SELECT GETDATE())
       ,@startTime2 datetime2(1) = (SELECT GETDATE())
insert into #LINEITEM_TMP_HASH select * from EXT_LINEITEM_1
SELECT DATEDIFF(ms, @startTime1, GETDATE()) AS ElapsedTime_ms_step1
insert into LINEITEM_IN select * from #LINEITEM_TMP_HASH
SELECT DATEDIFF(ms, @startTime2, GETDATE()) AS ElapsedTime_ms_step2
GO
3
1
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
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?