question

serverlesssql avatar image
0 Votes"
serverlesssql asked serverlesssql commented

Creating External Table over partitioned Delta Lake in Serverless SQL Pools

Hi,

I'm testing creating Views and External Tables over partitioned data in Azure Storage which is using the Delta Lake format. When I create a View and use the partitioned column in the delta lake to filter then I see reduced data processed (which is good). However when I create an External Table over the same partitioned Delta lake data, the partitioned column appears NULL in the results and filtering does not work.

For example:

CREATE EXTERNAL TABLE LDW.DeltaFactSalesOrder
(
CustomerKey int ,
ProductKey int ,
DueDateKey int ,
ShipDateKey int ,
OrderDate date,
SalesOrderNumber nvarchar(20)
)
WITH
(
LOCATION = 'spark/delta/factinternetsales/',
DATA_SOURCE = ExternalDataSourceDataWarehouse,
FILE_FORMAT = SynapseDeltaFormat
)


OrderDate is the partition column in the Delta Lake (created using the PARTITION BY spark sql syntax) but it's just NULL and doesn't seem to work when filtering.

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered serverlesssql commented

Hi @serverlesssql,

Apologies for the delayed response and thanks for using Microsoft Q&A forum and posting your query.

As per conversation with product team, External tables do not support partitioned columns now, and it is recommend to use Views in that case. \

Below are few related feedbacks in Azure Synapse Analytics user voice forum. Please feel free to up-vote and/or comment on them to help increase the priority of feature request.

Hope this info helps.



Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @serverlesssql,

Just checking in to see if the above suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·

Hi @serverlesssql,

We still have not heard back from you. Just wanted to check if the below information was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·

Hi, thank you for the information.

0 Votes 0 ·