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.

  1. Double click the dmg file that you downloaded
  1. Follow instruction
  1. Agree License
  1. Click Install
  1. Enter Admin Credential (if asked)
  1. Confirm Installation

Edit Configuration File (Only For Mac)

  1. Open the below file with Text Editor.
/Library/simba/athenaodbc/lib/simba.athenaodbc.ini
  1. Add below line
ODBCInstLib=/usr/local/lib/libodbcinst.dylib
  1. Save Changes.

Create a Connection

Click the down arrow button next to Project Name.

Click the Add Button.

Select Amazon Athena.

On the dialog, enter Name, AWS Region, S3 Output Location, Username, 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)

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.

Then Click Test Connection and make sure it connects successfully.

Click Add to save the 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!