全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 钉钉智能硬件
流计算

DML语法

更新时间:2017-06-07 13:26:11

INSERT INTO

语法格式:

  1. [INSERT|REPLACE] INTO TABLE tablename
  2. SELECT [DISTINCT ]sel_expr1, sel_expr2
  3. FROM table_reference
  4. [WHERE where_condition]
  5. [GROUP BY col_list]
  6. ;

INSERT/REPALCE 语法主要用途在于更新数据表操作。注意事项如下:

  • 流计算支持两类数据表更新,包括INSERT,即Append写入目的数据存储,例如写入DataHub需要使用Insert写入,因为DataHub是一个队列只能提供Append写入;以及REPLACE,即向目的数据更新写入,例如天猫双十一大屏,对于当前成交总额是不断更新上一次的写入结果因此是REPALCE写入。对于INSERT写入,RESULT表不允许定义PRIMARY KEY;对于REPLACE写入,RESULT必须定义PRIMARY KEY,且该KEY需要和实际数据存储保持一致。

    例如定义TableStore的PRIMARY KEY,须和TableStore定义的PRIMARY KEY一致,否则可能出现将数据写错乱情况!

  • 单个流计算作业支持在一个SQL(作业)里面包含多个DML操作,同样也允许包含多个数据源、多个数据目标端、多个维表。例如在一个作业文件里面包含两段完全业务上独立的SQL,分别写出到不同的数据目标端。这类场景通常发生在用户希望节省资源,用一个CU带动更多的SQL逻辑运行。但请注意,用户必须自己确保你的一个CU能够跑更多SQL逻辑,此时通常计算数据量都不大。

  • 流计算不支持单独SELECT操作,当前在执行SELECT查询之前必须执行INSERT操作将结果保存起来。同时,须注意的是,一个SQL文件支持多个源表输入和多个结果表输出。

  • INSERT DML操作不是按照名称进行字段对应!! 而是按照SQL标准按照字段顺序定义,请参考DDL定义

  • 只有RESULT表和TMP表可以执行INSERT操作,且每张表只能执行一次INSERT操作,DIM表和STREAM表不能执行INSERT操作。操作约束如下表:

表类型 操作约束
源表 只能引用(FROM),不可执行INSERT
维表 只能引用(JOIN),不可执行INSERT
结果表 仅支持INSERT操作
临时表 支持INSERT和引用操作
  • 指定DISTINCT选项时,将符合条件的记录进行去重输出。

  • sel_expr可以为表中的列名,或计算表达式,支持”*”表示表中所有的列。

  • table_reference 支持表的Join及子查询。

  • 如果使用了聚合函数,则要指定GROUP BY分类的列。

子查询

普通的SELECT是从几张表中读数据,如SELECT column_1, column_2 … FROM table_name,但查询的对象也可以是另外一个SELECT操作,需要注意的是子查询必须加别名。代码如下:

  1. INSERT INTO result_table
  2. SELECT * from
  3. (
  4. SELECT t.a,
  5. sum(t.b) AS sum_b
  6. FROM t1 t
  7. GROUP BY t.a) t1
  8. WHERE t1.sum_b > 100;

UNION ALL

语法格式:

  1. select_statement
  2. UNION ALL
  3. select_statement

UNION ALL 将两个流给合并起来,要求两个流的字段完全一模一样(字段个数,字段顺序)。示例代码如下:

示例

  1. select a.id,a.col1,a.col2 FROM stream_tableA a
  2. WHERE a.col1=1
  3. UNION ALL
  4. select b.id,b.col1,b.col2 FROM stream_tableB b
  5. WHERE b.col2=1

特别注意的是,UNION ALL不能和聚合函数(例如COUNT、SUM)混合使用。例如

  1. SELECT
  2. id1,
  3. name1,
  4. count(*) as total_count1
  5. FROM
  6. left
  7. UNION ALL
  8. SELECT
  9. id2,
  10. name2,
  11. count(*) as total_count2
  12. FROM
  13. right

上述用法当前SQL可能导致列错乱,不是正确的做法! 因此建议如下写法:

  1. CREATE TMP TABLE t1
  2. (
  3. id BIGINT,
  4. name STRING,
  5. total BIGINT
  6. );
  7. INSERT INTO t1
  8. SELECT
  9. id1,
  10. name1,
  11. count(*) as total_count1
  12. FROM
  13. left;
  14. CREATE TMP TABLE t2
  15. (
  16. id BIGINT,
  17. name STRING,
  18. total BIGINT
  19. );
  20. INSERT INTO t2
  21. SELECT
  22. id2,
  23. name2,
  24. count(*) as total_count2
  25. FROM
  26. left;
  27. INSERT INTO XXX
  28. SELECT
  29. id,
  30. name,
  31. total
  32. FROM t1
  33. UNION ALL
  34. SELECT
  35. id,
  36. name,
  37. total
  38. FROM t2

JOIN

语法格式:

  1. join_table:
  2. table_reference JOIN table_factor [join_condition]
  3. | table_reference {LEFT OUTER |INNER} JOIN table_reference join_condition
  4. table_reference:
  5. table_factor
  6. | join_table
  7. table_factor:
  8. tbl_name [alias]
  9. | table_subquery alias
  10. | ( table_references )
  11. join_condition:
  12. ON equality_expression ( AND equality_expression )*

说明:

  • 当前仅支持流式和静态维表关联,不支持双流的关联

  • 只支持等值连接, 不支持不等连接。

  • LEFT JOIN 会从左表(shop)那里返回所有的记录,即使在右表(sale_detail)中没有匹配的行。

CASE WHEN

CASE WHEN表达式可以根据表达式value的计算结果灵活返回不同的值,阿里云流计算支持两种CASE WHEN表达式:

  1. CASE value
  2. WHEN (_condition1) THEN result1
  3. WHEN (_condition2) THEN result2
  4. ...
  5. ELSE resultn
  6. END

或者

  1. CASE
  2. WHEN (_condition1) THEN result1
  3. WHEN (_condition2) THEN result2
  4. WHEN (_condition3) THEN result3
  5. ...
  6. ELSE resultn
  7. END

实例一

  1. SELECT
  2. CASE
  3. WHEN shop_name IS NULL THEN 'default_region'
  4. WHEN shop_name LIKE 'hang%' THEN 'zj_region'
  5. END AS region
  6. FROM sale_detail;

实例二

  1. SELECT
  2. CASE shop_name
  3. WHEN IS NULL THEN 'default_region'
  4. WHEN LIKE 'hang%' THEN 'zj_region'
  5. END AS region
  6. FROM sale_detail;

表达式

当前select子句、where子句、group by子句、distribute by子句等可以包含以下表达式:

  • Column字段: 直接Column名,用户可以使用*代表所有列
  • CASE WHEN 子句
  • 系统内置函数

以下内容在公共云暂不提供:

  • UDF:用户自定义函数,公共云暂不提供
  • UDAF:聚合函数。
  • UDAF(UDF):聚合函数+用户自定义函数。
  • UDF(UDF):函数嵌套使用。
  • UDAF (case when):聚合函数嵌套case when使用。
本文导读目录