You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "marvelshan (via GitHub)" <gi...@apache.org> on 2025/04/14 12:58:51 UTC
[PR] doc/document options clause [datafusion]
marvelshan opened a new pull request, #15708:
URL: https://github.com/apache/datafusion/pull/15708
## Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
-->
- Closes #.#10451
## Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
-->
This PR adds documentation for the `OPTIONS` clause, including generic options and format-specific options, to ensure users have clear guidance on available settings.
## What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
-->
- Renamed `write_options.md` to `format_options.md` to reflect its scope for both reading and writing.
- Added examples for each format (JSON, CSV, Parquet) in the `OPTIONS` clause.
- Documented generic options and format-specific options in a structured format.
- Updated heading levels to ensure all sections appear in the table of contents.
## Are these changes tested?
<!--
We typically require tests for all PRs in order to:
1. Prevent the code from being accidentally broken by subsequent changes
2. Serve as another way to document the expected behavior of the code
If tests are not included in your PR, please explain why (for example, are they covered by existing tests)?
-->
Do not require automated tests
## Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be updated before approving the PR.
-->
Yes, the documentation now includes detailed examples and descriptions of the `OPTIONS` clause for `CREATE EXTERNAL TABLE` and `COPY` queries.
<!--
If there are any breaking changes to public APIs, please add the `api change` label.
-->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] doc/document options clause [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #15708:
URL: https://github.com/apache/datafusion/pull/15708#issuecomment-2802871540
> Run extended tests
- FYI, I am using this PR as a way to test https://github.com/apache/datafusion/pull/15704 from @ashdnazg
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Improve documentation for format `OPTIONS` clause [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #15708:
URL: https://github.com/apache/datafusion/pull/15708#discussion_r2051458845
##########
docs/source/user-guide/sql/format_options.md:
##########
@@ -0,0 +1,209 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Format Options
+
+DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` statements. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. In some cases, Options can be specified in multiple ways with a set order of precedence.
+
+## Specifying Options and Order of Precedence
+
+Format-related options can be specified in three ways, in decreasing order of precedence:
+
+- `CREATE EXTERNAL TABLE` syntax
+- `COPY` option tuples
+- Session-level config defaults
+
+For a list of supported session-level config defaults, see [Configuration Settings](../configs). These defaults apply to all operations but have the lowest level of precedence.
+
+If creating an external table, table-specific format options can be specified when the table is created using the `OPTIONS` clause:
+
+```sql
+CREATE EXTERNAL TABLE
+ my_table(a bigint, b bigint)
+ STORED AS csv
+ LOCATION '/test/location/my_csv_table/'
+ OPTIONS(
+ NULL_VALUE 'NAN',
+ 'has_header' 'true',
+ 'format.delimiter' ';'
+ );
+```
+
+When running `INSERT INTO my_table ...`, the options from the `CREATE TABLE` will be respected (e.g., gzip compression, special delimiter, and header row included). Note that compression, header, and delimiter settings can also be specified within the `OPTIONS` tuple list. Dedicated syntax within the SQL statement always takes precedence over arbitrary option tuples, so if both are specified, the `OPTIONS` setting will be ignored.
+
+Finally, options can be passed when running a `COPY` command.
+
+```sql
+COPY source_table
+ TO 'test/table_with_options'
+ PARTITIONED BY (column3, column4)
+ OPTIONS (
+ format parquet,
+ compression snappy,
+ 'compression::column1' 'zstd(5)',
+ )
+```
+
+In this example, we write the entirety of `source_table` out to a folder of Parquet files. One Parquet file will be written in parallel to the folder for each partition in the query. The next option `compression` set to `snappy` indicates that unless otherwise specified, all columns should use the snappy compression codec. The option `compression::col1` sets an override, so that the column `col1` in the Parquet file will use the ZSTD compression codec with compression level `5`. In general, Parquet options that support column-specific settings can be specified with the syntax `OPTION::COLUMN.NESTED.PATH`.
+
+# Available Options
+
+## Execution-Specific Options
+
+The following options are available when executing a `COPY` query.
+
+| Option Key | Description | Default Value |
Review Comment:
Thank you for helping push this along @marvelshan -- it takes non trivial effort to make these docs
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Improve documentation for format `OPTIONS` clause [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb merged PR #15708:
URL: https://github.com/apache/datafusion/pull/15708
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] doc/document options clause [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #15708:
URL: https://github.com/apache/datafusion/pull/15708#issuecomment-2802869717
Run extended tests
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Improve documentation for format `OPTIONS` clause [datafusion]
Posted by "marvelshan (via GitHub)" <gi...@apache.org>.
marvelshan commented on code in PR #15708:
URL: https://github.com/apache/datafusion/pull/15708#discussion_r2049931675
##########
docs/source/user-guide/sql/format_options.md:
##########
@@ -0,0 +1,209 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Format Options
+
+DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` statements. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. In some cases, Options can be specified in multiple ways with a set order of precedence.
+
+## Specifying Options and Order of Precedence
+
+Format-related options can be specified in three ways, in decreasing order of precedence:
+
+- `CREATE EXTERNAL TABLE` syntax
+- `COPY` option tuples
+- Session-level config defaults
+
+For a list of supported session-level config defaults, see [Configuration Settings](../configs). These defaults apply to all operations but have the lowest level of precedence.
+
+If creating an external table, table-specific format options can be specified when the table is created using the `OPTIONS` clause:
+
+```sql
+CREATE EXTERNAL TABLE
+ my_table(a bigint, b bigint)
+ STORED AS csv
+ LOCATION '/test/location/my_csv_table/'
+ OPTIONS(
+ NULL_VALUE 'NAN',
+ 'has_header' 'true',
+ 'format.delimiter' ';'
+ );
+```
+
+When running `INSERT INTO my_table ...`, the options from the `CREATE TABLE` will be respected (e.g., gzip compression, special delimiter, and header row included). Note that compression, header, and delimiter settings can also be specified within the `OPTIONS` tuple list. Dedicated syntax within the SQL statement always takes precedence over arbitrary option tuples, so if both are specified, the `OPTIONS` setting will be ignored.
+
+Finally, options can be passed when running a `COPY` command.
+
+```sql
+COPY source_table
+ TO 'test/table_with_options'
+ PARTITIONED BY (column3, column4)
+ OPTIONS (
+ format parquet,
+ compression snappy,
+ 'compression::column1' 'zstd(5)',
+ )
+```
+
+In this example, we write the entirety of `source_table` out to a folder of Parquet files. One Parquet file will be written in parallel to the folder for each partition in the query. The next option `compression` set to `snappy` indicates that unless otherwise specified, all columns should use the snappy compression codec. The option `compression::col1` sets an override, so that the column `col1` in the Parquet file will use the ZSTD compression codec with compression level `5`. In general, Parquet options that support column-specific settings can be specified with the syntax `OPTION::COLUMN.NESTED.PATH`.
+
+# Available Options
+
+## Execution-Specific Options
+
+The following options are available when executing a `COPY` query.
+
+| Option Key | Description | Default Value |
Review Comment:
Thank you very much for your review! I truly appreciate you taking the time to make the final adjustments. I’ve learned a lot of valuable details from this process. If there’s anything else that needs further revision, please don’t hesitate to let me know—I’d be more than happy to make the necessary changes.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Improve documentation for format `OPTIONS` clause [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #15708:
URL: https://github.com/apache/datafusion/pull/15708#discussion_r2047668929
##########
docs/source/user-guide/sql/write_options.md:
##########
@@ -1,127 +0,0 @@
-<!---
- Licensed to the Apache Software Foundation (ASF) under one
- or more contributor license agreements. See the NOTICE file
- distributed with this work for additional information
- regarding copyright ownership. The ASF licenses this file
- to you under the Apache License, Version 2.0 (the
- "License"); you may not use this file except in compliance
- with the License. You may obtain a copy of the License at
-
- http://www.apache.org/licenses/LICENSE-2.0
-
- Unless required by applicable law or agreed to in writing,
- software distributed under the License is distributed on an
- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- KIND, either express or implied. See the License for the
- specific language governing permissions and limitations
- under the License.
--->
-
-# Write Options
-
-DataFusion supports customizing how data is written out to disk as a result of a `COPY` or `INSERT INTO` query. There are a few special options, file format (e.g. CSV or parquet) specific options, and parquet column specific options. Options can also in some cases be specified in multiple ways with a set order of precedence.
-
-## Specifying Options and Order of Precedence
-
-Write related options can be specified in the following ways:
-
-- Session level config defaults
-- `CREATE EXTERNAL TABLE` options
-- `COPY` option tuples
-
-For a list of supported session level config defaults see [Configuration Settings](../configs). These defaults apply to all write operations but have the lowest level of precedence.
-
-If inserting to an external table, table specific write options can be specified when the table is created using the `OPTIONS` clause:
-
-```sql
-CREATE EXTERNAL TABLE
- my_table(a bigint, b bigint)
- STORED AS csv
- COMPRESSION TYPE gzip
- LOCATION '/test/location/my_csv_table/'
- OPTIONS(
- NULL_VALUE 'NAN',
- 'has_header' 'true',
- 'format.delimiter' ';'
- )
-```
-
-When running `INSERT INTO my_table ...`, the options from the `CREATE TABLE` will be respected (gzip compression, special delimiter, and header row included). There will be a single output file if the output path doesn't have folder format, i.e. ending with a `\`. Note that compression, header, and delimiter settings can also be specified within the `OPTIONS` tuple list. Dedicated syntax within the SQL statement always takes precedence over arbitrary option tuples, so if both are specified the `OPTIONS` setting will be ignored. NULL_VALUE is a CSV format specific option that determines how null values should be encoded within the CSV file.
-
-Finally, options can be passed when running a `COPY` command.
-
-<!--
- Test the following example with:
- CREATE TABLE source_table AS VALUES ('1','2','3','4');
--->
-
-```sql
-COPY source_table
- TO 'test/table_with_options'
- PARTITIONED BY (column3, column4)
- OPTIONS (
- format parquet,
- compression snappy,
- 'compression::column1' 'zstd(5)',
- )
-```
-
-In this example, we write the entirety of `source_table` out to a folder of parquet files. One parquet file will be written in parallel to the folder for each partition in the query. The next option `compression` set to `snappy` indicates that unless otherwise specified all columns should use the snappy compression codec. The option `compression::col1` sets an override, so that the column `col1` in the parquet file will use `ZSTD` compression codec with compression level `5`. In general, parquet options which support column specific settings can be specified with the syntax `OPTION::COLUMN.NESTED.PATH`.
-
-## Available Options
-
-### Execution Specific Options
-
-The following options are available when executing a `COPY` query.
-
-| Option | Description | Default Value |
-| ----------------------------------- | ---------------------------------------------------------------------------------- | ------------- |
-| execution.keep_partition_by_columns | Flag to retain the columns in the output data when using `PARTITIONED BY` queries. | false |
-
-Note: `execution.keep_partition_by_columns` flag can also be enabled through `ExecutionOptions` within `SessionConfig`.
-
-### JSON Format Specific Options
-
-The following options are available when writing JSON files. Note: If any unsupported option is specified, an error will be raised and the query will fail.
-
-| Option | Description | Default Value |
-| ----------- | ---------------------------------------------------------------------------------------------------------------------------------- | ------------- |
-| COMPRESSION | Sets the compression that should be applied to the entire JSON file. Supported values are GZIP, BZIP2, XZ, ZSTD, and UNCOMPRESSED. | UNCOMPRESSED |
-
-### CSV Format Specific Options
-
-The following options are available when writing CSV files. Note: if any unsupported options is specified an error will be raised and the query will fail.
-
-| Option | Description | Default Value |
-| --------------- | --------------------------------------------------------------------------------------------------------------------------------- | ---------------- |
-| COMPRESSION | Sets the compression that should be applied to the entire CSV file. Supported values are GZIP, BZIP2, XZ, ZSTD, and UNCOMPRESSED. | UNCOMPRESSED |
-| HEADER | Sets if the CSV file should include column headers | false |
-| DATE_FORMAT | Sets the format that dates should be encoded in within the CSV file | arrow-rs default |
-| DATETIME_FORMAT | Sets the format that datetimes should be encoded in within the CSV file | arrow-rs default |
-| TIME_FORMAT | Sets the format that times should be encoded in within the CSV file | arrow-rs default |
-| RFC3339 | If true, uses RFC339 format for date and time encodings | arrow-rs default |
-| NULL_VALUE | Sets the string which should be used to indicate null values within the CSV file. | arrow-rs default |
-| DELIMITER | Sets the character which should be used as the column delimiter within the CSV file. | arrow-rs default |
-
-### Parquet Format Specific Options
-
-The following options are available when writing parquet files. If any unsupported option is specified an error will be raised and the query will fail. If a column specific option is specified for a column which does not exist, the option will be ignored without error. For default values, see: [Configuration Settings](https://datafusion.apache.org/user-guide/configs.html).
-
-| Option | Can be Column Specific? | Description |
-| ---------------------------- | ----------------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
-| COMPRESSION | Yes | Sets the compression codec and if applicable compression level to use |
-| MAX_ROW_GROUP_SIZE | No | Sets the maximum number of rows that can be encoded in a single row group. Larger row groups require more memory to write and read. |
-| DATA_PAGESIZE_LIMIT | No | Sets the best effort maximum page size in bytes |
-| WRITE_BATCH_SIZE | No | Maximum number of rows written for each column in a single batch |
-| WRITER_VERSION | No | Parquet writer version (1.0 or 2.0) |
-| DICTIONARY_PAGE_SIZE_LIMIT | No | Sets best effort maximum dictionary page size in bytes |
-| CREATED_BY | No | Sets the "created by" property in the parquet file |
-| COLUMN_INDEX_TRUNCATE_LENGTH | No | Sets the max length of min/max value fields in the column index. |
-| DATA_PAGE_ROW_COUNT_LIMIT | No | Sets best effort maximum number of rows in a data page. |
Review Comment:
We seem to have lost some of these options in the new doc
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] doc/document options clause [datafusion]
Posted by "marvelshan (via GitHub)" <gi...@apache.org>.
marvelshan commented on PR #15708:
URL: https://github.com/apache/datafusion/pull/15708#issuecomment-2805961608
Thank you for the suggestions!
I realized there were several places I hadn’t tested thoroughly. After some testing, I’ve made updates and pushed a new version.
I also wanted to check with you: since there are quite a few available options on file `config.rs`, should we just list a few key ones in the documentation, or aim to include all of them? I’m wondering if listing everything might make the docs overly complex.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] doc/document options clause [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #15708:
URL: https://github.com/apache/datafusion/pull/15708#discussion_r2044271528
##########
docs/source/user-guide/sql/format_options.md:
##########
@@ -0,0 +1,142 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Format Options
+
+DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` query. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. Options can also in some cases be specified in multiple ways with a set order of precedence.
Review Comment:
```suggestion
DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` statements. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. In some cases, Options can be specified in multiple ways with a set order of precedence.
```
##########
docs/source/user-guide/sql/format_options.md:
##########
@@ -0,0 +1,142 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Format Options
+
+DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` query. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. Options can also in some cases be specified in multiple ways with a set order of precedence.
+
+## Specifying Options and Order of Precedence
+
+Format-related options can be specified in the following ways:
+
+- Session-level config defaults
+- `CREATE EXTERNAL TABLE` options
+- `COPY` option tuples
+
Review Comment:
I think it would be helpful to explicit specify the order of precedence here. Something like
```suggestion
Format-related options can be specified in three ways, in decreasing order of precedence:
- `CREATE EXTERNAL TABLE` syntax
- `COPY` option tuples
- Session-level config defaults
```
##########
docs/source/user-guide/sql/format_options.md:
##########
@@ -0,0 +1,142 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Format Options
+
+DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` query. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. Options can also in some cases be specified in multiple ways with a set order of precedence.
+
+## Specifying Options and Order of Precedence
+
+Format-related options can be specified in the following ways:
+
+- Session-level config defaults
+- `CREATE EXTERNAL TABLE` options
+- `COPY` option tuples
+
+For a list of supported session-level config defaults, see [Configuration Settings](../configs). These defaults apply to all operations but have the lowest level of precedence.
+
+If creating an external table, table-specific format options can be specified when the table is created using the `OPTIONS` clause:
+
+```sql
+CREATE EXTERNAL TABLE
+ my_table(a bigint, b bigint)
+ STORED AS csv
+ LOCATION '/test/location/my_csv_table/'
+ OPTIONS(
+ NULL_VALUE 'NAN',
+ 'has_header' 'true',
+ 'format.delimiter' ';'
+ )
+```
+
+When running `INSERT INTO my_table ...`, the options from the `CREATE TABLE` will be respected (e.g., gzip compression, special delimiter, and header row included). Note that compression, header, and delimiter settings can also be specified within the `OPTIONS` tuple list. Dedicated syntax within the SQL statement always takes precedence over arbitrary option tuples, so if both are specified, the `OPTIONS` setting will be ignored.
+
+Finally, options can be passed when running a `COPY` command.
+
+```sql
+COPY source_table
+ TO 'test/table_with_options'
+ PARTITIONED BY (column3, column4)
+ OPTIONS (
+ format parquet,
+ compression snappy,
+ 'compression::column1' 'zstd(5)',
+ )
+```
+
+In this example, we write the entirety of `source_table` out to a folder of Parquet files. One Parquet file will be written in parallel to the folder for each partition in the query. The next option `compression` set to `snappy` indicates that unless otherwise specified, all columns should use the snappy compression codec. The option `compression::col1` sets an override, so that the column `col1` in the Parquet file will use the ZSTD compression codec with compression level `5`. In general, Parquet options that support column-specific settings can be specified with the syntax `OPTION::COLUMN.NESTED.PATH`.
+
+# Available Options
+
+## Generic Options
+
+| Option | Description | Default Value |
+| ---------- | ------------------------------------------------------------- | ---------------- |
+| NULL_VALUE | Sets the string which should be used to indicate null values. | arrow-rs default |
+
+## Execution-Specific Options
+
+The following options are available when executing a `COPY` query.
+
+| Option | Description | Default Value |
+| ------------------------- | ---------------------------------------------------------------------------------- | ------------- |
+| KEEP_PARTITION_BY_COLUMNS | Flag to retain the columns in the output data when using `PARTITIONED BY` queries. | false |
+
+Note: `execution.keep_partition_by_columns` flag can also be enabled through `ExecutionOptions` within `SessionConfig`.
+
+## JSON Format Options
+
+The following options are available when reading or writing JSON files. Note: If any unsupported option is specified, an error will be raised and the query will fail.
+
+| Option | Description | Default Value |
+| ----------- | ---------------------------------------------------------------------------------------------------------------------------------- | ------------- |
+| COMPRESSION | Sets the compression that should be applied to the entire JSON file. Supported values are GZIP, BZIP2, XZ, ZSTD, and UNCOMPRESSED. | UNCOMPRESSED |
+
+**Example:**
+
+```sql
+CREATE EXTERNAL TABLE t
+STORED AS JSON
+LOCATION '/tmp/foo.json'
+OPTIONS('COMPRESSION', 'gzip');
Review Comment:
I think this needs to have a column definition
```sql
> CREATE EXTERNAL TABLE t
STORED AS JSON
LOCATION '/tmp/foo.json'
OPTIONS('COMPRESSION', 'gzip'); 🤔 Invalid statement: sql parser error: Expected: string or numeric value, found: , at Line: 4, Column: 22
```
Also, to write data you need to specify a directory otherwise you get an error
```sql
> CREATE EXTERNAL TABLE t(a int)
STORED AS JSON
LOCATION '/tmp/foo.json'
OPTIONS('COMPRESSION' 'gzip');
0 row(s) fetched.
Elapsed 0.003 seconds.
> insert into t values(1);
Error during planning: Inserting into a ListingTable backed by a single file is not supported, URL is possibly missing a trailing `/`. To append to an existing file use StreamTable, e.g. by using CREATE UNBOUNDED EXTERNAL TABLE
```
Also there is an extra `,`
So maybe something like
```suggestion
CREATE EXTERNAL TABLE t(a int)
STORED AS JSON
LOCATION '/tmp/foo'
OPTIONS('COMPRESSION' 'gzip');
-- Inserting arow creates a new file in /tmp/foo
INSERT INTO t VALUES(1);
```
##########
docs/source/user-guide/sql/format_options.md:
##########
@@ -0,0 +1,142 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Format Options
+
+DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` query. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. Options can also in some cases be specified in multiple ways with a set order of precedence.
+
+## Specifying Options and Order of Precedence
+
+Format-related options can be specified in the following ways:
+
+- Session-level config defaults
+- `CREATE EXTERNAL TABLE` options
+- `COPY` option tuples
+
+For a list of supported session-level config defaults, see [Configuration Settings](../configs). These defaults apply to all operations but have the lowest level of precedence.
+
+If creating an external table, table-specific format options can be specified when the table is created using the `OPTIONS` clause:
+
+```sql
+CREATE EXTERNAL TABLE
+ my_table(a bigint, b bigint)
+ STORED AS csv
+ LOCATION '/test/location/my_csv_table/'
+ OPTIONS(
+ NULL_VALUE 'NAN',
+ 'has_header' 'true',
+ 'format.delimiter' ';'
+ )
+```
+
+When running `INSERT INTO my_table ...`, the options from the `CREATE TABLE` will be respected (e.g., gzip compression, special delimiter, and header row included). Note that compression, header, and delimiter settings can also be specified within the `OPTIONS` tuple list. Dedicated syntax within the SQL statement always takes precedence over arbitrary option tuples, so if both are specified, the `OPTIONS` setting will be ignored.
+
+Finally, options can be passed when running a `COPY` command.
+
+```sql
+COPY source_table
+ TO 'test/table_with_options'
+ PARTITIONED BY (column3, column4)
+ OPTIONS (
+ format parquet,
+ compression snappy,
+ 'compression::column1' 'zstd(5)',
+ )
+```
+
+In this example, we write the entirety of `source_table` out to a folder of Parquet files. One Parquet file will be written in parallel to the folder for each partition in the query. The next option `compression` set to `snappy` indicates that unless otherwise specified, all columns should use the snappy compression codec. The option `compression::col1` sets an override, so that the column `col1` in the Parquet file will use the ZSTD compression codec with compression level `5`. In general, Parquet options that support column-specific settings can be specified with the syntax `OPTION::COLUMN.NESTED.PATH`.
+
+# Available Options
+
+## Generic Options
+
+| Option | Description | Default Value |
+| ---------- | ------------------------------------------------------------- | ---------------- |
+| NULL_VALUE | Sets the string which should be used to indicate null values. | arrow-rs default |
Review Comment:
I think this is a CSV specific option (not a generic option)
For example
```sql
> create external table my_table(a int) stored as JSON location '/tmp/foo' options('NULL_VALUE' 'NULL');
Invalid or Unsupported Configuration: Config value "null_value" not found on JsonOptions
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Improve documentation for format `OPTIONS` clause [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #15708:
URL: https://github.com/apache/datafusion/pull/15708#discussion_r2049366426
##########
docs/source/user-guide/sql/format_options.md:
##########
@@ -0,0 +1,209 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Format Options
+
+DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` statements. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. In some cases, Options can be specified in multiple ways with a set order of precedence.
+
+## Specifying Options and Order of Precedence
+
+Format-related options can be specified in three ways, in decreasing order of precedence:
+
+- `CREATE EXTERNAL TABLE` syntax
+- `COPY` option tuples
+- Session-level config defaults
+
+For a list of supported session-level config defaults, see [Configuration Settings](../configs). These defaults apply to all operations but have the lowest level of precedence.
+
+If creating an external table, table-specific format options can be specified when the table is created using the `OPTIONS` clause:
+
+```sql
+CREATE EXTERNAL TABLE
+ my_table(a bigint, b bigint)
+ STORED AS csv
+ LOCATION '/test/location/my_csv_table/'
+ OPTIONS(
+ NULL_VALUE 'NAN',
+ 'has_header' 'true',
+ 'format.delimiter' ';'
+ );
+```
+
+When running `INSERT INTO my_table ...`, the options from the `CREATE TABLE` will be respected (e.g., gzip compression, special delimiter, and header row included). Note that compression, header, and delimiter settings can also be specified within the `OPTIONS` tuple list. Dedicated syntax within the SQL statement always takes precedence over arbitrary option tuples, so if both are specified, the `OPTIONS` setting will be ignored.
+
+Finally, options can be passed when running a `COPY` command.
+
+```sql
+COPY source_table
+ TO 'test/table_with_options'
+ PARTITIONED BY (column3, column4)
+ OPTIONS (
+ format parquet,
+ compression snappy,
+ 'compression::column1' 'zstd(5)',
+ )
+```
+
+In this example, we write the entirety of `source_table` out to a folder of Parquet files. One Parquet file will be written in parallel to the folder for each partition in the query. The next option `compression` set to `snappy` indicates that unless otherwise specified, all columns should use the snappy compression codec. The option `compression::col1` sets an override, so that the column `col1` in the Parquet file will use the ZSTD compression codec with compression level `5`. In general, Parquet options that support column-specific settings can be specified with the syntax `OPTION::COLUMN.NESTED.PATH`.
+
+# Available Options
+
+## Execution-Specific Options
+
+The following options are available when executing a `COPY` query.
+
+| Option Key | Description | Default Value |
Review Comment:
This largely replicates what is already in this page: https://datafusion.apache.org/user-guide/configs.html
I think the idea is that the format_options page will describe options specific to formats -- I will push a small commit to fix
##########
docs/source/user-guide/sql/format_options.md:
##########
@@ -0,0 +1,209 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Format Options
+
+DataFusion supports customizing how data is read from or written to disk as a result of a `COPY`, `INSERT INTO`, or `CREATE EXTERNAL TABLE` statements. There are a few special options, file format (e.g., CSV or Parquet) specific options, and Parquet column-specific options. In some cases, Options can be specified in multiple ways with a set order of precedence.
+
+## Specifying Options and Order of Precedence
+
+Format-related options can be specified in three ways, in decreasing order of precedence:
+
+- `CREATE EXTERNAL TABLE` syntax
+- `COPY` option tuples
+- Session-level config defaults
+
+For a list of supported session-level config defaults, see [Configuration Settings](../configs). These defaults apply to all operations but have the lowest level of precedence.
+
+If creating an external table, table-specific format options can be specified when the table is created using the `OPTIONS` clause:
+
+```sql
+CREATE EXTERNAL TABLE
+ my_table(a bigint, b bigint)
+ STORED AS csv
+ LOCATION '/test/location/my_csv_table/'
+ OPTIONS(
+ NULL_VALUE 'NAN',
+ 'has_header' 'true',
+ 'format.delimiter' ';'
+ );
+```
+
+When running `INSERT INTO my_table ...`, the options from the `CREATE TABLE` will be respected (e.g., gzip compression, special delimiter, and header row included). Note that compression, header, and delimiter settings can also be specified within the `OPTIONS` tuple list. Dedicated syntax within the SQL statement always takes precedence over arbitrary option tuples, so if both are specified, the `OPTIONS` setting will be ignored.
+
+Finally, options can be passed when running a `COPY` command.
+
+```sql
+COPY source_table
+ TO 'test/table_with_options'
+ PARTITIONED BY (column3, column4)
+ OPTIONS (
+ format parquet,
+ compression snappy,
+ 'compression::column1' 'zstd(5)',
+ )
+```
+
+In this example, we write the entirety of `source_table` out to a folder of Parquet files. One Parquet file will be written in parallel to the folder for each partition in the query. The next option `compression` set to `snappy` indicates that unless otherwise specified, all columns should use the snappy compression codec. The option `compression::col1` sets an override, so that the column `col1` in the Parquet file will use the ZSTD compression codec with compression level `5`. In general, Parquet options that support column-specific settings can be specified with the syntax `OPTION::COLUMN.NESTED.PATH`.
+
+# Available Options
+
+## Execution-Specific Options
+
+The following options are available when executing a `COPY` query.
+
+| Option Key | Description | Default Value |
+| ----------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------ | ------------- |
+| execution.batch_size | Default batch size while creating new batches. | 8192 |
+| execution.coalesce_batches | Whether to coalesce small batches into larger ones between operators. | true |
+| execution.collect_statistics | Should DataFusion collect statistics after listing files. | false |
+| execution.target_partitions | Number of partitions for query execution. Defaults to number of CPU cores. | (num_cpus) |
+| execution.time_zone | The default time zone (e.g., "+00:00"). | "+00:00" |
+| execution.planning_concurrency | Fan-out during initial physical planning. Defaults to number of CPU cores. | (num_cpus) |
+| execution.skip_physical_aggregate_schema_check | Skip verifying that the schema produced by planning the input of `Aggregate` matches the input plan. | false |
+| execution.sort_spill_reservation_bytes | Reserved memory for each spillable sort operation (bytes). | 10485760 |
+| execution.sort_in_place_threshold_bytes | Below what size should data be concatenated and sorted in a single RecordBatch (bytes). | 1048576 |
+| execution.meta_fetch_concurrency | Number of files to read in parallel when inferring schema and statistics. | 32 |
+| execution.minimum_parallel_output_files | Guarantees a minimum level of output files running in parallel. | 4 |
+| execution.soft_max_rows_per_output_file | Target number of rows in output files when writing multiple (soft max). | 50000000 |
+| execution.max_buffered_batches_per_output_file | Maximum number of RecordBatches buffered for each output file. | 2 |
+| execution.listing_table_ignore_subdirectory | Should subdirectories be ignored when scanning directories for data files. | true |
+| execution.enable_recursive_ctes | Should DataFusion support recursive CTEs. | true |
+| execution.split_file_groups_by_statistics | Attempt to eliminate sorts by packing & sorting files with non-overlapping statistics into same file groups. | false |
+| execution.keep_partition_by_columns | Should DataFusion keep the columns used for partition_by in the output RecordBatches. | false |
+| execution.skip_partial_aggregation_probe_ratio_threshold | Aggregation ratio threshold for skipping partial aggregation. | 0.8 |
+| execution.skip_partial_aggregation_probe_rows_threshold | Number of input rows partial aggregation partition should process before switching mode. | 100000 |
+| execution.use_row_number_estimates_to_optimize_partitioning | Use row number estimates to optimize partitioning. | false |
+| execution.enforce_batch_size_in_joins | Should DataFusion enforce batch size in joins. | false |
+
+**Usage Example:**
+
+```sql
+COPY my_table TO '/tmp/output.csv'
+OPTIONS (
+ 'execution.batch_size' '4096',
+ 'execution.target_partitions' '8',
+ 'execution.keep_partition_by_columns' 'true'
+);
+```
+
+Note: `execution.keep_partition_by_columns` flag can also be enabled through `ExecutionOptions` within `SessionConfig`.
+
+## JSON Format Options
+
+The following options are available when reading or writing JSON files. Note: If any unsupported option is specified, an error will be raised and the query will fail.
+
+| Option | Description | Default Value |
+| ----------- | ---------------------------------------------------------------------------------------------------------------------------------- | ------------- |
+| COMPRESSION | Sets the compression that should be applied to the entire JSON file. Supported values are GZIP, BZIP2, XZ, ZSTD, and UNCOMPRESSED. | UNCOMPRESSED |
+
+**Example:**
+
+```sql
+CREATE EXTERNAL TABLE t(a int)
+STORED AS JSON
+LOCATION '/tmp/foo/'
+OPTIONS('COMPRESSION' 'gzip');
+-- Inserting arow creates a new file in /tmp/foo
+INSERT INTO t VALUES(1);
+```
+
+## CSV Format Options
+
+The following options are available when reading or writing CSV files. Note: If any unsupported option is specified, an error will be raised and the query will fail.
+
+| Option | Description | Default Value |
+| -------------------- | --------------------------------------------------------------------------------------------------------------------------------- | ------------------ |
+| COMPRESSION | Sets the compression that should be applied to the entire CSV file. Supported values are GZIP, BZIP2, XZ, ZSTD, and UNCOMPRESSED. | UNCOMPRESSED |
+| HAS_HEADER | Sets if the CSV file should include column headers. If not set, uses session or system default. | None |
+| DELIMITER | Sets the character which should be used as the column delimiter within the CSV file. | `,` (comma) |
+| QUOTE | Sets the character which should be used for quoting values within the CSV file. | `"` (double quote) |
+| TERMINATOR | Sets the character which should be used as the line terminator within the CSV file. | None |
+| ESCAPE | Sets the character which should be used for escaping special characters within the CSV file. | None |
+| DOUBLE_QUOTE | Sets if quotes within quoted fields should be escaped by doubling them (e.g., `"aaa""bbb"`). | None |
+| NEWLINES_IN_VALUES | Sets if newlines in quoted values are supported. If not set, uses session or system default. | None |
+| DATE_FORMAT | Sets the format that dates should be encoded in within the CSV file. | None |
+| DATETIME_FORMAT | Sets the format that datetimes should be encoded in within the CSV file. | None |
+| TIMESTAMP_FORMAT | Sets the format that timestamps should be encoded in within the CSV file. | None |
+| TIMESTAMP_TZ_FORMAT | Sets the format that timestamps with timezone should be encoded in within the CSV file. | None |
+| TIME_FORMAT | Sets the format that times should be encoded in within the CSV file. | None |
+| NULL_VALUE | Sets the string which should be used to indicate null values within the CSV file. | None |
+| NULL_REGEX | Sets the regex pattern to match null values when loading CSVs. | None |
+| SCHEMA_INFER_MAX_REC | Sets the maximum number of records to scan to infer the schema. | None |
+| COMMENT | Sets the character which should be used to indicate comment lines in the CSV file. | None |
+
+**Example:**
+
+```sql
+CREATE EXTERNAL TABLE t (col1 varchar, col2 int, col3 boolean)
+STORED AS CSV
+LOCATION '/tmp/foo/'
+OPTIONS('DELIMITER' '|', 'HAS_HEADER' 'true', 'NEWLINES_IN_VALUES' 'true');
+```
+
+## Parquet Format Options
Review Comment:
👍 this looks great
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org