Flink SQL查询

最新推荐文章于 2024-06-05 08:30:00 发布

原创最新推荐文章于 2024-06-05 08:30:00 发布 · 707 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#flink #sql #数据库

Flink 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何使用FlinkSQL进行数据生成、表结构定义，包括时间戳处理、TopN查询、流与流之间的Join操作，以及维表联结和状态管理的最佳实践。

分享最近使用的Flink SQL的写法

前置内容

本文的写法是直接调用 bin/sql-client.sh 执行

设置显示模式

SET sql-client.execution.result-mode=tableau;

创建生成数据的表

-- 创建一张表添加 处理时间 事件时间和watermark
-- 通过datagen 来创建
CREATE TABLE ws (
  id INT,
  vc INT,
  pt AS PROCTIME(), -- 处理时间
  et AS cast(CURRENT_TIMESTAMP as timestamp(3)), -- 事件时间
  -- et AS CURRENT_TIMESTAMP, --事件时间
  WATERMARK FOR et AS et - INTERVAL '5' SECOND   -- watermark
) WITH (
  'connector' = 'datagen',
  'rows-per-second' = '1',
  'fields.id.min' = '1',
  'fields.id.max' = '3',
  'fields.vc.min' = '1',
  'fields.vc.max' = '100'
);


-- 查看表的结构

Flink SQL> desc ws;
+------+-----------------------------+-------+-----+----------------------+----------------------------+
| name |                        type |  null | key |               extras |                  watermark |
+------+-----------------------------+-------+-----+----------------------+----------------------------+
|   id |                         INT |  TRUE |     |                      |                            |
|   vc |                         INT |  TRUE |     |                      |                            |
|   pt | TIMESTAMP_LTZ(3) *PROCTIME* | FALSE |     |        AS PROCTIME() |                            |
|   et |  TIMESTAMP_LTZ(3) *ROWTIME* | FALSE |     | AS CURRENT_TIMESTAMP | `et` - INTERVAL '5' SECOND |
+------+-----------------------------+-------+-----+----------------------+----------------------------+
4 rows in set

时间格式的总结

默认指定时间为: TIMESTAMP_LTZ(3)。
带LTZ是存储时区的，不带LTZ是不存储时区的时间格式。
下面图是详细的时间格式。

flink中的时间格式

Flink SQL 详细写法

TopN查询

-- ws表以id进行分区，vc排序的top3数据
select 
    id,
    et,
    vc,
    rownum
from 
(
    select 
        id,
        et,
        vc,
        row_number() over(
            partition by id 
            order by vc desc -- 可以是升序或者是降序
        ) as rownum
    from ws
)
where rownum<=3;  -- topN的关键  3:N

查询结果如下所示， rownum是top排名的字段。
op: + - 代表的是新增和删除 I: 代表插入 U: Update 更新
top3查询返回
如果我们只返回top1, 那么就相当于对每个分区去重后保留的结果。保留的结果是按照vc排序所获取的想要的最大值或最小值。

TopN实现去重的一些特殊要求

排序字段一定是时间属性列
可以降序[最新的数据] 也可以升序[最开始插入的数据]
Demo如下:

-- et是事件时间字段 需要按照这样的一个时间字段进行排序
select 
    id,
    et,
    vc,
    rownum
from 
(
    select 
        id,
        et,
        vc,
        row_number() over(
            partition by id
            order by et desc
        ) as rownum
    from ws
)
where rownum=1;

如下是按照id进行分区，获取的每个分区内最新数据的结果:
去重获取最新数据demo返回结果

流之间的join的sql实现

创建表

创建与ws进行关联的表 ws1

CREATE TABLE ws1 (
  id INT,
  vc INT,
  pt AS PROCTIME(), -- 处理时间
  et AS cast(CURRENT_TIMESTAMP as timestamp(3)), -- 事件时间
  WATERMARK FOR et AS et - INTERVAL '0.001' SECOND   -- watermark
) WITH (
  'connector' = 'datagen',
  'rows-per-second' = '1',
  'fields.id.min' = '3',
  'fields.id.max' = '5',
  'fields.vc.min' = '1',
  'fields.vc.max' = '100'
);

Inner Join

Inner Join（Inner Equal Join）：流任务中，只有两条流 Join 到才输出，输出 +[L, R]

SELECT *
FROM ws
INNER JOIN ws1
ON ws.id = ws1.id

InnerJoin返回

Left Join

Left Join（Outer Equal Join）分为三种输出情况：

左流与右流同时: +[L, R]
左流快于右流: +[L, null] -> -[L, null] -> +[L, R] 中间有撤回，是等到了右流的撤回
- 这是一个过程，左流快于右流是两边都会到，只是速度不同。当左边到的时候，因为是Left Join 所以左流需要先输出，右流部分空置null。同时左流结果会加入到State中；后面当右流到的时候，需要先删除之前操作的结果，即 -[L, null]，然后再输出左右流Join的结果+[L, R]
Right Join 方向和Left Join相反，逻辑相同。

Left Join Demo:

RIGHT JOIN Demo:
right_join

Full Join

Full Join 结合Left Join 和 Right Join ，谁先到谁输出，后到的匹配后撤回修改。

FULL OUTER JOIN Demo:
full_outer_join

流 Join 的特点

流的上游是无限的数据，所以要做到关联的话，Flink 会将两条流的所有数据都存储在 State 中，所以 Flink 任务的 State 会无限增大，因此你需要为 State 配置合适的 TTL，以防止 State 过大。
有等值关联和非等值关联的join条件
- 非等值关联消耗性能比较大。
- 非等值使用shuffle 策略是 Global，所有数据发往一个并发。
- 等值 shuffle 策略是 Hash， Join On的数据发往下游。

间隔连接(Interval Join):

理解: 内连接的基础上加上watermark 时间区间的要求。
时间区间的格式:

-- ltime 左表时间  rtime  右表时间 
1. ltime = rtime
2. ltime >= rtime AND ltime < rtime + INTERVAL '10' MINUTE
3. ltime BETWEEN rtime - INTERVAL '10' SECOND AND rtime + INTERVAL '5' SECOND

时间间隔需要时在同一种类型的条件下,否则会产生如下报错:
时间格式不统一
Demo:

-- 在时间条件内的数据才能进行join
SELECT *
FROM ws,ws1
WHERE ws.id = ws1. id
AND ws.et BETWEEN ws1.et - INTERVAL '2' SECOND AND ws1.et + INTERVAL '2' SECOND ;

返回结果:
interval Join

维表联结查询 Lookup Join

实时获取外部缓存的 Join, 外部缓存可以时Redis 、KaFKa、MySQL、HBase

SQL DEMO:

CREATE TABLE Customers (
  id INT,
  name STRING,
  country STRING,
  zip STRING
) WITH (
  'connector' = 'jdbc',
  'url' = 'jdbc:mysql://hadoop102:3306/customerdb',
  'table-name' = 'customers'
);

-- order表每来一条数据，都会去mysql的customers表查找维度数据

SELECT o.order_id, o.total, c.country, c.zip
FROM Orders AS o
JOIN Customers FOR SYSTEM_TIME AS OF o.proc_time AS c  -- 注意写法
ON o.customer_id = c.id;

其他sql参数支持

order by 支持
- 必须有时间字段且为升序
limit 支持

SQL Hits

sql 提示，为sql执行增加更多的配置的变化. 只对当前表生效.

select * from ws/*+ OPTIONS('rows-per-second'='10', 'fields.id.max'='7')*/;

集合操作

(SELECT id FROM ws) UNION (SELECT id FROM ws1); -- 集合合并并且去重
(SELECT id FROM ws) UNION ALL (SELECT id FROM ws1); -- 将集合合并，不做去重。
(SELECT id FROM ws) INTERSECT (SELECT id FROM ws1); -- 集合交集并且去重
(SELECT id FROM ws) INTERSECT ALL (SELECT id FROM ws1); -- 交集不做去重
(SELECT id FROM ws) EXCEPT (SELECT id FROM ws1); -- 差集并且去重
(SELECT id FROM ws) EXCEPT ALL (SELECT id FROM ws1); -- 差集不做去重  这是一个回撤流

子查询

子查询的结果集只能有一列
Demo:

SELECT id, vc
FROM ws
WHERE id IN (
SELECT id FROM ws1
)

注意: 可能有State变大的问题，注意TTL的设置。