Tables - Ocient Documentation

This group of DDL SQL statements allows database administrators to manage tables. Database administrators can create, modify, drop, and export tables. You can also truncate segments on a table. Additional statements are available for unique settings like segment redundancy, compression settings, and streamloader properties. You can view information about tables using the sys.tables system catalog table. For information on other database components, see the pages on Databases, Schemas, Views, and Indexes.

CREATE TABLE

Creates a new table in the current database. The table name must be distinct from the name of any existing tables in the database unless the REPLACE keyword is specified. To use REPLACE in the CREATE TABLE statement, you must have DELETE privileges. By default, columns are nullable unless otherwise specified. For faster query results, you can define one for the table, which must be a timestamp, date, or time column, with a specified bucket resolution. Tables with a specified TimeKey can perform query operations faster, especially if they involve time filtering. You can specify a Clustering Key composed of one or more fixed-length columns. Designating columns as cluster keys that are frequently referenced in queries can greatly improve performance. For details about defining TimeKeys and clustering indexes, see TimeKeys and Clustering Keys. See the Data Types section for table-supported data types. Required Privileges: You must have the CREATE TABLE privilege for the current database.

For examples, see CREATE TABLE SQL Statement Examples.

Syntax

SQL

CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name
    [ ( <column_definition> [, ...] | <clustering_definition> ) ]
    [ [ WITH ] <create_option> [, ... ] ]
    [ AS (query) ]

<column_definition> ::=
   column_name [ data_type ] [ <timekey_definition> ] [ <column_constraint> ] [, ...] ]

<timekey_definition> ::=
  TIME KEY BUCKET (bucket_granularity, bucket_value)

<column_constraint> ::=
   | [ NOT ] NULL
   | DEFAULT expression
   | COMMENT comment
   | COMPRESSION GDC [ (compression_value), EXISTING schema_name ]
   | COMPRESSION [ compression_scheme ]

<clustering_definition> ::=
    CLUSTERING KEY key_name (ck_col1, ck_col2 [, ...])
        [, INDEX index_name (idx_col1, idx_col2 [, ...])
        [, INDEX ... ] ]

 <create_option> ::=
   INDEX index_name (index_column) [ USING index_type ]
   | RETENTION POLICY AGE retention_granularity retention_value
   | STORAGESPACE storage_space_name
   | SEGMENTSIZE segment_value
   | REDUNDANCY segment_part (redundancy_scheme)
   | STREAMLOADER_PROPERTIES streamloader_json
   | COMMENT '<string>'

Parameter	Type	Description
`table_name`	string	A unique identifier for the table. `table_name` must be distinct from the name of any existing tables in the database unless the `REPLACE` keyword is specified.
`query`	string	A `SELECT` query used to load data into the newly created table. For details, see CREATE TABLE AS SELECT .

Column Definition (`<column_definition>`)

The parameters listed here are required for defining each column in a table.

Parameter	Type	Description
`column_name`	string	An identifier for a column to be included in the newly created table.
`data_type`	string	The data type of a specified column. For a list of supported data, see Data Types.

Clustering Key and Index Definition (`<clustering_definition>`)

The parameters listed here are required for defining a Clustering Key or clustering indexes on a table. For details about how to apply clustering columns, see Clustering Key.

SQL

CLUSTERING KEY key_name (ck_col1, ck_col2 [, ...])
    [, INDEX index_name (idx_col1, idx_col2 [, ...])
    [, INDEX ... ] ]

Parameter	Type	Description
`key_name`	string	An identifier for the Clustering Key.
`ck_col1, ck_col2 [, ...]`	string	A series of specific columns comprising the Clustering Key. Clustering Key columns must not be nullable. Specify `NOT NULL` in the column definition for the respective columns. For details, see Column Definition. No limit exists on the number of columns for the Clustering Key.
`index_name`	string	Optional. An identifier for a clustering index. You must include the definition of columns that comprise the index using the `idx_col1, idx_col2 [, ...]` parameter.
`idx_col1, idx_col2 [, ...]`	string	Optional. A series of specific columns comprising a clustering index. You can apply clustering indexes only to columns included in the Clustering Key. Specify any number of columns in any specified order. You must include the identifier for the index using the `index_name` parameter.

TimeKey Definition (`<timekey_definition>`)

This syntax example is for a single TimeKey column, which can be included in the column definition of a CREATE TABLE statement. For the full syntax, see CREATE TABLE. For details about using TimeKeys, see TimeKeys.

SQL

column_name [ data_type ] TIME KEY BUCKET (bucket_granularity, bucket_value [, NOINDEX] )

Parameter	Type	Description
`column_name`	string	An identifier for the column.
`data_type`	string	Optional. The data type of the column. For the TimeKey column, this column should be DATE or TIMESTAMP type. The TimeKey column can also support INT or BIGINT type, but these data types do not use a `bucket_value` argument.
`bucket_granularity`	numeric	The granularity of the TimeKey column, based on the specified TIME type in `bucket_value`.
`bucket_value`	string	The TIME type to parse the TimeKey column. Supported values include: `[ DAY, HOUR, MINUTE, SECOND ]` For example, `BUCKET(1, DAY)` sets the time-bucket granularity to a fixed width of one day.

Column Constraint (`<column_constraint>`)

The parameters listed here include constraints and other configurations for individual columns. For best performance, one column with date or time data in each table should be defined as the TIME KEY with a specified bucket_value. For details about TimeKey columns, see TimeKeys.

Parameter	Type	Description
`expression`	data type of the column	If you specify this parameter as a constraint, the parameter sets the default value for the column. The value can be a literal or a constant, deterministic expression. Default expressions cannot use non-deterministic functions such as `RAND()`. The system evaluates the expression at the time of table creation and stores the resulting value. If the expression result does not match the column data type, the system automatically attempts type coercion to convert it. Literals Usage — The value can be a literal enclosed in quotes or an unquoted numeric literal. For example, this `CREATE TABLE` SQL statement includes a default value for its `UUID` column. `CREATE TABLE example_table ( col1 UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' );` The supported geospatial data types `POINT`, `LINESTRING`, and `POLYGON` require WKT formatting. For formatting examples, see the Well-known text representation of geometry. Expressions Usage — The value can also be an expression that evaluates to a constant, such as an arithmetic operation or a function execution. For example, this `CREATE TABLE` statement uses two default expressions. The column `col1` uses a math equation that evaluates to `104`. The column `col2` concatenates two strings using the `CONCAT` function to create the new string `'HEYYOU'`. `CREATE TABLE example_table ( col1 INT DEFAULT 4 + (5 * 20), col2 VARCHAR DEFAULT CONCAT('HEY', 'YOU') );`
`comment`	string	An optional comment for the column.
`compression_value`	numeric	An integer value of either `1`, `2` or `4` is used to define the storage of `COMPRESSION GDC`. The compression of the values is defined as follows: - A `compression_value` of 1 can hold up to 255 unique values. - A `compression_value` of 2 can hold up to 65535 unique values. - A `compression_value` of 4 can hold millions of unique values. For tuple columns, compression is specified for each tuple value rather than with other constraints. The example here defines a tuple column with GDC compression only for the first inner `VARCHAR` value. `my_tuple TUPLE<<INT, VARCHAR(255) COMPRESSION GDC(1), VARCHAR(255)>>` It is not recommended to use compression on a column that will contain more than one million unique values.
`schema_name`	string	Fully qualified column name or a column name if you specify a column in the same table. If `EXISTING` is specified as a compression constraint, the compression GDC reuses the system lookup table for the `schema_name` column, rather than creating a new one. The existing column must be in the same database as the new column. `schema_name` should include the schema, table, and column names, each separated by periods and enclosed in double-quotation marks, for example: `"schema_name"."table_name"."column_name"` If an existing column is specified, any new column that uses the existing column’s system lookup table must be deleted before the existing column can be deleted.
`compression_scheme`	string	The type of compression used for the column. Supported values include: `[ COMPRESSION NONE \| COMPRESSION ZSTD \| COMPRESSION DYNAMIC ]` If no compression setting is specified, the compression defaults to `COMPRESSION DYNAMIC` for fixed-length columns and `COMPRESSION NONE` for variable-length columns. `COMPRESSION ZSTD` applies to `VARCHAR` columns as well as other data types. For the `COMPRESSION DYNAMIC` setting, the System applies LZ4 compression only if the column data is dynamically determined to be compressible. For fixed-length columns, `COMPRESSION DYNAMIC` applies delta-delta compression. For details about Ocient-supported compression schemes, see Table Compression Options.

Create Option (`<create_option>`)

The parameters listed here include various options to configure table storage space, segments, redundancy, streamloading, and indexes.

Parameter	Type	Description
`index_name`	string	An identifier for the index. The name must be distinct from the name of any existing index on the table.
`index_column`	string	The name of the column for the index. Identical indexes on the same column are not allowed. A column can only have multiple indexes if they are of different types or parameters.
`index_type`	string	Optional. One of these supported secondary index types: `INVERTED \| HASH \| NGRAM \| SPATIAL \| ZONE_MAP` If unspecified, this value defaults to an index type based on its data type. For details, see Index Type Requirements and Defaults.
`retention_granularity`	numeric	A number that represents the amount of time before the system deletes data in the table. You must pair this number with a specific time type, represented by the `retention_value` parameter. For details about setting up retention policies, see Table Retention Policies.
`retention_value`	string	The unit of time that represents how long the table retains data. Supported values are: `WEEK`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `MILLISECOND` You must pair this number with the amount of time, represented by the `retention_granularity` parameter. For example, `1 DAY` schedules the table retention to a granularity of one day.
`storage_space_name`	string	An identifier for the `STORAGESPACE`.
`segment_value`	numeric	A value to define the size of the segment.
`segment_part`	string	Specifies the segment part redundancy of the table. Supported values include: `\{ DATA \| MANIFEST \| INDEX \| SUMMARY_STATS \| STATS }` These settings are defined as follows: `DATA`: The actual data for the table. `MANIFEST`: Header information stored about the data, which describes how to locate any specified cluster of rows within the data. `INDEX`: The index of the data used for quicker lookups and better query performance. `SUMMARY_STATS`: A collection of statistics on the data that includes compression, row count, and average column size. `STATS`: Used in the optimizer, the probability density function and combinable distinct estimators used to make better optimizations to query plans.
`redundancy_scheme`	string	The redundancy scheme. Supported values include: `\{ COPY \| PARITY }` These settings are defined as follows: `COPY`: A copy of the bytes is stored throughout the storage cluster to ensure redundancy. This option uses more storage but is faster during rebuilds and node outages. `PARITY`: Using the parity encoding specified on the storage cluster, this option uses parity bits to ensure redundancy for the data. This option uses less storage but is slower during rebuilds and node outages.
`streamloader_json`	string	A JSON string that defines the streamloader parameters. For details, see ALTER TABLE STREAMLOADER PROPERTIES.

Example This example creates a new table in the current database and schema named trades. The table uses the TIMESTAMP column created_at as the TimeKey, with the granularity set at 1 hour. The columns ticker_symbol and t_type are defined as the table clustering keys. The example also includes a streamloader property pageQueryExclusionDuration to delay how soon data pages that were recently added can be included in query results.

SQL

 CREATE TABLE trades (
    id UUID NOT NULL,
    ticker_symbol VARCHAR(255) NOT NULL COMPRESSION GDC(2),
    t_type VARCHAR(255) NOT NULL COMPRESSION GDC(1),
    raw_ticker_data VARCHAR(255),
    created_at TIMESTAMP NOT NULL TIME KEY BUCKET(1, HOUR),
    array_of_tuples tuple<<varchar(2040), byte, bigint, double, timestamp, date, time, decimal(3,2), st_point, boolean, binary(6), hash(8), ip, uuid>>[],
    CLUSTERING INDEX idx_ticker_symbol_type (ticker_symbol, t_type),
    INDEX idx_type (t_type)
 )
    STORAGESPACE ocient_storage,
    REDUNDANCY DATA (PARITY),
    REDUNDANCY MANIFEST (COPY),
    STREAMLOADER_PROPERTIES '{
    "pageQueryExclusionDuration" : "2700s"
}';

CREATE TABLE AS SELECT (CTAS)

CTAS provides the ability to create and load a new table from the result of a query on one or more existing tables. The first column of the query result maps to the first column of the new table definition, the second column maps to the second column of the new table, and so on. The new table is available for querying after it has been created, and the entire result set from the query has been loaded into the table. When you receive a response to the CTAS SQL statement, the load is complete, and the table is ready. When you create a table from a SELECT SQL statement, the schema for the table can be automatically determined based on the query results. You can override this behavior with an alternative schema, provided the query results can automatically be cast to the target column types. CTAS also supports all syntax options for the new table. CTAS does not support default values and explicit nullable definitions on the column of the table. CTAS statements support secondary and prefix indexes. To create a table, you must have both the CREATE TABLE privilege for the current database and the SELECT privilege on all referenced tables and views. For syntax and parameter information, see CREATE TABLE.

Default Table Definitions

By default, a new table created with a CTAS statement retains column names, data types, and nullable definitions from the queried table. You can override this configuration with alternate table definitions in the CTAS statement.

Tables created by a CTAS statement do not inherit some table definitions from the original table, including the following:

TimeKey
Clustering Key and Clustering indexes
Secondary indexes
Column compression
Optional table configurations (see create option)

To include these table definitions, you must explicitly specify them in the CTAS statement.

Examples These CTAS examples select columns from the original_table table that this CREATE TABLE statement defines. This table contains these columns:

col_int — Non-nullable integer with the default value 123456789
col_bigint — Non-nullable 8-byte signed integer
col_id — Non-nullable integer
col_point — Non-nullable point with the default value POINT(0 0)
col_timestamp — Non-nullable TimeKey with granularity set at 1 day
col_varchar — Variable-length character string with a maximum length of 255 characters and Zstandard compression

The table has a Clustering Key ck using the col_bigint and col_id columns. It also has two secondary indexes: a hash index idx_01 on the col_varchar column and a spatial index on the idx_02 column.

SQL

CREATE TABLE original_table (
 	col_int INT NOT NULL DEFAULT 123456789,
 	col_bigint BIGINT NOT NULL,
 	col_id INT NOT NULL,
	col_point POINT NOT NULL DEFAULT 'POINT(0 0)',
	col_timestamp TIMESTAMP TIME KEY BUCKET (1,DAY) NOT NULL,
	col_varchar VARCHAR(255) COMPRESSION ZSTD,
	CLUSTERING KEY ck (col_bigint, col_id)
)
WITH
    INDEX idx_01 (col_varchar) USING HASH,
    INDEX idx_02 (col_point) USING SPATIAL;

CTAS Using All Columns from a Base Table This example shows a basic CTAS statement that inherits most of its table definition from the original_table table.

SQL

CREATE TABLE basic_ctas AS (
	SELECT * FROM original_table );

The new basic_ctas table includes all the columns and data types from the original table definition. However, it does not include the segment keys, indexes, or the compression on the col_varchar column. The EXPORT TABLE SQL statement shows the differences in the basic_ctas table.

SQL

EXPORT TABLE basic_ctas;

Output

Text

CREATE TABLE basic_ctas (
    "col_int" INT NOT NULL,
    "col_bigint" BIGINT NOT NULL,
    "col_id" INT NOT NULL,
    "col_point" POINT NOT NULL,
    "col_timestamp" TIMESTAMP NOT NULL,
    "col_varchar" VARCHAR(536870912) COMPRESSION DYNAMIC NULL
)
REDUNDANCY cde (PARITY),
REDUNDANCY column_metadata (COPY),
REDUNDANCY data (PARITY),
REDUNDANCY index (PARITY),
REDUNDANCY pdf (PARITY),
REDUNDANCY skip_lists (COPY),
REDUNDANCY stats (PARITY),
REDUNDANCY summary_stats (PARITY),
REDUNDANCY table_of_contents (COPY),
STORAGESPACE "ss0",
SEGMENTSIZE 4;

In this output, the table options for REDUNDANCY, STORAGESPACE, and SEGMENTSIZE are all default table settings. CTAS Using a Full Table Definition This example CTAS statement includes a more detailed table definition. The definition includes new columns for the TimeKey, Clustering Key, and secondary indexes. The example also makes various changes from the original_table schema:

Different column default value
Different compression scheme (dynamic compression)
New TimeKey granularity of 1 hour
Three columns in the Clustering Key
Different secondary index types (NGRAM and SPATIAL)

SQL

CREATE TABLE complex_ctas (
 	col_amt INT NOT NULL,
 	col_phone BIGINT NOT NULL COMPRESSION DYNAMIC,
 	col_id INT NOT NULL,
	col_point POINT NOT NULL DEFAULT 'POINT(0 0)',
	col_timestamp TIMESTAMP TIME KEY BUCKET (1,HOUR) NOT NULL,
	col_varchar VARCHAR(255) COMPRESSION DYNAMIC,
	CLUSTERING KEY ck (col_amt, col_phone, col_id)
)
WITH
    INDEX idx_01 (col_varchar) USING NGRAM(3),
    INDEX idx_02 (col_point) USING SPATIAL
AS (
	SELECT * FROM original_table );

CTAS Using a Subset of Table Columns This example selects only a subset of columns, col_int, col_bigint, and col_id, from the original table to insert into the new subset table. The example also specifies alternate table options for REDUNDANCY and SEGMENTSIZE.

SQL

CREATE TABLE subset (
 	col_amt INT NOT NULL,
 	col_phone BIGINT NOT NULL,
 	col_id INT NOT NULL
)
WITH
    REDUNDANCY cde (PARITY),
    SEGMENTSIZE 3
AS (
    SELECT
        col_int,
        col_bigint,
        col_id
FROM
    original_table );

Due to limitations of the JDBC API, the reported modified row count might not be accurate for tables larger than two billion rows.

CTAS Using Transformations on Columns This example performs various transformation functions on the original columns as it selects them for the new table. These include:

col_int_multiply column is the multiplication of the col_int values by 10.
col_month_add column is the result of adding three months to each col_timestamp column value.
col_year column is the extraction of the year value from each col_timestamp column value.
col_substring column contains the first three characters from each col_varchar column value.

SQL

CREATE TABLE calc_table (
 	col_int_multiply INT NOT NULL,
 	col_month_add TIMESTAMP NOT NULL,
 	col_year INT NOT NULL,
 	col_substring VARCHAR(255)
)
AS (
    SELECT
        col_int * 10,
        ADD_MONTHS(col_timestamp, 3),
        YEAR(col_timestamp),
        SUBSTRING(col_varchar, 1, 3)
FROM
    original_table );

CTAS USING LOADERS

Specify one or more Loader Nodes for executing the CTAS SQL statement. If you do not use this option, the Ocient System uses all Loader Nodes that are live to execute the SQL statement. This statement is useful for managing loading operations, particularly when balancing multiple loads of different sizes and resource requirements. Alternatively, this statement can also help simplify small batch loads by sourcing the data from a single Loader Node. Syntax

Parameter	Type	Description
`streamloader`	string	A unique name for the Loader Node. Identify the names of Loader Nodes from the `sys.nodes` table by using this query: `SELECT name FROM sys.nodes;` If the name of the Loader Node contains special characters, you must enclose it in quotes, such as `"stream-loader1"`.
`query`	string	A `SELECT` query that defines values or a table and any of its columns to use for data in the specified `table_name` table.

For the query to execute successfully, the specified names of the Loader Nodes must:

Identify nodes that are live.
Identify nodes that have the Loader role.

Examples Create a table named my_schema.my_ctas_table_2 with a clustering index named idx on the int_col column with the values in the int_col column in the table named my_schema.my_table. Use the Loader Node named stream-loader1 to execute this SQL statement.

SQL

CREATE TABLE my_schema.my_ctas_table_2 (
   CLUSTERING INDEX idx (int_col)
 )
 USING LOADERS "stream-loader1"
 AS (SELECT int_col FROM my_schema.my_table);

In this example, execute the same CTAS SQL statement with two Loader Nodes named stream-loader2 and stream-loader3.

SQL

CREATE TABLE my_schema.my_ctas_table_2 (
   CLUSTERING INDEX idx (int_col)
 )
 USING LOADERS "stream-loader2","stream-loader3"
 AS (SELECT int_col FROM my_schema.my_table);

DROP TABLE

DROP TABLE removes one or more existing tables in the current database, along with all associated views.

This action cannot be undone.

To remove a table, the logged-in user must be a system-level user or have the DELETE TABLE privileges for the table. Syntax

SQL

DROP TABLE [ IF EXISTS ] table_name [, ...]

Parameter	Type	Description
`table_name`	string	A unique identifier for the table. You can drop multiple tables by specifying additional table names and separating each with commas.

Examples This example drops an existing table in the current database and schema named employees.

SQL

DROP TABLE employees;

In this example, drop two tables in the current database and schema named employees and departments.

SQL

DROP TABLE employees, departments;

When you drop multiple tables, and none of them exist in the database, the database returns an error for each missing table. Use the IF EXISTS statement to convert the error to a warning. If you execute the DROP TABLE statement and only some of the tables exist while other tables are missing, the database drops the existing tables and returns warnings for each missing table.

ALTER TABLE

ALTER TABLE RENAME

ALTER TABLE RENAME renames an existing table. Required Privileges To rename a table, you must have the ALTER TABLE privilege for the table. The Ocient System requires these privileges if this statement includes a change to the schema:

VIEW privilege on the current schema of the table
VIEW TABLE and CREATE TABLE privileges on the target schema (if the schema already exists)
CREATE TABLE privilege on the database (if the schema does not exist)

Syntax

SQL

ALTER TABLE [ IF EXISTS ] old_table_name RENAME TO new_table_name

Parameter	Type	Description
`old_table_name`	string	The name of the table to alter.
`new_table_name`	string	The new name to replace `old_table_name`.

Example This example renames an existing table in the current database and schema named us.employees to mid_west_employees.

SQL

ALTER TABLE us.employees RENAME TO mid_west_employees;

This example renames an existing table in the current database named us.employees to north_america.employees.

SQL

ALTER TABLE us.employees RENAME TO north_america.employees;

ALTER TABLE RENAME COLUMN

ALTER TABLE RENAME COLUMN renames an existing column. To rename a column, you must have the ALTER TABLE privilege for the table. Syntax

SQL

ALTER TABLE [ IF EXISTS ] table_name RENAME COLUMN old_column_name TO new_column_name

Parameter	Type	Description
`table_name`	string	A unique identifier for the table.
`old_column_name`	string	The name of the table column to alter.
`new_column_name`	string	The new column name to replace `old_column_name`.

Example This example renames an existing column name in the table employees in the current database and schema to first_name.

SQL

ALTER TABLE employees RENAME COLUMN name TO first_name;

ALTER TABLE ADD COLUMN

ALTER TABLE ADD COLUMN adds a new column to the table. To add a column, you must have the ALTER TABLE privilege for the table. New columns must either be nullable or specify a default value. For a defined list of column parameters, see Column Definition. For constraints, see Column Constraint. Syntax

SQL

ALTER TABLE [ IF EXISTS ] table_name ADD COLUMN <column_definition>;

<column_definition> ::=
   column_name [ data_type ] [ <column_constraint> [, ...] ]

<column_constraint> ::=
   TIME KEY BUCKET(bucket_granularity [, bucket_value ] )
   | [ NOT ] NULL
   | DEFAULT literal
   | COMMENT comment
   | COMPRESSION GDC [ (compression_value), EXISTING schema_name ]
   | COMPRESSION [ compression_scheme ]

Parameter	Type	Description
`table_name`	string	A unique identifier for the table.

Examples This example adds a BIGINT column to the employees table in the current database and schema with the default value of 0.

SQL

ALTER TABLE employees ADD COLUMN
    new_column BIGINT
        NOT NULL
        DEFAULT 0;

This example adds a column that is nullable.

SQL

ALTER TABLE employees ADD COLUMN
    new_column BIGINT
    NULL;

ALTER TABLE ALTER COLUMN COMPRESSION

ALTER TABLE ALTER COLUMN COMPRESSION alters an existing column in the table to change its compression scheme. Supported compression schemes are COMPRESSION NONE, COMPRESSION DYNAMIC, and COMPRESSION ZSTD.

Altering the compression setting of a column only affects compression for data loaded after you execute the SQL statement.

For details about Ocient-supported compression schemes, see Table Compression Options. Syntax

SQL

ALTER TABLE [ IF EXISTS ] table_name ALTER COLUMN column_name
    SET COMPRESSION [ compression_scheme ];

Parameter	Type	Description
`table_name`	string	The name of the table containing the column to alter.
`column_name`	string	The name of the column to alter.
`compression_scheme`	string	Supported values for the compression schemes are: `COMPRESSION NONE` specifies no compression applied. `COMPRESSION DYNAMIC` applies only if the column data is dynamically determined to be compressible. `COMPRESSION ZSTD` applies to VARCHAR columns as well as other data types. For this option only, you can specify these additional parameters: `compression_level` — This value signifies how much compression the data receives. The default value is `0`. The full range of values is from `-7` through `15`. The database uses less memory when this value is lower, whereas more memory when this value is larger. Larger values provide better compression. `dictionary_size` — Dictionary size specified as a positive integer that signifies the size of the shared compression dictionary in bytes. The default value is `32768` (32K). The full range of values is from `4096` (4K) through `1048576` (1MB). This value denotes the amount of memory consumed during segment generation. In general, larger values provide better compression but use more memory.

Examples This example alters the compression scheme for the column employee_name in the table employees in the current database and schema.

SQL

ALTER TABLE employees ALTER COLUMN employee_name
    SET COMPRESSION LZ4;

This example alters the compression scheme to Zstandard for the column employee_name in the table employees in the current database and schema.

SQL

ALTER TABLE employees ALTER COLUMN employee_name
    SET COMPRESSION ZSTD
        compression_level=5,
        dictionary_size=32768;

ALTER TABLE ALTER REDUNDANCY

ALTER TABLE ALTER REDUNDANCY alters the segment part redundancy for future segments of an existing table. Note that altering a segment part redundancy setting only affects data loaded after applying the SQL statement.

SQL

ALTER TABLE [ IF EXISTS ] table_name ALTER REDUNDANCY segment_part (redundancy_scheme)

Parameter	Type	Description
`table_name`	string	The name of the table that you want to alter.
`segment_part`	string	Specifies the segment part redundancy of the table. Supported values include: `\{ DATA \| MANIFEST \| INDEX \| PDF \| CDE \| STATS }` These settings are defined as follows: `DATA`: The actual data for the table. `MANIFEST`: Header information stored about the data, which describes how to locate any given cluster of rows within the data. `INDEX`: The index of the data used for quicker lookups and better query performance. `STATS`: Used in the optimizer, the probability density function and combinable distinct estimators used to make better optimizations to query plans.
`redundancy_scheme`	string	The redundancy scheme. Supported values include: `\{ COPY \| PARITY }` These settings are defined as follows: `COPY` — The system stores a copy of the bytes throughout the storage cluster to ensure redundancy. This option uses more storage but is faster during rebuilds and node outages. `PARITY` — The system uses the parity encoding specified on the storage cluster, and uses parity bits to ensure redundancy for the data. This option uses less storage but is slower during rebuilds and node outages.

Example This example alters the STATS part to COPY redundancy.

SQL

ALTER TABLE employees ALTER REDUNDANCY STATS (COPY);

ALTER TABLE DROP COLUMN

ALTER TABLE DROP COLUMN drops an existing column from the table. You cannot remove the TimeKey column and the clustering key columns from the table. When you remove a column, the database does not remove or free any actual data.

SQL

ALTER TABLE [ IF EXISTS ] table_name DROP COLUMN column_name [ IF EXISTS ]

Parameter	Type	Description
`table_name`	string	The name of the table to alter.
`column_name`	string	The name of the column to drop.

Example This example removes a column named address from the table employees.

SQL

ALTER TABLE employees DROP COLUMN address;

ALTER TABLE STREAMLOADER_PROPERTIES [#alter-table-streamloader_properties]

ALTER TABLE STREAMLOADER_PROPERTIES resets the table streamloader properties to the provided string. The properties string must be in valid JSON format. The database registers streamloader changes dynamically. Therefore, you do not need to restart nodes or take other actions for the changes to take effect. Any properties not specified in the string default to the system-wide setting.

SQL

ALTER TABLE [ IF EXISTS ] table_name STREAMLOADER_PROPERTIES streamloader_json

Parameter	Type	Description
`table_name`	string	The name of the table to alter.
`streamloader_json`	string	The streamloader properties to alter. See this table for a list of all supported properties.

Configuring Streamloader Properties

STREAMLOADER_PROPERTIES is a field on the table metadata that must be written as a JSON string in order to be read properly. The database can dynamically render any changes to STREAMLOADER_PROPERTIES with the ALTER TABLE SQL statement. You can set Loader Node properties for a new table as a parameter in the CREATE TABLE SQL statement. You do not need to restart the database node for the changes to take effect. Per-Table Streamloader Properties

Parameter	Data type	Description
`pageQueryExclusionDuration`	An integer in nanoseconds (ns) or a string with the suffix `ns`, `us`, `ms`, or `s` appended. For example: `"10s"` = 10 seconds, `"1000us"` = 1,000 microseconds	Per-table configuration for the time interval for pages that should be excluded from queries. The database excludes pages with time column values that are greater than the duration of the query. A value of `0` means the database does not exclude any pages. By default, this value is set to `0` if not specified.

Example This example sets the Loader Node properties of the table employees to {"pageQueryExclusionDuration" : "30s"}. This means that any pages added less than 30 seconds ago will not be included in query results.

SQL

ALTER TABLE employees
    STREAMLOADER_PROPERTIES '{
        "pageQueryExclusionDuration" : "30s"
        }';

ALTER TABLE DISABLE INDEX

The ALTER TABLE DISABLE INDEX statement instructs future queries not to use the specified indexes, but existing segments and new segments continue to have the index available in case you enable the index again. All secondary indexes except for secondary clustering key indexes can be disabled. Trying to disable other types of indexes generates an error. You can specify the index by name or UUID. Syntax

SQL

ALTER TABLE [ IF EXISTS ] table_name DISABLE INDEX
    { index_name_or_uuid | IN (index_name_or_uuid [,...]) }

Parameter	Type	Description
`table_name`	string	The name of the table to alter.
`index_name_or_uuid`	string	The name or UUID of the index to disable. If you specify the index by name, the name must match an existing index. If you specify a UUID instead, it does not have to match an existing index. Therefore, you can disable a dropped index using its UUID, which ensures it is not used within old segments that were loaded with the index. You can get a list of index names and UUIDs in your database by referencing the sys.indexes table in the system catalog.

Examples This example disables an existing index named current_idx on the table employees.

SQL

ALTER TABLE employees DISABLE INDEX current_idx;

This example disables an existing or dropped index with the UUID 5c15d8de-36fa-4055-9bdc-3f1750aaeea0.

SQL

ALTER TABLE employees DISABLE INDEX '5c15d8de-36fa-4055-9bdc-3f1750aaeea0';

This example disables both indexes current_idx and other_idx on the table employees.

SQL

ALTER TABLE employees DISABLE INDEX IN (current_idx, other_idx);

ALTER TABLE ENABLE INDEX

The ALTER TABLE ENABLE INDEX statement reverts the operation performed by the ALTER TABLE DISABLE INDEX statement. Syntax

SQL

ALTER TABLE [ IF EXISTS ] table_name ENABLE INDEX index_name_or_uuid

Parameter	Type	Description
`table_name`	string	The name of the table to alter.
`index_name_or_uuid`	string	The name or UUID of the index to enable. If you specify the index by name, the name must match an existing index. If you specify a UUID instead, it does not have to match an existing index. Therefore, you can disable a dropped index using its UUID, which ensures it is not used within old segments that were loaded with the index. You can get a list of index names and UUIDs in your database by referencing the sys.indexes table in the system catalog.

Examples This example enables an existing index named current_idx on the table employees.

SQL

ALTER TABLE employees ENABLE INDEX current_idx;

This example enables an existing or dropped index with the UUID 5c15d8de-36fa-4055-9bdc-3f1750aaeea0.

SQL

ALTER TABLE employees ENABLE INDEX '5c15d8de-36fa-4055-9bdc-3f1750aaeea0';

This example enables both indexes current_idx and other_idx on the table employees.

SQL

ALTER TABLE employees ENABLE INDEX IN (current_idx, other_idx);

ALTER TABLE ENABLE RETENTION POLICY AGE

This ALTER TABLE SQL statement enacts a new retention policy on the specified table. A table can have only one retention policy. For details about retention policies, see Table Retention Policies. Required Privileges You must have the ALTER and DELETE privileges for the specified table.

Enacting a new retention policy on an existing table already loaded with data might cause the system to delete many rows.

Syntax

SQL

ALTER TABLE [ IF EXISTS ] table_name ENABLE RETENTION POLICY AGE
    retention_granularity retention_value

Parameter	Type	Description
`table_name`	string	The name of the table to alter.
`retention_granularity`	numeric	A number that represents the amount of time before the system deletes data in the table. You must pair this number with a specific time type, represented by the `retention_value` parameter. For details about setting up retention policies, see Table Retention Policies.
`retention_value`	string	The unit of time that represents how long the table retains data. Supported values are: `WEEK`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `MILLISECOND` You must pair this number with the amount of time, represented by the `retention_granularity` parameter. For example, `1 DAY` schedules the table retention to a granularity of one day.

Example This example creates a new retention policy for my_table that deletes any rows older than 1 day.

SQL

ALTER TABLE my_table ENABLE RETENTION POLICY AGE 1 DAY;

ALTER TABLE DISABLE RETENTION POLICY

This ALTER TABLE SQL statement disables a retention policy on the specified table. For details about retention policies, see Table Retention Policies. Required Privileges You must have the ALTER TABLE system privilege and the ALTER privilege for the specified table. Syntax

SQL

ALTER TABLE [ IF EXISTS ] table_name DISABLE RETENTION POLICY

Parameter	Type	Description
`table_name`	string	The name of the table to alter.

Example This example removes the retention policy for my_table.

SQL

ALTER TABLE my_table DISABLE RETENTION POLICY;

DELETE FROM TABLE

Removes rows from the specified table. You can use the WHERE clause to specify the rows to remove. If a DELETE SQL statement lacks the WHERE clause, then the database deletes all rows in the table. To use this statement, you must have the DELETE privilege for the table. For details and examples, see Remove Records from an Ocient System.

DELETE actions cannot be undone.If a DELETE operation fails during execution, the database rolls back the changes and returns to its original state.

Due to limitations of the JDBC API, the reported modified row count might not be accurate for DELETE operations that are larger than two billion rows.

Syntax

SQL

DELETE FROM table_name [ WITH cte ] [ WHERE <filter_clause> ]

Parameter	Type	Description
`table_name`	string	The name of the table, specified as a string, indicates where to delete rows.
`cte`	string	A common table expression that defines temporary data for the `DELETE` statement. For details about using common table expressions, see WITH.
`<filter_clause>`	None	A logical combination of predicates that filter the rows to delete based on one or more columns. For details, see the WHERE clause. The `DELETE` SQL statement removes all rows from a table if you do not include the `WHERE` clause.

Examples Delete Rows from the Table with Filter Criteria This DELETE SQL statement removes all rows in the movies table that have a budget of less than 10000.

SQL

DELETE FROM movies WHERE budget < 10000;

Delete Rows from the Table Using a Common Table Expression This example uses a common table expression using the WITH keyword to find rows representing all transactions that occurred before 2022 that are less than $100. The DELETE SQL statement receives the results from the common table expression. Then, the database executes this statement to delete the corresponding rows.

SQL

DELETE FROM transactions
    WITH old_transactions AS (
        SELECT
            transaction_id
        FROM
        	transactions
    	WHERE transaction_date < '2022-01-01'
        	AND amount < 100
	)
	WHERE transaction_id IN (
		SELECT transaction_id
		FROM old_transactions );

EXPORT TABLE

EXPORT TABLE shows the CREATE TABLE statement for an existing table in the current database. To export a table, you must have the SELECT TABLE privilege for the table.

SQL

EXPORT TABLE table_name

Parameter	Type	Description
`table_name`	string	The name of the table that you want to export.

Example This example exports an existing table in the current database and schema named trades.

SQL

EXPORT TABLE trades;

Output

SQL

CREATE TABLE "admin@system"."trade_test" (
    "id" UUID NOT NULL,
    "ticker_symbol" VARCHAR(1048576) COMPRESSION GDC(2) NOT NULL,
    "t_type" VARCHAR(1048576) COMPRESSION GDC(1) NOT NULL,
    "raw_ticker_data" VARCHAR(1048576) COMPRESSION DYNAMIC NULL,
    "created_at" TIMESTAMP TIME KEY BUCKET(1, HOUR) NOT NULL,
    "array_of_tuples" TUPLE<<VARCHAR(1048576) COMPRESSION DYNAMIC,TINYINT,BIGINT,DOUBLE PRECISION,TIMESTAMP,DATE,TIME,DECIMAL(3,2),POINT,BOOLEAN,BINARY(6),BINARY(8),IP,UUID>>[] NULL,
    CLUSTERING INDEX "idx_ticker_symbol_type" ("ticker_symbol", "t_type"),
    INDEX "idx_type" ("t_type")
)
REDUNDANCY cde (PARITY),
REDUNDANCY manifest (COPY),
REDUNDANCY pdf (PARITY),
REDUNDANCY stats (PARITY),
REDUNDANCY column_metadata (COPY),
REDUNDANCY index (PARITY),
REDUNDANCY summary_stats (PARITY),
REDUNDANCY skip_lists (COPY),
REDUNDANCY data (PARITY),
STORAGESPACE "storage",
SEGMENTSIZE 4,
STREAMLOADER_PROPERTIES '{"pageQueryExclusionDuration" : "30s"}';
CREATE INDEX "new_idx" ON "admin@system"."trade_test" ("raw_ticker_data") USING HASH;

INSERT INTO TABLE

INSERT INTO inserts rows into a table in the current database using literal values, column references, function executions, computed expressions, or column default values. This SQL statement requires the INSERT privilege for the relevant table.

Due to limitations of the JDBC API, the reported modified row count might not be accurate for insert operations that are larger than two billion rows.

Syntax

SQL

INSERT INTO table_name [ ( col1, col2 [, ...] ) ]
    [ WITH cte ] { query | [ DEFAULT VALUES | VALUES [ <rows_to_insert> ] }

<rows_to_insert> ::=
    ( row1_exp1, row1_exp2 [, ...] ),
    ( row2_exp1, row2_exp2 [, ...] )
    [, ...]

Using DEFAULT VALUES inserts a single row where each target column is populated with its column defaults (as defined in the column definition) instead of an explicit VALUES list.For table columns that do not each have a defined default value, the inserted row is NULL. If the column has no default and also has the NOT NULL constraint, the insert operation generates an error.

Parameter	Type	Description
`table_name`	string	The name of the table for insertion.
`col1, col2 [, ... ]`	string	A list of specific columns to insert specific values. This column list defaults to all columns in the table if you do not specify any column names. The `INSERT` statement can use a subset of the table columns. Any columns not included in the statement are populated with their column default value in their column definition. If the column definition does not specify a default, the inserted row is NULL. If the column has no default and also has the `NOT NULL` constraint, the insert operation generates an error.
`cte`	string	A common table expression that defines temporary data for the `INSERT` statement. For details about using common table expressions, see WITH.
`query`	string	A `SELECT` query that defines values or a table and any of its columns that should be inserted into the specified `table_name`.
`row1_exp1, row1_exp2 [, ...]`	string	The expressions to insert into columns in the table. This list must match the number of columns specified in the INSERT statement. Similarly, each expression must match the data type of the column that corresponds to its position. Expressions can be any of the following: Literals: `1`, `3.14`, ‘`abc`’, `DATE '2024-01-01'`, etc. Column references: If your `INSERT` statement includes a common table expression using a `WITH` clause, you can reference columns from the separate table in that clause. Function executions: `ABS(-5)`, `NOW()`, `ST_DISTANCE`, etc. Computed expressions: `price * quantity`, `COALESCE(x, 0)`, etc. Column default values: Use the keyword `DEFAULT` to insert a default value specified in the column definition. If the column definition does not specify a default, the inserted row is NULL. If the column has no default and also has the `NOT NULL` constraint, the insert operation generates an error.

Examples Insert Values from One Column This example inserts the columns from system.table_b into system.table_a.

SQL

INSERT INTO system.table_a SELECT * FROM system.table_b;

Insert Values from Multiple Columns This example inserts the column system.table_b.id_col_b into system.table_a.id_col_a and system.table_b.int_col_b into system.table_a.int_col_a.

SQL

INSERT INTO system.table_a (id_col_a, int_col_a)
    SELECT id_col_b, int_col_b FROM system.table_b;

Insert Literal Values Create a table with product, quantity, and date of sale information with these non-nullable columns:

product — Product identifier
quantity — Quantity of the product sold
sale_date — Date of sale

SQL

CREATE TABLE sales (
    product INT NOT NULL,
    quantity INT NOT NULL,
    sale_date DATE NOT NULL
);

Insert three rows of literal values that represent different sales.

SQL

INSERT INTO sales (product, quantity, sale_date)
    VALUES
        (1, 10, '2023-01-15'),
        (2, 5, '2023-01-20'),
        (1, 8, '2023-02-05');

Insert Values Using a Common Table Expression In this example, a common table expression performs calculations on the sales table before inserting rows into the monthly_sales_summary table. The example uses the monthly_sales_summary table created by this CREATE TABLE statement with these non-nullable columns:

product_id — Product identifier
month — Month part of the date
total_quantity — Total quantity of the product

SQL

CREATE TABLE monthly_sales_summary (
    "product_id" INT NOT NULL,
    "month" DATE NOT NULL,
    "total_quantity" INT NOT NULL
);

The common table expression following the WITH keyword extracts the month from the sale date sale_date and calculates the sum of the quantity sold total_quantity of the product from the sales table before inserting this data. Then, the INSERT SQL statement specifies to insert the data into the monthly_sales_summary table.

SQL

INSERT INTO monthly_sales_summary (product_id, month, total_quantity)
WITH monthly_totals AS (
    SELECT
        product,
        DATE_TRUNC('month', sale_date) AS month,
        SUM(quantity_sold) AS total_quantity
    FROM
        sales
    GROUP BY
        product,
        DATE_TRUNC('month', sale_date)
)
SELECT
    product,
    month,
    total_quantity
FROM
    monthly_totals;

Insert Columns Using Default Values This code utilizes the customers table with these columns:

customer_id — Customer identifier
name — Customer name
status — Customer status with the default active status
created_at — Created date

SQL

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP
);

Use the DEFAULT VALUES keyword to insert one row of default values into the table. For columns that lack a defined default value, the operation inserts a NULL row.

SQL

INSERT INTO customers DEFAULT VALUES;

The resulting row contains all NULL values except for the status column, which has the active default value.

SQL

SELECT * FROM customers;

Output

SQL

   | customer_id | name  | status | created_at |
   | ----------- | ----- | ------ | ---------- |
   |             |       | ACTIVE |            |

Alternatively, you can insert default values by using the DEFAULT keyword as one of the row values in the INSERT statement.

SQL

INSERT INTO customers (customer_id, name, status, created_at)
    VALUES (1, 'Alice', DEFAULT, NULL);

Output

SQL

   | customer_id | name  | status | created_at |
   | ----------- | ----- | ------ | ---------- |
   |             |       | ACTIVE |            |
   | 1           | Alice | ACTIVE |            |

INSERT INTO TABLE USING LOADERS

Specify one or more Loader Nodes for executing the INSERT INTO SQL statement. If you do not use this option, the Ocient System uses all Loader Nodes that are live to execute the SQL statement. This statement is useful for managing loading operations, particularly when balancing multiple loads of different sizes and resource requirements. Alternatively, this statement can also help simplify small batch loads by sourcing the data from a single Loader Node. Syntax

SQL

INSERT INTO TABLE table_name USING LOADERS streamloader [, ... ]
     query

Parameter	Type	Description
`streamloader`	string	A unique name for the Loader Node. Identify the names of Loader Nodes from the `sys.nodes` table by using this query: `SELECT name FROM sys.nodes;` If the name of the streamloader contains special characters, you must enclose it in quotes, such as `"stream-loader1"`.

For the query to execute successfully, the specified names must:

Identify nodes that are live.
Identify nodes that have the Loader role.

Examples This example inserts the column system.table_b.id_col_b into system.table_a.id_col_a and system.table_b.int_col_b into system.table_a.int_col_a. Use the Loader Node named stream-loader1 to execute this SQL statement.

SQL

INSERT INTO system.table_a (id_col_a, int_col_a)
    USING LOADERS "stream-loader1"
    SELECT id_col_b, int_col_b FROM system.table_b;

In this example, execute the same SQL statement with two Loader Nodes named stream-loader2 and stream-loader3.

SQL

INSERT INTO system.table_a (id_col_a, int_col_a)
    USING LOADERS "stream-loader2","stream-loader3"
    SELECT id_col_b, int_col_b FROM system.table_b;

TRUNCATE TABLE

TRUNCATE TABLE removes some or all records from an existing table in the current database. The system deletes the truncated data, but the table and its schema remain intact in the system even if all data is deleted. If the entire table is truncated, Global Dictionary Compression tables remain in place. To truncate a table, you must have the DELETE privilege for the table. To remove a subset of rows from a table, you can use the DELETE FROM TABLE SQL statement. For details and examples of using TRUNCATE, see Remove Records from an Ocient System.

This action cannot be undone and results in data loss.

Syntax

SQL

TRUNCATE TABLE table_name

TRUNCATE TABLE table_name WHERE segment_group_id = <ID>

TRUNCATE TABLE table_name WHERE segment_group_id in (<ID>, ...)

Parameter	Type	Description
`table_name`	string	The name of the table to truncate.
`segment_group_id`	numeric	Identifier of the segment group.

Examples This example truncates an existing table in the current database and schema named students.

SQL

TRUNCATE TABLE students;

This example truncates an existing table in the current database named us.students.

SQL

TRUNCATE TABLE us.students;

This example truncates a single segment group from an existing table in the current database named students.

SQL

TRUNCATE TABLE students WHERE segment_group_id = 123456789;

This example truncates a number of segment groups from an existing table in the current database named us.students.

SQL

TRUNCATE TABLE us.students WHERE segment_group_id IN (1,2,3,4,5);

Core Elements of an Ocient System General SQL Syntax Database Password Security Settings Data Control Language (DCL) Statement Reference System Catalog

​CREATE TABLE

​Column Definition (<column_definition>)

​Clustering Key and Index Definition (<clustering_definition>)

​TimeKey Definition (<timekey_definition>)

​Column Constraint (<column_constraint>)

​Create Option (<create_option>)

​CREATE TABLE AS SELECT (CTAS)

​Default Table Definitions

​CTAS USING LOADERS

​DROP TABLE

​ALTER TABLE

​ALTER TABLE RENAME

​ALTER TABLE RENAME COLUMN

​ALTER TABLE ADD COLUMN

​ALTER TABLE ALTER COLUMN COMPRESSION

​ALTER TABLE ALTER REDUNDANCY

​ALTER TABLE DROP COLUMN

​ALTER TABLE STREAMLOADER_PROPERTIES [#alter-table-streamloader_properties]

​Configuring Streamloader Properties

​ALTER TABLE DISABLE INDEX

​ALTER TABLE ENABLE INDEX

​ALTER TABLE ENABLE RETENTION POLICY AGE

​ALTER TABLE DISABLE RETENTION POLICY

​DELETE FROM TABLE

​EXPORT TABLE

​INSERT INTO TABLE

​INSERT INTO TABLE USING LOADERS

​TRUNCATE TABLE

​Related Links

CREATE TABLE

Column Definition (`<column_definition>`)

Clustering Key and Index Definition (`<clustering_definition>`)

TimeKey Definition (`<timekey_definition>`)

Column Constraint (`<column_constraint>`)

Create Option (`<create_option>`)

CREATE TABLE AS SELECT (CTAS)

Default Table Definitions

CTAS USING LOADERS

DROP TABLE

ALTER TABLE

ALTER TABLE RENAME

ALTER TABLE RENAME COLUMN

ALTER TABLE ADD COLUMN

ALTER TABLE ALTER COLUMN COMPRESSION

ALTER TABLE ALTER REDUNDANCY

ALTER TABLE DROP COLUMN

ALTER TABLE STREAMLOADER_PROPERTIES [#alter-table-streamloader_properties]

Configuring Streamloader Properties

ALTER TABLE DISABLE INDEX

ALTER TABLE ENABLE INDEX

ALTER TABLE ENABLE RETENTION POLICY AGE

ALTER TABLE DISABLE RETENTION POLICY

DELETE FROM TABLE

EXPORT TABLE

INSERT INTO TABLE

INSERT INTO TABLE USING LOADERS

TRUNCATE TABLE

Related Links