hi hi all. I got a table avro_salescompleted_x tha...
# troubleshooting
g
hi hi all. I got a table avro_salescompleted_x thats a combination of salesbaskets and salespayments. Salesbasket look at follows.
Copy code
{
  "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.
Copy code
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
Yep, your right. It is do to basketitems being an array not a separate table. What you need to do is UNNEST the BASKETITEMS before performing the aggregation and windowing.
g
will go search for flink and unnest
d
yep you can do
Copy code
UNNEST('BASKETITEMS) as bitems
You might need a subquery for it too
and use bitems later in the groupby
g
hehehe, do appreciate the pointers... but all i can to that is "huh" and then go google.
d
Maybe it can be structured something like this:
Copy code
SELECT
    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;
You have to adjust a bit probably
g
will try this. thanks. curious though just looking at above... which item in the basketarray would it do... or does it create a record associated with storeid for every item in the basket ?
d
Actually I think maybe you need to do the UNNESTING before the windowing …
g
will have a look, thanks.
d
Copy code
WITH 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;
I think this is might be closer to what you need. Have to experiment with it a bit.
Yes, exactly. When you unnest the BASKETITEMS array, it creates a separate record for each item in the basket, associating each item with the corresponding STORE ID
👍 1
g
thanks.
refactored the above to match my case of fields.
Copy code
WITH 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:
Copy code
[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
To address your other question, this query does not create an output table by itself unless executed in a context that implicitly or explicitly defines an output sink eg. writing the results into another table or file. In most SQL execution environments, this would typically be done by including a clause such as CREATE TABLE AS SELECT ...or inserting into an existing table INSERT INTO some_table SELECT .... See if this version is an improvement:
Copy code
WITH 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;
g
thinking as i have a sales per brand, sales per product (name column) and more... it might make sense to push the unbundling of the array once and then do various queries on it where i do the different group by's.
... broke it up as follows
Copy code
CREATE 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:
Copy code
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.
strange.... the create table executes... the select itself works. the insert executes. select * fails, but a select of one or 2 column work. i want to run select with groups by's based on watermark, to aggregate sales per product or per brand or per category per tumble describe of table
Copy code
Flink 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
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.streaming.runtime.tasks.ExceptionInChainedOperatorException: Could not forward element to next operator
d
Well looks like you’ve successfully created an intermediate table, unnested_sales. The error you’re encountering when attempting to perform a SELECT * from the unnested_sales table might be due to a few possible reasons … Check for possible schema mismatch. ie ensure that all columns in your SELECT clause match the defined schema of the unnested_sales table in terms of data types and names. Sometimes, subtle differences (like case sensitivity or trailing spaces) can cause issues. Also check watermark usage If you’re trying to query the table using window functions or time-based aggregations immediately after insertion, note that watermarks propagate with a delay in streaming. if you’re querying for aggregated results based on saleTimestamp_WM right after inserting data, you may encounter errors or incomplete results if the sys hasn’t had enough time to establish the correct event time order and generate the watermark in the right way. Try running a simple SELECT query on a single column or a limited set of columns without any grouping or windowing to verify basic query functionality is ok. If successful you can gradually introduce complexity by grouping by a static column to understand at what point the issue arises. Check the Flink SQL client’s buffer settings and ensure they are adequately configured for your stream’s throughput. Verify that your watermark strategy is correctly implemented and that events are being timestamped and watermarked appropriately in the source data and also during insertion
g
will do a column for column select... if i run the select that does the insert that works though... so thinking problem got to be at the target table... but it's all string values except for the watermark which i do define as timestamp(3)
d
I think it has to match exactly more or less
g
create table
Copy code
CREATE 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
Copy code
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
Copy code
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... 😉
so seems i got 2 problems. select saleValue from unnested_sales; results in nulls and select saleTimestamp_WM from unnested_sales; results in error.
... this works however...
Copy code
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;
got it working... did it via a CTAS... removing human error from the 2 steps.
Copy code
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;
that allows me to select from unnested_sales;
this is just weird... this return all columns, including "saleTimestamp_WM"
Copy code
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;
and when building it like this then "saleTimestamp_WM" is null
Copy code
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;
made progress... went and dropped the auto generated schema and topic and reran the create as select.. the new generated table has all information in it...
good...
GOT IT Working....
and went back to create table, followed by a seperate insert. the single create table as select had issues with the saleTimestamp_WM not being a timecol but a timestamp. will post working result shortly.
slightly long, but maybe someone finds this usefull/example:
Copy code
-- 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
Looks like a very complete example actually. Maybe it could be written up as a blog with sample data.
It seems like you encountered almost every major issue with Flink SQL. It might indeed save others many iterations but needs more documentation I think.
g
A blog is/was the original intension so yes there will def be one. I know my source app can be pulled into a 2 separate containers, basket create, creating and posting to basket topic and making call to making call out to separate container for payment, doing it's own post... the project/blog is however not about the source app... so can see/will expect some flames 😉
... been thinking, i want to take some of the output flink tables and partition them, enabling parallelism... aka see it in flink interface as multiple output/compute threads... thinking big data volume picture... little todo for ltr 😉 aka scope creap...