Hi Team, We are attempting to perform the followi...
# troubleshooting
m
Hi Team, We are attempting to perform the following logic using Flink's Table API, with the goal of publishing only the latest change: Scenario: 1.Flink Table1 created from CDC DataStream that have multiple rows with composite primary key 2.Select all the rows for a given one of the composite key 3.Apply LISTAGG to get all the rows for a given one of the composite key 4.When one of the existing row gets updated from CDC 5.Emit event with the change to that row and all the remaining rows with LISTAGG Issue that we face now: 1) First Event is emitted without the row that got updated. 2) Second event is emitted with the row that got updated along with the other existing rows for the key Expectation: 1) Emit only the second event that have row that got updated along with the other existing rows for the key More details are given below When there is an update on Table1, we want to publish only that update However, we are observing that two events are being published — one without the actual update and one with it. Behavior Observed: If an ID has only one row, a single event is emitted as expected. If an ID has multiple rows, and only one row is updated (e.g., a row with c1): Flink internally performs the following steps: 1. -U → Removes the row with c1 (old value) 2. +U → Adds an updated intermediate row (e.g., only c2 without c1) 3. Event is published here (incomplete) 4. -U → Removes c2, seemingly clearing the intermediate state 5. +U → Adds both updated rows (c1 and c2) 6. Another event is published (correct, with all data) 7. Ideally, only the final +U should be published, and intermediate steps filtered out.
Copy code
SELECT  
    t1.ID,  
    LISTAGG( 
        CONCAT_WS(
            CHR(31),  -- Unit Separator
            t1.c1, 
            t1.c2
        ), 
        '\u001E'  -- Record Separator
    ) AS t1_concat  
FROM table1 t1  
JOIN table2 t2 ON t1.ID = t2.ID  
GROUP BY t1.ID
Issue: This intermediate emission leads to duplicate events — one partial and one final — whereas only the final consolidated state should be emitted. Question: How can I make sure that I emit only the latest event that have change instead of emitting 1) Event that doesn't have the row that I am updating 2) Event that have the row that i am updating Update on one of the row in Flink Table- Operation AGGLIST, Join and GroupBY we see ROWKIND -U +U -U +U How can I filter for only the last +U and emit event? Please let me know your view Here is the code
Copy code
StreamTableEnvironment streamTableEnvironment = StreamTableEnvironment.create(streamExecutionEnvironment);

        FlinkConfigurationManager flinkConfigurationManager = new      FlinkConfigurationManager(streamExecutionEnvironment);
 // Run the query that provides publishable data
    Table viewsAggregateQueryResult =   streamTableEnvironment.sqlQuery(sqlQuery);
     // Process the result rows
    DataStream<JsonNode> eventDataStream = streamTableEnvironment
                .toChangelogStream(viewsAggregateQueryResult)
                .filter(new UpdateBeforeFilter())
                .uid(PROC_NAME + "-update-before-filter-" + "-v1")
                .name("update before filter:") // filter out -U   Rowkind
                .map(new  RowToJsonNodeMapper(mappings.toString()));


 // Output the data after transformation to 
        eventDataStream
                .map(new EventTransformerMapFunction(
                        configs.getKafkaSchemaHeaders()))
                .keyBy(EventData::getKey)               
                .sinkTo(new KafkaSinkWriter(
                        getOutboundKafkaSink(configs)));

        streamExecutionEnvironment.execute();
d
Flink SQL isn't really designed for this. The results from a query that produces updates are a changelog stream that when applied to a materialized view will ultimately yield the expected result. But it will get there incrementally. A couple of options: • Use a sink that can handle this updating changelog stream, and then query that sink, as needed. E.g., you could materialize the results using a JDBC database sink. Or maybe use the upsert kafka sink with a compacted topic. • Implement a ProcessTableFunction that converts the output of the current query into what you'd rather work with.
m
Appreciate your inputs, thank you
Thank you @David Anderson, I was able to fix this by adding KeyedProcessFunction with onTimer that expire after a sec and add the latest state in statestore to Collector that will be sent to Sink.