投稿日：2024/01/20

背景

　お客様から「各テーブルの過去データをAzure Data Lake Storage Gen2(以下、ADLS Gen2と記載)にCSVファイルとして格納しているが、そのデータをSQL Databaseに復元することはできないか？」といったお問い合わせを頂きました。
　私は過去にSynapse Analyticsの環境で同様のお問い合わせを頂くことがあり、その際は外部表の機能を使用して対応しておりました。外部表についての記事はQiitaにもわかりやすく纏まっておりますので気になる方はご参照ください。
　しかし、SQL DatabaseにおけるCREATE EXTERANAL TABLEコマンドには以下の制約があり、外部表での復元は難しいと考えました。
　┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
　　Azure SQL Database では、Azure Blob Storage への外部テーブルの作成はサポートされていません。★
　┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
　そのため、今回はBULK INSERTでの対応を調査、検証しましたので以下に結果を記載致します。

環境説明

　今回の環境は以下の通りです。
　■SQL Database / ADLS Gen2
　　パブリック ネットワーク アクセス
　　　選択したネットワークのみ

実施手順

　ストレージアカウントのパブリック ネットワーク アクセス設定が「選択した仮想ネットワークと IP アドレスから有効」となっている場合は、資格情報にマネージド IDを利用します。
　以下、実施手順となります。

　　① SQL Database のマネージド ID設定
　　② ADLS Gen2 のネットワーク設定
　　③ ADLS Gen2 のロールに SQL Databaseを追加
　　④ SQL Database に資格情報を作成
　　⑤ ADLS Gen2に格納されているCSVファイルを取り込む

① SQL Database のマネージドID設定

　SQL Database リソース のマネージド ID が有効になっているか確認します。
　[セキュリティ] > [ID] > [システム割り当てマネージドID]

　設定がオフになっていれば、オンに切り替えて設定を保存しましょう。

② ADLS Gen2 のネットワーク設定

　ADLS Gen2のネットワーク設定で「信頼されたサービスの一覧にある Azure サービスがこのストレージ アカウントにアクセスすることを許可します。」を有効になっているか確認します。
　[セキュリティとネットワーク] > [ネットワーク] > [例外]

③ ADLS Gen2のロールに SQL Databaseを追加

　「ストレージ BLOB データ共同作成者」ロールに SQL Databaseを追加します。
　この操作にはストレージアカウントへの「所有者」の組み込みロールが必要となります。
　[アクセス制御(IAM)] > [追加] > [ロールの割り当ての追加]

　アクセスの割り当て先にマネージド ID を選択し、SQL Serverを選択します。
　設定を行うと下記キャプチャ画面のようにメンバーとしてSQL Serverが加わります。

　最後にメンバーに割り当てるロールを設定します。
　今回は[ストレージ BLOBデータ閲覧者]を選択します。

④ SQL Database に資格情報を作成

　SSMSからSQL Databaseに接続して資格情報をCREATE DATABASE SCOPED CREDENTIALコマンドで作成します。
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
　マネージド ID を使用して Azure Blob Storage からファイルをインポートするには、ID 名が MANAGED IDENTITY である必要があります。★
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

　作成した資格情報を使用し、対象のADLS Gen2へアクセスするためのデータソースをCREATE EXTERNAL DATA SOURCEコマンドで作成します。

-- 資格情報の作成
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = 'Managed Identity'

-- データソースの指定
CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
TYPE = BLOB_STORAGE,
LOCATION = 'https://<ストレージアカウント名>.blob.core.windows.net',
CREDENTIAL = AzureStorageCredential
);

作成した資格情報やデータソースは以下のSQLで確認することができます。

SELECT 
 a.name CredentialName
,a.credential_id
,a.credential_identity
,b.name ExternalDatasource
,b.location
FROM sys.database_scoped_credentials a
LEFT JOIN sys.external_data_sources b
ON a.credential_id = b.credential_id

⑤ ADLS Gen2に格納されているCSVファイルを取り込む

　準備が整いましたので、実際にBULK INSERTでCSVファイルを取り込めるか確認してみましょう。

BULK INSERT [スキーマ名].[テーブル名]
FROM '[コンテナ名]/[プレフィックス名]/[ファイル名.csv]'
WITH (
DATA_SOURCE = '[作成したデータソース名]',
FIRSTROW = 0,
FORMAT = 'CSV'
)

　以下、companyinfoテーブルから一時テーブル #T1を作成し、#T1に対してcsvファイルをBULK INSERTした結果です。