George Leonard
07/04/2024, 5:30 PMCREATE 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
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
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.
{
"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
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;Martijn Visser
07/04/2024, 7:12 PMMartijn Visser
07/04/2024, 7:13 PMGeorge Leonard
07/04/2024, 7:14 PMGeorge Leonard
07/04/2024, 7:15 PMMartijn Visser
07/04/2024, 7:44 PMMartijn Visser
07/04/2024, 7:45 PMMartijn Visser
07/04/2024, 7:45 PMGeorge Leonard
07/05/2024, 5:39 AMGeorge Leonard
07/05/2024, 5:40 AMGeorge Leonard
07/05/2024, 5:46 AM--- 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.
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.
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
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);George Leonard
07/05/2024, 5:53 AMFlink 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 tableGeorge Leonard
07/05/2024, 6:04 AMINSERT 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 :
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Column 'window_start' not found in any tableGeorge Leonard
07/05/2024, 6:08 AMselect
JSON_VALUE(STORE, '$.store.id') AS STORE_ID,
TERMINALPOINT,
TOTAL,
TO_TIMESTAMP(SALETIMESTAMP) AS SALETIMESTAMP
from avro_salescompleted
getting
[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....George Leonard
07/05/2024, 6:13 AMCREATE 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
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);George Leonard
07/05/2024, 6:33 AMselect
JSON_VALUE(STORE, '$.ID') AS STORE_ID
from avro_salescompleted;
and
select
JSON_VALUE(STORE, '$.id') AS STORE_ID
from avro_salescompleted;
still getting null coming back...
below is a select * from avro_salescompleted;Martijn Visser
07/05/2024, 6:47 AMselect `STORE`.`ID`
from avro_salescompleted;George Leonard
07/05/2024, 6:48 AMMartijn Visser
07/05/2024, 6:49 AMGeorge Leonard
07/05/2024, 6:50 AMGeorge Leonard
07/05/2024, 6:50 AMGeorge Leonard
07/05/2024, 6:50 AMMartijn Visser
07/05/2024, 6:56 AMSELECT TO_TIMESTAMP(FROM_UNIXTIME(CAST('1720161150978' AS BIGINT) / 1000)) AS timestamp_value
will return
2024-07-05 08:32:30.000
So in your case
SELECT TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALESTIMESTAMP AS BIGINT) / 1000)) AS timestamp_valueGeorge Leonard
07/05/2024, 6:57 AMGeorge Leonard
07/05/2024, 6:58 AMMartijn Visser
07/05/2024, 6:59 AMGeorge Leonard
07/05/2024, 7:00 AMMartijn Visser
07/05/2024, 7:00 AMGeorge Leonard
07/05/2024, 7:01 AMGeorge Leonard
07/05/2024, 7:05 AMSELECT
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) ;George Leonard
07/05/2024, 7:29 AMCREATE 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'
);Martijn Visser
07/05/2024, 7:53 AMMartijn Visser
07/05/2024, 7:56 AMCREATE 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
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;George Leonard
07/05/2024, 8:50 AMFlink 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'
> );
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Unknown identifier 'WM'George Leonard
07/05/2024, 8:52 AMGeorge Leonard
07/05/2024, 9:05 AMGeorge Leonard
07/05/2024, 9:08 AMGeorge Leonard
07/05/2024, 10:33 AMFlink 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;
[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).George Leonard
07/05/2024, 10:58 AMCREATE 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'
);George Leonard
07/05/2024, 11:01 AMSELECT
`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;George Leonard
07/05/2024, 11:06 AMCREATE 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
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
[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.George Leonard
07/05/2024, 11:14 AMCREATE 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.
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;George Leonard
07/05/2024, 11:15 AM