How to connect multiple databases dynamically in apache nifi

Question

I want to connect multiple datasources based on some conditions, i don't want to create multiple DBCPConnectionPool services to connect each database, i just want to check if my attribute value is oracle, then i have to connect my Oracle database by passing Oracle configurations like url ,driver name,user name and password, if it MySQL then i need to dynamically pass MySQL related configurations, could you please help with processors which i can use.

I am using updateAttribute processor and putting some conditions like below :

Attribute: db.url
Value: ${datasource:equals('Oracle'):ifElse('Oracle-jdbc-url', 'Myslq-jdbc-url')}

Attribute: db.username
Value: ${datasource:equals('Oracle'):ifElse('oracle-username', 'mysql-username')}

Attribute: db.password
Value: ${datasource:equals('Oracle'):ifElse('oracle-password', 'mysql-password')}.

so i am not sure how to pass or read these values to DBCPConnectionPool

Answer 1

You can use the DBCPConnectionPoolLookup controller service for this. You set up DBCPConnectionPool instances for each database connection you want, then you add user-defined (aka dynamic) properties to DBCPConnectionPoolLookup where the name of the property is the value that the database.name attribute will contain, and the value of the user-defined property will be the corresponding DBCPConnectionPool instance.

For example, if your datasource attribute contains "Oracle" or "MySQL" or something, you can use UpdateAttribute to set database.name to ${datasource}, then create a DBCPConnectionPool for your Oracle connection and add a property named Oracle to the DBCPConnectionPoolLookup with the Oracle DBCPConnectionPool as the value.