Create external data source Azure SQL

Question

I was looking for a solution to create an external data source in my SQL Database.

CREATE DATABASE SCOPED CREDENTIAL ADLSCredential
WITH
    IDENTITY = '<client_id>@<OAuth_2.0_Token_EndPoint>',
    SECRET = '7Re4z84oUuCeMKjWzMmVNTXcsj4YH7okVl********='
;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'adl://<datalakeaccountname>.azuredatalakestore.net',
    CREDENTIAL = ADLSCredential
);

When executing this request, the result is always:

Failed to execute query. Error: Incorrect syntax near 'HADOOP'.

After some researches, I found the following information :

You were using Azure SQL DB and NOT Azure SQL Data Warehouse (DW) to set up external tables from ADLS or similar types of Data sources.

Is there a way to set up external tables on Azure SQL Database from data that is hosted on Azure Data Lake Store?

The topic is dated from September 12, 2018 and there is no answer to the question above.

May you help me?

Best regards,

Tristan.

Answer 1

You can only set up external connectivity in Azure SQL Data Warehouse.

The documentation says it applies to Azure SQL Database, but this is misleading, the engine that provides this feature is Polybase and it is only available in SQL DW an SQL Server 2016+ (IaaS or on-prem).

If you read down the page on the documentation you will find a note:

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