こんばんは。この記事では、Azure SQL Database上のテーブルに対してBULK INSERTを実行する方法を整理しておきます。
それではまいります。
Contents
ローカルのCSVをBULK INSERTすることはできない
まず、はじめにSSMSでAzure SQL Databaseに接続して、ローカルで持っているCSVファイルをアップロードする、ということはできません。以下のようなエラーが出ます。
メッセージ 4861、レベル 16、状態 1、行 16
Cannot bulk load because the file "ファイル名" could not be opened. Operating system error code (null).
この点は以下のブログ記事などでも言及されています。
https://teratail.com/questions/340124
公式ドキュメントのここの記載が該当しているのかな？
BULK INSERT または OPENROWSET(BULK…) を使用して SQL Server にデータをインポートする – SQL Server | Microsoft Docs
解決方法：対象ファイルをAzure Storageに配置して読み込む
というわけで、解決方法としては、BULK INSERT対象のファイルをAzure Blob Storage上に配置して読み込む方法があるようです。
Azure BLOB ストレージのデータに一括アクセスする – SQL Server | Microsoft Docs
BULK INSERT または OPENROWSET(BULK…) を使用して SQL Server にデータをインポートする – SQL Server | Microsoft Docs
ので、今回はこれを試してみました。
Azure Blob Storageにデータを配置する
はじめにBULK INSERTしたいデータをBLOBストレージの適当なコンテナに格納します。
ファイルのSAS トークン&URLを発行する
続いて、アップロードしたファイルのSAS URLを取得します。
SAS URLってなんだ？という方は以下の記事をご参照ください。
対象コンテンツ右側のオプションからURLの生成が行えます。
いったんは規定値のまま、一日の有効期限で発行しましょう。
すると、コンテンツのURL + SASトークンの形で、SAS URLが発行されますので、これを控えておきます。
マスターキーとデータベーススコープの資格情報を作成する
続けて、Azure SQL Database側にデータベーススコープの資格情報を作成します。結論、SSMSまたはクエリエディタから以下のように実行すればOKです。
CREATE MASTER KEY ENCRYPTION BY PASSWORD='★任意の強力なパスワード★';
CREATE DATABASE SCOPED CREDENTIAL ★任意の資格情報名★
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '★BLOBコンテンツのSASトークン★';と、ここで作成したマスターキーとデータベーススコープの資格情報とは何でしょうか。
データベースマスターキー
SQL Server とデータベース暗号化キー – SQL Server | Microsoft Docs
- 証明書の秘密キーやデータベース内の非対称キーを保護する際に使用するキー
- 対象キー（暗号化と復号化の両方に使えるキー）
- サービスマスターキーによって保護される
というのが一般的な説明のようですが、今回の場合は、データベーススコープの資格情報を保護するために使用されることになります。
ちなみに、サービスマスターキーやデータベースマスターキーといった概念の関係は以下Docsで図でもまとめられているようです。ぱっと見よく分からない図だなと思いましたが、データベースマスターキーは、サービスマスターキーに保護され、そして証明書や非対称キーを保護する、という上述の関係は読み取れました。
暗号化階層 – SQL Server | Microsoft Docs
データベーススコープの資格情報
CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL) – SQL Server | Microsoft Docs
- SQL Server 外部のリソースへの接続に必要な認証情報を含むレコードで、通常、 Windows ユーザーとパスワードが含まれる
今回の場合は、データベースがAzure Storageに接続するために必要な認証情報のレコードを作っていることになります。
ちなみに、MASTER KEY作成なしでデータベーススコープの資格情報作成コマンドだけ実行すると以下のエラーが出ます。
メッセージ 15581、レベル 16、状態 6、行 25
Please create a master key in the database or open the master key in the session before performing this operation.
エラーメッセージの通り、この前にマスターキーなるものを作成しておく必要があるとのことでした。
外部データソースを構成
資格情報を作成したら、外部データソースの作成です。以下コマンドを実行します。
CREATE EXTERNAL DATA SOURCE ★任意の外部データソース名★
WITH (
TYPE = BLOB_STORAGE,
LOCATION = 'https://＜★ストレージアカウント名★＞.blob.core.windows.net',
CREDENTIAL = ★先ほど作成した資格情報名★
);すると、外部データソースが作成されたことが確認できます。
BULK INSERT！
ここまできて、ようやくBULK INSERTができます！
BULK INSERT ★INSERT先テーブル名★
FROM '★コンテナ名★/★ファイル名★'
WITH
(
DATA_SOURCE = '★外部データソース名★',
FIRSTROW = 2,
FORMAT = 'CSV'
)＃実は今回は、公開データセットであるTitanicの乗客リストをBULK INSERTしてみていたのですが、以下のように無事BLOB STORAGEからBULK INSERTすることができました！
うーむ、SQL Database (SQL Server)の扱いに慣れていないからかわかりませんが、ちょっと準備に時間がかかる作業でした・・・特に資格情報の作成と外部データソースの作成あたり。
初心者目線だとSAS URLとかからもう少し直感的に読み込めてもいい気はしましたが、いったんドキュメントにはこの手順で紹介されているということで。
勉強になりました！
参考になりましたら幸いです。
おしまい
コメントを残す