This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 02-12-201911:36 AM- edited
2 weeks ago
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.
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.
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.
In-database processing requires 64-bit Alteryx with 64-bit database drivers.
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.