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

Alteryx 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 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.
  • 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