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