You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2023/05/05 10:46:56 UTC
[GitHub] [arrow-datafusion] alamb opened a new pull request, #6247: Minor: document syntax of CREATE EXTERNAL TABLE
alamb opened a new pull request, #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247
# Which issue does this PR close?
# Rationale for this change
When writing tests yesterday it was quite challenging to remember the exact CREATE EXTERNAL table syntax so I want to document it for next time
# What changes are included in this PR?
add doc comments and update DDL user guide
# Are these changes tested?
N/A
# Are there any user-facing changes?
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] r4ntix commented on a diff in pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "r4ntix (via GitHub)" <gi...@apache.org>.
r4ntix commented on code in PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247#discussion_r1186265916
##########
docs/source/user-guide/sql/ddl.md:
##########
@@ -47,17 +47,50 @@ CREATE SCHEMA cat.emu;
## CREATE EXTERNAL TABLE
-Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. It is not necessary
-to provide schema information for Parquet files.
+`CREATE EXTERNAL TABLE` SQL statement registers a location on a local
+file system or remote object store as a named table which can be queried.
+
+The supported syntax is:
+
+```
+CREATE EXTERNAL TABLE
+[ IF NOT EXISTS ]
+<TABLE_NAME>[ (<column_definition>) ]
+STORED AS <file_type>
+[ WITH HEADER ROW ]
+[ DELIMITER <char> ]
+[ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+[ PARTITIONED BY (<column list>) ]
+[ WITH ORDER (<ordered column list>)
+[ OPTIONS (<key_value_list>)
+LOCATION <literal>
+
+<column_definition> := (<column_name> <data_type>, ...)
+
+<column_list> := (<column_name>, ...)
+
+<ordered_column_list> := (<column_name> <sort_clause>, ...)
+
+<key_value_list> := (<literal> <literal, <literal> <literal>, ...)
Review Comment:
> I don't think any option is (yet) handled by datafusion core
>
> I believe datafusion-cli handles some: https://arrow.apache.org/datafusion/user-guide/cli.html#registering-s3-data-sources
>
> @r4ntix maybe knows more
@alamb Yes, not all options are supported in datafusion-core. There is no actual `[ OPTIONS (<key_value_list>) ]` support in the current datafusion-core.
@metesynnada Do you mean that for all the `[ ... ]` options, do we need to be more detailed in the documentation? 🤔️
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] metesynnada commented on a diff in pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "metesynnada (via GitHub)" <gi...@apache.org>.
metesynnada commented on code in PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247#discussion_r1186005263
##########
docs/source/user-guide/sql/ddl.md:
##########
@@ -47,17 +47,50 @@ CREATE SCHEMA cat.emu;
## CREATE EXTERNAL TABLE
-Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. It is not necessary
-to provide schema information for Parquet files.
+`CREATE EXTERNAL TABLE` SQL statement registers a location on a local
+file system or remote object store as a named table which can be queried.
+
+The supported syntax is:
+
+```
+CREATE EXTERNAL TABLE
+[ IF NOT EXISTS ]
+<TABLE_NAME>[ (<column_definition>) ]
+STORED AS <file_type>
+[ WITH HEADER ROW ]
+[ DELIMITER <char> ]
+[ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+[ PARTITIONED BY (<column list>) ]
+[ WITH ORDER (<ordered column list>)
+[ OPTIONS (<key_value_list>)
+LOCATION <literal>
+
+<column_definition> := (<column_name> <data_type>, ...)
+
+<column_list> := (<column_name>, ...)
+
+<ordered_column_list> := (<column_name> <sort_clause>, ...)
+
+<key_value_list> := (<literal> <literal, <literal> <literal>, ...)
Review Comment:
Is there a collection of available options? If someone were to implement a mechanism based on options, how should it be documented?
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247#discussion_r1186015878
##########
docs/source/user-guide/sql/ddl.md:
##########
@@ -47,17 +47,50 @@ CREATE SCHEMA cat.emu;
## CREATE EXTERNAL TABLE
-Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. It is not necessary
-to provide schema information for Parquet files.
+`CREATE EXTERNAL TABLE` SQL statement registers a location on a local
+file system or remote object store as a named table which can be queried.
+
+The supported syntax is:
+
+```
+CREATE EXTERNAL TABLE
+[ IF NOT EXISTS ]
+<TABLE_NAME>[ (<column_definition>) ]
+STORED AS <file_type>
+[ WITH HEADER ROW ]
+[ DELIMITER <char> ]
+[ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+[ PARTITIONED BY (<column list>) ]
+[ WITH ORDER (<ordered column list>)
+[ OPTIONS (<key_value_list>)
+LOCATION <literal>
+
+<column_definition> := (<column_name> <data_type>, ...)
+
+<column_list> := (<column_name>, ...)
+
+<ordered_column_list> := (<column_name> <sort_clause>, ...)
+
+<key_value_list> := (<literal> <literal, <literal> <literal>, ...)
Review Comment:
I don't think any option is (yet) handled by datafusion core
I believe datafusion-cli handles some: https://arrow.apache.org/datafusion/user-guide/cli.html#registering-s3-data-sources
@r4ntix maybe knows more
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] ozankabak commented on a diff in pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "ozankabak (via GitHub)" <gi...@apache.org>.
ozankabak commented on code in PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247#discussion_r1186387363
##########
docs/source/user-guide/sql/ddl.md:
##########
@@ -47,17 +47,50 @@ CREATE SCHEMA cat.emu;
## CREATE EXTERNAL TABLE
-Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. It is not necessary
-to provide schema information for Parquet files.
+`CREATE EXTERNAL TABLE` SQL statement registers a location on a local
+file system or remote object store as a named table which can be queried.
+
+The supported syntax is:
+
+```
+CREATE EXTERNAL TABLE
+[ IF NOT EXISTS ]
+<TABLE_NAME>[ (<column_definition>) ]
+STORED AS <file_type>
+[ WITH HEADER ROW ]
+[ DELIMITER <char> ]
+[ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+[ PARTITIONED BY (<column list>) ]
+[ WITH ORDER (<ordered column list>)
+[ OPTIONS (<key_value_list>)
Review Comment:
The right bracket `]` seems missing.
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb merged pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb merged PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247#discussion_r1186162705
##########
docs/source/user-guide/sql/ddl.md:
##########
@@ -47,17 +47,50 @@ CREATE SCHEMA cat.emu;
## CREATE EXTERNAL TABLE
-Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. It is not necessary
-to provide schema information for Parquet files.
+`CREATE EXTERNAL TABLE` SQL statement registers a location on a local
+file system or remote object store as a named table which can be queried.
+
+The supported syntax is:
+
+```
+CREATE EXTERNAL TABLE
+[ IF NOT EXISTS ]
+<TABLE_NAME>[ (<column_definition>) ]
+STORED AS <file_type>
+[ WITH HEADER ROW ]
+[ DELIMITER <char> ]
+[ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+[ PARTITIONED BY (<column list>) ]
+[ WITH ORDER (<ordered column list>)
+[ OPTIONS (<key_value_list>)
+LOCATION <literal>
+
+<column_definition> := (<column_name> <data_type>, ...)
+
+<column_list> := (<column_name>, ...)
+
+<ordered_column_list> := (<column_name> <sort_clause>, ...)
+
+<key_value_list> := (<literal> <literal, <literal> <literal>, ...)
Review Comment:
> If someone were to implement a mechanism based on options, how should it be documented?
I recommend anything meant to be used by users of datafusion should be explicitly in the `CREATE TABLE` syntax -- e.g. https://github.com/apache/arrow-datafusion/issues/6248)
If we do want to do something with options, perhaps it could be documented in https://arrow.apache.org/datafusion/user-guide/sql/ddl.html
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] r4ntix commented on a diff in pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "r4ntix (via GitHub)" <gi...@apache.org>.
r4ntix commented on code in PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247#discussion_r1186265916
##########
docs/source/user-guide/sql/ddl.md:
##########
@@ -47,17 +47,50 @@ CREATE SCHEMA cat.emu;
## CREATE EXTERNAL TABLE
-Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. It is not necessary
-to provide schema information for Parquet files.
+`CREATE EXTERNAL TABLE` SQL statement registers a location on a local
+file system or remote object store as a named table which can be queried.
+
+The supported syntax is:
+
+```
+CREATE EXTERNAL TABLE
+[ IF NOT EXISTS ]
+<TABLE_NAME>[ (<column_definition>) ]
+STORED AS <file_type>
+[ WITH HEADER ROW ]
+[ DELIMITER <char> ]
+[ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+[ PARTITIONED BY (<column list>) ]
+[ WITH ORDER (<ordered column list>)
+[ OPTIONS (<key_value_list>)
+LOCATION <literal>
+
+<column_definition> := (<column_name> <data_type>, ...)
+
+<column_list> := (<column_name>, ...)
+
+<ordered_column_list> := (<column_name> <sort_clause>, ...)
+
+<key_value_list> := (<literal> <literal, <literal> <literal>, ...)
Review Comment:
> I don't think any option is (yet) handled by datafusion core
>
> I believe datafusion-cli handles some: https://arrow.apache.org/datafusion/user-guide/cli.html#registering-s3-data-sources
>
> @r4ntix maybe knows more
@alamb Yes, not all options are supported in datafusion-core. There is no actual `[ OPTIONS (<key_value_list>) ]` support in the current datafusion-core.
@metesynnada Do you mean that for all the `[ ... ]` options, do we need to be more detailed in the documentation?
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] ozankabak commented on a diff in pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "ozankabak (via GitHub)" <gi...@apache.org>.
ozankabak commented on code in PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247#discussion_r1186387054
##########
datafusion/sql/src/parser.rs:
##########
@@ -43,6 +43,30 @@ fn parse_file_type(s: &str) -> Result<String, ParserError> {
}
/// DataFusion extension DDL for `CREATE EXTERNAL TABLE`
+///
+/// Syntax:
+///
+/// ```text
+/// CREATE EXTERNAL TABLE
+/// [ IF NOT EXISTS ]
+/// <TABLE_NAME>[ (<column_definition>) ]
+/// STORED AS <file_type>
+/// [ WITH HEADER ROW ]
+/// [ DELIMITER <char> ]
+/// [ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+/// [ PARTITIONED BY (<column list>) ]
+/// [ WITH ORDER (<ordered column list>)
+/// [ OPTIONS (<key_value_list>)
Review Comment:
The right bracket `]` seems missing.
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #6247: Minor: document syntax of CREATE EXTERNAL TABLE
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #6247:
URL: https://github.com/apache/arrow-datafusion/pull/6247#discussion_r1186413276
##########
datafusion/sql/src/parser.rs:
##########
@@ -43,6 +43,30 @@ fn parse_file_type(s: &str) -> Result<String, ParserError> {
}
/// DataFusion extension DDL for `CREATE EXTERNAL TABLE`
+///
+/// Syntax:
+///
+/// ```text
+/// CREATE EXTERNAL TABLE
+/// [ IF NOT EXISTS ]
+/// <TABLE_NAME>[ (<column_definition>) ]
+/// STORED AS <file_type>
+/// [ WITH HEADER ROW ]
+/// [ DELIMITER <char> ]
+/// [ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+/// [ PARTITIONED BY (<column list>) ]
+/// [ WITH ORDER (<ordered column list>)
+/// [ OPTIONS (<key_value_list>)
Review Comment:
Good catch -- thank you !
```suggestion
/// [ WITH ORDER (<ordered column list>)
/// [ OPTIONS (<key_value_list>) ]
```
##########
docs/source/user-guide/sql/ddl.md:
##########
@@ -47,17 +47,50 @@ CREATE SCHEMA cat.emu;
## CREATE EXTERNAL TABLE
-Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. It is not necessary
-to provide schema information for Parquet files.
+`CREATE EXTERNAL TABLE` SQL statement registers a location on a local
+file system or remote object store as a named table which can be queried.
+
+The supported syntax is:
+
+```
+CREATE EXTERNAL TABLE
+[ IF NOT EXISTS ]
+<TABLE_NAME>[ (<column_definition>) ]
+STORED AS <file_type>
+[ WITH HEADER ROW ]
+[ DELIMITER <char> ]
+[ COMPRESSION TYPE <GZIP | BZIP2 | XZ | ZSTD> ]
+[ PARTITIONED BY (<column list>) ]
+[ WITH ORDER (<ordered column list>)
+[ OPTIONS (<key_value_list>)
Review Comment:
```suggestion
[ WITH ORDER (<ordered column list>)
[ OPTIONS (<key_value_list>) ]
```
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org