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
• SELECT
• UPDATE
• DELETE
• EXPLAIN
• EXPLAIN CALCITE
• SHOW
• Window Functions
• Table Expression and Join Support
• Logical Operator Support
• Conditional Expression Support
• Subquery Expression Support
• Type Cast Support
• Array Support
• LIKELY/UNLIKELY

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, ...);

CREATE TABLE foo (a INT, b FLOAT, c TEXT, d TIMESTAMP);
INSERT INTO foo VALUES (NULL, 3.1415, 'xyz', '2015-05-11 211720');

INSERT INTO destination_table SELECT * FROM source_table;

INSERT INTO destination_table (id, name, age, gender) SELECT * FROM source_table;

INSERT INTO destination_table (id, name, age, gender) SELECT id, name, age, gender  FROM source_table;

INSERT INTO destination_table (name, gender, age, id) SELECT name, gender, age, id  FROM source_table;

INSERT INTO votes_summary (vote_id, vote_count) SELECT vote_id, sum(*) FROM votes GROUP_BY vote_id;

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});

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 ]
          { * | 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 ]* ')'

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

For more information, see SELECT.

UPDATE table_name SET assign [, assign ]* [ WHERE booleanExpression ]

UPDATE UFOs SET shape='ovate' where shape='eggish'; 

UPDATE test_facts SET lookup_id = (SELECT SAMPLE(test_lookup.id) 
FROM test_lookup WHERE test_lookup.val = test_facts.val);

UPDATE test_facts SET val = val+1, lookup_id = (SELECT SAMPLE(test_lookup.id)
FROM test_lookup WHERE test_lookup.val = test_facts.val);

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 FROM table_name [ * ] [ [ AS ] alias ]
[ WHERE condition ]

EXPLAIN <STMT>;

EXPLAIN CALCITE <STMT>;

omnisql> explain calcite (select * from movies);
Explanation
LogicalProject(movieId=[$0], title=[$1], genres=[$2])
   LogicalTableScan(table=[[CATALOG, omnisci, MOVIES]])

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

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

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

Database       Owner
omnisci        admin
2004_zipcodes  admin
game_results   jane
signals        jason
...

SHOW TABLES;

table_name
omnisci_states
omnisci_counties
omnisci_countries
streets_nyc
streets_miami
...

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

Window Functions

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.

Using Joins in a Distributed Environment

There are two ways of creating joins in a distributed environment.

1. Replicate small dimension tables that are used in the join.
2. Create a shard key on the column used in the join (note that there is a limit of one shard key per table).
   1. 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.

Scenario 1: one table (Customers) is a very small table.

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;

Scenario 2: both tables are large and are joined using a 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;

Scenario 2a: both tables are large and are joined on a 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;

Logical Operator Support

Conditional Expression Support

Subquery Expression Support

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

The following table shows cast type conversion support.

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.

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[]);

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}

omnisql> SELECT UNNEST(colors) AS c FROM test_array;
Exception: UNNEST not supported in the projection list yet.

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

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

omnisql> SELECT name, colors FROM test_array WHERE colors[1]='green';
name|colors
Banana|{green, yellow}
Olive|{green, black}
Zucchini|{green, yellow}

omnisql> SELECT * FROM test_array WHERE colors IS NULL;
name|colors|qty
Rutabaga|NULL|{}

The following queries use arrays in an INTEGER field:

omnisql> SELECT name, qty FROM test_array WHERE qty[2] >1;
name|qty
Banana|{1, 2}
Pepper|{1, 2, 3}

omnisql> SELECT name, qty FROM test_array WHERE 15< ALL qty;
No rows returned.

omnisql> SELECT name, qty FROM test_array WHERE 2 = ANY qty;
name|qty
Banana|{1, 2}
Pepper|{1, 2, 3}

omnisql> SELECT COUNT(*) FROM test_array WHERE qty IS NOT NULL;
EXPR$0
8

omnisql> SELECT COUNT(*) FROM test_array WHERE CARDINALITY(qty)<0;
EXPR$0
6

LIKELY/UNLIKELY

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.