Skip to main content
Question

DEFAULT values not being populated by Kafka microbatch loader

  • March 27, 2020
  • 3 replies
  • 15 views
johnnychien

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..

    3 replies

    SergeB
    Forum|alt.badge.img
    • SergeB
    • Participating Frequently
    • March 29, 2020

    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....

    ThePebble
    • ThePebble
    • Participating Frequently
    • April 1, 2020

    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.

    ThePebble
    • ThePebble
    • Participating Frequently
    • April 1, 2020

    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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement