Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

In-DB Query and the Dynamic Output tool!

Alteryx
Alteryx
Created

Question

Have you ever wondered what query was being sent to your database at a certain point in your workflow?

Answer

You can view this using a Dynamic Output In-DB tool! All you have to do is place a Dynamic Output tool after any tool in your In-DB process in order to see the query that is being sent to the database. Once the tool is placed in the desired spot in your workflow, make sure that you have the "Query" option selected in the Dynamic Output tool's configuration:

 

Capture.JPG

 

You can now run the workflow and since the output tab of the Dynamic Output tool is a normal connection tab, you will be able to view the query using the browse everywhere feature and the results window. If you are interested in viewing the entire query and/or copying and pasting it you will have to add a browse tool after wards so you can access the query string in the Cell Viewer:

 

Capture2.JPG

 

A "fun fact" with this process is that you can take the query that is produced by the Dynamic Output tool and paste it into the SQL Editor of a normal Input tool to produce the same results:

 

Capture3.JPG

 

Capture4.JPG

 

 

Comments
5 - Atom

Is there a way to dynamically change the where clause in the tool 'Connect in-DB' like that of the 'Dynamic Input' under Developer tab? If using 'Dynamic input' tool is the only way out, then I find that Dynamic input takes much longer and hence not convenient. The In-DB tools have much higher running speed.

Alteryx
Alteryx

Hi Abinaya, There isn't a "out of the box" tool that can do this, but you could build a batch macro to update a where clause in the Connect In-DB tool. For example, I have an In-DB process connected to a table that has every city by state in the US. My where clause is initially searching for just the City of Glen Ellyn:

Connect.PNG

 

What you can do is turn this into a batch macro with the action tool updating the portion of the where clause you are interested in. In my case, just the city name:

ACtion.PNG

 

The downside to this is that you will have to stream your data out of the In-DB tools. The In-DB macro output tool can only be used in a standard macro. So to use this in a batch macro, you will need to use the data stream out tool and connect a macro output. An Alternative would be to have your batch macro contain your entire process so that you can keep it In-DB.

 

Once I have completed the macro, I can insert it into a workflow. In my workflow I have provided a list of Cities that I was to update my initial where clause with:

workflow1.PNG

 

Once run, the list of Cities I provided updated the original city listed in the where clause and the output data shows the results for each of the newly provided cities:

Results.PNG

 

5 - Atom
Hi Mike,

Thank you for your help!
I found that running the SQL query as many times is taking quite a while and therefore I am excluding the where clause and extracting the entire dataset.
Later on, I am doing an inner join (In-DB) to work like a where clause. I found this takes lesser run-time.

Thanks, much appreciated!
Abinaya
5 - Atom
Hi all: Is the answer to Mike's question (two posts above) still to create a batch macro? SR
Alteryx
Alteryx

@SR The Dynamic Input In-DB tool will do this.  It accepts the Connection Name and Query as Input so the query could be manually constructed using any of the regular Alteryx tools like a formula tool and then passed to the Dynamic Input In-DB tool.  Here's a screenshot of an example.  I haven't modified the where clause here but you could easily do that with a Formula tool.

 

2016-12-13_7-53-02.png

 

 

 

 

5 - Atom
@AndyM, Thank you! This is exactly what I needed
7 - Meteor

Hi Andy,

 

In your screen shot, I see you have connected your tool Dynamic input in- DB with File input. I am trying to connect similar way to a file and then connect the output to a DB join operation ( with the output from a DB table data source) but I could not do it, please share your thought if this is possible to do. Why I am doing is that, I dont want to create a temporary table in DB ( assume I dont have write previlege in DB) , in that case i want to join data in csv with another table from database and create the output. The workflow is giving error as "unable to find connection-2" .

ScreenShot.PNG