wondr if anyone can point me in problem... table ...
# troubleshooting
g
wondr if anyone can point me in problem... table created
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,
    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
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 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
Copy code
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,
Copy code
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:
Copy code
[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.