基于MRS-Flink组件开发问题：JdbcSink操作GaussDB(DWS)数据库报错No Route to Host

最新推荐文章于 2025-01-22 23:40:35 发布

原创最新推荐文章于 2025-01-22 23:40:35 发布 · 547 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#flink #大数据 #华为云

业务需用华为云平台FusionInsight Manager的Flink组件，实现源端数据向DWS数据库实时同步。在MRS工程加业务代码后启动程序报错，经测试排除数据库连接和业务逻辑问题，最终定位是数据库Url的IP问题，将其改为云端IP后程序正常运行。

业务需求：应用华为云平台FusionInsight Manager中的Flink组件配合业务程序实现源端数据向DWS数据库的实时同步。

在MRS二次开发样例工程的基础上加入自己的业务代码：

public class ReadFromKafka {

    public static void main(String[] args) throws Exception {
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        ParameterTool paraTool = ParameterTool.fromArgs(args);
        Properties properties = paraTool.getProperties();

        DataStream<String> messageStream =
                env.addSource(
                        new FlinkKafkaConsumer<>(
                                paraTool.get("topic"), new SimpleStringSchema(), properties));

        SingleOutputStreamOperator<String> operator = messageStream.
                rebalance()
                .map(
                        new MapFunction<String, String>() {
                            @Override
                            public String map(String s) throws Exception {
                                return "Flink says => " + s + System.getProperty("line.separator");
                            }
                        });

        operator.addSink(JdbcSink.
                sink("insert into test.test_fff(fff)  values (?)",
                        (preparedStatement, s) -> {
                            preparedStatement.setString(1, s);
                        },
                        JdbcExecutionOptions.builder()
                                .withBatchSize(10)
                                .withBatchIntervalMs(1000)
                                .withMaxRetries(3)
                                .build(),
                        new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
                                .withDriverName("org.postgresql.Driver") 
                                .withUrl("jdbc:postgresql://xx.xxx.xxx.xxx:xxxx/GS_DW")// 数据库Url地址
                                .withUsername("username")
                                .withPassword("password")
                                .build()
                ));

        env.execute();
    }
}

加入需求业务后启动程序，消息发送程序实时发送编辑好的Json字符串，启动ReakFromKafka接收并消费消息。此时发生报错“java.net.NoRouteToHostException: No route to host (Host unreachable)”,程序终止。报错信息：Caused by: java.net.NoRouteToHostException: No route to host (Host unreachable)

数据库连接经过验证没有问题。（将数据库写入代码从addSink方法中取出，直接在main方法中执行入库正常，测试代码及数据库结果如下）

public class ReadFromKafka3 {

    public static void main(String[] args) throws Exception {
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        ParameterTool paraTool = ParameterTool.fromArgs(args);
        Properties properties = paraTool.getProperties();

        DataStream<String> messageStream =
                env.addSource(
                        new FlinkKafkaConsumer<>(
                                paraTool.get("topic"), new SimpleStringSchema(), properties));


        tryInsert(new Worker(1, "jjj", "123@123.com", 22, new BigDecimal("553.212")));

        SingleOutputStreamOperator<String> operator = messageStream.
                rebalance()
                .map(
                        new MapFunction<String, String>() {
                            @Override
                            public String map(String s) throws Exception {
                                return "Flink says => " + s + System.getProperty("line.separator");
                            }
                        });

        env.execute();
    }


    public static void tryInsert(Worker worker) {
        String sql = "insert into test.worker(id, name, email, age, salary) values (? ,? ,? ,? ,? )";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn =DriverManager.getConnection("jdbc:postgresql://xx.xxx.xxx.xxx:xxxx/GS_DW",
                    "username", "password");
            ps = conn.prepareStatement(sql);

            ps.setInt(1, worker.getInteger("id"));
            ps.setString(2, worker.getString("name"));
            ps.setString(3, worker.getString("email"));
            ps.setInt(4, worker.getInteger("age"));
            ps.setBigDecimal(5, worker.getBigDecimal("salary"));

            ps.executeUpdate();
            System.out.println(" insert finished! ");

        } catch (Exception e) {
            System.out.println(" insert error : " + e.toString() + " , " + e.getCause());
            System.out.println("e.getMessage() = " + e.getMessage() + e.getLocalizedMessage());
            e.printStackTrace();
        } finally {
            DruidUtil.closePsConn(ps, conn);
        }
    }
}

操作结果：