SQL Capabilities

DML allows you to update and query data stored in OmniSci.
See Using Geospatial Objects: Geospatial Functions for details on geospatial functions.
INSERT
Use for single-row ad hoc inserts. (When inserting many rows, use the more efficient COPY command.)
INSERT INTO <destination_table> VALUES (<value>, ...);
INSERT INTO <table> (<column>, ...) VALUES (value, ...);
Examples
CREATE TABLE foo (a INT, b FLOAT, c TEXT, d TIMESTAMP);
INSERT INTO foo VALUES (NULL, 3.1415, 'xyz', '2015-05-11 211720');
You can also insert into a table as SELECT, as shown in the following examples:
Example 1
Example 2
Example 3
Example 4
Example 1
INSERT INTO destination_table SELECT * FROM source_table;
Example 2
INSERT INTO destination_table (id, name, age, gender) SELECT * FROM source_table;
Example 3
INSERT INTO destination_table (name, gender, age, id) SELECT name, gender, age, id  FROM source_table;
Example 4
INSERT INTO votes_summary (vote_id, vote_count) SELECT vote_id, sum(*) FROM votes GROUP_BY vote_id;
You can insert array literals into array columns. The inserts in the following example each have three array values, and demonstrate how you can:
Create a table with variable-length and fixed-length array columns.
Insert NULL arrays into these colums.
Specify and insert array literals using {...} or ARRAY[...] syntax.
Insert empty variable-length arrays using{} and ARRAY[] syntax.
Insert array values that contain NULL elements.
CREATE TABLE ar (ai INT[], af FLOAT[], ad2 DOUBLE[2]); 
INSERT INTO ar VALUES ({1,2,3},{4.0,5.0},{1.2,3.4}); 
INSERT INTO ar VALUES (ARRAY[NULL,2],NULL,NULL); 
INSERT INTO ar VALUES (NULL,{},{2.0,NULL});
Default Values
If you create a table with column that has a default value, or alter a table to add a column with a default value, using the INSERT command creates a record that includes the default value if it is omitted from the INSERT. For example, assume a table created as follows:
CREATE TABLE tbl (
   id INTEGER NOT NULL, 
   name TEXT NOT NULL DEFAULT 'John Doe', 
   age SMALLINT NOT NULL);
If you omit the name column from an INSERT or INSERT FROM SELECT statement, the missing value for column name is set to 'John Doe'.
INSERT INTO tbl (id, age) VALUES (1, 36); creates the record 1|'John Doe'|36 .
INSERT INTO tbl (id, age) SELECT id, age FROM old_tbl; also sets all the name values to John Doe .
SELECT
query:
  |   WITH withItem [ , withItem ]* query
  |   {
          select
      }
      [ ORDER BY orderItem [, orderItem ]* ]
      [ LIMIT [ start, ] { count | ALL } ]
      [ OFFSET start { ROW | ROWS } ]
​
withItem:
      name
      [ '(' column [, column ]* ')' ]
      AS '(' query ')'
​
orderItem:
      expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
​
select:
      SELECT [ DISTINCT ] [/*+ hints */]
          { * | projectItem [, projectItem ]* }    
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]
      [ WINDOW window_name AS ( window_definition ) [, ...] ]
​
projectItem:
      expression [ [ AS ] columnAlias ]
  |   tableAlias . *
​
tableExpression:
      tableReference [, tableReference ]*
  |   tableExpression [ ( LEFT ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
​
joinCondition:
      ON booleanExpression
  |   USING '(' column [, column ]* ')'
​
tableReference:
      tablePrimary
      [ [ AS ] alias ]
​
tablePrimary:
      [ catalogName . ] tableName
  |   '(' query ')'
​
groupItem:
      expression
  |   '(' expression [, expression ]* ')'
For more information, see SELECT.
ORDER BY
Sort order defaults to ascending (ASC).
Sorts null values after non-null values by default in an ascending sort, before non-null values in a descending sort. For any query, you can use NULLS FIRST to sort null values to the top of the results or NULLS LAST to sort null values to the bottom of the results.
Allows you to use a positional reference to choose the sort column. For example, the command SELECT colA,colB FROM table1 ORDER BY 2 sorts the results on colB because it is in position 2.
SELECT Hints
Currently, the only supported SELECT hint is /*+ cpu_mode */ , which forces query execution on CPU, even if a GPU is available. You can use the cpu_mode hint when:
You know in advance that that running on CPU is more efficient. 
You want to avoid overhead related to a query being punted from GPU to CPU because of a potential runtime failure. 
Syntax
Enable cpu_mode hint for a SELECT statement:
SELECT /*+ cpu_mode */ FROM ...;
SELECT hints must appear first, immediately after the SELECT statement; otherwise, the query fails.
All SQL hints affect the entire query. For example, if you define the cpu_mode hint in a subquery SELECT clause, it affects to the entire query.
For example, both the WITH clause and main SELECT clause are run on the CPU.
WITH CTE AS (SELECT ... FROM ...) SELECT /*+ cpu_mode */ FROM CTE;
Here, both the subquery and outer query are run on the CPU.
SELECT ... FROM (SELECT /*+ cpu_mode */ FROM ...);
SELECT /*+ cpu_mode */ ... FROM (SELECT ... FROM ...) WHERE ...;
UPDATE
UPDATE table_name SET assign [, assign ]* [ WHERE booleanExpression ]
Changes the values of the specified columns based on the assign argument (identifier=expression) in all rows that satisfy the condition in the WHERE clause.
Example
UPDATE UFOs SET shape='ovate' where shape='eggish';
Currently, OmniSci does not support updating a geo column type (POINT, LINESTRING, POLYGON, or MULTIPOLYGON) in a table.
Update Via Subquery
You can update a table via subquery, which allows you to update based on calculations performed on another table.
Examples
Example 1
Example 2
Example 3
Example 1
UPDATE test_facts SET lookup_id = (SELECT SAMPLE(test_lookup.id) 
FROM test_lookup WHERE test_lookup.val = test_facts.val);
Example 2
UPDATE test_facts SET val = val+1, lookup_id = (SELECT SAMPLE(test_lookup.id)
FROM test_lookup WHERE test_lookup.val = test_facts.val);
Example 3
UPDATE test_facts SET lookup_id = (SELECT SAMPLE(test_lookup.id) 
FROM test_lookup WHERE test_lookup.val = test_facts.val) WHERE id < 10;
DELETE
DELETE FROM table_name [ * ] [ [ AS ] alias ]
[ WHERE condition ]
Deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, all rows in the table are deleted, resulting in a valid but empty table.
EXPLAIN
Shows generated Intermediate Representation (IR) code, identifying whether it is executed on GPU or CPU. This is primarily used internally by OmniSci to monitor behavior.
EXPLAIN <STMT>
For example, when you use the EXPLAIN command on a basic statement, the utility returns 90 lines of IR code that is not meant to be human readable. However, at the top of the listing, a heading indicates whether it is IR for the CPU or IR for the GPU, which can be useful to know in some situations.
EXPLAIN CALCITE
Returns a relational algebra tree describing the high-level plan to execute the statement.
EXPLAIN CALCITE <STMT>
The table below lists the relational algebra classes used to describe the execution plan for a SQL statement.
Method
Description
LogicalAggregate
Operator that eliminates duplicates and computes totals.
LogicalCalc
Expression that computes project expressions and also filters.
LogicalChi
Operator that converts a stream to a relation.
LogicalCorrelate
Operator that performs nested-loop joins.
LogicalDelta
Operator that converts a relation to a stream.
LogicalExchange
Expression that imposes a particular distribution on its input without otherwise changing its content.
LogicalFilter
Expression that iterates over its input and returns elements for which a condition evaluates to true.
LogicalIntersect
Expression that returns the intersection of the rows of its inputs.
LogicalJoin
Expression that combines two relational expressions according to some condition.
LogicalMatch
Expression that represents a MATCH_RECOGNIZE node.
LogicalMinus
Expression that returns the rows of its first input minus any matching rows from its other inputs. Corresponds to the SQL EXCEPT operator.
LogicalProject
Expression that computes a set of ‘select expressions’ from its input relational expression.
LogicalSort
Expression that imposes a particular sort order on its input without otherwise changing its content.
LogicalTableFunctionScan
Expression that calls a table-valued function.
LogicalTableModify
Expression that modifies a table. Similar to TableScan, but represents a request to modify a table instead of read from it.
LogicalTableScan
Reads all the rows from a RelOptTable.
LogicalUnion
Expression that returns the union of the rows of its inputs, optionally eliminating duplicates.
LogicalValues
Expression for which the value is a sequence of zero or more literal row values.
LogicalWindow
Expression representing a set of window aggregates. See Window Functions​
For example, a SELECT statement is described as a table scan and projection.
omnisql> EXPLAIN CALCITE (SELECT * FROM movies);
Explanation
LogicalProject(movieId=[$0], title=[$1], genres=[$2])
   LogicalTableScan(TABLE=[[CATALOG, omnisci, MOVIES]])
If you add a sort order, the table projection is folded under a LogicalSort procedure.
omnisql> EXPLAIN calcite (SELECT * FROM movies ORDER BY title);
Explanation
LogicalSort(sort0=[$1], dir0=[ASC])
   LogicalProject(movieId=[$0], title=[$1], genres=[$2])
      LogicalTableScan(TABLE=[[CATALOG, omnisci, MOVIES]])
When the SQL statement is simple, the EXPLAIN CALCITE version is actually less “human readable.” EXPLAIN CALCITE is more useful when you work with more complex SQL statements, like the one that follows. This query performs a scan on the BOOK table before scanning the BOOK_ORDER table.
omnisql> EXPLAIN calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
FROM book b, book_customer bc, book_order bo, shipper s
WHERE bo.cust_id = bc.cust_id AND b.book_id = bo.book_id AND bo.shipper_id = s.shipper_id
AND s.name = 'UPS';
Explanation
LogicalProject(firstname=[$5], lastname=[$6], title=[$2], orderdate=[$11], name=[$14])
    LogicalFilter(condition=[AND(=($9, $4), =($0, $8), =($10, $13), =($14, 'UPS'))])
        LogicalJoin(condition=[true], joinType=[INNER])
            LogicalJoin(condition=[true], joinType=[INNER])
                LogicalJoin(condition=[true], joinType=[INNER])
                    LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK]])
                    LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_CUSTOMER]])
                LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_ORDER]])
            LogicalTableScan(TABLE=[[CATALOG, omnisci, SHIPPER]])
Revising the original SQL command results in a more natural selection order and a more performant query.
omnisql> EXPLAIN calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
FROM book_order bo, book_customer bc, book b, shipper s
WHERE bo.cust_id = bc.cust_id AND bo.book_id = b.book_id AND bo.shipper_id = s.shipper_id
AND s.name = 'UPS';
Explanation
LogicalProject(firstname=[$10], lastname=[$11], title=[$7], orderdate=[$3], name=[$14])
    LogicalFilter(condition=[AND(=($1, $9), =($5, $0), =($2, $13), =($14, 'UPS'))])
        LogicalJoin(condition=[true], joinType=[INNER])
            LogicalJoin(condition=[true], joinType=[INNER])
                LogicalJoin(condition=[true], joinType=[INNER])
                  LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_ORDER]])
                  LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_CUSTOMER]])
                LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK]])
            LogicalTableScan(TABLE=[[CATALOG, omnisci, SHIPPER]])
SHOW
Use SHOW commands to get information about databases, tables, and user sessions.
Command
Description
SHOW CREATE TABLE
Shows the CREATE TABLE statement that could have been used to create the table.
SHOW CREATE TABLE omnisci_states;
CREATE TABLE omnisci_states (
 id TEXT ENCODING DICT(32),
 abbr TEXT ENCODING DICT(32),
 name TEXT ENCODING DICT(32),
 omnisci_geo GEOMETRY(MULTIPOLYGON, 4326) NOT NULL);
SHOW DATABASES
Retrieve the databases accessible for the current user, showing the database name and owner.
Database Owner
omnisci admin
2004_zipcodes admin
game_results jane
signals jason
...
SHOW QUERIES
Returns a list of queued queries in the system; information includes session ID, status, query string, account login name, client address, database name, and device type (CPU or GPU). See SHOW QUERIES Example to see output.

NOTE: SHOW QUERIES is only available if the runtime query interrupt parameter (enable-runtime-query-interrupt) is set.
SHOW TABLE DETAILS [<table-name>, <table-name>, ...]
Displays storage-related information for a table, such as the table ID/name, number of data/metadata files used by the table, total size of data/metadata files, and table epoch values.
You can see table details for all tables that you have access to in the current database, or for only those tables you specify.
See SHOW TABLE DETAILS Examples to see example output.
SHOW TABLES
Retrieve the tables accessible for the current user.
SHOW TABLES;
table_name
omnisci_states
omnisci_counties
omnisci_countries
streets_nyc
streets_miami
...
SHOW USER DETAILS
Show name, ID, superuser status, default database, and login permission status for all or specified database users.
Show details for all users:
omnisql> SHOW USER DETAILS;
NAME |ID|IS_SUPER|DEFAULT_DB|CAN_LOGIN
admin|0 |true | |true
alice|1 |true |omnisci |true
bob |2 |true | |false
chuck|3 |false |omnisci |true
Show details for users bob and alice:
omnisql> SHOW USER DETAILS bob,alice;
NAME |ID|IS_SUPER|DEFAULT_DB|CAN_LOGIN
bob |2 |true | |false
alice|1 |true |omnisci |true
SHOW USER SESSIONS
Retrieve all persisted user sessions, showing the session ID, user login name, client address, and database name. Admin or superuser privileges required.
SHOW USER SESSIONS;
session_id login_name client_address db_name
453-X6ds mike http:198.51.100.1 game_results
453-0t2r erin http:198.51.100.11 game_results
421-B64s shauna http:198.51.100.43 game_results
213-06dw ahmed http:198.51.100.12 signals
333-R28d cat http:198.51.100.233 signals
497-Xyz6 inez http:198.51.100.5 ships
...
SHOW QUERIES Example
Show the queries in the queue:
show queries;
query_session_id|current_status|submitted          |query_str                                                   |login_name|client_address     |db_name   |exec_device_type
834-8VAA        |Pending       |2020-05-06 08:21:15|select d_date_sk, count(1) from date_dim group by d_date_sk;|admin     |tcp:localhost:48596|tpcds_sf10|CPU
826-CLKk        |Running       |2020-05-06 08:20:57|select count(1) from store_sales, store_returns;            |admin     |tcp:localhost:48592|tpcds_sf10|CPU
828-V6s7        |Pending       |2020-05-06 08:21:13|select count(1) from store_sales;                           |admin     |tcp:localhost:48594|tpcds_sf10|GPU
946-rtJ7        |Pending       |2020-05-06 08:20:58|select count(1) from item;                                  |admin     |tcp:localhost:48610|tpcds_sf10|GPU
To interrupt a query in the queue, see KILL QUERY.
SHOW TABLE DETAILS Examples
Show details for all tables you have access to:
omnisql> show table details;
table_id|table_name       |column_count|is_sharded_table|shard_count|max_rows           |fragment_size|max_rollback_epochs|min_epoch|max_epoch|min_epoch_floor|max_epoch_floor|metadata_file_count|total_metadata_file_size|total_metadata_page_count|total_free_metadata_page_count|data_file_count|total_data_file_size|total_data_page_count|total_free_data_page_count
1       |omnisci_states   |11          |false           |0          |4611686018427387904|32000000     |-1                 |1        |1        |0              |0              |1                  |16777216                |4096                     |4082                          |1              |536870912           |256                  |242
2       |omnisci_counties |13          |false           |0          |4611686018427387904|32000000     |-1                 |1        |1        |0              |0              |1                  |16777216                |4096                     |NULL                          |1              |536870912           |256                  |NULL
3       |omnisci_countries|71          |false           |0          |4611686018427387904|32000000     |-1                 |1        |1        |0              |0              |1                  |16777216                |4096                     |4022                          |1              |536870912           |256                  |182
Show details for table omnisci_states:
omnisql> show table details omnisci_states;
table_id|table_name    |column_count|is_sharded_table|shard_count|max_rows           |fragment_size|max_rollback_epochs|min_epoch|max_epoch|min_epoch_floor|max_epoch_floor|metadata_file_count|total_metadata_file_size|total_metadata_page_count|total_free_metadata_page_count|data_file_count|total_data_file_size|total_data_page_count|total_free_data_page_count
1       |omnisci_states|11          |false           |0          |4611686018427387904|32000000     |-1                 |1        |1        |0              |0              |1                  |16777216                |4096                     |4082                          |1              |536870912           |256                  |242
The number of columns returned includes system columns. As a result, the number of columns in column_count can be up to two greater than the number of columns created by the user.
KILL QUERY
Interrupt a queued query. Specify the query by using its session ID.
To see the queries in the queue, use the SHOW QUERIES command:
show queries;
query_session_id|current_status|submitted          |query_str                                                   |login_name|client_address     |db_name   |exec_device_type
834-8VAA        |Pending       |2020-05-06 08:21:15|select d_date_sk, count(1) from date_dim group by d_date_sk;|admin     |tcp:localhost:48596|tpcds_sf10|CPU
826-CLKk        |Running       |2020-05-06 08:20:57|select count(1) from store_sales, store_returns;            |admin     |tcp:localhost:48592|tpcds_sf10|CPU
828-V6s7        |Pending       |2020-05-06 08:21:13|select count(1) from store_sales;                           |admin     |tcp:localhost:48594|tpcds_sf10|GPU
946-rtJ7        |Pending       |2020-05-06 08:20:58|select count(1) from item;                                  |admin     |tcp:localhost:48610|tpcds_sf10|GPU
To interrupt the last query in the list (ID 946-rtJ7):
kill query '946-rtJ7'
Showing the queries again indicates that 946-rtJ7 has been deleted:
show queries;
query_session_id|current_status|submitted          |query_str                                                   |login_name|client_address     |db_name   |exec_device_type
834-8VAA        |Pending       |2020-05-06 08:21:15|select d_date_sk, count(1) from date_dim group by d_date_sk;|admin     |tcp:localhost:48596|tpcds_sf10|CPU
826-CLKk        |Running       |2020-05-06 08:20:57|select count(1) from store_sales, store_returns;            |admin     |tcp:localhost:48592|tpcds_sf10|CPU
828-V6s7        |Pending       |2020-05-06 08:21:13|select count(1) from store_sales;                           |admin     |tcp:localhost:48594|tpcds_sf10|GPU
KILL QUERY is only available if the runtime query interrupt parameter (enable-runtime-query-interrupt) is set.
Window Functions
Window functions allow you to work with a subset of rows related to the currently selected row. For a given dimension, you can find the most associated dimension by some other measure (for example, number of records or sum of revenue).
Window functions must always contain an OVER clause. The OVER clause splits up the rows of the query for processing by the window function.
The PARTITION BY list divides the rows into groups that share the same values of the PARTITION BY expression(s). For each row, the window function is computed using all rows in the same partition as the current row.
Rows that have the same value in the ORDER BY clause are considered peers. The ranking functions give the same answer for any two peer rows.
Function
Description
row_number()
Number of the current row within the partition, counting from 1.
rank()
Rank of the current row with gaps. Equal to the row_number of its first peer.
dense_rank()
Rank of the current row without gaps. This function counts peer groups.
percent_rank()
Relative rank of the current row: (rank-1)/(total rows-1).
cume_dist()
Cumulative distribution value of the current row: (number of rows preceding or peers of the current row)/(total rows)
ntile(num_buckets)
Subdivide the partition into buckets. If the total number of rows is divisible by num_buckets, each bucket has a equal number of rows. If the total is not divisible by num_buckets, the function returns groups of two sizes with a difference of 1.
lag(value, offset)
Returns the value at the row that is offset rows before the current row within the partition
lead(value, offset)
Returns the value at the row that is offset rows after the current row within the partition
first_value(value)
Returns the value from the first row of the window frame (the rows from the start of the partition to the last peer of the current row).
last_value(value)
Returns the value from the last row of the window frame.
Usage Notes
OmniSciDB supports the aggregate functions AVG, MIN, MAX, SUM, and COUNT in window functions.
OmniSciDB does not support empty partitions. For example, the following query triggers an exception because the OVER clause requires a PARTITION BY list:
SELECT dest, ntile(4) OVER (ORDER BY total_count DESC) AS quartile FROM my_test_data.
Window functions only work on single fragment datasets. If you want to run window functions over base data in your table, you must ensure there is only one fragment (by increasing the fragment size to be greater than the number of rows expected in the table before import). If you are running the window function on top of an intermediate result (for example, a GROUP BY), the intermediate result is contained in a single fragment, even if the underlying table contains multiple fragments. This happens automatically if a GROUP BY clause is part of the window function query.
Example
This query shows the top airline carrier for each state, based on the number of departures.
select origin_state, carrier_name, n 
   from (select origin_state, carrier_name, row_number() over(
      partition by origin_state order by n desc) as rownum, n 
         from (select origin_state, carrier_name, count(*) as n 
            from flights_2008_7M where extract(year 
               from dep_timestamp) = 2008 
   group by origin_state, carrier_name )) where rownum = 1
Table Expression and Join Support
<table> , <table> WHERE <column> = <column>
<table> [ LEFT ] JOIN <table> ON <column> = <column>
If a join column name or alias is not unique, it must be prefixed by its table name.
You can use BIGINT, INTEGER, SMALLINT, TINYINT, DATE, TIME, TIMESTAMP, or TEXT ENCODING DICT data types. TEXT ENCODING DICT is the most efficient because corresponding dictionary IDs are sequential and span a smaller range than, for example, the 65,535 values supported in a SMALLINT field. Depending on the number of values in your field, you can use TEXT ENCODING DICT(32) (up to approximately 2,150,000,000 distinct values), TEXT ENCODING DICT(16) (up to 64,000 distinct values), or TEXT ENCODING DICT(8) (up to 255 distinct values). For more information, see Data Types and Fixed Encoding.
Geospatial Joins
By default, a join involving a geospatial operator (such as ST_Contains) utilizes the loop join framework.
To allow all loop joins, set the allow-loop-joins flag to true at either the command line when starting OmniSci, or in omnisci.conf. Running geo join queries without allow-loop-joins set to true results in the following error:
Hash join failed: no equijoin expression found.
If you set trivial-loop-join-threshold, loop joins are allowed if the inner table has fewer rows than the trivial join loop threshold you specify. The default value is 1,000 rows.
For geospatial joins, the inner table should always be the more complicated primitive. For example, for ST_Contains(polygon, point), the point table should be the outer table and the polygon table should be the inner table.
Geo join best practice is to increase the trivial join loop threshold for the size of the inner table.
When you increase the trivial loop join threshold by 1, you increase the run time by the number of rows in your outer table. For example, if your outer table has 100,000,000 rows, and your trivial loop join threshold is 10, you potentially run 1,000,000,000 operations in the loop. If you increase the loop join to 100, you increase the number of operations run to 10,000,000,000.
Using Joins in a Distributed Environment
You can create joins in a distributed environment in two ways:
Replicate small dimension tables that are used in the join.
Create a shard key on the column used in the join (note that there is a limit of one shard key per table). If the column involved in the join is a TEXT ENCODED field, you must create a SHARED DICTIONARY that references the FACT table key you are using to make the join.
1 small table, 1 large
2 large tables joined with shard key
2 large tables joined on TEXT ENCODED column
1 small table, 1 large
# Table customers is very small
CREATE TABLE sales (
id INTEGER,
customerid TEXT ENCODING DICT(32),
saledate DATE ENCODING DAYS(32),
saleamt DOUBLE);
​
CREATE TABLE customers (
id TEXT ENCODING DICT(32),
someid INTEGER,
name TEXT ENCODING DICT(32))
WITH (partitions = 'replicated') #this causes the entire contents of this table to be replicated to each leaf node. Only recommened for small dimension tables.
SELECT c.id, c.name from sales s inner join customers c on c.id = s.customerid limit 10;
2 large tables joined with shard key
CREATE TABLE sales (
id INTEGER,
customerid BIGINT, #note the numeric datatype, so we don't need to specify a shared dictionary on the customer table
saledate DATE ENCODING DAYS(32),
saleamt DOUBLE,
SHARD KEY (customerid))
WITH (SHARD_COUNT = <num gpus in cluster>)
​
CREATE TABLE customers (
id TEXT BIGINT,
someid INTEGER,
name TEXT ENCODING DICT(32)
SHARD KEY (id))
WITH (SHARD_COUNT=<num gpus in cluster>);
​
SELECT c.id, c.name FROM sales s INNER JOIN customers c ON c.id = s.customerid LIMIT 10;
2 large tables joined on TEXT ENCODED column
CREATE TABLE sales (
id INTEGER,
customerid TEXT ENCODING DICT(32),
saledate DATE ENCODING DAYS(32),
saleamt DOUBLE,
SHARD KEY (customerid))
WITH (SHARD_COUNT = <num gpus in cluster>)
​
#note the difference when customerid is a text encoded field:
​
CREATE TABLE customers (
id TEXT,
someid INTEGER,
name TEXT ENCODING DICT(32),
SHARD KEY (id),
SHARED DICTIONARY (id) REFERENCES sales(customerid))
WITH (SHARD_COUNT = <num gpus in cluster>)
​
SELECT c.id, c.name FROM sales s INNER JOIN customers c ON c.id = s.customerid LIMIT 10;
The join order for one small table and one large table matters. If you swap the sales and customer tables on the join, it throws an exception stating that table "sales" must be replicated.
Logical Operator Support
Operator
Description
AND
Logical AND
NOT
Negates value
OR
Logical OR
Conditional Expression Support
Expression
Description
CASE WHEN condition THEN result ELSE default END
Case operator
COALESCE(val1, val2, ..)
Returns the first non-null value in the list
Subquery Expression Support
Expression
Description
expr IN (subquery or list of values)
Evaluates whether expr equals any value of the IN list.
expr NOT IN (subquery or list of values)
Evaluates whether expr does not equal any value of the IN list.
Usage Notes
You can use a subquery anywhere an expression can be used, subject to any runtime constraints of that expression. For example, a subquery in a CASE statement must return exactly one row, but a subquery can return multiple values to an IN expression.
You can use a subquery anywhere a table is allowed (for example, FROM subquery), using aliases to name any reference to the table and columns returned by the subquery.
Type Cast Support
Expression
Example
Description
CAST(expr AS type)
CAST(1.25 AS FLOAT)
Converts an expression to another data type
The following table shows cast type conversion support.
FROM/TO:
TINYINT
SMALLINT
INTEGER
BIGINT
FLOAT
DOUBLE
DECIMAL
TEXT
BOOLEAN
DATE
TIME
TIMESTAMP
TINYINT
-
Yes
Yes
Yes
Yes
Yes
Yes
No
No
No
No
n/a
SMALLINT
Yes
-
Yes
Yes
Yes
Yes
Yes
No
No
No
No
n/a
INTEGER
Yes
Yes
-
Yes
Yes
Yes
Yes
No
Yes
No
No
No
BIGINT
Yes
Yes
Yes
-
Yes
Yes
Yes
No
No
No
No
No
FLOAT
Yes
Yes
Yes
Yes
-
Yes
No
No
No
No
No
No
DOUBLE
Yes
Yes
Yes
Yes
Yes
-
No
No
No
No
No
n/a
DECIMAL
Yes
Yes
Yes
Yes
Yes
Yes
-
No
No
No
No
n/a
TEXT
No
No
No
No
No
No
No
-
No
No
No
No
BOOLEAN
No
No
Yes
No
No
No
No
No
-
n/a
n/a
n/a
DATE
No
No
No
No
No
No
No
No
n/a
-
No
Yes
TIME
No
No
No
No
No
No
No
No
n/a
No
-
n/a
TIMESTAMP
No
No
No
No
No
No
No
No
n/a
Yes
No
-
Array Support
OmniSci supports arrays in dictionary-encoded text and number fields (TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, and DOUBLE). Data stored in arrays are not normalized. For example, {green,yellow} is not the same as {yellow,green}. As with many SQL-based services, OmniSci array indexes are 1-based.
OmniSci supports NULL variable-length arrays for all integer and floating-point data types, including dictionary-encoded string arrays. For example, you can insert NULL into BIGINT[ ], DOUBLE[ ], or TEXT[ ] columns. OmniSci supports NULL fixed-length arrays for all integer and floating-point data types, but not for dictionary-encoded string arrays. For example, you can insert NULL into BIGINT[2] DOUBLE[3], but not into TEXT[2] columns.
Expression
Description
​
​
​
​
​
​
​
ArrayCol[n] ...
Returns value(s) from specific location n in the array.
​
​
​
​
​
​
​
UNNEST(ArrayCol)
Extract the values in the array to a set of rows. Requires GROUP BY; projecting UNNEST is not currently supported.
​
​
​
​
​
​
​
test = ANY ArrayCol
ANY compares a scalar value with a single row or set of values in an array, returning results in which at least one item in the array matches. ANY must be preceded by a comparison operator.
​
​
​
​
​
​
​
test = ALL ArrayCol
ALL compares a scalar value with a single row or set of values in an array, returning results in which all records in the array field are compared to the scalar value. ALL must be preceded by a comparison operator.
​
​
​
​
​
​
​
CARDINALITY()
Returns the number of elements in an array. For example: `omnisql> \d arr  CREATE TABLE arr (  sia SMALLINT[])  omnisql> select sia, CARDINALITY(sia) from arr;  sia
EXPR$0  NULL
NULL  {}
0  {NULL}
1  {1}
1  {2,2}
2  {3,3,3}
3`
Examples
The following examples show query results based on the table test_array created with the following statement:
CREATE TABLE test_array (name TEXT ENCODING DICT(32),colors TEXT[] ENCODING DICT(32), qty INT[]);
Example 1
Example 2
Example 3
Example 4
Example 5
Example 6
Example 1
omnisql> SELECT * FROM test_array;
name|colors|qty
Banana|{green, yellow}|{1, 2}
Cherry|{red, black}|{1, 1}
Olive|{green, black}|{1, 0}
Onion|{red, white}|{1, 1}
Pepper|{red, green, yellow}|{1, 2, 3}
Radish|{red, white}|{}
Rutabaga|NULL|{}
Zucchini|{green, yellow}|{NULL}
Example 2
omnisql> SELECT UNNEST(colors) AS c FROM test_array;
Exception: UNNEST not supported in the projection list yet.
Example 3
omnisql> SELECT UNNEST(colors) AS c, count(*) FROM test_array group by c;
c|EXPR$1
green|4
yellow|3
red|4
black|2
white|2
Example 4
omnisql> SELECT name, colors [2] FROM test_array;
name|EXPR$1
Banana|yellow
Cherry|black
Olive|black
Onion|white
Pepper|green
Radish|white
Rutabaga|NULL
Zucchini|yellow
Example 5
omnisql> SELECT name, colors FROM test_array WHERE colors[1]='green';
name|colors
Banana|{green, yellow}
Olive|{green, black}
Zucchini|{green, yellow}
Example 6
omnisql> SELECT * FROM test_array WHERE colors IS NULL;
name|colors|qty
Rutabaga|NULL|{}
The following queries use arrays in an INTEGER field:
Example 1
Example 2
Example 3
Example 4
Example 5
Example 1
omnisql> SELECT name, qty FROM test_array WHERE qty[2] >1;
name|qty
Banana|{1, 2}
Pepper|{1, 2, 3}
Example 2
omnisql> SELECT name, qty FROM test_array WHERE 15< ALL qty;
No rows returned.
Example 3
omnisql> SELECT name, qty FROM test_array WHERE 2 = ANY qty;
name|qty
Banana|{1, 2}
Pepper|{1, 2, 3}
Example 4
omnisql> SELECT COUNT(*) FROM test_array WHERE qty IS NOT NULL;
EXPR$0
8
Example 5
omnisql> SELECT COUNT(*) FROM test_array WHERE CARDINALITY(qty)<0;
EXPR$0
6
LIKELY/UNLIKELY
Expression
Description
LIKELY(X)
Provides a hint to the query planner that argument X is a Boolean value that is usually true. The planner can prioritize filters on the value X earlier in the execution cycle and return results more efficiently.
UNLIKELY(X)
Provides a hint to the query planner that argument X is a Boolean value that is usually not true. The planner can prioritize filters on the value X later in the execution cycle and return results more efficiently.
Usage Notes
SQL normally assumes that terms in the WHERE clause that cannot be used by indices are usually true. If this assumption is incorrect, it could lead to a suboptimal query plan. Use the LIKELY(X) and UNLIKELY(X) SQL functions to provide hints to the query planner about clause terms that are probably not true, which helps the query planner to select the best possible plan.
Use LIKELY/UNLIKELY to optimize evaluation of OR/AND logical expressions. LIKELY/UNLIKELY causes the left side of an expression to be evaluated first. This allows the right side of the query to be skipped when possible. For example, in the clause UNLIKELY(A) AND B, if A evaluates to FALSE, B does not need to be evaluated.
Consider the following:
SELECT COUNT(*) FROM test WHERE UNLIKELY(x IN (7, 8, 9, 10)) AND y > 42;
If x is one of the values 7, 8, 9, or 10, the filter y > 42 is applied. If x is not one of those values, the filter y > 42 is not applied.

SQL - Previous

Data Manipulation (DML)

Geospatial Capabilities

Last updated 3 days ago

Command	Description
`SHOW CREATE TABLE`	Shows the CREATE TABLE statement that could have been used to create the table. `SHOW CREATE TABLE omnisci_states;` `CREATE TABLE omnisci_states ( id TEXT ENCODING DICT(32), abbr TEXT ENCODING DICT(32), name TEXT ENCODING DICT(32), omnisci_geo GEOMETRY(MULTIPOLYGON, 4326) NOT NULL);`
`SHOW DATABASES`	Retrieve the databases accessible for the current user, showing the database name and owner. `Database Owner omnisci admin 2004_zipcodes admin game_results jane signals jason` `...`
`SHOW QUERIES`	Returns a list of queued queries in the system; information includes session ID, status, query string, account login name, client address, database name, and device type (CPU or GPU). See SHOW QUERIES Example to see output. NOTE: SHOW QUERIES is only available if the runtime query interrupt parameter (`enable-runtime-query-interrupt`) is set.
`SHOW TABLE DETAILS [<table-name>, <table-name>, ...]`	Displays storage-related information for a table, such as the table ID/name, number of data/metadata files used by the table, total size of data/metadata files, and table epoch values. You can see table details for all tables that you have access to in the current database, or for only those tables you specify. See SHOW TABLE DETAILS Examples to see example output.
`SHOW TABLES`	Retrieve the tables accessible for the current user. `SHOW TABLES;` `table_name omnisci_states omnisci_counties omnisci_countries streets_nyc streets_miami ...`
`SHOW USER DETAILS`	Show name, ID, superuser status, default database, and login permission status for all or specified database users. Show details for all users: `omnisql> SHOW USER DETAILS; NAME \|ID\|IS_SUPER\|DEFAULT_DB\|CAN_LOGIN admin\|0 \|true \| \|true alice\|1 \|true \|omnisci \|true bob \|2 \|true \| \|false chuck\|3 \|false \|omnisci \|true` Show details for users `bob` and `alice`: `omnisql> SHOW USER DETAILS bob,alice; NAME \|ID\|IS_SUPER\|DEFAULT_DB\|CAN_LOGIN bob \|2 \|true \| \|false alice\|1 \|true \|omnisci \|true`
`SHOW USER SESSIONS`	Retrieve all persisted user sessions, showing the session ID, user login name, client address, and database name. Admin or superuser privileges required. `SHOW USER SESSIONS;` `session_id login_name client_address db_name 453-X6ds mike http:198.51.100.1 game_results 453-0t2r erin http:198.51.100.11 game_results 421-B64s shauna http:198.51.100.43 game_results 213-06dw ahmed http:198.51.100.12 signals 333-R28d cat http:198.51.100.233 signals 497-Xyz6 inez http:198.51.100.5 ships ...`

Expression	Description
`LIKELY(X)`	Provides a hint to the query planner that argument `X` is a Boolean value that is usually true. The planner can prioritize filters on the value `X` earlier in the execution cycle and return results more efficiently.
`UNLIKELY(X)`	Provides a hint to the query planner that argument `X` is a Boolean value that is usually not true. The planner can prioritize filters on the value `X` later in the execution cycle and return results more efficiently.

Method	Description
`LogicalAggregate`	Operator that eliminates duplicates and computes totals.
`LogicalCalc`	Expression that computes project expressions and also filters.
`LogicalChi`	Operator that converts a stream to a relation.
`LogicalCorrelate`	Operator that performs nested-loop joins.
`LogicalDelta`	Operator that converts a relation to a stream.
`LogicalExchange`	Expression that imposes a particular distribution on its input without otherwise changing its content.
`LogicalFilter`	Expression that iterates over its input and returns elements for which a condition evaluates to true.
`LogicalIntersect`	Expression that returns the intersection of the rows of its inputs.
`LogicalJoin`	Expression that combines two relational expressions according to some condition.
`LogicalMatch`	Expression that represents a MATCH_RECOGNIZE node.
`LogicalMinus`	Expression that returns the rows of its first input minus any matching rows from its other inputs. Corresponds to the SQL EXCEPT operator.
`LogicalProject`	Expression that computes a set of ‘select expressions’ from its input relational expression.
`LogicalSort`	Expression that imposes a particular sort order on its input without otherwise changing its content.
`LogicalTableFunctionScan`	Expression that calls a table-valued function.
`LogicalTableModify`	Expression that modifies a table. Similar to TableScan, but represents a request to modify a table instead of read from it.
`LogicalTableScan`	Reads all the rows from a RelOptTable.
`LogicalUnion`	Expression that returns the union of the rows of its inputs, optionally eliminating duplicates.
`LogicalValues`	Expression for which the value is a sequence of zero or more literal row values.
`LogicalWindow`	Expression representing a set of window aggregates. See Window Functions

Function	Description
`row_number()`	Number of the current row within the partition, counting from 1.
`rank()`	Rank of the current row with gaps. Equal to the `row_number` of its first peer.
`dense_rank()`	Rank of the current row without gaps. This function counts peer groups.
`percent_rank()`	Relative rank of the current row: (rank-1)/(total rows-1).
`cume_dist()`	Cumulative distribution value of the current row: (number of rows preceding or peers of the current row)/(total rows)
`ntile(num_buckets)`	Subdivide the partition into buckets. If the total number of rows is divisible by num_buckets, each bucket has a equal number of rows. If the total is not divisible by num_buckets, the function returns groups of two sizes with a difference of 1.
`lag(value, offset)`	Returns the value at the row that is offset rows before the current row within the partition
`lead(value, offset)`	Returns the value at the row that is offset rows after the current row within the partition
`first_value(value)`	Returns the value from the first row of the window frame (the rows from the start of the partition to the last peer of the current row).
`last_value(value)`	Returns the value from the last row of the window frame.

Operator	Description
`AND`	Logical AND
`NOT`	Negates value
`OR`	Logical OR

Expression	Description
`CASE WHEN condition THEN result ELSE default END`	Case operator
`COALESCE(val1, val2, ..)`	Returns the first non-null value in the list

Expression	Description
`expr IN (subquery or list of values)`	Evaluates whether expr equals any value of the IN list.
`expr NOT IN (subquery or list of values)`	Evaluates whether expr does not equal any value of the IN list.

Expression	Example	Description
`CAST(expr AS type`)	`CAST(1.25 AS FLOAT)`	Converts an expression to another data type

Expression	Description
`ArrayCol[n] ...`	Returns value(s) from specific location `n` in the array.
`UNNEST(ArrayCol)`	Extract the values in the array to a set of rows. Requires `GROUP BY`; projecting `UNNEST` is not currently supported.
`test = ANY ArrayCol`	`ANY` compares a scalar value with a single row or set of values in an array, returning results in which at least one item in the array matches. `ANY` must be preceded by a comparison operator.
`test = ALL ArrayCol`	`ALL` compares a scalar value with a single row or set of values in an array, returning results in which all records in the array field are compared to the scalar value. `ALL` must be preceded by a comparison operator.
`CARDINALITY()`	Returns the number of elements in an array. For example: `omnisql> \d arr CREATE TABLE arr ( sia SMALLINT[]) omnisql> select sia, CARDINALITY(sia) from arr; sia	EXPR$0 NULL	NULL {}	0 {NULL}	1 {1}	1 {2,2}	2 {3,3,3}	3`