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.
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
Make sure to assing below permissions.
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
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.
# Get a connection
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.
On Mac, by default, your Amazon Amazon Athena ODBC Driver is installed to following location.
But if you installed it to another location, please change it accordingly.
This is your Amazon Athena Region (e.g. us-west-2)
You can pass either of followings:
- IAM Credentials
- Default Credentials
- IAM Profile
- Instance Profile
In this example, we'll use IAM Credential
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)
Since we use IAM Credential for this example, you need to pass your aws access key id.
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.
Once it's imported by clicking Save button, you can start analyzing the data!