... I have the following 2 streams created... from...
# troubleshooting
g
... I have the following 2 streams created... from topics, changing the serialization format. I'd like to pull them into flink as tables and then join them into a avro_salescompleted
Copy code
CREATE 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;
Copy code
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'
);