Creating a table in an Azure SQL database from a Blob repository

Question

I need to extract large tables from our Azure data warehouse and move them to standalone Azure SQL databases. I was not able to get Data Factory to work fast enough for my scenario. I can get my tables in the Blob store from my data store through external tables. I cannot figure out how to create an external table in an Azure SQL database with an external data source in my Blob repository.

This is a format file, an external data source, and an external table used to get my table into memory storage:

CREATE EXTERNAL FILE FORMAT [DelimitedText] WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR = N'~¶~', USE_TYPE_DEFAULT = False ), DATA_COMPRESSION = N'org.apache.hadoop.io.compress.GzipCodec') GO CREATE EXTERNAL DATA SOURCE [myDataSource] WITH ( TYPE = HADOOP, LOCATION = N'wasbs://<blob container>@<storage account>.blob.core.windows.net', CREDENTIAL = [myCredential]) GO CREATE EXTERNAL TABLE [dbo].[myTable] WITH ( DATA_SOURCE = [myDataSource] , LOCATION = N'MY_FOLDER/', FILE_FORMAT = [DelimitedText] ) AS SELECT * FROM dbo.mytable 

The only external data source that I can create in an Azure SQL database is TYPE=SHARD_MAP_MANAGER , is this correct or necessary? This link looks like I should be able to create an external data source using TYPE=HADOOP , but I get the error "error near EXTERNAL". I also cannot create an EXTERNAL FILE FORMAT. Is this possible in an Azure SQL database?

https://msdn.microsoft.com/en-us/library/dn935022.aspx#Examples: Azure SQL Database

Ultimately, I try to create an external table for my blob store, and then insert into the table in my Azure SQL database from this blob. Then remove the container.

Answer 1

You cannot use PolyBase functions in an Azure SQL database, only in local SQL Server 2016 databases.

The article has a note:

PolyBase is only supported on SQL Server 2016, Azure SQL Data Warehouse and Parallel Data Warehouse. Elastic Database queries are only supported in Azure SQL Database v12 or later.

Instead, you can create an Azure SQL Data Warehouse (on the same Azure SQL Server, if you want). A guide will work for you if you use it instead. It will not work for Hadoop ( https://msdn.microsoft.com/en-us/library/mt703314.aspx ), but since I understand your question, which you are importing from the Azure blade storage, and this will work on the data storage Azure SQL

Answer 2

The Azure SQL Database recently acquired the ability to download files from Azure Blob using BULK INSERT or OPENROWSET. Start here .

Two simple code examples taken from a related article:

 BULK INSERT Product FROM 'data/product.dat' WITH ( DATA_SOURCE = 'MyAzureBlobStorageAccount'); SELECT Color, count(*) FROM OPENROWSET(BULK 'data/product.bcp', DATA_SOURCE = 'MyAzureBlobStorage', FORMATFILE='data/product.fmt', FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as data GROUP BY Color;