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?
3条答案
按热度按时间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:
Using BULK INSERT.
For more information, please visit this article.
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: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.w41d8nur3#
You don't need to specify the
TYPE = BLOB_STORAGE,
while creating external table. If you are usingOPENROWSET
to access the data then you can query the blob storage withtype
parameter, but while creating external table you can omit thetype
parameter.Change your
CREATE EXTERNAL DATA SOURCE
query syntax to following and use it to create the external table and it should work.