Timestamp Format Issue (NiFi's PutDatabaseRecord)

Question

Getting error when inserting a csv-file into a MSSQL database using the PutDatabaseRecord (1.9.2) processor in Nifi.

ListFile → FetchFile → PutDatabaseRecord

The schema used:

{ "type" : "record", "name" : "mibas_t005_talkgroup", "fields" : [  
{ "name" : "talkgroup_ref", "type" : ["null","long"] },
{ "name" : "fleet_ref", "type" : ["null","long"] },
{ "name" : "talkgroup_id", "type" : ["null","long"] },
{ "name" : "talkgroup_name", "type" : ["null","string"] },
{ "name" : "group_type", "type" : ["null","string"] },
{ "name" : "dispatcher_ref", "type" : ["null","string"] },
{ "name" : "multigroup_ref", "type" : ["null","string"] },
{ "name" : "owner_", "type" : ["null","string"] },
{ "name" : "fne_state", "type" : ["null","string"] },
{ "name" : "addl_attrvar", "type" : ["null","string"] },
{ "name" : "last_user_modified", "type" : ["null","string"] },
{ "name" : "time_stamp", "type" : ["null", { "type":"long", "logicaltype":"timestamp-millis"} ]},
{ "name" : "fne_oid", "type" : ["null","string"] }]}

Datatype in the table is DateTime

lauritz · Accepted Answer

The solution was changing the datatype definition in the schema from logicaltype to logicalType (Upper case T there for you). wtf!!!

Eg: *{ "name" : "time_stamp", "type" : ["null", { "type":"long", "logicalType":"timestamp-millis"}*

The irritating part is that the schema validates. No warning! But, if you mistakenly use String instead of the correct string (lower case S) or Type instead of type in you schema, your schema will not validate and you can correct it. But for logicalType no.

DennisJaheruddi · Answer

It seems that the PutDatabaseRecord processor expects your timestamp to be numeric (probably milliseconds since the epoch).

I notice your screenshot contains a specification of the timestamp format, but I don't see this as an option in the documentation, so possibly that has no effect in this processor.

Here is the reference: https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.9.2/org.apache.nifi.processors.standard.PutDatabaseRecord/index.html

----

Possible solution: Load or convert the timestamp to the right value.

A simple trick to check what the value should be, is by manually inserting a date into the table, loading it with Nifi and then inspecting the value.

(You can also test writing it back, that should confirm that the value works).

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.