dodil data table — data

Read and mutate rows. Six subcommands. All of them build a SQL statement and send it through dodil.tables.v1.Tables/Execute — the typed shortcuts exist so you don’t have to template SQL strings, not because they reach a different API.

Persistent flag on the group: --bucket / -b.

For ad-hoc SQL, dodil data sql -b BUCKET "<sql>" is shorter than dodil data table query, and dodil data pg sends the same statement over the Postgres wire. Both are documented in the CLI Guide overview.

dodil data table query

dodil data table query [sql] -b BUCKET

Runs SQL. The statement is a positional argument — wrap it in quotes. Handles reads and writes alike.

# Aggregate
dodil data table query \
  "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type ORDER BY n DESC" \
  -b kb-prod
 
# JSON column extraction
dodil data table query \
  "SELECT user_id, payload->>'sku' AS sku FROM events WHERE event_type = 'purchase'" \
  -b kb-prod -o json
 
# Window function
dodil data table query \
  "SELECT id, event_type, ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY id DESC) AS rn FROM events" \
  -b kb-prod

--freshness is a deprecated no-op. The flag still parses, but running it prints Flag --freshness has been deprecated, reads are read-your-writes by default; this flag is ignored and the query runs unchanged. There is nothing to set: every read sees every committed write. How the engine decides which path serves a read is on How it works.

Large results spool. Above a size threshold the result is written to object storage and returned as a manifest of presigned GETs instead of inline rows. You’ll see:

(result spooled to object storage: N rows across M part(s); re-run with a LIMIT or fetch the presigned parts)

Add a LIMIT, or fetch the parts. See Execute → Spooled results.

High-value query patterns

Aggregates over a large partitioned table are fanned out across partitions automatically; joins and window functions run on one executor. See Performance & layout.

dodil data table insert

dodil data table insert [name] -b BUCKET --row JSON [--row JSON ...] [--mode MODE]

dodil data table insert events -b kb-prod \
  --row '{"id":1,"user_id":"u-101","occurred_at":1779876000000000,"event_type":"click","payload":{"page":"/pricing"}}' \
  --row '{"id":2,"user_id":"u-101","occurred_at":1779876060000000,"event_type":"click","payload":{"page":"/signup"}}'

There is no --rows flag taking a JSON array. It is --row (or -r), once per row.

Each --row becomes one INSERT statement, so a row whose primary key already exists fails that statement with SQLSTATE 23505; use upsert when you mean replace. --mode overwrite emits INSERT OR REPLACE INTO, whose OR REPLACE modifier is inert — it still fails on an existing key.

INSERT … SELECT has no flag form — run it as SQL.

dodil data table upsert

dodil data table upsert [name] -b BUCKET --row JSON [--row JSON ...] \
  [--match-column COL ...] [--merge]

The typed keyed write — insert-or-update by key. This is the CLI’s form of the Upsert RPC.

# Full-row upsert on the table's PK
dodil data table upsert events -b kb-prod \
  --row '{"id":1,"user_id":"u-101","occurred_at":1779876000000000,"event_type":"click_pricing","payload":{"page":"/pricing"}}'
 
# Partial merge — only event_type changes; payload and occurred_at are left alone
dodil data table upsert events -b kb-prod --merge \
  --row '{"id":1,"user_id":"u-101","event_type":"click_pricing"}'

--merge is the flag that decides whether an absent column means “set it to null” or “leave it”. Without it, an upsert is a full-row replace.

dodil data table merge

dodil data table merge [name] -b BUCKET \
  --row JSON [--row JSON ...] \
  --match-column COL [--match-column COL ...] \
  [--when-matched ACTION] [--when-not-matched ACTION]

General MERGE with explicit arms — use this when you need delete-on-match or skip semantics. For a plain upsert, table upsert is simpler.

# Upsert by composite key
dodil data table merge events -b kb-prod \
  --match-column id --match-column user_id \
  --row '{"id":1,"user_id":"u-101","event_type":"click_pricing","payload":{"page":"/pricing","variant":"B"}}' \
  --row '{"id":4,"user_id":"u-103","event_type":"signup","payload":{"plan":"pro"}}'
 
# Delete-on-match (cleanup pattern)
dodil data table merge events -b kb-prod \
  --match-column id --match-column user_id \
  --when-matched delete --when-not-matched skip \
  --row '{"id":5,"user_id":"u-104"}'

The no-match arm is skip, not ignore. The matched arm accepts skip too.

The returned row count is the number of rows the statement wrote, inserts and updates together. The insert-versus-update split is not reported.

dodil data table update

dodil data table update [name] -b BUCKET \
  --predicate "SQL_WHERE_CLAUSE" \
  --updates-json '{ "col": "value", ... }'

UPDATE events SET event_type = 'click_pricing'
WHERE id = 1 AND user_id = 'u-101';
 
-- A non-PK predicate is fine — see below.
UPDATE events SET event_type = 'archived'
WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';

dodil data table update events -b kb-prod \
  --predicate "id = 1 AND user_id = 'u-101'" \
  --updates-json '{"event_type":"click_pricing"}'
 
dodil data table update events -b kb-prod \
  --predicate "occurred_at < TIMESTAMP '2025-01-01 00:00:00'" \
  --updates-json '{"event_type":"archived"}'

A WHERE clause is mandatory. UPDATE and DELETE without one are refused upfront with a BigQuery-style error. To clear a table, use TRUNCATE TABLE.

A predicate on non-key columns is fine: the engine resolves the matching keys first and then writes each one, so the update is safe under concurrent writes exactly like a key-addressed one. It costs one extra read proportional to the predicate’s selectivity.

Wire-shape gotcha: --updates-json is a flat { col: value } map. Do not wrap it in a "fields" key — {"fields":{…}} parses as a single-column SET clause and the update is a no-op.

dodil data table delete-rows

dodil data table delete-rows [name] -b BUCKET --predicate "SQL_WHERE_CLAUSE"

dodil data table delete-rows events -b kb-prod \
  --predicate "id = 1 AND user_id = 'u-101'"
 
dodil data table delete-rows events -b kb-prod \
  --predicate "occurred_at < TIMESTAMP '2025-01-01 00:00:00'"

Same rules as update: a WHERE clause is mandatory, and a non-key predicate resolves the matching keys first.

Deleted rows still occupy storage until optimize rewrites the files and vacuum expires the old versions — see maintenance.