Blink SQL之创建数据维表

Blink创建数据维表

概述

在维表DDL语法中增加1行PERIOD FOR SYSTEM_TIME的声明，定义维表的变化周期，即可使用标准的CREATE TABLE语法定义实时计算维表。

示例

CREATE TABLE white_list (
  id varchar,
  name varchar,
  age int,
  PRIMARY KEY (id),
  PERIOD FOR SYSTEM_TIME  --定义维表的变化周期。实时计算3.x及以上版本，维表DDL中可以不声明该句，在维表JOIN时，声明FOR SYSTEM_TIME AS OF PROCTIME（）即可。
) with (
  type = 'RDS',
  ...
);

• 维表必须指定主键。维表JOIN时，ON的条件必须包含所有主键的等值条件。
• 目前仅支持源表INNER JOIN或LEFT JOIN维表。
• 维表的唯一键（UK）必须为数据库表中的唯一键。如果维表声明的唯一键不是数据库表的唯一键会产生以下影响：
  • 维表的读取速度变慢。
  • 在维表JOIN时，会从第一条数据进行JOIN，在加入Job的过程中，相同KEY的多条记录在数据库中按顺序发生变化，可能导致JOIN结果错误。

INDEX语法

维表定义要求声明PRIMARY KEY，这种情况下只能实现一对一连接。为支持一对多连接的需求，引入了INDEX语法。非Cache All的维表JOIN通过INDEX LOOKUP的方式实现一对多连接的需求。

CREATE TABLE Persons (
    ID bigint,
    LastName varchar,
    FirstName varchar,
    Nick varchar,
    Age int,
    [UNIQUE] INDEX(LastName,FirstName,Nick), --定义INDEX，不需要指定具体的类型，例如，fulltext或clustered等。
    PERIOD FOR SYSTEM_TIME
) with (
  type='RDS',
  ...
);

UNIQUE INDEX表示一对一连接，而INDEX表示一对多连接。

注意事项

• 实时计算2.2.7及以后版本支持UNIQUE CONSTRAINT（UNIQUE KEY），实时计算2.2.7以下版本可以使用PRIMARY KEY的定义。
• 在生成执行计划时，引擎优先采用UNIQUE INDEX。即如果DDL中使用INDEX，但JOIN等值连接条件中同时包含UNIQUE和NON-UNIQUE INDEX时，优先使用UNIQUE INDEX查找右表数据。
• 支持一对多连接的维表类型，例如RDS和MaxCompute。
• 您可以增加maxJoinRows参数，表示在一对多连接时，左表一条记录连接右表的最大记录数（默认值为1024）。在一对多连接的记录数过多时，可能会极大的影响流任务的性能，因此您需要增大Cache的内存（cacheSize限制的是左表key的个数）。
• 表格存储Tablestore和Hologres维表不支持使用INDEX进行一对多JOIN。

维表、源表和结果表的区别

类别	源表	结果表	维表
是否能驱动计算	是	否	否
是否能读取数据	是，直接读取。	否	是，仅通过源表和维表JOIN读取。
是否能写入数据	否	是	否
是否支持Cache	否	否	是

创建交互式分析Hologres维表

什么是交互式分析Hologres

交互式分析Hologres是实时交互分析产品，兼容PostgreSQL协议，与大数据生态紧密连接，支持高并发、低延时实时分析与处理PB级数据，可以轻松使用现有BI（Business Intelligence）工具对数据进行多维分析和业务探索。

使用限制

创建Hologres维表时建议选择行存模式，列存模式对于点查场景性能开销较大。

选择行存模式创建维表时必须设置主键，并且将主键设置为clustering key才可以工作。示例语句如下：

begin;
create table test(a int primary key, b text, c text, d float8, e int8);
call set_table_property('test', 'orientation', 'row');
call set_table_property('test', 'clustering_key', 'a');
commit;

Hologres维表的主键必须是Blink Join On的字段，Blink Join On的字段也必须是维表完整的主键字段，两者必须完全匹配。

Hologres Blink Connector的维表功能不支持一对多的输出。

不支持读取Hologres分区表的数据。

语法示例

CREATE TABLE hologres_dim_table(
  id INT,
  len INT,
  content VARCHAR,
  PRIMARY KEY (id),
  PERIOD FOR SYSTEM_TIME  --定义维表的变化周期。
) WITH (
  type='hologres',
  endpoint='...',
  dbname='...',
  tablename='...',
  username='...',
  password='...'
);

WITH参数

参数	描述	是否必选	备注
type	数据库类型。	是	固定值为hologres。
endpoint	Hologres端点。	是	无。
tablename	表名称。	是	无。
dbname	数据库名称。	是	无。
username	用户名称。	是	无。
password	密码。	是	无。

备注 如果Schema不为Public时，则tableName需要填写为schema.tableName。

CACHE参数

参数	描述	是否必选	示例值
cache	缓存策略。	否	目前交互式分析Hologres维表支持两种缓存策略：None（默认值）：无缓存。LRU：缓存维表里的部分数据。
cacheSize	缓存大小。	否	当缓存策略选择LRU时，可以设置缓存大小，默认为10000行。
cacheTTLMs	缓存失效时间，单位为毫秒。	否	当缓存策略选择LRU时，可以设置缓存失效时间，默认不过期。
partitionedJoin	是否根据JoinKey进行分区。	否	参数的取值：false（默认值）：不根据JoinKey进行分区。true：根据JoinKey进行分区，将数据分发到各JOIN节点，提高缓存命中率。
async	是否异步读取数据。	否	默认值：false

缓存注意事项

• 源表的每条数据都会触发系统先在Cache中查找数据，如果没有找到，则去物理维表中查找。
• 需要配置相关参数：缓存大小（cacheSize）和缓存更新时间间隔（cacheTTLMs）。

类型映射

Hologres字段类型	实时计算Flink版字段类型
INT	INT
INT[]	ARRAY<INT>
BIGINT	BIGINT
BIGINT[]	ARRAY<BIGINT>
REAL	FLOAT
REAL[]	ARRAY<FLOAT>
DOUBLE PRECISION	DOUBLE
DOUBLE PRECISION[]	ARRAY<DOUBLE>
BOOLEAN	BOOLEAN
BOOLEAN[]	ARRAY<BOOLEAN>
TEXT	VARCHAR
TEXT[]	ARRAY<VARCHAR>
NUMERIC	DECIMAL
DATE	DATE
TIMESTAMP WITH TIMEZONE	TIMESTAMP

代码示例

create table randomSource (a int, b VARCHAR, c VARCHAR) with (type = 'random');

create table test (
  a int, 
  b VARCHAR, 
  c VARCHAR, 
  PRIMARY KEY (a, b), PERIOD FOR SYSTEM_TIME
) with (
  type = 'hologres',
  ...
);

create table print_sink (
  a int, 
  b VARCHAR
) with (
  type = 'print', 
  `ignoreWrite` = 'false'
);

insert into print_sink
select randomSource.a, test.b from randomSource 
LEFT JOIN test FOR SYSTEM_TIME AS OF PROCTIME() 
on randomSource.a = test.a and randomSource.b = test.b;

创建表格存储Tablestore维表

什么是表格存储Tablestore

表格存储Tablestore是构建在阿里云飞天分布式系统之上的分布式NoSQL数据存储服务。表格存储通过数据分片和负载均衡技术，实现数据规模与访问并发上的无缝扩展，提供海量结构化数据的存储和实时访问服务。其功能和特性类似开源的HBase。

示例

CREATE TABLE ots_dim_table (
 id int,
 len int,
 content VARCHAR,
 PRIMARY KEY (id),
 PERIOD FOR SYSTEM_TIME--维表标识。
) WITH (
 type='ots',
 endPoint='<yourEndpoint>',
 instanceName='<yourInstanceName>',
 tableName='<yourTableName>',
 accessId='<yourAccessId>',
 accessKey='<yourAccessKey>'
);

• 在声明维表时，必须要指名主键。
• 在维表JOIN时，ON条件必须包含所有主键的等值条件。
• Tablestore的主键即表的Rowkey。

WITH参数

参数	说明	备注
type	维表类型	固定值为ots。
endPoint	表格存储的实例访问地址	VPC网络环境需要选择实例的VPC Endpoint。
instanceName	表格存储的实例名称	无
tableName	表格存储的数据表名	无
accessId	表格存储读取的AccessKey ID	无
accessKey	表格存储读取的密钥AccessKey Secret	无

CACHE参数

参数	说明	备注
cache	缓存策略	表格存储维表支持两种缓存策略：None（默认值）：无缓存。LRU：缓存维表里的部分数据。
cacheSize	缓存大小	当选择LRU缓存策略后，可以设置缓存大小，默认为10000行。
cacheTTLMs	缓存超时时间，单位为毫秒。	当选择LRU缓存策略后，可以设置缓存失效的超时时间。

代码示例

CREATE TABLE datahub_input1 (
id            BIGINT,
name        VARCHAR,
age           BIGINT
) WITH (
<