Data Definition (DDL)

<column name> <column type> [(<column size / property list>)] [[NOT] NULL]

id INTEGER(SHARD_KEY)            -- id is an integer and the table's shard key
name VARCHAR(64, TEXT_SEARCH)    -- name is a 64-byte limited string and text-searchable
ip IPV4                          -- ip is a string in IPv4 format
cost DECIMAL(10, 2)              -- cost is able to hold an 8.2 decimal

CREATE SCHEMA [IF NOT EXISTS] <schema name>

CREATE SCHEMA example_container

ALTER SCHEMA <schema name>
RENAME TO <new schema name>

ALTER SCHEMA example_container
RENAME TO example_container_renamed

DROP SCHEMA [IF EXISTS] <schema name> [CASCADE]

DROP SCHEMA example_container CASCADE

SHOW [CREATE] SCHEMA < <schema name> | * >

SHOW CREATE SCHEMA example_container

| CREATE SCHEMA "example_container";   |

DESC[RIBE] [SCHEMA] <schema name>

DESC demo

+------------+
| Set_name   |
+------------+
| stocks     |
| nyctaxi    |
| flights    |
+------------+

SET CURRENT SCHEMA [<schema name>]

CREATE [OR REPLACE] [REPLICATED] [TEMP] TABLE [IF NOT EXISTS] [<schema name>.]<table name>
(
    <column name> <column definition> [COMMENT '<column comment>'],
    ...
    <column name> <column definition> [COMMENT '<column comment>'],
    [[SOFT] PRIMARY KEY (<column list>)],
    [SHARD KEY (<column list>)],
    [FOREIGN KEY
        (<column list>) REFERENCES <foreign table name>(<foreign column list>) [AS <foreign key name>],
        ...
        (<column list>) REFERENCES <foreign table name>(<foreign column list>) [AS <foreign key name>]
    ]
)
[<partition clause>]
[<tier strategy clause>]
[<index clause>]
[<table property clause>]

CREATE TABLE example.various_types
(
    i    INTEGER NOT NULL                                 COMMENT 'non-nullable integer, part of primary key (defined at end)',
    bi   BIGINT NOT NULL                                  COMMENT 'long, part of primary key, shard key, foreign key source (defined at end)',
    b    BOOLEAN                                          COMMENT '0s and 1s only',
    ub   UNSIGNED BIGINT                                  COMMENT 'native unsigned long',
    r    REAL                                             COMMENT 'native float',
    d    DOUBLE                                           COMMENT 'native double',
    s    VARCHAR(TEXT_SEARCH)                             COMMENT 'string, searchable, only limited in size by system-configured value',
    c    VARCHAR(30, DICT)                                COMMENT 'char32 using dictionary-encoding of values',
    p    VARCHAR(256, TEXT_SEARCH)                        COMMENT 'char256, searchable',
    ip   IPV4                                             COMMENT 'IP address',
    ui   UUID(INIT_WITH_UUID)                             COMMENT 'UUID',
    ts   TIMESTAMP                                        COMMENT 'timestamp',
    td   DATE                                             COMMENT 'simple date',
    tt   TIME                                             COMMENT 'simple time',
    dt   DATETIME(INIT_WITH_NOW)                          COMMENT 'date/time',
    dc   DECIMAL                                          COMMENT 'integer of up to 27 digits',
    dc8  DECIMAL(18, 4)                                   COMMENT '8-byte decimal',
    dc12 DECIMAL(27, 18)                                  COMMENT '12-byte decimal',
    n    NUMERIC(18, 4)                                   COMMENT 'alias for DECIMAL(18, 4)',
    byt  BYTES                                            COMMENT 'BLOB',
    w    WKT                                              COMMENT 'geospatial column for WKT string data',
    j    JSON                                             COMMENT 'JSON string',
    v    VECTOR(10)                                       COMMENT 'vector column holding 10 floating point values',
    ai   INTEGER[3]                                       COMMENT 'array column holding 3 integer values',
    PRIMARY KEY (i, bi),                                  /* primary key columns must be NOT NULL                                               */
    SHARD KEY (bi),                                       /* shard key columns must be part of the primary key                                  */
    FOREIGN KEY (bi) REFERENCES example.lookup(id) AS fk  /* foreign key is often on the shard key                                              */
)
INDEX (ip)                                                /* index on IP column                                                                 */
INDEX (ts)                                                /* index on timestamp column                                                          */

PARTITION BY RANGE ( <column expression> )
[
    PARTITIONS
    (
        <partition name> [ MIN ( <least value> ) ] [ MAX ( <greatest value> ) ],
        ...
        <partition name> [ MIN ( <least value> ) ] [ MAX ( <greatest value> ) ]
    )
]

CREATE TABLE example.customer_order_range_partition_by_year
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY RANGE (YEAR(purchase_ts))
PARTITIONS
(
    order_2014_2016 MIN(2014) MAX(2017),
    order_2017                MAX(2018),
    order_2018                MAX(2019),
    order_2019                MAX(2020)
)

PARTITION BY INTERVAL ( <column expression> )
PARTITIONS
(
    STARTING [AT] (<least value>) INTERVAL (<interval size>)
)

CREATE TABLE example.customer_order_interval_partition_by_year
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP
)
PARTITION BY INTERVAL (YEAR(purchase_ts))
PARTITIONS
(
    STARTING AT (2014) INTERVAL (1)
)

CREATE TABLE example.customer_order_interval_partition_by_day_timestampdiff
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP
)
PARTITION BY INTERVAL (TIMESTAMPDIFF(DAY, '2014-01-01', purchase_ts))
PARTITIONS
(
    STARTING AT (0) INTERVAL (1)
)

CREATE TABLE example.customer_order_interval_partition_by_day_interval
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP
)
PARTITION BY INTERVAL (purchase_ts)
PARTITIONS
(
    STARTING AT ('2014-01-01') INTERVAL (INTERVAL '1' DAY)
)

CREATE TABLE example.customer_order_interval_partition_by_hour_interval
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP
)
PARTITION BY INTERVAL (purchase_ts)
PARTITIONS
(
    STARTING AT ('2014-01-01') INTERVAL (INTERVAL '1' HOUR)
)

PARTITION BY LIST ( <column expression list> )
[
    PARTITIONS
    (
        <partition name> VALUES ( <value lists> ),
        ...
        <partition name> VALUES ( <value lists> )
    )
]

CREATE TABLE example.customer_order_manual_list_partition_by_year
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY LIST (YEAR(purchase_ts))
PARTITIONS
(
    order_2014_2016 VALUES (2014, 2015, 2016),
    order_2017      VALUES (2017),
    order_2018      VALUES (2018),
    order_2019      VALUES (2019)
)

CREATE TABLE example.customer_order_manual_list_partition_by_year_and_month
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY LIST (YEAR(purchase_ts), MONTH(purchase_ts))
PARTITIONS
(
    order_2016_0203 VALUES ((2016, 2), (2016, 3)),
    order_2020_03   VALUES ((2020, 3))
)

PARTITION BY LIST ( <column expression list> )
AUTOMATIC

CREATE TABLE example.customer_order_automatic_list_partition_by_year_and_month
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY LIST (YEAR(purchase_ts), MONTH(purchase_ts))
AUTOMATIC

PARTITION BY HASH ( <column expressions> )
PARTITIONS <total partitions>

CREATE TABLE example.customer_order_hash_partition_by_year_and_month
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY HASH (YEAR(purchase_ts), MONTH(purchase_ts))
PARTITIONS 10

PARTITION BY SERIES ( <column list> )
[PERCENT_FULL <percentage>]

CREATE TABLE example.customer_order_series_partition_error_default_percent_full
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,4),
    purchase_ts  TIMESTAMP NOT NULL
    SHARD KEY(customer_id)
)
PARTITION BY SERIES (customer_id)

CREATE TABLE example.route_series_partition_by_track
(
    TRACKID    VARCHAR NOT NULL,
    x          DOUBLE NOT NULL,
    y          DOUBLE NOT NULL,
    TIMESTAMP  TIMESTAMP NOT NULL
)
PARTITION BY SERIES (TRACKID)
PERCENT_FULL 25

TIER STRATEGY
(
    <tier strategy>,
    ...
    <tier strategy>
)

CREATE OR REPLACE TABLE example.customer_order
(
    id          INT NOT NULL,
    customer_id INT NOT NULL,
    total_price DECIMAL(10,2),
    purchase_ts TIMESTAMP,
    SHARD KEY (customer_id)
)
TIER STRATEGY
(
    ( ( VRAM 1, RAM 7, PERSIST 5 ) )
)

CREATE OR REPLACE TABLE example.customer_order
(
    id          INT NOT NULL,
    customer_id INT NOT NULL,
    total_price DECIMAL(10,2),
    purchase_ts TIMESTAMP,
    SHARD KEY (customer_id)
)

SHOW CREATE TABLE example.customer_order

| CREATE TABLE "example"."customer_order"
(
    "id" INTEGER NOT NULL,
    "customer_id" INTEGER (shard_key) NOT NULL,
    "total_price" DECIMAL (10, 2),
    "purchase_ts" TIMESTAMP
)
TIER STRATEGY (
    ( ( VRAM 1, RAM 7, PERSIST 5 ) )
); |

<[ATTRIBUTE] | CHUNK [SKIP] | LOW CARDINALITY | GEOSPATIAL | CAGRA | HNSW> INDEX (<column>)
...
<[ATTRIBUTE] | CHUNK [SKIP] | LOW CARDINALITY | GEOSPATIAL | CAGRA | HNSW> INDEX (<column>)

CREATE TABLE example.employee
(
    id INTEGER NOT NULL,
    dept_id INTEGER NOT NULL,
    manager_id INTEGER,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    sal DECIMAL(18,4),
    hire_date DATE,
    work_district WKT,
    office_longitude REAL,
    office_latitude REAL,
    profile VECTOR(10),
    PRIMARY KEY (id, dept_id),
    SHARD KEY (dept_id)
)
INDEX (last_name)
LOW CARDINALITY INDEX (dept_id)
CHUNK SKIP INDEX (id)
GEOSPATIAL INDEX (work_district)
GEOSPATIAL INDEX (office_longitude, office_latitude)
CAGRA INDEX (profile)
HNSW INDEX (profile)

USING TABLE PROPERTIES
(
    <table property> = <value>,
    ...
    <table property> = <value>
)

CREATE OR REPLACE TABLE example.customer_order
(
    id          INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    total_price DECIMAL(10,2),
    purchase_ts TIMESTAMP,
    SHARD KEY (customer_id)
)
USING TABLE PROPERTIES (CHUNK SIZE = 1000000, TTL = 15)

CREATE [OR REPLACE] [REPLICATED] [TEMP] [LOGICAL | MATERIALIZED] EXTERNAL TABLE [<schema name>.]<table name>
[<table definition clause>]
<
	REMOTE QUERY '<source data query>'
	|
	FILE PATHS <file paths>
		[FORMAT <[DELIMITED] TEXT [(<delimited text options>)] | AVRO | JSON | PARQUET | SHAPEFILE>]
>
[WITH OPTIONS (<load option name> = '<load option value>'[,...])]
[<partition clause>]
[<tier strategy clause>]
[<index clause>]
[<table property clause>]

(
    <column name> <column definition> [COMMENT '<column comment>'],
    ...
    <column name> <column definition> [COMMENT '<column comment>'],
    [PRIMARY KEY (<column list>)],
    [SHARD KEY (<column list>)],
    [FOREIGN KEY
        (<column list>) REFERENCES <foreign table name>(<foreign column list>) [AS <foreign key name>],
        ...
        (<column list>) REFERENCES <foreign table name>(<foreign column list>) [AS <foreign key name>]
    ]
)

CREATE LOGICAL EXTERNAL TABLE example.ext_employee_dept2
REMOTE QUERY 'SELECT * FROM example.employee WHERE dept_id = 2'
WITH OPTIONS (DATA SOURCE = 'example.jdbc_ds')

CREATE EXTERNAL TABLE example.ext_product
FILE PATHS 'kifs://data/products.csv'

CREATE EXTERNAL TABLE example.ext_employee
FILE PATHS 'kifs://data/employee.parquet'
WITH OPTIONS (PRIMARY KEY = (id))

CREATE EXTERNAL TABLE example.ext_employee
FILE PATHS 'kifs://data/employee.csv'
WITH OPTIONS
(
	COLUMN FORMATS = '
	{
		"hire_date": {"date": "YYYY-MM-DD"}
	}'
)

CREATE EXTERNAL TABLE example.ext_product
FILE PATHS 'products.csv'
WITH OPTIONS
(
	DATA SOURCE = 'example.product_ds',
	REFRESH ON START = TRUE
)

CREATE EXTERNAL TABLE example.ext_product
FILE PATHS 'products.csv'
WITH OPTIONS
(
	DATA SOURCE = 'example.product_ds',
	SUBSCRIBE = TRUE,
	POLL_INTERVAL = '60'
)

CREATE EXTERNAL TABLE example.ext_employee_dept2
REMOTE QUERY 'SELECT * FROM example.ext_employee WHERE dept_id = 2'
WITH OPTIONS
(
	DATA SOURCE = 'example.jdbc_ds',
	REFRESH ON START = TRUE
)

CREATE EXTERNAL TABLE example.ext_product
FILE PATHS 'products.csv'
WITH OPTIONS (DATA SOURCE = 'example.product_ds')

CREATE EXTERNAL TABLE example.ext_employee_dept2
REMOTE QUERY 'SELECT * FROM example.employee WHERE dept_id = 2'
WITH OPTIONS (DATA SOURCE = 'example.jdbc_ds')

CREATE EXTERNAL TABLE example.ext_order
FILE PATHS 'orders/'
WITH OPTIONS (DATA SOURCE = 'example.order_ds', SUBSCRIBE = TRUE)

-- Load new orders for product 42 continuously into a table
--   order_id is an ever-increasing sequence allotted to each new order
CREATE EXTERNAL TABLE example.ext_order_product42
REMOTE QUERY 'SELECT * FROM example.orders WHERE product_id = 42'
WITH OPTIONS
(
	DATA SOURCE = 'example.jdbc_ds',
	SUBSCRIBE = TRUE,
	REMOTE_QUERY_INCREASING_COLUMN = 'order_id'
)

CREATE [OR REPLACE] [REPLICATED] [TEMP] TABLE [<schema name>.]<table name> AS
(
    <select statement>
)

CREATE REPLICATED TEMP TABLE example.new_temporary_table AS
(
    SELECT *
    FROM example.old_table
)

CREATE OR REPLACE TABLE example.new_sharded_table AS
(
    SELECT a, b, c, d, KI_SHARD_KEY(a, b) /* KI_HINT_INDEX(d) */
    FROM example.old_table
)

CREATE TABLE example.new_pk_copy_table
(
    a INTEGER NOT NULL,
    b INTEGER NOT NULL,
    c VARCHAR(32) NOT NULL,
    d TIMESTAMP,
    PRIMARY KEY (a, b, c),
    FOREIGN KEY (d) REFERENCES example.old_table_lookup(d)
)

INSERT INTO example.new_pk_copy_table
SELECT *
FROM example.old_table

ALTER TABLE [<schema name>.]<table name>
RENAME TO <new table name>

ALTER TABLE [<schema name>.]<table name>
< MOVE TO | SET SCHEMA > <other schema name>

ALTER TABLE example_olap.sales_2017
MOVE TO example_archive

ALTER TABLE [<schema name>.]<table name>
SET ACCESS MODE < NO_ACCESS | READ_ONLY | WRITE_ONLY | READ_WRITE >

ALTER TABLE [<schema name>.]<table name>
SET TTL <new ttl>

ALTER TABLE example.employee
SET TTL 7

ALTER TABLE example.employee
SET TTL -1

ALTER TABLE [<schema name>.]<table name>
ADD <column name> <column definition> [DEFAULT <string/numeric constant | column name>]

ALTER TABLE example.employee
ADD salary NUMERIC(10, 2) NOT NULL DEFAULT 0

ALTER TABLE example.employee
ADD category VARCHAR(32, DICT)

ALTER TABLE example.employee
ADD bio VARCHAR(TEXT_SEARCH)

ALTER TABLE [<schema name>.]<table name>
RENAME COLUMN <column current name> TO <column new name>

ALTER TABLE example.employee
RENAME COLUMN bio TO biography

ALTER TABLE [<schema name>.]<table name>
MODIFY [COLUMN] <column name> <column definition>

ALTER TABLE [<schema name>.]<table name>
ALTER COLUMN <column name> <column definition>

ALTER TABLE example.employee
ALTER COLUMN first_name VARCHAR(50, DICT) NOT NULL

ALTER TABLE [<schema name>.]<table name>
DROP COLUMN <column name>

ALTER TABLE [<schema name>.]<table name>
ADD INDEX (<column name>)

ALTER TABLE example.employee
ADD INDEX (last_name)

ALTER TABLE [<schema name>.]<table name>
DROP INDEX (<column name>)

ALTER TABLE example.employee
DROP INDEX (last_name)

ALTER TABLE [<schema name>.]<table name>
ADD LOW CARDINALITY INDEX (<column name>)

ALTER TABLE example.employee
ADD LOW CARDINALITY INDEX (dept_id)

ALTER TABLE [<schema name>.]<table name>
DROP LOW CARDINALITY INDEX (<column name>)

ALTER TABLE example.employee
DROP LOW CARDINALITY INDEX (dept_id)

ALTER TABLE [<schema name>.]<table name>
ADD CHUNK [SKIP] INDEX (<column name>)

ALTER TABLE example.employee
ADD CHUNK SKIP INDEX (id)

ALTER TABLE [<schema name>.]<table name>
DROP CHUNK [SKIP] INDEX (<column name>)

ALTER TABLE example.employee
DROP CHUNK SKIP INDEX (id)