create s3 failed

[web3creator]

Describe the bug

datafusion-cli
❯ CREATE EXTERNAL TABLE test
STORED AS PARQUET
OPTIONS(
'region' 'xxx',
'access_key_id' 'xxxx',
'secret_access_key' 'xxxx'
)
LOCATION 'xxxxx';
Invalid or Unsupported Configuration: Found unsupported option access_key_id with value xxxx for Parquet format!
❯

To Reproduce

datafusion-cli

datafusion-cli
❯ CREATE EXTERNAL TABLE test
STORED AS PARQUET
OPTIONS(
    'region' 'xxx',
    'access_key_id' 'xxxx',
    'secret_access_key' 'xxxx'
)
LOCATION 'xxxxx';
Invalid or Unsupported Configuration: Found unsupported option access_key_id with value xxxx for Parquet format!
❯ 

Expected behavior

No response

Additional context

No response

[Jefffrey]

Looks to be a regression introduced by #7390

Specifically:

https://github.com/apache/arrow-datafusion/blob/d1554c85c0da2342c5247ed22a728617e8f69142/datafusion/common/src/file_options/parquet_writer.rs#L208

Can probably relax this strict check to allow unknown keys since they could be used for other stuff, like S3 object store

Same could potentially be done for JSON and CSV too:

https://github.com/apache/arrow-datafusion/blob/d1554c85c0da2342c5247ed22a728617e8f69142/datafusion/common/src/file_options/json_writer.rs#L54

https://github.com/apache/arrow-datafusion/blob/d1554c85c0da2342c5247ed22a728617e8f69142/datafusion/common/src/file_options/csv_writer.rs#L100

[web3creator]

@Jefffrey I also located this issue，However, I think this kind of testing is unreasonable and restrictions should be relaxed

[devinjdangelo]

The reason for the strict checking is to help identify typos or logical errors easily in SQL queries rather than silently ignoring invalid options. Given that datafusion-cli is using the same data structure to store additional options, there are a few options:

• Add the additional datafusion-cli valid options to datafusion proper so they can be silently ignored rather than error
• Have datafusion-cli remove any valid datafusion-cli specific options in the create external table statement before executing the statement
• Downgrade the configuration error in datafusion to a warning message so execution doesn't fail

I favor one of the first two options, but the third may be the best if we expect this problem to crop up beyond datafusion-cli. The question would be if it is likely for many downstream projects to inject additional options passed to a ListingTable or CopyTo statement.

[devinjdangelo]

Here is a quick draft PR that implements option 2. Let me know if this branch solves your issue. I'll try to get some time to verify with a real S3 bucket myself in the next day or two. #8426

[alamb]

BTW it would be great if someone could write a regression test in datafusion-cli somehow so this functionality is not broken again (@web3creator is this something you could potentially help with?)

[alamb]

Thanks everyone for the quick fix