External table in Blob Storage in Azure SQL(Not Azure SQL DW)

Question

Here is my script which I am trying to run in Azure SQL Database:

CREATE DATABASE SCOPED CREDENTIAL some_cred WITH IDENTITY = user1,   
    SECRET = '<Key of Blob Storage container>';  
CREATE EXTERNAL DATA SOURCE TEST
WITH
(
    TYPE=BLOB_STORAGE,
    LOCATION='wasbs://<containername>@accountname.blob.core.windows.net',
    CREDENTIAL= <somecred>`enter code here`
);

CREATE EXTERNAL TABLE dbo.test
(
val VARCHAR(255)
)
WITH
(
DATA_SOURCE = TEST
)

I am getting the following error:

External tables are not supported with the provided data source type.

My goal is to create external table in blob storage so that Hive query in HDInsight references to the same blob. The table needs to be managed through Azure SQL. What's wrong with this script?

Answer 1

Azure SQL Database does have the feature to load files stored in Blob Storage but it only via the BULK INSERT and OPENROWSET language features. See here for more information.

BULK INSERT dbo.test
FROM 'data/yourFile.txt'
WITH ( DATA_SOURCE = 'YourAzureBlobStorageAccount');

The way you have scripted it is more like an external table using Polybase which is only available in SQL Server 2016 and Azure SQL Data Warehouse at this time.

Answer 2

I'm thinking External tables can be used for Cross Database Querying (Elastic queries). So it couldn't able to use the External Data Source which is BLOB_STORAGE