DDL - Tables

These functions are used to create and modify data tables in OmniSci.

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] <table>
  (<column> <type> [NOT NULL] [ENCODING <encodingSpec>],
  [SHARD KEY (<column>)],
  [SHARED DICTIONARY (<column>) REFERENCES <table>(<column>)], ...)
  [WITH (<property> = value, ...)];

Create a table named <table> specifying <columns> and table properties. Table and column names cannot include quotes, spaces, or special characters.

Datatypes

Supported types:

Datatype Size (bytes) Notes
TEXT ENCODING DICT 4 Max cardinality 1 billion distinct string values
TEXT ENCODING NONE Variable Size of the string + 6 bytes
TIMESTAMP 8 Linux timestamp from 30610224000000 (1/1/1000 00:00:00.000) through 29379542399999 (12/31/2900 23:59:59.999). Can also be inserted and stored in human-readable format (YYYY-MM-DD HH:MM:S).
TIME 8 Minimum value: 00:00:00; maximum value: 23:59:59.
DATE 8 Minimum value: 1000-01-01; maximum value: 2900-12-31.
FLOAT 4 Variable precision. Minimum value: -3.4 x e^38; maximum value: 3.4 x e^38.
DOUBLE 8 Variable precision. Minimum value: -1.79 x e^308; maximum value: 1.79 x e^308.
INTEGER 4 Minimum value: -2,147,483,647; maximum value: 2,147,483,647.
TINYINT 1 Minimum value: -127; maximum value: 127.
SMALLINT 2 Minimum value: -32,767; maximum value: 32,767.
BIGINT 8 Minimum value: -9,223,372,036,854,775,807; maximum value: 9,223,372,036,854,775,807.
BOOLEAN 1 TRUE: 'true', '1', 't'. FALSE: 'false', '0', 'f'. Text values are not case-sensitive.
DECIMAL/NUMERIC 8 Precise. Maximum precision: 19. Scale must be less than precision.

For more information, see Datatypes and Fixed Encoding.

For geospatial datatypes, see Geospatial Primitives

.

Example

Create a table named tweets and specify the columns, including type, in the table.

CREATE TABLE IF NOT EXISTS tweets (
   tweet_id BIGINT NOT NULL,
   tweet_time TIMESTAMP NOT NULL ENCODING FIXED(32),
   lat FLOAT,
   lon FLOAT,
   sender_id BIGINT NOT NULL,
   sender_name TEXT NOT NULL ENCODING DICT,
   location TEXT ENCODING  DICT,
   source TEXT ENCODING DICT,
   reply_to_user_id BIGINT,
   reply_to_tweet_id BIGINT,
   lang TEXT ENCODING  DICT,
   followers INT,
   followees INT,
   tweet_count INT,
   join_time TIMESTAMP ENCODING  FIXED(32),
   tweet_text TEXT,
   state TEXT ENCODING  DICT,
   county TEXT ENCODING DICT,
   place_name TEXT,
   state_abbr TEXT ENCODING DICT,
   county_state TEXT ENCODING DICT,
   origin TEXT ENCODING DICT,
   phone_numbers bigint);

<encodingSpec>

Supported encoding:

Encoding Descriptions
DICT Dictionary encoding on string columns (default for TEXT columns). Limit of 1 billion unique string values.
NONE No encoding. Valid only on TEXT columns. No Dictionary is created. Aggregate operations are not possible on this column type.
FIXED (bits) Fixed length encoding of integer or timestamp columns. See Datatypes and Fixed Encoding.

WITH Clause <property>

WITH clause properties:

Property Description
fragment_size Number of rows per fragment that is a unit of the table for query processing. Default = 32 million rows, which is not expected to be changed.
max_rows Set the maximum number of rows allowed in a table to create a capped collection. When this limit is reached, the oldest fragment is removed. Default = 2^62.
page_size Number of I/O page bytes. Default = 1MB, which does not need to be changed.
partitions

Partition strategy option:

  • SHARDED: Partition table using sharding.
  • REPLICATED: Partition table using replication.
shard_count Number of shards to create, typically equal to the number of GPUs across which the data table is distributed.

Sharding

Sharding partitions a database table across multiple servers so each server has a part of the table with the same columns but with different rows. Partitioning is based on a sharding key defined when you create the table.

Without sharding, the dimension tables involved in a join are replicated and sent to each GPU, which is not feasible for dimension tables with many rows. Specifying a shard key makes it possible for the query to execute efficiently on large dimension tables.

Currently, specifying a shard key is useful for joins, only:

  • If two tables specify a shard key with the same type and the same number of shards, a join on that key only sends a part of the dimension table column data to each GPU.
  • For multi-node installs, the dimension table does not need to be replicated and the join executes locally on each leaf.

Constraints

  • A shard key must specify a single column to shard on. There is no support for sharding by a combination of keys.
  • One shard key can be specified for a table.
  • Data are partitioned according to the shard key and the number of shards (shard_count).
  • A value in the column specified as a shard key is always sent to the same partition.
  • The number of shards should be equal to the number of GPUs in the cluster.
  • Only integers and dictionary-encoded columns can be shard keys.
  • Tables must share the dictionary for the column to be involved in sharded joins. If the dictionary is not specified as shared, the join does not take advantage of sharding. Dictionaries are reference-counted and only dropped when the last reference drops.

Recommendations

  • Set shard_count to the number of GPUs you eventually want to distribute the data table across.
  • Referenced tables must also be shard_count -aligned.
  • Sharding should be minimized because it can introduce load skew accross resources, compared to when sharding is not used.

Examples

Basic sharding:

CREATE TABLE  customers(
   accountId text,
   name text,
   SHARD KEY (accountId))
  WITH (shard_count = 4);

Sharding with shared dictionary:

CREATE TABLE transactions(
   accountId text,
   action text,
   SHARD KEY (accountId),
   SHARED DICTIONARY (accountId) REFERENCES customers(accountId))
  WITH (shard_count = 4);

CREATE TABLE AS SELECT

CREATE TABLE <newTableName> AS (<SELECT statement>) [WITH (vacuum='delayed')];

Create a table with the specified columns, copying any data that meet SELECT statement criteria.

The vacuum option formats the table to more efficiently handle DELETE requests. The only parameter available in this release is delayed. Rather than immediately remove deleted rows, vacuum marks items to be deleted, and they are removed at an optimal time.

Note

OmniSci Core database does not support CREATE TABLE AS SELECT:

  • In a distributed cluster.
  • For text fields that are not dictionary encoded.

Examples

Create the table newTable. Populate the table with all information from the table oldTable, effectively creating a duplicate of the original table.

CREATE TABLE newTable AS (SELECT * FROM oldTable);

Create a table named trousers. Populate it with data from the columns name, waist, and inseam from the table wardrobe.

CREATE TABLE trousers AS (SELECT name, waist, inseam FROM wardrobe);

Create a table named cosmos. Populate it with data from the columns star and planet from the table universe where planet has the class M.

CREATE TABLE cosmos AS (SELECT star, planet FROM universe WHERE class="M");

ALTER TABLE

ALTER TABLE <table> RENAME TO <table>;
ALTER TABLE <table> RENAME COLUMN <column> TO <column>;
ALTER TABLE <table> ADD [COLUMN] <column> <type> [NOT NULL] [ENCODING <encodingSpec>]
ALTER TABLE <table> ADD (<column> <type> [NOT NULL] [ENCODING <encodingSpec>], ...)

Examples

Rename the table tweets to retweets.

ALTER TABLE tweets RENAME TO retweets;

Rename the column source to device in the table retweets.

ALTER TABLE retweets RENAME COLUMN source TO device;

Add the column lang to the table tweets using a TEXT ENCODING DICTIONARY.

ALTER TABLE tweets ADD COLUMN lang TEXT ENCODING DICT;

Add the columns lang and encode to the table tweets using a TEXT ENCODING DICTIONARY for each.

ALTER TABLE tweets ADD (lang TEXT ENCODING DICT, encode TEXT ENCODING DICT);

DROP TABLE

DROP TABLE [IF EXISTS] <table>;

Example

DROP TABLE IF EXISTS tweets;

TRUNCATE TABLE

TRUNCATE TABLE <table>;

Use the TRUNCATE TABLE statement to remove all rows from a table without deleting the table structure.

This releases table on-disk and memory storage and removes dictionary content unless it is a shared dictionary.

Removing rows is more efficient than using DROP TABLE. Dropping followed by recreating the table invalidates dependent objects of the table requiring you to regrant object privileges. Truncating has none of these effects.

Example

TRUNCATE TABLE tweets;

OPTIMIZE TABLE

OPTIMIZE TABLE [<table>] WITH VACUUM

Use this statement to remove rows from storage that have been marked as deleted via DELETE statements.

When you omit the optional table name, the statement removes deleted rows from all tables for which you have DELETE privileges.