【Azure】Azure Synapse Analyticsを用いてAzure SQL Databaseにデータを読み込む際のベスト プラクティス

#1. はじめに
###1-1. ご挨拶
初めまして、井村と申します。
プロジェクトにて「Azure Synapse Analytics」を用いて「Azure SQL Databse」へのアップサート処理が必要となりました。Microsoft公式ドキュメントにて参考記事を見つけ実際に構築しました。Microsoft公式ドキュメントでは簡潔に記載されているので、本記事では構築時の画像やテストを含めた記事を投稿いたします。
※参考記事は「Azure SQL Database にデータを読み込む際のベスト プラクティス」になります。

###1-2. 対象読者

• Azure portalからAzureサービスを操作したことある。
• Azure Synapse Analyticsに興味がある。

###1-3. 成果物

• Azure Data Lake Storage Gen2(以降ADLSGen2)：オブジェクト ストレージ ソリューションです。今回はCSVファイルを１つ格納します。「Azure Synapse Analytics」に含まれるサービスです。
• Synapse pipeline：ETLツールです。「Azure Synapse Analytics」に含まれるサービスです。今回はCopyアクティビティとストアドプロシージャアクティビティを連結させたパイプラインを作成します。
• Azure SQL Database(以降SQLDB)：リレーショナルデータベースです。今回はテーブルを２つ作成します。

#2. 構築_準備編
「Synapse pipeline」以外のインフラを構築します。
###2-1. 「Azure Synapse Analytics」および「ADLSGen2」の構築
「Synapse ワークスペースの作成」よりAzureポータルからポチポチでOKです。「ADLSGen2」も忘れずに作成します。

###2-2. 「Azure SQL Database」の構築
「クイックスタート: Azure SQL Database の単一データベースを作成する」よりAzureポータルからポチポチでOKです。もちろんスペックは最低値で大丈夫です。

###2-3. テーブル、ストアドプロシージャの構築
「データのコピー ツールを使用して Azure Blob Storage から SQL データベースにデータをコピーする」よりAzureポータルからポチポチでOKです。まずはCSVファイルを「ADLSGen2」へアップロードします。

次は「SQLDB」上にテーブル（TempTable、TargetTable）およびストアドプロシージャ（spMergeData）を作成します。「SQLDB」左ペインの「クエリ エディター(プレビュー)」をクリックし、「ログイン」、「パスワード」を入力後、「OK」ボタンを押下します。

以下にSQL文を実行した結果になります。SQL文も記載いたします。

TempTable

CREATE TABLE dbo.TempTable
(
    ID int IDENTITY(1,1) NOT NULL,
    FirstName varchar(50),
    LastName varchar(50)
)
GO
CREATE CLUSTERED INDEX IX_emp_ID ON dbo.TempTable (ID);

TargetTable

CREATE TABLE dbo.TargetTable
(
    ID int IDENTITY(1,1) NOT NULL,
    FirstName varchar(50),
    LastName varchar(50)
)
GO
CREATE CLUSTERED INDEX IX_emp_ID ON dbo.TargetTable (ID);

StoredProcedure

CREATE PROCEDURE [dbo].[spMergeData]
AS
BEGIN
  SET IDENTITY_INSERT dbo.TargetTable ON
  MERGE dbo.TargetTable AS target
  USING dbo.TempTable AS source
  ON (target.[ID] = source.[ID])
  WHEN MATCHED THEN
    UPDATE SET
    FirstName = source.FirstName,
    LastName = source.LastName
  WHEN NOT matched THEN
    INSERT([ID], [FirstName], [LastName])
    VALUES (source.ID, source.FirstName, source.LastName);
  TRUNCATE TABLE dbo.TempTable
  SET IDENTITY_INSERT dbo.TargetTable OFF
END

#3. 構築_パイプライン構築編
それではパイプラインを作成します。Azureポータルの「Azure Synapse Analytics」から「Synapse Studio」を起動させます。
###3-1. コピーアクティビティの構築
「Synapse Studio」で、 [統合] ハブに移動します。+ を選択して「パイプライン」をクリックします。

[アクティビティ] で [移動と変換] フォルダーを展開し、「データのコピー」オブジェクトをデザイナーにドラッグします。
※以下画面はドラッグ後になります。

">

ソースタブで「ADLS Gen2」のCSVファイルを選択します。

シンクタブで「SQLDB」の「TempTable」を選択します。

マッピングタブで「スキーマのインポート」をクリックして、「Firstname」、「Lastname」を紐づけます(自動)。

これでコピーアクティビティの構築が完了です。

###3-2. ストアドプロシージャアクティビティの構築
最後の構築になります。
[アクティビティ] で [全般] フォルダーを展開し、「ストアドプロシージャ」オブジェクトをデザイナーにドラッグします。
「データのコピー」オブジェクトと「ストアドプロシージャ」オブジェクトを紐づけます。
※以下画面はドラッグ後になります。

設定タブで「SQLDB」および「ストアドプロシージャ」を選択します。
画面上部の「すべての発行」をクリックしたら、構築は完了となります。

#4. テスト
それではテストをしましょう。
最初のテストは構築したパイプラインを実行して「SQLDB」の「TargetTable」を確認します。
次のテストはCSVファイルの中身を修正して再度パイプラインを実行して「TargetTable」を確認します。

###4-1. 最初のテスト
まずはSQLのSELECT文を実行し、「TargetTable」を確認。当然データはありません。

パイプラインを実行します。「トリガーの追加」から「今すぐトリガー」をクリックします。

「TargetTable」はCSVファイルを取り込み、以下のようになりました。
しっかり取り込めています。

###4-2. 次のテスト
それではCSVファイルを変更します。
以下に変更し、再度パイプラインを実行します。

結果は以下になります。

無事に更新されました。「ID」が"2"である「Lastname」の"Doe"が"hogehoge"へアップデート、「ID」が"3"であるデータがインサートされています。「TempTarget」テーブルは「コピーアクティビティ」で全データをコピー後、「ストアドプロシージャアクティビティ」のTRUNCATE処理にて全データ削除しています。

#5. おわりに
本記事を最後まで読んで頂きましてありがとうございます。
これら一連の流れはデータ分析基盤の「ADLSGen2」がデータレイク、「SQLDB」の「TargetTable」がデータウェアハウスとしての一例になります。「Azure Synapse Analytics」はたくさんの機能がありますのでこれからも学んでいきます。

#6. 参考記事

• Synapse ワークスペースの作成
• クイックスタート: Azure SQL Database の単一データベースを作成する
• データのコピー ツールを使用して Azure Blob Storage から SQL データベースにデータをコピーする
• Azure SQL Database にデータを読み込む際のベスト プラクティス