Vertica 7.2 stuck importing messages from Kafka

0
votes

I'm trying to deal Kafka in Vertica v7.2.0-0 new feature. I have running Kafka broker, topic 'events' with json data. I configured Kafka scheduler in Vertica, set up broker, target table, Kafka JSON parser, etc. All is ok except when Vertica is recieving broken messages she rollbacking COPY transaction and do not move offset instead of getting new messages and just skip errors or write em to rejected table. Lets say I have NOT NULL column in table and when Vertica gets json without needed column next error was continuously occured (from vertica.log):

COPY "public"."events" SOURCE KafkaSource(stream='events|0|410015', brokers='1.2.3.4:9092', duration=interval '9884 milliseconds', stop_on_eof=true, executionparallelism=1 ) PARSER KafkaJSONParser( ) REJECTED DATA AS TABLE public.kafka_rej2 TRICKLE NO COMMIT 
Cannot set a NOT NULL column (device_type) to a NULL value in COPY statement

And how it looks in kafka_config.kafka_events:

Error occurred, rolling back changes from Micro batch.
| java.sql.SQLDataException: [Vertica]VJDBC ERROR: Cannot set a NOT NULL column (device_type) to a NULL value in COPY statement at com.vertica.util.ServerErrorData.buildException(Unknown Source) at com.vertica.dataengine.VResultSet.fetchChunk(Unknown Source) at com.vertica.dataengine.VResultSet.initialize(Unknown Source) at com.vertica.dataengine.VQueryExecutor.readExecuteResponse(Unknown Source) at com.vertica.dataengine.VQueryExecutor.handleExecuteResponse(Unknown Source) at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source) at com.vertica.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.vertica.jdbc.common.SStatement.executeUpdate(Unknown Source) at com.vertica.solutions.kafka.scheduler.MicroBatch.execute(MicroBatch.java:158) at com.vertica.solutions.kafka.scheduler.LaneWorker.run(LaneWorker.java:67) at java.lang.Thread.run(Unknown Source) Caused by: com.vertica.support.exceptions.DataException: [Vertica]VJDBC ERROR: Cannot set a NOT NULL column (device_type) to a NULL value in COPY statement ... 11 more

Oblivious thet I have en error, but how to tell Vertica to skip copy errors and go to next offset in Kafka topic? I have tried all KafkaJSONParser options but without and effect.

2
apache-kafkavertica
I think this question will be better suited on the official HPE forum. - Kermit

2 Answers

0
votes

I believe this is an artifact from the way that COPY and constraints work with Vertica. Usually when a record is "bad", COPY will reject the record and move on. However, the NOT NULL constraint happens further down the load stack and causes the entire statement to rollback. Because of this, the offset counter is never incremented: this would cause data loss.

The workaround is to target a table without the NOT NULL constraint, for now.

0
votes

You must create a flex table Instead of typical table. Try the following command (before the COPY command):

CREATE FLEX TABLE events();