Handle error "External tables are not supported with the provided data source type" in Azure SQL DB?

Question

According to Microsoft Documentation, external tables are now available on Azure SQL Database, having Blob Storage as Data Source.

I was able to create the Master Key, the credential and the Data Source of TYPE = BLOB_STORAGE, as per the script below, and noticed that this version doesn't support file format (Not sure how to handle this as well).

So I tried creating an external table with one single field as VARCHAR(MAX). Therefore I got stuck with this error message:

Mensagem 46525, Nível 16, Estado 31, Linha 32
External tables are not supported with the provided data source type.

This is the script I've been using:

-- Cria uma chave mestra
CREATE MASTER KEY;
go

-- Cria credencial com a chave do Blob fsarquivo2 (essa é a chave)
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = 'user',
    SECRET = 'Q/rAy00000000000000000000000003Zo4RsxbIb57i2DoJTtU4JYQl1W5FDBIITapphJDRSv4OtniL3Dg=='
;

-- Aqui vc mostra onde fica a fechadura através da URI
CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'wasbs://[email protected]',
    CREDENTIAL = AzureStorageCredential
);


create external table luiz
(field varchar(MAX))
with
(
    DATA_SOURCE = AzureStorage,
    LOCATION = 'container0/terrcad.txt'
)

Can anyone help me?

Martijn Pieters · Accepted Answer · 2020-09-19 07:37:50Z

2

All you did is correct except that you tried to create an External Table from a data source in a Blob Storage and that is not possible. Instead of creating an external table you need to use BULK INSERT or OPENROWSET to get access to file and import it.

The following is an example using BULK/INSERT:

BULK INSERT Product
FROM 'container0/terrcad.txt'
WITH ( DATA_SOURCE = 'AzureStorage');

Using BULK INSERT.

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'AzureStorage',
   FORMAT = 'CSV',
   FORMATFILE='invoices.fmt',
   FORMATFILE_DATA_SOURCE = 'MyAzureInvoices'
   ) AS DataFile;

For more information, please visit this article.

edited Sep 19, 2020 at 7:37

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Dec 14, 2019 at 0:57

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TPPZ Over a year ago

What is invoices.fmt? In my CSV scenario I just have 1 file in the blob storage and if I make up something for the FORMATFILE then I see this error: Cannot bulk load. The file "foo.fmt" does not exist or you don't have file access rights.

Alberto Morillo Over a year ago

It is a format file. Please read the following documentation to know how to create a format file: learn.microsoft.com/en-us/sql/relational-databases/…

ice1e0 · Accepted Answer · 2022-03-30 20:12:38Z

0

There is a tiny mistake in your code which would make it work: You can only create an external table on a blob storage when you define the TYPE of the DATA SOURCE as HADOOP storage:

-- Aqui vc mostra onde fica a fechadura através da URI
CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://[email protected]',
    CREDENTIAL = AzureStorageCredential
);

Take a look at this example from Microsoft: Configure PolyBase to access external data in Azure Blob Storage

It looks like that the TYPE BLOB_STORAGE type can only be used for BULK INSERT and OPENROWSET functions.

answered Mar 30, 2022 at 20:12

ice1e0

1,0278 silver badges17 bronze badges

Comments

Not a bug · Accepted Answer · 2023-06-23 06:46:13Z

0

You don't need to specify the TYPE = BLOB_STORAGE, while creating external table. If you are using OPENROWSET to access the data then you can query the blob storage with type parameter, but while creating external table you can omit the type parameter.

Change your CREATE EXTERNAL DATA SOURCE query syntax to following and use it to create the external table and it should work.

CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    LOCATION = 'wasbs://[email protected]',
    CREDENTIAL = AzureStorageCredential
);

answered Jun 23, 2023 at 6:46

Not a bug

4,3342 gold badges43 silver badges82 bronze badges

Collectives™ on Stack Overflow

Handle error "External tables are not supported with the provided data source type" in Azure SQL DB?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related