创建 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仅支持只涉及一个物理表的情况,即不支持包含joinunionSelectStmt

  • 支持的聚合运算仅限: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

相关SQL#

USE DATABASE

SHOW DEPLOYMENT

DROP DEPLOYMENT