Connect to Amazon Athena with Exploratory

In this blog post, I’ll walk you through a way to connect to your Amazon Athena with Exploratory using Amazon Athena Data Source.

Prerequisite

Before you get started, please make sure to attach required permissions to your AWS account. (Or ask Admin person to do so)

Go to AWS IAM Management Console

Attach Permissions

Make sure to assign below permissions.

AWSQuickSightListIAM
AmazonS3FullAccess
AmazonAthenaFullAccess

Setup

Install unixODBC (Only For Mac)

Please follow this note to install unixODBC.

Install Amazon Athena ODBC Driver

First, please download Amazon Athena ODBC Driver, then install it.

Below is an example for MacOS.

Double click the dmg file that you downloaded

Follow instruction

Agree License

Click Install

Enter Admin Credential (if asked)

Confirm Installation

Edit Configuration File (Only For Mac)

Open the below file with Text Editor.

/Library/simba/athenaodbc/lib/simba.athenaodbc.ini

Add below line

ODBCInstLib=/usr/local/lib/libodbcinst.dylib

Save Changes.

Check ODBC Driver registration in unixODBC

Please follow the steps to check if the ODBC driver is properly registered to unixODBC.

Open Terminal App and run odbcinst -j Below is an example.

➜ odbcinst -j

unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/hidetakakojima/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Open the file for the DRIVERS (e.g. /usr/local/etc/odbcinst.ini) with Text Editor

Check if you can see below entry for Athena ODBC Driver.

[Simba Athena ODBC Driver]
Driver=/Library/simba/athenaodbc/lib/libathenaodbc_sb64.dylib

If you don't see this entry, please add the entry to the file then save it.

Create a Connection

Click the down arrow button next to Project Name.

Click the Add Button.

Select Amazon Athena under "Database" tab.

On the dialog, enter Connection Name, AWS Region, S3 Output Location, User name, and Password.

Connection Name

Enter a name for your Amazon Athena Connection

ODBC Driver for Athena

Use the default value (i.e. Simba Athena ODBC Driver)

AWS Region

This is your Amazon Athena Region (e.g. us-west-2)

S3 Output Location

This is the path that output is saved. Please replace it with your path that you have write permission. (e.g. aws-athena-query-results-xxxxxxxxxxx-us-west-2 xxxxxxxxxxx varies)

VPC Endpoint

This is an optional parameter. When you are accessing from inside the Virtual Private Cloud (VPC), you can connect to Athena via VPC endpoint instead of connecting over the Internet.

To get the VPC Endpoint information, access to your VPC dashboard with a browser, then click the "Endpoints" link and check the DNS names.

Also, make sure to open port 443 and 444 for Inbound Rules of the Security Group associated with the VPC endpoint used for connecting to Athena.

If you do not enable private DNS hostnames, Amazon VPC provides a DNS endpoint name that you can use in the following format. (Make sure to replace VPC_Endpoint_ID with your actual ID)

VPC_Endpoint_ID.athena.Region.vpce.amazonaws.com

Please refer Connect to Amazon Athena Using an Interface VPC Endpoint for details.

Username

Since we use IAM Credential for this example, you need to pass your aws access key id.

Password

Since we use IAM Credential for this example, you need to pass your secret access key.

Workgroup

This is an optional parameter. You can set workgroup to Control Query Access

Then Click Test Connection and make sure it connects successfully.

Click Add to save the connection.

HTTP Proxy

If you setup HTTP Proxy on System Configuration Dialog,

This HTTP proxy information is automatically applied to your Amazon Athena connection.

Create Amazon Athena Data Source

Click Plus button next to Data Frames and select Database Data

Then select Athena.

Write SQL query

Select AWS Athena from Connection pull down list. Then you can see schema information on your left-hand side tree and can write a SQL query. In this example, it queries from a elb_logs table of sampledb database.

select * from sampledb.elb_logs
where request_verb = 'GET'
and backend_port = 80

Import Data

Once it’s imported by clicking Save button, you can start analyzing the data!

Troubleshooting

Connection to Athena failed with 'Simba Athena ODBC Driver' : file not found error.

When you try to connect to Amazon Athena from your Mac, you might see the below error and cannot connect to Athena.

Error : nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib 'Simba Athena ODBC Driver' : file not found

if you hit this issue, please follow the below steps.

Open Terminal App and run odbcinst -j

Below is an example.

➜ odbcinst -j

unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/hidetakakojima/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Open the file for the DRIVERS (e.g. /usr/local/etc/odbcinst.ini) with Text Editor

Check if you can see below entry for Athena ODBC Driver.

[Simba Athena ODBC Driver]
Driver=/Library/simba/athenaodbc/lib/libathenaodbc_sb64.dylib