I have a lookup which is based on KV store. The lookup contains thousands of rows. We want to delete rows from this lookup which are older than 7 days.
To give an idea this is how the lookup looks like except that it contains thousands of rows.
We keep on appending new data to this lookup on a daily basis but we don't really care about data which are 7 days old and hence want to remove those rows.
How do I do remove the rows efficiently from this KV store based lookup. I know the classic way to do this would be to search for rows that are newer than 7 days and output the results to the same lookup. Something like,
inputlookup my_lookup | where date > now()-7 days | outputlookup my_lookup
But given that the lookup contains tons of rows, I would like to do this efficiently and remove just the old rows instead of writing the entire result set again.
This should be possible with KV store but I am unable to figure out how.
Thanks @rnowitzki for replying.
Is there anything apart from REST API to delete rows from KV store. I was looking for an approach where SPL could be leveraged.
Also, you are right about the -7d thing. I used it just to get my point across. In practice, I would have to use epoch time.
You might want to try the rest command. I never used it, but if you can call the uri that deletes the rows with the command, you could stay in the SPL world.
Let us know if it works.
Hi @iet_ashish ,
Besides the input-/outputlookup I am only aware of using REST. Check this thread for a solution.
You could run it as a cron maybe.
In the example they use LastUpdateTime with an exact epoch timestamp. I am not sure if you can use something like -7d.
If you have to give a unix timestamp, you could calculate it on the CLI and call the command in the crontable with a variable for the timestamp.