polybase support in SQL server managed instance

Question

polybase support in SQL server managed instance

Dyaneshwaran Sivashanmugam 21

Hi all,

i get below error when creating external table in SQL Server managed instance (azure), my datasource

Msg 46525, Level 16, State 31, Line 17
External tables are not supported with the provided data source type.

---------------

CREATE EXTERNAL DATA SOURCE AzureStorage with (
TYPE = BLOB_STORAGE ,
LOCATION ='wasbs://******@storageaccount.blob.core.windows.net',
CREDENTIAL = AzureStorageCredential
);

any advise will be great help

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Oury Ba-MSFT 21,126 Microsoft Employee Moderator

Hi @Dyaneshwaran Sivashanmugam Thank you for posting your question on Microsoft Q&A and for using Azure services.

My understanding is that you are getting an error message after running the above code.

I believe the error message stemmed from TYPE = BLOB_STORAGE.

Data virtualization (Polybase) is currently in preview for Azure SQL Managed Instance. Reference Data virtualization with Azure SQL Managed Instance (Preview)
Data virtualization provides two ways of querying external files stored in Azure Data Lake Storage or Azure Blob Storage.
Use Transact-SQL (T-SQL) to explicitly enable the data virtualization feature before using it.

To enable data virtualization capabilities, run the following command:

exec sp_configure 'polybase_enabled', 1;
go
reconfigure.
go

Provide the location of the file(s) you intend to query using the location prefix corresponding to the type of external source and endpoint/protocol, such as the following examples:

Please see an example from the doc I provided in the answer.

Kent Maxwell 11 Reputation points

2022-12-16T16:50:12.983+00:00

Hello @Oury Ba-MSFT

Can you point me to a roadmap that describes when the same Polybase functionality of SQL Server 2022 will be available in Azure SQL Managed Instance? I am specifically interested in when it will be possible to query Delta Lake from Azure Storage in Managed Instance.

Thank you,

Kent
Nikola Zagorac 1 Reputation point Microsoft Employee

2022-12-19T11:38:58.38+00:00

Hi @Kent Maxwell ,

thanks for asking about Delta format support. At this time, we are still weighing the demand for this, so that we can decide how to prioritize the feature. Would you mind posting this as an idea in the SQL MI ideas forum so that we can track the interest?

Thanks so much!
Kent Maxwell 11 Reputation points

2022-12-19T12:01:29.457+00:00

Thank you @Nikola Zagorac
I posted the idea in the SQL MI forum. Here is a link for lineage purposes: https://feedback.azure.com/d365community/idea/c205e0e8-927f-ed11-a81b-000d3ae5ae95
Nikola Zagorac 1 Reputation point Microsoft Employee

2022-12-19T12:53:32.927+00:00

Thank you! Monitoring for further consideration.

Answer 2

Dyaneshwaran Sivashanmugam 21

Thank you @Oury Ba-MSFT , it works for CSV file but i have file with pipe separator
will this approach allows to define delimiter while reading (or) it supports only comma separator ?

Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2022-08-09T16:58:27.04+00:00

Hi @Dyaneshwaran Sivashanmugam

Try this as the definition of your file format that you'll pass on to Create external table statement. You can play with field terminator (delimiter) and similarly, string escaping character...

CREATE EXTERNAL FILE FORMAT CSVFileFormat
WITH (
FORMAT_TYPE=DELIMITEDTEXT,
FORMAT_OPTIONS
(
FIELD_TERMINATOR='|',
STRING_DELIMITER = '"'
)
)

Hope that helps.

Regards,'
Oury
Dyaneshwaran Sivashanmugam 21 Reputation points

2022-08-10T21:17:38.147+00:00

I am able to create external table with above file format, but i get error when selecting rows

Msg 16562, Level 16, State 1, Line 49
External table 'dbo.testexternal' is not accessible because location does not exist or it is used by another process.

Completion time: 2022-08-10T14:11:07.1651669-07:00

below are steps followed
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential10 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', Secret = 'saskey';

CREATE EXTERNAL DATA SOURCE [AzureStorage7] WITH (LOCATION = N'abs://******@storageaccount.blob.core.windows.net', CREDENTIAL = [AzureStorageCredential10])

CREATE EXTERNAL FILE FORMAT [Csvfileformat] WITH (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = N'|', USE_TYPE_DEFAULT = False))

CREATE EXTERNAL TABLE testexternal
(
[BrComId] [int] IDENTITY(1,1) NOT NULL,
[AsOfDate] varchar NULL

)
WITH
(
LOCATION = '\test.csv',
DATA_SOURCE = [AzureStorage7],
FILE_FORMAT = [Csvfileformat]

)
GO
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2022-08-16T19:30:46.46+00:00

@Dyaneshwaran Sivashanmugam Sorry for the delay in my response.

Could you please create a support ticket so we can further investigate. Let us know if you don't have a support plan so we can enable a onetime free support ticket.

Regards,
Oury

Share via

polybase support in SQL server managed instance

1 additional answer

Your answer