Flink集群搭建
1、独立集群
1、上传解压配置环境变量
# 解压
tar -xvf flink-1.15.2-bin-scala_2.12.tgz
# 配置环境变量
vim /etc/profile
export FLINK_HOME=/usr/local/soft/flink-1.15.2
export PATH=$PATH:$FLINK_HOME/bin
source /etc/profile
2、修改配置文件
- flink-conf.yaml
jobmanager.rpc.address: master
jobmanager.bind-host: 0.0.0.0
taskmanager.bind-host: 0.0.0.0
taskmanager.host: localhost # noe1和node2需要单独修改
taskmanager.numberOfTaskSlots: 4
rest.address: master
rest.bind-address: 0.0.0.0
- masters
master:8081
- workers
node1
node2
3、同步到所有节点
scp -r flink-1.15.2 node1:`pwd`
scp -r flink-1.15.2 node2:`pwd`
# 修改node1和node2中地taskmanager.host
taskmanager.host: node1
taskmanager.host: node2
4、启动Flink独立集群
start-cluster.sh
# stop-cluster.sh
# flink web ui
http://master:8081
5、提交任务
- 将代码打包上传到服务器提交
flink run -c com.shujia.flink.core.Demo1StreamWordCount flink-1.0.jar
- 在flink web ui中直接提交
2、Flink on Yarn
flink on yarn模式:将flink地任务提交到yarn上运行
1、整合
# 在环境变量中配置HADOOP_CLASSSPATH
vim /etc/profile
export HADOOP_CLASSPATH=`hadoop classpath`
source /etc/profile
3、Flink on yarn部署模式
1、Application Mode
1、将任务提交到yarn上运行,yarn会为每一个flink地任务启动一个jobmanager和一个或者多个taskmanasger
2、代码main函数不再本地运行,dataFlow不再本地构建,如果代码报错在本地看不到详细地错误日志
flink run-application -t yarn-application -c com.shujia.flink.core.Demo1StreamWordCount flink-1.0.jar
# 查看yarn的日志
yarn logs -applicationId application_1717039073374_0001
2、Per-Job Cluster Mode
1、将任务提交到yarn上运行,yarn会为每一个flink地任务启动一个jobmanager和一个或者多个taskmanasger
2、代码地main函数在本地启动,在本地构建dataflow,再将dataflow提交给jobmanager,如果代码报错再本地可以烂到部分错误日志
flink run -t yarn-per-job -c com.shujia.flink.core.Demo1StreamWordCount flink-1.0.jar
3、Session Mode
1、先再yarn中启动一个jobmanager, 不启动taskmanager
2、提交任务地时候再动态申请taskmanager
3、所有使用session模式提交的任务共享同一个jobmanager
4、类似独立集群,只是集群在yarn中启动了,可以动态申请资源
5、一般用于测试
# 1、先启动会话集群
yarn-session.sh -d
# 2、在提交任务
flink run -t yarn-session -Dyarn.application.id=application_1717039073374_0004 -c com.shujia.flink.core.Demo1StreamWordCount flink-1.0.jar
# 在网页中直接提交
Kafka搭建文档
1、上传解压修改环境变量
# 解压
tar -xvf kafka_2.11-1.0.0.tgz
mv kafka_2.11-1.0.0 kafka-1.0.0
# 配置环境变量
vim /etc/profile
export KAFKA_HOME=/usr/local/soft/kafka-1.0.0
export PATH=$PATH:$KAFKA_HOME/bin
source /etc/profile
2、修改配置文件
vim config/server.properties
broker.id=0 每一个节点broker.id 要不一样
zookeeper.connect=master:2181,node1:2181,node2:2181/kafka
log.dirs=/usr/local/soft/kafka-1.0.0/data 数据存放的位置
3、将kafka文件同步到node1,node2
# 同步kafka文件
scp -r kafka-1.0.0/ node1:`pwd`
scp -r kafka-1.0.0/ node2:`pwd`
# 将master中的而环境变量同步到node1和node2中
scp /etc/profile node1:/etc/
scp /etc/profile node2:/etc/
# 在ndoe1和node2中执行source
source /etc/profile
4、修改node1和node2中的broker.id
vim config/server.properties
# node1
broker.id=1
# node2
broker.id=2
5、启动kafka
# 1、需要启动zookeeper, kafka使用zo保存元数据
# 需要在每隔节点中执行启动的命令
zkServer.sh start
# 查看启动的状体
zkServer.sh status
# 2、启动kafka,每个节点中都要启动(去中心化的架构)
# -daemon后台启动
kafka-server-start.sh -daemon /usr/local/soft/kafka-1.0.0/config/server.properties
# 测试是否成功
#生产者
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic shujia
# 消费者
--from-beginning 从头消费,, 如果不在执行消费的新的数据
kafka-console-consumer.sh --bootstrap-server master:9092,node1:9092,node2:9092 --from-beginning --topic shujia
使用kafka
1、创建topic
在生产和消费数据时,如果topic不存在会自动创建一个分区为1,副本为1的topic
--replication-factor ---每一个分区的副本数量, 同一个分区的副本不能放在同一个节点,副本的数量不能大于kafak集群节点的数量
--partition --分区数, 根据数据量设置
--zookeeper zk的地址,将topic的元数据保存在zookeeper中
kafka-topics.sh --create --zookeeper master:2181,node1:2181,node2:2181/kafka --replication-factor 2 --partitions 3 --topic bigdata
2、查看topic描述信息
kafka-topics.sh --describe --zookeeper master:2181,node1:2181,node2:2181/kafka --topic shujia
3、获取所有topic
__consumer_offsetsL kafka用于保存消费便宜量的topic
kafka-topics.sh --list --zookeeper master:2181,node1:2181,node2:2181/kafka
4、创建控制台生产者
kafka-console-producer.sh --broker-list master:9092,node1:9092,node3:9092 --topic bigdata
5、创建控制台消费者
--from-beginning 从头消费,, 如果不在执行消费的新的数据
kafka-console-consumer.sh --bootstrap-server master:9092,node1:9092,node3:9092 --from-beginning --topic bigdata
kafka数据保存的方式
# 1、保存的文件
/usr/local/soft/kafka_2.11-1.0.0/data
# 2,每一个分区每一个副本对应一个目录
# 3、每一个分区目录中可以有多个文件, 文件时滚动生成的
00000000000000000000.log
00000000000000000001.log
00000000000000000002.log
# 4、滚动生成文件的策略
log.segment.bytes=1073741824
log.retention.check.interval.ms=300000
# 5、文件删除的策略,默认时7天,以文件为单位删除
log.retention.hours=168
2、Flink整合kafka
1、idea中整合
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-connector-kafka</artifactId>
<version>${flink.version}</version>
</dependency>
2、集群中整合
# 将flink-sql-connector-kafka-1.15.2.jar包上传到Flink的lib目录下
cd /usr/local/soft/flink-1.15.2/lib
状态与容错
1、状态
之前的计算结果可以看作时状态,基于之前的结果进行计算可以称为有状态计算
2、checkpoint
可以定时将flink计算的状态持久化到hdfs中,如果任务执行失败,可以基于hdfs中保存到的状态恢复任务,保证之前的结果不丢失
1、开启checkpoint的方式
- 在代码中单独开启
// 每 1000ms 开始一次 checkpoint
env.enableCheckpointing(5000);
// 高级选项:
// 当手动取消任务时,是否保留HDFS中保留hdfs中的快照
env.getCheckpointConfig().setExternalizedCheckpointCleanup(CheckpointConfig.ExternalizedCheckpointCleanup.RETAIN_ON_CANCELLATION);
//flink计算的状态会先保存在taskmanager中,当触发checkpoint时会将状态持久化到hdfs中
//指定状态在算子中保存的位置(状态后端)
//HashMapStateBackend:将状态保存在taskmanager的内存中
env.setStateBackend(new HashMapStateBackend());
//指定checkpoint保存快照的位置
env.getCheckpointConfig().setCheckpointStorage("hdfs://master:9000/flink/checkpoint");
-
在配置文件中统一开启
vim flink-conf.yaml
execution.checkpointing.interval: 5000
execution.checkpointing.externalized-checkpoint-retention: RETAIN_ON_CANCELLATION
execution.checkpointing.max-concurrent-checkpoints: 1
execution.checkpointing.min-pause: 0
execution.checkpointing.mode: EXACTLY_ONCE
execution.checkpointing.timeout: 10min
execution.checkpointing.tolerable-failed-checkpoints: 0
execution.checkpointing.unaligned: false
state.backend: hashmap
state.checkpoints.dir: hdfs://master:9000/flink/checkpoint
2、使用checkpoint
- 第一次提交任务之间提交
flink run -t yarn-session -p 3 -Dyarn.application.id=application_1717039073374_0009 -c com.shujia.flink.state.Demo5ExactlyOnceSInkKafka flink-1.0.jar
- 重庆任务时基于hdfs中的快照重启
# -s 指定恢复任务的位置
flink run -t yarn-session -p 3 -Dyarn.application.id=application_1717039073374_0009 -c com.shujia.flink.state.Demo5ExactlyOnceSInkKafka -s hdfs://master:9000/flink/checkpoint/aa5c16e40767a315674780ba01a92fb3/chk-2 flink-1.0.jar
Flink SQL
1、sql命令行
# 启动flink集群
yarn-session.sh -d
# 进入sql命令行
sql-client.sh
# 1、创建表,数据源时kafka
CREATE TABLE students (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'kafka',
'topic' = 'students', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'csv' -- 指定数据的格式
);
# 2、编写sql进行连续查询
select
clazz,count(1)as num
from students
group by clazz;
# 3、生产数据
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic students
2、sql命令行打印结果模式
1、表格模式(table mode)默认
在内存中实体化结果,并将结果用规则的分页表格可视化展示出来。执行如下命令启用:
SET 'sql-client.execution.result-mode' = 'table';
2、变更日志模式(changelog mode
不会实体化和可视化结果,而是由插入(
+
)和撤销(-
)组成的持续查询产生结果流。
SET 'sql-client.execution.result-mode' = 'changelog';
3、Tableau模式(tableau mode)
更接近传统的数据库,会将执行的结果以制表的形式直接打在屏幕之上。具体显示的内容会取决于作业 执行模式的不同(
execution.type
):
SET 'sql-client.execution.result-mode' = 'tableau';
3、处理模式
1、流处理模式
1、可以用于处理有界流和无界流
2、流处理模式输出连续结果
3、流处理模式底层时持续流模型
SET 'execution.runtime-mode' = 'streaming';
2、批处理模式
1、批处理模式只能用于处理有界流
2、输出最终结果
3、底层是MapReduce模型
SET 'execution.runtime-mode' = 'batch';
4、连接器
1、kafka
- kafka source
-- 创建biao --- 无界流
-- TIMESTAMP(3): 时flink总的时间字段
CREATE TABLE students_kafka (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING,
`event_time` TIMESTAMP(3) METADATA FROM 'timestamp',-- 获取kfka时间戳
`partition` BIGINT METADATA VIRTUAL, -- 获取kafka数据所在的分区
`offset` BIGINT METADATA VIRTUAL,-- 偏移量
-- 指定时间字段和水位线生成策略
WATERMARK FOR event_time AS event_time - INTERVAL '5' SECOND
) WITH (
'connector' = 'kafka',
'topic' = 'students',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
select id,name,event_time,`partition`,`offset` from students_kafka;
-- 每隔5秒统计每个班级的人数
select
clazz,
TUMBLE_START(event_time,INTERVAL '5' SECOND) as win_start,
TUMBLE_END(event_time,INTERVAL '5' SECOND) as win_end,
count(id) as num
from
students_kafka
group by
clazz,
-- 滚动的事件时间窗口
TUMBLE(event_time,INTERVAL '5' SECOND);
- kafka sink
-- 创建sink表
CREATE TABLE students_kafka_sink (
id STRING,
name STRING
) WITH (
'connector' = 'kafka',
'topic' = 'id_name',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv'
);
-- 1、将仅插入的结果写入sink表
insert into students_kafka_sink
select id,name from
students_kafka;
-- 查看结果
kafka-console-consumer.sh --bootstrap-server master:9092,node1:9092,node2:9092 --from-beginning --topic id_name
select * from students_kafka_sink;
-- 2、将更新更改查询结果写入kafka
-- 将更新更改的流写入kafka需要使用canal-json格式,
-- canal-json中带上了数据操作的类型
-- {"data":[{"clazz":"理科六班","num":377}],"type":"INSERT"}
CREATE TABLE clazz_num (
clazz STRING,
num BIGINT
) WITH (
'connector' = 'kafka',
'topic' = 'clazz_num',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'canal-json'
);
insert into clazz_num
select
clazz,
count(1) as num
from
students_kafka
group by
clazz;
-- 查看结果
kafka-console-consumer.sh --bootstrap-server master:9092,node1:9092,node2:9092 --from-beginning --topic clazz_num
select * from clazz_num;
2、JDBC
- 整合
# 将依赖包上传到flink的lib目录下
flink-connector-jdbc-1.15.2.jar
mysql-connector-java-5.1.47.jar
# 依赖更新后需要重启集群才会生效
yarn application -list
yarn application -kill [appid]
yarn-session.sh -d
sql-client.sh
- mysql source
-- 创建soure 表 --- 有界流
-- 字段名称和字段类型需要和数据库中保存一致
CREATE TABLE students_mysql (
id int,
name STRING,
age INT,
gender STRING,
clazz STRING
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://master:3306/bigdata29',
'table-name' = 'students',
'username' ='root',
'password' = '123456'
);
- mysql sink
-- 创建mysql sink表。需要增加主键约束,flink会通过主键更新数据
CREATE TABLE clazz_num_mysql (
clazz STRING,
num BIGINT,
PRIMARY KEY (clazz) NOT ENFORCED
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://master:3306/bigdata29?useUnicode=true&characterEncoding=UTF-8',
'table-name' = 'clazz_num_mysql', -- 需要手动创建
'username' ='root',
'password' = '123456'
);
insert into clazz_num_mysql
select
clazz,
count(1) as num
from
students_kafka
group by
clazz;
3、HDFS
- hdfs source
-- 创建hdfs source表 -- 有界流
CREATE TABLE students_hdfs (
id int,
name STRING,
age INT,
gender STRING,
clazz STRING
) WITH (
'connector' = 'filesystem', -- 必选:指定连接器类型
'path' = 'hdfs://master:9000/bigdata29/data/students.csv', -- 必选:指定路径
'format' = 'csv' -- 必选:文件系统连接器指定 format
);
CREATE TABLE clazz_num_batch (
clazz STRING,
num BIGINT
) WITH (
'connector' = 'filesystem', -- 必选:指定连接器类型
'path' = 'hdfs://master:9000/data/clazz_num_batch', -- 必选:指定路径
'format' = 'csv' -- 必选:文件系统连接器指定 format
);
-- 查询数据
insert into clazz_num_batch
select
clazz,
count(1) as num
from
students_hdfs
group by clazz;
-- 创建hdfs source表 -- 无界流
CREATE TABLE students_hdfs_stream (
id int,
name STRING,
age INT,
gender STRING,
clazz STRING
) WITH (
'connector' = 'filesystem', -- 必选:指定连接器类型
'path' = 'hdfs://master:9000/data/students', -- 必选:指定路径
'format' = 'csv', -- 必选:文件系统连接器指定 format
'source.monitor-interval' = '5000' -- 指定扫描目录的间隔时间
);
- hdfs sink
-- 将仅追加的结果流写入hdfs
CREATE TABLE students_hdfs_sink (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'filesystem', -- 必选:指定连接器类型
'path' = 'hdfs://master:9000/data/students_sink', -- 必选:指定路径
'format' = 'csv' -- 必选:文件系统连接器指定 format
);
insert into students_hdfs_sink
select id,name,age,sex,clazz from students_kafka;
-- 2、将更新更改的结果写入hdfs
CREATE TABLE clazz_num_hdfs (
clazz STRING,
num BIGINT
) WITH (
'connector' = 'filesystem', -- 必选:指定连接器类型
'path' = 'hdfs://master:9000/data/clazz_num', -- 必选:指定路径
'format' = 'canal-json' -- 必选:文件系统连接器指定 format
);
insert into clazz_num_hdfs
select
clazz,
count(1) as num
from
students_kafka
group by clazz;
4、hbase
- 整合
# 将依赖包上传到flink的lib目录下
flink-sql-connector-hbase-2.2-1.15.2.jar
# 依赖更新后需要重启集群才会生效
yarn application -list
yarn application -kill [appid]
yarn-session.sh -d
sql-client.sh
-- 1、在hbase中创建表
create 'students_flink','info'
-- 创建hbase sink表
CREATE TABLE students_hbase (
id STRING,
info ROW<name STRING,age INT,sex STRING,clazz STRING>, -- 指定列簇中的咧
PRIMARY KEY (id) NOT ENFORCED -- 设置hbaserowkey
) WITH (
'connector' = 'hbase-2.2',
'table-name' = 'students_flink',
'zookeeper.quorum' = 'master:2181,node1:2181,node2:2181'
);
insert into students_hbase
select
id,
ROW(name,age,sex,clazz) as info
from students_kafka;
-- 查看结果
select * from students_hbase;
scan 'students_flink'
5、datagen
用于生成测试数据,可以用于高性能测试
-- 出啊关键datagen source表
CREATE TABLE students_datagen (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'datagen',
'rows-per-second'='5', -- 指定每秒生成的数据量
'fields.id.length'='5',
'fields.name.length'='3',
'fields.age.min'='1',
'fields.age.max'='100',
'fields.sex.length'='1',
'fields.clazz.length'='4'
);
6、print
在task manager中打印结果
CREATE TABLE print_table (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'print'
);
CREATE TABLE print_table
WITH ('connector' = 'print')
-- 应用目标表的字段创建新的
LIKE students_datagen (EXCLUDING ALL);
insert into print_table
select * from students_datagen;
7、BlackHole
用于高性能测试
CREATE TABLE blackhole_table (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'blackhole'
);
insert into blackhole_table
select * from students_datagen;
5、数据格式
1、csv
数据中字段的顺序需要和建表语句字段的顺序保持一致 (顺序映射)
默认按照逗号分割
CREATE TABLE students_csv (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'kafka',
'topic' = 'students', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'csv', -- 指定数据的格式
'csv.field-delimiter' = ',' ,-- 指定分隔符
'csv.ignore-parse-errors' ='true' -- 跳过脏数据
);
2、json
flink表中的字段和类型需要和json中保持一致(同名映射)
CREATE TABLE cars (
car STRING,
city_code STRING,
county_code STRING,
card BIGINT,
camera_id STRING,
orientation STRING,
road_id BIGINT,
`time` BIGINT,
speed DOUBLE
) WITH (
'connector' = 'kafka',
'topic' = 'cars', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'json', -- 指定数据的格式
'json.ignore-parse-errors' ='true'
);
3、canal-json
用于保存更新更改的结果流
CREATE TABLE clazz_num (
clazz STRING,
num BIGINT
) WITH (
'connector' = 'kafka',
'topic' = 'clazz_num',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'canal-json'
);
insert into clazz_num
select
clazz,
count(1) as num
from
students_kafka
group by
clazz;
6、时间属性
1、处理时间
-- PROCTIME() 生成处理时间的函数
CREATE TABLE words (
word STRING,
proctime AS PROCTIME() -- 声明一个额外的列作为处理时间属性
) WITH (
'connector' = 'kafka',
'topic' = 'words',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
-- 实时统计每个单词最近5秒单词的数量
select
word,
TUMBLE_START(proctime,INTERVAL '5' SECOND) win_start,
TUMBLE_END(proctime,INTERVAL '5' SECOND) win_end,
count(1) as num
from
words
group by
word,
TUMBLE(proctime,INTERVAL '5' SECOND);
2、事件时间
java,2024-06-04 09:21:10
java,2024-06-04 09:21:11
java,2024-06-04 09:21:12
java,2024-06-04 09:21:13
java,2024-06-04 09:21:16
java,2024-06-04 09:21:20
CREATE TABLE words_event_time (
word STRING,
`event_time` TIMESTAMP(3), -- 时间字段
-- 指定时间字段和水位线生成策略
WATERMARK FOR event_time AS event_time - INTERVAL '5' SECOND
) WITH (
'connector' = 'kafka',
'topic' = 'words_event_time',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
select
word,
TUMBLE_START(event_time,INTERVAL '5' SECOND) win_start,
TUMBLE_END(event_time,INTERVAL '5' SECOND) win_end,
count(1) as num
from
words_event_time
group by
word,
TUMBLE(event_time,INTERVAL '5' SECOND);
7、SQL语法
1、Hints
动态表选择:可以在查询表的时候动态修改表的参数配置
CREATE TABLE students (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'kafka',
'topic' = 'students', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'csv' -- 指定数据的格式
);
select * from students /*+ OPTIONS('csv.ignore-parse-errors' ='true') */;
-- latest-offset: 读取任务启动之后生产的数据
select * from students /*+ OPTIONS('csv.ignore-parse-errors' ='true','scan.startup.mode' = 'latest-offset') */;
CREATE TABLE students_hdfs_stream (
id int,
name STRING,
age INT,
gender STRING,
clazz STRING
) WITH (
'connector' = 'filesystem', -- 必选:指定连接器类型
'path' = 'hdfs://master:9000/data/students', -- 必选:指定路径
'format' = 'csv' -- 必选:文件系统连接器指定 format
);
select * from students_hdfs_stream /*+OPTIONS('source.monitor-interval' = '5000') */
2、WITH
当有一段sql逻辑重复时,可以定义在with语句中,减少代码量
with tmp as (
select
id,name,age,clazz
from
students_hdfs_stream
where age > 22
)
select * from tmp
union all
select * from tmp;
3、SELECT WHERE
select * from students_hdfs_stream
where
age > 21
and gender in ('男','女');
4、SELECT DISTINCT
对于流处理的问题
1、flink会将之前的数据保存在状态中,用于判断是否重复
2、如果表的数据量很大,随着时间的推移状态会越来越大,状态的数据时先保存在TM的内存中的,时间长了可能会出问题
select distinct * from students /*+ OPTIONS('csv.ignore-parse-errors' ='true','scan.startup.mode' = 'latest-offset') */;
5、窗口函数(TVFs)
1、滚动窗口函数
CREATE TABLE bid (
item STRING,
price DECIMAL(10, 2),
bidtime TIMESTAMP(3),
WATERMARK FOR bidtime AS bidtime - INTERVAL '5' SECOND
) WITH (
'connector' = 'kafka',
'topic' = 'bid',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic bid
C,4.00,2020-04-15 08:05:01
A,2.00,2020-04-15 08:07:01
D,5.00,2020-04-15 08:09:01
B,3.00,2020-04-15 08:11:01
E,1.00,2020-04-15 08:13:01
F,6.00,2020-04-15 08:17:01
-- TUMBLE:滚动窗口函数,在原表的基础上增加窗口开始时间,窗口结束时间,窗口时间
SELECT item,price,bidtime,window_start,window_end,window_time FROM TABLE(
TUMBLE(TABLE bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES)
);
-- 窗口聚合计算
-- 实时统计最近10分钟所有商品的平均价格
SELECT
window_start,
window_end,
avg(price) as avg_price
FROM
TABLE(
TUMBLE(TABLE bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES)
)
group by
window_start,
window_end;
2、滑动窗口函数
CREATE TABLE bid_proctime (
item STRING,
price DECIMAL(10, 2),
proctime AS PROCTIME()
) WITH (
'connector' = 'kafka',
'topic' = 'bid_proctime',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic bid_proctime
C,4.00
A,2.00
D,5.00
B,3.00
E,1.00
F,6.00
-- HOP: 滑动窗口函数
SELECT item,price,proctime,window_start,window_end,window_time FROM TABLE(
HOP(TABLE bid_proctime, DESCRIPTOR(proctime), INTERVAL '5' SECOND, INTERVAL '10' SECOND)
);
-- 窗口聚合
SELECT
window_start,
window_end,
avg(price) as avg_price
FROM
TABLE(
HOP(TABLE bid_proctime, DESCRIPTOR(proctime), INTERVAL '5' SECOND, INTERVAL '10' SECOND)
)
group by
window_start,
window_end;
3、CUMULATE
CREATE TABLE bid (
item STRING,
price DECIMAL(10, 2),
bidtime TIMESTAMP(3),
WATERMARK FOR bidtime AS bidtime - INTERVAL '5' SECOND
) WITH (
'connector' = 'kafka',
'topic' = 'bid',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic bid
C,4.00,2020-04-15 08:05:01
A,2.00,2020-04-15 08:07:01
D,5.00,2020-04-15 08:09:01
B,3.00,2020-04-15 08:11:01
E,1.00,2020-04-15 08:13:01
F,6.00,2020-04-15 08:17:01
SELECT * FROM TABLE(
CUMULATE(TABLE bid, DESCRIPTOR(bidtime), INTERVAL '2' MINUTES, INTERVAL '10' MINUTES)
);
4、会话窗口
CREATE TABLE bid_proctime (
item STRING,
price DECIMAL(10, 2),
proctime AS PROCTIME()
) WITH (
'connector' = 'kafka',
'topic' = 'bid_proctime',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic bid_proctime
C,4.00
C,2.00
C,5.00
C,3.00
C,1.00
C,6.00
-- 实时统计每个商品的总的金额,隔5秒没有数据开始统计
select
item,
SESSION_START(proctime,INTERVAL '5' SECOND) as session_start,
SESSION_END(proctime,INTERVAL '5' SECOND) as session_end,
sum(price) as sum_price
from
bid_proctime
group by
item,
SESSION(proctime,INTERVAL '5' SECOND);
6、GROUP BY
-- 出啊关键datagen source表
CREATE TABLE words_datagen (
word STRING
) WITH (
'connector' = 'datagen',
'rows-per-second'='50000', -- 指定每秒生成的数据量
'fields.word.length'='5'
);
CREATE TABLE blackhole_table (
word STRING,
num BIGINT
) WITH (
'connector' = 'blackhole'
);
-- 分组聚合需要将之前的计算结果保存在状态中,
-- 如果状态无限增长,会导致checkpoint时间拉长,如果checkpoint超时失败了,也会导致任务失败
insert into blackhole_table
select
word,
count(1)as num
from
words_datagen /*+ OPTIONS('fields.word.length'='7') */
group by
word;
7、OVER
1、sum max min avg count
CREATE TABLE `order` (
order_id STRING,
amount DECIMAL(10, 2),
product STRING,
order_time TIMESTAMP(3),
WATERMARK FOR order_time AS order_time - INTERVAL '5' SECOND
) WITH (
'connector' = 'kafka',
'topic' = 'order',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic order
1,4.00,001,2020-04-15 08:05:01
2,2.00,001,2020-04-15 08:07:01
3,5.00,001,2020-04-15 08:09:01
4,3.00,001,2020-04-15 08:11:01
5,1.00,001,2020-04-15 08:13:01
6,6.00,001,2020-04-15 08:17:01
6,6.00,001,2020-04-15 08:20:01
6,6.00,001,2020-04-15 08:21:01
6,10.00,001,2020-04-15 08:21:02
6,11.00,001,2020-04-15 08:21:03
6,12.00,001,2020-04-15 08:21:04
-- 1、实时统计每个商品的累计总金额,将总金额放在每一条数据的后面
-- 流处理的问题
-- a、sum over必须按照时间升序排序,因为数据时一条一套过来的,只能做累加求和,不能做全局求和
-- b、只能按照时间升序排序,如果按照其他的字段排序,每来一条数据都需要重新排序,计算代价太大,影响性能
select
order_id,
amount,
product,
order_time,
sum(amount) over(
partition by product
order by order_time
)
from
`order`
;
-- 2、实时统计每个商品的累计总金额,将总金额放在每一条数据的后面,只统计最近10分钟的数据
select
order_id,
amount,
product,
order_time,
sum(amount) over(
partition by product
order by order_time
-- 统计10分钟前到当前行的数据
RANGE BETWEEN INTERVAL '10' MINUTES PRECEDING AND CURRENT ROW
)
from
`order`
;
-- 2、实时统计每个商品的累计总金额,将总金额放在每一条数据的后面,计算最近5条数据
select
order_id,
amount,
product,
order_time,
sum(amount) over(
partition by product
order by order_time
-- 从前4条数据到当前行
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
)
from
`order`
;
-- 2、实时统计每个商品的最大金额,将总金额放在每一条数据的后面,计算最近5条数据
select
order_id,
amount,
product,
order_time,
max(amount) over(
partition by product
order by order_time
-- 从前4条数据到当前行
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
)
from
`order`
;
2、row_number
-- 如果只是增加排名,只能按照时间字段升序排序
select
order_id,
amount,
product,
order_time,
row_number() over(partition by product order by order_time) as r
from
`order`
;
-- 实时统计每个商品金额最高的前两个商品 -- TOPN
-- 去完topn之后需要计算的排名的数据较少了,计算代价降低了
select *
from (
select
order_id,
amount,
product,
order_time,
row_number() over(partition by product order by amount desc) as r
from
`order`
)
where r <= 2
8、ORDER BY
-- 子流处理模式中,order by 需要按照时间字段升序排序
select * from
`order`
order by
order_time,amount
-- 加上limit ,计算代价就不搞了,就可以按照普通字段进行排序了
select * from
`order`
order by
amount
limit 2;
9、模式检测(CEP)
1、案例1
我们先实现第一版报警程序,对于一个账户,如果出现小于 $1 美元的交易后紧跟着一个大于 $500 的交易,就输出一个报警信息。
CREATE TABLE tran (
id STRING,
amount DECIMAL(10, 2),
proctime as PROCTIME()
) WITH (
'connector' = 'kafka',
'topic' = 'tran',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'latest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic tran
1,4.00
1,2.00
1,5.00
1,0.90
1,600.00
1,4.00
1,2.00
1,0.10
1,200.00
1,700.00
-- MATCH_RECOGNIZE(模式检测)
-- 在数据流上对数据进行匹配,当数满足我们定义的规则后,返回匹配的结果
-- 我们先实现第一版报警程序,对于一个账户,如果出现小于 $1 美元的交易后紧跟着一个大于 $500 的交易,就输出一个报警信息。
SELECT *
FROM tran
MATCH_RECOGNIZE (
PARTITION BY id -- 分组字段
ORDER BY proctime -- 排序字段,只能按照时间字段升序排序
MEASURES -- 相当于select
A.amount as min_amount,
A.proctime as min_proctime,
B.amount as max_amount,
B.proctime as max_proctime
PATTERN (A B) -- 定义规则
DEFINE -- 定义条件
A as amount < 1,
B as amount > 500
) AS T
-- 我们先实现第一版报警程序,对于一个账户,如果出现小于 $1 美元的交易后紧跟着一个大于 $500 的交易,就输出一个报警信,两次事件需要在10秒内出现
SELECT *
FROM tran
MATCH_RECOGNIZE (
PARTITION BY id -- 分组字段
ORDER BY proctime -- 排序字段,只能按照时间字段升序排序
MEASURES -- 相当于select
A.amount as min_amount,
A.proctime as min_proctime,
B.amount as max_amount,
B.proctime as max_proctime
PATTERN (A B) WITHIN INTERVAL '5' SECOND -- 定义规则,增加事件约束,需要在5秒内匹配出结果
DEFINE -- 定义条件
A as amount < 1,
B as amount > 500
) AS T
-- 我们先实现第一版报警程序,对于一个账户,如果连续出现三次出现小于 $1 美元的交易后紧跟着一个大于 $500 的交易,就输出一个报警信息
SELECT *
FROM tran
MATCH_RECOGNIZE (
PARTITION BY id -- 分组字段
ORDER BY proctime -- 排序字段,只能按照时间字段升序排序
MEASURES -- 相当于select
A.amount as a_amount, -- 获取最后一条
min(A.amount) as min_a_amount, -- 取最小的
max(A.amount) as max_a_amount, -- 取最大的
sum(A.amount) as sum_a_amount, -- 求和
avg(A.amount) as avg_a_amount, -- 平均
FIRST(A.amount) AS first_a_amount, -- 取前面第一条
LAST(A.amount) AS LAST_a_amount, -- 取后面第一条
B.amount as b_amount
PATTERN (A{3} B) -- 定义规则
DEFINE -- 定义条件
A as amount < 1,
B as amount > 500
) AS T;
1,0.90
1,0.10
1,0.20
1,600.00
2、案例2
找出一个单一股票价格不断下降的时期
CREATE TABLE ticker (
symbol STRING,
rowtime TIMESTAMP(3), -- 时间字段
price DECIMAL(10, 2) ,
tax DECIMAL(10, 2),
-- 指定时间字段和水位线生成策略
WATERMARK FOR rowtime AS rowtime
) WITH (
'connector' = 'kafka',
'topic' = 'ticker',
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'latest-offset',
'format' = 'csv',
'csv.ignore-parse-errors' ='true' -- 当有脏数据时是否跳过当前行
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic ticker
ACME,2024-06-04 10:00:00,12,1
ACME,2024-06-04 10:00:01,17,2
ACME,2024-06-04 10:00:02,19,1
ACME,2024-06-04 10:00:03,21,3
ACME,2024-06-04 10:00:04,25,2
ACME,2024-06-04 10:00:05,18,1
ACME,2024-06-04 10:00:06,15,1
ACME,2024-06-04 10:00:07,14,2
ACME,2024-06-04 10:00:08,24,2
ACME,2024-06-04 10:00:09,25,2
ACME,2024-06-04 10:00:10,19,1
-- 找出一个单一股票价格不断下降的时期
select * from
ticker
MATCH_RECOGNIZE (
PARTITION BY symbol -- 分组字段
ORDER BY rowtime -- 排序字段,只能按照时间字段升序排序
MEASURES -- 相当于select
A.price as a_price,
FIRST(B.price) as FIRST_b_price,
LAST(B.price) as last_b_price,
C.price as c_price
AFTER MATCH SKIP PAST LAST ROW -- 从当前匹配成功止呕的下一行开始匹配
PATTERN (A B+ C) -- 定义规则
DEFINE -- 定义条件
-- 如果时第一个B,就和A比较,如果时后面的B,就和前一个B比较
B as (LAST(B.price,1)is null and B.price < A.price) or B.price < LAST(B.price,1),
C as C.price > LAST(B.price)
) AS T;
10、JOINs
1、Regular Joins
和hive sql中的join是一样的
CREATE TABLE students (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'kafka',
'topic' = 'students', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'latest-offset', -- 指定读取数据的位置
'format' = 'csv' -- 指定数据的格式
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic students
1500100001,施笑槐,22,女,文科六班
1500100002,吕金鹏,24,男,文科六班
1500100003,单乐蕊,22,女,理科六班
1500100004,葛德曜,24,男,理科三班
1500100005,宣谷芹,22,女,理科五班
1500100006,边昂雄,21,男,理科二班
1500100007,尚孤风,23,女,文科六班
CREATE TABLE scores (
sid STRING,
cid STRING,
score INT
) WITH (
'connector' = 'kafka',
'topic' = 'scores', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'latest-offset', -- 指定读取数据的位置
'format' = 'csv' -- 指定数据的格式
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic scores
1500100001,1000001,98
1500100001,1000002,5
1500100001,1000003,137
1500100001,1000004,29
1500100001,1000005,85
1500100001,1000006,52
1500100002,1000001,139
1500100002,1000002,102
-- inner jion
select
a.id,a.name,b.sid,b.score
from
students as a
inner join
scores as b
on a.id=b.sid;
-- left join
select
a.id,a.name,b.sid,b.score
from
students as a
left join
scores as b
on a.id=b.sid;
-- full join
select
a.id,a.name,b.sid,b.score
from
students as a
full join
scores as b
on a.id=b.sid;
-- 常规的关联方式,会将两个表的数据一直保存在状态中,时间长了,状态会越来越大,导致任务执行失败
-- 状态有效期,状态在flink中保存的事件,状态保留多久需要根据实际业务分析
SET 'table.exec.state.ttl' = '10000';
2、Interval Joins
在一段时间内关联
CREATE TABLE students_proctime (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING,
proctime AS PROCTIME()
) WITH (
'connector' = 'kafka',
'topic' = 'students', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'latest-offset', -- 指定读取数据的位置
'format' = 'csv' -- 指定数据的格式
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic students
1500100001,施笑槐,22,女,文科六班
1500100002,吕金鹏,24,男,文科六班
1500100003,单乐蕊,22,女,理科六班
1500100004,葛德曜,24,男,理科三班
1500100005,宣谷芹,22,女,理科五班
1500100006,边昂雄,21,男,理科二班
1500100007,尚孤风,23,女,文科六班
CREATE TABLE scores_proctime (
sid STRING,
cid STRING,
score INT,
proctime AS PROCTIME()
) WITH (
'connector' = 'kafka',
'topic' = 'scores', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'latest-offset', -- 指定读取数据的位置
'format' = 'csv' -- 指定数据的格式
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic scores
1500100001,1000001,98
1500100001,1000002,5
1500100001,1000003,137
1500100001,1000004,29
1500100001,1000005,85
1500100001,1000006,52
1500100002,1000001,139
1500100002,1000002,102
select a.id,a.name,b.sid,b.score from
students_proctime a, scores_proctime b
where a.id=b.sid
-- a表的时间需要在b表时间10秒内
and (
a.proctime BETWEEN b.proctime - INTERVAL '10' SECOND AND b.proctime
or b.proctime BETWEEN a.proctime - INTERVAL '10' SECOND AND a.proctime
);
3、Temporal Joins
CREATE TABLE orders (
order_id STRING,
price DECIMAL(32,2),
currency STRING,
order_time TIMESTAMP(3),
WATERMARK FOR order_time AS order_time
) WITH (
'connector' = 'kafka',
'topic' = 'orders', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'latest-offset', -- 指定读取数据的位置
'format' = 'csv' -- 指定数据的格式
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic orders
o_001,1,EUR,2024-06-06 12:00:00
o_002,100,EUR,2024-06-06 12:00:07
o_003,200,EUR,2024-06-06 12:00:16
o_004,10,EUR,2024-06-06 12:00:21
o_005,20,EUR,2024-06-06 12:00:25
-- 汇率表
CREATE TABLE currency_rates (
currency STRING,
conversion_rate DECIMAL(32, 2),
update_time TIMESTAMP(3),
WATERMARK FOR update_time AS update_time,
PRIMARY KEY(currency) NOT ENFORCED -- 主键,区分不同的汇率
) WITH (
'connector' = 'kafka',
'topic' = 'currency_rates1', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'canal-json' -- 指定数据的格式
);
insert into currency_rates
values
('EUR',0.12,TIMESTAMP'2024-06-06 12:00:00'),
('EUR',0.11,TIMESTAMP'2024-06-06 12:00:09'),
('EUR',0.15,TIMESTAMP'2024-06-06 12:00:17'),
('EUR',0.14,TIMESTAMP'2024-06-06 12:00:23');
kafka-console-consumer.sh --bootstrap-server master:9092,node1:9092,node2:9092 --from-beginning --topic currency_rates
-- 使用常规关联方式关联时态表只能关联到最新的数据
select
a.price,a.order_time,b.conversion_rate,b.update_time
from
orders as a
join
currency_rates as b
on a.currency=b.currency;
-- 时态表join
-- FOR SYSTEM_TIME AS OF a.order_time: 使用a表的时间到b表中查询对应时间段的数据
select
a.price,a.order_time,b.conversion_rate,b.update_time
from
orders as a
join
currency_rates FOR SYSTEM_TIME AS OF a.order_time as b
on a.currency=b.currency;
4、lookup join
用于流表关联维度表
流表:动态表
维度表:不怎么变化的变,维度表的数据一般可以放在hdfs或者mysql
CREATE TABLE scores (
sid INT,
cid STRING,
score INT,
proctime AS PROCTIME()
) WITH (
'connector' = 'kafka',
'topic' = 'scores', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'latest-offset', -- 指定读取数据的位置
'format' = 'csv' -- 指定数据的格式
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic scores
1500100001,1000001,98
1500100002,1000002,5
1500100001,1000003,137
CREATE TABLE students (
id INT,
name STRING,
age INT,
gender STRING,
clazz STRING
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://master:3306/bigdata29',
'table-name' = 'student',
'username' ='root',
'password' = '123456',
'lookup.cache.max-rows' = '1000', -- 最大缓存行数
'lookup.cache.ttl' ='10000' -- 缓存过期时间
);
--1、使用常规关联方式
-- 维表的数据只在任务启动的时候读取一次,后面不再实时读取,
-- 只能关联到任务启动时读取的数据
select a.sid,a.score,b.id,b.name from
scores as a
left join
students as b
on a.sid=b.id;
-- lookup join
-- 当流表每来一条数据时,使用关联字段到维表的数据源中查询
-- 每一次都需要查询数据库,性能会降低
select a.sid,a.score,b.id,b.name from
scores as a
left join
students FOR SYSTEM_TIME AS OF a.proctime as b
on a.sid=b.id;
8、整合Hive
1、整合
# 上传依赖到flink的lib目录下
flink-sql-connector-hive-3.1.2_2.12-1.15.2.jar
# 重启flink集群
yarn application -list
yarn application -kill XXX
yarn-session.sh -d
sql-client.sh
2、hive catalog
catalog—>database—>table---->字段---->数据
catalog是数据库上面的一个概念,一个cataloglog中可以i有多个database,
catalog就是flink抽象的元数据层default_catalog:是flink默认的元数据,将元数据保存在jobmanager的内存中
-- 1、启动hive的元数据服务
nohup hive --service metastore &
-- 2、创建hive catalog
CREATE CATALOG hive_catalog WITH (
'type' = 'hive',
'hive-conf-dir' = '/usr/local/soft/hive-3.1.2/conf'
);
show catalogs;
--3、切换catalog
use catalog hive_catalog;
-- 查询hive中的表
select * from hive_catalog.bigdata29.students;
-- 创建数据库
create database flink;
-- flink可以查询hive的表,hive不能查询flink创建的动态表
-- 在hive cagalog 中保存flink的动态表
CREATE TABLE students_csv (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'kafka',
'topic' = 'students', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'csv', -- 指定数据的格式
'csv.field-delimiter' = ',' ,-- 指定分隔符
'csv.ignore-parse-errors' ='true' -- 跳过脏数据
);
2、hive function
-- 加载hive函数
LOAD MODULE hive WITH ('hive-version' = '3.1.2');
select split('java,flink',',');
CREATE TABLE lines (
line STRING
) WITH (
'connector' = 'kafka',
'topic' = 'lines', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'csv', -- 指定数据的格式
'csv.field-delimiter' = '|' ,-- 指定分隔符
'csv.ignore-parse-errors' ='true' -- 跳过脏数据
);
kafka-console-producer.sh --broker-list master:9092,node1:9092,node2:9092 --topic lines
java,java,flink
select
word,count(1) as num
from
lines,
lateral table(explode(split(line,','))) t(word)
group by
word;
9、Checkpoint
1、编写sql文件
vim word_count.sql
-- 1、创建source表
CREATE TABLE lines (
line STRING
) WITH (
'connector' = 'kafka',
'topic' = 'lines', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'csv', -- 指定数据的格式
'csv.field-delimiter' = '|' ,-- 指定分隔符
'csv.ignore-parse-errors' ='true' -- 跳过脏数据
);
-- 创建sink表
CREATE TABLE print_table (
word STRING,
num BIGINT
) WITH (
'connector' = 'print'
);
-- 加载hive函数
LOAD MODULE hive WITH ('hive-version' = '3.1.2');
-- 执行sql
insert into print_table
select
word,count(1) as num
from
lines,
lateral table(explode(split(line,','))) t(word)
group by
word;
2、执行sql文件
-- 第一次直接提交任务
sql-client.sh -f word_count.sql
3、失败重启
-- 基于hdfs中保存的快照重启任务
-- 在inert into 语句的前面增加
SET 'execution.savepoint.path' = 'hdfs://master:9000/flink/checkpoint/d915e6278f156a9278156e67105f914e/chk-36';
-- 重启任务
sql-client.sh -f word_count.sql
10、当一个表被多次使用时
vim student.sql
CREATE TABLE students_csv (
id STRING,
name STRING,
age INT,
sex STRING,
clazz STRING
) WITH (
'connector' = 'kafka',
'topic' = 'students', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'csv', -- 指定数据的格式
'csv.field-delimiter' = ',' ,-- 指定分隔符
'csv.ignore-parse-errors' ='true' -- 跳过脏数据
);
-- 创建sink表
CREATE TABLE clazz_num (
clazz STRING,
num BIGINT
) WITH (
'connector' = 'print'
);
CREATE TABLE sex_num (
sex STRING,
num BIGINT
) WITH (
'connector' = 'print'
);
-- 执行一组sql,如果多个sql中使用了同一张表,flink只会读取一次
EXECUTE STATEMENT SET
BEGIN
insert into clazz_num
select
clazz,
count(1) as num
from
students_csv
group by
clazz;
insert into sex_num
select
sex,
count(1) as num
from
students_csv
group by
sex;
END;
11、反压
1、测试反压
-- 出啊关键datagen source表
CREATE TABLE words_datagen (
word STRING
) WITH (
'connector' = 'datagen',
'rows-per-second'='50000', -- 指定每秒生成的数据量
'fields.word.length'='5'
);
CREATE TABLE blackhole_table (
word STRING,
num BIGINT
) WITH (
'connector' = 'blackhole'
);
-- 反压发生情况
--1、单词太多,状态太大导致反压
insert into blackhole_table
select
word,
count(1)as num
from
words_datagen /*+ OPTIONS('fields.word.length'='6') */
group by
word;
--2、数据量太大导致反压
insert into blackhole_table
select
word,
count(1)as num
from
words_datagen /*+ OPTIONS('fields.word.length'='5','rows-per-second'='400000') */
group by
word;
2、解决反压
1、增加资源
-- 1、增加Taskmanager的内存
-- 启动汲取设置tm的内存
yarn-session.sh -tm 6G -d
-- 2、增加并行度
SET 'parallelism.default' = '8';
2、预聚合
-- 开启微批处理
set 'table.exec.mini-batch.enabled' ='true';
set 'table.exec.mini-batch.allow-latency' = '5 s';
set 'table.exec.mini-batch.size' ='100000';
-- 开启预聚合
set 'table.optimizer.agg-phase-strategy' ='TWO_PHASE';
案例
1、实时统计道路拥堵情况
每隔1分钟计算最近15分钟每个道路的车流量和平均车速(滑动事件时间窗口)
CREATE TABLE cars (
car STRING,
city_code STRING,
county_code STRING,
card BIGINT,
camera_id STRING,
orientation STRING,
road_id BIGINT,
`time` BIGINT,
speed DOUBLE,
event_time as TO_TIMESTAMP(FROM_UNIXTIME(`time`)),-- 生成新的字段
-- 指定事件时间和水位线
WATERMARK FOR event_time AS event_time - INTERVAL '5' SECOND
) WITH (
'connector' = 'kafka',
'topic' = 'cars', -- 指定topic
'properties.bootstrap.servers' = 'master:9092,node1:9092,node2:9092', -- 指定kafka集群列表
'properties.group.id' = 'testGroup', -- 指定消费者组
'scan.startup.mode' = 'earliest-offset', -- 指定读取数据的位置
'format' = 'json', -- 指定数据的格式
'json.ignore-parse-errors' ='true'
);
--每隔1分钟计算最近15分钟每个道路的车流量和平均车速(滑动事件时间窗口)
select
road_id,
HOP_start(event_time,INTERVAL '1' MINUTES, INTERVAL '15' MINUTES) as win_start,
HOP_end(event_time,INTERVAL '1' MINUTES, INTERVAL '15' MINUTES) as win_end,
count(distinct car) as flow,
avg(speed) as avg_speed
from
cars
group by
road_id,
HOP(event_time,INTERVAL '1' MINUTES, INTERVAL '15' MINUTES);