hi hi all, need some assistance... I have a topic...
# troubleshooting
g
hi hi all, need some assistance... I have a topic called pb_salescompleted which is Pb serialised -- Source topic is Pb serialized -- key is based the invnumber (as it was used t join salesbaskets and salespayments) -- See right at bottom for example payload. this is my thinking... needless to say it's not working, getting buckets of red thrown at my when as a start i try and select from pb_salescompleted.
Copy code
CREATE TABLE pb_salescompleted (
    INVNUMBER STRING,
    SALEDATETIME STRING,
    SALETIMESTAMP TIMESTAMP,
    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
) WITH (
    'connector' = 'kafka',
    'topic' = 'pb_salescompleted',
    'properties.bootstrap.servers' = 'broker:29092',
    'scan.startup.mode' = 'earliest-offset',
    'key.format' = 'raw',
    'key.fields' = 'store_id',
    'format' = 'protobuf'
);
Output table, backed by topic, will be sinked to MongoDB via Kafka Connect
Copy code
CREATE TABLE avro_salesperstoreperterminalper5min (
    store_id STRING,
    terminalpoint STRING,
    window_start TIMESTAMP,
    window_end TIMESTAMP,
    salesperterminal INT,
    totalperterminal BIGINT
) WITH (
    'connector' = 'upsert-kafka',
    'topic' = 'salesperstoreperterminalper5min',
    'properties.bootstrap.servers' = 'broker:29092',
    'key.format' = 'raw',
    'value.format' = 'avro-confluent',
    'value.avro-confluent.url' = 'http://schema-registry:8081',
    'value.fields-include' = 'EXCEPT_KEY'
);
Worker/aggregator
Copy code
INSERT INTO salesperstoreperterminalper5min
SELECT 
  store_id, 
  terminalpoint,
  window_start, 
  window_end,
  COUNT(*) AS salesperterminal,
  SUM(TOTAL) AS totalperterminal
FROM pb_salescompleted
GROUP BY store_id, terminalpoint, 
TUMBLE(SALETIMESTAMP, INTERVAL '5' MINUTE)
Original Payload of salescompleted.
Copy code
{
  "INVNUMBER": "bf1b7564-946d-4694-a8a3-c66948a25788",
  "SALEDATETIME": "2024-07-03T08:56:20.009+02:00",
  "SALETIMESTAMP": "1719989780009",
  "TERMINALPOINT": "16",
  "NETT": 1428.05,
  "VAT": 199.93,
  "TOTAL": 1627.98,
  "STORE": {
    "ID": "324213422",
    "NAME": "PineTown"
  },
  "CLERK": {
    "ID": "10002",
    "NAME": "Greg"
  },
  "BASKETITEMS": [
    {
      "ID": "000000041",
      "NAME": "Castle Lite NRB 24 x 330ml",
      "BRAND": "Castle",
      "CATEGORY": "Beverage",
      "PRICE": 269.99,
      "QUANTITY": 4
    },
    {
      "ID": "000000013",
      "NAME": "Sunlight Dishwashing Liquid",
      "BRAND": "Sunlight",
      "CATEGORY": "Cleaning",
      "PRICE": 2.2,
      "QUANTITY": 3
    }
  ],
  "FINTRANSACTIONID": "46146e77-9d8a-467b-97fb-cb751afe226a",
  "PAYDATETIME": "2024-07-03T09:01:18.009+02:00",
  "PAYTIMESTAMP": "1719990078009",
  "PAID": 1627.98
}
[5:19 PM] George salescompleted was created using
Copy code
CREATE STREAM pb_salescompleted WITH (KAFKA_TOPIC='pb_salescompleted',
       VALUE_FORMAT='ProtoBuf',
       PARTITIONS=1)
       as  
select 
	b.InvoiceNumber InvNumber, 
	b.SaleDateTime,
	b.SaleTimestamp, 
	b.TerminalPoint,
	b.Nett,
	b.Vat,
	b.Total,
	b.store,
	b.clerk,
	b.BasketItems,
	p.FinTransactionId,
	p.PayDateTime,
	p.PayTimestamp,
	p.Paid
from 
	pb_salespayments p INNER JOIN
	pb_salesbaskets b
WITHIN 7 DAYS 
on b.InvoiceNumber = p.InvoiceNumber
emit changes;
m
The TUMBLE isn’t valid Flink SQL syntax. See https://nightlies.apache.org/flink/flink-docs-release-1.19/docs/dev/table/sql/queries/window-tvf/ for the documentation
And the last content looks like ksqlDB
g
I"m not even getting to the tumble, the first create table is failing on the deserialize. It's creating the table, but erroring out when I select from it. yes the last is ksql as a example of how pb_salescompleted was created.
switched to trying to get this logic to work using json_salescompleted (as a temporary work around I'm able to create a json serialized version of the join).
m
Are you trying to use Protobuf in combination with Schema Registry?
Because at this moment in Apache Flink, you need to compile the protobuf code and add them to the classpath
g
... ok, hmmm, json is to slow i've found, will switch the salescompleted to a avro format then, and use that into flink.
just surprised... with everyone saying json is slow, and i found it also, but it's also the native format from document stores... then the move of the binary formats like avro and Pb surprised that Pb not natively included.
Created the following avro source table then... in Kafka and also the matching flink table.
Copy code
--- Kafka topic
CREATE OR REPLACE STREAM avro_salescompleted WITH (KAFKA_TOPIC='avro_salescompleted',
       VALUE_FORMAT='avro',
       PARTITIONS=1)
       as  
select 
	b.InvoiceNumber InvNumber, 
	b.SaleDateTime,
	b.SaleTimestamp, 
	b.TerminalPoint,
	b.Nett,
	b.Vat,
	b.Total,
	b.store,
	b.clerk,
	b.BasketItems,
	p.FinTransactionId,
	p.PayDateTime,
	p.PayTimestamp,
	p.Paid  
from 
	pb_salespayments p INNER JOIN
	pb_salesbaskets b
WITHIN 7 DAYS 
on b.InvoiceNumber = p.InvoiceNumber
emit changes;
please advise how do I access store.id to group by.
Copy code
CREATE TABLE avro_salescompleted (
    INVNUMBER 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
) WITH (
 'connector' = 'kafka',
 'topic' = 'avro_salescompleted',
 'properties.bootstrap.servers' = 'broker:29092',
 'scan.startup.mode' = 'earliest-offset',
 'properties.group.id' = 'testGroup',
 'format' = 'avro-confluent'
);
-- was thinking of creating output table like this.
Copy code
CREATE TABLE avro_salesperstoreperterminalper5min (
    store_id STRING,
    terminalpoint STRING,
    window_start TIMESTAMP,
    window_end TIMESTAMP,
    salesperterminal INT,
    totalperterminal BIGINT,
    PRIMARY KEY (store_id, terminalpoint) NOT ENFORCED
) WITH (
    'connector' = 'upsert-kafka',
    'topic' = 'salesperstoreperterminalper5min',
    'properties.bootstrap.servers' = 'broker:29092',
    'key.format' = 'raw',
    'value.format' = 'avro-confluent',
    'value.avro-confluent.url' = 'http://schema-registry:8081',
    'value.fields-include' = 'EXCEPT_KEY'
);
and then the insert into output as
Copy code
INSERT INTO salesperstoreperterminalper5min
SELECT 
  store_id,
  terminalpoint,
  window_start, 
  window_end,
  COUNT(*) AS salesperterminal,
  SUM(TOTAL) AS totalperterminal
FROM (
    select 
        JSON_VALUE(STORE, '$.store.id') AS `store_id`,
        TERMINALPOINT as terminalpoint,
        TOTAL,
        SALETIMESTAMP
    from avro_salescompleted
) 
GROUP BY store_id, terminalpoint, 
TUMBLE(SALETIMESTAMP, INTERVAL '5' MINUTE);
... wondering, my kafka topic has the SALETIMESTAMP as a epoc based string, thinking I'm going to have a probably cast that in the sub select into a date type for the window'ing to work, -- error
Copy code
Flink SQL> SELECT
>   store_id,
>   terminalpoint,
>   window_start,
>   window_end,
>   COUNT(*) AS salesperterminal,
>   SUM(TOTAL) AS totalperterminal
> FROM (
>     select
>         JSON_VALUE(STORE, '$.store.id') AS `store_id`,
>         TERMINALPOINT as terminalpoint,
>         TOTAL,
>         SALETIMESTAMP
>     from avro_salescompleted
> )
> GROUP BY store_id, terminalpoint,
> TUMBLE(SALETIMESTAMP, INTERVAL '5' MINUTE);
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Column 'window_start' not found in any table
modified my aggregate select as follows.
Copy code
INSERT INTO salesperstoreperterminalper5min
SELECT 
  STORE_ID,
  TERMINALPOINT,
  window_start, 
  window_end,
  COUNT(*) AS salesperterminal,
  SUM(TOTAL) AS totalperterminal
FROM (
    select 
        JSON_VALUE(STORE, '$.store.id') AS STORE_ID,
        TERMINALPOINT,
        TOTAL,
        TO_TIMESTAMP(SALETIMESTAMP) AS SALETIMESTAMP
    from avro_salescompleted
) 
GROUP BY STORE_ID, TERMINALPOINT, 
TUMBLE(SALETIMESTAMP, INTERVAL '5' MINUTE);
Getting :
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Column 'window_start' not found in any table
figured lets try the base select first, work up from there.
Copy code
select 
        JSON_VALUE(STORE, '$.store.id') AS STORE_ID,
        TERMINALPOINT,
        TOTAL,
        TO_TIMESTAMP(SALETIMESTAMP) AS SALETIMESTAMP
    from avro_salescompleted
getting
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.ValidationException: One or more required options are missing.

Missing required options are:

url
confused....
ok, first bit resolved... needed to specify schema registry, that last little 3 letters url was the hint. source flink table created updated to
Copy code
CREATE TABLE avro_salescompleted (
    INVNUMBER 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
) WITH (
 'connector' = 'kafka',
 'topic' = 'avro_salescompleted',
 'properties.bootstrap.servers' = 'broker:29092',
 'scan.startup.mode' = 'earliest-offset',
 'properties.group.id' = 'testGroup',
  'value.format' = 'avro-confluent',
  'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
  'value.fields-include' = 'EXCEPT_KEY'
);
this now returns records, except.... its not including the store_id number... null. if i now execute below it's complaining can't find window_start
Copy code
SELECT 
  STORE_ID,
  TERMINALPOINT,
  window_start, 
  window_end,
  COUNT(*) AS salesperterminal,
  SUM(TOTAL) AS totalperterminal
FROM (
    select 
        JSON_VALUE(STORE, '$.store.id') AS STORE_ID,
        TERMINALPOINT,
        TOTAL,
        TO_TIMESTAMP(SALETIMESTAMP) AS SALETIMESTAMP
    from avro_salescompleted
) 
GROUP BY STORE_ID, TERMINALPOINT, 
TUMBLE(SALETIMESTAMP, INTERVAL '5' MINUTE);
tried
Copy code
select 
        JSON_VALUE(STORE, '$.ID') AS STORE_ID
from avro_salescompleted;
and
Copy code
select 
        JSON_VALUE(STORE, '$.id') AS STORE_ID
from avro_salescompleted;
still getting null coming back... below is a select * from avro_salescompleted;
m
STORE doesn't contain a JSON string, but it's a ROW data type. So you'll need to use:
Copy code
select `STORE`.`ID` 
from avro_salescompleted;
g
ok, will try... how do i make it that it is a json value ?
m
You would basically post the JSON data as a STRING in your input topic
g
then... that "dammm" SALETIMESTAMP is a string of the epoc seconds... for life of me how do i convert that to a proper time stamp...
i've tried TO_TIMESTAMP_LTZ(cast(SALETIMESTAMP as bigint), 0)
and many more 😉
m
Copy code
SELECT TO_TIMESTAMP(FROM_UNIXTIME(CAST('1720161150978' AS BIGINT) / 1000)) AS timestamp_value
will return
Copy code
2024-07-05 08:32:30.000
So in your case
Copy code
SELECT TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALESTIMESTAMP AS BIGINT) / 1000)) AS timestamp_value
g
this returns data. select cast(SALETIMESTAMP as bigint) from avro_salescompleted; but when i then t_timestamp it fails.
returns null
m
Have you tried mine?
g
sorry, seems slack was slow in updating screen, did notsee, trying...
m
No worries
g
sweet... making progress... ok, now to assemble and get it to create tumbling window.
any chance you can tell me whats wrong here lastly...
Copy code
SELECT 
  STORE_ID,
  TERMINALPOINT,
  WINDOW_START, 
  WINDOW_END,
  COUNT(*) AS salesperterminal,
  SUM(TOTAL) AS totalperterminal
FROM (
    select 
        `STORE`.`ID` AS STORE_ID,
        TERMINALPOINT,
        TOTAL,
     TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALETIMESTAMP AS BIGINT) / 1000)) AS SALETIMESTAMP
    from avro_salescompleted  )
GROUP BY STORE_ID, TERMINALPOINT, 
TUMBLE(SALETIMESTAMP, INTERVAL '5' MINUTE) ;
popping into meeting, will spin back to this in about 90min. the idea of the above select is to output the resuilts into a table created using below.
Copy code
CREATE TABLE avro_salesperstoreperterminalper5min (
    store_id STRING,
    terminalpoint STRING,
    window_start TIMESTAMP,
    window_end TIMESTAMP,
    salesperterminal INT,
    totalperterminal BIGINT,
    PRIMARY KEY (store_id, terminalpoint) NOT ENFORCED
) WITH (
    'connector' = 'upsert-kafka',
    'topic' = 'salesperstoreperterminalper5min',
    'properties.bootstrap.servers' = 'broker:29092',
    'key.format' = 'raw',
    'value.format' = 'avro-confluent',
    'value.avro-confluent.url' = 'http://schema-registry:8081',
    'value.fields-include' = 'EXCEPT_KEY'
);
m
It's a wrong tumbling window syntax, and you'll need to make sure that you're adding a watermark strategy based on the timestamp that you're establishing
E.g.
Copy code
CREATE TABLE avro_salescompleted (
    INVNUMBER 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(SALESTIMESTAMP AS BIGINT) / 1000)),
    WATERMARK FOR SALESTIMESTAMP_WM AS WM
) WITH (
 'connector' = 'kafka',
 'topic' = 'avro_salescompleted',
 'properties.bootstrap.servers' = 'broker:29092',
 'scan.startup.mode' = 'earliest-offset',
 'properties.group.id' = 'testGroup',
  'value.format' = 'avro-confluent',
  'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
  'value.fields-include' = 'EXCEPT_KEY'
);
And then
Copy code
SELECT 
    STORE_ID,
    TERMINALPOINT,
    window_start, 
    window_end, 
    COUNT(*) as salesperterminal,
    SUM(TOTAL) as totalperterminal
  FROM TABLE(
    TUMBLE(TABLE avro_salescompleted, DESCRIPTOR(SALESTIMESTAMP_WM), INTERVAL '5' MINUTES))
  GROUP BY STORE_ID, TERMINALPOINT, window_start, window_end;
g
....
Copy code
Flink SQL> CREATE TABLE avro_salescompleted (
>     INVNUMBER 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)),
>     WATERMARK FOR SALESTIMESTAMP_WM AS WM
> ) WITH (
>  'connector' = 'kafka',
>  'topic' = 'avro_salescompleted',
>  'properties.bootstrap.servers' = 'broker:29092',
>  'scan.startup.mode' = 'earliest-offset',
>  'properties.group.id' = 'testGroup',
>   'value.format' = 'avro-confluent',
>   'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
>   'value.fields-include' = 'EXCEPT_KEY'
> );
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Unknown identifier 'WM'
... i see you casting that string into a date inside the source table... looking at code i can see it's so that we have a date to work with for watermark
... other than the above error... can we spin back/side track to the below statement... the original message/payload was build in a golang app as a json structure, then posted onto kafka using Protobuf serialization. I've done the exact same in a Golang app that serializes it as a json string 10x slower though). with us not always having control of the inbound stream, trying to understand how to take that kafka string/payload shaped as json and work with it... considering it's likely going to end in a document store.
... at the moment for this version of these tests/how to's i've create salescompleted on the kafka cluster as a join of 2 topics... the next "phase" is to rather access those 2 topics from flink and do the build of the salescompleted here. after which the above aggregations are then done, and pushed back onto the kafka cluster for consumption.
i removed the references to WM... got table build, if i then run the below select i get the err (Need to understand what the WM is for and why it's failing... big gaps in knowdledge 😉 )
Copy code
Flink SQL> SELECT
>     `STORE`.`ID` as STORE_ID,
>     TERMINALPOINT,
>     window_start,
>     window_end,
>     COUNT(*) as salesperterminal,
>     SUM(TOTAL) as totalperterminal
>   FROM TABLE(
>     TUMBLE(TABLE avro_salescompleted, DESCRIPTOR(SALESTIMESTAMP_WM), INTERVAL '5' MINUTES))
>   GROUP BY `STORE`.`ID`, TERMINALPOINT, window_start, window_end;
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.ValidationException: The window function requires the timecol is a time attribute type, but is TIMESTAMP(3).
got the table created with the watermark using the salestimestamp_wm name as per below, it did not like the alising into "wn"
Copy code
CREATE TABLE avro_salescompleted (
    INVNUMBER 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)),
    WATERMARK FOR SALESTIMESTAMP_WM AS SALESTIMESTAMP_WM
) WITH (
    'connector' = 'kafka',
    'topic' = 'avro_salescompleted',
    'properties.bootstrap.servers' = 'broker:29092',
    'scan.startup.mode' = 'earliest-offset',
    'properties.group.id' = 'testGroup',
    'value.format' = 'avro-confluent',
    'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
    'value.fields-include' = 'EXCEPT_KEY'
);
and for the aggregation, needed to change how I referenced the store.id to below.
Copy code
SELECT 
    `STORE`.`ID` as STORE_ID,
    TERMINALPOINT,
    window_start, 
    window_end, 
    COUNT(*) as salesperterminal,
    SUM(TOTAL) as totalperterminal
  FROM TABLE(
    TUMBLE(TABLE avro_salescompleted, DESCRIPTOR(SALESTIMESTAMP_WM), INTERVAL '5' MINUTES))
  GROUP BY `STORE`.`ID`, TERMINALPOINT, window_start, window_end;
making progress... needed to change the output table create
Copy code
CREATE TABLE avro_salesperstoreperterminalper5min (
    store_id STRING,
    terminalpoint STRING,
    window_start TIMESTAMP,
    window_end TIMESTAMP,
    salesperterminal BIGINT,
    totalperterminal DOUBLE,
    PRIMARY KEY (store_id, terminalpoint, window_start, window_end) NOT ENFORCED
) WITH (
    'connector' = 'upsert-kafka',
    'topic' = 'salesperstoreperterminalper5min',
    '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' = 'EXCEPT_KEY'
);
But now when I run the below insert
Copy code
Insert into avro_salesperstoreperterminalper5min
SELECT 
    `STORE`.`ID` as STORE_ID,
    TERMINALPOINT,
    window_start, 
    window_end, 
    COUNT(*) as salesperterminal,
    SUM(TOTAL) as totalperterminal
  FROM TABLE(
    TUMBLE(TABLE avro_salescompleted, DESCRIPTOR(SALESTIMESTAMP_WM), INTERVAL '5' MINUTES))
  GROUP BY `STORE`.`ID`, TERMINALPOINT, window_start, window_end;
I end with
Copy code
[ERROR] Could not execute SQL statement. Reason:
java.lang.IllegalArgumentException: Avro does not support TIMESTAMP type with precision: 6, it only supports precision less than 3.
awesome, resolved... change my create table to:
Copy code
CREATE TABLE avro_salesperstoreperterminalper5min (
    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' = 'salesperstoreperterminalper5min',
    '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' = 'EXCEPT_KEY'
);
then this works.
Copy code
Insert into avro_salesperstoreperterminalper5min
SELECT 
    `STORE`.`ID` as STORE_ID,
    TERMINALPOINT,
    window_start,
    window_end,
    COUNT(*) as salesperterminal,
    SUM(TOTAL) as totalperterminal
  FROM TABLE(
    TUMBLE(TABLE avro_salescompleted, DESCRIPTOR(SALESTIMESTAMP_WM), INTERVAL '5' MINUTES))
  GROUP BY `STORE`.`ID`, TERMINALPOINT, window_start, window_end;
QUESTION though, how do I get my store_id, terminalpoint, window_start and window_end into the payload itself, it's only in the key atm.