George Leonard
07/12/2024, 3:33 PM{
"invoiceNumber": "db72a290-c668-4ca5-9c19-aa0260ce9709",
"saleDateTime": "2024-06-19T08:23:58.111+02:00",
"saleTimestamp": "1718778238111",
"store": {
"id": "224213412",
"name": "Durbanville"
},
"clerk": {
"id": "10018",
"name": "Max"
},
"terminalPoint": "2",
"basketItems": [
{
"id": "000000040",
"name": "Tastic Rice 2kg",
"brand": "Tastic",
"category": "Food Cupboard",
"price": 41.99,
"quantity": 4
},
{
"id": "000000018",
"name": "McD Big Mac",
"brand": "McD",
"category": "Food Cupboard",
"price": 5.49,
"quantity": 3
},
{
"id": "000000046",
"name": "HTH Granular Mineral Soft 8kg",
"brand": "HTH",
"category": "Pool Care",
"price": 439.99,
"quantity": 1
},
{
"id": "000000028",
"name": "PnP Sunflower Oil 2L",
"brand": "PnP",
"category": "Food Cupboard",
"price": 79.99,
"quantity": 1
},
{
"id": "000000023",
"name": "Brookes Oros 2L",
"brand": "Brooks",
"category": "Beverage",
"price": 44.99,
"quantity": 1
}
],
"nett": 749.4,
"vat": 104.92,
"total": 854.32
}
I want to do something like the following.
counter the items per brand per store. and value per brand per store, tumbling over 5 min.
the below is not working as it is thinking BASKETITEMS is a table... pretty sure it's more because it's an array.
SELECT
`STORE`.`ID` as STORE_ID,
`BASKETITEMS`.`BRAND` as BRAND,
window_start,
window_end,
COUNT(*) as SALESPERBRAND,
SUM(`BASKETITEMS`.`Price`) as TOTALPERBRAND
FROM TABLE(
TUMBLE(TABLE avro_salescompleted_x, DESCRIPTOR(SALESTIMESTAMP_WM), INTERVAL '5' MINUTES))
GROUP BY `STORE`.`ID`, `BASKETITEMS`.`BRAND`, window_start, window_end;D. Draco O'Brien
07/13/2024, 10:31 AMGeorge Leonard
07/13/2024, 10:34 AMD. Draco O'Brien
07/13/2024, 10:38 AMUNNEST('BASKETITEMS) as bitems
You might need a subquery for it tooD. Draco O'Brien
07/13/2024, 10:39 AMGeorge Leonard
07/13/2024, 10:45 AMD. Draco O'Brien
07/13/2024, 10:53 AMSELECT
s.STORE_ID,
bi.BRAND,
window_start,
window_end,
COUNT(*) as SALESPERBRAND,
SUM(bi.PRICE * bi.QUANTITY) as TOTALPERBRAND
FROM (
SELECT
`STORE`.`ID` AS STORE_ID,
UNNEST(`BASKETITEMS`) AS bi -- unnest the basket items
FROM TABLE(
TUMBLE(TABLE avro_salescompleted_x, DESCRIPTOR(SALESTIMESTAMP_WM), INTERVAL '5' MINUTES)
) -- apply the tumble window before unnesting
) AS expanded_sales
GROUP BY
s.STORE_ID,
bi.BRAND,
window_start,
window_end;D. Draco O'Brien
07/13/2024, 10:53 AMGeorge Leonard
07/13/2024, 10:56 AMD. Draco O'Brien
07/13/2024, 10:57 AMGeorge Leonard
07/13/2024, 10:58 AMD. Draco O'Brien
07/13/2024, 10:59 AMWITH unnested_sales AS (
SELECT
`STORE`.`ID` AS STORE_ID,
bi.`BRAND` AS BRAND,
bi.`PRICE` * bi.`QUANTITY` AS ITEM_VALUE, -- calculate item value
bi.`SALETIMESTAMP` -- assuming each basket item has a timestamp reflecting the sale time
FROM TABLE(avro_salescompleted_x) -- reference your original table
CROSS JOIN UNNEST(`BASKETITEMS`) AS bi -- unnest the basket items
)
SELECT
STORE_ID,
BRAND,
window_start,
window_end,
COUNT(*) AS SALESPERBRAND,
SUM(ITEM_VALUE) AS TOTALPERBRAND
FROM TABLE(
TUMBLE(unnested_sales, DESCRIPTOR(SALETIMESTAMP), INTERVAL '5' MINUTES)
) -- apply tumbling window after unnesting
GROUP BY
STORE_ID,
BRAND,
window_start,
window_end;D. Draco O'Brien
07/13/2024, 10:59 AMD. Draco O'Brien
07/13/2024, 11:06 AMGeorge Leonard
07/13/2024, 11:07 AMGeorge Leonard
07/15/2024, 11:25 AMWITH unnested_sales AS (
SELECT
`store`.`id` as `store_id`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `itemValue`, -- calculate item value
bi.`saleDateTime_Ltz` -- assuming each basket item has a timestamp reflecting the sale time
FROM TABLE (avro_salescompleted_x) -- reference your original table
CROSS JOIN UNNEST(`basketItems`) AS bi -- unnest the basket items
)
SELECT
store_id,
brand,
window_start,
window_end,
COUNT(*) AS `salesperbrand`,
SUM(itemValue) AS `totalperbrand`
FROM TABLE(
TUMBLE(unnested_sales, DESCRIPTOR(saleDateTime_Ltz), INTERVAL '5' MINUTES)
) -- apply tumbling window after unnesting
GROUP BY
store_id,
brand,
window_start,
window_end;
question, does this create a output table? or is this a unraveling select ?
as it is above it's err:
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.sql.parser.impl.ParseException: Encountered ")" at line 7, column 38.
Was expecting one of:
"(" ...
"." ...D. Draco O'Brien
07/15/2024, 11:56 AMWITH unnested_sales AS (
SELECT
`store`.`id` as `store_id`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `itemValue`,
bi.`saleDateTime_Ltz`
FROM TABLE(avro_salescompleted_x()) -- assuming avro_salescompleted_x is a table function
CROSS JOIN UNNEST(`basketItems`) AS bi
)
SELECT
store_id,
brand,
window_start,
window_end,
COUNT(*) AS `salesperbrand`,
SUM(itemValue) AS `totalperbrand`
FROM TABLE(
TUMBLE(unnested_sales, DESCRIPTOR(saleDateTime_Ltz), INTERVAL '5' MINUTE) -- fixed INTERVAL to singular form
)
GROUP BY
store_id,
brand,
window_start,
window_end;George Leonard
07/15/2024, 1:41 PMGeorge Leonard
07/15/2024, 2:03 PMCREATE TABLE unnested_sales (
`store_id` STRING,
`product` STRING,
`brand` STRING,
`saleValue` DOUBLE,
`category` STRING,
`saleDateTime_Ltz` STRING,
`saleTimestamp_WM` TIMESTAMP(3),
WATERMARK FOR `saleTimestamp_WM` AS `saleTimestamp_WM`
) WITH (
'connector' = 'kafka',
'topic' = 'unnested_sales',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
) ;
insert statement:
Insert into unnested_sales
SELECT
`store`.`id` as `store_id`,
bi.`name` AS `product`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `saleValue`,
bi.`category` AS `category`,
`saleDateTime_Ltz`,
TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)) AS `saleTimestamp_WM`
FROM avro_salescompleted_x -- assuming avro_salescompleted_x is a table function
CROSS JOIN UNNEST(`basketItems`) AS bi;
i can now do various aggregations on brand, product, category per time slices.George Leonard
07/15/2024, 2:43 PMFlink SQL> desc unnested_sales;
+------------------+------------------------+------+-----+--------+--------------------+
| name | type | null | key | extras | watermark |
+------------------+------------------------+------+-----+--------+--------------------+
| store_id | STRING | TRUE | | | |
| product | STRING | TRUE | | | |
| brand | STRING | TRUE | | | |
| saleValue | DOUBLE | TRUE | | | |
| category | STRING | TRUE | | | |
| saleDateTime_Ltz | STRING | TRUE | | | |
| saleTimestamp_WM | TIMESTAMP(3) *ROWTIME* | TRUE | | | `saleTimestamp_WM` |
+------------------+------------------------+------+-----+--------+--------------------+
7 rows in set
error
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.streaming.runtime.tasks.ExceptionInChainedOperatorException: Could not forward element to next operatorD. Draco O'Brien
07/15/2024, 4:12 PMGeorge Leonard
07/15/2024, 4:19 PMD. Draco O'Brien
07/15/2024, 4:21 PMGeorge Leonard
07/15/2024, 4:24 PMCREATE TABLE unnested_sales (
`store_id` STRING,
`product` STRING,
`brand` STRING,
`saleValue` DOUBLE,
`category` STRING,
`saleDateTime_Ltz` STRING,
`saleTimestamp_WM` TIMESTAMP(3),
WATERMARK FOR `saleTimestamp_WM` AS `saleTimestamp_WM`
) WITH (
'connector' = 'kafka',
'topic' = 'unnested_sales',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
) ;
insert
Insert into unnested_sales
SELECT
`store`.`id` as `store_id`,
bi.`name` AS `product`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `saleValue`,
bi.`category` AS `category`,
`saleDateTime_Ltz`,
TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)) AS `saleTimestamp_WM`
FROM avro_salescompleted_x -- assuming avro_salescompleted_x is a table function
CROSS JOIN UNNEST(`basketItems`) AS bi;
and the source table
CREATE TABLE avro_salesbaskets_x (
`invoiceNumber` STRING,
`saleDateTime_Ltz` STRING,
`saleTimestamp_Epoc` STRING,
`terminalPoint` STRING,
`nett` DOUBLE,
`vat` DOUBLE,
`total` DOUBLE,
`store` row<`id` STRING, `name` STRING>,
`clerk` row<`id` STRING, `name` STRING, `surname` STRING>,
`basketItems` array<row<`id` STRING, `name` STRING, `brand` STRING, `category` STRING, `price` DOUBLE, `quantity` INT>>,
`saleTimestamp_WM` AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)),
WATERMARK FOR `saleTimestamp_WM` AS `saleTimestamp_WM`
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salesbaskets',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
other than me being male and blind as my wife might accuse me... can't see any mis matches,
any female is welcome to find the problem and point me at it... 😉George Leonard
07/15/2024, 4:55 PMGeorge Leonard
07/15/2024, 4:56 PMSELECT
`store`.`id` as `store_id`,
bi.`name` AS `product`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `saleValue`,
bi.`category` AS `category`,
`saleDateTime_Ltz`,
TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)) AS `saleTimestamp_WM`
FROM avro_salescompleted_x -- assuming avro_salescompleted_x is a table function
CROSS JOIN UNNEST(`basketItems`) AS bi;George Leonard
07/15/2024, 5:00 PMCREATE TABLE unnested_sales
WITH (
'connector' = 'kafka',
'topic' = 'unnested_sales',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
)
AS SELECT
`store`.`id` as `store_id`,
bi.`name` AS `product`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `saleValue`,
bi.`category` AS `category`,
`saleDateTime_Ltz` as saleDateTime_Ltz,
TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)) AS `saleTimestamp_WM`
FROM avro_salescompleted_x -- assuming avro_salescompleted_x is a table function
CROSS JOIN UNNEST(`basketItems`) AS bi;George Leonard
07/15/2024, 5:00 PMGeorge Leonard
07/15/2024, 5:13 PMSELECT
`store`.`id` as `store_id`,
bi.`name` AS `product`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `saleValue`,
bi.`category` AS `category`,
`saleDateTime_Ltz` as saleDateTime_Ltz,
TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)) AS `saleTimestamp_WM`
FROM avro_salescompleted_x -- assuming avro_salescompleted_x is a table function
CROSS JOIN UNNEST(`basketItems`) AS bi;
and when building it like this then "saleTimestamp_WM" is null
CREATE TABLE unnested_sales
WITH (
'connector' = 'kafka',
'topic' = 'unnested_sales',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
)
AS SELECT
`store`.`id` as `store_id`,
bi.`name` AS `product`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `saleValue`,
bi.`category` AS `category`,
`saleDateTime_Ltz` as saleDateTime_Ltz,
TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)) AS `saleTimestamp_WM`
FROM avro_salescompleted_x -- assuming avro_salescompleted_x is a table function
CROSS JOIN UNNEST(`basketItems`) AS bi;George Leonard
07/15/2024, 5:22 PMGeorge Leonard
07/15/2024, 5:22 PMGeorge Leonard
07/15/2024, 5:44 PMGeorge Leonard
07/15/2024, 5:45 PMGeorge Leonard
07/15/2024, 5:54 PM-- Source topics is Avro serialized.
-- Flink UI : http://localhost:9081/#/overview
-- The below builds avro_salescompleted_x locally (on Apache Flink environment) as a output of a join from below 2 tables, the join results are inserted into avro_salescompleted_x.
-- salesbaskets_x and salespayments_x is build as virtual tables from the original topics (salesbaskets and salespayments)
-- join key is invoiceNumber.
-- After this we do a simple aggregate on sales per store per terminal per 5min and per hour (these values are at the root of the avro_salesbaskets table).
-- After this we unnest the baskeItems array into table:unnested_sales and then calculate:
-- sales per store per product per 5 min
-- sales per store per brand per 5 min
-- sales per store per category per 5min
-- NOTE: Case sentivity... need to match the case as per types/fs.go structs avro sections.
-- pull (INPUT) the avro_salesbaskets topic into Flink into avro_salesbaskets_x
CREATE TABLE avro_salesbaskets_x (
`invoiceNumber` STRING,
`saleDateTime_Ltz` STRING,
`saleTimestamp_Epoc` STRING,
`terminalPoint` STRING,
`nett` DOUBLE,
`vat` DOUBLE,
`total` DOUBLE,
`store` row<`id` STRING, `name` STRING>,
`clerk` row<`id` STRING, `name` STRING, `surname` STRING>,
`basketItems` array<row<`id` STRING, `name` STRING, `brand` STRING, `category` STRING, `price` DOUBLE, `quantity` INT>>,
`saleTimestamp_WM` AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)),
WATERMARK FOR `saleTimestamp_WM` AS `saleTimestamp_WM`
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salesbaskets',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
-- pull (INPUT) the avro_salespayments topic into Flink (avro_salespayments_x)
CREATE TABLE avro_salespayments_x (
`invoiceNumber` STRING,
`payDateTime_Ltz` STRING,
`payTimestamp_Epoc` STRING,
`paid` DOUBLE,
`finTransactionId` STRING,
`payTimestamp_WM` AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(`payTimestamp_Epoc` AS BIGINT) / 1000)),
WATERMARK FOR `payTimestamp_WM` AS `payTimestamp_WM`
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salespayments',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.avro-confluent.properties.use.latest.version' = 'true',
'value.fields-include' = 'ALL'
);
-- Our avro_salescompleted_x (OUTPUT) table which will push values to the CP Kafka topic.
-- https://nightlies.apache.org/flink/flink-docs-release-1.13/docs/connectors/table/formats/avro-confluent/
CREATE TABLE avro_salescompleted_x (
`invoiceNumber` STRING,
`saleDateTime_Ltz` STRING,
`saleTimestamp_Epoc` STRING,
`terminalPoint` STRING,
`nett` DOUBLE,
`vat` DOUBLE,
`total` DOUBLE,
`store` row<`id` STRING, `name` STRING>,
`clerk` row<`id` STRING, `name` STRING, `surname` STRING>,
`basketItems` array<row<`id` STRING, `name` STRING, `brand` STRING, `category` STRING, `price` DOUBLE, `quantity` INT>>,
`payDateTime_Ltz` STRING,
`payTimestamp_Epoc` STRING,
`paid` DOUBLE,
`finTransactionId` STRING,
`payTimestamp_WM` AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(`payTimestamp_Epoc` AS BIGINT) / 1000)),
`saleTimestamp_WM` AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)),
WATERMARK FOR `saleTimestamp_WM` AS `saleTimestamp_WM`
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salescompleted_x',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
-- the fields in the select is case sensitive, needs to match theprevious create tables which match the definitions in the struct/avro sections.
Insert into avro_salescompleted_x
select
b.invoiceNumber,
b.saleDateTime_Ltz,
b.saleTimestamp_Epoc,
b.terminalPoint,
b.nett,
b.vat,
b.total,
b.store,
b.clerk,
b.basketItems,
a.payDateTime_Ltz,
a.payTimestamp_Epoc,
a.paid,
a.finTransactionId
FROM
avro_salespayments_x a,
avro_salesbaskets_x b
WHERE a.invoiceNumber = b.invoiceNumber
AND a.payTimestamp_WM > b.saleTimestamp_WM
AND b.saleTimestamp_WM > (b.saleTimestamp_WM - INTERVAL '1' HOUR);
-- Create sales per store per terminal per 5 min output table - dev purposes
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
) WITH (
'connector' = 'kafka',
'topic' = 'avro_sales_per_store_per_terminal_per_5min_x',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
-- Calculate sales per store per terminal per 5 min - dev purposes
-- Aggregate query/worker
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(saleTimestamp_WM), INTERVAL '5' MINUTES))
GROUP BY `store`.`id`, terminalPoint, window_start, window_end;
-- Create sales per store per terminal per hour output table
CREATE TABLE avro_sales_per_store_per_terminal_per_hour_x (
`store_id` STRING,
`terminalPoint` STRING,
window_start TIMESTAMP(3),
window_end TIMESTAMP(3),
`salesperterminal` BIGINT,
`totalperterminal` DOUBLE
) WITH (
'connector' = 'kafka',
'topic' = 'avro_sales_per_store_per_terminal_per_hour_x',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
-- Calculate sales per store per terminal per hour
Insert into avro_sales_per_store_per_terminal_per_hour_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(saleTimestamp_WM), INTERVAL '1' HOUR))
GROUP BY `store`.`id`, terminalPoint, window_start, window_end;
--- unest the salesBasket
CREATE TABLE unnested_sales (
`store_id` STRING,
`product` STRING,
`brand` STRING,
`saleValue` DOUBLE,
`category` STRING,
`saleDateTime_Ltz` STRING,
`saleTimestamp_Epoc` STRING,
`saleTimestamp_WM` AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(`saleTimestamp_Epoc` AS BIGINT) / 1000)),
WATERMARK FOR `saleTimestamp_WM` AS `saleTimestamp_WM`
) WITH (
'connector' = 'kafka',
'topic' = 'unnested_sales',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
insert into unnested_sales
SELECT
`store`.`id` as `store_id`,
bi.`name` AS `product`,
bi.`brand` AS `brand`,
bi.`price` * bi.`quantity` AS `saleValue`,
bi.`category` AS `category`,
`saleDateTime_Ltz` as saleDateTime_Ltz,
`saleTimestamp_Epoc` as saleTimestamp_Epoc
FROM avro_salescompleted_x -- assuming avro_salescompleted_x is a table function
CROSS JOIN UNNEST(`basketItems`) AS bi;
-- Sales per store per brand per 5 min - output table
CREATE TABLE avro_sales_per_store_per_brand_per_5min_x (
`store_id` STRING,
`brand` STRING,
window_start TIMESTAMP(3),
window_end TIMESTAMP(3),
`salesperbrand` BIGINT,
`totalperbrand` DOUBLE
) WITH (
'connector' = 'kafka',
'topic' = 'avro_sales_per_store_per_brand_per_5min_x',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
Insert into avro_sales_per_store_per_brand_per_5min_x
SELECT
store_id,
brand,
window_start,
window_end,
COUNT(*) as `salesperbrand`,
SUM(saleValue) as `totalperbrand`
FROM TABLE(
TUMBLE(TABLE unnested_sales, DESCRIPTOR(saleTimestamp_WM), INTERVAL '5' MINUTE))
GROUP BY store_id, brand, window_start, window_end;
-- Sales per store per product per 5 min - output table
CREATE TABLE avro_sales_per_store_per_product_per_5min_x (
`store_id` STRING,
`product` STRING,
window_start TIMESTAMP(3),
window_end TIMESTAMP(3),
`salesperproduct` BIGINT,
`totalperproduct` DOUBLE
) WITH (
'connector' = 'kafka',
'topic' = 'avro_sales_per_store_per_product_per_5min_x',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
Insert into avro_sales_per_store_per_product_per_5min_x
SELECT
store_id,
product,
window_start,
window_end,
COUNT(*) as `salesperproduct`,
SUM(saleValue) as `totalperproduct`
FROM TABLE(
TUMBLE(TABLE unnested_sales, DESCRIPTOR(saleTimestamp_WM), INTERVAL '5' MINUTE))
GROUP BY store_id, product, window_start, window_end;
-- Sales per store per category per 5 min - output table
CREATE TABLE avro_sales_per_store_per_category_per_5min_x (
`store_id` STRING,
`category` STRING,
window_start TIMESTAMP(3),
window_end TIMESTAMP(3),
`salesperproduct` BIGINT,
`totalperproduct` DOUBLE
) WITH (
'connector' = 'kafka',
'topic' = 'avro_sales_per_store_per_category_per_5min_x',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
Insert into avro_sales_per_store_per_category_per_5min_x
SELECT
store_id,
category,
window_start,
window_end,
COUNT(*) as `salespercategory`,
SUM(saleValue) as `totalpercategory`
FROM TABLE(
TUMBLE(TABLE unnested_sales, DESCRIPTOR(saleTimestamp_WM), INTERVAL '5' MINUTE))
GROUP BY store_id, category, window_start, window_end;D. Draco O'Brien
07/16/2024, 8:34 AMD. Draco O'Brien
07/16/2024, 8:36 AMGeorge Leonard
07/16/2024, 8:54 AMGeorge Leonard
07/16/2024, 9:52 AM