Blink SQL之创建数据结果表

Blink创建数据结果表

概述

Blink使用CREATE TABLE作为输出结果数据的格式定义，同时定义数据如何写入到目的数据结果表。

结果表有两种类型：

• Append类型：输出存储是日志系统、消息系统、操作日志类的RDS数据库等，数据流输出结果追加到存储中，不会修改原有数据。
• Update类型：输出存储生命了主键的数据库(如：RDS、HBase等)，数据流的输出存在Upsert操作。

结果表语法

CREATE TABLE tableName
    (columnName dataType [, columnName dataType ]*)
    [ WITH (propertyName=propertyValue [, propertyName=propertyValue ]*) ];

结果表示例

CREATE TABLE rds_output(
id INT,
len INT,
content VARCHAR,
PRIMARY KEY(id)
) WITH (
type='rds',
url='yourDatabaseURL',
tableName='yourTableName',
userName='yourDatabaseUserName',
password='yourDatabasePassword'
);

创建Oracle数据库结果表

注意事项

• 实时计算将每行结果数据拼成一行SQL写入目标数据库。
• 数据库中存在需要的表时，Oracle结果表会向该表写入或更新数据；不存在需要的表时，Oracle结果表会新建一个用于写入结果的表。
• 逻辑表和物理表的主键不必一致，但是逻辑表的主键必须包含物理表主键。
• 如果未定义主键，以Append方式插入数据；如果已经定义主键，以Upsert方式插入数据。

语法示例

CREATE TABLE oracle_sink(
  employee_id BIGINT,
  employee_name VARCHAR,
  employee_age INT,
  PRIMARY KEY(employee_id)
) WITH (
    type = 'oracle',
    url = '<yourUrl>',
    userName = '<yourUserName>',
    password = '<yourPassword>',
    tableName = '<yourTableName>'
);

with参数

参数	描述	是否必选	示例值
type	结果表类型	是	固定值为oracle。
url	数据库连接串	是	jdbc:oracle:thin:@192.168.171.62:1521:sit0
userName	登录数据库的用户名	是	无
password	登录数据库的密码	是	无
tableName	数据库的表名	是	无
maxRetryTimes	向结果表插入数据的最大尝试次数	否	默认值为10。
batchSize	单次写入数据的批次大小。	否	默认值为50。
bufferSize	去重的缓存大小。	否	默认值为500。
flushIntervalMs	写超时时间，单位为毫秒。	否	默认值为500。
excludeUpdateColumns	更新表的某行数据时，是否不更新该行指定的列数据。	否	默认不填写。
ignoreDelete	是否忽略删除操作。	否	默认值为false。

注意

• batchSize和bufferSize在指定主键后参数才生效。参数在达到任一阈值时都会触发数据写入。
• flushIntervalMs 如果在写超时时间内没有向数据库写入数据，系统会将缓存的数据再写一次。
• excludeUpdateColumns在更新表的某行数据时，默认不更新主键数据。

类型映射

Oracle 字段类型	实时计算Flink版字段类型
CHAR、VARCHAR、VARCHAR2	VARCHAR
FLOAT	DOUBLE
NUMBER	BIGINT
DECIMAL	DECIMAL

代码示例

CREATE TABLE oracle_source (
  employee_id BIGINT,
  employee_name VARCHAR,
  employ_age INT
) WITH (
  type = 'random'
);

CREATE TABLE oracle_sink(
  employee_id BIGINT,
  employee_name VARCHAR,
  employ_age INT,
  primary key(employee_id)
)with(
  type = 'oracle',
  url = 'jdbc:oracle:thin:@192.168.171.62:1521:sit0',
  userName = 'blink_test',
  password = 'blink_test',
  tableName = 'oracle_sink'
);

INSERT INTO oracle_sink
SELECT * FROM oracle_source;

创建交互式分析Hologres结果表

注意事项

由于Hologres是异步写入数据的，因此需要添加blink.checkpoint.fail_on_checkpoint_error=true作业参数，作业异常时才会触发Failover。

语法示例

create table Hologres_sink(
  name varchar,
  age BIGINT,
  birthday BIGINT
) with (
  type='hologres',
  dbname='<yourDbname>',
  tablename='<yourTablename>',
  username='<yourUsername>',
  password='<yourPassword>',
  endpoint='<yourEndpoint>',
  field_delimiter='|' --该参数可选。
);

WITH参数

参数	说明	是否必填	备注
type	结果表类型	是	固定值为hologres。
dbname	数据库名称。	是	无
tablename	表名称	是	无
username	用户名	是	无
password	密码	是	无
endpoint	Hologres VPC 端点信息	是	参考访问域名列表。
field_delimiter	导出数据时，不同行之间使用的分隔符。	否	默认值为"\u0002"。
mutateType	流式写入语义。	否	默认值为insertorignore。
partitionrouter	分区表写入	否	默认值为false。
ignoredelete	是否忽略撤回消息。	否	默认值为false。
createPartTable	当写入分区表时，是否根据分区值自动创建不存在的分区表。	否	false（默认值）：不会自动创建。true：自动创建。

注意

• 如果Schema不为Public时，则tableName需要填写为schema.tableName。
• field_delimiter参数不能在数据中插入分隔符，且需要与bulkload语义一同使用。
• createPartTable参数如果分区值中存在短划线（-），暂不支持自动创建分区表。

流式语义

流处理，也称为流数据或流事件处理，即对一系列无界数据或事件连续处理。

根据Hologres Sink的配置和Hologres表的属性，流式语义分为以下两种：

• Exactly-once（仅一次）：即使在发生各种故障的情况下，系统只处理一次数据或事件。
• At-least-once（至少一次）：如果在系统完全处理之前丢失了数据或事件，则从源头重新传输，因此可以多次处理数据或事件。如果第一次重试成功，则不必进行后续重试。

在Hologres结果表中使用流式语义，注意事项：

• 如果Hologres物理表未设置主键，则Hologres Sink使用At-least-once语义。

• 如果Hologres物理表已设置主键，则Hologres Sink通过主键确保Exactly-once语义。当同主键数据出现多次时，需要设置mutateType参数确定更新结果表的方式。

mutateType取值如下：

• insertorignore（默认值）：保留首次出现的数据，忽略后续所有数据。仅第一次数据流触发更新，后续忽略。无法实现局部更新。
• insertorreplace：使用后续出现的数据整行替换已有数据。根据主键覆盖更新。无法实现局部更新。
• insertorupdate：使用后续出现的数据选择性替换已有数据。根据主键覆盖更新。可以局部更新。

默认情况下，Hologres Sink只能向一张表导入数据。如果导入数据至分区表的父表，即使导入成功，也会查询数据失败。可以设置参数partitionRouter为true，开启自动将数据路由到对应分区表的功能。

注意事项：

• tablename参数需要填写为父表的表名。
• Blink Connector不会自动创建分区表，需要提前手动创建需要导入数据的分区表，否则会导入失败。

宽表Merge和局部更新功能

在把多个流的数据写到一张Hologres宽表的场景中，会涉及到宽表Merge和数据的局部更新。示例如下：

假设有两个Flink数据流，一个数据流中包含A、B和C字段，另一个数据流中包含A、D和E字段，Hologres宽表WIDE_TABLE包含A、B、C、D和E字段，其中A字段为主键。具体操作如下：

1. 使用Flink SQL创建两张Hologres结果表，其中一张表只声明A、B和C字段，另一张表只声明A、D和E字段。这两张表都映射至宽表WIDE_TABLE。
2. 两张结果表的属性设置：
   • mutatetype设置为insertorupdate，根据主键更新数据。
   • ignoredelete设置为true，防止回撤消息产生Delete请求。
3. 将两个Flink数据流的数据分别INSERT至对应的结果表中。

注意

• 宽表必须有主键。
• 每个数据流的数据都必须包含完整的主键字段。
• 列存模式的宽表Merge场景在高RPS的情况下，CPU使用率会偏高，建议关闭表中字段的Dictionary encoding功能。

类型映射

Hologres	BLINK
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[]	ARRA<VARCHAR>
NUMERIC	DECIMAL
DATE	DATE
TIMESTAMP WITH TIMEZONE	TIMESTAMP

创建云原生数据仓库AnalyticDB MySQL版2.0结果表

什么是云原生数据仓库AnalyticDB MySQL版

云原生数据仓库AnalyticDB MySQL版是阿里巴巴自主研发的海量数据实时高并发在线分析（Realtime OLAP）云计算服务，支持在毫秒级单位时间内，对千亿级数据进行实时地多维分析透视和业务探索。

DDL定义

CREATE TABLE stream_test_hotline_agent (
id INTEGER,
len BIGINT,
content VARCHAR，
PRIMARY KEY(id)
) WITH (
type='ads',
url='yourDatabaseURL',
tableName='<yourDatabaseTableName>',
userName='<yourDatabaseUserName>',
password='<yourDatabasePassword>',
batchSize='20'
);

WITH参数

参数	说明	备注
type	结果表类型	固定值为ads。
url	JDBC连接地址	云原生数据仓库AnalyticDB MySQL版数据库地址。示例：url ='jdbc:mysql://databaseName****-cn-shenzhen-a.ads.aliyuncs.com:10014/databaseName'。
tableName	表名	无
username	账号	无
password	密码	无
maxRetryTimes	写入重试次数	可选，默认值为10。
bufferSize	流入多少条数据后开始输出。	可选，默认值为5000，表示输入的数据达到5000条就开始输出。
batchSize	一次批量写入的条数	可选，默认值为1000。
batchWriteTimeoutMs	写入超时时间	可选，单位为毫秒，默认值为5000。表示如果缓存中的数据在等待5秒后，依然没有达到输出条件，系统会自动输出缓存中的所有数据。
connectionMaxActive	单连接池最大连接数	可选，默认值为30。
ignoreDelete	是否忽略delete操作	默认值为false。

注意

• 如果错误码是20015，则表示batchSize设置过大。云原生数据仓库AnalyticDB MySQL版batchSize不能超过1 MB。例如，batchSize设置为1000，则平均每条记录大小不能超过1 KB。

类型映射

云原生数据仓库AnalyticDB MySQL版字段类型	实时计算Flink版字段类型
BOOLEAN	BOOLEAN
TINYINT、SMALLINT、INT	INT
BIGINT	BIGINT
DOUBEL	DOUBLE
VARCHAR	VARCHAR
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP

创建数据总线DataHub结果表

DDL定义

create table datahub_output(
  name VARCHAR,
  age BIGINT,
  birthday BIGINT
)with(
  type='datahub',
  endPoint='<yourEndpoint>,
  project='<yourProjectName>',
  topic='<yourTopicName>',
  accessId='<yourAccessId>',
  accessKey='<yourAccessKey>',
  batchSize='<yourBatchSize>',
  batchWriteTimeoutMs='1000'
);

WITH参数

参数	参数说明	是否必填	备注
type	源表类型	是	固定值为datahub。
endPoint	endPoint地址	是	DataHub的endPoint地址，请参考DataHub域名列表。
project	DataHub项目名称	是	无
topic	DataHub中Topic名称	是	无
accessId	AccessKey ID	是	无
accessKey	AccessKey Secret	是	无
maxRetryTimes	读取最大重试次数	否	Blink 2.2.7以下版本：默认3；Blink 2.2.7及以上版本：默认20
batchSize	一次批量写入的条数	否	Blink 3.3以下版本：默认300；Blink 3.3及以上版本：默认100
batchWriteTimeoutMs	缓存数据的超时时间	否	可选，单位为毫秒，默认值为5000。表示如果缓存中的数据在等待5秒后，依然没有达到输出条件，系统会自动输出缓存中的所有数据。
maxBlockMessages	每次写入的最大Block数	否	默认值为100。
reserveMilliSecond	TIMESTAMP类型是否保留毫秒	否	默认值为false。
partitionBy	写入结果表前会根据该值进行Hash分类，数据会流向对应的结果表。	否	默认值为空，随机进行数据分配。 partitionBy决定数据流到Blink的哪个Subtask。
hashFields	指定了列名之后，相同列的值会写入到同一个Shard。	否	默认值为Null，即随机写入。可以指明多个列值，用逗号（,）分隔。例如，hashFields='a,b'。hashFields决定数据流写到DataHub的哪个Shard。

类型映射

DataHub字段类型	实时计算字段类型
BIGINT	BIGINT
TIMESTAMP	BIGINT
STRING	VARCHAR
DOUBLE	DOUBLE
BOOLEAN	BOOLEAN
DECIMAL	DECIMAL

注意

DataHub的TIMESTAMP精确到微秒，在Unix时间戳中为16位，但实时计算定义的TIMESTAMP精确到毫秒，在Unix时间戳中为13位，所以建议您使用BIGINT进行映射。如果您需要使用TIMESTAMP，建议使用计算列进行转换。

代码示例

create table datahub_input(
  name VARCHAR
) with (
  type='datahub',
  endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
  project='test1',
  topic='topic1',
  accessId='<yourAccessID>',
  accessKey='<yourAccessSecret>',
  startTime='2018-06-01 00:00:00'
);

create table datahub_output(
  name varchar
)with(
  type='datahub',
  endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
  project='test2',
  topic='topic2',
  accessId='<yourAccessID>',
  accessKey='<yourAccessSecret>',
  batchSize='1000',
  batchWriteTimeoutMs='500'
);

INSERT INTO datahub_output
SELECT 
  LOWER(name)
from datahub_input;

创建日志服务SLS结果表

注意事项

日志服务SLS结果表仅支持VARCHAR类型的字段。

什么是日志服务

日志服务SLS是针对日志类数据的一站式服务。日志服务可以帮助您快捷地完成数据采集、消费、投递以及查询分析，提升运维和运营效率，建立海量日志处理能力。日志服务本身是流数据存储，实时计算Flink版能将其作为流式数据的输入。

DDL定义

create table sls_stream(
 `name` VARCHAR,
 age BIGINT,
 birthday BIGINT
)with(
 type='sls',
 endPoint='http://cn-hangzhou-corp.sls.aliyuncs.com',
 accessId='<yourAccessId>',
 accessKey='<yourAccessKey>',
 project='<yourProjectName>',
 logstore='<yourLogstoreName>'
);

WITH参数

参数	注释说明	是否必填	备注
endPoint	EndPoint地址	是	服务入口
project	项目名	是	无
logstore	表名	是	无
accessId	AccessKey ID	是	无
accessKey	AccessKey Secret	是	无
topic	属性字段	否	默认值为空，可以将选定的字段作为属性字段topic填充。
timestampColumn	属性字段	否	默认值为空，可以将选定的字段作为属性字段timestamp填充（类型必须为INT）。如果未指定，则默认填充当前时间。
source	属性字段。日志的来源地，例如产生该日志机器的IP地址。	否	默认值为空，可以将选定的字段作为属性字段source填充。
partitionColumn	分区列	否	如果mode为partition，则该参数必填。
flushIntervalMs	触发数据写入的周期	否	默认值为2000，单位为毫秒。
reserveMilliSecond	TIMESTAMP类型是否保留毫秒值。	否	默认值为false，不保留。

类型映射

日志服务字段类型	实时计算Flink版字段类型
STRING	VARCHAR

代码示例

CREATE TABLE random_input (
  a VARCHAR, 
  b VARCHAR) with (
    type = 'random'