QUESTION: Keep columns when using PARTITIONED BY with SQL

[hveiga]

Hi folks,

I am using Datafusion to partition some data stored in parquet files into a different set of parquet files. I would like those newly created files to contain the columns I am partitioning by, however currently the column gets removed as it becomes part of the file directory structure. Something like:

COPY (SELECT col1, col2, col3, col4 FROM my_external_table) TO '/output' PARTITIONED BY (col1) OPTIONS (format parquet);

...

/output/col1=val1/some_random_file_name.parquet
/output/col1=val2/some_random_file_name.parquet
/output/col1=val3/some_random_file_name.parquet
...

Is there a way in SQL to keep col1 in the output parquet files? If not, would it make sense to add this as part of a new option when calling COPY?

I have been looking in the documentation and open/closed issues but I could not find a way to do this, but if there is some information about it, a link would be greatly appreciated.

Thanks!

[devinjdangelo]

Your description of the behavior is correct. Datafusion will remove the partition columns from the data before writing out any files. There is not currently a config to modify this behavior, but it would be relatively straightforward to add. We would just need to make this line conditional on a config:

datafusion/datafusion/core/src/datasource/file_format/write/demux.rs

Line 303 in a8847e1

remove_partition_by_columns(&parted_batch, &partition_by)?;

[hveiga]

Understood. Thanks for the quick response. Is it ok to open an issue with this request? Or how should I proceed from here? I am happy to give it a try, but I must be honest I am not too familiar with the codebase.

[devinjdangelo]

Understood. Thanks for the quick response. Is it ok to open an issue with this request? Or how should I proceed from here? I am happy to give it a try, but I must be honest I am not too familiar with the codebase.

Feel free to open a feature request issue! You are also free to take a stab at implementing if you are interested. I may be able to take a look at it myself as well.

[hveiga]

Created #10971 for this feature request. Thanks again!

[hveiga]

@devinjdangelo I would appreciate if you could take a look to #10971 (comment) and give some guidance. Thanks!