Input Data vs Connect In-DB

[smixon]

Hi Community,

I'm new to Alteryx and this may be a real simple question, but I haven't found anything (at least in the five or so minutes I've been looking).   What is truly the difference between connecting to a database (Oracle in this instance) with the "Input Data Tool" and using the SQL editor with a pre-written script versus using the "Connect In-DB" tool with the same database and pre-written script?

For this example, let's say I do something really simple like execute the query and export it to an Excel workbook.  I know that you get other functions with the In-DB tools if you start with the "Connect In-DB" tool, but I'm just wondering from an input perspective if there is a difference.

Thanks!

[AmeliaG · Accepted solution]

Hi @smixon,

Thanks for your question! 

In your use case, there is very little difference. Whether the SQL query runs in the input data tool or in the Connect-in DB tool, it will be executed on the database. The only difference is if the data is returned over the network. With the regular input data tool, the data will be automatically streamed over the network into local memory on your computer. With the Connect-in DB tool, the data will only be streamed out of the database if you use the 'Data Stream Out' tool. 

Therefore, if you simply want to output to an Excel sheet, the two workflows would look like:

Both of these workflow are effectively doing the same thing. 

[smixon]

Awesome! Thanks for the reply!

[AmeliaG]

To add a bit more color, all of the regular Alteryx tool (i.e. NOT In-DB) are processed in memory on the machine where you are running Alteryx. 

The In-DB tools were designed to allow you to visually build a SQL query which is processed on the database. An example where you might want to use In-DB would be if you have a huge table in Oracle and you want to blend it with a small Excel file and then write a new table in Oracle. You can use the In-DB tools to stream the Excel data into a temp table and then do the join In-DB. This would reduce the overall data movement over the network and optimize your run time. Of course, if your endpoint is a different database or an Excel file, you will need to stream the data out of Oracle regardless. 

Hope this helps!

Amelia

[tennetiharika4]

I think thats a fantastic explanation.Thanks

[TravisCraven]

I know this is an older post but this may continue to be a question that comes up. 

Why do I get alias errors when I try to publish to Gallery, regardless of Input tool or In-Data connection?

After working through both scenarios with Alteryx Support it was recommended to us not to try to publish local data connections, either Input or In-Database. 

For Input data connections the solution was to create the connection on the Alteryx Gallery Admin, then point the source to that company Gallery connection.

For In-Database connections, the solution is to create a file connection saved locally. Once published the connection will stay with the workflow even if a user downloads the workflow without having the connection saved locally. If that user accidentally saves the workflow locally they will sever the gallery link and have to re-download or create their own file connection to publish with. 

Hopefully that helps if other people have errors and come across this post. 

Travis

[wcmaas]

Hi Amelia, 

Any advice when the In-DB Processing Tool is returning an error for my SQL query being too long?

Thanks in advance

- Will

[vball]

This is such an old post that I don't know if this is still useful. But I found that sometimes the SQL query is not written in the most restricted "grammar" and Alteryx does not like it. If you use other SQL tools like SQL Developer, some "grammar" issues are ignored and the SQL can still run just fine but Alteryx seems to be a stricter teacher:)