QueryDatabaseTableRecord with DBCPConnectionPoolLookup

[#1]

Hi,

No incoming connections are permitted with QueryDatabaseTableRecord. How i can populate the database.name attribute for QueryDatabaseTableRecord in order to be able to use DBCPConnectionPoolLookup as database connection as indicated in the documentation ?

[#2]

Hi @dmorvant ,

The QueryDatabaseTableRecord processor is probably not the processor that you need. The main objective of this processor it to periodically query the same table looking for recent records added to the table that need to be ingested. Rather than triggered by an incoming flowfile, this processor is typically scheduled to run at regular intervals.

It's also a stateful processor, which records the last record seen in the configured table. If you were able to change the configured database where it was looking for the table, it could confuse the processor and generate unexpected results.

I believe that the processor that you're looking for is the ExecuteSQLRecord. This processor receives a flowfile with a SQL query and executes the query against the configured DBCP. If using DBCPConnectionPoolLookup, you can add a "database.name" attribute to the incoming flowfile to do what you want.

If this is not what you need, it would help to understand what's your end goal so that we can thing of other ways to do what you need.

Cheers,

André

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

[#3]

Thanks for your anwser but it's not exactly what I need.

The end goal is to periodically query the same table looking for recent records added. However we have only 1 test server on which we have 3 test corridors by creating 3 Process Groups which each have an 'env' variable allowing via DBCPConnectionPoolLookup to connect to the associated database.
This allows us to deliver via Ansible/NiFi Registry components from development server without having to reconfigure each connection to database on target test corridor.

It works well with ExecuteSQLRecord but not QueryDatabaseTableRecord.

[#4]

Thanks for the explanation, @dmorvant .

From what you described, it seems to me that the ideal solution for this would be to use NiFi Parameters.

You could have 3 different Parameter Contexts, each of them with a different value for database.name.

Each one of the 3 Process Groups would use one of the Parameter Contexts. Then, when you create your DBCPConnectionPool controller service, you can specifyy the Database Connection URL parameter as, for example: 

jdbc:postgresql://cdp.100.20.115.239.nip.io:5432/#{database.name}

With this, the controller service of each Process Group would connect to the database defined in the Parameter Context of that group. You don't even need a DBCPConnectionPoolLookup in this case.

Would that address you need?

What the NiFi version that you are using?

André

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.