Dynamic Parameters

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

cpeterson10
Posts: 3
Joined: Wed Feb 10, 2016 9:52 pm

Dynamic Parameters

Postby cpeterson10 » Wed Feb 10, 2016 9:56 pm

New to CloverETL. Is it possible to read a list of "stored values" from a DB table and use the values as parameters in Clover Job/Graphs?

Thanks!

CloverDM
Posts: 12
Joined: Wed Jan 27, 2016 12:22 pm

Re: Dynamic Parameters

Postby CloverDM » Mon Feb 15, 2016 4:19 am

We were initially in this situation ,unfortunetly we could not find a build in feature for this , Hence We developed our own custom etl plugin which is in Java , which reads a database config table and loads it into memory (Heap ) when the JVMs of clover etl serve boots up. Then use the CTL custom plugins to get the values which are stored in the Heap , So you can readily use them in your graph by calling that custom CTL function.

Support , If you have any in build functionality for this already materialized for coming releases please let us know .

Thanks

cholastal
Posts: 125
Joined: Tue Sep 01, 2015 1:22 pm

Re: Dynamic Parameters

Postby cholastal » Tue Feb 16, 2016 2:21 pm

Hi

You can use DB Lookup Table for this purpose. You can connect it to a database table and then just look up the values in a similar way as if they were parameters. You can also enhance this approach and read the data from the lookup table in the jobflow, pass it to the graph parameters and then you can access these parameters in ordinary way. You can also utilize a DBInputTable to pass the parameters via the jobflow. I attached an archive with two graphs and a jobflow, where the above is shown. However, in order to run the jobflow you need to run it in CloverETL Server project.

Hope this helps.
Attachments
db_as_param.rar
(4.22 KiB) Downloaded 199 times

---
Lukas Cholasta
CloverCARE Support
CloverDX

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

cpeterson10
Posts: 3
Joined: Wed Feb 10, 2016 9:52 pm

Re: Dynamic Parameters

Postby cpeterson10 » Wed Feb 17, 2016 9:33 pm

Thanks! Very helpful. It it possible to get a copy go the DB table you used in your example?

CloverDM
Posts: 12
Joined: Wed Jan 27, 2016 12:22 pm

Re: Dynamic Parameters

Postby CloverDM » Fri Feb 19, 2016 6:53 am

Yes , this is correct , but think abt the situation every single time the graph inititates the DB look up table has to be loaded ,
Would you think it is a good design when you handle million of records, rather you can load all these in memory when jvm bootstrap and use it in graph when ever you want.

cholastal
Posts: 125
Joined: Tue Sep 01, 2015 1:22 pm

Re: Dynamic Parameters

Postby cholastal » Thu Feb 25, 2016 3:52 pm

CloverDM wrote:Yes , this is correct , but think abt the situation every single time the graph inititates the DB look up table has to be loaded ,
Would you think it is a good design ...


The DB lookup table is not loading all the records from the database at the start of the graph. It works more like SQL interpreter and when you want to look up some values in the database, the DB lookup table sends a SQL request and returns the result. Additionally, it can store a defined number of returned results in cache and if you look up values that are already stored in that cache, the DB lookup table reads the values from the cache instead of sending another request. For more information, please refer to our documentation.



cpeterson10 wrote:Thanks! Very helpful. It it possible to get a copy go the DB table you used in your example?


I'm sorry, I'm not sure what you mean. If you want to test against the database used in the example, it won't be possible due to the fact it is behind firewall and NAT. If you want to create the table in your database, you can use the following code:

Code: Select all

CREATE TABLE test.dbo.params
(
  key_ nchar( 60),
  value nchar( 100)
);


The above code is for MS-SQL.

Best regards

---
Lukas Cholasta
CloverCARE Support
CloverDX

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


cron