Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Running multiple SQL update queries in a specific sequence

Niall_R
6 - Meteoroid

Hi all,

 

I’m looking for advice on the best approach for triggering a series of SQL update statements on snowflake (one part of a wider process where we use more diverse Alteryx functions). These SQL statements don’t require data to be input or output, so we really just need to execute them in sequence, with some control on order and dependencies. I know you can use pre/post SQL on an input or output tool, but these can’t, as far as I know, be chained together. General requirements:

 

  • Stays as close to pure SQL as possible, for easier updating of queries created elsewhere;
  • Update statements can be chained together with flow control and dependencies;
  • Low maintenance;
  • Relatively easy to understand the flow visually

Some options I’ve considered:

 

1. Using standard in-db tools to select, transform and write back data – not ideal for transfer-ability to/from other tools, and since full SQL statements already exist which could theoretically be executed in a single tool. The example below would equate to a single update statement, but dozens are required.

2019-06-04_16h38_37.png

 

2. Using the ‘run’ tool with snowsql CLI tool to run SQL stored in text files – possibly the best option, but a bit fiddly? Workflow would look a bit like option 5, but with 'run' tool instead of custom macro


3. Chaining together output tools, using write to a dummy table, with the SQL Update query stored in the pre or post-SQL, like below

2019-06-04_11h43_58.png

 

4. Using the CREW runner macros to run workflows one after another. This would require creating too many separate workflows, and reduce direct transparency


5. Creating a chain-able macro with one SQL input and one ‘trigger’ input, plus a success/fail output that is just dynamically setting the Post SQL on an output node (like the CREW conditional runner macro). Please ignore the errors on my mock up below:

 

2019-06-04_16h41_16.png

Do these approaches make sense, or is there some vastly simpler way that I’m overlooking? I’d love it if Alteryx could provide a single ‘Run SQL’ tool, but this doesn’t seem to exist.

 

Thanks in advance!

5 REPLIES 5
koppenhe
7 - Meteor

Have you considered using a list runner macro? Below is a link to a previous discussion:

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/List-Runner/m-p/146703

Niall_R
6 - Meteoroid

Hi @koppenhe, yes that's what I was trying to allude to with option 4 and we use it for some other scenarios, but it would require the creation of many different workflows in this instance. It also doesn't easily allow the developers to step into each one to see the command that is being run. The other options mostly have the advantage of being able to view the SQL and where it fits in the overall sequence and dependencies.

Niall_R
6 - Meteoroid

I've ended up going with something like the last option - creating a macro to which I can pass a simple SQL query, and then chaining these together. I've also voted for this idea:

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Have-an-SQL-Tool/idi-p/5392 

Niall_R
6 - Meteoroid

I've ended up going with something like the last option - creating a macro to which I can pass a simple SQL query, and then chaining these together. I've also voted for this idea: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Have-an-SQL-Tool/idi-p/5392 

j4jackycheng
7 - Meteor

Hi Niall_R

 

I have been using Alteryx for half a year now, and i have been searching for the answer for the same question since day 1.

And luckily i found your post today.  The problem with Alteryx community is that it's very difficult to learn stuff if i don't know the the exact Alteryx terminology to search for.


Currently, i use Option 3 when i needed to do this, but i found it becoming overly messy if i have many steps to be chained together.


I just read the "Idea" link you posted, can't believe the idea is still under review under 5+ years.

Previously I used to use Pentaho and find it much more easier to achieve this.  ( I need to use Alteryx now as it's my company's choice)

 

Cheers,

Jacky

Labels