创建 DEPLOYMENT#

Syntax#

CreateDeploymentStmt
				::= 'DEPLOY' [DeployOptionList] DeploymentName SelectStmt

DeployOptionList
				::= DeployOption*
				    
DeployOption
				::= 'OPTIONS' '(' DeployOptionItem (',' DeployOptionItem)* ')'
				    
DeploymentName
				::= identifier

DeployOption的定义详见DEPLOYMENT属性DeployOption（可选）。

SelectStmt的定义详见Select查询语句。

DEPLOY语句可以将SQL部署到线上。OpenMLDB仅支持部署Select查询语句，并且需要满足OpenMLDB SQL上线规范和要求。

Example

在集群版的在线请求模式下，部署上线一个SQL脚本。

CREATE DATABASE db1;
-- SUCCEED

USE db1;
-- SUCCEED: Database changed

CREATE TABLE demo_table1(c1 string, c2 int, c3 bigint, c4 float, c5 double, c6 timestamp, c7 date);
-- SUCCEED: Create successfully

DEPLOY demo_deploy SELECT c1, c2, sum(c3) OVER w1 AS w1_c3_sum FROM demo_table1 WINDOW w1 AS (PARTITION BY demo_table1.c1 ORDER BY demo_table1.c6 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

-- SUCCEED

我们可以使用 SHOW DEPLOYMENT demo_deploy; 命令查看部署的详情，执行结果如下：

 --------- -------------------
  DB        Deployment
 --------- -------------------
  demo_db   demo_deploy
 --------- -------------------
1 row in set
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  SQL
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  DEPLOY demo_data_service SELECT
  c1,
  c2,
  sum(c3) OVER (w1) AS w1_c3_sum
FROM
  demo_table1
WINDOW w1 AS (PARTITION BY demo_table1.c1
  ORDER BY demo_table1.c6 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
;
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set
# Input Schema
 --- ------- ------------ ------------
  #   Field   Type         IsConstant
 --- ------- ------------ ------------
  1   c1      Varchar     NO
  2   c2      Int32       NO
  3   c3      Int64       NO
  4   c4      Float       NO
  5   c5      Double      NO
  6   c6      Timestamp   NO
  7   c7      Date        NO
 --- ------- ------------ ------------

# Output Schema
 --- ----------- ---------- ------------
  #   Field       Type       IsConstant
 --- ----------- ---------- ------------
  1   c1          Varchar   NO
  2   c2          Int32     NO
  3   w1_c3_sum   Int64     NO
 --- ----------- ---------- ------------ 

DeployOption（可选）#

DeployOption
						::= 'OPTIONS' '(' DeployOptionItem (',' DeployOptionItem)* ')'

DeployOptionItem
						::= LongWindowOption

LongWindowOption
						::= 'LONG_WINDOWS' '=' LongWindowDefinitions

目前只支持长窗口LONG_WINDOWS的优化选项。

长窗口优化#

LongWindowDefinitions
					::= 'LongWindowDefinition (, LongWindowDefinition)*'

LongWindowDefinition
					::= WindowName':'[BucketSize]

WindowName
					::= string_literal

BucketSize
					::= int_literal | interval_literal

interval_literal ::= int_literal 's'|'m'|'h'|'d'

其中BucketSize为用于性能优化的可选项，OpenMLDB会根据BucketSize设置的粒度对表中数据进行预聚合，默认为1d。

限制条件#

目前长窗口优化有以下几点限制：

SelectStmt仅支持只涉及一个物理表的情况，即不支持包含join或union的SelectStmt。
支持的聚合运算仅限：sum, avg, count, min, max, count_where, min_where, max_where, sum_where, avg_where。
执行deploy命令的时候不允许表中有数据。
对于带 where 条件的运算，如 count_where, min_where, max_where, sum_where, avg_where ，有额外限制：
1. 主表必须是内存表 (storage_mode = 'Memory')
2. BucketSize 类型应为范围类型，即取值应为interval_literal类，比如，long_windows='w1:1d'是支持的, 不支持 long_windows='w1:100'。
3. where 条件必须是 <column ref> op <const value> 或者 <const value> op <column ref>的格式。
  - 支持的 where op: >, <, >=, <=, =, !=
  - where 关联的列 <column ref>，数据类型不能是 date 或者 timestamp
为了得到最佳的性能提升，数据需按 timestamp 列的递增顺序导入。

Example

DEPLOY demo_deploy OPTIONS(long_windows="w1:1d") SELECT c1, sum(c2) OVER w1 FROM demo_table1
    WINDOW w1 AS (PARTITION BY c1 ORDER BY c2 ROWS_RANGE BETWEEN 5d PRECEDING AND CURRENT ROW);
-- SUCCEED

创建 DEPLOYMENT

Contents

创建 DEPLOYMENT#

Syntax#

DeployOption（可选）#

长窗口优化#

限制条件#

相关SQL#