Online Specifications and Requirements for SQL#

OpenMLDB can provide real-time feature extraction services under online request mode. The DEPLOY command can deploy a SQL script feature extraction on the requested sample online. If the deployment is successful, users can perform real-time feature extraction through the Restful API or JDBC API. Note that only some SQL commands can be deployed to provide services online. To deploy these SQL commands please follow the specifications below.

Supported Statements under Online Request Mode#

Online request mode only supports SELECT query statement.

Supported `SELECT` Clause by Online Request Mode#

It is worth noting that not all SELECT query statements can be deployed online, see SELECT Statement for detail.

The following table shows the SELECT clause supported under online request mode.

SELECT Clause	Note
Simple query on single table	The so-called simple single-table query is to process the column of a table, or use operation expressions, single-row processing function (Scalar Function) and their combined expressions on the table. You need to follow the specifications of Single-table query under Online Request mode
`JOIN` Clause	OpenMLDB currently only supports LAST JOIN. For Online Request mode, please follow the specifications of LAST JOIN under Online Request mode
`WINDOW` Clause	The window clause is used to define one or several windows. Windows can be named or anonymous. Aggregate functions can be called on the window to perform some analytical computations. For Online Request mode, please follow the specifications of WINDOW under Online Request mode
`LIMIT` Clause	The LIMIT clause is used to limit the number of results. OpenMLDB currently only supports one parameter to limit the maximum number of rows of returned data.

Specifications of `SELECT` Clause Supported by Online Request Mode#

Specifications of simple single-table query under Online Request Mode#

Only column computations, expressions, and single-row processing functions (Scalar Function) and their combined expressions are supported.
Only two part from syntax perspective: select_list and from table. It does not contain GROUP BY clause, WHERE clause, HAVING clause and WINDOW clause.
Only involves the computation of one table, and does not include the computation of joined multiple tables.

Example

-- desc: SELECT all columns
SELECT * FROM t1;
  
-- desc: rename expression 1
SELECT COL1 as c1 FROM t1;
 
-- desc: rename expression 2
SELECT COL1 c1 FROM t1;

-- desc: SELECT on column expression
SELECT COL1 FROM t1;
SELECT t1.COL1 FROM t1;
 
-- desc: unary expression
SELECT -COL2 as COL2_NEG FROM t1;
  
-- desc: binary expression
SELECT COL1 + COL2 as COL12_ADD FROM t1;
 
-- desc: type cast
SELECT CAST(COL1 as BIGINT) as COL_BIGINT FROM t1;
  
-- desc: function expression
SELECT substr(COL7, 3, 6) FROM t1;

Specifications of LAST JOIN under Online Request Mode#

Only LAST JOIN is supported.
At least one JOIN condition is an EQUAL condition like left_table.column=right_table.column, and the rgith_table.column needs to be indexed as a KEY of the right table.
In the case of LAST JOIN with sorting, ORDER BY only supports column expressions, all columns of type int16, int32, int64, timestamp, and the column needs to be indexed as a timestamp (TS) of the right table.
Right TableRef
- refer to a physical table name or subquery
- for subquery, limits to
  - Simple Projection (select * from tb or select id, val from tb)
  - Window subquery, e.g select id, count(val) over w as cnt from t1 window w as (...). Here the left TableRef of last join and window subquery must have the same request table, request table is the left most physical table of SQL syntax tree
  - Since OpenMLDB 0.8.0 WHERE claused simple projection ( 例如 select * from tb where id > 10)

Example

CREATE DATABASE db1;
-- SUCCEED
    
USE db1;
-- SUCCEED: Database changed
    
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=absolute, TTL=30d));
-- SUCCEED

CREATE TABLE t2 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=absolute, TTL=30d));
-- SUCCEED

desc t1;
 --- ---------- ----------- ------ --------- 
  #   Field      Type        Null   Default  
 --- ---------- ----------- ------ --------- 
  1   col0       Varchar     YES             
  2   col1       Int         YES             
  3   std_time   Timestamp   YES             
 --- ---------- ----------- ------ --------- 
 --- -------------------- ------ ---------- ---------- --------------- 
  #   name                 keys   ts         ttl        ttl_type       
 --- -------------------- ------ ---------- ---------- --------------- 
  1   INDEX_0_1639524729   col1   std_time   43200min   kAbsoluteTime  
 --- -------------------- ------ ---------- ---------- --------------- 

Specifications of WINDOW under Online Request Mode#

Window boundary: only PRECEDING and CURRENT ROW are supported.
Window type: only ROWS and ROWS_RANGE are supported.
PARTITION BY only supports column expressions, including multiple columns, all columns needs to be indexed as a KEY. The rule apply to the main table and all union sources
ORDER BY only support column expressions, single column only, and the column needs to be indexed as a timestamp (TS). The rule apply to the main table and all union sources
Other supported keywords: EXCLUDE CURRENT_ROW, EXCLUDE CURRENT_TIME, MAXSIZE and INSTANCE_NOT_IN_WINDOW. See WindowSpec elements specifically designed by OpenMLDB for detail.
WINDOW UNION ，supports those formats:
- table ref or simple table projection，e.g t1 or select id, val from t1. The schema of main table and union source must be the same，and index optimized for columns referred by PARTITION BY, ORDER BY from union sources
- Since OpenMLDB 0.8.0, simple projection over table last join，e.g UNION (select * from t1 last join t2 ON ...). Index requirements：
  - t1 last join t2meet the requirements for LAST JOIN
  - columns refered by PARTITION BY, ORDER BY must resolved to the left most table from last join, t1 here.

Online Specifications and Requirements for SQL

Contents