Oracle to Kafka Null character '\u0000' in messages

[dineshkumarl]

Hi All,

I have a replicate task to load data from Oracle as source and Kafka as target. 

A few of these columns are frequently showing up in the Kafka messages with values inclusive of the string "\u0000" or repeats of that string. I have looked in the Oracle source tables to see that the special character for NUL exists in those columns but is not visible in a typical query UI tool. When using Avro, those special NULL characters are coming across as "\u0000". I am sending messages in Avro format along with Avro schema registry, how do I make sure those special NUL characters are replaced with nothing?

I have tried character substitution +U0 substituted with +U200b but I still see the character '\u0000' in the messages.

I have referred the below post: 

Solved: DB2 to Kafka NUL character problem - Qlik Community - 1728163

Kindly let me know if I am missing out on something.

/Thanks

[Steve_Nguyen]

@dineshkumarl 

you first need to figures out what is the source special character then you can use ascii to do the character subsitution.

https://www.rapidtables.com/code/text/ascii-table.html

[john_wang]

Hello @dineshkumarl ,

In Kafka AVRO format messages, "\u0000" represents a null value (Unicode empty/null). This is because the AVRO serialization format uses a special marker to indicate null values, "\u0000" is the byte representation of that marker. When a field in an AVRO message is null, its value is replaced with this flag. That's why you get it.

Looks to me you need not to find a way to replace it but need to take care about the marker during consume the messages. anyway how you handle null values will depend on the specific requirements of your consume applications, it's irrelevant to Replicate producer side. Correct me if I'm inaccurate.

Regards,

John.

[dineshkumarl]

Thank you for the information. I will work on Kafka connect sink config and update the solution.