Azure Synapse Analytics 専用 SQL プールと Databricks を組み合わせる際の検討メモ

はじめに

Databricks と Azure Synapse Analytics 専用 SQL プールを組み合わせて利用することを検討した際の雑なメモを共有します。

Synapse Dedicated SQL の設計方針

前提

• Spark サービス（ Databricks 等）上でメダリオンアーキテクチャに基づきデータ処理を行い、Spark にて 専用 SQL とデータ連携（送受信）を実施する。
• 移行元（オラクル）のデータ処理ロジックを実行する基盤であり、バッチ処理時のみの起動を想定。
• データ利活用を目的としたクエリを発行を実施しない。
• Spark サービス（ Databricks 等）上でデータ品質チェック済みのデータを連携するため、データ品質チェックを本基盤では実施しない。

データの流れ

• Databricks
  • landing(CSV)
  • raw(Delta)
  • enrich(Delta)
• Synapse Decated SQL
  • enrich(Decated SQL)
  • curate(Decated SQL)
• Databricks
  • curate(Delta)

Databricks から Synapse Dedicated SQL に接続する方法

1. Synapse テーブルの操作
   1. Spark コネクター
      • SQL Server 用の Apache Spark コネクタ - Spark connector for SQL Server | Microsoft Learn
   2. jdbc コネクター
      • SQL Server 用 Microsoft JDBC ドライバー - JDBC Driver for SQL Server | Microsoft Learn
2. T-SQL の実行
   1. pyodbc
      • Python SQL ドライバー - pyodbc - Python driver for SQL Server | Microsoft Learn
   2. sqlcmd
      • sqlcmd ユーティリティ - SQL Server | Microsoft Learn

専用 SQL リソースの利用方針

下記を参考に、データベースの実装方針を検討してください。複数の専用 SQLデータベースに移行する場合には、柔軟なサービスの起動を並列実行が可能であるが、時間（切り上げ ※１）単位での課金がかかることに注意が必要です。

• 移行元が単一のデータベース

  • 移行元のスキーマをそのままにして、単一の専用 SQLデータベースに移行

• 移行元が複数のデータベース

  • 移行元のスキーマをそのまま

    • 複数の専用 SQLデータベースに移行

  • データベース名とスキーマ名を連結した名称（例：PDB1.HR.EMPLOYEES -> PDB1_HR_EMPLOYEES）をテーブル名として

    • 単一の専用 SQLデータベースの単一スキーマに移行

    • 単一の専用 SQLデータベースの複数スキーマに移行

    • 複数の専用 SQLデータベースの１つ以上のスキーマに移行

※１下記のように記載されています。

ただし、1 時間の間に、アクティブなデータ ウェアハウスを一時停止に切り替えた場合には、1 時間分のコンピューティング料金が課金されます。

引用元：価格 - Azure Synapse Analytics | Microsoft Azure

テーブルの設計方針

• インデックス
  • 基本的には、ヒープ。※1
  • 1億レコードを超える場合には、Spark での処理を検討する。
  • 1億レコードを超える場合、数行の処理時にパフォーマンスの課題がある場合、かつ、Spark での処理ができない場合には、クラスター化インデックス、あるいは、非クラスター化インデックスの利用を検討。 ※2
• データ分散オプション
  • 基本的には、ラウンド ロビン分散。
  • テーブルサイズが、2GB を超える場合に、ハッシュ分散の利用を検討。 ※3
  • 小さなテーブルとの結合にパフォーマンスの課題がある場合には、レプリケートの利用を検討。
• パーティション分割
  • 基本的には、利用しない。
  • 長期間のデータを保持する場合には、集計で利用しない期間のデータの削除や Spark での処理を検討する。
• テーブル制約
  • 主キー制約（一意制約 + NOT NULL 制約）、あるいは、一意制約を、パフォーマンスの観点で問題があった場合に設定すること ※4 。SSMA により自動で設定されない可能性があるため、リフト＆シフトの思想に基づき、積極的に設定は行わない。ただし、制約を満たせない可能性がある場合には設定してはいけない ※5。
  • 外部キー、および、チェック制約は設定することはできない。（2022年11月 1日時点）

※1 Synapse のドキュメントに下記の記載あり。

1 億行未満の小さなルックアップ テーブルの場合、通常はヒープ テーブルが適しています。 クラスター列ストア テーブルは、含まれる行が 1 億行を超えて初めて最適な圧縮が実現します。

引用元：Oracle 移行の設計とパフォーマンス - Azure Synapse Analytics | Microsoft Learn

※2 Synapse のドキュメントに下記の記載あり。

1 - 2 列のみが頻繁に使われる大規模なテーブル (1 億行以上)

引用元：専用 SQL プール (以前の SQL DW) のチート シート - Azure Synapse Analytics | Microsoft Learn

※3 Synapse のドキュメントに下記の記載あり。

ディスク上のテーブル サイズが 2 GB を超えている。

引用元：ハッシュによる分散 - Azure Synapse Analytics | Microsoft Learn

※4 Synapse のドキュメントに下記の記載あり。

主キーや一意キーを使用すると、専用 SQL プール エンジンでクエリの最適な実行プランを生成できます。

引用元：主キー、外部キー、および一意キー - Azure Synapse Analytics | Microsoft Learn

※5 Synapse のドキュメントに下記の記載あり。

ユーザーはそれらの列のすべての値が一意であることを確認する必要があります。 これに違反すると、クエリで不正確な結果が返される可能性があります。

引用元：主キー、外部キー、および一意キー - Azure Synapse Analytics | Microsoft Learn

参考リンク

• Oracle 移行の設計とパフォーマンス - Azure Synapse Analytics | Microsoft Learn
• 分散テーブルの設計ガイダンス - Azure Synapse Analytics | Microsoft Learn

ワークロード管理

• 基本的には、最小限のリソースを利用するワークロードグループにより処理を実行。
• 大量データを処理する場合には、十分なコンピューティング リソースを割り当てるワークロードグループにより実行する。処理時の前後の処理にて、 sys.sp_set_session_context によるwlm_contextの変更することで、ワークロードグループを変更可能。

参考リンク

• 専用 SQL プールのデータ読み込みのベスト プラクティス - Azure Synapse Analytics | Microsoft Learn
• CREATE WORKLOAD CLASSIFIER (Transact-SQL) - SQL Server | Microsoft Learn
• CREATE WORKLOAD GROUP (Transact-SQL) - SQL Server | Microsoft Learn

専用 SQL プールのデータ読み込みのベスト プラクティス - Azure Synapse Analytics | Microsoft Learn

セキュリティ

• データ利活用を目的としたクエリの発行を想定しておらず、次のようなシステムユーザーを作成する。
  • データベースオブジェクトのデプロイユーザー
  • データエンジニアリングユーザー
  • データ書き込み運用ユーザー
  • 読み取り専用システム運用ユーザー

参考リンク

• メタデータ設計
  • Synapse POC プレイブック: Azure Synapse Analytics の専用 SQL プールを使用したデータ ウェアハウス - Azure Synapse Analytics | Microsoft Learn

データのロード処理

• データのロード方法
  • 基本的には、Databricks からPARQUET 形式のファイルをストレージ配置したうえで ※1、取り込む
    1. Databricks にて、対象のテーブルからデータを抽出したデータフレームを作成
    2. Databricks にて、監査列を更新
    3. Databricks にて、ストレージに Parquet 形式で書き込む
    4. Databricks にて、Synapse 専用 SQL にて実行するデータ取り込み SQL 文を生成
    5. Databricks にて、dbutils.notebook.exit にて SQL 文をリターン
    6. Azure Data factory にて、SQL 文を取得
    7. Azure Data factory にて、Script activity により取得した SQL 文を実行
  • 削除が許容されないテーブルが対象である場合には、下記の方法を検討してください。
    • Merge による Upsert 処理
  • 列ストアインデックスを設定している場合には、下記の方法を検討
    • CTAS と Rename により取り込む ※2
  • 大規模なデータの場合には、下記の方法を検討してください。
    • CTAS と Rename により取り込む ※2
• 事前処置と事後処理

  • 事前処理

    • 大規模な処理の場合には、大規模処理用のワークロードに変更。

  • 事後処理

    • 統計情報の更新
    • インデックスを設定している場合には、インデクスの再構築
    • 大規模な処理の場合には、デフォルトのワークロードに変更。
    • テーブルサイズの圧縮
      • DELETE + LOAD の処理を実施する場合には、DELETE 対象のデータのストレージが残ってしまうため、CTAS + RENAME 処理が必要。

  • データベースメンテナンス

※1 Synapse のドキュメントに下記の記載あり。

データの読み込みには、PARQUET ファイル形式と共に COPY INTO を使用することをお勧めします。

引用元：データ読み込み用の PolyBase または COPY INTO - Azure Synapse Analytics | Microsoft Learn

※2 Synapse のドキュメントに下記の記載とサンプルコードあり。

MERGE ステートメントの少なくとも一部は、CTAS を使用して置き換えることができます。

引用元：J. CTAS を使用して MERGE ステートメントを簡略化する - SQL Server | Microsoft Learn

統計の管理時間を短縮するには、統計情報が含まれる列、つまり更新を頻繁に行う必要がある列を限定します。 たとえば、毎日新しい値が追加される可能性のある日付列を更新することをお勧めします。 結合にかかわっている列、WHERE 句で使用されている列、GROUP BY で見つかった列などに関する統計の作成に重点を置いてください。

専用 SQL プールのベスト プラクティス - Azure Synapse Analytics | Microsoft Learn

• 専用 SQL プールにデータを取り込む - Azure Synapse Analytics | Microsoft Learn
• チュートリアル - Azure Databricks を使用して ETL 操作を実行する | Microsoft Learn

インデックスについて、下記で整理されている。

• テーブルのインデックス作成 - Azure Synapse Analytics | Microsoft Learn

パーティション分割されていないテーブルでは、DELETE を使用する代わりに、CTAS を使用して、テーブルに保持するデータを書き込むことをご検討ください。

引用元：専用 SQL プールのベスト プラクティス - Azure Synapse Analytics | Microsoft Learn

次の操作は、最小ログ記録が可能です。

• CREATE TABLE AS SELECT (CTAS)
• INSERT..SELECT
• CREATE INDEX
• ALTER INDEX REBUILD
• DROP INDEX
• TRUNCATE TABLE
• DROP TABLE
• ALTER TABLE SWITCH PARTITION

トランザクションの最適化 - Azure Synapse Analytics | Microsoft Learn

spark.conf.set( "spark.sql.parquet.writeLegacyFormat", "true")

引用元：チュートリアル - Azure Databricks を使用して ETL 操作を実行する | Microsoft Learn

テーブル圧縮は不可。

ALTER TABLE dbo.LINEITEM REBUILD PARTITION = ALL  
WITH (DATA_COMPRESSION = ROW)
GO  

Data compression type 'Row' is not supported in this version of SQL Server.

関数

• 基本的には、ユーザー定義関数を利用してはいけない。パフォーマンスの観点で問題ないことを確認の上で利用すること。

ユーザー定義関数（UDF）は、なるべく使わない

引用元：Azure Synapse Analytics 専用SQL Poolベストプラクティス (slideshare.net)

同時実行数

• 同時実行数はサービスレベルによって異なり ※1 、上限を超えたクエリはキューに入れられるため ※2 、規模が小さい処理を行う際には同時実行数を最大限利用できるようなワークフロー設計を実施する。
• 規模が大きい処理については、ワークロード管理機能に基づき、他の処理を同時に実行しないようことが望ましい。

※1 Synapse のドキュメントに下記の記載あり。

サービス レベル	同時クエリの最大数	REQUEST_MIN_RESOURCE_GRANT_PERCENT でサポートされる最小量 (%)
DW100c	4	25%
DW200c	8	12.5%
DW300c	12	8%
DW400c	16	6.25%
DW500c	20	5%

引用元：ワークロード グループの同時実行の最大値 - Azure Synapse Analytics | Microsoft Learn

※2 Synapse のドキュメントに下記の記載あり。

最大 128 個の同時実行クエリが実行され、残りのクエリはキューに入れられます。

引用元：ワークロードの管理 - Azure Synapse Analytics | Microsoft Learn

パフォーマンスチューニング

• 専用 SQL プール (以前の SQL DW) のチート シート - Azure Synapse Analytics | Microsoft Learn
• Azure_Synapse_Toolbox/CreateDistributionAdvisor_PublicPreview.sql at master · microsoft/Azure_Synapse_Toolbox (github.com)
• Synapse SQL Distribution Advisor - Azure Synapse Analytics | Microsoft Learn

ベストプラクティス集

• 専用 SQL プールのベスト プラクティス - Azure Synapse Analytics | Microsoft Learn
• 専用 SQL プールのデータ読み込みのベスト プラクティス - Azure Synapse Analytics | Microsoft Learn
• 設計上の成功 - Azure Synapse Analytics | Microsoft Learn

gzip等の圧縮ファイルをを取り込む場合、ファイル数を60個以上に分割してください。
ファイル分割しない場合に、データ取り込み処理が非圧縮時より遅くなることが多いです。

Only a single reader is used per gzip compressed file since uncompressing the file in the buffer is single threaded.

引用元:Azure SQL Data Warehouse loading patterns and strategies

バッファー内のファイルの解凍はシングルスレッドであるため、gzip圧縮ファイルに1つのリーダーのみが使用されます。

上記の妙訳

データ書き込み処理におけるステージングテーブルの利用について

ステージングテーブルを利用したほうがパフォーマンスが高い可能性がある。

データを SQL プール テーブルに移行する際に最速の読み込み速度を達成するには、データを 1 つのステージング テーブルに読み込みます。 ステージング テーブルをヒープとして定義し、分散オプションにラウンドロビンを使用します。

運用テーブルでハッシュ分散を使用している場合、ハッシュ分散を使用してステージング テーブルを定義すると、読み込みと挿入の合計時間が速くなる可能性があります。

引用元：ステージング テーブルに読み込む

ステージングテーブルの一時テーブルの利用について

ステージングテーブルにて一時テーブルを利用することで、パフォーマンスの最適化を実施することが可能となる。ただし、tempdbの容量に注意が必要となる。

一時テーブルはローカル ストレージを利用するため、SQL プールでは、パフォーマンスを向上することができます。

引用元：テーブルの永続性

SQL プール リソースでは、一時テーブルは、リモート ストレージではなくローカル ストレージに結果が書き込まれるため、パフォーマンス上の利点があります。

引用元：SQL プールの一時テーブル

tempdb 最大 GB DW100c あたり 399 GB です。 そのため、DWU1000c では、tempdb のサイズは 3.99 TB になります。

引用元：Azure Synapse Analytics (旧称 SQL DW) の容量制限

Merge 処理は、ハッシュ分散の時しか使えない。

行 1 でのクエリの実行が開始されました

Merge statements with a WHEN NOT MATCHED [BY TARGET] clause must target a hash distributed table.

総実行時間: 00:00:23.082

列ストア圧縮の最適化について

データ取り込み後、列ストアを再構築する必要があります。

パーティション テーブルの場合、再構築は一度に 1 つのパーティションずつ実行されます。 再構築されるパーティション内のデータは "オフライン" であり、そのパーティションの再構築が完了するまで使用できません。

引用元：順序指定クラスター化列ストア インデックスを使用したパフォーマンス チューニング

大規模なテーブルでは、単一のパーティションとインデックスを同時に再構築すると、より実用的です。

大量のデータの場合は、通常、CTAS の方が ALTER INDEX より高速です。 少量のデータの場合は、ALTER INDEX を簡単に使用できるため、テーブルを入れ替える必要はありません。

引用元：セグメントの品質を向上させるためのインデックスの再構築

順序指定 CCI の作成はオフライン操作です。 パーティションがないテーブルの場合、順序指定 CCI の作成プロセスが完了するまで、ユーザーはデータにアクセスできません。 パーティション テーブルでは、エンジンによってパーティション単位で順序指定 CCI パーティションが作成されるため、ユーザーは、順序指定 CCI の作成が処理中ではないパーティションのデータにアクセスできます。 このオプションを使用すると、大きなテーブルでの順序指定 CCI の作成時に、ダウンタイムを最小限に抑えることができます。

引用元：大きなテーブルでの順序指定 CCI の作成

トランザクション

• Azure Synapse Analytics の専用 SQL プールでトランザクションを使用する - Azure Synapse Analytics | Microsoft Learn
• 専用 SQL プールのトランザクションの最適化 - Azure Synapse Analytics | Microsoft Learn