Connect to Amazon Athena with Exploratory using ODBC

HowTo
#1

Connect to Amazon Athena with Exploratory using ODBC

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

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 assing below permissions.

  • AWSQuickSightListIAM
  • AmazonS3FullAccess
  • AmazonAthenaFullAccess

Setup

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

Install odbc R Package.

Click R Packages menu on Project List Page and select Install tab.
Then type in odbc and click Install.

Create an R Script Data Source

Click plus button next to Data Frame

And type in followings to script editor.

library(odbc)
library(DBI)

# Get a connection
DBI::dbConnect(
  odbc::odbc(), 
  driver = "/Library/simba/athenaodbc/lib/libathenaodbc_sbu.dylib", 
  AwsRegion = "<your_region>",
  AuthenticationType = "IAM Credentials",
  S3OutputLocation = "s3://aws-athena-query-results-<your_hash>-<your_region>/",
  UID = "YOUR_AWS_ACCESS_KEY_ID",
  PWD = "YOUR_SECRET_ACCESS_KEY"
) -> con

# write SQL query
dbGetQuery(con, "SELECT * FROM sampledb.elb_logs limit 10")

There are couples of parameters that you need to pass to get a connection.

Driver

On Mac, by default, your Amazon Amazon Athena ODBC Driver is installed to following location.

/Library/simba/athenaodbc/lib/libathenaodbc_sbu.dylib.

But if you installed it to another location, please change it accordingly.

AwsRegion

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

AuthenticationType

You can pass either of followings:

  • IAM Credentials
  • Default Credentials
  • IAM Profile
  • Instance Profile

In this example, we’ll use IAM Credential

S3OutputLocation

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

UID

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

PWD

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

For your Access key ID and secret, you can get it from AWS IAM Management Console.

Once a connection is retrieved, you can write a SQL query. In this example, it queries from a elb_logs table of sampledb database.

Import Data

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