Synapse Spark - create delta table with partition

Ryan Abbey 1,181

Has anyone else created a delta table with partition on Synapse 2.4?

The below command created the table but there is no partition folder and the json has partitionBy:[] so appears to be ignoring the partitionby

df.select('Col1', 'Col2', 'Col3', 'Col4', 'Col5').write.format("delta").mode("overwrite").option("mergeSchema", 'true').partitionBy("Col3").option("path", "/path/x").saveAsTable('x')

Is there anything wrong with the command?

Ryan Abbey 1,181 Reputation points

2021-08-30T00:47:20.267+00:00

v3.1 does at least create the partitioning when creating the table... however, appears to lose it on an overwrite... that means any time you want to consolidate files, you have to know the partitioning? Something appears off to me!

v3.1 also seems to write out 200 files regardless of existing partitioning!
Ryan Abbey 1,181 Reputation points

2021-08-30T03:25:13.797+00:00

Found answer to the 200 files - as a "merge" operation does shuffles, it would appear that can only be controlled by the configuration variable shuffle.partitions

My question on that - if I have 3 pipelines kicking off different notebooks (or even potentially the same notebook but different destinations), if I set different "shuffle.partitions" for each, would last in win? Or is the variable specific to the current session and each pipeline would start its own session?
Saurabh Sharma 23,826 Reputation points Microsoft Employee

2021-08-30T19:51:03.923+00:00

@Ryan Abbey I am checking this one internally and get back to you.

Thanks
Saurabh
Saurabh Sharma 23,826 Reputation points Microsoft Employee

2021-08-31T15:11:00.93+00:00

@Ryan Abbey Partitioned catalog tables aren’t officially supported in Delta Lake 0.6.1 (the version currently running in Spark 2.4). The best recommendation is to use Spark 3 for partitioned catalog tables in Delta.
Regarding your second question - How are you doing the overwrite ? Are you using saveAsTable() again in the overwrite mode ?

Thanks
Saurabh
Ryan Abbey 1,181 Reputation points

2021-08-31T23:15:40.34+00:00

Partitioning is not a major for us at the moment - don't want to use v3 since that's still in preview. Re the overwrite, it may not be what's happening, I was monitoring the JSON files and it went from partitionBy:[Col3] to partitionBy:[]. However, after further runs, I did notice there was a Col3 subfolder so might just have been the JSON that was misleading... I haven't looked any further for that

What I'd be more interested in knowing is the below
If I have 3 pipelines kicking off different notebooks (or even potentially the same notebook but different destinations), if I set different "shuffle.partitions" for each, would last in win? Or is the variable specific to the current session and each pipeline would start its own session?
Ryan Abbey 1,181 Reputation points

2021-09-26T20:29:28.733+00:00

Any answer on this? will "shuffle.partitions" only affect my current notebook or does it impact everything running on the same cluster?

Share via

Synapse Spark - create delta table with partition

Your answer