Azure DB連携

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

 

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

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

今回は、BULK INSERT文を利用して、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
BULK INSERT文によるデータ追加_1_1

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

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

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(何らかのパスワード)'
BULK INSERT文によるデータ追加_3_1

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

SELECT * FROM sys.symmetric_keys
BULK INSERT文によるデータ追加_3_2

4) Azure Blob Storageにアクセスするための資格情報にSASを指定する必要があるため、SASトークンを作成する。
BULK INSERT文によるデータ追加_4_1

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

SASトークンやURLが作成されるため、「BLOB SASトークン」の値をコピーしておく。
BULK INSERT文によるデータ追加_4_3

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

CREATE DATABASE SCOPED CREDENTIAL (資格情報名) 
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '(BLOB SAS トークンの値)'
BULK INSERT文によるデータ追加_5_1

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

SELECT * FROM sys.database_scoped_credentials
BULK INSERT文によるデータ追加_5_2

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

CREATE EXTERNAL DATA SOURCE (外部データソース名)
    WITH ( TYPE = BLOB_STORAGE,
           LOCATION = ' (Azure Blob StorageのコンテナURL)',
           CREDENTIAL=  (資格情報名))
BULK INSERT文によるデータ追加_6_1

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

BULK INSERT文によるデータ追加_6_2

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

SELECT * FROM sys.external_data_sources
BULK INSERT文によるデータ追加_6_3

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

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

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

SELECT * FROM dbo.USER_DATA
BULK INSERT文によるデータ追加_8

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

DROP EXTERNAL DATA SOURCE (外部データソース名)
BULK INSERT文によるデータ追加_9_1

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

SELECT * FROM sys.external_data_sources
BULK INSERT文によるデータ追加_9_2

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

DROP DATABASE SCOPED CREDENTIAL (資格情報名)
BULK INSERT文によるデータ追加_10_1

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

SELECT * FROM sys.database_scoped_credentials
BULK INSERT文によるデータ追加_10_2

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

DROP MASTER KEY
BULK INSERT文によるデータ追加_11_1

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

SELECT * FROM sys.symmetric_keys
BULK INSERT文によるデータ追加_11_2

要点まとめ

  • BULK INSERT文を利用して、CSVファイルのデータをAzure SQL Databaseのテーブルに追加するには、CSVファイルをAzure Blob Storageに配置する必要がある。
RELATED POST