Table and Engine Commands

This domain covers table engine controls, table lifecycle, SQL, data mutation, and maintenance commands.

Engine Commands

Command group: dodil k3 engine

Persistent flag:

--bucket, -b

Subcommand	Args	Purpose
`enable`	none	Enable table engine
`get`	none	Get engine details
`disable`	none	Disable table engine

Table Commands

Command group: dodil k3 table

Persistent flag:

--bucket, -b

Lifecycle and read commands

Subcommand	Args	Important flags
`create`	`<name>`	`--description`, `--columns-json`, `--partition-column`, `--merge-key`, `--source`, `--pipeline-template-id`
`list`	none	`--bucket`
`get`	`<name>`	`--bucket`
`describe`	`<name>`	`--bucket`
`delete`	`<name>`	`--bucket`
`query`	`<sql>`	`--bucket`

Data mutation commands

Subcommand	Args	Important flags
`insert`	`<name>`	`--row` (repeatable), `--mode`
`merge`	`<name>`	`--row` (repeatable), `--match-column`, `--when-matched`, `--when-not-matched`
`update`	`<name>`	`--predicate`, `--updates-json`
`delete-rows`	`<name>`	`--predicate`

Maintenance commands

Subcommand	Args	Important flags
`optimize`	`<name>`	`--target-file-size-mb`, `--z-order-column`
`vacuum`	`<name>`	`--retention-hours`, `--dry-run`, `--disable-retention-check`
`compact`	`<name>`	`--batch-size`

Key Flag Guidance

--columns-json: JSON array of column descriptors.
--row: JSON object per row; pass multiple --row flags for batch writes.
--updates-json: JSON object mapping column to new literal value.
--predicate: SQL-like predicate for update/delete row selection.
--source: table source marker (manual or pipeline_generated).

DuckDB SQL via table query

table query uses the TableService SQL path backed by DuckDB-style SQL semantics.

High-value query patterns

Pattern	Example	Advantage
Aggregation	`SELECT kind, COUNT(*) FROM events GROUP BY kind`	Quick operational metrics and dashboard rollups
Time buckets	`SELECT date_trunc('day', created_at), COUNT(*) FROM events GROUP BY 1`	Simple trend analysis by time window
Window analytics	`SELECT *, ROW_NUMBER() OVER (PARTITION BY kind ORDER BY created_at DESC) rn FROM events`	Latest-per-group and ranking scenarios
Percentiles	`SELECT quantile_cont(duration_ms, 0.95) AS p95 FROM events`	SLO and latency analysis
JSON extraction	`SELECT json_extract_string(payload, '$.type') AS type, COUNT(*) FROM events GROUP BY 1`	Analyze nested JSON without pre-flattening

Mutation guidance:

Keep table query focused on read/analytic SQL.
Use dedicated commands (insert, merge, update, delete-rows) for controlled writes.

Examples


# Enable engine
dodil k3 engine enable --bucket kb-dev
 
# Create table
dodil k3 table create events --bucket kb-dev \
  --columns-json '[{"name":"id","type":3,"nullable":false},{"name":"kind","type":1,"nullable":true}]' \
  --merge-key id
 
# Insert rows
dodil k3 table insert events --bucket kb-dev \
  --row '{"id":1,"kind":"deploy"}' \
  --row '{"id":2,"kind":"incident"}'
 
# Query
dodil k3 table query "SELECT kind, COUNT(*) FROM events GROUP BY kind" --bucket kb-dev
 
# Query with DuckDB window function
dodil k3 table query \
  "SELECT id, kind, ROW_NUMBER() OVER (PARTITION BY kind ORDER BY id DESC) AS rn FROM events" \
  --bucket kb-dev
 
# Update and delete
dodil k3 table update events --bucket kb-dev \
  --predicate "id = 2" \
  --updates-json '{"kind":"incident-critical"}'
 
dodil k3 table delete-rows events --bucket kb-dev --predicate "id = 1"
 
# Maintenance
dodil k3 table optimize events --bucket kb-dev --z-order-column kind
dodil k3 table vacuum events --bucket kb-dev --dry-run
dodil k3 table compact events --bucket kb-dev --batch-size 20000

Important Notes

CLI does not provide a dedicated CreateTablePipeline command yet.
For pipeline-generated tables, use direct API POST /:bucket/tables/pipelines.
table query is implemented via Execute path in current CLI code.

Next: Mount Commands