TableService API

Package: dodil.k3.tables.v1

TableService owns table engine lifecycle, table CRUD/schema, SQL/data operations, and table maintenance.

What It Is For

Enable per-bucket table engine.
Create manual tables or pipeline-generated tables.
Execute SQL and DML-like operations.
Optimize, vacuum, restore, compact, and inspect table history.

Endpoint Map

Engine

gRPC method	HTTP route
`EnableEngine`	`POST /:bucket/tables/_engine`
`GetEngine`	`GET /:bucket/tables/_engine`
`DisableEngine`	`DELETE /:bucket/tables/_engine`

Tables and Schema

gRPC method	HTTP route
`CreateTable`	`POST /:bucket/tables`
`CreateTablePipeline`	`POST /:bucket/tables/pipelines`
`ListTables`	`GET /:bucket/tables`
`GetTable`	`GET /:bucket/tables/:table_name`
`DeleteTable`	`DELETE /:bucket/tables/:table_name`
`AlterTable`	`PATCH /:bucket/tables/:table_name/schema`
`DescribeTable`	`GET /:bucket/tables/:table_name/describe`
`ListPartitions`	`GET /:bucket/tables/:table_name/partitions`

Data and SQL

gRPC method	HTTP route
`Query`	`POST /:bucket/tables/_query`
`Execute`	`POST /:bucket/tables/_execute`
`Insert`	`POST /:bucket/tables/:table_name/insert`
`Merge`	`POST /:bucket/tables/:table_name/merge`
`Update`	`POST /:bucket/tables/:table_name/update`
`DeleteRows`	`POST /:bucket/tables/:table_name/delete-rows`

Maintenance and Materialization

gRPC method	HTTP route
`Optimize`	`POST /:bucket/tables/:table_name/optimize`
`Vacuum`	`POST /:bucket/tables/:table_name/vacuum`
`Restore`	`POST /:bucket/tables/:table_name/restore`
`History`	`GET /:bucket/tables/:table_name/history`
`Compact`	`POST /:bucket/tables/:table_name/_compact`
`Materialize`	`POST /:bucket/tables/:source_table/materialize`
`ListTemplates`	`GET /:bucket/tables/_templates`

Key Arguments

Create manual table

Field	Type	Required	Purpose
`bucket`	string	yes	Bucket scope
`name`	string	yes	Table name
`description`	string	no	Table description
`columns`	`Column[]`	yes	Column definitions
`partition_columns`	string[]	no	Partition keys
`merge_keys`	string[]	no	Keys for merge semantics

Column core fields:

name
type (STRING, INT, LONG, FLOAT, DOUBLE, BOOLEAN, DATE, TIMESTAMP, BINARY, JSON)
nullable
optional default_expression

Create pipeline-generated table

Field	Type	Required	Purpose
`bucket`	string	yes	Bucket scope
`name`	string	yes	Target table name
`template_id`	string	yes	Analytics/extraction template
`folder_prefix`	string	no	Scope auto-generated ingest rule

Query and execute

QueryRequest fields:

Field	Type	Required	Purpose
`bucket`	string	yes	Bucket scope
`table_name`	string	yes	Target table
`sql`	string	yes	SQL (supports `{table}` placeholder)
`limit`	int64	no	Max rows
`lookup_tables`	map<string,string>	no	Alias-to-table joins
`freshness`	enum	no	`EVENTUAL`, `STRONG`, `OLTP_ONLY`

ExecuteRequest fields:

bucket
sql
optional freshness for read plans

DuckDB SQL Dialect and Query Patterns

TableService SQL paths use a DuckDB-oriented SQL dialect for analytical reads.

Dialect profile

ANSI-style SQL core (SELECT, JOIN, GROUP BY, ORDER BY, CTEs, subqueries).
DuckDB analytical functions (window functions, percentile/quantile functions, rich date/time functions).
JSON-friendly querying through DuckDB JSON functions when columns store JSON payloads.
Fast analytical scans and aggregations through vectorized execution.

Recommended statement types

Query type	Example SQL	Why use it
Aggregate metrics	`SELECT kind, COUNT(*) AS cnt FROM {table} GROUP BY kind ORDER BY cnt DESC`	Fast rollups for dashboards and ops reporting
Time-bucket analysis	`SELECT date_trunc('hour', created_at) AS hour_bucket, COUNT(*) FROM {table} GROUP BY 1 ORDER BY 1`	Reliable time-series summaries without extra ETL
Window ranking/dedup	`SELECT id, kind, created_at, ROW_NUMBER() OVER (PARTITION BY kind ORDER BY created_at DESC) AS rn FROM {table}`	Ranking, latest-record extraction, and dedup patterns
JSON field extraction	`SELECT json_extract_string(payload, '$.customer_tier') AS tier, COUNT(*) FROM {table} GROUP BY tier`	Query nested attributes without flattening first
Percentile/SLO views	`SELECT quantile_cont(duration_ms, 0.95) AS p95_ms FROM {table}`	Useful for latency and quality SLO monitoring
Multi-table joins	`SELECT e.id, d.owner FROM {table} e LEFT JOIN dim_owners d ON e.owner_id = d.owner_id`	Enrich fact data with dimensions for analysis

Query endpoint usage guidance

Use Query when you want a rowset response.
Use Execute for statement execution flows where row materialization is not required, or when routing through the CLI query path.
For data mutation semantics, prefer dedicated APIs (Insert, Merge, Update, DeleteRows) instead of ad-hoc SQL mutations.

Insert/merge/update/delete

Operation	Key request fields
`Insert`	`rows` (JSON-encoded row strings), `mode` (`append`/`overwrite`)
`Merge`	`rows`, `match_columns`, `when_matched`, `when_not_matched`
`Update`	`updates` (protobuf struct map), `predicate`
`DeleteRows`	`predicate`

Maintenance

Operation	Key request fields
`Optimize`	`target_file_size_mb`, `z_order_columns`
`Vacuum`	`retention_hours`, `dry_run`, `disable_retention_check`
`Restore`	`version` or `restore_timestamp`
`History`	`limit`, `before_version`
`Compact`	`batch_size`

Examples

Enable table engine


curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/_engine" \
  -H "Authorization: Bearer $K3_TOKEN" \
  -H "x-organization-id: $K3_ORG" \
  -H "Content-Type: application/json" \
  -d '{"bucket":"kb-prod"}'

Create a manual table


curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables" \
  -H "Authorization: Bearer $K3_TOKEN" \
  -H "x-organization-id: $K3_ORG" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "name": "events",
    "columns": [
      {"name": "id", "type": "COLUMN_TYPE_LONG", "nullable": false},
      {"name": "kind", "type": "COLUMN_TYPE_STRING", "nullable": true},
      {"name": "created_at", "type": "COLUMN_TYPE_TIMESTAMP", "nullable": false}
    ],
    "partition_columns": ["kind"],
    "merge_keys": ["id"]
  }'

Insert rows


curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/events/insert" \
  -H "Authorization: Bearer $K3_TOKEN" \
  -H "x-organization-id: $K3_ORG" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "table_name": "events",
    "rows": [
      "{\"id\":1,\"kind\":\"deploy\",\"created_at\":\"2026-05-14T09:00:00Z\"}",
      "{\"id\":2,\"kind\":\"incident\",\"created_at\":\"2026-05-14T09:05:00Z\"}"
    ],
    "mode": "append"
  }'

Run SQL query


curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/_query" \
  -H "Authorization: Bearer $K3_TOKEN" \
  -H "x-organization-id: $K3_ORG" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "table_name": "events",
    "sql": "SELECT kind, COUNT(*) AS cnt FROM {table} GROUP BY kind",
    "freshness": "FRESHNESS_EVENTUAL"
  }'

Create a pipeline-generated table


curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/pipelines" \
  -H "Authorization: Bearer $K3_TOKEN" \
  -H "x-organization-id: $K3_ORG" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "name": "lease_extracts",
    "template_id": "lease_extraction",
    "folder_prefix": "leases"
  }'

Run a compaction tick


curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/events/_compact" \
  -H "Authorization: Bearer $K3_TOKEN" \
  -H "x-organization-id: $K3_ORG" \
  -H "Content-Type: application/json" \
  -d '{"bucket":"kb-prod","table_name":"events","batch_size":10000}'

Common Use Cases

Build analytics tables directly on object-derived data.
Maintain mixed OLTP/OLAP workloads with freshness-aware reads.
Run operator maintenance jobs (optimize, vacuum, _compact) for predictable performance.

Back to API index