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

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;

Core Elements of an Ocient System Data Query Language (DQL) Statement Reference Database Password Security Settings Data Control Language (DCL) Statement Reference Data Manipulation Language (DML) 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

​EXPORT 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

EXPORT TABLE

Related Links