Flink动态ClickhouseSink+自动建表

通过自定义注解的形式，对JdbcSink进行封装，支持自动建表、自动拼接insert语句

主类

package flink.security.sink;


import cn.hutool.core.util.ObjectUtil;
import cn.hutool.json.JSONUtil;
import lombok.Cleanup;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.flink.connector.jdbc.JdbcConnectionOptions;
import org.apache.flink.connector.jdbc.JdbcExecutionOptions;
import org.apache.flink.connector.jdbc.JdbcSink;
import org.apache.flink.connector.jdbc.JdbcStatementBuilder;
import org.apache.flink.streaming.api.functions.sink.SinkFunction;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * 通用clickhouse sink
 */
@Slf4j
public class SecurityClickHouseSink {
    public static <T> SinkFunction<T> getSink(Class<T> model) throws Exception {
        createTable(model);
        String sql = createSql(model);
        return JdbcSink.sink(
                sql,
                new JdbcStatementBuilder<T>() {
                    @SneakyThrows
                    @Override
                    public void accept(PreparedStatement preparedStatement, T t) throws SQLException {
                        Class<?> obj = t.getClass();
                        Field[] declaredFields = obj.getDeclaredFields();
                        Stream<Field> usedFields = getUsedFields(declaredFields);
                        List<Field> fields = usedFields.collect(Collectors.toList());
                        for (int i = 0; i < fields.size(); i++) {
                            fields.get(i).setAccessible(true);
                            Object o = fields.get(i).get(t);
                            if (o instanceof Boolean) {
                                preparedStatement.setInt(i + 1, (Boolean) o ? 1 : 0);
                            } else if (o instanceof List) {
                                preparedStatement.setString(i + 1, JSONUtil.toJsonStr(o));
                            } else if (o instanceof Date) {
                                preparedStatement.setString(i + 1, DateFormatUtil.toYmdHms(((Date) o).getTime()));
                            } else {
                                preparedStatement.setObject(i + 1, o);
                            }
                        }
                    }
                },
                new JdbcExecutionOptions.Builder()
                        .withBatchSize(10000)
                        .withBatchIntervalMs(1000L * 10)
                        .build(),
                new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
                        .withDriverName(Constant.CLICKHOUSE_DRIVER_NAME)
                        .withUrl(Constant.CLICKHOUSE_URL)
                        .build()
        );
    }

    /**
     * 组装sql
     */
    private static <T> String createSql(Class<T> model) {
        // 1、获取表名
        SecurityTable tableAnno = model.getAnnotation(SecurityTable.class);
        if (tableAnno == null) {
            throw new RuntimeException("缺少SecurityTable注解");
        }
        String tableName = tableAnno.value().trim();
        // 2、获取字段名
        Field[] declaredFields = model.getDeclaredFields();
        // 2.1 过滤掉带SecurityField注解且isTableFile=false的字段
        Stream<Field> fields = getUsedFields(declaredFields);
        // 3.2 获取字段
        List<String> fieldnames = fields.map(SecurityClickHouseSink::getTableFiledName).collect(Collectors.toList());
        // 3、获取占位符
        List<String> placeholder = fieldnames.stream().map(f -> "?").collect(Collectors.toList());

        if (ObjectUtil.isEmpty(placeholder) || ObjectUtil.isEmpty(fieldnames)) {
            throw new RuntimeException("缺少必要的表字段！");
        }
        // 分布式表需要插入视图
        String distributed = tableAnno.distributed();
        return "insert into " + tableName + (StringUtils.isBlank(distributed) ? "" : "_mr") + "(" + StringUtils.join(fieldnames, ",") + ") values(" + StringUtils.join(placeholder, ",") + ")";
    }

    /**
     * 组装sql
     */
    private static <T> void createTable(Class<T> model) throws Exception {
        // 1、获取表名
        SecurityTable tableAnno = model.getAnnotation(SecurityTable.class);
        if (tableAnno == null) {
            throw new RuntimeException("缺少SecurityTable注解");
        }
        String tableName = tableAnno.value().trim();
        String engine = tableAnno.engine().trim();
        String distributed = tableAnno.distributed().trim();
        String cluster = tableAnno.cluster().trim();
        // 2、获取字段名
        Field[] declaredFields = model.getDeclaredFields();
        // 2.1 过滤掉带SecurityField注解且isTableFile=false的字段
        Stream<Field> fields = getUsedFields(declaredFields);
        // 3.2 获取字段
        List<String> fieldnames = fields.map(f -> {
            String wordType = f.getType().getName();
            SecurityField annotation = f.getAnnotation(SecurityField.class);
            switch (wordType) {
                case "java.lang.String":
                    wordType = "String";
                    break;
                case "java.util.Date":
                case "java.time.LocalDate":
                case "java.time.LocalDateTime":
                    wordType = "Datetime";
                    break;
                case "java.util.List":
                    wordType = "String";
                    break;
                case "boolean":
                    wordType = "UInt8";
                    break;
                case "java.lang.Double":
                    wordType = "Decimal(15,3)";
                    break;
                case "java.lang.Integer":
                    wordType = "Int32";
                    break;
                case "java.lang.Long":
                    wordType = "Int64";
                    break;
                default:
                    wordType = "String";
            }
            if (annotation == null) {
                return f.getName() + " " + wordType + " comment ''";
            } else {
                String comment = Optional.of(annotation.comment()).orElse("").replace("'", "`").replace("'", "`");
                return (StringUtils.isBlank(annotation.value()) ? f.getName() : annotation.value()) + " " + wordType + " comment '" + comment + "'";
            }
        }).collect(Collectors.toList());

        if (ObjectUtil.isEmpty(fieldnames)) {
            throw new RuntimeException("缺少必要的表字段！");
        }

        String cols = StringUtils.join(fieldnames, ",\n");

        try(Connection connection = DriverManager.getConnection(Constant.CLICKHOUSE_URL, Constant.CLICKHOUSE_USERNAME, Constant.CLICKHOUSE_PASSWORD);) {
            String localtable = String.format("create table if not exists  %s %s (%s) engine = %s", tableName, cluster, cols, engine);
            log.error(localtable);
            try (PreparedStatement preparedStatement = connection.prepareStatement(localtable)) {
                preparedStatement.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("建表失败请查看建表语句：\r\n" + localtable);
            }
            if (StringUtils.isNotBlank(distributed)) {
//                localtable = "CREATE TABLE default.PowerConsumption ON CLUSTER default_cluster AS default.PowerConsumption_local\n" +
//                        "ENGINE = Distributed(default_cluster, default, PowerConsumption_local, rand());";
                localtable = String.format("create table if not exists  %s_mr  %s AS %s engine = %s", tableName, cluster, tableName, distributed);
                log.error(localtable);
                try (PreparedStatement preparedStatement = connection.prepareStatement(localtable)) {
                    preparedStatement.executeUpdate();
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new RuntimeException("建表失败请查看建表语句：\r\n" + localtable);
                }
            }
        }
    }

    private static String getTableFiledName(Field field) {
        SecurityField annotation = field.getAnnotation(SecurityField.class);
        if (annotation == null) {
            return field.getName();
        } else {
            String value = annotation.value();
            if (StringUtils.isBlank(value)) {
                return field.getName();
            }
            return value;
        }
    }

    private static Stream<Field> getUsedFields(Field[] declaredFields) {
        return Arrays.stream(declaredFields).filter(field -> {
            SecurityField annotation = field.getAnnotation(SecurityField.class);
            return annotation == null || annotation.isTableField();
        });
    }

}

 自定义注解

package cn.chinaunicom.sdsi.flink.security.anno;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SecurityField {
    String value() default "";
    boolean isTableField() default true;
    String comment() default "''";
}

package cn.chinaunicom.sdsi.flink.security.anno;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface SecurityTable {
    String value();
    String engine() default "";
    String distributed() default "";
    String cluster() default "";
}

使用方式

SingleOutputStreamOperator<SecurityAlarms> process = attack.connect(broadcastStream).process(new BroadcastProcessFunction<SecurityAlarms, Co...

SinkFunction<SecurityAlarms> sink = SecurityClickHouseSink.getSink(SecurityAlarms.class);
process.addSink(sink);

Bean的定义

@Data
@SecurityTable(value = "security_data_center.security_alarms",
        cluster = "on cluster cluster_p2_r2_2345",
        engine = "ReplicatedReplacingMergeTree partition by (toYYYYMMDD(collectorReceiptTime)) order by (collectorReceiptTime,eventId)",
        distributed = "Distributed(cluster_p2_r2_2345,security_data_center,security_alarms,rand())")
public class SecurityAlarms extends CommonFileds{

    @SecurityField(comment = "数据接收来源【数据接收来源（安恒云）】")
    private String dispatchSource;
    @SecurityField(comment = "活动目录唯一标识【活动目录唯一标识】")
    private String DN;
    @SecurityField(comment = "DPI告警类型【DPI数据告警类型】")
    private String DPIAlertType;
}