cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.

In-Database Processing FAQ

Alteryx Alumni (Retired)

Establishing Connections

Is the driver support the same for In-Database and standard Alteryx workflows?

With In-Database, driver support has been simplified in order to optimize for speed:

  • For 32-bit Alteryx, 32-bit drivers for SQL Server and Oracle are supported with In-Database.
  • For 64-bit Alteryx, 64-bit drivers for SQL Server and Oracle are supported with In-database.
  • 32-bit drivers are not supported for 64-bit Alteryx.

What permissions are required to initiate an In-Database workflow using the Connect In-DB tool?
Read privileges are required to access the underlying database.

What permissions are required to output data to the database using the Write In-DB tool?
Write privileges are required to create a table in the database.

What permissions are required to stream data into an In-Database workflow using the Data Stream In tool?

  • Either read-only or write privileges are sufficient when using SQL Server.
  • Write privileges to the GLOBAL TEMPSPACE directory are required when using Oracle.

What happens to the temporary tables created by the Data Stream In tool?
The temporary tables are deleted at the end of the run. If Alteryx crashes while the Data Stream In tool is being run, then the next time that an In-DB module, all temp files created by Alteryx in the db in the previous three days are cleaned out.

Are the underlying ‘rules’ (e.g., database time outs) maintained during an In-DB process, or does Alteryx modify or circumvent those?
The underlying ‘rules’ are maintained during the process the same as with the DB connections via the standard Input and Output Data tools. If there is a database timeout or if there is a limit to the number of queries per day that the user can run, it will affect the user’s connection to the database.


SQL Statement Generation

How is the SQL statement created?
A SELECT statement is triggered by the Connect In-DB tool and additional queries are created by downstream tools and nested within this query. The addition of one of the following three tools completes the query and sends it to the underlying database: Write In-DB, Data Stream Out, Browse In-DB.

A user can input his or her own SQL statement in the Query box for the Connect In-DB tool, which also gets embedded within the SELECT statement.

When is the SQL statement processed?
The SQL query for the underlying database is triggered at runtime for each Browse, Data Stream Out or Write Data tool.  

How is Browse Caching Enabled?
The Browse Data In-DB tool can be configured to cache the data as a .yxdb file when the workflow is run.

Once the data cached, if the workflow is re-run and the database connection or query (including the number of records to browse) has not changed, the query will not be re-run. Instead, the data will be pulled from the cache.


An output message indicates whether or not the data was cached. Clicking the link will open the data results as a .yxdb file in a separate window.

What are the differences between caching in the Input Data tool and caching in an In-DB workflow?
Using the “Cache data” option in the Input Data tool prevents redundant data transfer from a database to Alteryx, which can significantly reduce the time it takes to run a workflow.
Using the “Enable caching” option in the Browse Data In-DB tool prevents the workflow from sending redundant queries to the underlying database. The “Enable caching” option is on by default.


When is the In-DB cache used?
The In-DB cache is used any time a workflow is re-run without changes to upstream tools. Making a change to any upstream tool will trigger a new query and a new cache will be created.

Does the “Browse first [100] records” option impact the amount of data processed by the workflow?
No, the “Browse first [100] records” option only limits the number of records displayed in the Browse. Other tools in the workflow will process the number of records that pass through at any given point

For more information, please see the In-Database Overview in the Alteryx Help.

Huge thanks to Maureen Wolfson for the suggestion and passing this along, until next time!

Chad
Follow me on Twitter! @AlteryxChad 

Comments
Alteryx Alumni (Retired)

Chad, can you clarify how the cache behaves when the workflow or connection stays the same but the data changes in the table through an update of the record?

 

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