Parquet partitionBy - date column to nested folders

1stcommander — Mon, 11 Nov 2019 14:10:40 GMT

Hi,

when writing a DataFrame to parquet using partitionBy(<date column>), the resulting folder structure looks like this:

root

|----------------- day1

|----------------- day2

|----------------- day3

Is it possible to create a structure like to following without explicitely creating the partitioning columns:

root

|----- year1

|-----month1

|----- day1

|----- ....

|----- year2

|----- month

I know that i could achieve it with something like

df
.withColumn("year", year(col("date_col"))).withColumn("month", month(col("date_col"))).withColumn("day", dayofmonth(col("date_col"))).withColumn("hour", hour(col("date_col"))).partitionBy("year","month","day","hour")

taken from (https://stackoverflow.com/questions/52527888/spark-partition-data-writing-by-timestamp),

but when you do like this you also have to use the "virtual" columns when querying from the files in SparkSQL afterwards in order to profit from partition pruning. (In the example, you have to use "WHERE year = 2017 AND month = 2 " - if you use "WHERE date_col >= to_date('2017-02-01') AND date_col <= to_date('2017-03-01')" it doesn`t use partition pruning.

I'm wondering if there is some functionality that i currently just do not know about that can

a) automatically create the nested folder structure

b) also use this for partition pruning while querying

Thank you

Re: Parquet partitionBy - date column to nested folders

1stcommander — Mon, 11 Nov 2019 14:17:10 GMT

Unfortunately the format has been broken on saving 😞

Here is the structure as-is example:

Here is the desired structure example:

Re: Parquet partitionBy - date column to nested folders

Saphira — Wed, 13 Nov 2019 14:09:41 GMT

Hey @1stcommander

You'll have to create those columns yourself. If it's something you will have to do often you could always write a function. In any case, imho it's not that much work.

Im not sure what your problem is with the partition pruning. It's almost as if you're saying you want the exact thing you said you dont want.

Good luck

topic Re: Parquet partitionBy - date column to nested folders in Data Engineering

Parquet partitionBy - date column to nested folders

Re: Parquet partitionBy - date column to nested folders

Re: Parquet partitionBy - date column to nested folders