DEFAULT values not being populated by Kafka microbatch loader

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

SergeB

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

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

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