George Leonard
07/05/2024, 3:44 PMCREATE STREAM avro_salesbaskets WITH (KAFKA_TOPIC='avro_salesbaskets',
VALUE_FORMAT='Avro',
PARTITIONS=1)
as
select
InvoiceNumber,
SaleDateTime,
SaleTimestamp,
CAST(SaleTimestamp AS BIGINT) AS Sales_epoc_bigint,
TerminalPoint,
Nett,
Vat,
Total,
store,
clerk,
BasketItems
from
pb_salesbaskets
emit changes;
CREATE STREAM avro_salespayments WITH (KAFKA_TOPIC='avro_salespayments',
VALUE_FORMAT='Avro',
PARTITIONS=1)
as
select
InvoiceNumber,
FinTransactionId,
PayDateTime,
PayTimestamp,
CAST(PayTimestamp AS BIGINT) AS Pay_epoc_bigint,
Paid
from pb_salespayments
emit changes;
the thinking to create the 2 flink tables was;
CREATE TABLE avro_salesbaskets (
INVOICENUMBER 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>>,
SALESTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALETIMESTAMP AS BIGINT) / 1000)),
WATERMARK FOR SALESTIMESTAMP_WM AS SALESTIMESTAMP_WM
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salesbaskets',
'properties.bootstrap.servers' = 'broker:29092',
'scan.startup.mode' = 'earliest-offset',
'properties.group.id' = 'testGroup',
'key.format' = 'avro-confluent',
'key.fields' = 'INVOICENUMBER',
'key.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
'value.format' = 'avro-confluent',
'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
'value.fields-include' = 'EXCEPT_KEY'
);
CREATE TABLE avro_salespayments (
INVOICENUMBER STRING,
FINTRANSACTIONID STRING,
PAYDATETIME STRING,
PAYTIMESTAMP STRING,
PAID DOUBLE,
PAYTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(PAYTIMESTAMP AS BIGINT) / 1000)),
WATERMARK FOR PAYTIMESTAMP_WM AS PAYTIMESTAMP_WM
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salespayments',
'properties.bootstrap.servers' = 'broker:29092',
'scan.startup.mode' = 'earliest-offset',
'properties.group.id' = 'testGroup',
'key.format' = 'avro-confluent',
'key.fields' = 'INVOICENUMBER',
'key.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
'value.format' = 'avro-confluent',
'value.avro-confluent.schema-registry.url' = 'http://schema-registry:8081',
'value.fields-include' = 'EXCEPT_KEY'
);