Nifi: Multiple databases , same query, how do pass database name to the connection string?

7 replies
13734 views
Translate All

Hi,

I'm trying to connect to different databases that all have the same structure and execute the same query on them. However, i can't figure out how to give this to nifi execute SQL processor and i'm having to create a processor for every connection to the database. IS there a way for me to create a list of connection strings and then simply loop through them?

i.e. i need to dynamically assign the connection pooling service to the processor. How?

Apache NiFi

Shu_ashuAnswer

Gold ⭐️⭐️⭐️

@Yasir Khokhar

Starting from NiFi-1.7 version we can dynamically assign connection pooling service by using

DBCPConnectionPoolLookup controller service.

From documentation:

1.Provides a DBCPService that can be used to dynamically select another DBCPService. This service requires an attribute named 'database.name' to be passed in when asking for a connection, and will throw an exception if the attribute is missing. 2.The value of 'database.name' will be used to select the DBCPService that has been registered with that name. 3.This will allow multiple DBCPServices to be defined and registered, and then selected dynamically at runtime by tagging flow files with the appropriate 'database.name' attribute

If you are using NiFi version <1.7.0 then we need to have to define each DBCPConnection controller service for each connection of ExecuteSql processor.

Shu_ashuAnswer

Gold ⭐️⭐️⭐️

@Yasir Khokhar

Starting from NiFi-1.7 version we can dynamically assign connection pooling service by using

DBCPConnectionPoolLookup controller service.

From documentation:

1.Provides a DBCPService that can be used to dynamically select another DBCPService. This service requires an attribute named 'database.name' to be passed in when asking for a connection, and will throw an exception if the attribute is missing. 2.The value of 'database.name' will be used to select the DBCPService that has been registered with that name. 3.This will allow multiple DBCPServices to be defined and registered, and then selected dynamically at runtime by tagging flow files with the appropriate 'database.name' attribute

If you are using NiFi version <1.7.0 then we need to have to define each DBCPConnection controller service for each connection of ExecuteSql processor.

Translate

azureAuthor

Gold ⭐️

@shu

Many thanks for responding--however, with this solution I will need multiple ExecuteSQL processors each specifying the database.name attribute value, correct? I'll end up with dozens of ExecuteSQL processors that have the same SQL but different Database.name.

Is my understanding correct or am i missing a trick?

I guess, the question is, how/where do i set the database.name attribute? I've configured the service, but how is the execute SQL processor configured?

Translate

Shu_ashu

Gold ⭐️⭐️⭐️

@Yasir Khokhar

If you are using DBCPConnectionPoolLookup controller service for ExecuteSQL processor then each flowfile feeding to ExecuteSQL processor needs to have database.name attribute with the value of DBCPconnectionpool name that is given.

-

Now based on database.name attribute value ExecuteSQL processor is going to select connectionpool dynamically, so we are going to use one ExecuteSQL processor using multiple DBCPconnectionPool dynamically.

-

Use UpdateAttribute Processor to set database.name attribute to the flowfile and configure/enable DBCPConnectionPoolLookup service select this service in ExecuteSQL processor.

Translate