... created the below table using: ```CREATE TABLE...
# troubleshooting
g
... created the below table using:
Copy code
CREATE TABLE avro_salescompleted_x (
    INVOICENUMBER STRING,
    SALEDATETIME STRING,
    SALETIMESTAMP STRING,
    TERMINALPOINT STRING,
    NETT DOUBLE,
    VAT DOUBLE,
    TOTAL DOUBLE,
    STORE rowID STRING, NAME STRING,
    CLERK rowID STRING, NAME STRING,
    BASKETITEMS array<rowID 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' = 'upsert-kafka',
    'topic' = 'avro_salescompleted_x',
    'properties.bootstrap.servers' = 'broker:29092',
    'key.format' = 'raw',
    'key.fields' = 'INVOICENUMBER',
    'value.format' = 'avro-confluent',
    'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
    'value.fields-include' = 'EXCEPT_KEY'
);
into which I want to insert as per below
Copy code
Insert into avro_salescompleted_x
    SELECT 
        a.INVOICENUMBER,
        a.SALEDATETIME ,
        a.SALETIMESTAMP ,
        a.TERMINALPOINT ,
        a.NETT,
        a.VAT,
        a.TOTAL,
        a.STORE,
        a.CLERK,
        a.BASKETITEMS,
        b.FINTRANSACTIONID,
        b.PAYDATETIME,
        b.PAYTIMESTAMP,
        b.PAID
    FROM 
        avro_salesbaskets a
        LEFT JOIN avro_salespayments b
    ON a.INVOICENUMBER = b.INVOICENUMBER;
as can be seen, this is not syntactically correct yet, think the time scope should be limited, say only execute on data visible in the last 2 hours. Results in
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.ValidationException: Unsupported options found for 'upsert-kafka'.

Unsupported options:

key.fields

Supported options: