only change... originally i consumed data from a t...
# troubleshooting
g
only change... originally i consumed data from a table that was backed by a topic, that table was connector-kafka i now source from avro_salescompleted_x which is populated by a insert and the connector is as required upsert_kafka
d
Remove the primary key constraint
deduplicate downstream.
that’s probably the easiest way
g
if i remove primary key:
Copy code
CREATE TABLE avro_salescompleted_x (
    INVOICENUMBER STRING,
    SALEDATETIME STRING,
    SALETIMESTAMP STRING,
    TERMINALPOINT STRING,
    NETT DOUBLE,
    VAT DOUBLE,
    TOTAL DOUBLE,
    STORE row<ID STRING, NAME STRING>,
    CLERK row<ID STRING, NAME STRING>,
    BASKETITEMS array<row<ID STRING, NAME STRING, BRAND STRING, CATEGORY STRING, PRICE DOUBLE, QUANTITY INT>>,
    FINTRANSACTIONID STRING,
    PAYDATETIME STRING,
    PAYTIMESTAMP STRING,
    PAID DOUBLE,
    SALESTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALETIMESTAMP AS BIGINT) / 1000)),
    PAYTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(PAYTIMESTAMP AS BIGINT) / 1000)),
    WATERMARK FOR SALESTIMESTAMP_WM AS SALESTIMESTAMP_WM
) WITH (
    'connector' = 'kafka',
    'topic' = 'avro_salescompleted_x',
    'properties.bootstrap.servers' = 'broker:29092',
    'properties.group.id' = 'testGroup',
    'value.format' = 'avro-confluent',
    'value.avro-confluent.url' = 'http://schema-registry:8081',
    'value.fields-include' = 'ALL'
);
Insert
Copy code
Insert into avro_salescompleted_x
    SELECT 
        b.INVOICENUMBER,
        b.SALEDATETIME,
        b.SALETIMESTAMP,
        b.TERMINALPOINT,
        b.NETT,
        b.VAT,
        b.TOTAL,
        b.STORE,
        b.CLERK,
        b.BASKETITEMS,        
        a.FINTRANSACTIONID,
        a.PAYDATETIME,
        a.PAYTIMESTAMP,
        a.PAID
    FROM 
        avro_salespayments_x a,
        avro_salesbaskets_x b
    WHERE a.INVOICENUMBER = b.INVOICENUMBER
    AND PAYTIMESTAMP_WM > SALESTIMESTAMP_WM 
    AND SALESTIMESTAMP_WM > (SALESTIMESTAMP_WM - INTERVAL '1' HOUR);
Errors into
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.TableException: Table sink 'default_catalog.default_database.avro_salescompleted_x' doesn't support consuming update and delete changes which is produced by node Join(joinType=[InnerJoin], where=[((INVOICENUMBER = INVOICENUMBER0) AND (PAYTIMESTAMP_WM > SALESTIMESTAMP_WM))], select=[INVOICENUMBER, FINTRANSACTIONID, PAYDATETIME, PAYTIMESTAMP, PAID, PAYTIMESTAMP_WM, INVOICENUMBER0, SALEDATETIME, SALETIMESTAMP, TERMINALPOINT, NETT, VAT, TOTAL, STORE, CLERK, BASKETITEMS, SALESTIMESTAMP_WM], leftInputSpec=[JoinKeyContainsUniqueKey], rightInputSpec=[JoinKeyContainsUniqueKey])
there should never be duplicates as this join should only ever result in one record being inserted into avro_salescomplated_x
still stuck/not understanding why the 2 source virtual tables needed to be upsert's as they are only ever read from.
d
Try declaring the table to be append-only. I think it did not like the removal of primary key but this should change it’s opinion.
g
how do i do append only ?
d
Ok, so its not a specific table DDL declaration but you avoid doing updates or other things that could trigger the need for a primary key. A more flexible approach is to use DataStream API. Actually whenever things get rough with SQL Flink API or Table API the DataStream API is often the last refuge.
So to do append only would be something like this:
Copy code
import org.apache.flink.api.common.functions.MapFunction;
import org.apache.flink.api.java.typeutils.RowTypeInfo;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.connectors.kafka.FlinkKafkaProducer;
import org.apache.flink.streaming.connectors.kafka.KafkaSerializationSchema;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
import org.apache.flink.types.Row;

public class AppendOnlyToKafka {

    public static void main(String[] args) throws Exception {
        final StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);

        // assume you already have DataStreams for avro_salespayments_x and avro_salesbaskets_x
        // assume these are already defined and named as salesPaymentsStream and salesBasketsStream

        // Perform the join operation using DataStream API to have more control
        DataStream<Row> joinedStream = salesPaymentsStream
                .keyBy(row -> row.getFieldAsString("INVOICENUMBER"))
                .intervalJoin(salesBasketsStream.keyBy(row -> row.getFieldAsString("INVOICENUMBER")))
                .between(Time.minutes(-1), Time.minutes(0))
                // Define the join function to merge rows as needed
                .process(new MyJoinFunction());

        // define Kafka Serialization Schema for output
        KafkaSerializationSchema<Row> kafkaSerializationSchema = new KafkaSerializationSchema<Row>() {
            @Override
            public ProducerRecord<byte[], byte[]> serialize(Row element, @Nullable Long timestamp) {
                // Convert Row to byte[] as needed for Kafka message value
                byte[] value = ...; // Implement serialization logic
                return new ProducerRecord<>("avro_salescompleted_x", null, null, value);
            }

            @Override
            public boolean equals(Object obj) {
                return obj instanceof KafkaSerializationSchema;
            }

            @Override
            public int hashCode() {
                return KafkaSerializationSchema.class.getName().hashCode();
            }
        };

        // configure FlinkKafkaProducer to send data to Kafka
        FlinkKafkaProducer<Row> kafkaProducer = new FlinkKafkaProducer<>(
                "avro_salescompleted_x",
                kafkaSerializationSchema,
                // Properties for Kafka connection
                props,
                // Semantic to AT_LEAST_ONCE or EXACTLY_ONCE depending on your requirement
                FlinkKafkaProducer.Semantic.AT_LEAST_ONCE);

        // add the producer to the DataStream to send data to Kafka
        joinedStream.addSink(kafkaProducer);

        env.execute("Append Only Data to Kafka");
    }

    // define your custom join function if needed
    public static class MyJoinFunction extends ProcessJoinFunction<Row, Row, Row> {
        @Override
        public void processElement1(Row left, Context ctx, Collector<Row> out) throws Exception {
            // impl your join logic
        }

        @Override
        public void processElement2(Row right, Context ctx, Collector<Row> out) throws Exception {
            // impl your join logic
        }
    }
}
I am not saying this is the best way. But it’s a way of getting it done. Hopefully others know of a simpler way to resolve the errors you are seeing. But in general DataStream API provides you with total flexibility in processing and sometimes its quicker than wrangling with Table API or SQL API
g
i can guarantee that both the source tables avro_salespayments and avro_salesbaskets only get inserted on, never updates. same avro_salescompleted will only get inserted on when both have matching records based on invoicenumber I've got the ksql version of this working, now trying to do the same, but in flink...