탐색 건너뛰기

Public preview: Loading files from Azure Blob storage into Azure SQL Database

2017년 2월 23일 목요일

Azure SQL Database enables you to directly load files stored in Azure Blob storage by using the following SQL statements:

  • BULK INSERT T-SQL command that loads a file from a Blob storage account into a SQL Database table
  • OPENROWSET table-value function that parses a file stored in Blob storage and returns the content of the file as a set of rows

The following example shows a BULK INSERT command that loads the content of the file into SQL Database:

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorageAccount');

You can parse the content of a remote file by using the OPENROWSET function and return rows from the file as results:

SELECT Name, Color, Price, Size, Quantity, Data, Tags
FROM OPENROWSET(BULK 'data/product.bcp', DATA_SOURCE = 'MyAzureBlobStorage',
                FORMATFILE='data/product.fmt', FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as products;

As a prerequisite, you need to create an EXTERNAL DATA SOURCE that will point to your Azure Blob storage account. You'll use the name of this EXTERNAL DATA SOURCE in the DATA_SOURCE attribute. Here's an example of an EXTERNAL DATA SOURCE that points to a public Azure Blob storage account:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://myazureblobstorage.blob.core.windows.net');

If your Azure Blob storage account is not public, you need to generate a shared access signatures (SAS) key for the account by using the Azure portal, put the SAS key in CREDENTIAL, and create an EXTERNAL DATA SOURCE with CREDENTIAL, as shown in the following example:

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';

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
       LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
       CREDENTIAL= MyAzureBlobStorageCredential);

 

For examples of code that loads the content of files from an Azure Blob Storage account, see SQL Server GitHub samples.

체험 계정

Azure 크레딧 $200을 얻고 인기 서비스를 12개월 간 체험해 보세요.

시작

Visual Studio

구독자는 매년 최대 $1800 상당의 Azure 서비스를 이용할 수 있습니다.

지금 활성화

스타트업

Microsoft for Startups에 참가하고 Azure 서비스를 체험해 보세요.

자세한 정보