SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server v.Next と SQL Database の BLOB ストレージからのローディングの新機能

leave a comment

SQL Server v.Next と SQL Database の新機能として BLOB ストレージからのローディング機能が追加されています。
SQL Database では Preview の機能ですが、SQL Server では、v.Next の CTP 1.1 で追加されています。
Public preview: Loading files from Azure Blob storage into Azure SQL Database

どのようなことが実施できる機能なのか試してみました。

BLOB ストレージからのローディング機能を使用することで、BLOB ストレージに保存されているファイルを「OPENROWSET」「BULK INSERT」を使用してアクセスすることができるようになります。

最初に機能を使用するデータベースでデータベーススコープの資格情報の作成を行います。
# マスターキーの作成を実施していない場合は「CREATE MASTER KEY」でマスターキーの作成を実施しておきます。

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=xxxxxxxxx'

これで使用する、BLOB ストレージの Shared Access Signature を登録しておきます。

次に BLOB ストレージの情報を外部データソースとして追加します。

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( 
TYPE = BLOB_STORAGE,
LOCATION = 'https://<BLOB ストレージアカウント>.blob.core.windows.net',
CREDENTIAL= MyAzureBlobStorageCredential)

 

これで、指定した BLOB ストレージに対して SAS でアクセスするための設定が完了です。

あとは、OPENROWSET や BULK INSERT を使用して、ファイルにアクセスをすることができます。

今回は以下のようなテキストを BLOB に配置しています。

1,2,3,4
5,6,7,8
9,10,11,12

 

OPENROWSET

以下のようなクエリを実行できます。

SELECT *
FROM OPENROWSET(
	BULK 'file/ImportData.txt', 
	DATA_SOURCE = 'MyAzureBlobStorage',
	SINGLE_CLOB
) AS T1

このクエリを実行した場合は、以下のように単純な CLOB として取得されます。

image

フォーマットファイルを指定することもできます。

14.0
4
1  SQLCHAR  0  4  ","    1  C1  ""
2  SQLCHAR  0  4  ","    2  C2  ""
3  SQLCHAR  0  4  ","    3  C3  ""
4  SQLCHAR  0  4  "\r\n" 4  C4  ""

フォーマットファイルを指定すると、以下のような行形式で取得できます。

SELECT *
FROM OPENROWSET(
	BULK 'file/ImportData.txt', 
	DATA_SOURCE = 'MyAzureBlobStorage',
	FORMATFILE='file/ImportData.fmt', 
	FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS T1

image

最新の SQL Server であれば、JSON 関数がありますので、BLOB ストレージに格納されている JSON にアクセスすることもできます。

DECLARE @json nvarchar(max)
SELECT @json = BulkColumn
FROM OPENROWSET(
	BULK 'file/ImportData.json', 
	DATA_SOURCE = 'MyAzureBlobStorage',
	SINGLE_CLOB
) AS T1

SELECT * 
FROM 
OPENJSON(@json)
WITH(
	Name sysname '$.name'
)

image

BULKINSERT

こちらは、BLOB ストレージから直接 INSERT するための機能が追加されています。

CSV ファイルをインポートする場合は以下のようになります。

BULK INSERT T1
FROM 'file/ImportData.txt'
WITH ( 
	DATA_SOURCE = 'MyAzureBlobStorage',
	FIELDTERMINATOR = ',' 
)

BLOB ストレージへのアクセスができるようになることで、クラウドに取得されたデータのアクセスやデータ移行時の手法として使用できるケースがいくつかあるのかもしれないですね。

Written by masayuki.ozawa

2月 25th, 2017 at 5:34 pm

Leave a Reply

*