目录
官方文档链接:TUMBLE
报错场景
最近在试用Flink的SQL Client,看到窗口函数部分,测试了下官方的案例,却报了这个错:
Flink SQL> SELECT * FROM TABLE(
> TUMBLE(
> DATA => TABLE Bid,
> TIMECOL => DESCRIPTOR(bidtime),
> SIZE => INTERVAL '10' MINUTES));
[ERROR] Could not execute SQL statement. Reason:
java.lang.UnsupportedOperationException: Currently Flink doesn't support individual window table-valued function TUMBLE(time_col=[bidtime], size=[10 min]).
Please use window table-valued function with aggregate together using window_start and window_end as group keys.
错误原因
这只是Flink官方用来展示tumple函数语法的,并不是完整的示例程序,注释有写的,看文档需要仔细一点:
解决方案
既然知道错误原因了,找正确的示例程序执行不就好了
SELECT window_start, window_end, SUM(price) as sum_price
FROM TABLE(
TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES))
GROUP BY window_start, window_end;
执行结果:
完整示例程序
1. 准备上游数据源
上游数据源选择mysql
创建表,并插入数据
-- 建库
create database if not exists broker;
-- 建表
CREATE TABLE `bid` (
`bidtime` timestamp NULL DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`item` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 插入数据
INSERT INTO broker.bid (bidtime, price, item)
VALUES ('2020-04-15 08:05:00', 4.00, 'C')
, ('2020-04-15 08:07:00', 2.00, 'A')
, ('2020-04-15 08:09:00', 5.00, 'D')
, ('2020-04-15 08:11:00', 3.00, 'B')
, ('2020-04-15 08:13:00', 1.00, 'E')
, ('2020-04-15 08:17:00', 6.00, 'F');
2. Flink SQL创建源表
create table Bid (
bidtime timestamp (3),
price decimal (10, 2),
item string,
watermark for bidtime as bidtime - interval '1' second
)
with (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://localhost:3306/broker',
'username' = 'root',
'password' = 'Root@123',
'table-name' = 'bid'
);
3. 使用tumble函数
SELECT window_start, window_end, SUM (price) as sum_price
FROM
TABLE (
TUMBLE (TABLE Bid, DESCRIPTOR (bidtime), INTERVAL '10' MINUTES))
GROUP BY window_start, window_end;