George Leonard
07/09/2024, 12:26 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,
PRIMARY KEY (INVOICENUMBER) NOT ENFORCED
) WITH (
'connector' = 'upsert-kafka',
'topic' = 'avro_salescompleted_x',
'properties.bootstrap.servers' = 'broker:29092',
'key.format' = 'raw',
'properties.group.id' = 'testGroup',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
populated by
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 a,
avro_salesbaskets b
WHERE a.INVOICENUMBER = b.INVOICENUMBER
AND PAYTIMESTAMP_WM > SALESTIMESTAMP_WM
AND SALESTIMESTAMP_WM > (SALESTIMESTAMP_WM - INTERVAL '1' HOUR);
I now created a output aggregate table
CREATE TABLE avro_sales_per_store_per_terminal_per_5min_x (
store_id STRING,
terminalpoint STRING,
window_start TIMESTAMP(3),
window_end TIMESTAMP(3),
salesperterminal BIGINT,
totalperterminal DOUBLE,
PRIMARY KEY (store_id, terminalpoint, window_start, window_end) NOT ENFORCED
) WITH (
'connector' = 'upsert-kafka',
'topic' = 'avro_sales_per_store_per_terminal_per_5min_x',
'properties.bootstrap.servers' = 'broker:29092',
'key.format' = 'avro-confluent',
'key.avro-confluent.url' = 'http://schema-registry:8081',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
and now want to run the following aggregation,
Insert into avro_sales_per_store_per_terminal_per_5min_x
SELECT
`STORE`.`ID` as STORE_ID,
TERMINALPOINT,
window_start,
window_end,
COUNT(*) as salesperterminal,
SUM(TOTAL) as totalperterminal
FROM TABLE(
TUMBLE(TABLE avro_salescompleted_x, DESCRIPTOR(SALESTIMESTAMP_WM), INTERVAL '5' MINUTES))
GROUP BY `STORE`.`ID`, TERMINALPOINT, window_start, window_end;
RESULTS In Error:
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.ValidationException: The Kafka table 'default_catalog.default_database.avro_sales_per_store_per_terminal_per_5min_x' with 'avro-confluent' format doesn't support defining PRIMARY KEY constraint on the table, because it can't guarantee the semantic of primary key.