topic How do I write dataframe to s3 without partition column name on the path in Warehousing & Analytics

How do I write dataframe to s3 without partition column name on the path

Jennifer — Mon, 19 Feb 2024 14:28:51 GMT

I am currently trying to write a dataframe to s3 like

df.write
.partitionBy("col1","col2")
.mode("overwrite")
.format("json")
.save("s3a://my_bucket/")

The path becomes `s3a://my_bucket/col1=abc/col2=opq/`

But I want to path to be `s3a://my_bucket/abc/opq/`

Is there a way to write the dataframe with a path removing the partition column name?

Re: How do I write dataframe to s3 without partition column name on the path

Jennifer — Mon, 19 Feb 2024 14:59:43 GMT

Thanks for the quick reply. If I use the new column for partitioning, "combined_col" will still be in the path I think.

Re: How do I write dataframe to s3 without partition column name on the path

Jennifer — Thu, 22 Feb 2024 09:28:57 GMT

The way I did at the end was to write files to dbfs first and then move them to s3 in order to have a customized path and file name. I could also avoid writing commit files to s3.

Re: How do I write dataframe to s3 without partition column name on the path

Sidhant07 — Thu, 21 Mar 2024 16:00:00 GMT

Hi @Jennifer ,

The default behavior of the .partitionBy() function in Spark is to create a directory structure with partition column names. This is similar to Hive's partitioning scheme and is done for optimization purposes. Hence, you cannot directly change this behavior to remove partition column names from the path.However, you can achieve your desired directory structure by doing a workaround. After saving the dataframe, you can rename the directories in your S3 bucket to remove the partition column names. This will have to be done outside of Spark, using AWS SDK or CLI.Here is an example of how you can do it using AWS CLI:

bash
aws s3 mv s3://my_bucket/col1=abc s3://my_bucket/abc --recursive
aws s3 mv s3://my_bucket/abc/col2=opq s3://my_bucket/abc/opq --recursive

Please note that this operation can be time-consuming if you have a large number of files or directories to rename.