cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.

Troubleshooting Database Connections

Community Operations Specialist
Community Operations Specialist

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.

 

This is a short overview to help understand and resolve your database connections. Believe it or not, the majority of support cases we receive in regards to supported database issues are actually from the configuration of the driver or an issue with the database. Now, you’re probably thinking “of course he is going to say that it isn’t Alteryx fault!” - but it’s true! Alteryx just reads what the driver passes to it. It isn’t translating the data like the driver is and it also isn’t trying to read your database, get past security settings, authenticate, or find the tables. That's why we often see so many support cases involving databases stem from driver misconfiguration or an underlying issue/error coming from a database.  Keeping that in mind as you troubleshoot, an error you receive in Alteryx can frequently end up being communicated directly from your driver or your database.

 

That said, we think it is important for users to understand what drivers do and what they can connect to in order to help circumvent issues that may stem from them. ODBC is the most commonly used driver and can have many variations depending on the software or database need.

 

Drivers

“ODBC is based on the device driver model, where the driver encapsulates the logic needed to convert a standard set of commands and functions into the specific calls required by the underlying system. For instance, a printer driver presents a standard set of printing commands, the API, to applications using the printing system. Calls made to those APIs are converted by the driver into the format used by the actual hardware, say PostScript or PCL.

 

In the case of ODBC, the drivers encapsulate many functions that can be broken down into several broad categories. One set of functions is primarily concerned with finding, connecting to, and disconnecting from the DBMS that driver talks to. A second set is used to send SQL commands from the ODBC system to the DBMS, converting or interpreting any commands that are not supported internally. For instance, a DBMS that does not support cursors can emulate this functionality in the driver. Finally, another set of commands, mostly internal, is used to convert data from the DBMS's internal formats to a set of standardized ODBC formats, which are based on the C language formats.

 

An ODBC driver enables an ODBC-compliant application to use a data source, normally a DBMS. Some non-DBMS drivers exist, for such data sources as CSV files, by implementing a small DBMS inside the driver itself. ODBC drivers exist for most DBMSs, including OraclePostgreSQLMySQLMicrosoft SQL Server (but not for the Compact aka CE edition), Sybase ASESAP HANA, and DB2. Because different technologies have different capabilities, most ODBC drivers do not implement all functionality defined in the ODBC standard. Some drivers also offer extra functionality not defined by the standard.

 

At this time Alteryx does not support JDBC drivers.

 

Driver Errors

Depending on the driver you have installed, you will see that the error will have the driver name or type in the error message:

 

Connection.png

 

If you see your Driver in the error message, the troubleshooting should start there. You may see that an Alteryx tool is referenced in the error, but more than likely it is the Input Data Tool, which relays the error message directly from the driver.

 

The driver error and suggested solutions can then be found doing a quick internet search on the error text!

 

Database

Just like the driver message, if you are seeing your database mentioned in the error, the first thing to do is to search that error on the internet. In most cases, you will also be able to see what the error is related to and how to fix it!

 

Database Errors 

Oracle:

 ORA.png

 

Hive:

Hive.png

 

Most of these errors can be found on the internet, usually in the database vendors' support forums, and can give you some direction as to where to look to resolve the issue. If, after you have researched the error and attempted to troubleshoot with your database administrator, you still have an issue - please waste no time posting your issue to our Community or reaching out to our Support Team for further assistance! If you do post on the Community or contact Support, please also include the version and type of the database you are using, driver version, and Alteryx version to help us accelerate your time to resolution. If possible, contact your DB admin and ask for your DB logs if they're available as well. This will give us the requisite information to get you up and running as quickly as possible! 

 

Please see the links below for more specific troubleshooting and setup instructions:

Connection Share 11.0

Spatial ODBC

Manage Connections 11.0

Creating Primary Keys

Connecting to ODBC Datasource

Hadoop 11.0

Oracle 11.0

OleDB Connection

Impala Connection with Kerberos

Credentials in database connections (good reference)

Oracle Eloqua Connection

Comments
Aurora
Aurora

Great article Dan - is it worth also giving folk a sense of how to troubleshoot strange errors to see if it's a query error or a driver error?

 

I'm thinking something along the lines of:

Step 1: is this a query issue?

- Can you run this same query through a direct query window or through some other connection to the DB (e.g. through Excel or Access)?

- if not, then this is likely to be a query issue (step through to "debugging a query")

- If yes, then we may have a connectivity issue

Step 2: Can you connect to anything on this db?

- When you open the input data - can you see the list of tables coming up?

- can you run simple queries (like selecting from a table with no filters)?

 

etc...

 

Reason for asking this is that I sometimes see people who believe they have a driver or a connectivity error, but sometimes it's just an error in a query?

 

Thanks for a great article Dan

Sean