Nifi flows ETL Process Log

[#1]

Hello,

I built a flow to pull data from the source (Oracle) and push it into the data warehouse. (Oracle).

The ETL procedure log must be created in a database, for example.

FLOW_ID, FLOW_NAME, START_TIME, END_TIME, STATUS, and ERROR_MESSAGE

How do I go about doing this?

Flow Example:

Log message into database like below

[#2]

hi @saquibsk,

What are you trying to achieve with this post, to be more precisely? You want to learn how to generate something in NiFi or you want somebody to help you write an SQL Query in your Data Warehouse? Please be so kind and provide some more details with regards to your problem.

[#3]

Hello @cotopaul,

I would like to create log of flows into database table. like when flow started, its succeed or failed. If failed then what is error message

example

Thanks,

Shakib

[#4]

Hi @saquibsk,

Ok, undestood. What I can tell you is that what you are trying to achieve is not impossible but is not easy either.

I believe in the power of a community but in the same time I believe that the scope of the community is to help you with solutions and advice to your problems and not to do the work for you 🙂

I assume that you already started a flow so lets start from there, what you developed, why it is not good and what you are missing from it.

From my point of view, there are two options here:

1) You modify all of your Processors to write the Bulletin Level at INFO (or Debug) and afterwards, using an InvokeHTTP, you can access your Bulletin Board with the REST API and extract your information. This it not highly recommended as you will generate very large logs files. Besides that, your certificates must be generated accordingly, otherwise you will get some errors.

2) At each step in your flow, you write a message to LogMessage, which will save your data into nifi-app.log. Here you can define in LogMessage exactly what you want to write. Afterwards, you can create a separate flow, using a TailFile Processor and extract everything you want from your nifi-app.log File. Here you will have to extract only the information you require 🙂

Once you have extracted your data, either from your Bulletin Board or from your LogFile, you can build the SQL Statement for inserting the data into the DB.