Apache Nifi date formatting in update Attribute

[#1]

Hello All,

I am new to Apache Nifi where we have a requirement to convert a user input date into Nifi date format and use it in an sql query running inside an executeSQL processor.

I am facing challenge while converting the user input date, Any help would be really appreciated.

Below are my flowfiles

GenerateFlowfile with a Custom Date properties with value as "2023-01-15 10:30:00"

UpdateAttribute using the Custom Date as End_date with value as ${'Custom Date':plus(864000000):format('yyyyMMddHHmmss')}

ExecuteSQL using End_date in Oracle query as " TO_DATE ('${End_Date}', 'YYYYMMDDHH24MISS')"

Also is there any better way to handle this.

[#2]

@Virt_Apatt Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our NiFi experts @SAMSAL @MattWho  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

[#3]

@Virt_Apatt 

Where is the "custom date" originating from.  I see you are creating a FlowFile with a "Custom_Date" attribute which you then modify later, but is this custom date changing often?  If so, where are you getting it from?

Any dataflow that requires constant user updating is not going to be ideal.  

A more thorough and detailed use case might get you more responses in the community, especially when asking "is there a better way".  

Screenshot of your current dataflow?

From what you shared, I don't see the need for the Update Attribute since you can manipulate the custom date directly in the GenerateFlowFile or ExecuteSQL processors by simply using the NiFi Expression Language (NEL) statement directly in during creation or during SQL query creation.

Thanks,
Matt

[#4 · Accepted solution]

@Virt_Apatt 

let me add some clarity to my last response with some example NiFi Expression Language (NEL) statements:

NiFi attributes are stored as strings.  In some cases NiFi will infer a number when it can, but when it comes to dates that is not possible.  So when you create a FlowFile attribute with date string you'll need to convert to a date type before you can add you 10 days in milliseconds to it.

Below is a NEL statement that you can enter your custom date into do the complete conversion in one step with out needing to pass the custom date in to the NEL from a FlowFile Attribute.  

${literal('2023-01-15 10:30:00'):toDate('yyyy-MM-dd hh:mm:ss'):plus(864000000):format('yyyyMMddHHmmss')}

Here is NEL statement where custom-Date comes from a pre-existing attribute on the FlowFile.  This one allows you to get your initial "Custom Date" from a FlowFile attribute added to your FlowFile at some point upstream in your dataflow (pulling from some source, from some distributed cache, etc). This eliminates the need for the extra UpdateAttribute processor.

${'Custom Date':toDate('yyyy-MM-dd hh:mm:ss'):plus(864000000):format('yyyyMMddHHmmss')}

Please help our community grow. If you found any of the suggestions/solutions provided helped you with solving your issue or answering your question, please take a moment to login and click "Accept as Solution" on one or more of them that helped.

Thank you,
Matt

[#5]

Thanks @MattWho for the detail explanation, This solution works, I have removed the UpdateAttribute,The Custom_Date would be an User input,Is there any better way we can allow user to input the Date and use it in Nifi Processor,Please suggest or shall I create a new questions,

[#6]

@Virt_Apatt 

I don't know enough about your use case to make any other suggestions.
All I know is that your user(s) supply some custom date that you have NiFi add 10 days to before running a Oracle query to get some result set returned to NiFi.

NiFi is typically used to build dataflows that are always in the running state, so users do not need to continuously stop, modify component(s), and start a dataflow/component.

What is the significance of this "custom date" that starts your dataflow?
Is there any pattern to these custom dates?
Can the next custom date be derived from the response from the previous Oracle query?
How often does this dataflow get executed?

Just some examples (there are many NiFi processor components that can fetch content from external sources):

1. You could start your dataflow with a getSFTP or getFile processor that is checks a specific source SFTP server or local directory for a specific filename. In that file is your custom date.  You then build your dataflow to extract that custom date from the consumed file to then execute your oracle query.  This way your NiFi is always running and just waiting for the next file to show up on the SFTP server or in that local directory it keeps checking.
2. Or maybe setup an http lister (ListenHTTP or HandleHTTPRequest) that listens for an http post that contains the custom date needed for your running dataflow.

Please help our community grow. If you found any of the suggestions/solutions provided helped you with solving your issue or answering your question, please take a moment to login and click "Accept as Solution" on one or more of them that helped.

Thank you,
Matt