> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Tables
> Manage tables in Ocient with SQL DDL, including CREATE TABLE, ALTER TABLE, and DROP TABLE, plus guidance on time keys, clustering keys, and partitioning.
export const TimeKey = "TimeKey®";
export const Ocient = "Ocient®";
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](/databases), [Schemas](/schemas), [Views](/views), and [Indexes](/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 {TimeKey} 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](/timekeys-and-clustering-keys).
See the [Data Types](/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](/create-table-sql-statement-examples).
**Syntax**
```sql SQL theme={null}
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name
[ ( [, ...] | ) ]
[ [ WITH ] [, ... ] ]
[ AS (query) ]
::=
column_name [ data_type ] [ ] [ ] [, ...] ]
::=
TIME KEY BUCKET (bucket_granularity, bucket_value)
::=
| [ NOT ] NULL
| DEFAULT expression
| COMMENT comment
| COMPRESSION GDC [ (compression_value), EXISTING schema_name ]
| COMPRESSION [ compression_scheme ]
::=
CLUSTERING KEY key_name (ck_col1, ck_col2 [, ...])
[, INDEX index_name (idx_col1, idx_col2 [, ...])
[, INDEX ... ] ]
::=
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 ''
```
| **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](#create-table-as-select-ctas) . |
### 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](/data-types). |
### Clustering Key and Index **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](/timekeys-and-clustering-keys#clustering-key).
```sql SQL theme={null} theme={null}
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](/tables#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 (``)
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](#create-table).
For details about using TimeKeys, see [TimeKeys](/timekeys-and-clustering-keys#timekeys).
```sql SQL theme={null} theme={null}
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 (``)
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](/timekeys-and-clustering-keys).
| **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](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Geometric_objects).
**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<>`
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 {Ocient} 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](/table-compression-options). |
### 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](/secondary-indexes#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](/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](#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 SQL theme={null}
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<>[],
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](#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](#create-option-\))
To include these table definit*i*ons, 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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
EXPORT TABLE basic_ctas;
```
Output
```none Text theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
DROP TABLE employees;
```
In this example, drop two tables in the current database and schema named `employees` and `departments`.
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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](#column-definition-\). For constraints, see [Column Constraint](#column-constraint-\).
**Syntax**
```sql SQL theme={null}
ALTER TABLE [ IF EXISTS ] table_name ADD COLUMN ;
::=
column_name [ data_type ] [ [, ...] ]
::=
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 SQL theme={null}
ALTER TABLE employees ADD COLUMN
new_column BIGINT
NOT NULL
DEFAULT 0;
```
This example adds a column that is nullable.
```sql SQL theme={null}
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](/table-compression-options).
**Syntax**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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](/system-catalog#sys-indexes) table in the system catalog. |
**Examples**
This example disables an existing index named `current_idx` on the table `employees`.
```sql SQL theme={null}
ALTER TABLE employees DISABLE INDEX current_idx;
```
This example disables an existing or dropped index with the UUID `5c15d8de-36fa-4055-9bdc-3f1750aaeea0`.
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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](/system-catalog) table in the system catalog. |
**Examples**
This example enables an existing index named `current_idx` on the table `employees`.
```sql SQL theme={null}
ALTER TABLE employees ENABLE INDEX current_idx;
```
This example enables an existing or dropped index with the UUID `5c15d8de-36fa-4055-9bdc-3f1750aaeea0`.
```sql SQL theme={null}
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 SQL theme={null}
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](/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 SQL theme={null}
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](/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 SQL theme={null}
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](/table-retention-policies).
**Required Privileges**
You must have the `ALTER TABLE` system privilege and the `ALTER` privilege for the specified table.
**Syntax**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
EXPORT TABLE trades;
```
Output
```sql SQL theme={null}
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<>[] 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;
```
## Related Links
[Core Elements of an Ocient System](/core-elements-of-an-ocient-system)
[Data Query Language (DQL) Statement Reference](/data-query-language-dql-statement-reference)
[Database Password Security Settings](/database-password-security-settings)
[Data Control Language (DCL) Statement Reference](/data-control-language-dcl-statement-reference)
[Data Manipulation Language (DML) Statement Reference](/data-manipulation-language-dml-statement-reference)
[System Catalog](/system-catalog)