community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

FAQ: How Do the In-Database tools Work?

Alteryx
Alteryx
Created on

In-Database tools FAQ

 

How do the standard Alteryx tools work when processing data from a database?

 

  • When processing data from a database, a standard Input Data tool downloads all of the specified input data to a temporary file on the local machine. All subsequent processing in Alteryx occurs locally. The download process has the potential to slow things down, especially when connecting to large tables in a database. The large input dataset (wherever it resides) has to be brought over the network to your computer and then processed.

 

How do the In-Database (In-DB) tools work?

 

  • In-Database processing enables blending and analysis against large datasets without the data moving outside of the database. In-DB tools make a connection to your database, and everything is processed in the database rather than bringing the data over the network to your local computer. Data will only be streamed out of the database and on to your local machine if you use a Data Stream Out or Browse In-DB tool. This can provide significant performance improvements over traditional analysis methods that require data to be moved to a separate environment for processing.
  • The In-DB tools are designed to allow you to visually build a SQL query which is processed on the database. Alteryx-specific functions will not work for In-DB tools such as Filter In-DB or Formula In-DB.

 

indb1.png

 

 

When would I want to use In-DB tools instead of standard tools?

 

  • An example when you might want to use In-DB tools instead of standard tools 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 temporary table in your database and then use the Join In-DB tool. 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.

 

 

indb2.png

 

 

 

What database permissions do I need to use the In-DB tools?

 

  • Read permissions are required to access the underlying database.
  • Write privileges are required to create a table in the database.
  • It is necessary to have CREATE permissions to be able to Stream in data to the database and to write data out to the database. The exception is Microsoft SQL Server.
  • To allow visibility of a temporary table across sessions, Alteryx must create a permanent table that is eventually deleted at the end of a workflow.

 

Additional Information:

 

  • In-database processing requires 64-bit Alteryx with 64-bit database drivers.
    • List of all data sources here.
  • You cannot cross databases with In-DB as there will be an architecture mismatch.
  • Spatial objects are currently not supported with In-Database tools.
  • There are six predictive tools that have in-database support. When a predictive tool with in-database support is placed on the canvas with another In-DB tool, the predictive tool automatically changes to the In-DB version. To change the version of the tool, right-click the tool, point to Choose Tool Version, and click a different version of the tool. You can find a complete list here.

 

For more answers, please see our Help Documentation's Frequently Asked Questions

Comments
Meteoroid

nice FAQ, I referenced it in my blog post as I was doing a quick evaluation of In-Database with Big Data engine Impala.

http://boristyukin.com/quick-evaluation-of-alteryx-in-database-tools/

Alteryx Partner

This is well written and helpful.

The part I still can't find is the underlying sql code for the Write In-DB tool... If you use the dynamic output tool afterwards, there is no detail of what action was performed in SQL...

If anyone knows...

 

Cheers,

Frederic

Meteoroid

@DiganP wrote:

In-Database tools FAQ

 

...

 

How do the In-Database (In-DB) tools work?

 

  • In-Database processing enables blending and analysis against large datasets without the data moving outside of the database. In-DB tools make a connection to your database, and everything is processed in the database rather than bringing the data over the network to your local computer. Data will only be streamed out of the database and on to your local machine if you use a Write Data In-DB, Data Stream Out or Browse In-DB tool. This can provide significant performance improvements over traditional analysis methods that require data to be moved to a separate environment for processing.

 


You mention that Write Data In-DB would stream the data out of the database into your local machine.  Is that correct?

The link here suggests the opposite: Write Data In-DB

 

If Write would stream the data out then back in, I would see no reason to ever use the In DB tools.

 

Appreciate any feedback you can give!

 

Thanks!

Alteryx
Alteryx

@YEM Good catch. What I meant to say is that the addition of one of the three tools (Write Data In-DBData Stream Out or Browse In-DB tool) completes the query and sends it to the underlying database. I'll fix this asap!

Atom

Is it possible to use the  Write Data In-DB to write a dataset to "temp" table and then use some other IN-DB tool to execute a complex SQL statement that can use the "temp" table?

 

for visual reference this would be ideal.

2019-04-18 22_05_32ALXDST.png

I am aware of the In-DB join, however, this is a simplistic example. In reality I need to run a complex sql statement, which can perform a join as in the example. 

currentALXDST.png

The final output would be writing the blended data(claimStats.yxdb and InvoiceStats.yxdb) into a SQL table or generating some sort of report. I can't figure out how to make Container 37 dependant on Container 36 completing first. The "Block Until Done" doesn't appear to be able to link to the "Input Data" tool.