This is a non-techie guide to connect to AWS Athena from Tableau. If you’re struggling, like I was, hopefully this will help!
Athena is one of Amazon’s services, part of the AWS ecosystem, which enables you to write SQL against S3 (another Amazon service). Although, if reading this post, chances are you know that already!
Prerequisites to connect Tableau to Athena
To connect Tableau and AWS Athena, two things are required:
- AWS account credentials with permission to query S3 data via Athena
- The driver to connect Athena and Tableau
The first of those is obvious. To test, open up AWS and see if you can run a simple query in Athena against your S3 data.
For the second, by default, Tableau Drivers are stored in this location: C:\Program Files\Tableau\Drivers
If there’s a driver named “AthenaJDBC42…” the Athena Tableau driver is installed. If not, then install the Athena driver.
How to install the Tableau Athena driver
To install the Athena driver, follow the link from the drivers page on the Tableau site.
It will ask you to download a file from the AWS site. At the time of writing, the Tableau documentation said use JDBC 4.2, so download that file.
Move the downloaded file to C:\Program Files\Tableau\Drivers
Next, restart your Tableau Desktop. Now you should be able to connect to Athena from Tableau!
How to connect Tableau to Athena
Open up Tableau Desktop and choose the Amazon Athena connector.
Next to fill in the gaps. This article on the Tableau site helped.
Firstly, the Server. Open up your S3 or Athena (you’ll need both in this process anyway) and check the URL. In the URL of both, after the question mark it should say: Region=xxxxxxx.
Copy the xxxxxx. In the Server box type athena.xxxxxx.amazonaws.com. The xxxxxx being the region code, such as eu-central-1. i.e. athena.eu-central-1.amazonaws.com
Leave the port to default – if it turns out to be wrong, contact your organistions AWS owners.
Next, the S3 Staging Directory. Go to Athena and go to Query Editor.
Next, go to Settings. From there you can copy the Query result location, or if it’s blank then set the location.
If the “Query result location” is “Not defined”, click Manage
There it will give you the S3 URL for the query results. If it’s blank, then set one up.
How to find the Athena location of query result in S3
Go to S3, check the buckets and see if one exists that is suitable to save the query results. If not, then create a new bucket. You need the bucket URL.
Copying the S3 URL can be temperamental – perhaps I’m doing something wrong – often the “Copy S3 URL” button isn’t active within S3. If there are files already in your Query results S3 bucket, click the checkbox next to the file name. That should activate the Copy S3 URL button, which you then click.
Back to Athena, and copy the S3 URL into the “Location of query result” box and click Save. You will then see it on the Query Editor Settings page.
Make sure you have the S3 bucket URL in your clipboard, it’s needed in the Tableau connector. Enter it into the S3 Staging Directory box.
To finish, your Access Key Id and optional Secret Access Key.
If you don’t know these details, go to your Security Credentials. Find this in the top right of your screen by clicking on the dropdown next to your name. Alternatively, use this URL: https://console.aws.amazon.com/iam/home?security_credentials
In My Security Credentials, there’s a button Create access key. Click that, where you can download a csv file which will have two fields:
- Access key ID
- Secret access key
Copy the Access Key ID (it will probably have 20+ characters) and enter into the Athena connector in Tableau.
Enter the Secret access key into the Secret access key box. And it should now connect!
Common issues connecting Tableau and Athena
As mentioned, to connect to Athena from Tableau needs a driver. Installation instructions are at the top of this post. If the driver isn’t installed, you’ll see the following message pop up on screen.
At the time of writing, the Tableau documentation said use JDBC 4.2, so download that file.
If it still doesn’t work, check your Java version – it should be using JDK v8.
To check your Java version, in the Windows start menu, search for “Configure Java”. Open that app and it’ll open the Java Control Panel. Click About and see what version of JDK is on your machine.
An Error occured while communicating with Amazon Athena. Invalid Username or Password.
If you get this error, go to your Security Credentials. In there, make sure you’re using the correct Access Key and Secret Access Key. Reset these if necessary, and enter into the Tableau connector.