Hi Matt, I don't think I need a pool or a cache, since DB connection will be used once for an object (table/view). So I think that won't be a problem create a DB connection, read object and destroy connection, for each object.
I'll try to implement this using DBCPService Controller Interface. Thanks for your consideration. Eduardo Fontes On Tue, Mar 5, 2024 at 11:10 PM Matt Burgess <mattyb...@apache.org> wrote: > Eduardo, > > It doesn't sound like DBCPConnectionPoolLookup will work for you because of > all the different connection strings. I don't know if there's a good reason > why we couldn't create the BasicDataSource when getConnection() is called, > passing in a Map of FlowFile attributes (that's how the Lookup version > works). One issue I do see is with "churn" if we're recreating the data > source each time. At that point it's not pooling connections. I suppose you > could have an internal cache of data sources but it would have to be > bounded and/or configurable and have a least-recently-used (LRU) eviction > strategy. > > DBCPService is the name of the controller service interface that the > database processors use, but that's a misnomer since the API doesn't > mention pooling specifically. Instead you could have an implementation that > uses a cache vs a pooling approach. But Apache DBCP does handle a lot of > the management (validation, eviction, idle timeouts, etc.) so unless > there's no way to avoid the potential memory/performance issues (like > having 50+ controller services in a PG) you could try to wrangle smaller > pools per data source and cache those if that's ok for your use case. > > My two cents, > Matt > > On Tue, Mar 5, 2024 at 7:25 PM Eduardo Fontes <eduardo.fon...@gmail.com> > wrote: > > > Hi Everybody! > > > > I'm thinking about make a generic ingestor with Apache NiFi but I found > > some difficulties because of the DataBase Connection Pool controller. It > > doesn't accept flowfiles parameters for its properties, specially > > connection string, username and password (for security reasons, some > > sensitive parameter name instead password itself). > > > > This is important because, as a generic ingestor, I might have hundreds > of > > different connection strings, and I had a lot of problems when I tried to > > put 50 DBCP controllers in a Process Group. > > > > I wouldn't like to create a flow for each ingestion, but one flow for > each > > database vendor. > > > > Does anyone have any suggestions on how I can achieve this? Would it be > > easy to create a parameterized DBCP controller? (That I could do it > myself) > > > > Best regards. > > > > Eduardo Fontes > > Data Eng / System Analyst Sr. > > >
