Repository navigation
Discussion for a Systematic Configuration in 'Create External Table' Options #8994
Description
Is your feature request related to a problem or challenge?
Currently, in our implementation of 'Create External Table', the configuration options are not systematically organized, leading to potential confusion and complexity for the users. This is especially evident when we compare our configuration pattern with other systems like Apache Flink and Apache Spark.
For instance, in our current setup, wiring CSV format options and AWS credential settings are done in the same context. This approach lacks the clarity and structure found in similar systems. Examples of more systematic configurations can be seen in:
Although Spark’s approach could be perceived as confusing when applied to our 'Create External Table' method, our method is currently more aligned with Spark's approach in terms of table creation.
However, one aspect where our system shines is in our session context configuration. We utilize a more intuitive dot (.) divided pattern, like datafusion.execution.parquet.statistics_enabled. This is more user-friendly and logically structured.
Describe the solution you'd like
I propose we adopt a more structured and systematic approach in defining table options, similar to our session context configuration. For example, instead of the current format:
CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 's3://boo/foo.csv'
OPTIONS ('AWS_ACCESS_KEY_ID' 'asdasd',
'AWS_SECRET_ACCESS_KEY', 'asdasd',
'timestamp_format' 'asdasd',
'date_format' 'asdasd')We could structure it more clearly:
CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 's3://boo/foo.csv'
OPTIONS ('aws.credentials.basic.accesskeyid' 'asdasd',
'aws.credentials.basic.secretkey', 'asdasd',
'format.csv.sink.timestamp_format' 'asdasd',
'format.csv.sink.date_format' 'asdasd')Or even more detailed:
CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 's3://boo/foo.csv'
OPTIONS ('aws.credentials.basic.accesskeyid' 'asdasd',
'aws.credentials.basic.secretkey', 'asdasd',
'format.csv.scan.datetime_regex' 'asdasd',
'format.csv.sink.timestamp_format' 'asdasd',
'format.csv.sink.date_format' 'asdasd')This approach would separate AWS credentials from CSV format options and further delineate options for scanning and sinking, enhancing clarity and ease of use.
Impact:
- User Experience: This change will significantly improve user experience by making configuration more intuitive and easy to understand.
- Documentation: Accompanying documentation will be necessary to guide users through the new configuration pattern.
- Compatibility: It’s important to note that this change will introduce breaking changes. Thus, a clear migration path needs to be provided for existing users.
Describe alternatives you've considered
No response
Additional context
No response
Activity
[-]Proposal for Systematic Configuration in 'Create External Table' Options[/-][+]Discussion for a Systematic Configuration in 'Create External Table' Options[/+]metesynnada commentedon Jan 25, 2024
PTAL @ozankabak @alamb @andygrove @Dandandan
alamb commentedon Jan 25, 2024
I think this proposal makes a lot of sense to me
Seeing something like
Seems pretty self explanatory and consistent with the other options as you have pointed out.
To ease the transition, we could also make some sort of aliases that allow the old keys to work too 🤔
alamb commentedon Jan 25, 2024
cc @devinjdangelo might also have some ideas
devinjdangelo commentedon Jan 26, 2024
Yes, this is a great idea! It will also allow for more uniformity in our session level and statement level option naming conventions. E.g. currently we have
vs
could become instead:
A nice side effect would be that
FileTypeWriterOptionscould safely ignore options outside of relevant name spaces rather than throwing an error. E.g. anything that doesn't matchformat.*could be ignored.metesynnada commentedon Jan 26, 2024
Why not
format.parquet.max_row_group_sizesince you may want to create two tables with different settings? I am not sure why it is a global configuration.alamb commentedon Jan 26, 2024
I think the global setting serves the let users change the defaults once and use those settings for all statements in the session, which can then be overridden per statement if desired.