... hmmm glueing some examples together , Anyone ...
# troubleshooting
g
... hmmm glueing some examples together , Anyone ?
Copy code
CREATE 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
Copy code
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:
Copy code
[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])
m
Your query is a streaming join so it produces updates. But your sink canโ€™t handle updates. You either need to use a query that results in append only results (like a temporal join) or use an upsert-Kafka sink
g
hmmm, and the answer from this noob is, "huh"
g
thinking, easiest is to change the target table, if i change the connector from kafka to upsert-kafka and retry the insert it results in. Still think i need to somehow limit the records/time span for which the select runs over.
Copy code
[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:
... still stuck here...
trying to fix, but also understand.. and this is not making sense... a upsert implies insert or update, that update implies i want a key... a unique way to find that record for the update... now why with upsert specified is it having a issue with me defining the key.fields column. ps that topic is/was created as part of this output stream. so the only producer to it will be this insert statement.
s
Upsert kafka needs the key defined using
PRIMARY KEY NOT ENFORCED
rather than key.fields
g
would you mind showing where / how ? I'm sure I tried primary key and got some error spit back at me ๐Ÿ˜‰
g
I recreated avro_salesbasket and avro_salespayment as per below
Copy code
CREATE 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'
);
Copy code
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
Copy code
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.
s
Connector should be upsert-kafka, not kafka
g
hmm avro-salespayments and avro_salesbaskets are source tables only... would this have a impact, the output, the insert runs against avro_salescompleted_x which is created as per below
Copy code
CREATE 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'
);
lack understanding why a source table would need the upsert-kafka vs just kafka.
so got the 2 source tables "flowing" in flink, further got an initial flow out of the combiner avro_salescompleted_x, for some records and then it stops. at the moment I'm joining everything with everything on the 2 tables.
m
The
PRIMARY 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 constraint
๐Ÿ‘ 1
g
ye was thinking primary key = upsert... as upsert is insert or update and that update would need to find the record that it wants to update, which would require a way to find it... primary key. thanks for confirmation.
working... hee haa, got it working, for those interested see <repo>/flink/creFlink2.sql https://github.com/georgelza/MongoCreator-GoProducer-pb