CloverETL is now CloverDX - Learn Why

Executing against multiple databases (outside Clover Server if possible)

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

vinny_howard
Posts: 3
Joined: Wed Aug 08, 2018 8:32 am

Executing against multiple databases (outside Clover Server if possible)

Postby vinny_howard » Wed Aug 08, 2018 8:44 am

Hi
Clover Newbie here. I have gone through the Quick Start Guide, and read through a lot of the documentation, but I can't find a way to do what I want.
I have dozens of separate SQL databases, all with different hosts and username/passwords. I have a couple of Clover Graphs that I want to run against each database. So I am looking for some way to parameterise this - essentially to loop through a list of database names/hosts/username/passwords, and find some way to feed that to the DB Connection property on a DBInputTable component, and execute each Graph against each database, and create different CSV files for each execution. I don't see any way to do this with a Lookup or a Parameter. Is there any way to do this outside of Clover Server, which I don't have?
thanks
Vinny

vazquezrosariop
Posts: 140
Joined: Mon Feb 29, 2016 5:33 pm

Re: Executing against multiple databases (outside Clover Server if possible)

Postby vazquezrosariop » Tue Aug 14, 2018 2:51 pm

Hi vinny,

Unfortunately, for the specific use case you are describing a CloverETL Server is the only way possible. The DbInputTable is designed to only accept one database names/hosts/username/password in the connection per graph execution. The only way to accomplish your use case would be to build a Jobflow that would create multiple graph executions per database by making use of Parameters (which unfortunately can't be changed during graph execution).
---
Pedro X. Vazquez Rosario
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com

vinny_howard
Posts: 3
Joined: Wed Aug 08, 2018 8:32 am

Re: Executing against multiple databases (outside Clover Server if possible)

Postby vinny_howard » Tue Aug 14, 2018 4:10 pm

Thanks Pedro, I suspected that might be the case.
cheers
Vinny

vinny_howard
Posts: 3
Joined: Wed Aug 08, 2018 8:32 am

Re: Executing against multiple databases (outside Clover Server if possible)

Postby vinny_howard » Wed Aug 15, 2018 3:17 pm

Hi Pedro
Just to clarify - if I were to use Clover Server, how would I set this up? Would I use a Jobflow, and have a FlatFileReader component to read the multiple rows of connection information from a CSV, and could that then be used to send the connection information into a Graph, to be picked up by the DBInputTable component? I'm playing around with the ExecuteGraph component but I can't see how to structure this to have one graph being run against multiple databases.
thanks
Vinny

vazquezrosariop
Posts: 140
Joined: Mon Feb 29, 2016 5:33 pm

Re: Executing against multiple databases (outside Clover Server if possible)

Postby vazquezrosariop » Thu Aug 16, 2018 1:32 pm

Hi Vinny,

That is exactly how I would recommend setting up such a use case. I would create Parameters (ex. Username, Password, Host) in the main graph that contains the DBInputTable and map them using the ExecuteGraph Component. You will also need to use Dynamic Metadata to account for the changes in table structure. Please note that in the documentation that you need to specify a SQL query for the Dynamic Metadata to work properly, this value can be parameterize as well.
---
Pedro X. Vazquez Rosario
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com