Creating External Table over partitioned Delta Lake in Serverless SQL Pools

Question

Creating External Table over partitioned Delta Lake in Serverless SQL Pools

Serverless SQL 216 MVP

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.

Answer accepted by question author

1 additional answer

Your answer

Answer 1

KranthiPakala-MSFT 46,737 Microsoft Employee Moderator

Hi @Serverless SQL ,

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.

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2021-07-23T19:02:46.527+00:00

Hi @Serverless SQL ,

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.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2021-07-26T23:24:03.14+00:00

Hi @Serverless SQL ,

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.
Serverless SQL 216 Reputation points MVP

2021-07-27T08:40:27.953+00:00

Hi, thank you for the information.

Answer 2

Jayachandran, Athul 21

Hi @KranthiPakala-MSFT ,

I have a similar problem now , I created an external table with custom definitions, I am able to see all columns querying external table except for the ones used in partition, they are still NULL

PFB

![262734-image.png ]1

Was this feature request rolled out post the above question, or is it still not available, could you help clarify. I just wanted to know if it is something in the datatype I created or not, doesn't seem so though.

Share via

Creating External Table over partitioned Delta Lake in Serverless SQL Pools

1 additional answer

Your answer