Flink JDBC Sink part 2

Question

I have posted a question few days back- Flink Jdbc sink

Now, I am trying to use the sink provided by flink.

I have written the code and it worked as well. But nothing got saved in DB and no exceptions were there. Using previous sink my code was not finishing(that should happen ideally as its a streaming app) but after the following code I am getting no error and the nothing is getting saved to DB.

public class CompetitorPipeline implements Pipeline {
    private final StreamExecutionEnvironment streamEnv;
    private final ParameterTool parameter;
   
    private static final Logger LOG = LoggerFactory.getLogger(CompetitorPipeline.class);

    public CompetitorPipeline(StreamExecutionEnvironment streamEnv, ParameterTool parameter) {
        this.streamEnv = streamEnv;
        this.parameter = parameter;
    }

    @Override
    public KeyedStream<CompetitorConfig, String> start(ParameterTool parameter) throws Exception {
        CompetitorConfigChanges competitorConfigChanges = new CompetitorConfigChanges();
        KeyedStream<CompetitorConfig, String> competitorChangesStream = competitorConfigChanges.run(streamEnv, parameter);
        

        //Add to JBDC Sink
        competitorChangesStream.addSink(JdbcSink.sink(
                "insert into competitor_config_universe(marketplace_id,merchant_id, competitor_name, comp_gl_product_group_desc," +
                        "category_code, competitor_type, namespace, qualifier, matching_type," +
                        "zip_region, zip_code, competitor_state, version_time, compConfigTombstoned, last_updated) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                (ps, t) -> {
                    ps.setInt(1, t.getMarketplaceId());
                    ps.setLong(2, t.getMerchantId());
                    ps.setString(3, t.getCompetitorName());
                    ps.setString(4, t.getCompGlProductGroupDesc());
                    ps.setString(5, t.getCategoryCode());
                    ps.setString(6, t.getCompetitorType());
                    ps.setString(7, t.getNamespace());
                    ps.setString(8, t.getQualifier());
                    ps.setString(9, t.getMatchingType());
                    ps.setString(10, t.getZipRegion());
                    ps.setString(11, t.getZipCode());
                    ps.setString(12, t.getCompetitorState());
                    ps.setTimestamp(13, Timestamp.valueOf(t.getVersionTime()));
                    ps.setBoolean(14, t.isCompConfigTombstoned());
                    ps.setTimestamp(15, new Timestamp(System.currentTimeMillis()));
                    System.out.println("sql"+ps);
                },
                new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
                        .withUrl("jdbc:mysql://127.0.0.1:3306/database")
                        .withDriverName("com.mysql.cj.jdbc.Driver")
                        .withUsername("xyz")
                        .withPassword("xyz@")
                        .build()));
        return competitorChangesStream;
    }
}

Do you have checkpointing enabled? I believe that may be necessary. — David Anderson, Mar 06 '21 at 12:56
I suspect the problem is in the code you haven't shared with us. — David Anderson, Mar 06 '21 at 19:41

score 1 · Answer 1 · answered May 17 '21 at 11:33

You need enable autocommit mode for jdbc Sink.

new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
                        .withUrl("jdbc:mysql://127.0.0.1:3306/database;autocommit=true")

It looks like SimpleBatchStatementExecutor only works in auto-commit mode. And if you need to commit and rollback batches, then you have to write your own ** JdbcBatchStatementExecutor **

score 1 · Answer 2 · answered Jun 12 '21 at 14:54

Have you tried to include the JdbcExecutionOptions ?

dataStream.addSink(JdbcSink.sink(
            sql_statement,
            (statement, value) -> {
                /* Prepared Statement */
            }, 
            JdbcExecutionOptions.builder()
                    .withBatchSize(5000)
                    .withBatchIntervalMs(200)
                    .withMaxRetries(2)
                    .build(),
            new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
                    .withUrl("jdbc:mysql://127.0.0.1:3306/database")
                    .withDriverName("com.mysql.cj.jdbc.Driver")
                    .withUsername("xyz")
                    .withPassword("xyz@")
                    .build()));

Flink JDBC Sink part 2

2 Answers2