George Leonard
07/05/2024, 4:03 PMCREATE 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
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
[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: