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!
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.
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.
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
Hope this helps!
Here's the Redshift page on what @SeanAdams is describing:
https://docs.aws.amazon.com/redshift/latest/gsg/t_querying_redshift_system_tables.html
thank you @apathetichell - that is helpful!