[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.