Dynamic Parameters

cpeterson10

• February 10, 2016 00:00

• Answered

Follow

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

  • February 15, 2016 03:19

  0

  Comment actions Permalink

  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
• 

  Lukas Cholasta

  • February 16, 2016 13:21

  0

  Comment actions Permalink

  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.

  • db_as_param.rar
• 

  cpeterson10

  • February 17, 2016 20:33

  0

  Comment actions Permalink

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

  cloverdm

  • February 19, 2016 05:53

  0

  Comment actions Permalink

  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.
• 

  Lukas Cholasta

  • February 25, 2016 14:52

  0

  Comment actions Permalink

  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.
  
  
  
  

  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:
  
  

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

  
  
  The above code is for MS-SQL.
  
  Best regards

Please sign in to leave a comment.