Remove SINGLE_FILE_OUTPUT option from COPY statement

[alamb]

Is your feature request related to a problem or challenge?

Figuring out how to use SINGLE_FILE_OUTPUT while writing https://github.com/apache/arrow-datafusion/blob/98a5a4eb1ea1277f5fe001e1c7602b37592452f1/datafusion/sqllogictest/test_files/repartition_scan.slt#L35-L38 was actually quite painful for me.

This option controls if the COPY statement writes a single file or multiple files into a directory.

Having datafusion automatically figure out that writing to foo.parquet should be a single file certainly would be a better UX in my opinion

Describe the solution you'd like

Yes, COPY still uses single_file_output option of the FileSinkConfig struct. We could potentially update COPY to instead rely on inference based on the path rather than an explicit option. E.g. copy table to file.parquet vs copy table to folder/. Then single_file_output could be removed entirely.

Describe alternatives you've considered

No response

Additional context

@devinjdangelo removed the corresponding option from LIstingTable (aka CREATE EXTERNAL TABLE) in #8604 and suggsted this in #8604 (comment)

[yyy1000]

I could try to help it. :)

[alamb]

I could try to help it. :)

A machine!

[yyy1000]

I created PR #9041 may help this. ❤️