SQL DatabaseでAzure Storageに配置したファイルからbulk Insertが出来るようになったので試してみた

SQL DatabaseでAzure Storageに配置したCSVファイル等からbulk Insertが出来るようになったので試してみました。

サンプルソースがあるのですが嵌りポイントが多かったのでその点についても言及したいと思います。

詳細については下記を参照してください。

Loading files from Azure Blob storage into Azure SQL Database

ちなみにPublic Previewです。

※この記事は2017年3月1日時点の情報となります。

サンプルソース

サンプルソースをベースに試してみたのでサンプルソースを基に記載していきます。

サンプルソース

サンプルソースではsqlファイルひとつとサンプルデータが3種類用意されています。

今回はCSVファイルからbulk Insertする方法を試します。

クエリの実行にはSSMSを利用してください。

先ずサンプルデータファイルをAzure Storageに配置しましょう。

任意のAzure Storageを作成しdataというコンテナを作成しcsvのサンプルファイルを配置します。

配置したときにcontent-typeがapplicaiton/octet-streamになっている場合は念のためtext/csvに変更しておいてください。

次にsqlファイルの中身を見ていきます。sqlファイルは「SETUP」と「CREATE DESTINATION TABLE」と「LOAD」の3処理に分けられています。ひとつづつ見ていきましょう。

「SET UP」

-- 暗号化ようのMASTER KEYを生成します。入力するパスワードに任意の文字を入力してください。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password';
-- Azure StorageにアクセスするためのSASトークンを設定します。
-- SasトークンはAzurePortalなどで作成してください。
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2015-12-11&ss=b&srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z&spr=https&sig=copyFromAzurePortal';

-- AzureStorageの設定を行います。Locationを任意のStorage名にしてください。
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (  TYPE = BLOB_STORAGE,
LOCATION = 'https://{Storage名}.blob.core.windows.net',
CREDENTIAL= MyAzureBlobStorageCredential);

最初に実行するクエリです。

暗号化用のMASTER KEYの作成とAzure Storageアクセス用のSASトークンの作成、接続先Azure Storageの情報を作成します。SASトークンは入力済みですがExpireされている日付に設定されているので任意に作成して書き換えてください。作成はAzure PortalからAzure Storageにアクセスして「Shared Access Signature」から生成します。

このクエリは最初の1回の発行だけで問題ありませんがSASトークンの設定にミスがあるとCREDENTIALの再作成の必要があるので注意してください。基本的にはDROPして作り直す形になります。DROP時にエラーになる場合の対処が結構めんどくさいのでその時はDBを再作成してください。

「CREATE DESTINATION TABLE 」

-- テーブルがあれば削除します
DROP TABLE IF EXISTS Product;
GO

-- テーブルを作成します
CREATE TABLE dbo.Product(
Name nvarchar(50) NOT NULL,
Color nvarchar(15) NULL,
Price money NOT NULL,
Size nvarchar(5) NULL,
Quantity int NULL,
Data nvarchar(4000) NULL,
Tags nvarchar(4000) NULL,
-- INDEX cci CLUSTERED COLUMNSTORE　← コメントアウトするか削除
)
GO

次にテーブルを作成します。テーブルがあれば削除し新規に作成する処理になります。このクエリも最初の一回でいいですが何度かinsertを試したい時には初期化のため実行してください。

ここでひとつ注意点があります。それは「INDEX cci CLUSTERED COLUMNSTORE」の作成です。これはクラスターカラムストアのインデックスを張るクエリなのですがCLUSTERED COLUMNSTOREはSQL DatabaseのP1(プレミアム)から対応している機能のため、サイズをStandard以下で作成してる場合エラーになります。

プレミアムサイズは最低でも1月5万はかかるため試すにしてもちょっと抵抗があったのでCLUSTERED COLUMNSTOREを外して実行すしました。今回は試行なので外しても問題ないと思います。

「LOAD」

-- CSVからbulk Insertを実行します。
BULK INSERT Product
FROM 'data/product.csv'
WITH (  DATA_SOURCE = 'MyAzureBlobStorage',
FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
FIRSTROW=2,
TABLOCK);

最後にCSVからのbulk Insertを実行します。WITH句で読み込み対象のStorageと各種設定を行っています。
問題なく実行されれば下記のような実行結果がかえってきます。

正常に登録されているか確認してみましょう。

登録されていれば成功です。

まとめ

Azure StorageにあるファイルからBulk Insertする流れは定期バッチでデータ連携する等のケースで利用できると思います。SASの生成タイミングやエラーが発生した時の対処方法など考慮することは多いですがLogic AppsやAzure Functionsと組み合わせると自動化が捗るんじゃないかと思います。

大量データ連携はビジネスユースではよくある要件なので場合によっては強力な機能になります。また、AWSやGCP等から大量データを引き継ぐ時にも利用できると思いますのでデータ移行時にも有効な手段になると思ってます。

早くGAして欲しいですね。