Table DDL

KalamDB supports three table types: USER, SHARED, and STREAM. See Table Types for detailed descriptions.

CREATE TABLE

Syntax


CREATE [USER|SHARED|STREAM] TABLE [IF NOT EXISTS] [<namespace>.]<table_name> (
  <column_name> <data_type> [NOT NULL|NULL] [DEFAULT <expr>] [PRIMARY KEY],
  ...,
  [CONSTRAINT <name> PRIMARY KEY (<column_name>)]
)
[WITH (
  TYPE = '<USER|SHARED|STREAM>',
  STORAGE_ID = '<storage_id>',
  USE_USER_STORAGE = <TRUE|FALSE>,
  FLUSH_POLICY = '<rows:N|interval:N|rows:N,interval:N>',
  DELETED_RETENTION_HOURS = <hours>,
  TTL_SECONDS = <seconds>,
  ACCESS_LEVEL = '<PUBLIC|PRIVATE|RESTRICTED>'
)];

WITH Options

Option	Applies To	Description
`TYPE`	All	Table type when not using prefix syntax
`STORAGE_ID`	All	Target storage backend
`USE_USER_STORAGE`	USER	Use per-user storage directory
`FLUSH_POLICY`	USER, SHARED	When to flush hot → cold tier
`DELETED_RETENTION_HOURS`	All	Soft-delete retention period
`TTL_SECONDS`	STREAM	Auto-eviction time for stream data
`ACCESS_LEVEL`	SHARED	PUBLIC, PRIVATE, or RESTRICTED

Examples

User table (per-user isolated):


CREATE TABLE app.messages (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  conversation_id BIGINT NOT NULL,
  sender TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user',
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
) WITH (
  TYPE = 'USER',
  FLUSH_POLICY = 'rows:1000,interval:60'
);

Shared table (global access):


CREATE SHARED TABLE app.config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW()
) WITH (
  ACCESS_LEVEL = 'PUBLIC'
);

Stream table (ephemeral with TTL):


CREATE STREAM TABLE app.events (
  event_id TEXT PRIMARY KEY,
  payload TEXT,
  created_at TIMESTAMP DEFAULT NOW()
) WITH (
  TTL_SECONDS = 30
);

ALTER TABLE


-- Add a column
ALTER TABLE [<namespace>.]<table_name>
  ADD COLUMN <name> <type> [NOT NULL|NULL] [DEFAULT <value>];
 
-- Drop a column
ALTER TABLE [<namespace>.]<table_name>
  DROP COLUMN <name>;
 
-- Modify a column type
ALTER TABLE [<namespace>.]<table_name>
  MODIFY COLUMN <name> <type> [NOT NULL|NULL];
 
-- Change table properties
ALTER TABLE [<namespace>.]<table_name>
  SET TBLPROPERTIES (ACCESS_LEVEL = '<PUBLIC|PRIVATE|RESTRICTED>');

DROP TABLE


DROP TABLE [IF EXISTS] [<namespace>.]<table_name>;
DROP USER TABLE [IF EXISTS] [<namespace>.]<table_name>;
DROP SHARED TABLE [IF EXISTS] [<namespace>.]<table_name>;
DROP STREAM TABLE [IF EXISTS] [<namespace>.]<table_name>;

CREATE VIEW


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

Example:


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

SHOW TABLES


SHOW TABLES;
SHOW TABLES IN <namespace>;
SHOW TABLES IN NAMESPACE <namespace>;

DESCRIBE TABLE


DESCRIBE TABLE [<namespace>.]<table_name>;
DESC TABLE [<namespace>.]<table_name>;
 
-- Show table history
DESCRIBE TABLE [<namespace>.]<table_name> HISTORY;

SHOW STATS


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