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

d5vmydt9  于 2023-06-28  发布在  其他
关注(0)|答案(3)|浏览(115)

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://container0@fsblobstorage.blob.core.windows.net',
    CREDENTIAL = AzureStorageCredential
);

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

Can anyone help me?

omqzjyyz

omqzjyyz1#

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.

1wnzp6jl

1wnzp6jl2#

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://container0@fsblobstorage.blob.core.windows.net',
    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.

w41d8nur

w41d8nur3#

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://container0@fsblobstorage.blob.core.windows.net',
    CREDENTIAL = AzureStorageCredential
);

相关问题