CREATE TABLE SQL Statement Examples

﻿
﻿Tables in an 
﻿ contain various configuration options that can impact performance, reduce storage requirements, or provide extra data safeguards. 
As some table configuration options cannot be added after data is already loaded, it is important that you take the time to design CREATE TABLE SQL statements with forethought for the queries that you expect to execute frequently. This topic provides examples of CREATE TABLE SQL statements designed for different use cases and describes the configuration choices. 
For syntax and parameter information, see CREATE TABLE﻿. 
Create a Table for Real-Time Analysis﻿
This example assumes you plan to ingest large quantities of time series data for rapid querying. The most important columns that you plan to reference in queries frequently include:
created_at — A granular timestamp column that represents the primary time reference in the table. 
user_id — An integer column to identify each unique user. 
sell_id — An integer column representing each transaction by a user. 
SQL
CREATE TABLE IF NOT EXISTS "transact_data" (
    "created_at" TIMESTAMP TIME KEY BUCKET(1, HOUR) NOT NULL,
    "user_id" INT NOT NULL DEFAULT 0, 
    "sell_id" BIGINT NOT NULL,
    "purchase_amount" BIGINT NOT NULL DEFAULT 0,
    "buyer_name" VARCHAR(1048576),
    CLUSTERING KEY "primary_index" ("sell_id", "user_id") 
)

CREATE INDEX "idx_02" ON "transact_data" ("purchase_amount");
CREATE INDEX "idx_03" ON "transact_data" ("buyer_name") 
    USING NGRAM(3);
CREATE TABLE IF NOT EXISTS "transact_data" (
    "created_at" TIMESTAMP TIME KEY BUCKET(1, HOUR) NOT NULL,
    "user_id" INT NOT NULL DEFAULT 0, 
    "sell_id" BIGINT NOT NULL,
    "purchase_amount" BIGINT NOT NULL DEFAULT 0,
    "buyer_name" VARCHAR(1048576),
    CLUSTERING KEY "primary_index" ("sell_id", "user_id") 
)

CREATE INDEX "idx_02" ON "transact_data" ("purchase_amount");
CREATE INDEX "idx_03" ON "transact_data" ("buyer_name") 
    USING NGRAM(3);
﻿
This example table makes use of these Ocient System configurations.
Column Name
Configuration Description
created_at
This column is the table 
﻿, which means that the database indexes for filter optimization. 
The example configures the TimeKey to partition segments by every hour, (1, HOUR), which is the approximate time granularity you expect to use in your query filters. 
user_id, sell_id
The example table identifies these two columns as the table Clustering Key. The database pairs these two columns for quick reference, such as filtering one column for row values of the other.  
purchase_amount
The CREATE INDEX SQL statement adds a secondary index to this column. 
As a BIGINT data type, the purchase_amount column defaults to the INVERTED index. 
buyer_name
This CREATE INDEX SQL statement adds a secondary index to this column. 
As a VARCHAR column, the buyer_name column normally defaults to a HASH index, but this example specifies it should be an NGRAM index instead. 
For details about using the TimeKey and Clustering Key functionality, see Time Keys, Clustering Keys, and Indexes﻿. 
For details about other index types, see Secondary Indexes﻿. 
Create a Table for Geospatial Data﻿
This example assumes you plan to ingest geospatial data linked to time series data using 
﻿. The most important columns that you plan to reference frequently in queries include:
route_time — A granular timestamp column that represents the primary time reference in the table. 
start_point, end_point — The geospatial point values for the start and destination locations. 
trip_id — An integer column to identify each unique trip. 
vehicle_id — An integer column that represents each vehicle. 
SQL
CREATE TABLE "trip_details" (
    "route_time" TIMESTAMP TIME KEY BUCKET(14, DAY) NOT NULL,
    "start_point" POINT NULL,
    "end_point" POINT NOT NULL DEFAULT 'POINT(0 0)',
    "route_path" LINESTRING NOT NULL
        DEFAULT 'LINESTRING(0 0, 1 1)',
    "route_area" POLYGON NOT NULL 
        DEFAULT 'POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))',
    "trip_id" INT NOT NULL,
    "vehicle_id" BIGINT NOT NULL,
    CLUSTERING KEY "idx01" ("trip_id", "vehicle_id")
);

CREATE INDEX "idx02" on "trip_details" ("start_point") 
    USING SPATIAL; 
CREATE INDEX "idx03" on "trip_details" ("end_point") 
    USING SPATIAL; 
CREATE TABLE "trip_details" (
    "route_time" TIMESTAMP TIME KEY BUCKET(14, DAY) NOT NULL,
    "start_point" POINT NULL,
    "end_point" POINT NOT NULL DEFAULT 'POINT(0 0)',
    "route_path" LINESTRING NOT NULL
        DEFAULT 'LINESTRING(0 0, 1 1)',
    "route_area" POLYGON NOT NULL 
        DEFAULT 'POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))',
    "trip_id" INT NOT NULL,
    "vehicle_id" BIGINT NOT NULL,
    CLUSTERING KEY "idx01" ("trip_id", "vehicle_id")
);

CREATE INDEX "idx02" on "trip_details" ("start_point") 
    USING SPATIAL; 
CREATE INDEX "idx03" on "trip_details" ("end_point") 
    USING SPATIAL; 
﻿
This example table makes use of these Ocient System configurations.
Column Name
Configuration Description
route_time
This column is the table 
﻿, which means that the database indexes for filter optimization.
The example configures the TimeKey to partition segments by every two weeks (14, DAY), which is the approximate time granularity that you expect to use in your query filters. 
trip_id, vehicle_id
The example table identifies these two columns as the table Clustering Key. The database pairs these two columns for quick reference, such as filtering one column for row values of the other.  
end_point, route_path, route_area
These columns have POINT, LINESTRING, and POLYGON data types, respectively. 
Each of these geospatial columns includes default values in their respective formats. 
start_point, end_point
The CREATE INDEX SQL statements for both of these columns assign SPATIAL indexes for quicker reference. This index is the default type for any column with a geospatial data type. 
For details about OcientGeo functionality, see Geospatial Functions﻿. 
Create a Table With Compression Options
This example table includes multiple compression options to ensure minimal storage for specific columns and extra performance. 
Most data types except arrays have COMPRESSION DYNAMIC applied by default.  
SQL
CREATE TABLE "call_records" (
    "utc_timestamp" TIMESTAMP TIME KEY BUCKET(1, DAY) NOT NULL,
    "sectorid" VARCHAR(500) 
        COMPRESSION ZSTD,
    "address" VARCHAR(500)
        COMPRESSION NONE, 
    "call_type" VARCHAR(500) 
        COMPRESSION ZSTD
        COMPRESSION GDC(4) NULL,
    "access_information" VARCHAR(500) 
        COMPRESSION GDC(4) NULL,
    "file_name" VARCHAR(500) 
        COMPRESSION ZSTD compression_level = 0, 
        dictionary_size = 32768 NULL,
    CLUSTERING KEY "idx01" ("call_type", "access_information")
)
CREATE TABLE "call_records" (
    "utc_timestamp" TIMESTAMP TIME KEY BUCKET(1, DAY) NOT NULL,
    "sectorid" VARCHAR(500) 
        COMPRESSION ZSTD,
    "address" VARCHAR(500)
        COMPRESSION NONE, 
    "call_type" VARCHAR(500) 
        COMPRESSION ZSTD
        COMPRESSION GDC(4) NULL,
    "access_information" VARCHAR(500) 
        COMPRESSION GDC(4) NULL,
    "file_name" VARCHAR(500) 
        COMPRESSION ZSTD compression_level = 0, 
        dictionary_size = 32768 NULL,
    CLUSTERING KEY "idx01" ("call_type", "access_information")
)
﻿
This example table makes use of these Ocient System configurations.
Column name
Configuration description
address
This column explicitly removes compression by specifying COMPRESSION NONE in the CREATE TABLE SQL statement.
call_type, access_information
These columns have VARCHAR data types configured with Global Dictionary Compression (GDC). 
This compression scheme maps the variable-length type rows to integers. Among other benefits, this compression allows both columns to support a Clustering Key for faster querying. 
The call_type column stacks multiple compression schemes by including both GDC and ZSTD compression to provide additional storage reduction.
For details about Ocient compression schemes, see Table Compression Options﻿.
Create a Table for Various Container Types﻿
This example table includes complex container data types that include a two-dimensional array, a tuple with variable-length values, and a tuple with various data types. 
SQL
CREATE TABLE "container_data" (
    "utc_timestamp" TIMESTAMP TIME KEY BUCKET(1, DAY) NOT NULL,
    "user_id" INT NOT NULL DEFAULT 0,
    "sell_id" INT NOT NULL DEFAULT 0,
    "point_array_col" POINT[] NULL, 
    "array_col" VARCHAR(500)[] COMPRESSION GDC(4) NULL, 
    "2D_array_col" INT[][],
    "tuple_col1" TUPLE<<
        VARCHAR(255),
        VARCHAR(500)
        >>,
    "tuple_col2" TUPLE<<
        INT,
        INT[], 
        VARCHAR(255) COMPRESSION GDC(1) COMPRESSION ZSTD, 
        VARCHAR(255) COMPRESSION DYNAMIC    
        >>,
    "matrix_col" MATRIX[5][10], 
    CLUSTERING INDEX "idx01" ("user_id", "sell_id")
)

CREATE INDEX "idx02" on "container_data" ("point_array_col")
    USING SPATIAL;
CREATE INDEX "idx03" on "container_data" ("tuple_col1"[1]) 
    USING HASH; 
CREATE INDEX "idx04" on "container_data" ("tuple_col2"[2]) 
    USING INVERTED;     
CREATE TABLE "container_data" (
    "utc_timestamp" TIMESTAMP TIME KEY BUCKET(1, DAY) NOT NULL,
    "user_id" INT NOT NULL DEFAULT 0,
    "sell_id" INT NOT NULL DEFAULT 0,
    "point_array_col" POINT[] NULL, 
    "array_col" VARCHAR(500)[] COMPRESSION GDC(4) NULL, 
    "2D_array_col" INT[][],
    "tuple_col1" TUPLE<<
        VARCHAR(255),
        VARCHAR(500)
        >>,
    "tuple_col2" TUPLE<<
        INT,
        INT[], 
        VARCHAR(255) COMPRESSION GDC(1) COMPRESSION ZSTD, 
        VARCHAR(255) COMPRESSION DYNAMIC    
        >>,
    "matrix_col" MATRIX[5][10], 
    CLUSTERING INDEX "idx01" ("user_id", "sell_id")
)

CREATE INDEX "idx02" on "container_data" ("point_array_col")
    USING SPATIAL;
CREATE INDEX "idx03" on "container_data" ("tuple_col1"[1]) 
    USING HASH; 
CREATE INDEX "idx04" on "container_data" ("tuple_col2"[2]) 
    USING INVERTED;     
﻿
This example table makes use of these Ocient System configurations.
Column name
Configuration description
point_array_col
This column is an array of POINT values that is indexed using a SPATIAL index type as specified by the CREATE INDEX SQL statement. 
tuple_col1
This tuple column contains two variable-length values. 
Each object in the tuple can support its own compression scheme and index. The CREATE INDEX SQL statement assigns a HASH index to the first value (VARCHAR(255)). 
tuple_col2
This tuple column contains various data types. Each object in the tuple can support its own compression scheme and index. 
The two VARCHAR objects in the tuple column each have their own unique compression scheme. 
The CREATE INDEX SQL statement assigns an INVERTED index to the second tuple object, the integer array INT[]. 
For details about Ocient container types, see Array, Tuple, and Matrix Overview﻿. 
﻿Related Links﻿
﻿Database, Tables, Views, and Indexes﻿﻿
﻿Table Compression Options﻿﻿
﻿Secondary Indexes﻿﻿

Column Name	Configuration Description
created_at	This column is the table , which means that the database indexes for filter optimization. The example configures the TimeKey to partition segments by every hour, (1, HOUR), which is the approximate time granularity you expect to use in your query filters.
user_id, sell_id	The example table identifies these two columns as the table Clustering Key. The database pairs these two columns for quick reference, such as filtering one column for row values of the other.
purchase_amount	The CREATE INDEX SQL statement adds a secondary index to this column. As a BIGINT data type, the purchase_amount column defaults to the INVERTED index.
buyer_name	This CREATE INDEX SQL statement adds a secondary index to this column. As a VARCHAR column, the buyer_name column normally defaults to a HASH index, but this example specifies it should be an NGRAM index instead.

Column Name	Configuration Description
route_time	This column is the table , which means that the database indexes for filter optimization. The example configures the TimeKey to partition segments by every two weeks (14, DAY), which is the approximate time granularity that you expect to use in your query filters.
trip_id, vehicle_id	The example table identifies these two columns as the table Clustering Key. The database pairs these two columns for quick reference, such as filtering one column for row values of the other.
end_point, route_path, route_area	These columns have POINT, LINESTRING, and POLYGON data types, respectively. Each of these geospatial columns includes default values in their respective formats.
start_point, end_point	The CREATE INDEX SQL statements for both of these columns assign SPATIAL indexes for quicker reference. This index is the default type for any column with a geospatial data type.

Column name	Configuration description
address	This column explicitly removes compression by specifying COMPRESSION NONE in the CREATE TABLE SQL statement.
call_type, access_information	These columns have VARCHAR data types configured with Global Dictionary Compression (GDC). This compression scheme maps the variable-length type rows to integers. Among other benefits, this compression allows both columns to support a Clustering Key for faster querying. The call_type column stacks multiple compression schemes by including both GDC and ZSTD compression to provide additional storage reduction.

Column name	Configuration description
point_array_col	This column is an array of POINT values that is indexed using a SPATIAL index type as specified by the CREATE INDEX SQL statement.
tuple_col1	This tuple column contains two variable-length values. Each object in the tuple can support its own compression scheme and index. The CREATE INDEX SQL statement assigns a HASH index to the first value (VARCHAR(255)).
tuple_col2	This tuple column contains various data types. Each object in the tuple can support its own compression scheme and index. The two VARCHAR objects in the tuple column each have their own unique compression scheme. The CREATE INDEX SQL statement assigns an INVERTED index to the second tuple object, the integer array INT[].

Updated 21 May 2024

Did this page help you?

Global Dictionary Compression

Remove Records from an Ocient System

CREATE TABLE SQL Statement Examples

Create a Table for Real-Time Analysis﻿

Create a Table for Geospatial Data﻿

Create a Table With Compression Options

Create a Table for Various Container Types﻿

﻿Related Links﻿

Create a Table for Real-Time Analysis

Create a Table for Geospatial Data

Create a Table for Various Container Types

Related Links