This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new e23d34bae6 Support `SHOW ALL VERBOSE` to show settings description
(#7735)
e23d34bae6 is described below
commit e23d34bae60bb2f9c496241e218bab795af3af83
Author: comphead <[email protected]>
AuthorDate: Sat Oct 7 04:00:53 2023 -0700
Support `SHOW ALL VERBOSE` to show settings description (#7735)
* Expand SHOW ALL stmt to show settings description
* cli tests
* comments
---
datafusion-cli/tests/cli_integration.rs | 2 +-
datafusion/core/src/catalog/information_schema.rs | 15 ++--
datafusion/sql/src/statement.rs | 30 ++++---
.../sqllogictest/test_files/information_schema.slt | 96 ++++++++++++++++++++++
.../sqllogictest/test_files/set_variable.slt | 2 +-
docs/source/user-guide/cli.md | 29 +++++--
6 files changed, 149 insertions(+), 25 deletions(-)
diff --git a/datafusion-cli/tests/cli_integration.rs
b/datafusion-cli/tests/cli_integration.rs
index 28344ffa94..119a0aa39d 100644
--- a/datafusion-cli/tests/cli_integration.rs
+++ b/datafusion-cli/tests/cli_integration.rs
@@ -43,7 +43,7 @@ fn init() {
)]
#[case::set_batch_size(
["--command", "show datafusion.execution.batch_size", "--format", "json",
"-q", "-b", "1"],
- "[{\"name\":\"datafusion.execution.batch_size\",\"setting\":\"1\"}]\n"
+ "[{\"name\":\"datafusion.execution.batch_size\",\"value\":\"1\"}]\n"
)]
#[test]
fn cli_quick_test<'a>(
diff --git a/datafusion/core/src/catalog/information_schema.rs
b/datafusion/core/src/catalog/information_schema.rs
index b30683a3ea..3a8fef2d25 100644
--- a/datafusion/core/src/catalog/information_schema.rs
+++ b/datafusion/core/src/catalog/information_schema.rs
@@ -626,7 +626,8 @@ impl InformationSchemaDfSettings {
fn new(config: InformationSchemaConfig) -> Self {
let schema = Arc::new(Schema::new(vec![
Field::new("name", DataType::Utf8, false),
- Field::new("setting", DataType::Utf8, true),
+ Field::new("value", DataType::Utf8, true),
+ Field::new("description", DataType::Utf8, true),
]));
Self { schema, config }
@@ -635,7 +636,8 @@ impl InformationSchemaDfSettings {
fn builder(&self) -> InformationSchemaDfSettingsBuilder {
InformationSchemaDfSettingsBuilder {
names: StringBuilder::new(),
- settings: StringBuilder::new(),
+ values: StringBuilder::new(),
+ descriptions: StringBuilder::new(),
schema: self.schema.clone(),
}
}
@@ -664,13 +666,15 @@ impl PartitionStream for InformationSchemaDfSettings {
struct InformationSchemaDfSettingsBuilder {
schema: SchemaRef,
names: StringBuilder,
- settings: StringBuilder,
+ values: StringBuilder,
+ descriptions: StringBuilder,
}
impl InformationSchemaDfSettingsBuilder {
fn add_setting(&mut self, entry: ConfigEntry) {
self.names.append_value(entry.key);
- self.settings.append_option(entry.value);
+ self.values.append_option(entry.value);
+ self.descriptions.append_value(entry.description);
}
fn finish(&mut self) -> RecordBatch {
@@ -678,7 +682,8 @@ impl InformationSchemaDfSettingsBuilder {
self.schema.clone(),
vec![
Arc::new(self.names.finish()),
- Arc::new(self.settings.finish()),
+ Arc::new(self.values.finish()),
+ Arc::new(self.descriptions.finish()),
],
)
.unwrap()
diff --git a/datafusion/sql/src/statement.rs b/datafusion/sql/src/statement.rs
index ab19fa716c..de85820543 100644
--- a/datafusion/sql/src/statement.rs
+++ b/datafusion/sql/src/statement.rs
@@ -757,28 +757,34 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
}
fn show_variable_to_plan(&self, variable: &[Ident]) -> Result<LogicalPlan>
{
- let variable = object_name_to_string(&ObjectName(variable.to_vec()));
-
if !self.has_table("information_schema", "df_settings") {
return plan_err!(
"SHOW [VARIABLE] is not supported unless information_schema is
enabled"
);
}
- let variable_lower = variable.to_lowercase();
+ let verbose = variable
+ .last()
+ .map(|s| ident_to_string(s) == "verbose")
+ .unwrap_or(false);
+ let mut variable_vec = variable.to_vec();
+ let mut columns: String = "name, value".to_owned();
+
+ if verbose {
+ columns = format!("{columns}, description");
+ variable_vec = variable_vec.split_at(variable_vec.len() -
1).0.to_vec();
+ }
- let query = if variable_lower == "all" {
+ let variable = object_name_to_string(&ObjectName(variable_vec));
+ let base_query = format!("SELECT {columns} FROM
information_schema.df_settings");
+ let query = if variable == "all" {
// Add an ORDER BY so the output comes out in a consistent order
- String::from(
- "SELECT name, setting FROM information_schema.df_settings
ORDER BY name",
- )
- } else if variable_lower == "timezone" || variable_lower ==
"time.zone" {
+ format!("{base_query} ORDER BY name")
+ } else if variable == "timezone" || variable == "time.zone" {
// we could introduce alias in OptionDefinition if this string
matching thing grows
- String::from("SELECT name, setting FROM
information_schema.df_settings WHERE name = 'datafusion.execution.time_zone'")
+ format!("{base_query} WHERE name =
'datafusion.execution.time_zone'")
} else {
- format!(
- "SELECT name, setting FROM information_schema.df_settings
WHERE name = '{variable}'"
- )
+ format!("{base_query} WHERE name = '{variable}'")
};
let mut rewrite = DFParser::parse_sql(&query)?;
diff --git a/datafusion/sqllogictest/test_files/information_schema.slt
b/datafusion/sqllogictest/test_files/information_schema.slt
index 74c1296fa4..d2ec21488d 100644
--- a/datafusion/sqllogictest/test_files/information_schema.slt
+++ b/datafusion/sqllogictest/test_files/information_schema.slt
@@ -202,12 +202,85 @@ datafusion.sql_parser.dialect generic
datafusion.sql_parser.enable_ident_normalization true
datafusion.sql_parser.parse_float_as_decimal false
+# show all variables with verbose
+query TTT rowsort
+SHOW ALL VERBOSE
+----
+datafusion.catalog.create_default_catalog_and_schema true Whether the default
catalog and schema should be created automatically.
+datafusion.catalog.default_catalog datafusion The default catalog name - this
impacts what SQL queries use if not specified
+datafusion.catalog.default_schema public The default schema name - this
impacts what SQL queries use if not specified
+datafusion.catalog.format NULL Type of `TableProvider` to use when loading
`default` schema
+datafusion.catalog.has_header false If the file has a header
+datafusion.catalog.information_schema true Should DataFusion provide access to
`information_schema` virtual tables for displaying schema information
+datafusion.catalog.location NULL Location scanned to load tables for `default`
schema
+datafusion.execution.aggregate.scalar_update_factor 10 Specifies the threshold
for using `ScalarValue`s to update accumulators during high-cardinality
aggregations for each input batch. The aggregation is considered
high-cardinality if the number of affected groups is greater than or equal to
`batch_size / scalar_update_factor`. In such cases, `ScalarValue`s are utilized
for updating accumulators, rather than the default batch-slice approach. This
can lead to performance improvements. By [...]
+datafusion.execution.batch_size 8192 Default batch size while creating new
batches, it's especially useful for buffer-in-memory batches since creating
tiny batches would result in too much metadata memory consumption
+datafusion.execution.coalesce_batches true When set to true, record batches
will be examined between each operator and small batches will be coalesced into
larger batches. This is helpful when there are highly selective filters or
joins that could produce tiny output batches. The target batch size is
determined by the configuration setting
+datafusion.execution.collect_statistics false Should DataFusion collect
statistics after listing files
+datafusion.execution.meta_fetch_concurrency 32 Number of files to read in
parallel when inferring schema and statistics
+datafusion.execution.parquet.allow_single_file_parallelism false Controls
whether DataFusion will attempt to speed up writing large parquet files by
first writing multiple smaller files and then stitching them together into a
single large file. This will result in faster write speeds, but higher memory
usage. Also currently unsupported are bloom filters and column indexes when
single_file_parallelism is enabled.
+datafusion.execution.parquet.bloom_filter_enabled false Sets if bloom filter
is enabled for any column
+datafusion.execution.parquet.bloom_filter_fpp NULL Sets bloom filter false
positive probability. If NULL, uses default parquet writer setting
+datafusion.execution.parquet.bloom_filter_ndv NULL Sets bloom filter number of
distinct values. If NULL, uses default parquet writer setting
+datafusion.execution.parquet.column_index_truncate_length NULL Sets column
index trucate length
+datafusion.execution.parquet.compression zstd(3) Sets default parquet
compression codec Valid values are: uncompressed, snappy, gzip(level), lzo,
brotli(level), lz4, zstd(level), and lz4_raw. These values are not case
sensitive. If NULL, uses default parquet writer setting
+datafusion.execution.parquet.created_by datafusion Sets "created by" property
+datafusion.execution.parquet.data_page_row_count_limit 18446744073709551615
Sets best effort maximum number of rows in data page
+datafusion.execution.parquet.data_pagesize_limit 1048576 Sets best effort
maximum size of data page in bytes
+datafusion.execution.parquet.dictionary_enabled NULL Sets if dictionary
encoding is enabled. If NULL, uses default parquet writer setting
+datafusion.execution.parquet.dictionary_page_size_limit 1048576 Sets best
effort maximum dictionary page size, in bytes
+datafusion.execution.parquet.enable_page_index true If true, reads the Parquet
data page level metadata (the Page Index), if present, to reduce the I/O and
number of rows decoded.
+datafusion.execution.parquet.encoding NULL Sets default encoding for any
column Valid values are: plain, plain_dictionary, rle, bit_packed,
delta_binary_packed, delta_length_byte_array, delta_byte_array, rle_dictionary,
and byte_stream_split. These values are not case sensitive. If NULL, uses
default parquet writer setting
+datafusion.execution.parquet.max_row_group_size 1048576 Sets maximum number of
rows in a row group
+datafusion.execution.parquet.max_statistics_size NULL Sets max statistics size
for any column. If NULL, uses default parquet writer setting
+datafusion.execution.parquet.metadata_size_hint NULL If specified, the parquet
reader will try and fetch the last `size_hint` bytes of the parquet file
optimistically. If not specified, two reads are required: One read to fetch the
8-byte parquet footer and another to fetch the metadata length encoded in the
footer
+datafusion.execution.parquet.pruning true If true, the parquet reader attempts
to skip entire row groups based on the predicate in the query and the metadata
(min/max values) stored in the parquet file
+datafusion.execution.parquet.pushdown_filters false If true, filter
expressions are be applied during the parquet decoding operation to reduce the
number of rows decoded
+datafusion.execution.parquet.reorder_filters false If true, filter expressions
evaluated during the parquet decoding operation will be reordered heuristically
to minimize the cost of evaluation. If false, the filters are applied in the
same order as written in the query
+datafusion.execution.parquet.skip_metadata true If true, the parquet reader
skip the optional embedded metadata that may be in the file Schema. This
setting can help avoid schema conflicts when querying multiple parquet files
with schemas containing compatible types but different metadata
+datafusion.execution.parquet.statistics_enabled NULL Sets if statistics are
enabled for any column Valid values are: "none", "chunk", and "page" These
values are not case sensitive. If NULL, uses default parquet writer setting
+datafusion.execution.parquet.write_batch_size 1024 Sets write_batch_size in
bytes
+datafusion.execution.parquet.writer_version 1.0 Sets parquet writer version
valid values are "1.0" and "2.0"
+datafusion.execution.planning_concurrency 13 Fan-out during initial physical
planning. This is mostly use to plan `UNION` children in parallel. Defaults to
the number of CPU cores on the system
+datafusion.execution.sort_in_place_threshold_bytes 1048576 When sorting, below
what size should data be concatenated and sorted in a single RecordBatch rather
than sorted in batches and merged.
+datafusion.execution.sort_spill_reservation_bytes 10485760 Specifies the
reserved memory for each spillable sort operation to facilitate an in-memory
merge. When a sort operation spills to disk, the in-memory data must be sorted
and merged before being written to a file. This setting reserves a specific
amount of memory for that in-memory sort/merge process. Note: This setting is
irrelevant if the sort operation cannot spill (i.e., if there's no
`DiskManager` configured).
+datafusion.execution.target_partitions 7 Number of partitions for query
execution. Increasing partitions can increase concurrency. Defaults to the
number of CPU cores on the system
+datafusion.execution.time_zone +00:00 The default time zone Some functions,
e.g. `EXTRACT(HOUR from SOME_TIME)`, shift the underlying datetime according to
this time zone, and then extract the hour
+datafusion.explain.logical_plan_only false When set to true, the explain
statement will only print logical plans
+datafusion.explain.physical_plan_only false When set to true, the explain
statement will only print physical plans
+datafusion.explain.show_statistics false When set to true, the explain
statement will print operator statistics for physical plans
+datafusion.optimizer.allow_symmetric_joins_without_pruning true Should
DataFusion allow symmetric hash joins for unbounded data sources even when its
inputs do not have any ordering or filtering If the flag is not enabled, the
SymmetricHashJoin operator will be unable to prune its internal buffers,
resulting in certain join types - such as Full, Left, LeftAnti, LeftSemi,
Right, RightAnti, and RightSemi - being produced only at the end of the
execution. This is not typical in stream proce [...]
+datafusion.optimizer.enable_round_robin_repartition true When set to true, the
physical plan optimizer will try to add round robin repartitioning to increase
parallelism to leverage more CPU cores
+datafusion.optimizer.enable_topk_aggregation true When set to true, the
optimizer will attempt to perform limit operations during aggregations, if
possible
+datafusion.optimizer.filter_null_join_keys false When set to true, the
optimizer will insert filters before a join between a nullable and non-nullable
column to filter out nulls on the nullable side. This filter can add additional
overhead when the file format does not fully support predicate push down.
+datafusion.optimizer.hash_join_single_partition_threshold 1048576 The maximum
estimated size in bytes for one input side of a HashJoin will be collected into
a single partition
+datafusion.optimizer.max_passes 3 Number of times that the optimizer will
attempt to optimize the plan
+datafusion.optimizer.prefer_existing_sort false When true, DataFusion will
opportunistically remove sorts when the data is already sorted, (i.e. setting
`preserve_order` to true on `RepartitionExec` and using
`SortPreservingMergeExec`) When false, DataFusion will maximize plan
parallelism using `RepartitionExec` even if this requires subsequently
resorting data using a `SortExec`.
+datafusion.optimizer.prefer_hash_join true When set to true, the physical plan
optimizer will prefer HashJoin over SortMergeJoin. HashJoin can work more
efficiently than SortMergeJoin but consumes more memory
+datafusion.optimizer.repartition_aggregations true Should DataFusion
repartition data using the aggregate keys to execute aggregates in parallel
using the provided `target_partitions` level
+datafusion.optimizer.repartition_file_min_size 10485760 Minimum total files
size in bytes to perform file scan repartitioning.
+datafusion.optimizer.repartition_file_scans true When set to `true`, file
groups will be repartitioned to achieve maximum parallelism. Currently Parquet
and CSV formats are supported. If set to `true`, all files will be
repartitioned evenly (i.e., a single large file might be partitioned into
smaller chunks) for parallel scanning. If set to `false`, different files will
be read in parallel, but repartitioning won't happen within a single file.
+datafusion.optimizer.repartition_joins true Should DataFusion repartition data
using the join keys to execute joins in parallel using the provided
`target_partitions` level
+datafusion.optimizer.repartition_sorts true Should DataFusion execute sorts in
a per-partition fashion and merge afterwards instead of coalescing first and
sorting globally. With this flag is enabled, plans in the form below ```text
"SortExec: [a@0 ASC]", " CoalescePartitionsExec", "
RepartitionExec: partitioning=RoundRobinBatch(8), input_partitions=1", ```
would turn into the plan below which performs better in multithreaded
environments ```text "SortPreservingMe [...]
+datafusion.optimizer.repartition_windows true Should DataFusion repartition
data using the partitions keys to execute window functions in parallel using
the provided `target_partitions` level
+datafusion.optimizer.skip_failed_rules false When set to true, the logical
plan optimizer will produce warning messages if any optimization rules produce
errors and then proceed to the next rule. When set to false, any rules that
produce errors will cause the query to fail
+datafusion.optimizer.top_down_join_key_reordering true When set to true, the
physical plan optimizer will run a top down process to reorder the join keys
+datafusion.sql_parser.dialect generic Configure the SQL dialect used by
DataFusion's parser; supported values include: Generic, MySQL, PostgreSQL,
Hive, SQLite, Snowflake, Redshift, MsSQL, ClickHouse, BigQuery, and Ansi.
+datafusion.sql_parser.enable_ident_normalization true When set to true, SQL
parser will normalize ident (convert ident to lowercase when not quoted)
+datafusion.sql_parser.parse_float_as_decimal false When set to true, SQL
parser will parse float as decimal type
+
# show_variable_in_config_options
query TT
SHOW datafusion.execution.batch_size
----
datafusion.execution.batch_size 8192
+# show_variable_in_config_options_verbose
+query TTT
+SHOW datafusion.execution.batch_size VERBOSE
+----
+datafusion.execution.batch_size 8192 Default batch size while creating new
batches, it's especially useful for buffer-in-memory batches since creating
tiny batches would result in too much metadata memory consumption
+
# show_time_zone_default_utc
# https://github.com/apache/arrow-datafusion/issues/3255
query TT
@@ -223,6 +296,26 @@ SHOW TIMEZONE
datafusion.execution.time_zone +00:00
+# show_time_zone_default_utc_verbose
+# https://github.com/apache/arrow-datafusion/issues/3255
+query TTT
+SHOW TIME ZONE VERBOSE
+----
+datafusion.execution.time_zone +00:00 The default time zone Some functions,
e.g. `EXTRACT(HOUR from SOME_TIME)`, shift the underlying datetime according to
this time zone, and then extract the hour
+
+# show_timezone_default_utc
+# https://github.com/apache/arrow-datafusion/issues/3255
+query TTT
+SHOW TIMEZONE VERBOSE
+----
+datafusion.execution.time_zone +00:00 The default time zone Some functions,
e.g. `EXTRACT(HOUR from SOME_TIME)`, shift the underlying datetime according to
this time zone, and then extract the hour
+
+
+# show empty verbose
+query TTT
+SHOW VERBOSE
+----
+
# information_schema_describe_table
## some_table
@@ -372,6 +465,9 @@ set datafusion.catalog.information_schema = false;
statement error Error during planning: SHOW \[VARIABLE\] is not supported
unless information_schema is enabled
SHOW SOMETHING
+statement error Error during planning: SHOW \[VARIABLE\] is not supported
unless information_schema is enabled
+SHOW SOMETHING VERBOSE
+
statement ok
set datafusion.catalog.information_schema = true;
diff --git a/datafusion/sqllogictest/test_files/set_variable.slt
b/datafusion/sqllogictest/test_files/set_variable.slt
index 440fb2c6ef..714e1e995e 100644
--- a/datafusion/sqllogictest/test_files/set_variable.slt
+++ b/datafusion/sqllogictest/test_files/set_variable.slt
@@ -243,4 +243,4 @@ statement ok
SET TIME ZONE = 'Asia/Taipei2'
statement error Arrow error: Parser error: Invalid timezone "Asia/Taipei2":
'Asia/Taipei2' is not a valid timezone
-SELECT '2000-01-01T00:00:00'::TIMESTAMP::TIMESTAMPTZ
+SELECT '2000-01-01T00:00:00'::TIMESTAMP::TIMESTAMPTZ
\ No newline at end of file
diff --git a/docs/source/user-guide/cli.md b/docs/source/user-guide/cli.md
index 05b4165e61..e8fdae7bb0 100644
--- a/docs/source/user-guide/cli.md
+++ b/docs/source/user-guide/cli.md
@@ -397,11 +397,13 @@ Available commands inside DataFusion CLI are:
- Show configuration options
+`SHOW ALL [VERBOSE]`
+
```SQL
> show all;
+-------------------------------------------------+---------+
-| name | setting |
+| name | value |
+-------------------------------------------------+---------+
| datafusion.execution.batch_size | 8192 |
| datafusion.execution.coalesce_batches | true |
@@ -414,6 +416,21 @@ Available commands inside DataFusion CLI are:
```
+- Show specific configuration option
+
+`SHOW xyz.abc.qwe [VERBOSE]`
+
+```SQL
+> show datafusion.execution.batch_size;
+
++-------------------------------------------------+---------+
+| name | value |
++-------------------------------------------------+---------+
+| datafusion.execution.batch_size | 8192 |
++-------------------------------------------------+---------+
+
+```
+
- Set configuration options
```SQL
@@ -432,12 +449,12 @@ For example, to set `datafusion.execution.batch_size` to
`1024` you
would set the `DATAFUSION_EXECUTION_BATCH_SIZE` environment variable
appropriately:
-```shell
+```SQL
$ DATAFUSION_EXECUTION_BATCH_SIZE=1024 datafusion-cli
DataFusion CLI v12.0.0
❯ show all;
+-------------------------------------------------+---------+
-| name | setting |
+| name | value |
+-------------------------------------------------+---------+
| datafusion.execution.batch_size | 1024 |
| datafusion.execution.coalesce_batches | true |
@@ -452,13 +469,13 @@ DataFusion CLI v12.0.0
You can change the configuration options using `SET` statement as well
-```shell
+```SQL
$ datafusion-cli
DataFusion CLI v13.0.0
❯ show datafusion.execution.batch_size;
+---------------------------------+---------+
-| name | setting |
+| name | value |
+---------------------------------+---------+
| datafusion.execution.batch_size | 8192 |
+---------------------------------+---------+
@@ -469,7 +486,7 @@ DataFusion CLI v13.0.0
❯ show datafusion.execution.batch_size;
+---------------------------------+---------+
-| name | setting |
+| name | value |
+---------------------------------+---------+
| datafusion.execution.batch_size | 1024 |
+---------------------------------+---------+
