COPY fails on cli with Invalid statement

[hveiga]

Describe the bug

I downloaded https://dist.apache.org/repos/dist/dev/arrow/apache-arrow-datafusion-37.0.0-rc2/ to test the new partition_by feature. I built datafusion-cli by running cargo build --release under datafusion-cli.

The use case is simple: load a parquet file and create multiple parquet files using hive-partitioned partitions.
When I try to run the documented COPY command on https://arrow.apache.org/datafusion/user-guide/sql/write_options.html I get an error.

To Reproduce

1. Build datafusion-cli from https://dist.apache.org/repos/dist/dev/arrow/apache-arrow-datafusion-37.0.0-rc2/.
2. Run ./datafusion-cli.
3. Create a table from a parquet file:

CREATE EXTERNAL TABLE t1
STORED AS PARQUET
LOCATION '/tmp/file.parquet';

4. Execute partition_by command:

COPY t1 TO '/tmp/hive_output/' (format parquet, partition_by 'col1');

5. Get an error: 🤔 Invalid statement: sql parser error: Unexpected token (

Expected behavior

Have the COPY statement generate the expected hive-partitioned parquet files.

Additional context

I don't know if I might be having an issue with my SQL statements or the COPY documentation is incorrect. Still, I thought it was good to report before 37.0.0 gets released. #9682

Thank you!

[tinfoil-knight]

The documentation is outdated.

• You need to specify options in this format: OPTIONS (...)
• Also, partition_by needs to be specified separately as PARTITIONED BY (<column>) & not with the options.

This is how your query would look now:

COPY t1 TO '/tmp/hive_output/' PARTITIONED BY (col1) OPTIONS (format parquet);

[tinfoil-knight]

Note to Maintainers:
#9905 will remove any compatibility with the old syntax entirely so IMO we should only add the new syntax in updated docs.

[hveiga]

Just tested and COPY t1 TO '/tmp/hive_output/' PARTITIONED BY (col1) OPTIONS (format parquet); works as expected. Thank you!

[tinfoil-knight]

@hveiga I think we should keep this issue open until the documentation is updated with the latest syntax.

[hveiga]

@hveiga I think we should keep this issue open until the documentation is updated with the latest syntax.

Makes sense. Re-opening.

[alamb]

Looks like I missed a spot in #9754, sorry about that.

The new syntax is documented here: https://arrow.apache.org/datafusion/user-guide/sql/dml.html#copy

[alamb]

I'll make a PR

[alamb]

#9931