Skip to Content
We are live but in Staging 🎉
Data EnginesSQLCLI GuideOverview

Tables — CLI Guide

Three dodil data entry points cover the Tables domain:

  • dodil data sql — run any SQL statement against a bucket’s tables. The one you’ll use most.
  • dodil data pg — the same SQL, sent over the Postgres wire instead of the gRPC door. Useful for checking adapter behaviour.
  • dodil data table — typed subcommands for lifecycle, row mutations and maintenance, for callers who’d rather not template SQL strings.

Every one of them ends at the same place: dodil.tables.v1.Tables/Execute on the tables-gateway. The typed table subcommands build a SQL statement and send it — there is no separate control-plane table API behind them.

dodil data sql -b kb-prod "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type"
dodil data pg  -b kb-prod "SELECT 1"          # same SQL, Postgres wire

For navigation, this guide splits the table subcommands into three pages by concern:

PageSubcommandsReference
dodil data table — lifecyclecreate · list · get · describe · delete · templates · pipeline · view-urlTables
dodil data table — dataquery · insert · upsert · merge · update · delete-rowsData + Execute
dodil data table — maintenanceoptimize · vacuum · compactMaintenance

dodil data engine is retired. Engines are implicit per bucket; capacity is managed with dodil data reservation. Running the old command prints a deprecation notice. See Capacity for reservations.

For install, auth, output format and global flags see CLI Basics. For the underlying contracts see Tables — API Reference.

Quick session

# 0. Nothing to enable — tables are implicit per bucket.
 
# 1. Create a table
dodil data table create events -b kb-prod \
  --columns-json '[
    {"name":"id",         "type":"bigint",  "nullable":false},
    {"name":"user_id",    "type":"varchar", "nullable":false},
    {"name":"event_type", "type":"varchar", "nullable":false},
    {"name":"payload",    "type":"json",    "nullable":true}
  ]' \
  --partition-column event_type \
  --merge-key id --merge-key user_id
 
# 2. Insert — one --row per row
dodil data table insert events -b kb-prod \
  --row '{"id":1,"user_id":"u-101","event_type":"click","payload":{"page":"/pricing"}}' \
  --row '{"id":2,"user_id":"u-102","event_type":"purchase","payload":{"sku":"A-12"}}'
 
# 3. Query — plain SQL is usually the shortest path
dodil data sql -b kb-prod \
  "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type"
 
# 4. Upsert by primary key
dodil data table upsert events -b kb-prod \
  --row '{"id":1,"user_id":"u-101","event_type":"click_pricing","payload":{"page":"/pricing","variant":"B"}}'
 
# 5. Drain the WAL to Delta, then bin-pack
dodil data table compact events -b kb-prod
dodil data table optimize events -b kb-prod

See the Quickstart for the same flow in SQL with prose annotations.

CLI vs API — where they differ

The CLI covers the common operations. Where it doesn’t, the answer is almost always “write the SQL” rather than “call a different API”.

AreaCLI coverageGap — and what to use instead
Ad-hoc SQLdodil data sql · dodil data pg · dodil data table query— complete. Every statement shape Execute accepts.
Table lifecyclecreate · list · get · describe · deleteALTER TABLE, TRUNCATE, CREATE INDEX, CTAS have no typed subcommand — run the SQL.
Row mutationsinsert · upsert · merge · update · delete-rowsINSERT … SELECT, joins and subqueries in a predicate — run the SQL.
Streaming writes—WriteStream (bulk/CDC channel) and Commit (atomic multi-row) are gRPC-only. See Data → Upsert.
Point reads—GetRow / BatchGetRows have no CLI form; use SELECT … WHERE pk = … or the RPC.
Maintenanceoptimize · vacuum · compactRESTORE TABLE t TO VERSION AS OF n is SQL — run it through dodil data sql.
Time travel—SELECT … VERSION AS OF n via dodil data sql. There is no History RPC or command; read the Delta log.
Templatestable templates · table pipeline create— complete for the warehouse-compatible catalog.
Freshness—Nothing to set. Reads are read-your-writes; --freshness is a deprecated no-op.

Global flags worth knowing

--bucket / -b is persistent on the dodil data table group and required on dodil data sql / pg.

FlagShortDescription
--bucket-bBucket the tables live in — it is also the database name
--output-otable (default) · json · yaml — pick json for piping into jq
--via—Data-plane transport: direct (default) · pg · bolt · http. For SQL only direct and pg are accepted.
--data-endpoint—Tables-gateway endpoint (default table-rpc.uk-lon-1.dodil.io:443)

For the full set (--api-endpoint, --token, --org, --timeout, …) see CLI Basics → Global Flags.

See also

Templatesdodil data table