Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

DEFAULT values not being populated by Kafka microbatch loader

We have a structured landing table
CREATE SEQUENCE IF NOT EXISTS seq_landing INCREMENT 1 START WITH 1;
CREATE TABLE IF NOT EXISTS landing (
id INT NOT NULL DEFAULT seq_landing.NEXTVAL,
payload VARCHAR(128) NOT NULL,
PRIMARY KEY(id) ENABLED
) ORDER BY id;
The Kafka message does not specify the ID field as we expect it to be generated by Vertica:
{ "payload": "data payload here"}
However we got load rejection ‘Missing or null value for column with NOT NULL constraint [id]
Wondering why Vertica does not generate the auto-key in this case..

Answers

  • SergeBSergeB Employee

    Hard to tell without knowing more specifics about the parser you are using.
    But when creating your target, try to specify payload as the target column
    vkconfig target --create ....... --target-columns payload
    This should add payload to the COPY statement generated for the microbach.
    COPY landing(payload) KafkaSource....

  • I can reproduce this with the KafkaParser. I'm not an expert on this, but I suspect the KafkaParser is explicitly setting all fields to NULL if the JSON doesn't contain those fields.

  • Try explicitly adding the columns you allow the parser to use so that it doesn't touch the not-null ones.
    vkconfig microbatch --update --microbatch landingBatch --target-columns payload

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.