Azure Blob Storage に配置されたファイルを SQL Database に Bulk Insert する

業務システムなどで、 Blob に CSV が配置されていて、そのファイルの内容を DB に Bulk Insert したいといった要望は、比較的あるあるかと思います。

ファイルサイズが小さければどうとでもなるのですが、それなりにサイズの大きなファイルですと、ちまちまインサートするとそれだけで日が暮れてしまうので、 SQL Database (SQL server)の機能を頑張って使って、あれこれやった備忘録を残します。

今回の動機

今まで、Azure Functions の Blob トリガーでファイルの配置を検知し、PowerShell Function から BCP コマンドを流したりして頑張っていました。

ですが、ここ半年ほど不可解な挙動をするようになり(流したいコマンドをまとめたファイルが、不定期に読み込みに失敗する等)ちょっと運用が辛い問題がありました。

そこで、そういう問題が極力減るように、SQL Server 側でファイルを読み込み、C# の Function App でトリガーだけする方式に切り替えようとしたためです。

SQL Server から Blob へアクセスできるようにする

マスターキーを作成する

まず初めに、SQL Database でデータベースマスターキーを作成していない方は、次のようにマスターキーを作成します。

CREATE MASTER KEY ENCRYPTION BY PASSWORD='{Your Password}'

データベース資格情報を作成する

次に Blob のコンテナにアクセスするための資格情報を作成します。

資格情報の作成には、SAS が必要なので、Storage Explorer なり、ポータルなりで SAS を生成してください。

SAS がある人は、次の SQL を実行します。

CREATE DATABASE SCOPED CREDENTIAL [{Your Credential Name}]
WITH IDENTITY = 'Shared Access Signature',
SECRET = '{Your SAS}'

Your Credential Name : 資格情報名(コンテナの URL とかにしておくと分かりやすい気がします。)
Your SAS : sv ~ から始まる SAS を指定します。ポータルなどでコピーしてくると先頭に ? が付いてくるかもしれないので、それは削除しましょう

この SQL が成功したら、次の SQL を実行して、実際に登録されているか確認します。

SELECT * FROM sys.[database_scoped_credentials]

name が先ほどの資格情報名のモノが出てきたら成功です。

外部ソースを設定する

最後に外部ソースとして、コンテナを登録します。

次の SQL を実行します。

CREATE EXTERNAL DATA SOURCE [{Your External Source Name}]
WITH(
    TYPE = BLOB_STORAGE,
    LOCATION = '{Your Blob Url}',
    CREDENTIAL = [Your Credential Name]
)

Your External Source Name : 外部ソース名です。SQL からこの外部ソースを参照する場合に指定するモノなので、分かりやすいものを付けましょう。
Your Blob Url : Blob ストレージの URL を指定します。(https://{ストレージアカウント名}.blob.core.windows.net)
Your Credential Name : 使用するデータベース資格情報名を指定します。

この SQL が成功したら、次の SQL を実行します。

name が先ほどの外部ソース名のモノが出てきたら、成功です。

SELECT * FROM sys.[external_data_sources]

実際に Bulk Insert してみる

事前に Insert したいテーブルなどは準備しておきましょう

次のような SQL で利用できます。

BULK INSERT {Your Table Name}
FROM '{Your Container Name}/{Blob Name}'
WITH 
(
    DATA_SOURCE = '{Your External Source Name}'
    , CODEPAGE = '65001'
    , FORMAT = 'CSV'
    , ROWTERMINATOR = '0x0a'
)

Your Table Name : Insert したいテーブル名
Your Container Name : 対象のコンテナ名
Blob Name : コンテナに配置されている Blob 名
Your External Source Name : 先ほど作成した外部ソース名

その他の設定としては、コードページが 65001、フォーマットが CSV、改行が 0x0a のファイルという情報を与えています。

細かいパラメータは、公式ドキュメントをご参照ください。

learn.microsoft.com

まとめ

これで、資格情報等は全て SQL Server 側に保持され、SQL さえ呼び出せれば、処理は全て SQL Server 側で行ってくれるようになります。

次回は、Azure Functions と組み合わせた例をまとめます。(気が向いたら)

ごった煮

色々な事を書いてます