Skip to content

cli.md

Latest commit

 

History

History
278 lines (196 loc) · 9.19 KB

cli.md

File metadata and controls

278 lines (196 loc) · 9.19 KB
Raw

CLI Guide

dft also provides a scriptable Command Line Interface (CLI) for executing queries directly from files or the command line. It can connect to the same DataFusion engine behind the TUI, or optionally run your queries against a FlightSQL server.

Basic Usage

# Run queries from a file
dft -f query.sql

# Run a query directly on the command line
dft -c "SELECT 1+2"

Saving Results and Format Options

Use --output (or -o) to save the result of one command or SQL file. The output format is inferred from the .csv, .json, .parquet, or (when enabled) .vortex suffix.

Repeat --format-option KEY=VALUE to configure the selected writer. Option names follow DataFusion's file format options; the optional format. prefix is accepted. For example, Parquet options can be global or column-specific:

dft -c "SELECT id, name FROM users" -o users.parquet \
  --format-option compression=zstd\(5\) \
  --format-option encoding::id=delta_binary_packed \
  --format-option encoding::name=delta_length_byte_array

CSV writer options use the same mechanism:

dft -c "SELECT id, name FROM users" -o users.csv \
  --format-option delimiter='|' \
  --format-option has_header=false

Invalid options fail with a configuration error. Compressed CSV/JSON output and Vortex-specific writer options are not currently supported.

To list the available keys, defaults, and descriptions for a format:

dft --print-format-options parquet
dft --print-format-options csv
dft --print-format-options json

FlightSQL Mode

Use --flightsql or -q to run commands or files against a FlightSQL server (instead of the default local SessionContext). You can override the default host for that single command with --host

dft -f query.sql --flightsql --host "http://127.0.0.1:50052"

FlightSQL Commands

Additioanlly, you can use the flightsql subcommand to interact with different methods exposed by a FlightSQL server directly:

# Execute a SQL query
dft flightsql statement-query --sql "SELECT * FROM table"

# List all catalogs
dft flightsql get-catalogs

# List schemas in a catalog
dft flightsql get-db-schemas --catalog mycatalog
dft flightsql get-db-schemas --db-schema-filter-pattern "my%"

# List tables in a schema
dft flightsql get-tables --catalog mycatalog --db-schema-filter-pattern myschema
dft flightsql get-tables --table-name-filter-pattern "table" --table-types VIEW

# Get supported table types
dft flightsql get-table-types

# Get SQL capabilities and server information
dft flightsql get-sql-info
dft flightsql get-sql-info --info 1 --info 2  # Query specific info IDs

# Get data type information (XDBC/ODBC type metadata)
dft flightsql get-xdbc-type-info
dft flightsql get-xdbc-type-info --data-type 4  # Filter by specific SQL data type

Headers

Additional HTTP headers can be attached to FlightSQL connections using --header or --headers-file. These are useful for passing authentication tokens, tenant identifiers, or any other metadata required by the server.

Single headers (--header)

Pass one or more Name: Value pairs directly on the command line. The flag can be repeated:

dft -c "SELECT 1" --flightsql --header "x-api-key: secret"
dft -c "SELECT 1" --flightsql --header "x-tenant: acme" --header "x-api-key: secret"

Headers file (--headers-file)

For many headers, or to avoid exposing secrets in shell history, store them in a file and point --headers-file at it:

dft -c "SELECT 1" --flightsql --headers-file ~/.config/dft/headers.txt

The file supports three formats (which can be mixed in the same file):

# Simple format
x-api-key: secret123
x-tenant: acme

# Curl config format
header = x-request-id: abc-123

# Curl -H flag format
-H "authorization: Bearer mytoken"
-H 'database: production'

Lines beginning with # and blank lines are ignored.

The headers file path can also be set in the config so it is always picked up without needing the flag each time:

[flightsql_client]
headers_file = "/home/user/.config/dft/headers.txt"

Precedence

When headers are specified in multiple places they are merged in this order (later sources win):

  1. Config file (flightsql_client.headers)
  2. Headers file (config headers_file or --headers-file)
  3. --header flags on the command line

Response headers (--print-headers)

To inspect the metadata returned by the FlightSQL server on each DoGet response, pass --print-headers. The response headers are printed before the query results and the response trailers (if the server sends any) after them:

dft -c "SELECT 1" --flightsql --print-headers
dft --print-headers flightsql statement-query --sql "SELECT 1"
Response headers:
  content-type: application/grpc
  date: ...
+----------+
| Int64(1) |
+----------+
| 1        |
+----------+
Response trailers:
  grpc-status: 0

Headers only (--headers-only)

For large results, --headers-only skips printing the results entirely and instead prints the headers, a summary of the response schema and total record count, and the trailers. It implies --print-headers and cannot be combined with --output, --json, or --bench:

dft -c "SELECT * FROM huge_table" --flightsql --headers-only
Response headers:
  content-type: application/grpc
Schema:
  x: Int64
  y: Utf8
Records: 1234567
Response trailers:
  grpc-status: 0

Auth

Basic Auth or Bearer Token can be set in your config, which is used by the client:

[flightsql_client.auth]
bearer_token = "MyToken"
basic_auth.username = "User"
basic_auth.password = "Pass"

Benchmark Queries

You can benchmark queries by adding the --bench parameter. This will run the query a configurable number of times and output a breakdown of the query's execution time with summary statistics for each component (logical planning, physical planning, execution time, and total time).

Benchmark Modes

Serial Benchmark (default): Measures query performance in isolation, running iterations one after another. This shows the pure query execution time without any contention or resource sharing overhead.

Concurrent Benchmark (--concurrent): Measures query performance under load by running iterations in parallel. This reveals:

  • Throughput (queries per second) with multiple concurrent clients
  • Resource contention and bottlenecks
  • Performance degradation under concurrent load

Concurrent mode uses adaptive concurrency: min(iterations, CPU cores) to avoid overwhelming the system.

Options

  • --bench: Enable benchmarking mode
  • --concurrent: Run iterations in parallel (for concurrent benchmarking)
  • -n <count>: Number of iterations (default: 10, configured in config file)
  • --run-before <query>: Run a setup query before benchmarking (useful for cache warming)
  • --save <file>: Save results to CSV file
  • --append: Append to existing results file instead of overwriting

Examples

# Serial benchmark (default)
dft -c "SELECT * FROM my_table" --bench

# Concurrent benchmark
dft -c "SELECT * FROM my_table" --bench --concurrent

# Custom iteration count
dft -c "SELECT ..." --bench -n 100

# Concurrent with custom iterations
dft -c "SELECT ..." --bench -n 100 --concurrent

# Save benchmark results to CSV
dft -c "SELECT ..." --bench --save results.csv

# Append results (compare serial vs concurrent)
dft -c "SELECT ..." --bench --save results.csv
dft -c "SELECT ..." --bench --concurrent --save results.csv --append

# Run a setup query before benchmarking
dft -c "SELECT ..." --bench --run-before="CREATE TEMP TABLE my_temp AS SELECT ..."

# FlightSQL benchmark (concurrent)
dft -c "SELECT ..." --bench --concurrent --flightsql

Output

Benchmark output includes:

  • Mode: serial or concurrent(N) where N is the concurrency level
  • Timing breakdown: Logical planning, physical planning, execution (min/max/mean/median)
  • Row counts: Validation that all runs returned the same number of rows
  • CSV format: Results include a concurrency_mode column for comparison

Note: Concurrent benchmarks typically show higher mean/median times due to resource contention - this is expected and reveals how the system performs under load.

Analyze Queries

The output from EXPLAIN ANALYZE provides a wealth of information on a queries execution - however, the amount of information and connecting the dots can be difficult and manual. Further, there is detail in the MetricSet's of the underlying ExecutionPlan's that is lost in the output.

To help with this the --analyze flag can used to generate a summary of the underlying ExecutionPlan MetricSets. The summary presents the information in a way that is hopefully easier to understand and easier to draw conclusions on a query's performance.

This feature is still in it's early stages and is expected to evolve. Once it has gone through enough real world testing and it has been confirmed the metrics make sense documentation will be added on the exact calculations - until then the source will need to be inspected to see the calculations.

dft -c "SELECT ..." --analyze

Generate TPC-H Data

Generate TPC-H data into your configured DB path

dft generate-tpch