Creating External Table in Azure SQL DW from Azure BLOB Storage

Question

I am creating a data source in Azure SQL DW in order to create external table using BLOB storage data but I am getting error while creating data source. I am new to this polybase environment so, please share your thought.

I am trying to call the data source in the external table code.

External Table

create external table [DBA].[TEST]
(
[type] varchar(3) COLLATE   SQL_Latin1_General_CP1_CI_AS NULL,
)
with (LOCATION='https://test.blob.core.windows.net/BCP/',
DATA_SOURCE=TEST,
FILE_FORMAT=pipe_header,
REJECT_TYPE=VALUE,
REJECT_VALUE=0
)

I am not sure which KEY do I need to pass here as well. Either I need to use BLOB storage key or something else as well.

Data Source

CREATE EXTERNAL DATA SOURCE TEST  
WITH (   
TYPE = BLOB_STORAGE,  
LOCATION = 'https://test.blob.core.windows.net/BCP/'
, CREDENTIAL = KEYS
)  
;

Error Code

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 3, column: 16: Incorrect syntax near 'BLOB_STORAGE'.

Incorrect syntax near 'BLOB_STORAGE'. Expecting CEDS_HADOOP, CEDS_RDBMS, CEDS_SHARDMAPMANAGER, or CEDS_SHAREDMEMORY"

Expectation

I need to create external table in azure sql data warehouse using BLOB storage account.

David Browne - Microsoft · Accepted Answer · 2019-01-28 20:06:48Z

1

Confusingly TYPE=BLOB_STORAGE is not used in PolyBase, only in BULK INSERT/OPENROWSET from Azure SQL Database. Use TYPE=HADOOP, as in this walkthrough: Load Contoso Retail data to Azure SQL Data

CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',
    CREDENTIAL = AzureStorageCredential
);

edited Jan 28, 2019 at 20:06

answered Jan 28, 2019 at 18:03

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

3 Comments

Jo Joseph Over a year ago

Thanks @David Browne for your response. I have a question here. Do I need to pass BLOB storage KEY1 in CREDENTIAL or which credentials I need to pas in CREDENTIAL parameter

David Browne - Microsoft Over a year ago

You need to create a DATABASE SCOPED CREDENTIAL. It's in that walkthrough.

Jo Joseph Over a year ago

Thanks and it makes sense.

Collectives™ on Stack Overflow

Creating External Table in Azure SQL DW from Azure BLOB Storage

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related