CREATE TABLE ... AS - Kinetica Docs

Creates a new table from the given query in the specified schema.

CREATE TABLE ... AS Syntax

CREATE [OR REPLACE] [REPLICATED] [TEMP] [VRAM] TABLE [<schema name>.]<table name> AS
(
    <select statement>
)
[<partition clause>]
[<tier strategy clause>]
[<table property clause>]

The parentheses around <select statement> are optional.

Any column aliases used must adhere to the supported naming criteria. While primary keys & foreign keys are not transferred to the new table, shard keys will be, if the column(s) composing them are part of the SELECT list.

Parameters

Parameter/Key	Description
`OR REPLACE`	Any existing table or view with the same name will be dropped before creating this one
`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
`VRAM`	The table’s data will be held in video memory, where supported
`<schema name>`	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
`<table name>`	Name of the table to create; must adhere to supported naming criteria
`<select statement>`	The query that will define both the initial structure and content of the created table
<partition clause>	Defines a partitioning scheme for the table to create
<tier strategy clause>	Defines the tier strategy for the table to create
<table property clause>	Assigns table properties, from a subset of those available, to the table to create

The following can be applied to <select statement> to affect the resulting table:

Keyword	Type	Description
KI_HINT_GROUP_BY_PK	hint	Creates a primary keys on the columns in the `GROUP BY` clause if the outermost `SELECT` statement contains a `GROUP BY`
KI_HINT_INDEX(column list)	hint	Indexes each of the columns specified in the column list
KI_SHARD_KEY(column list)	pseudo- function	Shards the result table with a shard key composed of all columns in the specified column list

Table Property Clause

A subset of table properties can be applied to the created table at creation time. The Table Property Clause syntax is the same as that in the CREATE TABLE Table Property Clause, and the USING keyword is optional in either. Of the properties available there, only CHUNK SIZE, COMPRESSION_CODEC, & TTL take effect here; any other is accepted without error and then discarded.

Examples

To create a replicated temporary table that is a copy of an existing table, failing if a table with the same name as the target table already exists:

CREATE TABLE ... AS (Replicated/Temporary) Example

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

To create a permanent table with columns a, b, c, & d a new shard key on columns a & b, and an index on column d, replacing a table with the same name as the target table, if it exists:

CREATE TABLE ... AS (Reshard) Example

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
)

To copy a table with columns a, b, c, & d, preserving the primary key on a, b, & c, and the foreign key from d; a new table must be created to match the schema of the old one and then records can be copied from the old one to the new one:

CREATE TABLE (Preserve Primary Key) Example, DDL Step

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

CREATE TABLE (Preserve Primary Key) Example, DML Step

INSERT INTO example.new_pk_copy_table
SELECT *
FROM example.old_table

This create/insert process is necessary, as neither primary keys nor foreign keys can be preserved through hints.

See Limitations for other restrictions.

​Parameters

​Table Property Clause

​Examples

Parameters

Table Property Clause

Examples