(4)FlinkSQL将socket数据写入到mysql方式一

NBI大数据湖北

本章节主要演示从socket接收数据,通过滚动窗口每30秒运算一次窗口数据,然后将结果写入Mysql数据库
图片
(1)准备一个实体对象,消息对象

package com.pojo;

import java.io.Serializable;

/**

  • Created by lj on 2022-07-05.
    */

public class WaterSensor implements Serializable {

private String id;
private long ts;
private int vc;

public WaterSensor(){

}

public WaterSensor(String id,long ts,int vc){
    this.id = id;
    this.ts = ts;
    this.vc = vc;
}

public int getVc() {
    return vc;
}

public void setVc(int vc) {
    this.vc = vc;
}

public String getId() {
    return id;
}

public void setId(String id) {
    this.id = id;
}

public long getTs() {
    return ts;
}

public void setTs(long ts) {
    this.ts = ts;
}

}

(2)编写socket代码,模拟数据发送

package com.producers;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.net.ServerSocket;
import java.net.Socket;
import java.util.Random;

/**

  • Created by lj on 2022-07-05.
    */

public class Socket_Producer {

public static void main(String[] args) throws IOException {

    try {
        ServerSocket ss = new ServerSocket(9999);
        System.out.println("启动 server ....");
        Socket s = ss.accept();
        BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(s.getOutputStream()));
        String response = "java,1,2";

        //每 2s 发送一次消息
        int i = 0;
        Random r=new Random();   
        String[] lang = {"flink","spark","hadoop","hive","hbase","impala","presto","superset","nbi"};

        while(true){
            Thread.sleep(2000);
            response= lang[r.nextInt(lang.length)] + "," + i + "," + i+"\n";
            System.out.println(response);
            try{
                bw.write(response);
                bw.flush();
                i++;
            }catch (Exception ex){
                System.out.println(ex.getMessage());
            }

        }
    } catch (IOException | InterruptedException e) {
        e.printStackTrace();
    }
}

}

(3)从socket端接收数据,并设置30秒触发执行一次窗口运算

package com.examples;

import com.pojo.WaterSensor;
import com.sinks.RetractStream_Mysql;
import org.apache.flink.api.common.functions.MapFunction;
import org.apache.flink.streaming.api.datastream.DataStreamSource;
import org.apache.flink.streaming.api.datastream.SingleOutputStreamOperator;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
import org.apache.flink.types.Row;

import static org.apache.flink.table.api.Expressions.$;

/**

  • Created by lj on 2022-07-06.
    */

public class Flink_Group_Window_Tumble_Sink_Mysql {

public static void main(String[] args) throws Exception {

    StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
    env.setParallelism(1);
    StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
    DataStreamSource<String> streamSource = env.socketTextStream("127.0.0.1", 9999,"\n");
    SingleOutputStreamOperator<WaterSensor> waterDS = streamSource.map(new MapFunction<String, WaterSensor>() {
        @Override
        public WaterSensor map(String s) throws Exception {
            String[] split = s.split(",");
            return new WaterSensor(split[0], Long.parseLong(split[1]), Integer.parseInt(split[2]));
        }
    });

    // 将流转化为表
    Table table = tableEnv.fromDataStream(waterDS,
            $("id"),
            $("ts"),
            $("vc"),
            $("pt").proctime());

    tableEnv.createTemporaryView("EventTable", table);

    Table result = tableEnv.sqlQuery(
            "SELECT " +
                    "id, " +                //window_start, window_end,
                    "COUNT(ts) ,SUM(ts)" +
                    "FROM TABLE( " +
                    "TUMBLE( TABLE EventTable , " +
                    "DESCRIPTOR(pt), " +
                    "INTERVAL '30' SECOND)) " +
                    "GROUP BY id , window_start, window_end"
    );

    tableEnv.toRetractStream(result, Row.class).addSink(new RetractStream_Mysql()); 
    env.execute();
}

}

(4)定义一个写入到mysql的sink

package com.sinks;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import org.apache.flink.api.java.tuple.Tuple2;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;
import org.apache.flink.types.Row;

/**

  • Created by lj on 2022-07-06.
    */

public class RetractStream_Mysql extends RichSinkFunction<Tuple2<Boolean, Row>> {

private static final long serialVersionUID = -4443175430371919407L;
PreparedStatement ps;
private Connection connection;

/**
 * open() 方法中建立连接,这样不用每次 invoke 的时候都要建立连接和释放连接
 *
 * @param parameters
 * @throws Exception
 */
@Override
public void open(Configuration parameters) throws Exception {
    super.open(parameters);
    connection = getConnection();
}

@Override
public void close() throws Exception {
    super.close();
    //关闭连接和释放资源
    if (connection != null) {
        connection.close();
    }
    if (ps != null) {
        ps.close();
    }
}

/**
 * 每条数据的插入都要调用一次 invoke() 方法
 *
 * @param context
 * @throws Exception
 */
@Override
public void invoke(Tuple2<Boolean, Row> userPvEntity, Context context) throws Exception {
    String sql = "INSERT INTO flinkcomponent(componentname,componentcount,componentsum) VALUES(?,?,?);";
    ps = this.connection.prepareStatement(sql);

    ps.setString(1,userPvEntity.f1.getField(0).toString());
    ps.setInt(2, Integer.parseInt(userPvEntity.f1.getField(1).toString()));
    ps.setInt(3, Integer.parseInt(userPvEntity.f1.getField(2).toString()));
    ps.executeUpdate();
}

private static Connection getConnection() {
    Connection con = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false","root","root");
    } catch (Exception e) {
        System.out.println("-----------mysql get connection has exception , msg = "+ e.getMessage());
    }
    return con;
}

}

(5)效果演示,每30秒往数据库写一次数据
图片

 

阅读 240

10年IT行业从业经验,5+年技术型管理经验。

4 声望
0 粉丝
0 条评论
推荐阅读
(4)SparkSQL中如何定义UDF和使用UDF
Spark SQL中用户自定义函数,用法和Spark SQL中的内置函数类似;是saprk SQL中内置函数无法满足要求,用户根据业务需求自定义的函数。首先定义一个UDF函数:

NBI大数据阅读 147

封面图
聊聊Redis的数据热点问题
&emsp;&emsp;前两天,我们使用的某云厂商服务挂了,而且一挂就是挂大半天,我们的服务强依赖于他们,所以我们也跟着一起挂。然而我们却无能为力,只能等他们恢复。事故原因中听他们提到Redis有个热key,正好我在...

xindoo1阅读 1.6k

封面图
基于 Impala 的高性能数仓实践之物化视图服务
接上篇,前两篇分别讲了执行引擎和虚拟数仓,它们是让一个 SQL 又快又好地执行的关键。但如果某些 SQL 过于复杂,比如多张大表进行 Join 并有大量的聚合类操作,那么再优秀的执行引擎也无法保证能够秒级执行完成...

网易数帆1阅读 425

Apache Kyuubi 在B站大数据场景下的应用实践
近几年随着B站业务高速发展,数据量不断增加,离线计算集群规模从最初的两百台发展到目前近万台,从单机房发展到多机房架构。在离线计算引擎上目前我们主要使用Spark、Presto、Hive。架构图如下所示,我们的BI、A...

网易数帆1阅读 366

封面图
从0到1设计通用数据大屏搭建平台
一直以来,许多产品平台都在尝试通过可视化搭建的手段来降低 GUI 应用的研发门槛,提高生产效率。随着我们业务的发展,数据建设的完善,用户对于数据可视化的诉求也日益增多,而数据大屏是数据可视化的其中一种展...

vivo互联网技术5阅读 485

技术分享 | Presto性能对比测试:Kubernetes部署 VS 物理机部署
Presto是开源分布式SQL查询引擎,可以对从GB到PB级大小的数据源进行交互式分析查询。Presto支持Hive、Cassandra、关系型数据库甚至专有数据存储等多种数据源,允许跨源查询。(详见参考[1] )

Alluxio1阅读 246

封面图
Apache Hudi X Apache Kyuubi,中国移动云湖仓一体的探索与实践
导读:在云原生 + 大数据的时代,随着业务数据量的爆炸式增长以及对高时效性的要求,云原生大数据分析技术,经历了从传统数仓到数据湖,再到湖仓一体的演进。本文主要介绍移动云云原生大数据分析 LakeHouse 的整...

网易数帆阅读 316

10年IT行业从业经验,5+年技术型管理经验。

4 声望
3 粉丝
宣传栏