Connect In-DB Tool
Connect In-DB Tool
Use Connect In-DB to create an in-database connection in a workflow. Use the tool to connect to a new or existing connection.
In-Database enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods. For more about the In-Database tool category, visit In-Database Overview.
Configure the Tool
In the Configuration window, select the Connection Name dropdown and select an option:
Manage Connections: Create a new connection or use an existing connection.
Open File Connection: Browse to a saved database connection file.
Once the connection is configured, Table or Query displays the name of the selected database table.
(Optional) Select Query Builder to select tables and construct queries. Go to Choose Table or Specify Query Window for more information about this topic.
Add a New In-DB Connection
Select the Connection Name dropdown arrow and select Manage Connections.
Select Data Source and select a source. Visit In-Database Overview for more information.
Note
From the Connect In-DB tool, you can select the Generic ODBC option to attempt a connection to an unsupported data source. This option does not guarantee a successful connection to unsupported data sources; however, data sources that are similar to Microsoft SQL Server have the best chance of success.
Select Connection Type and select type.
User: Create a connection that only you can use.
System: Create a connection that can be shared. Open Alteryx Designer as an administrator on your computer. This option is only for a Designer Admin.
File: Saves a database connection as an INDBC file so it can be packaged with a workflow. If this option is selected, a Connection File path location must be specified in order to save the file.
Select Connections and select an existing connection from the list or select New.
In Connection Name, enter a name for the connection.
Select Password Encryption and select an encryption option:
Hide: Hide the password using minimal encryption. If you plan to schedule this workflow to run on any machine other than your computer, select Hide. Visit Schedule Workflows for more information.
Encrypt for Machine: Any user on the computer will be able to fully use the workflow.
Encrypt for User: The signed-in user can use the workflow on any computer.
Allow Decryption of Password: Decrypts the password and passes it in the metadata. This option is only used in conjunction with In-DB predictive tools.
On the Read tab, select Driver and select an option or leave as default.
Select the Connection String dropdown arrow and select New database connection. For Oracle OCI and SQL Server ODBC connections, you can alternately select a saved or recent data connection.
Go to ODBC and OLEDB Database Connections for more information on creating ODBC and OLEDB connections.
Go to Supported Data Sources and File Formats for more information on a specific data platform.
Select the Write tab.
Select Driver and select a driver or leave as default.
In Connection String, enter or paste a connection string. For Oracle OCI and SQL Server ODBC connections, you can alternately select a saved or recent data connection. HDFS Connections To connect to HDFS:
Select the Connection String dropdown arrow and select New HDFS Connection.
Select HTTPFS, WebHDFS, or Knox Gateway server configuration. If you are using Knox Gateway with Spark, select Override default Namenode URL.
In Host, enter the Hadoop server URL or IP address.
In Port, leave the default port number which is based on your server configuration selection, or enter a port number.
By default, URL is based on Host. Enter a different URL, if desired.
By default, the Temp Directory is /tmp. Enter a different location for the temporary directory to write to, if desired.
Enter a user name in User Name and a password in Password. Required credentials vary based on the cluster setup.
httpfs: A user name is required, but it can be anything.
webhdfs: A user name is not required.
Knox Gateway: A user name and password are required. Use a trusted certificate when configuring Knox authentication. Alteryx does not support self-signed certificates.
Click Kerberos and select an authentication option for reading and writing to HDFS.
None: No authentication is used.
Kerberos MIT: Alteryx uses the default MIT ticket to authenticate with the server. You must first acquire a valid ticket using the MIT Kerberos Ticket Manager.
Kerberos SSPI: Alteryx uses Windows Kerberos keys for authentication, which are obtained when signing in to Windows with your Windows credentials. The User Name and Password fields are therefore not available. The option you choose depends on how your IT admin configured the HDFS server.
(Spark-only) Select Override default Namenode URL to override the Namenode URL and enter a host and port number if using Knox Gateway, or if the namenode server is running on a different computer than the httpfs or webhdfs server.
(Recommended) Select Test to test the connection.
Select OK.
Note
Go to Hadoop Distributed File System for more information. Go to Manual Connection Setup for more information on authentication requirements.
Select OK.
If you are connecting to a database with multiple tables the Choose Table or Specify Query window opens. Select the Tables tab. Go to Choose Table or Specify Query Window for more information.
Select a table and select OK.
Use an Existing In-DB Connection
Select the Connection Name dropdown arrow and select Manage Connections.
Select Data Source and select a source.
Note
From the Connect In-DB tool, you can select the Generic ODBC option to attempt a connection to an unsupported data source. This option does not guarantee a successful connection to unsupported data sources; however, data sources that are similar to Microsoft SQL Server have the best chance of success.
Select Connections and select an existing connection from the list.
Select OK.
You can also edit connection details and select Apply. Go to Manage In-DB Connections for more on managing existing In-DB connections.
Note
Go to Manage In-DB Connections to learn how to manage In-DB connections.
Connect In-DB and Advanced Query Options for Pre/Post-SQL
The 2025.1 Designer release introduces pre/post-SQL support for the Write Data In-DB, Data Stream In, and Data Stream Out tools. However, this option is not available via the Connect In-DB tool.
The reason is that the Connect In-DB tool primarily makes a call to get the list of tables. The real queries are run in the Write Data In-DB, Data Stream In, and Data Stream Out tools. When a Pre-SQL statement is added to the Output tool, that statement runs before the Select Table of Connect In-DB.