Tool or workflow if an input (redshift) table does not exist during that time

Question

Hi everyone,

First-time asking a question here. I have a redshift table that I use as an input table in my published workflow (scheduled in server), but this table gets updated (deleted and recreated) every hour. For a variety of reasons, I get a table does not exist from time to time -- and the error times vary. Is there a workflow I can add that will pause the workflow until this table is back up again (it takes 5-10 minutes for the table to be available again) OR a way to automatically rerun this scheduled workflow should an error occur?

Thanks in advance!

SeanAdams · Answer

thank you @apathetichell - that is helpful!

apathetichell · Answer

Here's the Redshift page on what @SeanAdams is describing:

https://docs.aws.amazon.com/redshift/latest/gsg/t_querying_redshift_system_tables.html

SeanAdams · Answer

Hey @kccruz

While I don't know the exact syntax for Redshift - the way to go about doing this is to read metadata.     This is where the DB Engine stores information about which tables exist and which do not.

On MS SQL Server this would be:

select

*

from

sys.tables

where

[name]  = 'TheTableINeed'

This query will tell you whether the table exists or not.

Now if you design your main canvas with Control Containers, or wrap it in macros - you can then base downstream activity on whether the table exists or not.

For example

* the first thing you do is look for the table.
* You then take your main workflow and wrap it in a macro or a control container 
* If you use a macro - Within that macro - you can programmatically disable the container that has all the input and processing tools that need this table to exist
* If you use a control container - same deal, just much simpler.

Hope this helps!