SQL Reference
Data Definition Language (DDL)...

Database, Tables, Views, and Indexes

This group of DDL commands allows database administrators to manage elements of the database and schema design. Database administrators can create and modify databases including SSO authentication settings. These commands also create, modify, drop, and export Tables, Views, and Indexes in the database as well as truncate segments on a Table. Additional commands are available for unique settings like segment redundancy, compression settings, streamloader properties.

DATABASE

CREATE DATABASE

CREATE DATABASE creates a new database. The database name must be distinct from the name of any existing database in the system.

To create a database, you must possess the CREATE DATABASE privilege for the current system.

Syntax

SQL


Parameter

Type

Description

database_name

string

A unique identifier for the database. The system generates an error if a duplicate name is provided.

system is a reserved database name and can neither be created nor dropped.

Example

To create a new database called ocient:

SQL


DROP DATABASE

DROP DATABASE removes an existing database. This SQL statement also disconnects all users currently connected to the database.

To remove a database, you must possess the DROP DATABASE privilege for the current database.

You cannot drop a database while it has any PIPELINE in a running status.

The DROP DATABASE SQL statement removes the existing database and all created users, tables, and views.

This action cannot be undone.

Syntax

SQL


Parameter

Type

Description

database_name

string

An identifier for the database to be dropped.

You can drop multiple databases by specifying additional database names and separating each with commas.

system is a reserved database name and can neither be created nor dropped.

Example

To remove an existing database named ocient:

SQL


ALTER DATABASE

ALTER DATABASE RENAME

ALTER DATABASE RENAME renames an existing database.

To rename a database, you must possess the ALTER DATABASE privilege for the database.

Syntax

SQL


Parameter

Type

Description

old_database_name

string

The old identifier of the database for rename.

new_database_name

string

The new identifier of the database for rename.

Example

To rename an existing database called oracle to ocient:

SQL


ALTER DATABASE SET / ALTER SSO INTEGRATION

ALTER DATABASE SET SSO CONNECTION configures the database to authenticate using an external SSO provider.

To set or alter a connection, you must be a system-level user or a database administrator and be connected to the database.

See Configuring the Ocient Database for more information on configuring SSO protocols.

SQL



Parameter

Type

Description

database

string

The identifier of the database to be configured.

sso_protocol

string

Supported values include: oidc, openid, and openid_connect All these values are aliases of each other, and use OAuth 2.0 Authorization.

property_name

string

One or more properties to include in the SSO integration. Each SSO property must be specified either as a literal, list, or map. See the SSO Properties table to see the requirements for each supported SSO parameter.



SSO properties also have the following rules:



- You can place any string key or value between double quotations (e.g., default_group OR "default_group").

- String values that contain characters other than [a-zA-z] | [0-9] | '_' require double quotations (e.g., "this.is.a.complex-$tring").

- Using a NULL value clears the existing configuration of a list or map property (e.g., user_claim_ids = NULL).



Name

Required

Value Type

Default Value

Description

issuer

Yes

string

N/A

The complete URL for the OAuth 2.0 and OpenID Connect Authorization Server. This property value is the expected \"iss\" claim in access tokens validated by the database.

client_id

Yes

string

N/A

The client identifier as registered with the OpenID Provider.

default_group

Yes

string

N/A

The group users are assigned if no group is specified in the group_claim_mappings property for the OpenID Provider.

disabled

No

Boolean

false

Set to true to disable the OIDC integration for maintenance temporarily.

⚠️ If you set this property to true, all authentication requests using this connection fail.

enable_id_token_authentication

No

Boolean

false

Set to true if the id_token also contains the authorization token.

In most circumstances, this option is necessary only for machine-to-machine connections without user interaction, such as a server using a script to connect to an Ocient System.

user_id_claims

No

list of strings (e.g., value [, ...]

["email"]

Set the identifier token claims used to identify users in audit trails. If you do not set a value, the system uses the "email" claim if it is present, otherwise it uses ["iss", "sub"].

additional_scopes

No

list of strings (e.g., value [, ...]

[]

Specifies additional scopes to request when executing the Authorization Code Flow.

additional_audiences

No

list of strings (e.g., value [, ...]

[]

Specifies additional audiences to accept when validating tokens. This property is useful for authorization servers without token exchange capability.

Each included additional audience is a case-sensitive URL from a provider, similar to the issuer value.

groups_claim_ids

No

list of strings (e.g., value [, ...]

[]

Specifies the token claims that can be used to map the user to a Database group. If you specify the groups_claim_mappings property, you must also specify the groups_claim_ids property.

groups_claim_mappings

No

map of strings (e.g., key = value [, ...])

{}

Specifies mappings from the Provider group to the Database group.

roles_claim_ids

No

list of strings (e.g., value [, ...]

[]

Specifies the token claims that can be used to map the user to a Database role. If you specify the roles_claim_mappings property, you must also specify the roles_claim_ids property.

roles_claim_mappings

No

map of strings (e.g., key = value [, ...])

{}

Specifies mappings from the Provider role to the Database role.

blocked_groups

No

list of strings (e.g., value [, ...]

[]

Specifies Provider groups that are restricted from connecting to the database.

blocked_roles

No

list of strings (e.g., value [, ...]

[]

Specifies Provider roles that are restricted from connecting to the database.



Example

This example sets an OpenID Connect connection on an existing database named example_database.

SQL


This example configures a wider assortment of SSO properties, including those that require list and map value types.

SQL



ALTER DATABASE REMOVE SSO INTEGRATION

ALTER DATABASE REMOVE SSO INTEGRATION removes an existing SSO connection from the database.

To remove a connection, you must be a system-level user or a database administrator.

Syntax

SQL


Parameter

Type

Description

database

string

The identifier of the database to be configured.

Example

To remove an existing connection from a database named example_database:

SQL


TABLE

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. The bucket resolution defaults to 1 day if you do not provide a value. Tables with a specified TimeKey can perform query operations faster, especially if they involve time filtering.  

You can specify a clustering index composed of one or more fixed-length columns, all of which become the cluster key for the table. If one or more clustering indexes are defined, you must specify them all in a contiguous and ascending order. Designating columns as cluster keys that are frequently referenced in queries can greatly improve performance. 

For more information on defining TimeKeys and clustering indexes, see Time Keys, Clustering Keys, and Indexes.

See the Data Types section for table-supported data types.

To create a table, you must possess the CREATE TABLE privilege for the current database. 

Syntax

SQL


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 more information, see CREATE TABLE AS SELECT.

Create Option (<create_option>)

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

Parameter

Type

Description

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 given 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 more information, see ALTER TABLE STREAMLOADER PROPERTIES.

clustering_index

string

An identifier for the clustering index.

For more information on clustering indexes, see Time Keys, Clustering Keys, and Indexes.

clustering_column

string

A column to be included in the clustering index. Each clustering_column that is specified becomes a clustering key.

If you define a clustering index, you must specify all clustering index columns in a contiguous and ascending order.

index_column

string

A designated column for an index. For more information on specifying index columns, see CREATE INDEX.

index_name

string

The identifier for an index to be created for the table.

index_type

string

The type of index to be created. For more information, see CREATE INDEX.

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.

Index Definition (<index_definition>)

The parameters listed here are required for defining an index on a table.

Parameter

Type

Description

index_column

string

A designed column for an index. For more information on specifying index columns, see CREATE INDEX.

index_name

string

The identifier for an index to be created for the table.

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.

Parameter

Type

Description

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 of to a fixed width of one day.

The bucket resolution defaults to 1, DAY if no value is provided.

literal

depends on the column data type

If specified as a constraint, sets the default value for the column.

The value must be a literal that is enclosed in quotes. Do not include an expression with this argument to cast this value to the data type of the column. The system automatically attempts type coercion to convert the literal to the data type of the column.

For example, this CREATE TABLE 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.

comment

string

An optional comment for the column.

compression_value

numeric

An integer value of either 1, 2 or 4 that is used to define the storage of COMPRESSION GDC. The compression of the values are 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 will reuse 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.



COMPRESSION ZSTD applies to VARCHAR columns as well as other data types.

For the COMPRESSION DYNAMIC setting, applies LZ4 compression only if the column data is dynamically determined to be compressible. For fixed-length columns, dynamic applies delta-delta compression.

For more details about setting column compression, see ALTER TABLE ALTER COLUMN COMPRESSION.

Example

This example creates a new table in the current database and schema called 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 AS SELECT (CTAS)

CTAS provides the ability to create and load new tables from the result of a query on one or more existing tables. 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 the user receives a response to the CTAS command, 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 results of the query. You can use an alternative schema provided the results of the query can automatically cast to the target column types. CTAS also supports all options for the new table. CTAS does not support default values and explicit nullable definitions on the column of the table.

Unlike the regular CREATE TABLE SQL statement, the database can infer the TimeKey column from the table definition even if it is not explicitly defined.

CTAS statements support secondary and prefix indexes.

To create a table, you must possess 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.

Examples

Create and load a new table in the current database on the existing schema my_schema named my_ctas_table.

Columns can derive their type from the SELECT SQL statement as shown by the id column in this example.

SQL


To create and load a new table called my_ctas_table_2 with all column definitions derived from the SELECT statement:

SQL


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 LOADERS

Specify one or more Loader Nodes for executing the CTAS SQL statement. If you do not use this option, the system uses all Loader Nodes that are live to execute the SQL statement.

Syntax

SQL


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


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

SQL


DROP TABLE

DROP TABLE removes one or more existing tables in the current database, along with all associated views. Note that this action cannot be undone.

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

Syntax

SQL


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


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

SQL


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.

To rename a table, you must possess the ALTER TABLE privilege for the table.

Syntax

SQL


Parameter

Type

Description

table_name

string

A unique identifier for the table.

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


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

SQL


ALTER TABLE RENAME COLUMN

ALTER TABLE RENAME COLUMN renames an existing column.

To rename a column, you must possess the ALTER TABLE privilege for the table.

Syntax

SQL


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 ADD COLUMN

ALTER TABLE ADD COLUMN adds a new column to the table.

To add a column, you must possess 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 Constraints.

Syntax

SQL


Parameter

Type

Description

table_name

string

A unique identifier for the table.

Examples

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

SQL


This example adds a column that is nullable.

SQL


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 LZ4, COMPRESSION DYNAMIC and ZSTD.

Note that altering the compression setting of a column only affects compression for data loaded after you apply the command.

Syntax

SQL


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 compression schemes include the following:

[ COMPRESSION NONE | COMPRESSION DYNAMIC | COMPRESSION ZSTD ]

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 additional parameters as follows:

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


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 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 will only affect data loaded after applying the SQL statement.

SQL


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:



PARITY: Using the parity encoding specified on the storage cluster, will use parity bits to ensure redundancy for the data. This option uses less storage but is slower during rebuilds and node outages.

COPY: A copy of the bytes will be stored throughout the storage cluster to ensure redundancy. This option uses more storage but is faster during rebuilds and node outages.

Example

This example alters the STATS part to COPY redundancy:

SQL


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


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


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

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 DISABLE INDEX

The ALTER TABLE DISABLE INDEX statement instructs future queries to not use the specified indexes, but existing segments and new segments will 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


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. You can therefore 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


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

SQL


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

SQL


ALTER TABLE ENABLE INDEX

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

Syntax

SQL


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. You can therefore 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


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

SQL


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

SQL


DELETE FROM TABLE

Removes rows from the specified table. You can use the WHERE clause to specify the rows to remove.

If a DELETE command 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 more information and examples for using DELETE, see Remove Records from an Ocient System.

DELETE actions cannot be undone.

If a DELETE operation fails during execution, the data rolls back to its original state.

Syntax

SQL


Parameter

Type

Description

table_name

string

The name of the table, specified as a string, that indicates where to delete rows.

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

Example

In this example, the DELETE SQL statement removes all rows in the movies table that have a budget of less than 10000.

SQL


EXPORT TABLE

EXPORT TABLE shows the CREATE TABLE statement for an existing table in the current database.

To export a table, you must possess the SELECT TABLE privilege for the table.

SQL


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


Output

SQL


INSERT INTO TABLE

INSERT INTO inserts rows into a table in the current database using the results of a SQL query. The column list of the query must match the columns in the table and the query results.

The column list defaults to all columns in the table if you do not specify any column names. The database does not require every column in the table to be populated from the query. If you do not choose a column to be populated, the database inserts default values for the column. The database inserts NULL values if the column does not have a default value and the column can contain NULL. If neither a default value nor NULL can be inserted, the operation fails.

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


Parameter

Type

Description

table_name

string

The name of the table to insert into.

column_name

string

The name of the columns in the table to insert into.

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.

Examples

This example inserts the columns from system.table_b into system.table_a in the same order.

SQL


This example inserts 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


You can also insert literal values. The CREATE TABLE statement is included to demonstrate the table schema.

SQL


You can insert multiple rows by using a UNION ALL clause.

SQL


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.

Syntax

SQL


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


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

SQL


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 SQL statement.

For more information and examples for using TRUNCATE, see Remove Records from an Ocient System.

This action cannot be undone.

Syntax

SQL


Parameter

Type

Description

table_name

string

The name of the table to truncate.

Examples

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

SQL


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

SQL


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

SQL


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

SQL


VIEW

CREATE VIEW

CREATE VIEW creates a new view in the current database or replaces an existing view. For view creation, the name of the view must be distinct from the name of any existing views in the database.

To create a view, you must have both the CREATE VIEW privilege for the current database and the SELECT privilege on all referenced tables and views in the query.

Syntax

SQL


Parameter

Type

Description

view_name

string

A distinct identifier used to name the view.

query

string

A SELECT query that defines the data from a table used to create the view.

Example

This example creates an existing view in the current database and schema called option_trades.

SQL


DROP VIEW

DROP VIEW removes one or more existing views in the current database.

To remove a view, you must possess the DROP VIEW privilege for the view.

Syntax

SQL


Parameter

Type

Description

view_name

string

The identifier of the view to drop.

You can drop multiple views by specifying additional view names and separating each with commas.

Example

This example drops an existing view in the current database and schema named star_employees.

SQL


This example drops two views named star_employees and bad_employees.

SQL


ALTER VIEW RENAME

ALTER VIEW RENAME renames an existing view.

To rename a view, you must possess the ALTER VIEW privilege for the view.

Syntax

SQL


Parameter

Type

Description

old_view_name

string

The identifier of the view to rename.

new_view_name

string

The new name for the specified view.

Examples

This example renames an existing view in the current database and schema called star_employees to star_mid_west_employees.

SQL


This example renames an existing view in the current database called us.star_employees to us.star_mid_west_employees.

SQL


ALTER VIEW AS

ALTER VIEW AS modifies an inner query of the existing view.

To modify the query for an existing view, you must be a system-level user or possess the ALTER VIEW privilege for the view. You must also have the SELECT privilege on all referenced tables and views in the new query.

Syntax

SQL


Parameter

Type

Description

view_name

string

The identifier for the view to alter.

query

string

A SELECT query that defines the new data from a table used to alter the view.

Example

This example alters a view in the current database and schema called star_employees.

SQL


EXPORT VIEW

EXPORT VIEW shows the CREATE VIEW statement for an existing view in the current database.

To export a view, the logged-in user must be a system-level user or possess the READ VIEW right for the view.

Syntax

SQL


Parameter

Type

Description

view_name

string

The identifier for the view to export.

Example

This example exports an existing view in the current database and schema called students.

SQL


INDEX

CREATE INDEX

CREATE INDEX creates a new secondary index.

Indexes help optimize database queries when created on columns that are frequently referenced. For more information on how indexes operate, see Secondary Indexes.

Creating an index does not trigger re-indexing of existing segments. Only segments generated after the CREATE INDEX is issued will contain the new index.

Indexes can be created on columns containing various different datatypes as long as the requirements are met. Please note that depending on the data type, the system can assign different index types by default if you decline to specify which index type to use. The name must be distinct from the name of any existing index on the table.

You can apply indexes regardless of whether they have GDC compression.

Syntax

SQL


Parameter

Type

Description

index_name

string

An identifier for the index to create.

The name must be distinct from the name of any existing index on the table.

table

string

The name of the table for the index.

column_name

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 type or parameters.

n_value

integer

Optional.

When used with a NGRAM index, this numeric value specifies the character length of the substrings to be indexed.

If unspecified, this value defaults to 3.

Index Types (<index_type>)

Ocient supports four index types alongside the clustering index: INVERTED, HASH, NGRAM, and SPATIAL.

An index notionally stores a mapping of column value to the rows that contain that value, and the index type differentiates the format in which the column values are stored and accessed.

Unless an index type is explicitly specified with a USING clause, the data type of a column determines a default index type that the system creates. For information on index type defaults, see Index Type Requirements and Defaults.

For container datatypes (e.g., arrays and tuples), the index stores the internal elements of the container, and is used on predicates that target the internal values. However, a mapping of NULL column values is generally stored for both scalar and container datatypes, so the index can always be used for column IS NULL predicates.

Index Type

Primary Data Types

Primary Usage Description

INVERTED

Fixed-length numeric columns.

Stores whole column value internally, meaning its storage size is approximately the same as the width of the data type.

Supports lookups using strict equality or range comparisons.

For more information, see the INVERTED index section.

HASH

Variable-length character columns

Stores a hash of the indexed column value rather than the full value.

Primarily used for exact comparisons.

For more information, see the HASH index section.

NGRAM

Variable-length character columns

Stores substrings equal in size to its n_value.

Storage requirements can greatly vary depending on column data size, width and cardinality.

Supports exact string comparison and filters including LIKE, NOT LIKE, SIMILAR TO and NOT SIMILAR TO.

For more information, see the NGRAM index section.

SPATIAL

Geospatial columns (POINT, LINESTRING, POLYGON)

Groups geographic objects for bounding-box filtering.

For more information, see the SPATIAL index section.

For further description and examples of the index types, see Secondary Indexes.

Examples

This example creates an index called new_idx on the address column of the table. Because address is a VARCHAR column, this index defaults to the HASH index type.

SQL


This example creates an index of type NGRAM on the address column. As the NGRAM has no specified n_value, it defaults to indexing substrings of three characters long.

SQL


This example creates an index on a component of the tuple_col column. As this column is of data type INT, the index defaults to using the INVERTED type.

SQL


This example creates an index on a component of the point_col column. As this column is of data type POINT, the index defaults to using the SPATIAL type.

SQL


DROP INDEX

DROP INDEX drops a secondary index on a table.

After an index is dropped, new segments that are generated will not contain the new index. However, no existing segments will be altered. This means that until a segment is rebuilt, you can still use the removed index internally and the system does not reclaim the storage space the removed index occupied.

SQL


Parameter

Type

Description

index_name

string

An identifier for the index to drop.

table_name

string

The name of the table with the index to drop.

Example

This example drops the index called new_idx on the employees table.

SQL


Related Links