Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

PutDatabaseRecord processor: Inconsisdent datatype expected date got number

avatar
Contributor

Hello All,

I am doing round trip of data from one table ( NIFI1) of an Oracle database to different table (NIFI2) if same database. Both tables have same structure.

CREATE TABLE INT_STG.NIFI1 ( ID INTEGER, NAME VARCHAR2(100 BYTE), UPDATED_DATE DATE )

CREATE TABLE INT_STG.NIFI2 ( ID INTEGER, NAME VARCHAR2(100 BYTE), UPDATED_DATE DATE )

In NIFI and using following design to move data:

QueryDatabaseTable ---> CovertRecord --> PutDatabaseRecord

I am getting " Inconsisdent datatype expected date got number" error in PutDatabaseRecord. Can someone please help me in finding out issue with the flow?

This is how data looks like between QueryDatabaseTable and ConvertRecord:

Objavro.schemaÈ{"type":"record","name":"NIFI1","namespace":"any.data","fields":[{"name":"ID","type":["null",{"type":"bytes","logicalType":"decimal","precision":38,"scale":0}]},{"name":"NAME","type":["null","string"]},{"name":"UPDATED_DATE","type":["null",{"type":"long","logicalType":"timestamp-millis"}]}]}�'»hd˦Ýhp/¬~a5j> I�Ïó˜¡W'»hd˦Ýhp/¬~a5j>

This is how data looks like between CovertRecord and PutDatabaseRecord

I�Ïó˜¡W

Please note that

a) QueryDatabaseTable has "Use Avro Logical Types" = true

b) Convert Record has Record Reader as AvroReader with Schema Access Registry = Use Embedded Avro Schema and

RecordWriter = AvroRecordSetWriter with Schema Access Registry = "Use Schema Text Property" and Schema Text =

{ "type": "record", "name": "ServiceStatusRecord", "fields" : [ {"name": "ID", "type": "int"}, {"name": "NAME", "type": ["null", "string"]}, {"name": "UPDATED_DATE", "type": "long", "logicalType": "timestamp-millis"} ] }

c) PutDatabaseRecord has AvroReader with Schema Access Strategy = "Use Schema Text Property" and Schema Text =

{ "type": "record", "name": "ServiceStatusRecord", "fields" : [ {"name": "ID", "type": "int"}, {"name": "NAME", "type": ["null", "string"]}, {"name": "UPDATED_DATE", "type": "long", "logicalType": "timestamp-millis"} ] }

Here is the error I am getting

img-2739.jpg

Here is the flow in NiFi

16672-flow.png

Thanks in advance

1 REPLY 1

avatar

Hello @M R

I was able to reproduce this behavior.

The reason is timestamp-millis logicalType is not used as expected. When PutDatabaseRecord executes SQL, 'UPDATED_DATE' was set as it is as Long representation, so Oracle complains about it. Oracle expects Date type.

Debugging further, I found that Avro doesn't read logicalType information if the type is defined as a String JSON node.

Your schema text is defined as follows:

{
  "name": "UPDATED_DATE",
  "type": "long",
  "logicalType": "timestamp-millis"
}

This way, 'logicalType' exists in the Field object, not for the 'type'. Since the 'type' element is textual, Avro parser don't decorate it.

It has to be:

{
  "name": "UPDATED_DATE",
  "type": {
    "type": "long",
    "logicalType": "timestamp-millis"
  }
}

To correctly annotate type with logicalType. Now, the 'type' element is an JSON object, and Avro parser uses 'logicalType' definition. Then it works as expected.