Clear Persistent Lookup

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

pfield
Posts: 13
Joined: Thu Mar 19, 2020 3:46 pm

Clear Persistent Lookup

Postby pfield » Sat Mar 21, 2020 3:34 pm

HI,

What is the correct way to clear and repopulate a persistent lookup?

I am using a shared persistent lookup as a method of locally storing a common set of mappings that need to be referenced by multiple graphs. However, when i get a new set of mappings that i need to upload it's very difficult to clear the existing values in the lookup and repopulate; i tried using the "Clear lookup table after finish" option in the LookupTableReaderWriter Component but this does nothing. The only method I can see is to physically delete the .db file which is stored - this often isn't possible as it seems they're held by some resources for a period of time so have to wait 5/10 minutes since the lookup was last used by a graph.

Am i using the lookup in the correct way? Is there a better way of approaching keeping local (not in a db) values for multiple graphs to reference?

Thanks,
Paul

dpavlis
Posts: 187
Joined: Sat Mar 10, 2007 8:12 pm

Re: Clear Persistent Lookup

Postby dpavlis » Thu Mar 26, 2020 6:18 pm

Hi Paul,

There is (unfortunately) no clear/direct way to remove all entries from Persistent Lookup Table. Your approach is what we would recommend - see this picture & notice the different phases of components:
image.png
image.png (171.34 KiB) Viewed 1501 times


However there might be a different way to accomplish what you are trying to do using other means of CloverDX. Perhaps if you explain a bit more what kind of mapping logic you keep in the lookup and how that needs to be shared among multiple graphs, we could suggest a solution.

PS: we will create a change request for our R&D to potentially look at purging Persistent Lookup.
David Pavlis
CloverCARE Support
CloverDX | Rapid Data Integration

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

pfield
Posts: 13
Joined: Thu Mar 19, 2020 3:46 pm

Re: Clear Persistent Lookup

Postby pfield » Thu Mar 26, 2020 8:06 pm

Thanks David!

This workaround is acceptable for now but yes it would be good if you had the option to purge the lookup.

Context is I have number of graphs which are run in a sequence and many of them need this information (it mainly contains details on a data model being used). I'm not using server so jobflows aren't an option, this means i tend to kick off lots of graphs manually one after another and don't want to have to repopulate a lookup each time.

Interesting you were able to delete the .db file immediately though - i'm not sure what's holding onto my files for a period of time which stops me from deleting straight away. Not a big problem though.

Paul

bartonv
Posts: 145
Joined: Wed May 03, 2017 12:10 pm

Re: Clear Persistent Lookup

Postby bartonv » Tue May 19, 2020 3:03 pm

Hi Paul,
just a thought on the issue with being unable to delete the .db file, could you confirm that you intend to delete the file in a later phase than the lookup table is being written into (as the screenshot provided by David indicates)? Similarly, can you confirm that the file is not "in-use" by any of your other graphs while you are trying to delete it?
Kind regards,
---
Vladimir Barton
CloverCARE Support
CloverDX

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

pfield
Posts: 13
Joined: Thu Mar 19, 2020 3:46 pm

Re: Clear Persistent Lookup

Postby pfield » Wed Jun 17, 2020 4:28 pm

Hi,

Use case is more at the beginning of the execution I want to repopulate the mappings. So if you imagine i run the graph (which in turn executes a series of other graphs synchronously) and i check the output, then decide I want to change something and run it again. In this scenario If I want to repopulate all my mappings again from their source i need to manually delete the .db files.

Hope that helps.

Thanks,
Paul

jandikovae
Posts: 50
Joined: Fri Nov 04, 2016 8:51 am

Re: Clear Persistent Lookup

Postby jandikovae » Thu Jul 09, 2020 8:17 pm

Hi Paul,

First, let me please recap the scenario. You run some sequence of graphs (presumably assured by RunGraph component in each parent graph) and when you want to run the sequence for the second time, without afore deleting the lookup files, the lookup stores invalid data (in general, twice the original ones), right?

As David mentioned before, in the Persistent Lookup the "Clear lookup table after finishing" doesn't work the same way as with the lookups that don't store their data in files. Therefore, you are right and the files have to be deleted manually (or using Server or Jobflow).

However, I have found a workaround for you suitable even without the Jobflow license. You should be able to use a component called SystemExecute and use it to execute a system command that would delete the files instead of you. My suggestion is to add this component to the very first graph in your sequence and the other components move to a later phase. See the picture:

systemExecuteGraph.PNG
systemExecuteGraph.PNG (17.05 KiB) Viewed 593 times

For example, my SystemExecute setups look as follows:
System command:
DEL C:\Users\Eva\workspace\Project1\data-tmp\lookupfiles.db
DEL C:\Users\Eva\workspace\Project1\data-tmp\lookupfiles.lg
Command interpreter:
cmd /c ${}

If you have a different OS then Windows, please take a look at the documentation for more information about the component or let us know as we can help. Also please let me know if I have misunderstood anything.

Thanks, Eva
---
Eva Jandikova
CloverCARE Support
CloverDX

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

pfield
Posts: 13
Joined: Thu Mar 19, 2020 3:46 pm

Re: Clear Persistent Lookup

Postby pfield » Thu Sep 10, 2020 11:18 am

HI Eva,

Just wanted to acknowledge and thank you for this reply.

I opted for a similar approach, though used a CustomJava component to clear files from that directory at the start each execution - though I like your solution better as it's less coding!

Paul


cron