I need to take large tables out of our Azure Data Warehouse and move them over to stand alone Azure SQL Databases. I haven't been able to get the Data Factory to work quickly enough for my scenario. I can get my tables into Blob storage from my Data Warehouse via external tables. What I can not figure out is how to create an external table on an Azure SQL Database with an external data source to my Blob storage.
This is the format file, external data source, and external table used to get my table into blob 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 I'm able to create in the Azure SQL Database is of TYPE=SHARD_MAP_MANAGER
is that right or necessary? This link looks like I should be able to create an external data source using TYPE=HADOOP
but I get an "error near EXTERNAL" error. I'm also unable to create an EXTERNAL FILE FORMAT. Is that possible in Azure SQL Database?
https://msdn.microsoft.com/en-us/library/dn935022.aspx#Examples: Azure SQL Database
Ultimately, I'm trying to create an external table to my blob storage and then insert into a table in my Azure SQL Database from that blob. Then drop the container.