Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

NiFi: Converting a datetime field (CSV to DB)

Solved Go to solution
Highlighted

NiFi: Converting a datetime field (CSV to DB)

New Contributor

Hi,

With NiFi, I'm picking up a CSV file whose records have a bit of a silly timestamp format: MM-dd-yyyy HH:mm:ss. I want to convert this into yyyy-MM-dd HH:mm:ss before attempting to insert this into a SQL server datetime field.

To do this, I'm trying the ConvertRecord processor, with a CSVReader and currently a JSONRecordSetWriter (convenient since it allows me easily read the resulting data. An example CSV is:

id;timestamp
1;11-12-2016 9:38:13
2;12-12-2016 9:38:14
3;12-21-2016 9:38:15

For the CSVReader, my schema text is:

{    
"type": "record",
    "name": "testdata",
    "fields": [
		{
            "name": "id",
            "type": "int"
        },
        {
            "name": "timestamp",
            "type": {"type":"string","logicalType":"timestamp"}
        }
    ]
}

I've set the property Timestamp format to MM-dd-yyyy HH:mm:ss.

I'm using the same schema text for the JSONRecordSetWriter and have the Timestamp format set to yyyy--MM-dd HH:mm:ss.

I'm expecting this to achieve the datetime conversion that I need to do, but in the resulting JSON my datetime field is exactly the same.

Attached you'll find and XML template that demos this.

Am I going about this the wrong way and completely misunderstanding what ConvertRecord does? Am I missing something?datetime-format-conversion.xml

Edit: I'm still interested to hear your take on this, but as it so often goes I solved the problem a little while after I posted this question. I'm using UpdateRecord and doing:

${field.value:toDate('MM-dd-yyyy HH:mm:ss'):format('yyyy-MM-dd HH:mm:ss')}

That works for me.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

New Contributor

Per @Steven Matison's suggestion, I'm posting the solution to the problem I was facing as an answer:

I was using the wrong processor. It turns out the UpdateRecord processor is a good fit for this. I'm updating the field in question with the following expression language command:

${field.value:toDate('MM-dd-yyyy HH:mm:ss'):format('yyyy-MM-dd HH:mm:ss')}

Here's an XML template that demonstrates this functionality: updaterecord-datetime-conversion.xml

View solution in original post

11 REPLIES 11
Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

This is the proper method... Good job!

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven

Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

New Contributor

Thanks! For the next person trying to accomplish something like this, I've attached another xml template that demonstrates this functionality: updaterecord-datetime-conversion.xml

Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

The next thing you can do is write your own answer, then accept it. I believe you will get some profile badges for doing that.

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven

Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

New Contributor

Per @Steven Matison's suggestion, I'm posting the solution to the problem I was facing as an answer:

I was using the wrong processor. It turns out the UpdateRecord processor is a good fit for this. I'm updating the field in question with the following expression language command:

${field.value:toDate('MM-dd-yyyy HH:mm:ss'):format('yyyy-MM-dd HH:mm:ss')}

Here's an XML template that demonstrates this functionality: updaterecord-datetime-conversion.xml

View solution in original post

Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

Rising Star

@Walter Vos Thanks, saved me from loosing my mind!

Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

Explorer

Hi @waltervos , I need to achieve this data conversion in updateAttribute processor. Below are my configuration in updateAttribure.

PropertyName: created_date

Value as : ${created_date.value:toDate("EEE MMM dd HH:mm:ss z yyyy"):toNumber():format("yyyy-MM-dd HH:mm:ss.SSS")}

 

After updateAttribute I have AttributesToJSON processor -> PutDatabaseRecord.

 

I am not getting the converted value after updateAttribute or AttributesToJSON. Please suggest how to get the updated value in PutDatabaseRecord. Input JSON attached. InputJson.png

Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

New Contributor

Why do you have toNumber() in there, @skiganesh ?

Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

Explorer

HI @waltervos , Yes, it worked without converting it to Number also, I was applying the solutions from another post. 

Highlighted

Re: NiFi: Converting a datetime field (CSV to DB)

Explorer

Hi @waltervos , I am not getting date converted in updateAttribute processor. Please suggest.

Don't have an account?
Coming from Hortonworks? Activate your account here