实时计算flinksql使用sum(),avg(),max(),min(),over(),WATERMARK示例

最新推荐文章于 2024-03-17 13:38:05 发布

倾一生爱恋换一世纯真

最新推荐文章于 2024-03-17 13:38:05 发布

阅读量728

点赞数 7

文章标签：大数据数据仓库 flink sql

本文链接：https://blog.csdn.net/weixin_44236758/article/details/135852371

版权

文章详细描述了如何在Flink中创建catalog、database和table，包括从Kafka主题读取数据并将其写入到MySQL表中，以及使用窗口函数进行实时数据分析。展示了如何配置Kafka连接和MySQL连接参数，以及如何定义RowtimeWatermark以处理时间戳事件.

摘要由CSDN通过智能技术生成

-- 创建CATALOG
CREATE CATALOG `FLINK_CATALOG` WITH('type' = 'generic_in_memory');
-- 创建DATABASE
CREATE DATABASE IF NOT EXISTS `FLINK_CATALOG`.`FLINK_DATABASE`;
CREATE TABLE IF NOT EXISTS `FLINK_CATALOG`.`FLINK_DATABASE`.`kafkacs2024` (
  `id` INT,
  `age` INT,
  `int_test` INT,
  `bigint_test` BIGINT,
  `float_test` FLOAT,
  `double_test` DOUBLE,
  `decimal_test` DECIMAL(18, 3),
  `varchar_test` STRING,
  `char_test` STRING,
  `date_test` DATE,
  `datetime_test` VARCHAR,
  `datetimev2_test` TIMESTAMP,
  `namex` STRING,
  `name` STRING,
  `time_test`  AS TO_TIMESTAMP(datetime_test),
  `sex_test` STRING,
  WATERMARK FOR time_test AS time_test - INTERVAL '0' SECOND  --为Rowtime定义Watermark。
) WITH (
  'connector' = 'kafka',
  'topic' = 'kafkacs2024',
  'properties.bootstrap.servers' = '192.168.12.140:9092,192.168.12.139:9092,192.168.12.141:9092',
  'properties.group.id' = 'ea4521bc-4948-4533-ab6e-8aeb0df06be7',
  'scan.startup.mode' = 'earliest-offset',
  'format' = 'json'
);
--   引用数据表:  [结果表]
--   type: MYSQL
--   datasourceName: mysql_5
--   tableName: jhhscs
CREATE TABLE IF NOT EXISTS `FLINK_CATALOG`.`FLINK_DATABASE`.`jhhscs1` (
  `id` INT,
    `sex_test` VARCHAR,
    `datetime_test` VARCHAR,
  `count_test` DOUBLE,
  `sum_test` DOUBLE,
  `avg_test` DOUBLE,
  `max_test` DOUBLE,
  `min_test` DOUBLE,
  PRIMARY KEY (`id`) NOT ENFORCED
) WITH (
  'connector' = 'jdbc',
  'url' = 'jdbc:mysql://192.168.12.207:3306/test?useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL',
  'username' = '#{username}',
  'password' = '#{password}',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'database-name' = 'test',
  'table-name' = 'jhhscs1'
);
 


 insert into  `FLINK_CATALOG`.`FLINK_DATABASE`.`jhhscs1`
 SELECT  id,sex_test,datetime_test,
  COUNT(double_test)  OVER (  PARTITION BY sex_test ORDER BY time_test    RANGE BETWEEN INTERVAL '2' MINUTE preceding AND CURRENT ROW) as count_test,
  SUM(double_test)  OVER (  PARTITION BY sex_test   ORDER BY time_test    RANGE BETWEEN INTERVAL '2' MINUTE preceding AND CURRENT ROW) as `sum_test`,
  AVG(double_test)  OVER (  PARTITION BY sex_test   ORDER BY time_test    RANGE BETWEEN INTERVAL '2' MINUTE preceding AND CURRENT ROW) as `avg_test`,
  MAX(double_test)  OVER (  PARTITION BY sex_test   ORDER BY time_test    RANGE BETWEEN INTERVAL '2' MINUTE preceding AND CURRENT ROW) as `max_test`,
  MIN(double_test)  OVER (  PARTITION BY sex_test   ORDER BY time_test    RANGE BETWEEN INTERVAL '2' MINUTE preceding AND CURRENT ROW) as `min_test`
 FROM  `FLINK_CATALOG`.`FLINK_DATABASE`.`kafkacs2024`

测试数据

message:{"id":2,"age":2,"int_test":2,"bigint_test":1000000002,"float_test":20.123,"double_test":20.1234567,"decimal_test":20.11,"varchar_test":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","char_test":"步瑞2","email_test":"m@sina.com","sex_test":"女","date_test":"2021-05-11","datetime_test":"2024-01-25 19:29:56.745","datetimev2_test":"2024-01-25 19:29:56.745","namex":"步瑞f1_2|f2_2|f3_2","name":"xx12"}
18446744073702197459
2147483647
message:{"id":3,"age":3,"int_test":3,"bigint_test":1000000003,"float_test":30.123,"double_test":30.1234567,"decimal_test":30.11,"varchar_test":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","char_test":"濮致之3","email_test":"A@126.com","sex_test":"男","date_test":"2021-05-11","datetime_test":"2024-01-25 19:29:57.054","datetimev2_test":"2024-01-25 19:29:57.054","namex":"濮致之f1_3|f2_3|f3_3","name":"xx13"}
18446744073702197459
2147483647
message:{"id":4,"age":4,"int_test":4,"bigint_test":1000000004,"float_test":40.123,"double_test":40.1234567,"decimal_test":40.11,"varchar_test":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","char_test":"孟明4","email_test":"R@qq.com","sex_test":"男","date_test":"2021-05-11","datetime_test":"2024-01-25 19:29:57.069","datetimev2_test":"2024-01-25 19:29:57.069","namex":"孟明f1_4|f2_4|f3_4","name":"xx14"}
18446744073702197459
2147483647
message:{"id":5,"age":5,"int_test":5,"bigint_test":1000000005,"float_test":50.123,"double_test":50.1234567,"decimal_test":50.11,"varchar_test":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","char_test":"杨江5","email_test":"g@googlemail.com","sex_test":"男","date_test":"2021-05-11","datetime_test":"2024-01-25 19:29:57.088","datetimev2_test":"2024-01-25 19:29:57.088","namex":"杨江f1_5|f2_5|f3_5","name":"xx15"}
18446744073702197459
2147483647
message:{"id":6,"age":6,"int_test":6,"bigint_test":1000000006,"float_test":60.123,"double_test":60.1234567,"decimal_test":60.11,"varchar_test":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","char_test":"苍江邦6","email_test":"s@yahoo.com.cn","sex_test":"男","date_test":"2021-05-11","datetime_test":"2024-01-25 19:29:57.102","datetimev2_test":"2024-01-25 19:29:57.102","namex":"苍江邦f1_6|f2_6|f3_6","name":"xx16"}
18446744073702197459
2147483647
message:{"id":7,"age":7,"int_test":7,"bigint_test":1000000007,"float_test":70.123,"double_test":70.1234567,"decimal_test":70.11,"varchar_test":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","char_test":"须山栋7","email_test":"X@263.net","sex_test":"男","date_test":"2021-05-11","datetime_test":"2024-01-25 19:29:57.113","datetimev2_test":"2024-01-25 19:29:57.113","namex":"须山栋f1_7|f2_7|f3_7","name":"xx17"}
18446744073702197459
2147483647
message:{"id":8,"age":8,"int_test":8,"bigint_test":1000000008,"float_test":80.123,"double_test":80.1234567,"decimal_test":80.11,"varchar_test":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","char_test":"通晶筠8","email_test":"z@qq.com","sex_test":"女","date_test":"2021-05-11","datetime_test":"2024-01-25 19:29:57.125","datetimev2_test":"2024-01-25 19:29:57.125","namex":"通晶筠f1_8|f2_8|f3_8","name":"xx18"}
18446744073702197459
2147483647
message:{"id":9,"age":9,"int_test":9,"bigint_test":1000000009,"float_test":90.123,"double_test":90.1234567,"decimal_test":90.11,"varchar_test":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","char_test":"谭琛9","email_test":"h@qq.com","sex_test":"男","date_test":"2021-05-11","datetime_test":"2024-01-25 19:29:57.137","datetimev2_test":"2024-01-25 19:29:57.137","namex":"谭琛f1_9|f2_9|f3_9","name":"xx19"}
18446744073702197459
2147483647

结果示例

倾一生爱恋换一世纯真

关注

7
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
实时计算flinksql使用sum(),avg(),max(),min(),over(),WATERMARK示例

实时计算flinksql使用sum(),avg(),max(),min(),over(),WATERMARK示例
复制链接

扫一扫