目录
四、DDL(Data Definition Language)数据定义
*9) AS select_statement(CTAS)基于查询的结果创建和填充表。
(2)Intersect 和 Intersect All(交)
*1)将flink-sql-connector-kafka-1.17.0.jar上传到flink的lib目录下
(6)将创建mysql的catalog的sql写到配置文件中
(2)启动hadoop,重启flink集群和sql-client
Table API和SQL是最上层的API,在Flink中这两种API被集成在一起,SQL执行的对象也是Flink中的表(Table),所以我们一般会认为它们是一体的。
一、sql-client准备
1.基于yarn-session模式
(1)启动hadoop
cd /opt/module/
myhadoop.sh start
(2)启动flink
/opt/module/flink-1.17.0/bin/yarn-session.sh -d
(3)启动Flink的sql-client
/opt/module/flink-1.17.0/bin/sql-client.sh embedded -s yarn-session
2.常用配置
#结果显示模式:默认table,还可以设置为tableau、changelog
SET sql-client.execution.result-mode=tableau;
#执行环境,默认streaming,也可以设置batch
SET execution.runtime-mode=streaming;
#默认并行度
SET parallelism.default=1;
#设置状态TTL
SET table.exec.state.ttl=1000;
在yarn-session中每次连接之前创建的数据库和配置都会消失,为了避免每次设置配置以及创建数据库,我们可以把这些初始化文件写在配置文件中,启动sql-client去读配置文件。
cd /opt/module/flink-1.17.0/
vim conf/sql-client-init.sql
######################################
SET sql-client.execution.result-mode=tableau;
CREATE DATABASE mydatabase;
######################################
#启动sql-client
/opt/module/flink-1.17.0/bin/sql-client.sh embedded -s yarn-session -i conf/sql-client-init.sql
show databases;
然后我们查看数据库就能看到我们初始化的数据库了。
二、流处理中的表
关系型表/SQL | 流处理 | |
处理的数据对象 | 字段元组的有界集合 | 字段元组的无限序列 |
查询(Query)对数据的访问 | 可以访问到完整的数据输入 | 无法访问到所有数据,必须“持续”等待流式输入 |
查询终止条件 | 生成固定大小的结果集后终止 | 永不停止,根据持续收到的数据不断更新查询结果 |
1.动态表(Dynamic Tables)
当流中有新数据到来,初始的表中会插入一行;而基于这个表定义的SQL查询,就应该在之前的基础上更新结果。这样得到的表就会不断地动态变化,被称为“动态表”(Dynamic Tables)。
动态表里面的数据会随时间变化。
2.持续查询(Continuous Query)
动态表可以像静态的批处理表一样进行查询操作。由于数据在不断变化,因此基于它定义的SQL查询也不可能执行一次就得到最终结果。这样一来,我们对动态表的查询也就永远不会停止,一直在随着新数据的到来而继续执行。这样的查询就被称作“持续查询”(Continuous Query)。
对动态表定义的查询操作,都是持续查询;而持续查询的结果也会是一个动态表。
- 流(stream)被转换为动态表(dynamic table);
-
对动态表进行持续查询(continuous query),生成新的动态表
-
生成的动态表被转换成流
3.将流转换成动态表
如果把流看作一张表,那么流中每个数据的到来,都应该看作是对表的一次插入(Insert)操作,会在表的末尾添加一行数据。因为流是连续不断的,而且之前的输出结果无法改变、只能在后面追加;所以我们其实是通过一个只有插入操作(insert-only)的更新日志(changelog)流,来构建一个表。
4.用SQL持续查询
(1)更新(Update)查询
当原始动态表不停地插入新的数据时,查询统计数量的值可能会新增也可能会更新,这种持续查询被称为更新查询(Update Query),更新查询得到的结果表如果想要转换成DataStream,必须调用toChangelogStream()方法。
(2)追加(Append)查询
只有INSERT操作被称为追加查询(Append Query)
5.将动态表转换为流
(1)仅追加(Append-only)流
仅通过插入(Insert)更改来修改的动态表,可以直接转换为“仅追加”流。这个流中发出的数据,其实就是动态表中新增的每一行。
(2)撤回(Retract)流
撤回流是包含两类消息的流,添加(add)消息和撤回(retract)消息。
简单说,就是把之前的数据先撤回,然后重新添加,这样实现更新效果。
(3)更新插入(Upsert)流
更新插入流中只包含两种类型的消息:更新插入(upsert)消息和删除(delete)消息。
对于更新插入流来说,INSERT插入操作和UPDATE更新操作,统一被编码为upsert消息;而DELETE删除操作则被编码为delete消息。
三、时间属性
时间属性可以在创建表的DDL里直接定义为一个字段,也可以在DataStream转换成表时定义。时间属性的数据类型必须为TIMESTAMP
时间属性的定义分成事件时间(event time)和处理时间(processing time)两种情况。
1.事件时间
通过WATERMARK语句来定义事件时间属性,把ts字段定义为事件时间属性,而且基于ts设置了5秒的水位线延迟。
CREATE TABLE EventTable(
user STRING,
url STRING,
ts TIMESTAMP(3),
#定义时间属性
WATERMARK FOR ts AS ts - INTERVAL '5' SECOND
) WITH (
...
);
注:因为时间戳是13位精确到毫秒的,我们如果水平线设置为秒的话涉及到时间精确度的问题:
time_ltz AS TO_TIMESTAMP_LTZ(ts, 3),
2.处理时间
调用系统内置的PROCTIME()函数来指定当前的处理时间属性
CREATE TABLE EventTable(
user STRING,
url STRING,
ts AS PROCTIME()
) WITH (
...
);
四、DDL(Data Definition Language)数据定义
1.数据库
#创建数据库
CREATE DATABASE db_flink;
CREATE DATABASE IF NOT EXISTS db_flink;
#查询所有数据库
SHOW DATABASES
#查询当前数据库
SHOW CURRENT DATABASE
#修改数据库
ALTER DATABASE [catalog_name.]db_name SET (key1=val1, key2=val2, ...)
#删除数据库
#RESTRICT:删除非空数据库会触发异常。默认启用
#CASCADE:删除非空数据库也会删除所有相关的表和函数
DROP DATABASE [IF EXISTS] [catalog_name.]db_name [ (RESTRICT | CASCADE) ]
#切换当前数据库
USE database_name;
2.表
(1)创建表
*1)常规列
*2)元数据列
元数据列由 METADATA 关键字标识,允许访问数据源本身具有的一些元数据。使用VIRTUAL关键字标识字段只读,而不会持久化。
比如kafka中有一个自带的字段叫timestamp记录读取和写入时间戳,我们可以直接读取。
CREATE TABLE MyTable (
`user_id` BIGINT,
`name` STRING,
#只读字段
`offset` BIGINT METADATA VIRTUAL,
`record_time` TIMESTAMP_LTZ(3) METADATA FROM 'timestamp'
) WITH (
'connector' = 'kafka'
...
);
#如果自定义的列名称和 Connector 中定义 metadata 字段的名称一样, FROM xxx 子句可省略
`timestamp` TIMESTAMP_LTZ(3) METADATA
#如果自定义列的数据类型和 Connector 中定义的 metadata 字段的数据类型不一致,程序运行时会自动 cast强转,但是这要求两种数据类型是可以强转的
`timestamp` BIGINT METADATA
*3)计算列
计算列是使用语法column_name AS computed_column_expression生成的虚拟列。在物理上并不存储在表中,只能读不能写。
CREATE TABLE MyTable (
`user_id` BIGINT,
`price` DOUBLE,
`quantity` DOUBLE,
`cost` AS price * quanitity
) WITH (
'connector' = 'kafka'
...
);
*4)定义Watermark
#严格升序,只要相等或者小于之前的,就认为是迟到的数据
WATERMARK FOR rowtime_column AS rowtime_column
#递增,一般不用,允许有相同的时间戳出现
WATERMARK FOR rowtime_column AS rowtime_column - INTERVAL '0.001' SECOND
#有界无序,可用于设置最大乱序时间,推荐
WATERMARK FOR rowtime_column AS rowtime_column – INTERVAL 'string' timeUnit
*5)主键
唯一,并且它们不包含NULL值,只支持 not enforced。
CREATE TABLE MyTable (
`user_id` BIGINT,
`name` STRING,
PARYMARY KEY(user_id) not enforced
) WITH (
'connector' = 'kafka'
...
);
*6)分区(PARTITIONED BY)
*7)with语句
指定外部存储系统的元数据信息。
CREATE TABLE KafkaTable (
`user_id` BIGINT,
`name` STRING,
`ts` TIMESTAMP(3) METADATA FROM 'timestamp'
) WITH (
'connector' = 'kafka',
'topic' = 'user_behavior',
'properties.bootstrap.servers' = 'localhost:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv'
)
*8)LIKE
用于基于现有表的定义创建表。此外,用户可以扩展原始表或排除表的某些部分。
CREATE TABLE Orders (
`user` BIGINT,
product STRING,
order_time TIMESTAMP(3)
) WITH (
'connector' = 'kafka',
'scan.startup.mode' = 'earliest-offset'
);
CREATE TABLE Orders_with_watermark (
-- Add watermark definition
WATERMARK FOR order_time AS order_time - INTERVAL '5' SECOND
) WITH (
-- Overwrite the startup-mode
'scan.startup.mode' = 'latest-offset'
)
LIKE Orders;
*9) AS select_statement(CTAS)
基于查询的结果创建和填充表。
CREATE TABLE my_ctas_table
WITH (
'connector' = 'kafka',
...
)
AS SELECT id, name, age FROM source_table WHERE mod(id, 10) = 0;
CTAS暂不支持创建临时表,指定显式列,指定显式水印,创建分区表,指定主键约束
*10)创建表简单示例
CREATE TABLE test(
id INT,
ts BIGINT,
vc INT
) WITH (
'connector' = 'print'
);
CREATE TABLE test1 (
`value` STRING
)
LIKE test;
(2)查看表
#查看所有表
#%匹配任意数量的字符,甚至零字符,\%匹配一个'%'字符
#_只匹配一个字符,\_只匹配一个'_'字符
SHOW TABLES [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] LIKE <sql_like_pattern> ]
#查看表信息
{ DESCRIBE | DESC } [catalog_name.][db_name.]table_name
(3)修改表
#修改表名
ALTER TABLE [catalog_name.][db_name.]table_name RENAME TO new_table_name
#修改表属性
ALTER TABLE [catalog_name.][db_name.]table_name SET (key1=val1, key2=val2, ...)
(4)删除表
DROP [TEMPORARY] TABLE [IF EXISTS] [catalog_name.][db_name.]table_name
五、查询
#启动hadoop
cd /opt/module/
myhadoop.sh start
#启动flink
/opt/module/flink-1.17.0/bin/yarn-session.sh -d
#启动sql-client
cd /opt/module/flink-1.17.0/
/opt/module/flink-1.17.0/bin/sql-client.sh embedded -s yarn-session -i conf/sql-client-init.sql
1.DataGen & Print
(1)创建数据生成器源表
设置数据源是随机生成器,并且指定了生成规则。以及查到之后做打印
CREATE TABLE source (
id INT,
ts BIGINT,
vc INT
) WITH (
'connector' = 'datagen',
'rows-per-second'='1',
'fields.id.kind'='random',
'fields.id.min'='1',
'fields.id.max'='10',
'fields.ts.kind'='sequence',
'fields.ts.start'='1',
'fields.ts.end'='1000000',
'fields.vc.kind'='random',
'fields.vc.min'='1',
'fields.vc.max'='100'
);
CREATE TABLE sink (
id INT,
ts BIGINT,
vc INT
) WITH (
'connector' = 'print'
);
(2)查询源表
select * from source;
(3)插入sink表并查询
INSERT INTO sink select * from source;
2.With子句
定义了仅为一个查询而存在的临时视图。记得中间不要加逗号。
WITH source_with_total AS (
SELECT id, vc+10 AS total
FROM source
)
SELECT id, SUM(total)
FROM source_with_total
GROUP BY id;
3.SELECT & WHERE 子句
SELECT id, vc + 10 FROM source;
#自定义source的数据
SELECT id, price FROM (VALUES (1, 2.0), (2, 3.1)) AS t (order_id, price)
4.SELECT DISTINCT 子句
用作根据 key 进行数据去重
5.分组聚合
SUM()、MAX()、MIN()、AVG(),COUNT(),GROUP BY
想要将结果表转换成流或输出到外部系统,必须采用撤回流(retract stream)或更新插入流(upsert stream)的编码方式;如果在代码中直接转换成DataStream打印输出,需要调用toChangelogStream()。
CREATE TABLE source1 (
dim STRING,
user_id BIGINT,
price BIGINT,
#精确到豪秒
row_time AS cast(CURRENT_TIMESTAMP as timestamp(3)),
#row_time 作为事件时间,并且可以延迟5秒
WATERMARK FOR row_time AS row_time - INTERVAL '5' SECOND
) WITH (
'connector' = 'datagen',
'rows-per-second' = '10',
'fields.dim.length' = '1',
'fields.user_id.min' = '1',
'fields.user_id.max' = '100000',
'fields.price.min' = '1',
'fields.price.max' = '100000'
);
CREATE TABLE sink1 (
dim STRING,
pv BIGINT,
sum_price BIGINT,
max_price BIGINT,
min_price BIGINT,
uv BIGINT,
window_start bigint
) WITH (
'connector' = 'print'
);
insert into sink1
select dim,
count(*) as pv,
sum(price) as sum_price,
max(price) as max_price,
min(price) as min_price,
-- 计算 uv 数
count(distinct user_id) as uv,
cast((UNIX_TIMESTAMP(CAST(row_time AS STRING))) / 60 as bigint) as window_start
from source1
group by
dim,
-- row_time 是毫秒,UNIX_TIMESTAMP得到秒的时间戳,将秒级别时间戳 / 60 转化为 1min,
cast((UNIX_TIMESTAMP(CAST(row_time AS STRING))) / 60 as bigint)
多维分析:
Grouping sets:聚合所有情况
Rollup:聚合数量从多到少
Cube:聚合数量从少到多
SELECT
supplier_id
, rating
, product_id
, COUNT(*)
FROM (
VALUES
('supplier1', 'product1', 4),
('supplier1', 'product2', 3),
('supplier2', 'product3', 3),
('supplier2', 'product4', 4)
)
-- 供应商id、产品id、评级
AS Products(supplier_id, product_id, rating)
GROUP BY GROUPING SETS(
(supplier_id, product_id, rating),
(supplier_id, product_id),
(supplier_id, rating),
(supplier_id),
(product_id, rating),
(product_id),
(rating),
()
);
6.分组窗口聚合(不推荐)
从1.13版本开始,分组窗口聚合已经标记为过时,推荐窗口TVF聚合。
计算每一个窗口的聚合结果,SQL中只支持基于时间的窗口,不支持基于元素个数的窗口。
- TUMBLE(time_attr, interval):滚动窗口。第一个参数:时间属性,第二个参数窗口长度。
- HOP(time_attr, interval, interval):滑动窗口。第一个参数:时间属性,第二个参数步长,第三个参数窗口长度。
- SESSION(time_attr, interval):会话窗口。第一个参数:时间属性,第二个参数时间间隔。
- TUMBLE_START,HOP_START,SESSION_START:窗口起始时间
- TUMBLE_END,HOP_END,SESSION_END:窗口结束时间
#准备数据
CREATE TABLE ws (
id INT,
vc INT,
pt AS PROCTIME(), --处理时间
et AS cast(CURRENT_TIMESTAMP as timestamp(3)), --事件时间
WATERMARK FOR et AS et - INTERVAL '5' SECOND --watermark
) WITH (
'connector' = 'datagen',
'rows-per-second' = '10',
'fields.id.min' = '1',
'fields.id.max' = '3',
'fields.vc.min' = '1',
'fields.vc.max' = '100'
);
#滚动窗口示例(时间属性字段,窗口长度)
select
id,
TUMBLE_START(et, INTERVAL '5' SECOND) wstart,
TUMBLE_END(et, INTERVAL '5' SECOND) wend,
sum(vc) sumVc
from ws
group by id, TUMBLE(et, INTERVAL '5' SECOND);
#滑动窗口(时间属性字段,滑动步长,窗口长度)
select
id,
HOP_START(pt, INTERVAL '3' SECOND,INTERVAL '5' SECOND) wstart,
HOP_END(pt, INTERVAL '3' SECOND,INTERVAL '5' SECOND) wend,
sum(vc) sumVc
from ws
group by id, HOP(et, INTERVAL '3' SECOND,INTERVAL '5' SECOND);
#会话窗口(时间属性字段,会话间隔)
select
id,
SESSION_START(et, INTERVAL '5' SECOND) wstart,
SESSION_END(et, INTERVAL '5' SECOND) wend,
sum(vc) sumVc
from ws
group by id, SESSION(et, INTERVAL '5' SECOND);
7.窗口表值函数(TVF)聚合
对比GroupWindow,TVF窗口更有效和强大。包括:提供更多的性能优化手段,支持GroupingSets语法,可以在window聚合中使用TopN,提供累积窗口。
对于窗口表值函数,窗口本身返回的是就是一个表,所以窗口会出现在FROM后面,GROUP BY后面的则是窗口新增的字段window_start和window_end。
FROM TABLE(
窗口类型(TABLE 表名, DESCRIPTOR(时间字段),INTERVAL时间…)
)
GROUP BY [window_start,][window_end,] --可选
(1)滚动窗口
#滚动窗口
SELECT
window_start,
window_end,
id , SUM(vc) as sumVC
FROM TABLE(
TUMBLE(TABLE ws, DESCRIPTOR(et), INTERVAL '5' SECONDS))
GROUP BY window_start, window_end, id;