Flink入门(十)异步多线程Mysql打宽表，加维度

有这样需求，消息发来是站点名stationName，但与需要和mysql中维度表join站点名的三字码stationCode（唯一标识符）。

flink版本1.6.3，maven配置如下：

   		<dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>25.1-jre</version>
        </dependency>
   		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>

读取kafka消息

        final StreamExecutionEnvironment env = StreamExecutionEnvironment.createLocalEnvironment();
        //链接kafka消息
        String kafkaBrokers=KafkaConfig.KAFKA_BROKER_LIST;
        String topicName="topic-test";
        Properties propsConsumer = new Properties();
        propsConsumer.setProperty("bootstrap.servers",kafkaBrokers) ;
        propsConsumer.setProperty("group.id", "trafficwisdom-streaming");
        propsConsumer.put("enable.auto.commit", false);
        propsConsumer.put("max.poll.records", 1000);
        FlinkKafkaConsumer011<String> consumer = new FlinkKafkaConsumer011<String>(topicName, new SimpleStringSchema(), propsConsumer);
        consumer.setStartFromLatest();
        DataStream<String> stream = env.addSource(consumer)；
        
        DataStream<Tuple2<Tuple3<String, String, String>, String>> filterStream = stream.map(new MapFunction<String, Tuple2<Tuple3<String, String, String>, String>>() {
                    @Override
                    public Tuple2<Tuple3<String, String, String>, String> map(String value) throws Exception {
                    	//格式转换忽略
                        JSONObject jsonObject = JSON.parseObject(value);
                        String fromPlace = jsonObject.getString("FromPlace");
                        String toPlace = jsonObject.getString("ToPlace");
                        String searchData = jsonObject.getString("SelectDate");
                        String response = jsonObject.getString("Response");
                        return Tuple2.of(Tuple3.of(fromPlace, toPlace, searchData), response);
                    }
                });
        filterStream.print();

新建异步链接mysql的类AsyncStationCodeMultiRequest

public class AsyncStationCodeMultiRequest extends RichAsyncFunction<Tuple2<Tuple3<String, String, String>, String>, Tuple2<Tuple3<String, String, String>, String>> {
    private transient DruidDataSource dataSource;
    private transient ListeningExecutorService executorService;
    private transient volatile Cache<String, String> stationNameCache;

    @Override
    public void open(Configuration parameters) throws Exception {
		//初始化多线程
        executorService = MoreExecutors.listeningDecorator(new ThreadPoolExecutor(10,10,60000L,
                TimeUnit.MILLISECONDS,new LinkedBlockingDeque<Runnable>(500)));
        //缓存设置
        stationNameCache = CacheBuilder.<Tuple2<String, String>, String>newBuilder().maximumSize(2000).expireAfterWrite(1, TimeUnit.DAYS) .build();
		//建立druid线程池
        dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUsername("root"); dataSource.setPassword("1111");
        dataSource.setUrl("jdbc:mysql://xxxxx:3306/wisdom");
        dataSource.setInitialSize(5);
        dataSource.setMinIdle(10);
        dataSource.setMaxActive(20);
    }

    @Override
    public void asyncInvoke(Tuple2<Tuple3<String, String, String>, String> input, ResultFuture<Tuple2<Tuple3<String, String, String>, String>> resultFuture) throws Exception {
    	//异步多线程处理
        executorService.submit(() -> {
            try {
                //逻辑处理，省略
                .......
                if(trainTicketNoBOList.size()>0) {
                    trainTicketBO.setData(trainTicketNoBOList);
                    String parseResult = JSON.toJSONString(trainTicketBO);
                    resultFuture.complete(Collections.singleton(Tuple2.of(Tuple3.of(fromStationCode, toStationCode, searchDate), parseResult)));
                }
            } catch (Exception e) {
                logger.error(e.getMessage());
                 resultFuture.complete(Collections.singleton(Tuple2.of(Tuple3.of(null, null, null), null)));
            }
        });
    }

    private List<TrainTicketNoBO> ticketLogChange(String response) throws SQLException {
        TrainInfoList trainInfoList = JSON.parseObject(response, TrainInfoList.class);
      	//省略处理逻辑
      	.....
      	里面调用getStationName(stationName)方法
		......
      	
        return trainTicketNoBOList;
    }
	//先从guava缓存取得站名与之匹配的stationCode，如果没有，从mysql查
    private String getStationName(String stationName) throws SQLException {
        return stationNameCache.getIfPresent(stationName) != null ? stationNameCache.getIfPresent(stationName) : getStationCodeFromSql(stationName);
    }
	//从mysql中查到stationCode，且存入guava缓存
    private String getStationCodeFromSql(String stationName) throws SQLException {
        String sql = "select station_code,station_name from wisdom.station_train_base where station_name='" + stationName + "' limit 1";
        PreparedStatement pstmt;
        String stationCode = null;
        Connection connection = null;
        try {
            connection=dataSource.getConnection();
            pstmt = (PreparedStatement) connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                stationCode = rs.getString("station_code");
            }
        } finally {
            if(connection!=null) {
                connection.close();
            }
        }
        if (stationCode != null) {
            stationNameCache.put(stationName, stationCode);
        }
        return stationCode;
    }

    @Override
    public void close() throws Exception {
        dataSource.close();
        ExecutorUtils.gracefulShutdown(2, TimeUnit.MINUTES, executorService);
    }
}

主流程

       DataStream<Tuple2<Tuple3<String, String, String>, String>> dimTable = AsyncDataStream.unorderedWait(filterStream, new AsyncStationCodeMultiRequest(), 2, TimeUnit.SECONDS, 100)
                .filter(s -> s.f0.f0 != null && s.f0.f1 != null);
        dimTable.print();

dimTable就是打宽表后的数据结果。