Azure SQLに対してPowerShellでUpsertする

# はじめに

　業務では「生データを取得」→「加工」→「Azure SQLへUpsert」という処理を実施しています。本記事では、「加工」と「Azure SQLへUpsert」の箇所を実現するための技術調査とPowerShellでのUpsert方法を記載します。

# 調査

Azure SQLへUpsertする方法として調査すると以下があります。

- Azure Data Factoryを使用する
  - https://learn.microsoft.com/ja-jp/azure/data-factory/connector-azure-sql-database?tabs=data-factory
  - ローコードで実装することができる
  - [料金形態](https://azure.microsoft.com/ja-jp/pricing/details/data-factory/data-pipeline/)
    - 使い方によっては料金が心配になる
  - JSONのような入れ子構造の加工は不向き
- Azure SQLで提供されている`MERGE`ステートメントを使用する
  - https://learn.microsoft.com/ja-jp/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16

　Azure Data Factory(ADF)はローコードで非エンジニアでも構築が可能な作りになっており簡単にUpsertを実現することができる。一方で、データ量やインスタンスの実行回数が多すぎるとコスト面が気になってきます。また、ADFではJSONのような入れ子のデータ構造を加工するのが苦手なようであまりプログラマブルにできないという点で、Azure SQLで提供されている`MERGE`ステートメントをスクリプトで実行することに決めました。スクリプトの場合、加工は自由です。


# Azure SQLのMERGE

https://learn.microsoft.com/ja-jp/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16

Azure SQLでは`MERGE`というステートメントが用意されています。
以下は説明で使用するDBのスキーマです。
```sql
CREATE SCHEMA Test;
Go

CREATE TABLE Test.Person (
  Id INT(4) NOT NULL,
  Name VARCHAR(64) NOT NULL,
  Age INT(3) DEFAULT NULL,
);
Go
```

```sql
MERGE INTO Test.Person AS 
target USING (VALUES (1234, "ゆうた", 10)) AS source (Id, Name, Age)
ON target.Id = source.Id
WHEN MATCHED THEN
  UPDATE SET Name = source.Name, Age = source.Age
WHEN NOT MATCHED THEN
  INSERT (Id, Name, Age) VALUES (source.Id, source.Name, source.Age);
```

上記は`(1234, "ゆうた", 8)`のレコードをUpsertするコードです。`Id`の値がDB内に存在した場合`UPDATE`、存在しない場合`INSERT`が実行されます。

# PowerShellでAzure SQLに接続する

 Azure SQLはAzure Entra IDに基づいた接続が可能です。Powershellで実現する方法として、[SqlServerというモジュール](https://www.powershellgallery.com/packages/SqlServer)の`Microsoft.Data.SqlClient`を用います。接続文字列については[Azure SQLのポータルから取得することが可能](https://learn.microsoft.com/ja-jp/azure/azure-sql/database/connect-query-content-reference-guide?view=azuresql#get-adonet-connection-information-optional---sql-database-only)です。接続文字列に`Authentication="Active Directory Default"`の文字が入っていればログイン済みのユーザー/サービスプリンシパルのEntra IDでAzure SQLへ接続することができます。

```powershell
# Install Module
Install-PSResource -Name SqlServer
Import-Module SqlServer

# Entra IDを用いてDBへ接続
$connectionString = 'Server=tcp:sql-hoge.database.windows.net,1433;Initial Catalog=sql-hoge-hogehoge-databases;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";';
$connection = New-Object Microsoft.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# 接続を閉じる
$connection.Close()
```

# PowerShellのスクリプト

以下のように、`MERGE`ステートメントを接続処理後に書けば実行することができます。

```powershell
# Install Module
Install-PSResource -Name SqlServer
Import-Module SqlServer

# Entra IDを用いてDBへ接続
$connectionString = 'Server=tcp:sql-hoge.database.windows.net,1433;Initial Catalog=sql-hoge-hogehoge-databases;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";';
$connection = New-Object Microsoft.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# SQLコマンドを実行
$commandText = @"
MERGE INTO Test.Person AS 
target USING (VALUES (1234, "ゆうた", 8)) AS source (Id, Name, Age)
ON target.Id = source.Id
WHEN MATCHED THEN
  UPDATE SET Name = source.Name, Age = source.Age
WHEN NOT MATCHED THEN
  INSERT (Id, Name, Age) VALUES (source.Id, source.Name, source.Age);
"@
$command = $connection.CreateCommand()
$command.CommandText = $commandText
$command.ExecuteNonQuery()

# 接続を閉じる
$connection.Close()
```

　筆者はGithub Actions上で生データを取得しスクリプトを実行してDBへ投入しています。
Github Actionsには[OIDC構成](https://docs.github.com/en/actions/security-for-github-actions/security-hardening-your-deployments/configuring-openid-connect-in-azure)が組めるため、サービスプリンシパルのEntra IDを用いてDBのパスワードを保持せずに接続することができ非常に便利です。

![azure-sql-powershell-upsert.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3592580/faffdace-b61e-3d83-cb26-35156ca5472b.png)

# まとめ

　本記事では、PowerShellでAzure SQLへUpsertする方法を紹介しました。また、Entra IDでの接続が可能なため、OIDC構成を用いることでCI/CDにも組み込みやすくなっています。Azure Data FactoryはローコードでUpsertをかなり簡単に実現できますが、課金額が許容できないほどだったため諦めました。