微信公众号：大数据开发运维架构
关注可了解更多大数据相关的资讯。问题或建议，请公众号留言;
如果您觉得“大数据开发运维架构”对你有帮助，欢迎转发朋友圈
从微信公众号拷贝过来，格式有些错乱，建议直接去公众号阅读
上篇文章展示了Flink连接Kafka集群的代码，平时我们做统计分析，经常会用到FlinkSQL,这里就贴一下FlinkSQL消费Kafka数据存入Mysql的代码实例，更多实战内容关注微信公众号：“大数据开发运维架构”
版本信息：
flink1.9.0
kafka0.10.0
mysql5.6.40
废话不多说直接上实战代码：
1.这里mysql数据库recommend中有一张表student，创建表语句：
SETNAMESutf8mb4;SETFOREIGN_KEY_CHECKS =0;-- ------------------------------ Table structure for student-- ----------------------------DROPTABLEIFEXISTS`student`;CREATETABLE`student`(`id`int(64)NULLDEFAULTNULL,`name`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULL,`course`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULL,`score`double(128,0)NULLDEFAULTNULL)ENGINE=InnoDBCHARACTERSET= utf8COLLATE= utf8_general_ci ROW_FORMAT =Compact;SETFOREIGN_KEY_CHECKS =1;
2.对应student表的实体类：
packagecom.hadoop.ljs.flink.sql;/***@author: Created By lujisen*@companyChinaUnicom Software JiNan*@date: 2020-03-01 07:50*@version: v1.0*@description: com.hadoop.ljs.flink.sql */publicclassStudent{/*唯一ID*/intid;/*名字*/ String name;/*课程*/ String course;/*分数*/doublescore;publicStudent(Integer f0, String f1, String f2, Double f3){ id=f0; name=f1; course=f2; score=f3; }publicintgetId(){returnid; }publicvoidsetId(intid){this.id = id; }publicStringgetName(){returnname; }publicvoidsetName(String name){this.name = name; }publicStringgetCourse(){returncourse; }publicvoidsetCourse(String course){this.course = course; }publicdoublegetScore(){returnscore; }publicvoidsetScore(doublescore){this.score = score; }}
3.自定义Sink类，存数据到mysql中：
packagecom.hadoop.ljs.flink.sql;importorg.apache.flink.configuration.Configuration;importorg.apache.flink.streaming.api.functions.sink.RichSinkFunction;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;/***@author: Created By lujisen*@companyChinaUnicom Software JiNan*@date: 2020-03-01 07:48*@version: v1.0*@description: com.hadoop.ljs.flink.sql */publicclassSinkStudent2MySQLextendsRichSinkFunction{publicstaticfinalString url="jdbc:mysql://10.124.165.31:3306/recommend??useUnicode=true&characterEncoding=UTF-8";publicstaticfinalString userName="root";publicstaticfinalString password="123456a?";privatestaticfinallongserialVersionUID = -4443175430371919407L; PreparedStatement ps;privateConnection connection;/**这里的open只调用一次*@paramparameters*@throwsException */@Overridepublicvoidopen(Configuration parameters)throwsException{super.open(parameters); connection = getConnection();String sql ="replace into student(id,name,course,score) values(?, ?, ?,?);";ps =this.connection.prepareStatement(sql); }@Overridepublicvoidclose()throwsException{super.close();if(connection !=null) { connection.close(); }if(ps !=null) { ps.close(); } }/** * 每条数据的插入都要调用一次 invoke() 方法 **@paramcontext*@throwsException */@Overridepublicvoidinvoke(Student student, Context context)throwsException{/*对每一条数据进行处理，组装数据*/ps.setLong(1, student.getId());ps.setString(2,student.getName());ps.setString(3, student.getCourse());ps.setDouble(4,student.getScore()); ps.executeUpdate(); }privatestaticConnectiongetConnection(){Connection con =null;try{Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url,userName,password);System.out.println("msql连接成功！");}catch(Exception e) {System.out.println("msql连接失败，错误信息"+ e.getMessage()); }returncon; }}
4.主函数类，从kafka接收消息，对每行数据进行拆分，注册为临时表，调用自定义SinkStudent2MySQL类，存入数据到student表中：
package com.hadoop.ljs.flink.sql;importorg.apache.flink.api.common.functions.FilterFunction;importorg.apache.flink.api.common.functions.MapFunction;importorg.apache.flink.api.common.serialization.SimpleStringSchema;importorg.apache.flink.api.common.typeinfo.Types;importorg.apache.flink.api.java.tuple.Tuple4;importorg.apache.flink.streaming.api.datastream.DataStream;importorg.apache.flink.streaming.api.environment.StreamExecutionEnvironment;importorg.apache.flink.streaming.connectors.kafka.FlinkKafkaConsumer010;importorg.apache.flink.table.api.EnvironmentSettings;importorg.apache.flink.table.api.Table;importorg.apache.flink.table.api.java.StreamTableEnvironment;importjava.util.Properties;/** * @author: Created By lujisen * @company ChinaUnicom Software JiNan * @date: 2020-03-01 07:47 * @version: v1.0 * @description: com.hadoop.ljs.flink.sql */publicclassFlinkKafkaKerberosSQLConsumer {publicstaticfinalStringkrb5Conf="D:\\kafkaSSL\\krb5.conf";publicstaticfinalStringkafkaJaasConf="D:\\kafkaSSL\\kafka_client_jaas.conf";publicstaticfinalStringtopic="topic2";publicstaticfinalStringconsumerGroup="test_topic2";publicstaticfinalStringbootstrapServer="salver31.hadoop.unicom:6667,salver32.hadoop.unicom:6667";publicstaticvoidmain(String[] args) throws Exception {//在windows中设置JAAS，也可以通过-D方式传入System.setProperty("java.security.krb5.conf", krb5Conf);System.setProperty("java.security.auth.login.config", kafkaJaasConf); StreamExecutionEnvironment senv = StreamExecutionEnvironment.getExecutionEnvironment();senv.setDefaultLocalParallelism(1); EnvironmentSettings bsSettings = EnvironmentSettings.newInstance().useBlinkPlanner().inStreamingMode().build(); StreamTableEnvironment tableEnv = StreamTableEnvironment.create(senv, bsSettings); FlinkKafkaConsumer010 myConsumer =newFlinkKafkaConsumer010(topic,newSimpleStringSchema(),getComsumerProperties());DataStream stream = senv.addSource(myConsumer);stream.filter(newFilterFunction() {@Overridepublicbooleanfilter(Stringvalue) throws Exception {if(null==value||value.split(",").length!=4){returnfalse; }returntrue; } });DataStream> map = stream.map(newMapFunction>() {privatestaticfinal long serialVersionUID =1471936326697828381L;@OverridepublicTuple4 map(Stringvalue) throws Exception {String[] split = value.split(",");returnnewTuple4<>(Integer.valueOf(split[0]), split[1], split[2], Double.valueOf(split[3])); } });//将数据注册为临时表，并制定fieldstableEnv.registerDataStream("student", map,"id,name,course,score");Table sqlQuery = tableEnv.sqlQuery("select id,name,course,score from student");DataStream> appendStream = tableEnv.toAppendStream(sqlQuery, Types.TUPLE(Types.INT, Types.STRING, Types.STRING,Types.DOUBLE)); appendStream.print();/*将每条数据转换成student实体类数据，sink到mysql中*/appendStream.map(newMapFunction, Student>() {privatestaticfinal long serialVersionUID =-4770965496944515917L;@OverridepublicStudent map(Tuple4 value) throws Exception {returnnewStudent(value.f0, value.f1, value.f2,value.f3); }}).addSink(newSinkStudent2MySQL());senv.execute("FlinkKafkaKerberosSQLConsumer"); }/*获取Kafka消费端配置*/privatestaticProperties getComsumerProperties() {Properties props =newProperties();props.put("bootstrap.servers",bootstrapServer);props.put("group.id",consumerGroup);props.put("auto.offset.reset","earliest");/*keberos集群，必须制定以下三项配置*/props.put("security.protocol","SASL_PLAINTEXT");props.put("sasl.kerberos.service.name","kafka");props.put("sasl.mechanism","GSSAPI");returnprops; }}
5.这里贴下pom.xml：
1.9.01.82.111.2.50.10.1.0org.apache.flinkflink-connector-kafka-0.10_2.11${flink.version}org.apache.flinkflink-table-planner-blink_${scala.binary.version}${flink.version}org.apache.flinkflink-table-runtime-blink_${scala.binary.version}${flink.version}org.apache.flinkflink-table-common${flink.version}mysqlmysql-connector-java5.1.46
6.发送数据到kafka，每条记录用逗号“,”拆分：
1001,name1,yuwen1,811002,name2,yuwen2,821003,name3,yuwen3,83
发送数据截图：
最近一些文章都是根据粉丝留言进行编写实战代码，如有其他需求直接给我公众号留言即可，觉得有用，多给转转朋友圈，谢谢关注！！！
