Azure Synapse Analyticsの専用SQLプールにデータを取り込む方法まとめ

こんばんは。今日はAzure Synapse Analyticsの専用SQLプールにデータを取り込む方法を調べたり試したりしていたので、分かったことを残しておきたいと思います。

それではまいります。

専用SQLプールにデータを読み込む方法

ETL の代わりに ELT を設計する – Azure Synapse Analytics | Microsoft Docs

上記に書いてあることをまとめただけですが、自分の頭の整理のために。

オプション実行方法コメント
PolyBase外部テーブル・T-SQL
・Azure Data Factory / Synapseパイプライン
・SSIS
・Azure Databricks
・高速かつシームレスかつ柔軟にデータを読み込むことができるため、推奨されている読み込みユーティリティ
COPYステートメント・T-SQL
・Azure Data Factory / Synapseパイプライン
・高速かつシームレスかつ柔軟にデータを読み込むことができるため、推奨されている読み込みユーティリティ
・PolyBase では提供されない追加の読み込み機能が多数ある
bcp・PolyBase および COPY ステートメントと比べて低速
・小規模の読み込みのみを対象
SqlBulkCopy API・PolyBase および COPY ステートメントと比べて低速
・小規模の読み込みのみを対象

というわけで、公式ドキュメントでも繰り返し言及されていますが、基本的にはCOPYステートメントあるいはPolyBaseを使った読み込み方法を選択していればよさそうです。

以下、いくつかのパターンを試してみたいと思います。

COPYステートメント

概要

COPY INTO (Transact-SQL) – (Azure Synapse Analytics) – SQL Server | Microsoft Docs

クイック スタート:単一の T-SQL ステートメントを使用してデータを一括読み込みする – Azure Synapse Analytics | Microsoft Docs

  • シンプルで高スループットのデータ インジェストができ、推奨されているユーティリティ
  • 専用SQLプールで最大のスループットが得られる
  • 単一の T-SQL ステートメントのみを活用でき、別途データベース オブジェクトを作成する必要はありません(PolyBaseのように外部テーブルを作成する必要がない、ということ?)
  • Shared Access Signature (SAS) を使用してストレージ アカウント キーを公開しなくても、より洗練されたアクセス許可モデルを指定できる

4つ目が不思議だったのですが、この方法ではBlob StorageからCSVファイルを読み込むときも、SAS URLを発行する必要がないんですよね。

下のDocsを読むと、SAS URL、アカウントキーを使った認証もできるようですが、その他Azure ADやサービスプリンシパルを使った認証を使うこともできるみたいで、この場合はSAS URLなどを発行する必要がなくなるようでした。

COPY ステートメントによる認証メカニズム – Azure Synapse Analytics | Microsoft Docs

T-SQLによる実装

こちらの手順に従って試してみることができます。

チュートリアル:ニューヨークのタクシー データを読み込む – Azure Synapse Analytics | Microsoft Docs

以下のように、読み込み用に別途ユーザを作成して読み込み用のリソースクラスを割り当てることが推奨されているようです。

サーバー管理者アカウントは管理操作を実行するためのものであり、ユーザー データに対するクエリの実行には適していません。 データの読み込みは、メモリを大量に消費する操作です。 メモリの最大値は、データ ウェアハウス ユニットリソース クラスの構成に従って定義されます。

データの読み込みに専用のログインとユーザーを作成することをお勧めします。 その後、適切な最大メモリ割り当てを有効にするリソース クラスに読み込みユーザーを追加します。

その上で以下のようなコマンドを実行。シンプルです。

COPY INTO [dbo].[TestTable]
FROM 'https://<ストレージアカウント名>.blob.core.windows.net/<読み込み対象ファイルまたはディレクトリのパス>'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = ',',
    FIELDQUOTE = '',
)
OPTION (LABEL = 'COPY : Load [dbo].[TestTable]');

2列だけのシンプルなデータですが、1000万行を15秒ほどで読み込めました。(リソースクラスはDW100c、ラウンドロビン分散の場合)

Data Factory / Synapse パイプラインによる実装

Azure Synapse Analytics にデータを読み込む – Azure Data Factory & Azure Synapse | Microsoft Docs

以下のキャプチャの通り、コピーアクティビティのシンクの設定でコピー方法を選択できるようになっています。

Polybase

概要

そもそもPolyBaseとは、という説明は以下。

PolyBase によるデータ仮想化の概要 – SQL Server | Microsoft Docs

SynapseでPolyBaseを使うと高速になる理由については以下のQiitaに説明がありました。COPYステートメントも同じ理由で高速なのかな?

Azure Synapse Analytics SQLプールのPolyBaseについて – Qiita

T-SQLによる実装

以下の公式チュートリアルに従って試してみることができます。

Contoso Retail データを専用 SQL プールに読み込む – Azure Synapse Analytics | Microsoft Docs

他の方の記事ですが実際に試してみた記事は以下。

PolyBaseでSynapse SQLプールにデータを投入してみた – Qiita

以上、Synapse専用SQLプールにデータを読み込む方法のまとめでした。今回はCOPYステートメントを実際に試してみましたが、時間をみつけて他の方法も試してみてコピー速度の比較なども行ってみたいですね。

少しでも参考になりましたら幸いです。

おしまい

この記事を気に入っていただけたらシェアをお願いします!

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

ABOUT US
Yuu113
初めまして。ゆうたろうと申します。 兵庫県出身、東京でシステムエンジニアをしております。現在は主にデータ分析、機械学習を活用してビジネスモデリングに取り組んでいます。 日々学んだことや経験したことを整理していきたいと思い、ブログを始めました。旅行、カメラ、IT技術、江戸文化が大好きですので、これらについても記事にしていきたいと思っています。