DBCP Controller Services for entry-level users

Goals

Create DBCP services to relieve entry-level users of more complex configurations. The controller services should

• not bundle any JDBC driver to avoid increasing NiFi binary size and to prevent licensing issues (e.g. we cannot provide MySQL or Oracle drivers)
• be vendor-specific for easier maintainability and better user experience
• support a selectable set of driver versions
• download the specified driver from an external source and also handle archives
• cache downloaded drivers
• exempt the user from specifying the driver classname and driver URL
• support TLS and sensitive dynamic properties
• document supported server versions

Background and strategic fit

Currently DBCPConnectionPool is used for connection pooling. The controller service configuration requires to provide the JDBC driver jar, URL and classname which may be difficult for new users. The new controller services would prevent users from needing

to search for JDBC driver, download them manually, provide the jar file path, the JDBC URL and the driver classname.

Technical details

For DBCP service Apache Commons DBCP will be chosen over HikariCP since it is more widely used in NiFi.. Currently DBCPConnectionPool is used for connection pooling. The controller service configuration requires to provide the JDBC driver jar and classname. The new controller services would extend DBCPConnectionPool and provide the specific driver.

The new Controller Service must handle downloading the driver from an external source, unarchive it if needed and load it. TLS parameters must be supported through SSLContextService and Sensitive Dynamic Properties. For every major vendor a

new service shall be created; vendor-specific controller services are for easier maintainability and better user experience.

Assumptions

• Users have internet access - without internet access the drivers cannot be download and DBCPConnectionPool shall be used

User interaction and design

Users could have two primary methods for getting a JDBC driver.

1. Use the existing DBCPConnectionPool - In this case the user must provide the driver location and driver classname 
2. Use the new Controller Services - Select the database version their using and the service downloads the corresponding driver

The internal could work as follows:

On enabling the new controller service it verifies if the driver for the specified version is already downloaded at a location. If so, it loads the driver, otherwise it downloads it.

The driver classname will be stored internally and automatically provided.

Technical Ideas for the Controller Service and driver support

1. In case the driver is not supported by PostgreSQL use DBCPConnectionPool and set you own driver
• it does not relieve entry level users from less configuration if their driver is not supported
2. Store previous JDBC drivers in NIFI when upgrading
• The NiFi binary is already large, it should not be increased
• Bundling drivers come with licensing issues (Oracle and MySQL cannot be bundled)
3. Load drivers lazily on the fly
• Would not fit customers with no access to the internet
• Caching should be supported
• Nexus may be used for remote NAR loading
4. New version of the service for each major version of the database
• We only upgrade drivers to the latest compatible driver with the specified version (e.g. Postgres15DBCPConnectionPool)
• We end up with old versions
• Change approach to versioned components; use provider components to change version
5. Resource loader
• use existing DBCPConnectionPool and introduce a new Driver Provider Service
• too complicated for entry level users

Questions

Below is a list of questions to be addressed as a result of this requirements document:

Not Doing