See the current usage using datafusion-cli --help:
Apache Arrow <dev@arrow.apache.org> Command Line Client for DataFusion query engine. USAGE: datafusion-cli [OPTIONS] OPTIONS: -b, --batch-size <BATCH_SIZE> The batch size of each query, or use DataFusion default -c, --command <COMMAND>... Execute the given command string(s), then exit --color Enables console syntax highlighting -f, --file <FILE>... Execute commands from file(s), then exit --format <FORMAT> [default: table] [possible values: csv, tsv, table, json, nd-json] -h, --help Print help information -m, --memory-limit <MEMORY_LIMIT> The memory pool limitation (e.g. '10g'), default to None (no limit) --maxrows <MAXROWS> The max number of rows to display for 'Table' format [possible values: numbers(0/10/...), inf(no limit)] [default: 40] --mem-pool-type <MEM_POOL_TYPE> Specify the memory pool type 'greedy' or 'fair', default to 'greedy' --top-memory-consumers <TOP_MEMORY_CONSUMERS> The number of top memory consumers to display when query fails due to memory exhaustion. To disable memory consumer tracking, set this value to 0 [default: 3] -d, --disk-limit <DISK_LIMIT> Available disk space for spilling queries (e.g. '10g'), default to None (uses DataFusion's default value of '100g') --object-store-profiling <OBJECT_STORE_PROFILING> Specify the default object_store_profiling mode, defaults to 'disabled'. [possible values: disabled, summary, trace] [default: Disabled] -p, --data-path <DATA_PATH> Path to your data, default to current directory -q, --quiet Reduce printing other than the results and work quietly -r, --rc <RC>... Run the provided files on startup instead of ~/.datafusionrc -V, --version Print version information
Available commands inside DataFusion CLI are:
> \q
> \?
> \d
> \d table_name
> \quiet [true|false]
> \h
> \h function
> \object_store_profiling [disabled|summary|trace]
When enabled, prints detailed information about object store (I/O) operations performed during query execution to STDOUT.
> \object_store_profiling trace ObjectStore Profile mode set to Trace > select count(*) from 'https://datasets.clickhouse.com/hits_compatible/athena_partitioned/hits_1.parquet'; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row(s) fetched. Elapsed 0.552 seconds. Object Store Profiling Instrumented Object Store: instrument_mode: Trace, inner: HttpStore 2025-10-17T18:08:48.457992+00:00 operation=Get duration=0.043592s size=8 range: bytes=174965036-174965043 path=hits_compatible/athena_partitioned/hits_1.parquet 2025-10-17T18:08:48.501878+00:00 operation=Get duration=0.031542s size=34322 range: bytes=174930714-174965035 path=hits_compatible/athena_partitioned/hits_1.parquet Summaries: +-----------+----------+-----------+-----------+-----------+-----------+-------+ | Operation | Metric | min | max | avg | sum | count | +-----------+----------+-----------+-----------+-----------+-----------+-------+ | Get | duration | 0.031542s | 0.043592s | 0.037567s | 0.075133s | 2 | | Get | size | 8 B | 34322 B | 17165 B | 34330 B | 2 | +-----------+----------+-----------+-----------+-----------+-----------+-------+
In addition to the normal SQL supported in DataFusion, datafusion-cli also supports additional statements and commands:
SHOW ALL [VERBOSE]Show configuration options
> show all; +-------------------------------------------------+---------+ | name | value | +-------------------------------------------------+---------+ | datafusion.execution.batch_size | 8192 | | datafusion.execution.coalesce_batches | true | | datafusion.execution.time_zone | UTC | | datafusion.explain.logical_plan_only | false | | datafusion.explain.physical_plan_only | false | | datafusion.optimizer.filter_null_join_keys | false | | datafusion.optimizer.skip_failed_rules | true | +-------------------------------------------------+---------+
SHOW <OPTION>>Show specific configuration option
> show datafusion.execution.batch_size; +-------------------------------------------------+---------+ | name | value | +-------------------------------------------------+---------+ | datafusion.execution.batch_size | 8192 | +-------------------------------------------------+---------+
SET <OPTION> TO <VALUE>> SET datafusion.execution.batch_size to 1024;
All available configuration options can be seen using SHOW ALL as described above.
You can change the configuration options using environment variables. datafusion-cli looks in the corresponding environment variable with an upper case name and all . converted to _.
For example, to set datafusion.execution.batch_size to 1024 you would set the DATAFUSION_EXECUTION_BATCH_SIZE environment variable appropriately:
$ DATAFUSION_EXECUTION_BATCH_SIZE=1024 datafusion-cli DataFusion CLI v12.0.0 > show all; +-------------------------------------------------+---------+ | name | value | +-------------------------------------------------+---------+ | datafusion.execution.batch_size | 1024 | | datafusion.execution.coalesce_batches | true | | datafusion.execution.time_zone | UTC | | datafusion.explain.logical_plan_only | false | | datafusion.explain.physical_plan_only | false | | datafusion.optimizer.filter_null_join_keys | false | | datafusion.optimizer.skip_failed_rules | true | +-------------------------------------------------+---------+ 8 rows in set. Query took 0.002 seconds.
You can change the configuration options using SET statement as well
$ datafusion-cli DataFusion CLI v13.0.0 > show datafusion.execution.batch_size; +---------------------------------+---------+ | name | value | +---------------------------------+---------+ | datafusion.execution.batch_size | 8192 | +---------------------------------+---------+ 1 row in set. Query took 0.011 seconds. > set datafusion.execution.batch_size to 1024; 0 rows in set. Query took 0.000 seconds. > show datafusion.execution.batch_size; +---------------------------------+---------+ | name | value | +---------------------------------+---------+ | datafusion.execution.batch_size | 1024 | +---------------------------------+---------+ 1 row in set. Query took 0.005 seconds.
datafusion-cli comes with build-in functions that are not included in the DataFusion SQL engine, see DataFusion CLI specific functions section for details.