How to insert Date into mongoDB using NIFI

1
votes

I am trying to add data from csv file to mongoDB using NIFI 1.4. CSV file is containing data like:

101,ISODate(2006-01-02T15:04:05.000Z)

and I am using PutMongoRecord Processor along with CSVReader 1.4.0 controller service. I have defined my schema as:

{"name" :"agent","type":"string"},
{"name" :"transactiondate","type":"string"}

So as result I am getting output as "ISODate(2006-01-02T15:04:05.000Z)" in mongoDB as datatype as String but it should be Date . So for that I need output as ISODate("2006-01-02T15:04:05.000Z") . Kindly help if there is any way to do so.

TIA

2
mongodbcsvapache-nifiisodate

2 Answers

3
votes

I think you need to convert your date from a string to an Avro logical type. According to this, "For PutMongoRecord all you have to do is use a long annotated as a timestamp or an int annotated as a date and make sure record reader has the format options done configured correctly for timestamp and date."

0
votes

@mattyb answer is perfect. Just adding more details here, since I ran into the same situation and was able to resolve it.

1.Here is an example on how you can handle date and timestamp in your AVRO schema.

{
  "type": "record",
  "namespace": "com.example",
  "name": "TestRecord",
  "fields": [
    { "name": "FLOAT_TO_INT", "type": "float" },
    { "name": "TIMESTAMP"   , "type": { "type":"long", "logicalType":"timestamp-millis"} },
    { "name": "TEXT"        , "type": "string" },
    { "name": "DATE"        , "type": { "type":"int", "logicalType":"date"} }
  ]
}

2.In your CSV reader configuration, use the above AVRO schema, and in Date Format, specify yyyy-MM-dd