George Leonard
07/05/2024, 6:48 PMCREATE TABLE avro_salescompleted_x (
-- one column mapped to the Kafka raw UTF-8 key
the_kafka_key STRING,
-- a few columns mapped to the Avro fields of the Kafka value
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>>,
FINTRANSACTIONID STRING,
PAYDATETIME STRING,
PAYTIMESTAMP STRING,
PAID DOUBLE,
SALESTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALETIMESTAMP AS BIGINT) / 1000)),
PAYTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(PAYTIMESTAMP AS BIGINT) / 1000)),
WATERMARK FOR SALESTIMESTAMP_WM AS SALESTIMESTAMP_WM
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salescompleted_x',
'properties.bootstrap.servers' = 'broker:29092',
'key.format' = 'raw',
'key.fields' = 'the_kafka_key',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'EXCEPT_KEY'
);
insert then
Insert into avro_salescompleted_x
SELECT
b.INVOICENUMBER as the_kafka_key,
b.INVOICENUMBER,
a.SALEDATETIME,
a.SALETIMESTAMP,
a.TERMINALPOINT,
a.NETT,
a.VAT,
a.TOTAL,
a.STORE,
a.CLERK,
a.BASKETITEMS,
b.FINTRANSACTIONID,
b.PAYDATETIME,
b.PAYTIMESTAMP,
b.PAID
FROM
avro_salesbaskets a LEFT JOIN
avro_salespayments b
ON a.INVOICENUMBER = b.INVOICENUMBER;
results in:
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.TableException: Table sink 'default_catalog.default_database.avro_salescompleted_x' doesn't support consuming update and delete changes which is produced by node Join(joinType=[LeftOuterJoin], where=[(INVOICENUMBER = INVOICENUMBER0)], select=[INVOICENUMBER, SALEDATETIME, SALETIMESTAMP, TERMINALPOINT, NETT, VAT, TOTAL, STORE, CLERK, BASKETITEMS, INVOICENUMBER0, FINTRANSACTIONID, PAYDATETIME, PAYTIMESTAMP, PAID], leftInputSpec=[NoUniqueKey], rightInputSpec=[NoUniqueKey])Martijn Visser
07/05/2024, 6:57 PMGeorge Leonard
07/05/2024, 6:59 PMMartijn Visser
07/05/2024, 7:02 PMGeorge Leonard
07/05/2024, 7:07 PM[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.ValidationException: Unsupported options found for 'upsert-kafka'.
Unsupported options:
key.fields
Supported options:George Leonard
07/06/2024, 8:08 AMGeorge Leonard
07/06/2024, 9:58 AMSimon Dahlbacka
07/07/2024, 11:24 AMPRIMARY KEY NOT ENFORCED rather than key.fieldsGeorge Leonard
07/07/2024, 11:33 AMSimon Dahlbacka
07/07/2024, 11:37 AMGeorge Leonard
07/07/2024, 11:37 AMCREATE TABLE avro_salesbaskets (
INVOICENUMBER 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>,
SALESTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALETIMESTAMP AS BIGINT) / 1000)),
WATERMARK FOR SALESTIMESTAMP_WM AS SALESTIMESTAMP_WM,
PRIMARY KEY (INVOICENUMBER) NOT ENFORCED
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salesbaskets',
'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'
);
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,
PRIMARY KEY (INVOICENUMBER) NOT ENFORCED
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salespayments',
'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'
);
then re ran the insert statement as per above and got
org.apache.flink.table.api.ValidationException: The Kafka table 'default_catalog.default_database.avro_salesbaskets' with 'avro-confluent' format doesn't support defining PRIMARY KEY constraint on the table, because it can't guarantee the semantic of primary key.Simon Dahlbacka
07/07/2024, 11:45 AMGeorge Leonard
07/07/2024, 11:48 AMCREATE TABLE avro_salescompleted_x (
-- one column mapped to the Kafka raw UTF-8 key
the_kafka_key STRING,
-- a few columns mapped to the Avro fields of the Kafka value
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>>,
FINTRANSACTIONID STRING,
PAYDATETIME STRING,
PAYTIMESTAMP STRING,
PAID DOUBLE,
SALESTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALETIMESTAMP AS BIGINT) / 1000)),
PAYTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(PAYTIMESTAMP AS BIGINT) / 1000)),
WATERMARK FOR SALESTIMESTAMP_WM AS SALESTIMESTAMP_WM,
PRIMARY KEY (INVOICENUMBER) NOT ENFORCED
) WITH (
'connector' = 'upsert-kafka',
'topic' = 'avro_salescompleted_x',
'properties.bootstrap.servers' = 'broker:29092',
'key.format' = 'raw',
'key.fields' = 'the_kafka_key',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'EXCEPT_KEY'
);George Leonard
07/07/2024, 11:48 AMGeorge Leonard
07/08/2024, 6:46 PMMartijn Visser
07/09/2024, 7:00 AMPRIMARY KEY constraint only needs to be defined for when you want to use upsert-kafka - If you don't need a changelog stream (which is what you read/write when using this as a connector), simply remove the constraintGeorge Leonard
07/09/2024, 7:25 AMGeorge Leonard
07/09/2024, 11:32 AM