How to populate audit columns using Nifi?

[#1]

Imagine loading data from a file (any file) to a table, it is best practice to track when the record was last inserted or updated in the table. Since these are audit columns, we need to get the username or run time and insert/update on the fly as these may not come from the source table often.

How can we achieve this in Apace Nifi?

[#2]

@Amrutham,

How I would do it (and actually doing it in production): before inserting your data into your Database, you could add an UpdateRecord processor, where you define your new columns.

In your UpdateRecord you will define the following:
- a Record Reader: with default settings.
- a Record Writer: no matter the format you will choose, you will have to define the schema of the file, containing the newly added columns.
- a property named "/user": where you either extract the username from an attribute (assuming that you have it) or you write the desired username directly in the value field.
- a property named "/insert_date": where you use NiFi's Expression Language to extract the run time in whatever format you would like. An example would be: ${now():toNumber():minus(86400000):format("yyyy-MM-dd HH:mm:ss.SSS", "Europe/Bucharest")}

Now, make sure that:

- the property name begins with "/", otherwise it will not work as you desire.

- the Replacement Value Strategy is set to Literal Value.

[#3]

Could you please share screenshot how to add or the XML which I can import and test?

[#4]

A screenshot of what? I have no template which I can export so i cannot share any XML Files 🙂