BULK INSERT文を利用してCSVファイルのデータをAzure SQL Databaseのテーブルに追加してみた

BULK INSERT文を利用すると、以下のサイトのように、CSVファイルのデータをSQL Serverのテーブルに追加することができる。
https://qiita.com/fuk101/items/d98716a48d69d5c7f1a7

ただ、Azure SQL Databaseの場合、BULK INSERT文でローカルに存在するCSVファイルをテーブルに追加しようとした場合は、以下のようなエラーが発生してしまう。

今回は、CSVファイルをAzure Blob Storageに配置し、Azure SQL Databaseのテーブルに追加してみたので、その手順を共有する。

前提条件

以下の記事に従って、Azure SQL Database・Azure Blob Storageの作成が完了していること。

Azure Potal上でSQLデータベースを作成してみたAzure Portal上では、常に最新の安定したバージョンの SQL Serverデータベースである「SQL データベース」が利用でき...

Azure Blob Storageを作成しファイルを格納してみたAzure Blob Storageを利用すると、Azure上にBlobデータ(テキストファイルや画像、アーカイブファイル等)を格納する...

また、A5M2からAzure SQL Databaseに接続できていること。その手順は、以下の記事を参考にすること。

A5M2を利用して各DBに接続してみたA5M2(A5:SQL Mk-2)は、SQL文の入力支援やER図作成などの機能を備えていて、Oracle、MySQL、PostgreSQ...

BULK INSERT文によるデータ追加

BULK INSERT文によるAzure SQL Databaseへのデータ追加手順は、以下の通り。

1) 追加前のUSER_DATAテーブルの内容は、以下の通り。

SELECT * FROM dbo.USER_DATA

また、追加対象のデータを記載したCSVファイルの内容は、以下の通り。

2) Azure Portalにログインし、追加対象のデータを記載したCSVファイル(test.csv)を、Azure Blob Storage上のコンテナ(work)に配置する。

3) Azure Blob Storageにアクセスするための資格情報を作成するために必要になる、マスターキーを作成する。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(何らかのパスワード)'

なお、作成したマスターキーは、以下のように確認できる。

SELECT * FROM sys.symmetric_keys

4) Azure Blob Storageにアクセスするための資格情報にSASを指定する必要があるため、SASトークンを作成する。

必要に応じて有効期限を変更後、「SASトークンおよびURLを生成」ボタンを押下する。

SASトークンやURLが作成されるため、「BLOB SASトークン」の値をコピーしておく。

5) Azure Blob Storageにアクセスするための資格情報を作成する。

CREATE DATABASE SCOPED CREDENTIAL (資格情報名) 
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '(BLOB SAS トークンの値)'

なお、作成した資格情報は、以下のように確認できる。

SELECT * FROM sys.database_scoped_credentials

6) Azure Blob Storageにアクセスするための外部データソースを作成する。

CREATE EXTERNAL DATA SOURCE (外部データソース名)
    WITH ( TYPE = BLOB_STORAGE,
           LOCATION = ' (Azure Blob StorageのコンテナURL)',
           CREDENTIAL=  (資格情報名))

なお、上図のLOCATIONは、test.csvを配置したコンテナ(work)のURLを指定している。

また、作成した外部データソースは、以下のように確認できる。

SELECT * FROM sys.external_data_sources

7) BULK INSERT文を利用して、CSVファイルのデータをAzure SQL Databaseのテーブルに追加する。

BULK INSERT 
    (テーブル名)
FROM 
    '(CSVファイル名)'
WITH ( 
    DATA_SOURCE = '(外部データソース名)'
  , FORMAT = 'CSV'
  , FIRSTROW = 1
  , DATAFILETYPE = 'CHAR'
  , CODEPAGE = '65001'
)

8) 追加後のUSER_DATAテーブルの内容は、以下の通り。

SELECT * FROM dbo.USER_DATA

9) 作成した外部データソースは、以下のように削除できる。

DROP EXTERNAL DATA SOURCE (外部データソース名)

なお、削除した外部データソースは、以下のように確認できる。

SELECT * FROM sys.external_data_sources

10) 作成した資格情報は、以下のように削除できる。

DROP DATABASE SCOPED CREDENTIAL (資格情報名)

なお、削除した資格情報は、以下のように確認できる。

SELECT * FROM sys.database_scoped_credentials

11) 作成したマスターキーは、以下のように削除できる。

DROP MASTER KEY

なお、削除したマスターキーは、以下のように確認できる。

SELECT * FROM sys.symmetric_keys

要点まとめ

• CSVファイルのデータをAzure SQL Databaseのテーブルに追加するには、CSVファイルをAzure Blob Storageに配置する必要がある。