Table DDL

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  DELETED_RETENTION_HOURS = <hours>,12  TTL_SECONDS = <seconds>,13  ACCESS_LEVEL = '<PUBLIC|PRIVATE|RESTRICTED>'14)];

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  FLUSH_POLICY = 'rows:1000,interval:60'11);

1CREATE SHARED TABLE app.config (2  key TEXT PRIMARY KEY,3  value TEXT NOT NULL,4  updated_at TIMESTAMP DEFAULT NOW()5) WITH (6  ACCESS_LEVEL = 'PUBLIC'7);

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 = 307);

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 (ACCESS_LEVEL = '<PUBLIC|PRIVATE|RESTRICTED>');16 17-- Create a vector index on an embedding column18ALTER TABLE [<namespace>.]<table_name>19  CREATE INDEX <embedding_column> USING COSINE;

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;

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

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>;

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

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;

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

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

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