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

#1. はじめに
###1-1. ご挨拶
初めまして、井村と申します。
プロジェクトにて「Azure Synapse Analytics」を用いて「Azure SQL Databse」へのアップサート処理が必要となりました。Microsoft公式ドキュメントにて参考記事を見つけ実際に構築しました。Microsoft公式ドキュメントでは簡潔に記載されているので、本記事では構築時の画像やテストを含めた記事を投稿いたします。
※参考記事は「[Azure SQL Database にデータを読み込む際のベスト プラクティス](https://docs.microsoft.com/ja-jp/azure/data-factory/connector-azure-sql-database?tabs=data-factory#best-practice-for-loading-data-into-azure-sql-database)」になります。

###1-2. 対象読者
- Azure portalからAzureサービスを操作したことある。
- Azure Synapse Analyticsに興味がある。

###1-3. 成果物

|<img width="500" alt="architecture.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/38eafc34-337d-a109-2e5f-914ebe732c15.png">|
|:-:|

- 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 ワークスペースの作成](https://docs.microsoft.com/ja-jp/azure/synapse-analytics/get-started-create-workspace)」よりAzureポータルからポチポチでOKです。「ADLSGen2」も忘れずに作成します。

|<img width="500" alt="01ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/69c7fad2-cad0-7e87-aff9-4b1c544e1ec5.png">|
|:-:|

###2-2. 「Azure SQL Database」の構築
「[クイックスタート: Azure SQL Database の単一データベースを作成する](https://docs.microsoft.com/ja-jp/azure/azure-sql/database/single-database-create-quickstart?tabs=azure-portal)」よりAzureポータルからポチポチでOKです。もちろんスペックは最低値で大丈夫です。

|<img width="500" alt="02ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/1f9744bd-180d-7059-ed02-7445549b9bed.png">|
|:-:|

###2-3. テーブル、ストアドプロシージャの構築
「[データのコピー ツールを使用して Azure Blob Storage から SQL データベースにデータをコピーする](https://docs.microsoft.com/ja-jp/azure/data-factory/tutorial-copy-data-tool)」よりAzureポータルからポチポチでOKです。まずはCSVファイルを「ADLSGen2」へアップロードします。

|<img width="500" alt="12ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/a3fd2bd8-78c2-6205-3bcf-60afab36ac62.png">|
|:-:|

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

|<img width="500" alt="04ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/8dfe8f94-f551-011b-0877-517e61cb9b2b.png">|
|:-:|

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

|<img width="500" alt="05ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/dd4075ef-eadc-6091-a3eb-c4c1546095a4.png">|
|:-:|

```erb: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);
```
```erb: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);
```
```erb: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」で、 [統合] ハブに移動します。+ を選択して「パイプライン」をクリックします。

|<img width="500" alt="06ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/e488a5c5-4e7a-0c24-8d11-6d256d0653f4.png">|
|:-:|

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

|<img width="500" alt="07ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/9b925148-07d7-c33f-40f0-6ab52136a294.png">">|
|:-:|

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

|<img width="500" alt="13ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/57e3eb56-cc62-e6d0-d04c-4f1723213483.png">|
|:-:|

|<img width="500" alt="14ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/9fef7737-7bf4-0f9a-8dc5-a2a542df6eca.png">|
|:-:|

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

|<img width="500" alt="15ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/585f3c0b-68b4-ce2a-24d8-a20fce154576.png">|
|:-:|

|<img width="500" alt="16ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/11cf63ad-d998-2542-e601-6f6b30910bca.png">|
|:-:|

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

|<img width="500" alt="17ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/bee965d0-a62a-d608-fa83-5d0a89c9ac74.png">|
|:-:|

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

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

|<img width="500" alt="18ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/796806ad-9875-5639-0d6d-3817551a7392.png">|
|:-:|

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

|<img width="500" alt="19ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/84286b07-0291-80db-8f6a-c94a9cf85ab2.png">|
|:-:|

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

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

|<img width="500" alt="20ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/d27f35d7-f112-3afb-9af6-daa96a9c95ad.png">|
|:-:|

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

|<img width="500" alt="21ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/fff1e8f8-d5c3-ca16-7334-ea565d280a27.png">|
|:-:|

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

|<img width="500" alt="22ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/99a0075d-864b-635c-4805-8018625afbf7.png">|
|:-:|

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

|<img width="500" alt="23ok.jpeg" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/aafa3839-1aff-9329-1f93-ce5fe51d737f.jpeg">|
|:-:|

結果は以下になります。

|<img width="500" alt="24ok.PNG" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1989465/e7a0ec27-94d0-7557-342f-fe48c3c0c06c.png">|
|:-:|

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

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

#6. 参考記事
- [Synapse ワークスペースの作成](https://docs.microsoft.com/ja-jp/azure/synapse-analytics/get-started-create-workspace)
- [クイックスタート: Azure SQL Database の単一データベースを作成する](https://docs.microsoft.com/ja-jp/azure/azure-sql/database/single-database-create-quickstart?tabs=azure-portal)
- [データのコピー ツールを使用して Azure Blob Storage から SQL データベースにデータをコピーする](https://docs.microsoft.com/ja-jp/azure/data-factory/tutorial-copy-data-tool)
-  [Azure SQL Database にデータを読み込む際のベスト プラクティス](https://docs.microsoft.com/ja-jp/azure/data-factory/connector-azure-sql-database?tabs=data-factory#best-practice-for-loading-data-into-azure-sql-database)