package com.ysservice.streaming.source;
import com.alibaba.ververica.cdc.debezium.DebeziumDeserializationSchema;
import com.ysservice.streaming.utils.MysqlUtil;
import com.ysservice.streaming.utils.SystemConstants;
import io.debezium.data.Envelope;
import org.apache.flink.api.common.typeinfo.BasicTypeInfo;
import org.apache.flink.api.common.typeinfo.TypeInformation;
import org.apache.flink.util.Collector;
import org.apache.kafka.connect.data.Field;
import org.apache.kafka.connect.data.Schema;
import org.apache.kafka.connect.data.Struct;
import org.apache.kafka.connect.source.SourceRecord;
/**
* @Description:将cdc数据反序列化成sql语句
* @author: WuBo
* @date:2021/10/14 11:13
*/
public class JsonDebeziumDeserializationSchema implements DebeziumDeserializationSchema {
@Override
public void deserialize(SourceRecord sourceRecord, Collector collector) throws Exception {
//从sourceRecord中获取binlog日志的database,table,type,after,before等数据
String topic = sourceRecord.topic();
String[] split = topic.split("[.]");
String database = split[1];
String table = split[2];
//获取操作类型
Envelope.Operation operation = Envelope.operationFor(sourceRecord);
//获取数据本身
Struct struct = (Struct) sourceRecord.value();
Struct after = struct.getStruct("after");
Struct before = struct.getStruct("before");
//用于拼接所有insert的字段名
String fieldNames = "";
//用于拼接所有insert的数据
String datas = "";
//用于拼接所有更新的数据
String updataDatas = "";
//用于拼接所有更新的where条件
String updataWhereData = "";
//获取每张表的唯一主键,用于删除数据
String key = MysqlUtil.getMysqlTableKey(database + "." + table);
//用于获取每张表唯一主键下所对应的数据
String key_data = "";
/*
1,同时存在 beforeStruct 跟 afterStruct数据的话,就代表是update的数据
2,只存在 beforeStruct 就是delete数据
3,只存在 afterStruct数据 就是insert数据
*/
if (after != null && before == null) {
//拼接insert的sql
Schema schema = after.schema();
for (Field field : schema.fields()) {
Object data = after.get(field);
String fieldName = field.name();
String fieldSchema = field.schema().toString();
if (data != null && !("".equals(data.toString()))) {
data = data.toString();
} else {
data = "null";
}
if (MysqlUtil.isNumType(fieldSchema) || "null".equals(data.toString())) {
datas += data + ",";
fieldNames += fieldName + ",";
} else {
datas += "'" + data + "',";
fieldNames += fieldName + ",";
}
}
} else if (before != null && after == null) {
//拼接delete的sql
Schema schema = before.schema();
for (Field field : schema.fields()) {
Object data = before.get(field);
String fieldName = field.name();
String fieldSchema = field.schema().toString();
if (MysqlUtil.isNumType(fieldSchema)) {
if (key.equals(fieldName)) {
key_data = data.toString();
}
} else {
if (key.equals(fieldName)) {
key_data = "'" + data.toString() + "'";
}
}
}
} else if (before != null && after != null) {
//拼接update更新后的sql
Schema schema = after.schema();
for (Field field : schema.fields()) {
Object data = after.get(field);
String fieldName = field.name();
String fieldSchema = field.schema().toString();
if (data != null && !("".equals(data.toString()))) {
data = data.toString();
} else {
data = "null";
}
if (MysqlUtil.isNumType(fieldSchema) || "null".equals(data.toString())) {
updataDatas += fieldName + "=" + data + ",";
} else {
updataDatas += fieldName + "=" + "'" + data + "',";
}
}
//拼接update更新前的sql
Schema before_schema = before.schema();
for (Field field : before_schema.fields()) {
Object data = before.get(field);
String fieldName = field.name();
String fieldSchema = field.schema().toString();
if (MysqlUtil.isNumType(fieldSchema)) {
if (key.equals(fieldName)) {
key_data = data.toString();
}
} else {
if (key.equals(fieldName)) {
key_data = "'" + data.toString() + "'";
}
}
}
}
//获取gp库的表名
String gpTableName = SystemConstants.getCdc_gp_schema() + "." + SystemConstants.getCdc_mysql_service_name() + "_" + database + "_" + table;
//按数据更新类型拼接最终的sql
String sql = "";
String type = operation.toString().toLowerCase();
if ("create".equals(type)) {
sql = "insert into " + gpTableName + "(" + fieldNames.substring(0, (fieldNames.length() - 1)) + ") values(" + datas.substring(0, datas.length() - 1) + ");";
} else if ("delete".equals(type)) {
sql = "delete from " + gpTableName + " where " + key + "=" + key_data + ";";
} else if ("update".equals(type)) {
sql = "update " + gpTableName + " set " + updataDatas.substring(0, updataDatas.length() - 1) + " where " + key + "=" + key_data + ";";
}
//如果sql有效,就正常写入gp库,如果sql无效就写入gp错误日志库
if ((!("".equals(fieldNames) && !("".equals(datas)))) || (!("".equals(key) && !("".equals(key_data)))) || (!("".equals(updataDatas) && !("".equals(updataWhereData))))) {
collector.collect(gpTableName + "<-gpTableName->" + sql);
} else {
collector.collect(SystemConstants.getCdc_gp_error_log_table() + "<-gpTableName->" + "insert into " + SystemConstants.getCdc_gp_error_log_table() + "(log_data)" + " values" + "('" + "gpTableName=" + gpTableName + ",type=" + type + ",before=" + before + ",after=" + after + "');");
}
}
@Override
public TypeInformation<String> getProducedType() {
return BasicTypeInfo.STRING_TYPE_INFO;
}
}
package com.ysservice.streaming.utils;
/**
* @Description:MysqlUtil
* @author: WuBo
* @date:2021/10/15 12:24
*/
public class MysqlUtil {
/**
* 传入一个mysql的数据类型,判断该数据类型是否是数字型的
* @param schema
* @return
*/
public static Boolean isNumType(String schema) {
String[] mysqlNumTypeArr = new String[]{
"TINYINT",
"MEDIUMINT",
"INT",
"INTEGER",
"BIGINT",
"FLOAT",
"DOUBLE",
"DECIMAL",
"LONG"
};
Boolean flag = false;
for (String mysqlNumType : mysqlNumTypeArr) {
if (schema.toUpperCase().contains(mysqlNumType)) {
flag = true;
break;
}
}
return flag;
}
/**
* 传入一个表名,获得该表名的唯一主键key
* @param table
* @return
*/
public static String getMysqlTableKey(String table) {
String[] table_keyArr = SystemConstants.getCdc_table_key();
String key = "";
for (String table_key : table_keyArr) {
String[] split = table_key.split(":");
if (split[0].equals(table)){
key = split[1];
}
}
return key;
}
}
flink cdc 将binlog日志反序列化成sql语句
最新推荐文章于 2023-04-06 20:50:43 发布