Alteryx Designer Knowledge Base

This article details different methods for connecting to an Oracle database from Alteryx. 

How To: Tell a Database connection type at a glance   Below is a quick overview of how the different types of database connections appear within "workflow dependencies." This will help determine and compare different connection types from a quick glance.   Prerequisites   Product - Alteryx Database connection(s)   Procedure   To view connections in the Workflow Dependencies, go to Options > Advanced Options > Workflow Dependencies. (The same notes apply for connections viewed through the Input/Output tool configurations except for the Gallery Connection).     1. Saved Data Connection Saved Data Connections can be recognized by the aka: at the beginning of the Connection String. They include all connections the user created through Options > Advanced Options > Manage Data Connections as well as Oracle and SQL Server connections created through the "Quick Connect" option. Also see https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Database-Connections-Creating-an-alias-and-the-advantages-of/ta-p/15836 Saved Data Connections are saved in xml files on the user's machine with passwords encrypted. %AppData%\Roaming\Alteryx\Engine\UserAlias.xml contains user connections and %ProgramData%\Alteryx\Engine\SystemAlias.xml contains system connections.   2. In-DB Connection In-DB Connections show the connection name the user picked when setting up the connection. All In-DB Connections are saved with encrypted passwords either in %AppData%\Roaming\Alteryx\Engine\UserConnections.xml for user connections or %ProgramData%\Alteryx\Engine\SystemConnections.xml for system connections.    3. In-DB Connection using a file In-DB connections using files point to the file storing the connection information. You can recognize these by the file extension .indbc. They also include the path to the .indbc file.   4. Gallery Connection Gallery connections are created by the Gallery admin and then shared with the user so they can use them on their local machine. They start with aka: like regular saved data connections. When viewed in the Input tool, you can see the connection name. In the workflow dependencies they show up as the key used to identify them in MongoDB.    5. DSN-less ODBC Connection Any connection using an ODBC driver starts with the keyword odbc: For a DSN-less connection, all relevant connection information is stored in the connection string.    6. ODBC w/DSN An ODBC connection using a DSN on the user's machine starts with the keyword odbc: then points to the DSN name. Additional parameters may be added to the connection string, such as username and password, they will override the information for the connection parameters stored with the DSN.    7. OleDB Connection An OleDB connection starts with the keyword odb: and contains all relevant connection information in the connection string. When selecting OleDB as the connection option in Alteryx, a wizard helps build the connection string.    8. Oracle OCI An Oracle OCI connection starts with the keyword oci: and requires a tnsnames.ora file.  By default, the  tnsnames . ora  file is located in the ORACLE_HOME/network/admin directory.   9. 32-bit Connection 32-bit connections can be made as long as a 32-bit driver is available (For ODBC, OleDB, Oracle clients). They can be recognized by the 32bit: keyword BEFORE any other keywords indicating the type of connection to use.    10. (SQL Server) Bulk connection Bulk connections have their own prefixes to let Alteryx know to expect a bulk connection, e.g. ssvb indicates a SQL Server Bulk Loader connection. They may have additional parameters added to the connection string.       Additional Resources   How To: Connect to an OleDB Data Source Connecting to an ODBC Datasource How To: Create an In-Database Connection

How To: format common ODBC DSN-less connection strings   Many people need to make ODBC connections to external data, but they may be unsure of how to format their connection strings, particularly with DSN-less connections. Here is a reference guide with examples of common DSN-less ODBC connection string formats. Here is a reference guide with examples of common DSN-less ODBC connection string formats. For connection strings not covered below, information can be found through the database or driver documentation or through online resources such as connectionstrings.org.   DSN-less connection strings help make workflows easier to export to other computers, as the importing computer does not need a matching DSN configured in the ODBC Data Source Administrator.   Prerequisites   ODBC driver for external data source such as SQL Server, Cloudera, or Hadoop    Products   Designer, Gallery   Procedure   DSN-less connection strings typically include: 1. the driver name, 2. server address, 3. database name or port number, and depending the authentication type, 4. user id and password.   Standard ODBC DSN Connection string format   odbc:DSN={DSN_Name};UID={Username};PWD={Password};   DSN-less example   odbc: Driver={SQL Server Native Client 11.0}; UID={Username}; PWD={Password}; DATABASE={Database_name}; SERVER={Database_Host}   DSN-less trusted connection, the id and password are taken from the driver configuration   odbc: Driver={SQL Server Native Client 11.0}; DATABASE={Database_name}; SERVER={Database_Host}; Trusted_Connection=yes   ODBC Connection string formats for Hadoop and Cloudera platforms   There are separate Hadoop and Cloudrea platforms and one where both are combined into one. These platforms use the same ODBC driver types: Hive (data warehousing), Impala (massively parallel processing), and Spark (performance based processing).   For DSN connections there is only parameter: DSN=[DataSourceName] (that's it).    In DSN-less connections for Cloudera and Hadoop, the driver, host, and port are all included in the connection string along with the authentication mechanism.   The authentication mechanisms for these driver types are the same (AuthMech in the connection string). 0 = No Authentication 1 = Kerberos 2 = User Name - the UID may be omitted with anonymous login enabled 3 = User Name And Password   No Authentication   Driver=Simba Hive|Impala|Spark ODBC Driver;Host=[Server]; Port=[PortNumber];AuthMech=0;   Kerberos   Driver=Simba Hive|Impala|Spark ODBC Driver;Host=[Server]; Port=[PortNumber];AuthMech=1;KrbRealm=[Realm]; KrbHostFQDN=[DomainName];KrbServiceName=[ServiceName];   User Name   Driver=Simba Hive|Impala|Spark ODBC Driver;Host=[Server]; Port=[PortNumber];AuthMech=2;UID=[YourUserName];   User Name and Password   Driver=Simba Hive|Impala|Spark ODBC Driver;Host=[Server]; Port=[PortNumber];AuthMech=3;UID=[YourUserName]; PWD=[YourPassword];   Optional parameters that may need to be added to a connection string if configured are: ServerType, SSL, and ThriftTransport.   Common Issues   If all the correct values for a connection string are not known, errors with the connection occur. In Designer, check the Results window and Engine log for details. For scheduled workflows, the Service log may be helpful, as well as the Gallery log if the workflow is running from the Gallery. A local system administrator may be needed to verify correct the parameters for the connection string. Here are the default locations for Alteryx logs, check your System Settings if the logs are not found. %ProgramData%\Alteryx\Engine logs %ProgramData%\Alteryx\Gallery\Logs %ProgramData%\Alteryx\Service Additional Resources   Connection Strings is a great resource with a thorough list of connection string formats for Oracle, MS SQL, PostfreSQL, and Teradata (others as well). Simba Hive ODBC Install Guide  Simba Impala ODBC Install Guide  Simba Spark ODBC Install Guide Troubleshooting Database Connections  Data source name not found and no default driver specified errors 

Too much connection data for an OCI driver may cause an OCIEnvCreate Error   An OCI error may occur when running a workflow from a network location with many OCI inputs: OCIEnvCreate Error: No error handle available to report exact OCI_ERROR.   Prerequisites   A Designer workflow with Input Data tools having OCI connections to an Oracle database.   Product - Alteryx Designer downloads.alteryx.com Product - Oracle Instant Client for validated versions see: Supported Data Sources Oracle download site: Oracle Instant Client downloads   Procedure   Decrease the total connection data that OCI driver in the Oracle Client has to process for the workflow. If the workflow is accessed from a network drive, try putting the workflow in a folder close to the root of the driver, rather than in a long folder directory, or move the workflow to your workstation instead of a network directory. If there are many OCI connections in the workflow, see if the same data could be imported with fewer connections.   The OCI driver is recording the network location of the workflow. Also, if the workflow contains multiple OCI inputs, the Oracle Client OCI driver appears to compile all of them together to create the workflow connections.    Common Issues   A maximum limit of connection data for an OCI driver can be reached In testing, we found that a maximum limit of connection data could be reached, and then the error would occur. The workflow would run fine with multiple OCI connections if the workflow was located on the workstation. However, when running the same workflow from a network drive, the length of the folder path would determine whether or not it would error out. Similarly, adding or removing more OCI inputs would have the same effect. A limit would be reached, and an error would occur beyond that limit.    Additional Resources   Oracle Call Interface Programmer's Guide OCIEnvCreate()  

How to find the relevant information and configure a Databricks connection through the Manage In-DB Connections window. The Databricks connection includes bulk loading capabilities that allow users to load large data sets faster. 

Handling NULL Values in Databases In relational databases a NULL value is used to represent a missing or unknown value in the column. The ANSI SQL-92 specification defines the handling of the NULL value in databases.      SQL Ternary Logic SQL relies on a three valued logic. Additionally to true and false logical expressions can also yield unknown.  This is a consequence of SQL's support of NULL to mark either absent or unknown data. Thus, if NULL is part of a logical expression the outcome will be NULL. In more colloquial terms NULL means "it could have any value".   Therefore, in all the below cases the result will be unknown:   NULL = NULL NULL = 1 NULL <> 1 NULL > 1   Please note that the SQL standard does not specify how NULL values should be ordered in comparison with non-NULL values.   Testing for NULL From the above it emerges that a specific SQL command is needed to check whether a value is indeed NULL. SQL has the <expression> is null and <expression> is not null expressions to ascertain an expression is NULL or not.   The SQL standard also defines an expression to compare two NULL values treating them the same. However, not all SQL flavors support it and may instead have their own proprietary alternative. The SQL standard specifies <expression> is not distinct from <expression>.   Where and Having Clauses A Where clause needs to be true for the SQL query to execute.  Therefore if the condition in the Where clause will evaluate to unknown it will reject all rows. For instance   SELECT c_columns FROM t_table WHERE c_columns = NULL   will reject all columns. In order to use NULL in Where clauses WHERE c_columns is NULL should be used instead.   It is important to note that since NULL is neither true nor false, but rather unknown in SQL logic the below query will never return NULL values.   SELECT c_columns FROM t_table WHERE c_columns = 'ANY VALUE'   Additional Resource   Handling Null Values - Microsoft help documentation 

No Lua script was found   When reading in from a database, the following message is seen:   Info: Input Data (n): No Lua script was found for corresponding ODBC driver.   Environment   Alteryx Designer 2018.4+ Windows Operating System ODBC database connection     Cause   This message indicates that you are connecting to a database, and no Lua script was found. This is expected when you are connecting to a database that does not work out of the box with Alteryx. Please note this is a message and not an error or warning; it will in no way prevent your workflows from executing successfully if you are not using a custom Lua script.     Explanation To support new ODBC drivers that Alteryx has not validated/tested, you can create a custom Lua script that will map driver and database data types to Alteryx types.  To learn more about this (and find sample Lua scripts) please see the documentation Customizable ODBC Driver Connections found in: %LOCALAPPDATA%/Alteryx/bin/RuntimeData/ODBC

"Unable to find connection 'x' " when workflow is scheduled   When running a workflow via the Scheduler (for Designer with Automation), the following error is observed:   Unable to find connection "x"   Environment   Alteryx Designer Designer with Automation Windows Operating System In-Database Connection   Diagnosis   1. Confirm that you are using Designer with Automation and not Alteryx Server. You can confirm this by opening the Alteryx System Settings from Designer under Options > Advanced Options > System Settings. On the Setup Type page you will see Designer and Scheduler Only selected.   2. Confirm the In-Database connection is setup as a User connection. You can check this from Designer by navigating to Options > Advanced Options > Manage In-DB Connections and choosing the Data Source and Connection name you are using. It will show next to the name of the Connection:     Alternatively, you can check in the drop-down for the Connection Name Connect In-DB tool or the Data Stream In tool:       Cause   The In-Database connection in use is a User connection; scheduling requires a System connection or a File connection.     Solution A - System Connection   Follow the steps in this article to set up a System connection: How To: Create an In-Database Connection This will require admin permissions. Please see the following article for more information: Unable to create System Connection/System Connection type missing   Solution B-  File Connection   Follow the steps in this article to set up a File connection: How To: Create an Alteryx In-DB Connection File The .indbc file will need to be available to the Run As User or the user running the Alteryx Service. To avoid any issues, you can package the workflow, then schedule as follows: Package the workflow by going to Options > Export Workflow, making sure you check the box to include the .indbc file Go to Options > View Schedules On the Workflows tab, click the + icon Navigate to the .yxzp file you created above and set the schedule frequency     Additional Resources   Database Connections: Creating an alias and the advantages of using an alias Database Issues – Working with Alteryx Customer Support Engineers (CSEs)

Hive ODBC can be slow when writing to tables. If you are looking for a faster option to write to Hive and want to create a new table or overwrite an existing table, use the  IN-DB tools   to output your data. 

Alteryx has the ability to connect with MIT Kerberos based Impala implementations. With this feature, came the need to tell the Simba Impala driver that you are using Windows Kerberos and not MIT's version.

How To: Create an In-Database (In-DB) Connection   Apart from making standard database connections, which allow for data to be pulled from the database directly into Alteryx, Designer also has In-Database functionality. The In-Database tools allow for the workflow created to be converted into a query which will then be executed on the database, and not locally in Designer.     Prerequisites   Alteryx Designer Windows Operating System   Procedure   In Designer, drag and drop the Connect In-DB tool (located in the In-Database tool palette) onto the canvas Select the “Manage Connections…” option to establish a new In-DB connection The Manage In-DB Connections window should appear. To configure this window: Select the Data Source/Database type from the Data Source  drop-down. Under Connection Type, select User, System or File connection. For file connections, see this article: How To: Create an Alteryx In-DB Connection File If you do not see the System option available, see this article: Unable to create System Connection/System Connection type missing Under Connections, select New. Under Connection Name, enter a name for this connection. Under Driver, select the method of connection to use. Click the Connection String drop-down. You can choose to use an existing connection or create a new connection by clicking the New database Connection… option. You can also paste in the full connection string if you have it available. Repeat steps 5 and 6 for the Write tab. Click OK. If the connection was successful, the Choose Table or Specify Query window should appear.   The connection will now be stored in the Connection Name drop-down.     Additional Resources   Troubleshooting Database Connections FAQ: How Do the In-Database tools Work? Database Issues – Working with Alteryx Customer Support Engineers (CSEs)  

Issue   I am trying to set up a System alias, but the System option is missing.   Standard Connection - System connection is missing from Options > Advanced Options > Manage Data Connections > Add Connection    In-DB Connection - System connection is not an option:     Environment   Alteryx Designer Windows Operating System   Cause   The Designer is not running Elevated (aka running as an Administrator). Alteryx must be run with elevated privileges in order to be able to create a System Connection within Alteryx.      Solution   Right click on the Alteryx program (or on the Alteryx icon on your Desktop) and choose Run as administrator Please contact your IT department if you are unable to run Alteryx as an administrator     Additional Resources   Creating an In-Database Connection Database Connections: Creating an alias and the advantages of using an alias Database Issues – Working with Alteryx Customer Support Engineers (CSEs)

How To: Create an Alteryx In-DB Connection File   As users collaborate, the sharing of workflows that connect to databases can be helpful. The In-DB Connection File type allows for this. A database connection is saved as an .indbc file so it can be packaged with a workflow.   Prerequisites   Alteryx Designer   Procedure   1. In Designer, navigate to Options > Advanced Options > Alias Manager > In-DB Connections     2. Select the Data Source from the dropdown (1). For our example we will select Oracle, then pick File for the connection type (2), Click the elipses (...) button to navigate to a folder where the .indbc file will be stored. Please note that the first time you set up a connection file, you will need to name the file and click Open to create the file (4) then provide a name for this file (3).       Then proceed to set up your connection as you would normally do by selecting the appropriate driver and setting up the connection string for Read & Write.       3. Now that your connection file is created, the next step is to connect to your database using this file. Bring a Connect In-DB Tool onto your canvas, click on the dropdown for the Connection name and select Open File Connection and browse to your file     4. You can then select a Table, use the Visual Query builder, or type in SQL directly in the Choose Table or Specific Query window       5. (Optional) If you would like to share this workflow, package your workflow in Designer by going to Options > Export workflow. Notice that the .indbc file is being packaged with the workflow.       At the receiving end, the database drivers/client, data sources for that database should be installed and set up. The .indbc file looks like below, with the password encrypted.   Note: Workflow and all images created in this article are from Alteryx version 2019.2

How To: Connect to an OleDB Data Source   Alteryx can connect to databases using different methods. T his guide will focus on setting up an OleDB connection to SQL server, however, the same general process can be followed to connect to any database via OleDB.     Prerequisites   Product - Alteryx Designer or Server. OleDB driver for the database (if it is not currently present on your machine, you must download and install it prior to using this guide. The download links to many of the drivers can be found   here. If you are unsure of where to get the driver or which driver to use please contact your IT or database admin for this information).     Procedure   Open Alteryx Designer and drag and drop the “Input Data” tool onto the canvas     Before 2019.2 Click the drop-down to "Connect to a File or Database." Then select "Database Connection" > "New OleDB Connection…" The "Data Link Properties" window should appear.     2019.2 and later Click OleDB under Microsoft SQL Server. The "Data Link Properties" window should appear.   All versions   Select the driver for the database you wish to connect to and click “Next.” In this guide we will be selecting the “SQL Server Native Client 11.0” driver. This is the recommended driver for Microsoft SQL Server.     On the next screen, enter the server name by either selecting it from the drop-down, or if it does not appear, by typing it in manually Select the authentication method of the database. If your database uses “Windows Authentication” additional login information is not required. If your database uses user ID/PW authentication please enter the username and password you will be connecting with. If you are unsure of your login information please contact your IT or database admin. Select the database on the server you wish to connect to by selecting it in the drop-down. Click the “Test Connection” button to test that you are set up correctly.   Upon successful test you will get the below popup window: Click the “OK” button On the “Data Link Properties” window click “OK”     The “Choose Table…” Window should appear. In the window select the table you wish to connect to.     After successfully connecting to a table, the connection will now be saved in the “Connect a File or Database” drop-down, both under the initial drop-down and also under “Database Connection”     Additional Resources   Help Doc page on supported data sources and file formats Help Doc page on ODBC and OleDB connections

How To: Gather ODBC Logs   Gathering logs from the driver can be useful to troubleshoot errors that appear in Alteryx when attempting to connect to a database. There are two types of logs that can be gathered:     1. Data Source Name (DSN) trace logs (set up via an individual DSN) - This log uses the driver settings of the individual DSN, meaning the logs are more specific to that datasource . - This is the preferred log to gather     2. Generic ODBC tracing - This produces a standard trace file using Microsofts ODBC trace library. This will gather logs for all connections made through any driver/DSN, and is therefore less verbose and more generic. - Only gather this if your driver does not offer DSN trace logs (Procedure A).     Prerequisites   Alteryx Designer Alteryx Server Any supported database driver     Procedure A - DSN trace log   Open the ODBC Data Source Administrator window Please note that there are separate windows for 64 and 32-bit data sources. Please choose the appropriate window based on the bitness of the driver that you are utilizing for the connection On the User DSN or System DSN tab, select the DSN you are using to connect in Alteryx Click the Configure button: Each driver has different configuration options for enabling logging. Generally, you will find the logging options under the Advanced tab or settings, or under Logging Options. You will also be required to set:  The level of logging - always select LOG_TRACE or the highest level, if on a scale LOG_TRACE is the preferred level                                                                                                                           Highest level on a scale of 0-6 The path to the log file Here are some examples of how to set the logging for different drivers: Amazon Redshift Simba Hive ODBC Snowflake From here, return to Alteryx Designer or Server and replicate the error. Gather the log from the location you specified. 8. Return to Alteryx and replicate the error. 9. Gather the log from the location you specified       Procedure B - Generic ODBC Trace   Open the ODBC Data Source Administrator window Please note that there are separate windows for 64 and 32-bit data sources. Please choose the appropriate window based on the bitness of the driver that you are utliziing for the connection Select the Tracing tab Under the Log File Path select the Browse button and set the file to an easy-to-reach location (i.e. your Desktop) Click Start Tracing Now Return to Alteryx and replicate the error Gather the log from the location you specified   Common Issues   I am unable to find the logging options for the driver I am using. Generally, the drivers have great help documentation for how to enable logging. A Google search of "my driver name enable logging DSN" should give you detailed steps specific to that driver. Some drivers do not have logging options available,  I configured the driver logging, but no log file is created. 1. If you have run your workflow but no log appears after the run completes, this generally means you do not have the appropriate permissions level to log. Please contact your DBA to confirm and investigate. 2. If you are using Snowflake, you cannot specify a log location. The default location is C:\Users\your_username 3. If you are using PostgreSQL:      1. In the Input Tool, add the following to the end of your connection string ;commlog=1;debug=1      2. By default, it writes the log to your user directory: C:\Users\your_username    Additional Resources   Troubleshooting Database Connections

Issue    During run time of workflow, the following error is received:    Error: Input Data (1): Error SQLExecute: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 99, HTTP Response Code: 1759505392, Error Message: Unable to connect to endpoint [Execution ID: e7fe279d-f39b-4872-b37d-8ad49d49f3f5]   Environment   Alteryx Designer Amazon Athena Environment Windows Operating System Amazon Athena ODBC Driver (found here)   Cause   In version 1.0.3 and greater of the ODBC Driver, Result Set Streaming is enabled by default which has extra requirements. More info on the requirements here.   Solution - Connection String (DSN-less) If connecting via a Connection String: Add UseResultsetStreaming=0 to your connection string Example string:  odbc:Driver={Simba Athena ODBC Driver};AwsRegion=[Region];S3OutputLocation=[S3Path];AuthenticationType=IAM Profile;AWSProfile=[YourProfileName];UseResultsetStreaming=0 Use this string with your standard tools (Input Data, Output Data) or with a Connect In-DB Tool. Solution B - DSN Connection  If you are connecting to your Amazon Athena environment with a DSN connection (via ODBC Admin): Open up ODBC Data Source Administrator  Find your Athena connection and select it Hit "Configure..." Hit "Advanced Options..." Uncheck the "Use Resultset Streaming" box Hit OK to save the Advanced Options Hit OK again to save your connection  

The guide covers SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, CROSS JOIN, WHERE BETWEEN, WHEREIN, DISTINCT and we hope you find it helpful.

We are working diligently and intelligently on making connections to databases easier, more secure, and faster in Alteryx. Even when we can make the best possible experience for our users come to fruition, any user may still encounter some issues due to the use of third party drivers and/or the intricate communications between the database, driver, and Alteryx. To help to minimize those issues, Alteryx is tested with an abundance of "supported" databases and drivers that are proven to be compatible but, unfortunately, you just can't test every database and every driver out there. Our technical specifications page found here will show you those databases Alteryx supports, as well as the drivers we have tested and support with respect to each database. This does not mean Alteryx will not connect to a database or driver you do not see on this page (or links within the page), however, Alteryx can not support those databases and/or drivers if troubleshooting or setup is required.

Connecting to Hadoop HDFS/Hive/Impala/Spark with Alteryx Designer.

Workflow processing can take longer when a large amount of data is streamed in and out of a database. In-database processing can be used to speed up a workflow.