CREATE TABLE - Kinetica Docs

Creates a new table.

CREATE TABLE Syntax

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

Parameters

OR REPLACE

Any existing table or view with the same name will be dropped before creating this one; mutually exclusive with IF NOT EXISTS

REPLICATED

The table will be distributed within the database as a replicated table

TEMP

The table will be a memory-only table; which, among other things, means it will not be persisted (if the database is restarted, the table will be removed), but it will have increased ingest performance

IF NOT EXISTS

If a table or view with the same name exists, do nothing; mutually exclusive with OR REPLACE

Name of the schema that will contain the created table; if no schema is specified, the table will be created in the user’s default schema

Name of the table to create; must adhere to the supported naming criteria

Name of a column to create within the table; must adhere to the supported naming criteria

Definition of the column associated with <column name>; see Data Definition (DDL) for column format

COMMENT '<column comment>'

Apply column comment <column comment> to the column associated with <column name>

SOFT

Modifier for PRIMARY KEY that creates a soft primary key instead of a standard primary key

PRIMARY KEY (<column list>)

Optional primary key specification clause, where <column list> is a comma-separated list of columns to use as the primary key for the table

SHARD KEY (<column list>)

Optional shard key specification clause, where <column list> is a comma-separated list of columns to use as the shard key for the table

FOREIGN KEY ...

Optional comma-separated set of foreign key specification clauses, with the following parameters:

Parameter	Description
`<column list>`	Comma-separated list of columns in the table to create that will reference a matching set of primary key columns in another table
`<foreign table name>`	Name of target table referred to in this foreign key
`<foreign column list>`	The primary key columns in the target table referred to in this foreign key, matching the list of columns specified in `<column list>` in the table to create
`AS <foreign key name>`	Optional alias for the foreign key

Defines a partitioning scheme for the table to create

Defines the tier strategy for the table to create

Applies any number of column indexes, chunk skip indexes, geospatial indexes, CAGRA indexes, or HNSW indexes to the table to create

Assigns table properties, from a subset of those available, to the table to create

Examples

To create a table with various column types and properties:

CREATE TABLE Example

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 Clause

A table can be further segmented into partitions. The supported partition types are:

Range

Partition by defined value ranges to improve filter & join performance

Interval

Partition by numeric or time-based intervals with dynamic partition creation

List

Partition by discrete sets of values to improve filter performance

Hash

Partition by hash of key columns to improve equi-join performance

Series

Partition into a sequence of dynamically-allocated partitions, filling each to a threshold

See Partitioning for details.

Range Partitioning

The general format for the range partition clause is:

PARTITION BY RANGE Syntax

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

The partition definition clause, PARTITIONS, is optional, though it is recommended to define partitions at table creation time, when feasible.

Defining (adding) partitions after data has been loaded will result in a performance penalty as the database moves existing records targeted for the new partition from the default partition into it.

For example, to create a range-partitioned table with the following criteria:

partitioned on the date/time of the order
partitions for years:
- 2014 - 2016
- 2017
- 2018
- 2019
records not in that range go to the default partition

PARTITION BY RANGE Example

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

Interval Partitioning

The general format for the interval partition clause is:

PARTITION BY INTERVAL Syntax

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

For example, to create an interval-partitioned table with the following criteria:

partitioned on the date/time of the order
one partition for each year from 2014 on
later year partitions are added as necessary
records prior to 2014 go to the default partition

PARTITION BY INTERVAL (Year) Syntax

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

To create an interval-partitioned table with the following criteria:

partitioned on the date/time of the order
one partition for each day from January 1st, 2014 on
later day partitions are added as necessary
records prior to 2014 go to the default partition

PARTITION BY INTERVAL (Day) Syntax

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

The same interval-partitioned scheme above can be created using the timestamp column directly, with the help of the INTERVAL function (described in the Date/Time Functions section):

PARTITION BY INTERVAL (Day) Alternate Syntax

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

This scheme can be easily modified to create an hourly partition instead:

PARTITION BY INTERVAL (Hour) Syntax

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

List Partitioning

The list partition clause has two forms:

manual

Define partitions as specific value lists; unmatched records go to the default partition

automatic

Database automatically creates a partition for each distinct partition key value

Manual

The general format for the manual list partition clause is:

PARTITION BY LIST Syntax

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

The partition definition clause, PARTITIONS, is optional, though it is recommended to define partitions at table creation time, when feasible.

For example, to create a manual list-partitioned table with the following criteria:

partitioned on the date/time of the order
partitions for years:
- 2014 - 2016
- 2017
- 2018
- 2019
records not in that list go to the default partition

PARTITION BY LIST (Year) Example

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

To create a manual list-partitioned table with a multi-column key and the following criteria:

partitioned on the date/time of the order
each partition corresponds to a unique year & month pair
partitions for years/months:
- February 2016 & March 2016
- March 2020
records not in that list go to the default partition

PARTITION BY LIST (Month) Example

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

Automatic

The general format for the automatic list partition clause is:

Automatic PARTITION BY LIST Syntax

PARTITION BY LIST ( <column expression list> )
AUTOMATIC

To create an automatic list-partitioned table with the following criteria:

partitioned on the date/time of the order
one partition for each unique year & month across all orders
partitions are added as necessary

Automatic PARTITION BY LIST (Month) Example

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

Hash Partitioning

The general format for the hash partition clause is:

PARTITION BY HASH Syntax

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

To create a hash-partitioned table with the following criteria:

partitioned on the date/time of the order
distributed among the fixed set of partitions, based on the hash of the year & month of the order
10 partitions

PARTITION BY HASH Example

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

Series Partitioning

The general format for the series partition clause is:

PARTITION BY SERIES

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

The PERCENT_FULL should be an integer between 1 and 100; the default is 50%. To create a series-partitioned table with the following criteria:

partitioned on the customer of each order
partitions with closed key sets will contain all orders from a set of unique customers
50% fill threshold

PARTITION BY SERIES Example

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)

To create a series-partitioned track table with the following criteria:

partitioned on the track ID
partitions with closed key sets will contain all points from a unique set of tracks
25% fill threshold

PARTITION BY SERIES (Tracks) Example

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 Clause

A table can have a tier strategy specified at creation time. If not assigned a tier strategy upon creation, a default tier strategy will be assigned.

TIER STRATEGY Clause Syntax

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

For example, to create a customer_order table with an above-average eviction priority in the RAM Tier:

TIER STRATEGY Example

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

If not specified, the default tier strategy will be assigned:

Default TIER STRATEGY Example

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 TABLE Command to Display TIER STRATEGY

SHOW CREATE TABLE example.customer_order

SHOW TABLE Command Output

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

The response to SHOW TABLE is a single-record result set with the DDL statement as the value in the DDL column, shown here with the column separators returned by kisql.

Index Clause

A table can have any number of indexes applied to any of its columns at creation time. The types of explicit indexes supported are:

Column (Attribute) Index

B-tree index on a column for equality and range filter performance

Low Cardinality Index

Column index optimized for columns with many duplicate values, using less memory

Chunk Skip Index

Improves equality-based filtering, especially on partition key columns

Geospatial Index

Improves performance of geospatial functions on geometry columns

CAGRA Index

GPU-accelerated graph-based index for vector similarity search; requires manual refresh

HNSW Index

Graph-based index for vector similarity search; automatically maintained

Index Clause Syntax

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

For example, to create a table with the following indexes:

column index on last_name
low-cardinality index on dept_id
chunk skip index on id
geospatial index on work_district
geospatial index on the pair of office_longitude & office_latitude
CAGRA index on profile

Index Example

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)

Table Property Clause

A subset of table properties can be applied to the table at creation time.

Table Property Clause

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

Available table properties include:

CHUNK COLUMN MEMORY

Size of the blocks of memory holding the data, when loaded; specified as the maximum number of bytes any one column should hold.

The size of dictionary-encoded columns is estimated.

CHUNK MEMORY

Size of the blocks of memory holding the data, when loaded; specified as the maximum total number of bytes all columns should hold.

The size of dictionary-encoded columns is estimated.

CHUNK SIZE

Size of the blocks of memory holding the data, when loaded; specified as the maximum number of records each block of memory should hold.

COMPRESSION_CODEC

The default compression type to apply to columns of this table not explicitly given one.

NO_ERROR_IF_EXISTS

Error suppression option, which causes no error to be returned if a table with the same name already exists; default is FALSE.

This is the same option as IF NOT EXISTS.

PRIMARY_KEY_TYPE

The type of primary key index to use.The default is memory.

Type	Description
`memory`	Primary key index is loaded into memory, occupying RAM but improving performance.
`disk`	Primary key index is stored on disk only, increasing available RAM at the cost of some performance.

TTL

The time-to-live (TTL) for the table; if not set, the table will not expire.

For example, to create a table with up to 1,000,000 records per chunk and that will expire in 15 minutes:

Table Property Example

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)

​Parameters

​Examples

​Partition Clause

Range

Interval

List

Hash

Series

​Range Partitioning

​Interval Partitioning

​List Partitioning

manual

automatic

​Manual

​Automatic

​Hash Partitioning

​Series Partitioning

​Tier Strategy Clause

​Index Clause

Column (Attribute) Index

Low Cardinality Index

Chunk Skip Index

Geospatial Index

CAGRA Index

HNSW Index

​Table Property Clause

Parameters

Examples

Partition Clause

Range Partitioning

Interval Partitioning

List Partitioning

Manual

Automatic

Hash Partitioning

Series Partitioning

Tier Strategy Clause

Index Clause

Table Property Clause