需求形貌：
1、数据从 Kafka 写入 Hive。
2、相关配置存放于 Mysql 中，通过 Mysql 举办法态读取。
3、此案例中的 Kafka 是举行了 Kerberos 安全认证的，如果不需要自行修改。
4、Flink 集成 Kafka 写入 Hive 需要举行 checkpoint 才能落盘至 HDFS。
5、先在 Hive 中创建表然后动态获取 Hive 的表结构。
6、Kafka 数据为 Json 格式，通过 FlatMap 扁平化处理惩罚后，根据表结构封装到 Row 中后完成写入。
7、写入时转换成暂时视图模式，利用 Flink-Sql 实现数据写入。
8、当地测试时 Hive 相关文件要放置到 resources 目次下。
9、当地测试时可以编辑 resources.flink_backup_local.yml 通过 ConfigTools.initConf 方法获取配置。
1）导入相关依靠
这里的依靠比力冗余，各人可以根据各自需求做删除或保留。
2）代码实现
2.1.resources
2.1.1.appconfig.yml
- mysql.url: "jdbc:mysql://1.1.1.1:3306/test?useSSL=false"
- mysql.username: "test"
- mysql.password: "123456"
- mysql.driver: "com.mysql.jdbc.Driver"
- log4j.rootLogger=info, stdout
- log4j.appender.stdout=org.apache.log4j.ConsoleAppender
- log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
- log4j.appender.stdout.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n
- <?xml version="1.0" encoding="UTF-8"?>
- <configuration monitorInterval="5">
- <Properties>
- <property name="LOG_PATTERN" value="%date{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n" />
- <property name="LOG_LEVEL" value="ERROR" />
- </Properties>
- <appenders>
- <console name="console" target="SYSTEM_OUT">
- <PatternLayout pattern="${LOG_PATTERN}"/>
- <ThresholdFilter level="${LOG_LEVEL}" onMatch="ACCEPT" onMismatch="DENY"/>
- </console>
- <File name="log" fileName="tmp/log/job.log" append="false">
- <PatternLayout pattern="%d{HH:mm:ss.SSS} %-5level %class{36} %L %M - %msg%xEx%n"/>
- </File>
- </appenders>
- <loggers>
- <root level="${LOG_LEVEL}">
- <appender-ref ref="console"/>
- <appender-ref ref="log"/>
- </root>
- </loggers>
- </configuration>
- hdfs:
- checkPointPath: 'hdfs://nameserver/user/flink/rocksdbcheckpoint'
- checkpointTimeout: 360000
- checkpointing: 300000
- maxConcurrentCheckpoints: 1
- minPauseBetweenCheckpoints: 10000
- restartInterval: 60
- restartStrategy: 3
- hive:
- defaultDatabase: 'ods'
- hiveConfDir: 'D:/WorkSpace/bigdata-flink-backup/kafka2hive/src/main/resources/'
- sourceTopic: 'topicA,topicB'
- tableName: 'table_name'
- kafka-consumer:
- prop:
- auto.offset.reset: 'earliest'
- bootstrap.servers: 'kfk01:9092,kfk02:9092,kfk03:9092'
- enable.auto.commit: 'false'
- fetch.max.bytes: '52428700'
- group.id: 'test'
- isKerberized: '1'
- keytab: 'D:/keytab/test.keytab'
- krb5Conf: 'D:/keytab/krb5.conf'
- max.poll.interval.ms: '300000'
- max.poll.records: '1000'
- principal: 'test@PRE.TEST.COM'
- security_protocol: 'SASL_PLAINTEXT'
- serviceName: 'kafka'
- session.timeout.ms: '600000'
- useTicketCache: 'false'
- topics: 'topicA,topicB'
- kafka-producer:
- defaultTopic: 'kafka2hive_error'
- prop:
- acks: 'all'
- batch.size: '1048576'
- bootstrap.servers: 'kfk01:9092,kfk02:9092,kfk03:9092'
- compression.type: 'lz4'
- key.serializer: 'org.apache.kafka.common.serialization.StringSerializer'
- retries: '3'
- value.serializer: 'org.apache.kafka.common.serialization.StringSerializer'
2.2.1.DBConn
- import java.sql.*;
- public class DBConn {
- private static final String driver = "com.mysql.jdbc.Driver"; //mysql驱动
- private static Connection conn = null;
- private static PreparedStatement ps = null;
- private static ResultSet rs = null;
- private static final CallableStatement cs = null;
- /**
- * 连接数据库
- * @return
- */
- public static Connection conn(String url,String username,String password) {
- Connection conn = null;
- try {
- Class.forName(driver); //加载数据库驱动
- try {
- conn = DriverManager.getConnection(url, username, password); //连接数据库
- } catch (SQLException e) {
- e.printStackTrace();
- }
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- }
- return conn;
- }
- /**
- * 关闭数据库链接
- * @return
- */
- public static void close() {
- if(conn != null) {
- try {
- conn.close(); //关闭数据库链接
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- }
- @Slf4j
- public class CommonUtils {
- public static StreamExecutionEnvironment setCheckpoint(StreamExecutionEnvironment env) throws IOException {
- // ConfigTools.initConf("local");
- Map hdfsMap = (Map) ConfigTools.mapConf.get("hdfs");
- env.enableCheckpointing(((Integer) hdfsMap.get("checkpointing")).longValue(), CheckpointingMode.EXACTLY_ONCE);//这里会造成offset提交的延迟
- env.getCheckpointConfig().setMinPauseBetweenCheckpoints(((Integer) hdfsMap.get("minPauseBetweenCheckpoints")).longValue());
- env.getCheckpointConfig().setCheckpointTimeout(((Integer) hdfsMap.get("checkpointTimeout")).longValue());
- env.getCheckpointConfig().setMaxConcurrentCheckpoints((Integer) hdfsMap.get("maxConcurrentCheckpoints"));
- env.getCheckpointConfig().enableExternalizedCheckpoints(CheckpointConfig.ExternalizedCheckpointCleanup.RETAIN_ON_CANCELLATION);
- env.setRestartStrategy(RestartStrategies.fixedDelayRestart(
- (Integer) hdfsMap.get("restartStrategy"), // 尝试重启的次数，不宜过小，分布式任务很容易出问题（正常情况），建议3-5次
- Time.of(((Integer) hdfsMap.get("restartInterval")).longValue(), TimeUnit.SECONDS) // 延时
- ));
- //设置可容忍的检查点失败数，默认值为0表示不允许容忍任何检查点失败
- env.getCheckpointConfig().setTolerableCheckpointFailureNumber(2);
- //设置状态后端存储方式
- env.setStateBackend(new RocksDBStateBackend((String) hdfsMap.get("checkPointPath"), true));
- // env.setStateBackend(new FsStateBackend((String) hdfsMap.get("checkPointPath"), true));
- // env.setStateBackend(new HashMapStateBackend(());
- return env;
- }
- public static FlinkKafkaConsumer<ConsumerRecord<String, String>> getKafkaConsumer(Map<String, Object> kafkaConf) throws IOException {
- String[] topics = ((String) kafkaConf.get("topics")).split(",");
- log.info("监听的topic: {}", topics);
- Properties properties = new Properties();
- Map<String, String> kafkaProp = (Map<String, String>) kafkaConf.get("prop");
- for (String key : kafkaProp.keySet()) {
- properties.setProperty(key, kafkaProp.get(key).toString());
- }
- if (!StringUtils.isBlank((String) kafkaProp.get("isKerberized")) && "1".equals(kafkaProp.get("isKerberized"))) {
- System.setProperty("java.security.krb5.conf", kafkaProp.get("krb5Conf"));
- properties.put("security.protocol", kafkaProp.get("security_protocol"));
- properties.put("sasl.jaas.config", "com.sun.security.auth.module.Krb5LoginModule required "
- + "useTicketCache=" + kafkaProp.get("useTicketCache") + " "
- + "serviceName="" + kafkaProp.get("serviceName") + "" "
- + "useKeyTab=true "
- + "keyTab="" + kafkaProp.get("keytab").toString() + "" "
- + "principal="" + kafkaProp.get("principal").toString() + "";");
- }
- properties.put("key.serializer", "org.apache.flink.kafka.shaded.org.apache.kafka.common.serialization.ByteArrayDeserializer");
- properties.put("value.serializer", "org.apache.flink.kafka.shaded.org.apache.kafka.common.serialization.ByteArrayDeserializer");
- FlinkKafkaConsumer<ConsumerRecord<String, String>> consumerRecordFlinkKafkaConsumer = new FlinkKafkaConsumer<ConsumerRecord<String, String>>(Arrays.asList(topics), new KafkaDeserializationSchema<ConsumerRecord<String, String>>() {
- @Override
- public TypeInformation<ConsumerRecord<String, String>> getProducedType() {
- return TypeInformation.of(new TypeHint<ConsumerRecord<String, String>>() {
- });
- }
- @Override
- public boolean isEndOfStream(ConsumerRecord<String, String> stringStringConsumerRecord) {
- return false;
- }
- @Override
- public ConsumerRecord<String, String> deserialize(ConsumerRecord<byte[], byte[]> record) throws Exception {
- return new ConsumerRecord<String, String>(
- record.topic(),
- record.partition(),
- record.offset(),
- record.timestamp(),
- record.timestampType(),
- record.checksum(),
- record.serializedKeySize(),
- record.serializedValueSize(),
- new String(record.key() == null ? "".getBytes(StandardCharsets.UTF_8) : record.key(), StandardCharsets.UTF_8),
- new String(record.value() == null ? "{}".getBytes(StandardCharsets.UTF_8) : record.value(), StandardCharsets.UTF_8));
- }
- }, properties);
- return consumerRecordFlinkKafkaConsumer;
- }
- }
2.3.1.ConfigTools
- @Slf4j
- public class ConfigTools {
- public static Map<String, Object> mapConf;
- /**
- * 获取对应的配置文件
- *
- * @param option
- */
- public static void initConf(String option) {
- String confFile = "/flink_backup_" + option + ".yml";
- try {
- InputStream dumpFile = ConfigTools.class.getResourceAsStream(confFile);
- mapConf = Yaml.loadType(dumpFile, HashMap.class);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- /**
- * 获取对应的配置文件
- *
- * @param option
- */
- public static void initMySqlConf(String option, Class clazz) {
- String className = clazz.getName();
- String confFile = "/appconfig.yml";
- Map<String, String> mysqlConf;
- try {
- InputStream dumpFile = ConfigTools.class.getResourceAsStream(confFile);
- mysqlConf = Yaml.loadType(dumpFile, HashMap.class);
- String username = mysqlConf.get("mysql.username");
- String password = mysqlConf.get("mysql.password");
- String url = mysqlConf.get("mysql.url");
- Connection conn = DBConn.conn(url, username, password);
- Map<String, Object> config = getConfig(conn, className, option);
- if (config == null || config.size() == 0) {
- log.error("获取配置文件失败");
- return;
- }
- mapConf = config;
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- private static Map<String, Object> getConfig(Connection conn, String className, String option) throws SQLException {
- PreparedStatement preparedStatement = null;
- try {
- String sql = "select config_context from app_config where app_name = '%s' and config_name = '%s'";
- preparedStatement = conn.prepareStatement(String.format(sql, className, option));
- ResultSet rs = preparedStatement.executeQuery();
- Map<String, String> map = new LinkedHashMap<>();
- String config_context = "";
- while (rs.next()) {
- config_context = rs.getString("config_context");
- }
- System.out.println("配置信息config_context："+config_context);
- // if(StringUtils.isNotBlank(config_context)){
- // System.out.println(JSONObject.toJSONString(JSONObject.parseObject(config_context), SerializerFeature.PrettyFormat));
- // }
- Map<String, Object> mysqlConfMap = JSON.parseObject(config_context, Map.class);
- return mysqlConfMap;
- }finally {
- if (preparedStatement != null) {
- preparedStatement.close();
- }
- if (conn != null) {
- conn.close();
- }
- }
- }
- public static void main(String[] args) {
- // initMySqlConf("local", TboxPeriodBackoutA3K.class);
- initConf("local");
- String s = JSON.toJSONString(mapConf);
- System.out.println(s);
- }
- }
2.4.1.SchemaPo
- /**
- * 字段属性对象
- */
- @Data
- @AllArgsConstructor
- @NoArgsConstructor
- public class SchemaPo implements Serializable {
- private String signal;
- private String type;
- }
2.5.1.Kafka2Hive-ODS
从 Kafka 中获取到的数据不做任那边置惩罚直接写入到 Hive 的 ODS 层
- public class Kafka2Hive_ODS {
- public static Logger logger = Logger.getLogger(Kafka2Hive_ODS.class);
-
- public static void main(String[] args) throws Exception {
- ConfigTools.initMySqlConf(args[0], AcpBackoutAll_X9E_ORIGINAL.class);
- Map<String, Object> mapConf = ConfigTools.mapConf;
- Map<String, Object> kafkaConsumerConf = (Map<String, Object>) mapConf.get("kafka-consumer");
- Map<String, Object> hiveConf = (Map<String, Object>) mapConf.get("hive");
- StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment().disableOperatorChaining();
- CommonUtils.setCheckpoint(env);
- EnvironmentSettings fsSettings = EnvironmentSettings
- .newInstance()
- .useBlinkPlanner()
- .inStreamingMode()
- .build();
- StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env, fsSettings);
- StreamStatementSet statementSet = tableEnv.createStatementSet();
- // 使用Hive的sql方言
- tableEnv.getConfig().setSqlDialect(SqlDialect.HIVE);
- //自定义一个名字（没有限制随意取）
- String name = "myhive";
- //这里需要配置hive表中的默认库（不是mysql的hive元数据库）
- String defaultDatabase = "ods";
- //hive-site.xml文件目录
- String hiveConfDir = (String) hiveConf.get("hiveConfDir");
- //创建HiveCatalog
- HiveCatalog hive = new HiveCatalog(name, defaultDatabase, hiveConfDir, "3.1.2");
- //注册HiveCatalog
- tableEnv.registerCatalog("myhive", hive);
- //使用HiveCatalog
- tableEnv.useCatalog("myhive");
- // FlinkKafkaConsumer<String> myConsumer = CommonUtils.getKafkaConsumer();
- FlinkKafkaConsumer<ConsumerRecord<String, String>> myConsumer = CommonUtils.getKafkaConsumer(kafkaConsumerConf);
- DataStream<ConsumerRecord<String, String>> stream = env.addSource(myConsumer);
- String tableName = (String) hiveConf.get("tableName");
- List<FieldSchema> schemas = hive.getHiveTable(new ObjectPath(defaultDatabase, tableName)).getSd().getCols();
- List<FieldSchema> partitionKeys = hive.getHiveTable(new ObjectPath(defaultDatabase, tableName)).getPartitionKeys();
- schemas.addAll(partitionKeys);
- List<SchemaPo> schemaPos = new ArrayList<>();
- List<String> fieldLists = new ArrayList<>();
- List<TypeInformation> typeList = new ArrayList<>();
- for (FieldSchema schema : schemas) {
- SchemaPo schemaPo = new SchemaPo();
- schemaPo.setSignal(schema.getName());
- schemaPo.setType(schema.getType());
- schemaPos.add(schemaPo);
- fieldLists.add(schema.getName());
- String type = schema.getType();
- if (type.equalsIgnoreCase("bigint")) {
- typeList.add(Types.LONG);
- } else {
- typeList.add(Types.STRING);
- }
- }
- String[] fieldNames = fieldLists.toArray(new String[fieldLists.size()]);
- TypeInformation[] types = typeList.toArray(new TypeInformation[typeList.size()]);
- SingleOutputStreamOperator<Row> originalRow = stream.flatMap(new KafkaMsgFormatFunction(schemaPos), new RowTypeInfo(types, fieldNames)).uid("ORIGINAL");
- tableEnv.createTemporaryView("originalRow", originalRow);
- StringBuilder sql = new StringBuilder();
- tableEnv.executeSql("alter table table_name set TBLPROPERTIES ('sink.partition-commit.policy.kind'='metastore')");
- sql.append("insert into `table_name` select ");
- sql.append(" * ");
- sql.append(" from `myhive`.`ods`.originalRow");
- tableEnv.executeSql(sql.toString());
- // env.execute();
- }
- static class KafkaMsgFormatFunction extends RichFlatMapFunction<ConsumerRecord<String,String>, Row> {
- private List<SchemaPo> schemaPos;
- public KafkaMsgFormatFunction(List<SchemaPo> schemaPos) {
- this.schemaPos = schemaPos;
- }
- @Override
- public void open(Configuration parameters) {
-
- }
- @Override
- public void flatMap(ConsumerRecord<String,String> record, Collector<Row> out) {
- String key = null;
- try {
- HashMap<String, Object> infoMap = JSON.parseObject((String) record.value(), HashMap.class);
-
- for (String signalkey : infoMap.keySet()) {
- resultMap.put(signalkey.toLowerCase(), String.valueOf(infoMap.get(signalkey)));
- }
- Row row = new Row(schemaPos.size());
- for (int i = 0; i < schemaPos.size(); i++) {
- SchemaPo schemaPo = schemaPos.get(i);
- String v = resultMap.get(schemaPo.getSignal());
- if (StringUtils.isBlank(v)) {
- row.setField(i, null);
- continue;
- }
- if ("bigint".equalsIgnoreCase(schemaPo.getType())) {
- Long svalue = Long.valueOf(resultMap.get(schemaPo.getSignal()));
- row.setField(i, svalue);
- } else {
- String svalue = resultMap.get(schemaPo.getSignal());
- row.setField(i, svalue);
- }
- }
- out.collect(row);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
- }
|
