SQL DDL

CREATE, DROP, INDEX, COMPACT, ALTER

CREATE TABLE

CREATE TABLE articles USING text
CREATE TABLE papers (id uuid, title text, embedding vector[4]) USING hybrid

CREATE INDEX

CREATE INDEX text_idx ON articles (text) USING BM25
CREATE INDEX ann_idx ON papers (embedding) USING HNSW
CREATE INDEX disk_idx ON ann_corpus (embedding) USING DISKANN

Common USING values: BM25, HNSW, DISKANN.

DROP TABLE

DROP TABLE logs

DESCRIBE

DESCRIBE articles

Returns row count, vector dimension, segment count, segment workers, and index sidecars.

COMPACT TABLE

COMPACT TABLE articles
COMPACT TABLE articles FULL

Merges small Parquet segments. FULL forces a more aggressive merge policy.

ALTER segment workers

ALTER TABLE articles SET SEGMENT_WORKERS = 4

ALTER column type

Assign or change a metadata column type on an existing table:

ALTER TABLE articles ALTER COLUMN year TYPE int
ALTER TABLE papers ALTER COLUMN embedding TYPE vector(384)
ALTER TABLE articles ALTER COLUMN year TYPE int REWRITE

REWRITE chains COMPACT TABLE … FULL after updating the manifest. Without it, run compact manually when segments still use the legacy four-column layout.

See Typed metadata columns for supported types, compound WHERE, and filter behavior.

WITH + JOIN

CTEs can be combined with metadata joins:

WITH cited AS (SELECT id, paper_id FROM papers)
SELECT id FROM cited
JOIN citations ON cited.paper_id = citations.paper_id
SPARSE SEARCH body BM25('query') LIMIT 10

SHOW TABLES

SHOW TABLES

Returns table names with row counts (analytics shape in Python).