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

[kccruz]

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!

[ArnaldoSandoval]

Hi @kccruz 

Based on the intermitence of the error you are receiving, it seems your workflow occasionally can't find the redshift table, probably because it is being recreated (by delete-recreate actions outside your workflow), here you are trying to synchronise two process, your Alteryx workflow with the redshift process, that by itself is very challenging, there are option within Alteryx to implement this sort of "wait unti" scenarios, but nothing to synchronise two different processes running under two different applications. Let us know if the redshift process can write a tag record either to an external file or a second table indicating the table is ready to be read.

hth

Arnaldo.

[kccruz]

Thanks for your response. This is one of the options I am considering. I'll discuss this with our tech team next week and update you.

[SeanAdams]

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!

[apathetichell]

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]

thank you @apathetichell - that is helpful!