Table DDL

KalamDB supports three application table types: USER, SHARED, and STREAM. For the full architecture, access matrices, storage behavior, and when to choose each type, see /docs/server/architecture/table-types.

CREATE TABLE

Syntax

SQL

1CREATE [USER|SHARED|STREAM] TABLE [IF NOT EXISTS] [<namespace>.]<table_name> (2  <column_name> <data_type> [NOT NULL|NULL] [DEFAULT <expr>] [PRIMARY KEY],3  ...,4  [CONSTRAINT <name> PRIMARY KEY (<column_name>)]5)6[WITH (7  TYPE = '<USER|SHARED|STREAM>',8  STORAGE_ID = '<storage_id>',9  USE_USER_STORAGE = <TRUE|FALSE>,10  FLUSH_POLICY = '<rows:N|interval:N|rows:N,interval:N>',11  TTL_SECONDS = <seconds>,12  EVICTION_STRATEGY = '<time_based|size_based|hybrid>',13  MAX_STREAM_SIZE_BYTES = <bytes>,14  COMPRESSION = '<none|snappy|zstd>'15)];

Table options are type-specific:

USER: STORAGE_ID, USE_USER_STORAGE, FLUSH_POLICY, COMPRESSION
SHARED: STORAGE_ID, FLUSH_POLICY, COMPRESSION
STREAM: TTL_SECONDS, EVICTION_STRATEGY, MAX_STREAM_SIZE_BYTES

Shared tables always use FORCE row-level security. Creating a shared table without CREATE POLICY is default-deny for user and service (zero rows on SELECT; writes fail). system and dba bypass RLS. ACCESS_LEVEL is not a table option.

For storage-specific options such as STORAGE_ID and USE_USER_STORAGE, see /docs/server/sql-reference/storage-id-usage. For flush behavior, see /docs/server/architecture/storage-tiers.

COMPRESSION accepts only none, snappy, and zstd, and is valid only for USER and SHARED tables. It controls the Apache Parquet codec used when table data is flushed or compacted into cold-storage segments. none writes uncompressed Parquet pages, snappy is the default and optimizes for low CPU overhead, and zstd uses Zstandard level 1 for better compression density with modest extra CPU. This option is not WebSocket gzip and does not change RocksDB hot-tier compression. STREAM tables use hot stream log storage and do not accept table Parquet compression.

Examples

User table (per-user isolated):

SQL

1CREATE TABLE app.messages (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  conversation_id BIGINT NOT NULL,4  sender TEXT NOT NULL,5  role TEXT NOT NULL DEFAULT 'user',6  content TEXT NOT NULL,7  created_at TIMESTAMP NOT NULL DEFAULT NOW()8) WITH (9  TYPE = 'USER',10  STORAGE_ID = 'local',11  USE_USER_STORAGE = false,12  FLUSH_POLICY = 'rows:1000,interval:60',13  COMPRESSION = 'snappy'14);

Shared table (global rows, FORCE RLS):

SQL

1CREATE SHARED TABLE app.config (2  key TEXT PRIMARY KEY,3  value TEXT NOT NULL,4  updated_at TIMESTAMP DEFAULT NOW()5) WITH (6  COMPRESSION = 'zstd'7);8 9CREATE POLICY public_config_read ON app.config10  FOR SELECT TO PUBLIC11  USING (true);

Stream table (ephemeral with TTL):

SQL

1CREATE STREAM TABLE app.events (2  event_id TEXT PRIMARY KEY,3  payload TEXT,4  created_at TIMESTAMP DEFAULT NOW()5) WITH (6  TTL_SECONDS = 30,7  EVICTION_STRATEGY = 'hybrid',8  MAX_STREAM_SIZE_BYTES = 10485769);

ALTER TABLE

SQL

1-- Add a column2ALTER TABLE [<namespace>.]<table_name>3  ADD COLUMN <name> <type> [NOT NULL|NULL] [DEFAULT <value>];4 5-- Drop a column6ALTER TABLE [<namespace>.]<table_name>7  DROP COLUMN <name>;8 9-- Modify a column type10ALTER TABLE [<namespace>.]<table_name>11  MODIFY COLUMN <name> <type> [NOT NULL|NULL];12 13-- Change table properties14ALTER TABLE [<namespace>.]<table_name>15  SET TBLPROPERTIES (<table_option> = <value>, ...);16 17-- Create a vector index on an embedding column18ALTER TABLE [<namespace>.]<table_name>19  CREATE INDEX <embedding_column> USING COSINE;

SET TBLPROPERTIES supports the same type-specific persisted options as CREATE TABLE. Use FLUSH_POLICY = NULL to clear a user/shared flush policy.

Examples:

SQL

1ALTER TABLE app.config2  SET TBLPROPERTIES (COMPRESSION = 'zstd');3 4ALTER TABLE app.messages5  SET TBLPROPERTIES (FLUSH_POLICY = 'rows:5000', USE_USER_STORAGE = true);6 7ALTER TABLE app.events8  SET TBLPROPERTIES (9    TTL_SECONDS = 3600,10    EVICTION_STRATEGY = 'size_based',11    MAX_STREAM_SIZE_BYTES = 104857612  );

Vector index example

SQL

1CREATE TABLE rag.documents_vectors (2  id BIGINT PRIMARY KEY,3  embedding EMBEDDING(384)4) WITH (TYPE = 'USER');5 6ALTER TABLE rag.documents_vectors7  CREATE INDEX embedding USING COSINE;

After the index exists, rank nearest rows with:

SQL

1SELECT id2FROM rag.documents_vectors3ORDER BY COSINE_DISTANCE(embedding, '[0.12,0.03,0.98]')4LIMIT 10;

DROP TABLE

SQL

1DROP TABLE [IF EXISTS] [<namespace>.]<table_name>;2DROP USER TABLE [IF EXISTS] [<namespace>.]<table_name>;3DROP SHARED TABLE [IF EXISTS] [<namespace>.]<table_name>;4DROP STREAM TABLE [IF EXISTS] [<namespace>.]<table_name>;

Grant shared-table rows with CREATE POLICY, not table options.

CREATE VIEW

SQL

1CREATE VIEW [<namespace>.]<view_name> AS <select_query>;2CREATE VIEW [<namespace>.]<view_name> (<column1>, <column2>, ...) AS <select_query>;

Example:

SQL

1CREATE VIEW chat.recent_messages AS2  SELECT id, sender, content, created_at3  FROM chat.messages4  WHERE created_at > NOW() - INTERVAL '1 hour'5  ORDER BY created_at DESC;

SHOW TABLES

SQL

1SHOW TABLES;2SHOW TABLES IN <namespace>;3SHOW TABLES IN NAMESPACE <namespace>;

DESCRIBE TABLE

SQL

1DESCRIBE TABLE [<namespace>.]<table_name>;2DESC TABLE [<namespace>.]<table_name>;3 4-- Show table history5DESCRIBE TABLE [<namespace>.]<table_name> HISTORY;

Returns full schema-registry metadata (column_id, is_primary_key, schema_version, Kalam data_type names, and more).

For the DataFusion-style shorthand (DESCRIBE namespace.table), CLI \d behavior, and EXPLAIN / EXPLAIN ANALYZE VERBOSE plan inspection, see /docs/server/sql-reference/diagnostics.

SHOW STATS

SQL

1SHOW STATS FOR TABLE [<namespace>.]<table_name>;