はじめに
業務では「生データを取得」→「加工」→「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
- ローコードで実装することができる
-
料金形態
- 使い方によっては料金が心配になる
- JSONのような入れ子構造の加工は不向き
- Azure SQLで提供されている
MERGEステートメントを使用する
Azure Data Factory(ADF)はローコードで非エンジニアでも構築が可能な作りになっており簡単にUpsertを実現することができる。一方で、データ量やインスタンスの実行回数が多すぎるとコスト面が気になってきます。また、ADFではJSONのような入れ子のデータ構造を加工するのが苦手なようであまりプログラマブルにできないという点で、Azure SQLで提供されているMERGEステートメントをスクリプトで実行することに決めました。スクリプトの場合、加工は自由です。
Azure SQLのMERGE
Azure SQLではMERGEというステートメントが用意されています。
以下は説明で使用するDBのスキーマです。
CREATE SCHEMA Test;
Go
CREATE TABLE Test.Person (
Id INT(4) NOT NULL,
Name VARCHAR(64) NOT NULL,
Age INT(3) DEFAULT NULL,
);
Go
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というモジュールのMicrosoft.Data.SqlClientを用います。接続文字列についてはAzure SQLのポータルから取得することが可能です。接続文字列にAuthentication="Active Directory Default"の文字が入っていればログイン済みのユーザー/サービスプリンシパルのEntra IDでAzure SQLへ接続することができます。
# 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ステートメントを接続処理後に書けば実行することができます。
# 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構成が組めるため、サービスプリンシパルのEntra IDを用いてDBのパスワードを保持せずに接続することができ非常に便利です。
まとめ
本記事では、PowerShellでAzure SQLへUpsertする方法を紹介しました。また、Entra IDでの接続が可能なため、OIDC構成を用いることでCI/CDにも組み込みやすくなっています。Azure Data FactoryはローコードでUpsertをかなり簡単に実現できますが、課金額が許容できないほどだったため諦めました。