CCCMKホールディングス TECH Labの Tech Blog

TECH Labスタッフによる格闘記録やマーケティング界隈についての記事など

ブログタイトル

トップ > Azure Synapse（旧Azure Datawarehouse）を触ってみた

Azure Synapse（旧Azure Datawarehouse）を触ってみた

こんにちは。技術開発ユニットの高橋です。

皆さんは、Azure Synapseをご存知でしょうか。Azure Synapse（旧Azure Datawarehouse）はMicrosoft Azureのデータウェアハウス製品で、大量のデータを気軽に格納・抽出することができます。

以下の記事にあるよう、弊社でもAzure Synapseを導入し、分析業務に活用しています。 https://www.itmedia.co.jp/news/articles/2006/19/news002.html

今回はAzure Synapse上でクエリの実行速度を計測し、その特性を探っていきたいと思います。

Azure Synapseとは

Azure Synapseは列指向データベース*1です。 列毎にデータを格納することで、集計操作が高速化できます。

また、Azure SynapseはMPP(massive parallel processing)というアーキテクチャを用いています。 これによって、複数のコンピューティングノード上でクエリを並列動作させ、高速に結果を返すことができます。 Azure Synapseでは、コンピューティングノードをDWという値でユーザーが間接的に制御できます（今回の記事では、最低設定のDW100cにて動作確認を行います）。

構文はSQL Serverと一部互換が保たれています。PolyBase等はSQL Serverにも提供されている機能ですが、以下では特に区別せずAzure Synapseの機能として記述していきます。

データロード

Azure Synapseはデータのインポート先にAzure Blob Storage(以下、blob。AWSのS3相当)を選択できます。 これを実現するために、PolyBase*2という機能を用います。

Azure Synapseでは、上のPolyBaseにCTAS(CREATE TABLSE AS SELECT)という構文を組み合わせ、データをロードすることができます。*3

-- Pylybase(外部テーブルを作成)
CREATE EXTERNAL TABLE [dbo].[external_dummy_data] (  
      [id] bigint NOT NULL,
      [value] bigint NOT NULL
)
WITH (LOCATION='/dummy_data/',
      DATA_SOURCE = exds_performance,  -- 別途作成
      FILE_FORMAT = csv_format  -- 別途作成
);

-- CTAS（外部テーブルからのロード ≒ blobからのロード）
CREATE TABLE [dbo].[dummy_data]
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH(id)
)
AS SELECT * FROM [dbo].[external_dummy_data];

それでは、blob上にデータを作成してロードしてみます。

例として、以下のデータをdummy.csvとしてblob上に配置しました。

1,100
2,200
3,300
4,400
5,500

このデータに対して上記のクエリを発行すると、Azure Synapse内にデータをロードできます。

selectで確認してみると、

select * from [dbo].[dummy_data];

id          value
----------- -----------
          1         100
          5         500
          2         200
          3         300
          4         400

(5 rows affected)

real    0m0.628s

データがAzure Synapse内に格納されていることがわかります。 ※実行時間の計測にはtimeコマンドを利用しています。

データ数を増やす

次に、データ数を1億件に増やしてみます。

dummy.csvを1億件にしてblobにアップロードします。 この状態でcreate tableを行い直すと、

CREATE TABLE [dbo].[dummy_data]
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH(id)
)
AS SELECT * FROM external_dummy_data;

real    0m50.079s

と、50秒程かかりました。 この程度の件数ならば、Azure Synapseは比較的高速にデータを格納することができます。

また、selectで1件任意のデータを取得すると、

select * from dbo.dummy_data where id = 50000000;

id                   value
-------------------- --------------------
            50000000           5000000000

(1 rows affected)

real    0m0.263s

200ms ~ 300msで結果を得られました。

テーブル構造

ところで先程、テーブルを作成する際、

CLUSTERED COLUMNSTORE INDEX,
DISTRIBUTION = HASH(id)

という値を設定しましたが、これはインデックスと分散の種類の指定です。

他の値も設定可能なため、いくつかの組み合わせでテーブルを作成し、 idをwhere句に指定して1件selectした場合の速度をざっくり確認してみます。

インデックス 分散 時間
CLUSTERED COLUMNSTORE INDEX HASH(id) 0m0.263s
CLUSTERED INDEX HASH(id) 0m0.282s
HEAP HASH(id) 0m6.305s
CLUSTERED COLUMNSTORE INDEX ROUNDROBIN 0m0.292s

クエリは初回だと数秒かかることもありますが、数回実行すると値が安定します。この場合は明確に、HEAPを指定すると遅いことがわかります。

次に、それぞれのテーブルを自己結合してみます。

インデックス 分散 時間
CLUSTERED COLUMNSTORE INDEX HASH(id) 0m10.899s
CLUSTERED INDEX HASH(id) 0m0.664s
HEAP HASH(id) 0m20.995s
CLUSTERED COLUMNSTORE INDEX ROUNDROBIN 0m23.843s

次はCLUSTERED INDEXが速くなりました。

これは、CLUSTERED INDEXにより行毎にデータが格納され、かつインデックスが貼られたことで、高速化したと考えられます。*4

※クエリの実行計画でseek/scanを確認すれば良い...と思ったのですが、 Azure Synapseではこのレベルでの実行計画を提供していないように思えます。何か情報ありましたら、コメントいただけると幸いです。

f:id:takahashii:20200721175505p:plain
blobへのアクセス等はポータルのクエリプランで確認可能です

集計

次に、countによる集計を行います。

select count(id) from dummy_data;
インデックス 分散 時間
CLUSTERED COLUMNSTORE INDEX HASH(id) 0m0.902s
CLUSTERED INDEX HASH(id) 0m6.701s
HEAP HASH(id) 0m4.971s
CLUSTERED COLUMNSTORE INDEX ROUNDROBIN 0m0.733s

こちらでは、CLUSTERED COLUMNSTORE INDEXを利用したテーブルが速いという結果になりました。

これは、CLUSTERED COLUMNSTORE INDEXを指定したことで列単位でデータが格納され、高速化に寄与したためと考えられます。

おわりに

Azure Synapseでは、CLUSTERED COLUMNSTORE INDEXとCLUSTERED INDEXでかなり動作速度が異なることがわかりました。実際にテーブル設計を行う際は、テーブルの利用方法に応じて適切なインデックス指定を行っていきましょう。特に、デフォルト値であるCLUSTERED COLUMNSTORE INDEXが設定されたテーブルでの結合（join）が非常に遅くなり得る点は、注意事項かと思います。

*1:https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-what-is

*2:https://docs.microsoft.com/ja-jp/sql/relational-databases/polybase/polybase-configure-azure-blob-storage?view=sql-server-ver15

*3:https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-ctas

*4:https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index