[EPIC] Improve DataFusions ability to work with files

[matthewmturner]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]
(This section helps Arrow developers understand the context and why for this feature, in addition to the what)

I would like to add functionality for writing files from DataFusion. To start, I've thought of the below.

• (1) Add write functionality to ObjectStore
• (2)Add write_json to ExecutionContext
• (3) Add write_arrow to ExecutionContext
• (4) Add COPY / COPY TO command for SQL (like postgres https://www.postgresql.org/docs/current/sql-copy.html)
• (5) Add ability to write partitioned datasets
• (6) Add support for writing metadata
• (7) Add write_csv method to DataFrame
• (8) Add write_parquet method to DataFrame
• (9) Add write_arrow method to DataFrame
• (10) Add write_json method to DataFrame
• (11) Add read_json method to DataFrame
• (12) Add read_json method to ExecutionContext
• (13) Add register_json method to ExecutionContext
• (14) Support IF NOT EXISTS for CREATE EXTERNAL TABLE
• (15) Add read_arrow to ExecutionContext
• (16) Add read_arrow to DataFrame

I will use this as a parent / tracker issue for the above points which will each have an issue.

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

[matthewmturner]

Just wanted to create this to start collecting feedback / thoughts. I havent had chance to really look into details of this yet. I hope to start in a couple weeks when I have some more time.

[matthewmturner]

@seddonm1 @houqp FYI - in case you have any thoughts.

[matthewmturner]

@yjshen also interested in your view - in particular on the ObjectStore point.

[Igosuki]

@matthewmturner I'd love to see PARTITION BY be implemented, which would output typical k=v partitions usable by the ListingTableProvider.

[matthewmturner]

@matthewmturner I'd love to see PARTITION BY be implemented, which would output typical k=v partitions usable by the ListingTableProvider.

@Igosuki Agreed that would be great, I've added it to the list.

[Igosuki]

And, probably, support writing metadata, both arbitrary and specific to datafusion like other apache engines do ?

[matthewmturner]

And, probably, support writing metadata, both arbitrary and specific to datafusion like other apache engines do ?

Added that as well.

For my information, do you have any examples from other systems that I could reference?

[xudong963]

The direct implementation of COPY FROM (copy data from a file to a table) is if a table exists, then we can create a new table from the file and union new table with the old table, otherwise, we can let the new table unions with an empty table with the same scheme.

BTW, we should make sqlparser-rs support those commands firstly.

[matthewmturner]

The direct implementation of COPY FROM (copy data from a file to a table) is if a table exists, then we can create a new table from the file and union new table with the old table, otherwise, we can let the new table unions with an empty table with the same scheme.

BTW, we should make sqlparser-rs support those commands firstly.

@xudong963 To confirm - does COPY FROM functionality already exist? I'm not sure if i understood correctly, but I was focused on writing tables/dataframes from datafusion to files for this issue.

And definitely agree on checking sqlparser-rs. I will check that out.

[xudong963]

To confirm - does COPY FROM functionality already exist

No, looking forward to seeing it. I just describe my idea of how to implement it.

[xudong963]

And definitely agree on checking sqlparser-rs. I will check that out.

FYI @matthewmturner apache/datafusion-sqlparser-rs#409

[matthewmturner]

@xudong963 very helpful, thank you.

[Igosuki]

@matthewmturner
For instance : https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/package.scala
Or after writing a parquet file with pandas :

############ file meta data ############
created_by: parquet-cpp-arrow version 6.0.1

Of course then other arbitrary metadata, for other systems such as warehouses.

[matthewmturner]

@Igosuki Thx much.

Will keep you posted when I start working on this.

[matthewmturner]

From what I see it looks like only the execution context can write files right now - let me know if im mistaken. I think it makes sense to add write functionality to dataframes as well. I've added to the list.

[Igosuki]

@alamb is write_parquet outside of the scope now ?

[matthewmturner]

@alamb FYI I don't think this issue should be closed yet. I'm using it as a tracker with the task list in the description.

[matthewmturner]

@Igosuki just to confirm what youre looking for - you want to be able to write partitioned parquet files from SQL?

Plus writing more metadata.

[Igosuki]

Exactly, like we can do with Spark. Le dim. 6 mars 2022 à 16:55, Matthew Turner ***@***.***> a écrit :

…

@Igosuki <https://github.com/Igosuki> just to confirm what youre looking for - you want to be able to write partitioned parquet files from SQL? — Reply to this email directly, view it on GitHub <#1777 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADDFBQ6C2RS7ORZUH7OC2TU6TIOVANCNFSM5NYSNHWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[alamb]

reopened -- I think github got a little over eager and interpreted the "closes #1777 task 7" comment to mean it should close the ticket

[matthewmturner]

@Igosuki sry if im being dumb / bad at searching google but i havent been able to find an example / docs of writing partitioned parquet files from SQL. Only writing with dataframe API or reading a partitioned parquet dataset with SQL. I havent had the chance to test, but is the command youre looking for something like:

COPY TABLE abc
TO `abc`
STORED AS PARQUET
PARTITION BY year, month

[Igosuki]

Personnally, I'm fine using the dataframe api, but the partitioned output isn't available right now in the API ? Spark goes with write.partitionBy(...).parquet("")

[matthewmturner]

Ok great - using dataframe api aligns more with my thinking. It was just the SQL part that was throwing me off. Thx.

[alamb]

We have pretty much completed the list on this ticket now. It is pretty amazing to see

https://docs.rs/datafusion/latest/datafusion/execution/context/struct.SessionContext.html#method.read_avro
https://docs.rs/datafusion/latest/datafusion/execution/context/struct.SessionContext.html#method.read_arrow