Manage In-Database Connections
Create and manage your in-database connections from a central location. Use the Manage In-DB Connections window to view existing in-database connections and add new in-database connections.
There are two ways to get to the Manage In-DB Connections window:
Add a Connect In-DB tool, and select Connection Name > Manage Connections in the Configuration window.
Select Options > Advanced Options > Manage In-DB Connections.
Create a New In-Database Connection
1. Use the Data Source dropdown to select a source. Go to In-Database overview for more information.
Generic ODBC Option
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.
2. Use the Connection Type dropdown to select a type.
User: Create a connection only you can use.
System: Create a connection you can share. Open Designer as an admin on your computer. This option is only for Designer Admins.
File: Save a database connection as a .indbc file so you can package it with a workflow. If you select this option, you must specify a Connection File path location to save the file.
3. Use the Connections dropdown to select an existing connection from the list, or select New.
4. In Connection Name, enter a name for the connection.
5. Use the Password Encryption dropdown to select an encryption option:
Hide: Hide the password with 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 can fully run the workflow.
Encrypt for User: The specified user can run the workflow on any computer.
Allow Decryption of Password: Decrypt the password and pass it in the metadata. This option is only used in conjunction with In-DB predictive tools.
6. On the Read tab, use the Driver dropdown to select an option or leave it as default.
7. Select the Connection String dropdown arrow and select New database connection. For Oracle OCI and SQL Server ODBC connections, you can select a saved or recent data connection.
Note
Refer to ODBC and OLEDB Database Connections for more information on creating ODBC and OLEDB connections. Refer to Supported Data Sources and File Formats for more information on a specific data platform.
8. Select the Write tab.
9. Use the Driver dropdown to select a driver or leave it as the default.
10. In Connection String, enter or paste a connection string. For Oracle OCI and SQL Server ODBC connections, you can select a saved or recent data connection.
Connection Considerations
When you use SQL Server Authentication, enter passwords without SQL formatting standards.
We don't support 3 or more consecutive pipe characters in passwords.
Password treatment occurs automatically, so you shouldn't enclose a password in curly brackets or escape any right curly brackets used as a character in a password.
Enter passwords without Oracle formatting standards.
Do not use double-quotes as password characters.
You can choose to enclose the password in double-quotes.
To connect to HDFS...
1. Select the Connection Strings dropdown arrow, and then select New HDFS Connection.
2. Select HTTPFS, WebHDFS, or Knox Gateway server configuration. If you use Knox Gateway with Apache Spark, select Override default Namenode URL.
3. In Host, enter the Hadoop server URL or IP address.
4. In Port, leave the default port number, which is based on your server configuration selection, or type a port number.
5. By default, URL is based on Host. Enter a different URL, if desired.
6. By default, Temp Directory is /tmp. Enter a different location for the temporary directory you want to write to, if desired.
7. Enter a username in User Name and a password in Password. Required credentials vary based on the cluster setup.
httpfs: A username is required, but it can be anything.
webhdfs: A username isn't required.
Knox Gateway: A username and password are both required. Use a trusted certificate when you configure Knox authentication. Alteryx doesn't support self-signed certificates.
8. Select Kerberos and select an authentication option to read and write to HDFS:
None: Don't use any authentication.
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. It obtains those keys when you log in to Windows with your credentials. So the User Name and Password fields are unavailable.
The option you choose depends on how your IT admin configures the HDFS server.
9. (Apache Spark only) Select Override default Namenode URL to override the Namenode URL and enter a host and port number if you use Knox Gateway—or if the namenode server runs on a different computer than the HTTPS or webhdfs server.
10. (Recommended) Select Test to test your connection.
11. Select OK.
Refer to Hadoop distributed file system for more information.
12. If you connect to a database with multiple tables, the Choose Table or Specify Query window opens. Select the Tables tab. Refer to Choose Table or Specify Query Window for more information.
13. Select a table and select OK.
Edit or Delete an In-Database Connection
1. In the Manage In-DB Connection window, use the Data Source dropdown to select a source.
Generic ODBC Option
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.
2. Use the Connections dropdown to select an existing connection.
3. Edit connection details or select Delete to delete the connection.
4. Select OK.
Refer to Connect In-DB Tool to learn how to create and manage In-DB connections and use them in a workflow. Refer to Supported Data Sources and File Formats for more on the data platforms and databases you can connect to.
Alteryx also supports standard database connections. Refer to Manage Data Connections.