Update mapping file

[#1]

Hello,

I have a mapping file that I use to get values from which I then pass into a ExecuteSQL processor. The file looks like this:

1 tag1,2016-01-01 13:01:01

2 tag2,2016-01-02 13:01:01

3 tag3,2016-01-03 13:01:01

4 tag4,2016-01-02 13:01:01

I have a counter and use ReplaceTextWithMapping to pull each of the values separated by "," and pass them as individual attributes to my SQL query with regex. What i'm looking to do is update the timestamp in this mapping file with the current time (which i'll later use for my next run as last run date). I can easily create a new attribute with ${now():format('yyyy-MM-dd HH:mm:ss')} but wondering what the best way to update the time field. If I replaceText and putFile i suspect it would replace my whole file with just that one line?

Thanks,

[#2]

Also just a note, inbetween the 1 and tag is \t

[#3 · Accepted solution]

@mliem you are correct in that it would replace the whole file. One option for you may be to use FetchFile first to retrieve the file of interest. Then use ReplaceText to append text to the end of it, and then use PutFile to overwrite the contents of the file, with the updated content.

[#4]

@mpayne How would you then get rid of the initial set of values? After I fetch file, i use replaceText with something like this:

${counter} ${TagName},${EndRun}

[#5]

@mliem I think I misunderstood a little bit what you are trying to do. I thought you were trying to just append something to the end of the file each time. But it looks like you're actually trying to replace the value. So instead of appending to the file, in ReplaceText you can use the Regex Replace "Replacement Strategy." For example, the Search Value may be something like:

(${count} ${TagName}),.*

with a Replacement Value of:

$1,${now():format('yyyy-MM-dd HH:mm:ss')}

So if the 'count' attribute is 3 and the 'TagName' attribute is 'tag3' you would replace '3 tag3,2016-01-03 13:01:01' with '3 tag3,2016-06-29 13:16:33:50' for instance.

Is this a little more along the lines of what you're looking for?

[#6]

@mpayne Perfect. this is exactly what I was looking for.

Yes I do maintain just 1 list of counts so I want to update not append and keep track of the last time it was run. Count, TagName always remain the same.

Just for some background (incase you know of a better way to handle this). I loop through each tag and pull the latest records from a sql db but each time I go through it there are some tags that I only need to pull every 3rd, 5th or 10th loop. I use one other field I didnt mention (priority) and I use RouteOnAttribute and modulus to see if I should run it that cycle. If the tag is part of the cycle I need to update the last run time so next cycle I can reference from that point. I did try looking into QueryDatabaseTable but had some issues that some other people were running into as well.

My new flow looks like:

FetchFile -> ReplaceText

Search Value: (${retry.counter}\t${TagName},${Priority}),.*), Replace Value: $1,${EndRun} - I create EndRun attribute earlier in the flow

->UpdateAttribute (for file name) ->Put file

Do you think there will be an issues with constantly having to fetch, regex replace and putfile? My list has counts that goes up to ~1200

Thanks

[#7]

Ah, thanks for the background! Whether or not you will have issues on fetching, replacing, and putting will depend on how big the file is, and how often this logic is happening. If it is 1200 iteration each time the logic runs and the logic runs every 24 hours then you will be fine. If it runs every 1 second then you may be hurting with this solution 🙂

A possibly better approach may be to use the ExecuteScript processor and write a simple Groovy or Python script that updates the file for you. Or you could potentially look at implementing some logic that would allow you to update the file only once at the end, if possible, instead of every one of the 1200 iterations, etc.