全部产品
弹性计算 会员服务 网络 安全 移动云 数加·大数据分析及展现 数加·大数据应用 管理与监控 云通信 阿里云办公 培训与认证 更多
存储与CDN 数据库 域名与网站(万网) 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 智能硬件
流计算

维表JOIN

更新时间:2018-03-20 10:30:37

什么是维表

维表是一张不断变化的表(不更新的表是变化表的一种特例)。如何查询/JOIN一张不断变化的表呢? 如果用传统的JOIN语法来表达JOIN dim_table ON xxx,会导致多次运行得到的结果不一致。所以我们在查询/JOIN维表的时候,需要明确指名我们想查看的是维表的哪个时刻的快照。因此我们引入了 SQL:2011 的 Temporal Table 的语义。

维表DDL

Blink SQL中没有专门为维表设计的DDL语法,使用标准的 CREATE TABLE 语法即可,但是需要额外增加一行PERIOD FOR SYSTEM_TIME的声明,这行声明定义了维表的变化周期,即表明该表是一张会变化的表。如下是一个简单的示例:

  1. CREATE TABLE white_list (
  2. id varchar,
  3. name varchar,
  4. age int,
  5. PRIMARY KEY (id), -- 用做维表的话,必须有声明的主键
  6. PERIOD FOR SYSTEM_TIME
  7. ) with (
  8. type = 'xxx',
  9. ...
  10. )

具体维表声明方式和参数见维表创建

注:声明一个维表的时候,必须要指名主键,维表JOIN的时候,ON的条件必须包含所有主键的等值条件。

维表JOIN

因为维表是一张不断变化的表,所以当我们在JOIN维表的时候,需要指名某条记录关联上该维表哪个时刻的快照。当前仅支持关联上当前时刻所看到的维表(未来会支持关联上左表rowtime所对应的维表快照)。

语法:

  1. SELECT column-names
  2. FROM table1 [AS <alias1>]
  3. [LEFT] JOIN table2 FOR SYSTEM_TIME AS OF PROCTIME() [AS <alias2>]
  4. ON table1.column-name1 = table2.key-name1

支持 INNER JOIN 和 LEFT JOIN,不支持 RIGHT JOIN, FULL JOIN。JOIN上维表的时候,在维表后面必须要带上FOR SYSTEM_TIME AS OF PROCTIME(),其含义是JOIN上每条数据的当前时刻所看到的维表。其工作模式是左表的一条记录到达的时候,去维表中查询并关联上匹配的数据。也就是说,如果维表插入了一条数据能匹配上之前左表的数据时,JOIN的结果流,不会发出更新的数据以弥补之前的未匹配。JOIN行为只发生在处理时间(processing time),即使维表中的数据都被删了,之前JOIN流已经发出的关联上的数据也不会被撤回或改变。

例如事件流JOIN白名单维表的SQL:

  1. SELECT e.*, w.*
  2. FROM event AS e
  3. JOIN white_list FOR SYSTEM_TIME AS OF PROCTIME() AS w
  4. ON e.id = w.id

注意:

  1. 维表JOIN的ON条件中一定要包括维表primary key的等值条件,因为我们需要根据key去查维表。当然ON条件中可以有其他条件,比如 ON event.id = white_list.id AND event.name = white_list.name,是可以的,只要包含了所有维表主键的等值条件。
  2. 维表和维表不能做JOIN!DDL声明的字段、主键等要和真实表里面定义的一致!

示例

测试数据

nameinfor:

id(bigint) name(VARCHAR) age(bigint)
1 lilei 22
2 hanmeimei 20
3 libai 28

phoneNumber:

name(VARCHAR) phoneNumber(bigint)
dufu 18867889855
baijuyi 18867889856
libai 18867889857
lilei 18867889858

测试case

  1. CREATE TABLE datahub_input1 (
  2. id BIGINT,
  3. name VARCHAR,
  4. age BIGINT
  5. ) WITH (
  6. type='datahub'
  7. );
  8. create table phoneNumber(
  9. name VARCHAR,
  10. phoneNumber bigint,
  11. primary key(name),
  12. PERIOD FOR SYSTEM_TIME
  13. )with(
  14. type='rds'
  15. );
  16. CREATE table result_infor(
  17. id bigint,
  18. phoneNumber bigint,
  19. name VARCHAR
  20. )with(
  21. type='rds'
  22. );
  23. INSERT INTO result_infor
  24. SELECT
  25. t.id
  26. ,w.phoneNumber
  27. ,t.name
  28. FROM datahub_input1 as t
  29. JOIN phoneNumber FOR SYSTEM_TIME AS OF PROCTIME() as w
  30. ON t.name = w.name;

测试结果

id(bigint) phoneNumber(bigint) name(varchar)
1 18867889858 lilei
3 18867889857 libai
本文导读目录