Azure SQLでBULK INSERTを使う方法【Blob Storage】

API

はじめに

結構ニッチなやつですが。

Azure SQLのBULK INSERTで詰まったので投稿します

前提として以下が必要です
・Azureサブスクリプション
・SQL Server(リソース+DB)
・BlobStorage(リソース+ストレージ)

Azure SQLとは

Azureクラウド上で動くSQL Serverです

SQL文はオンプレミス(普通の?)SQLServerほとんど同様。
ただ、クラウド上にあるので認証等のインフラは別物です。
ファイアーウォール設定、AzureAD認証、vnetで帯域設定するなどの対応が必要です
(今回はほとんど触れません)

認証は単純にSQLServer認証(ID、パスワード)を使います

BULK INSERT(T-SQL)とは

データを高速で一括挿入することが可能な処理です
基本的にはCSVファイルを読み込んで、外部データとしてDBに登録するイメージです

データが10万件など、大量のデータを処理する場合は
何回もInsert分を実行するのではなくBULKINSERTで実行するほうが圧倒的に性能が良いので極力こちらを採用したいです

注意したいのは
AzureSQLとSQLServer(オンプレミス版)では仕様が異なることですね

SQLServer(オンプレミス版)ではローカルのCSVを読み込むことができますが
AzureSQLではローカルのCSVを読み込むことができません

ではAzureSQLの場合、CSVをどこに置くのかというと
BlobStorageというAzureのストレージに配置する必要があります
ですので今回はAzureSQLとBlobStorageを利用する方法を順を追って紹介します

以下、公式のBULK INSERT (Transact-SQL)のドキュメントなので参考までに

BULK INSERT (Transact-SQL) - SQL Server
BULK INSERT ステートメントの Transact-SQL リファレンス。

準備 – Azure SQL –

Azure PortalへログインしてSQLデータベースを作成します

Azure SQLを初めて使う人は以下が必要になります
順番に見ていきましょう

・サーバの作成
・SQLデータベースの作成

ネットワーク、セキュリティは任意で設定可能ですが
今回は変更せずデフォルトで進めていきます

作成ボタンで作成します
Azureは基本的に従量課金制なので、ここから使用するごとに料金が発生します
個人の場合は、使っていないリソースなどは逐一削除するなどの意識を持っておくと良いです
その方がセキュリティ的にも安心です。

デプロイが完了したら以下の方法でファイアーウォールの設定をしましょう

続いてCSV取り込みのためのテーブルを作成しましょう
以下のクエリを実行します

-- ★CREATE文サンプル

CREATE TABLE 男女別人口 (
都道府県コード nvarchar(100),
都道府県名 nvarchar(100),
元号 nvarchar(100),
和暦_年 int,
西暦_年 int,
注 nvarchar(100),
人口_総数 int,
人口_男 int,
人口_女 int
Primary key(都道府県コード, 西暦_年)
)

SQLはクエリエディターから実行します

テーブルが作成されていることを確認する

ここまででAzure SQLの設定はOKです

準備 – Blob Storage –

続いてBlob Storageのリソースを準備します
Azure Portalからストレージ アカウントへ移動します

ストレージの作成がまだの場合は以下の手順で作成します

詳細設定等は任意で設定してください
今回はデフォルトの設定で進めていきます

「確認および作成」を押下します

作成ボタンを押せば、デプロイが開始されます
Blob Storageに関してもAzure SQLと同様、ここから料金が発生しますのでご注意ください

作成したストレージへ移動して、コンテナーというものを作成します
コンテナーはフォルダ単位でファイルを格納するための箱のようなもので、
コンテナー名と、コンテナー以下の相対パスはそれぞれ別で管理されることが多いです

コンテナーの作成においてパブリックアクセスレベルを設定します
今回はコンテナー(コンテナーとBLOBの匿名読み取りアクセス)に設定します
基本的にはプライベート設定にして外部アクセスを制限するのですが、
趣旨からそれてしまうので、セキュリティ面の対応は割愛します。

パブリックアクセルレベルがコンテナーに設定されていることを確認

続いてCSVファイルをアップロードしてあげましょう

と、その前にコンテナー直下にファイルを置くのはイマイチなので
コンテナーの下にCSVというフォルダを作成してあげます

続いて、CSVファイルをアップロードします
以下の国勢調査のデータを使わせていただきます

国勢調査 時系列データ CSV形式による主要時系列データ | ファイル | 統計データを探す | 政府統計の総合窓口
 国勢調査は、日本に住んでいるすべての人と世帯を対象とする国の最も重要な統計調査で、5年ごとに実施されます。国勢調査から得られる日本の人口や世帯の実態は、国や地方公共団体の行政において利用されることはもとより、民間企業や研究機関でも広く利用され、そのような利用を通じて国民生活に役立てられています。  国勢調査では、年齢...

こちらからダウンロード可能です

ダウンロードしたCSVですが以下に余計な文章が入っているので(CSVデータとしてどうかと思いますが)削除してあげましょう

面倒くさい人は以下を.csvで作成してください
行数の関係でデータ少なめです

"都道府県コード","都道府県名","元号","和暦(年)","西暦(年)","注","人口(総数)","人口(男)","人口(女)"
"00","全国","大正",9,1920,"",55963053,28044185,27918868
"01","北海道","大正",9,1920,"",2359183,1244322,1114861
"02","青森県","大正",9,1920,"",756454,381293,375161
"03","岩手県","大正",9,1920,"",845540,421069,424471

CSVファイルはアップロードする前に
必ず文字コードをUnicode(UTF-16)に変換してください
BULKINSERTではUTF-8などの文字コードのファイルだと文字化けしてしまうので注意が必要です

CSVが準備できたらアップロード

アップロードがされていればOKです

BULK INSERTを実行

それではBULK INSERTを実行しましょう

-- ★DROP EXTERNAL DATA SOURCE
IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'MyAzureBlobStorage')
DROP EXTERNAL DATA SOURCE MyAzureBlobStorage 

-- ★CREATE EXTERNAL DATA SOURCE
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
        LOCATION = 'https://<①ストレージ名>.blob.core.windows.net/<②コンテナー名>' -- ★今回はsample-csv
);
GO

-- BULK INSERT
BULK INSERT <③テーブル名> -- ★今回はdbo.男女別人口
FROM '<④コンテナー以下のファイルパス>' -- ★今回はCSV/c01.csv
WITH (
    DATA_SOURCE = 'MyAzureBlobStorage',
	FORMAT = 'CSV',
	FIELDTERMINATOR = ',',
	DATAFILETYPE='char',
	ROWTERMINATOR = '\n'
);

ちょっとだけ解説します

LOCATION = ‘https://<①ストレージ名>.blob.core.windows.net/<②コンテナー名>’

<①ストレージ名>、<②コンテナー名>には
準備 -Blob Storage-で作成したときのストレージ、コンテナー名を設定します

BULK INSERT <③テーブル名>
FROM ‘<④コンテナー以下のファイルパス>’

<③テーブル名>にはテーブル名「dbo.男女別人口」
<④コンテナー以下のファイルパス>には「CSV/c01.csv」を設定します

1秒もかからずに1000件知覚のデータが登録できました
手順通りに設定ができていれば失敗せずに登録できると思います

実行結果は「データを編集する」か、SELECT文を実行して確認しましょう

文字化けせずに登録されていればOKです

まとめ

簡単にBULK INSERTを実行するまでを紹介しましたが
紹介した方法だとセキュリティ的には微妙なので、現場では使えない方法だと思います
本当はSASトークンというものを作成して一時的にBlobStorageに接続をするか、APIのライブラリを使用して接続するなどの方法が一般的と思います

機会があれば紹介したいですが、AzureSQLって結構高いんですよね。
1日で692円、、

この後リソースは忘れずに削除しようと思います
個人でAzureを勉強するのは難しいですね、、

コメント

タイトルとURLをコピーしました