Project Status: Active – The project has reached a stable, usable state and is being actively developed. CRAN_Status_Badge downloads

The goal of the RAthena package is to provide a DBI-compliant interface to Amazon’s Athena (https://aws.amazon.com/athena/) using Boto3 SDK. This allows for an efficient, easy setup connection to Athena using the Boto3 SDK as a driver.

NOTE: Before using RAthena you must have an aws account or have access to aws account with permissions allowing you to use Athena.

Installation:

Before installing RAthena ensure that Python 3+ is installed onto your machine: https://www.python.org/downloads/. To install Boto3 either it can installed the pip command or using RAthena installation function:

pip install boto3

RAthena Method (after RAthena has been installed this method can be used)

To install RAthena you can get it from CRAN with:

install.packages("RAthena")

Or to get the development version from Github with:

remotes::install_github("dyfanjones/rathena")

Connection Methods

Hard Coding

The most basic way to connect to AWS Athena is to hard-code your access key and secret access key. However this method is not recommended as your credentials are hard-coded.

AWS Profile Name

The next method is to use profile names set up by AWS CLI or created manually in the ~/.aws directory. To create the profile names manually please refer to: https://boto3.amazonaws.com/v1/documentation/api/latest/guide/configuration.html.

Setting up AWS CLI

RAthena is compatible with AWS CLI. This allows your aws credentials to be stored and not be hard coded in your connection.

To install AWS CLI please refer to: https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-install.html, to configure AWS CLI please refer to: https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-configure.html

Once AWS CLI has been set up you will be able to connect to Athena by only putting the s3_staging_dir.

Using default profile name:

Connecting to Athena using profile name other than default.

Assuming ARN Role for connection

Another method in connecting to Athena is to use Amazon Resource Name (ARN) role.

Setting credentials in environmental variables:

Connecting to Athena directly using ARN role:

To change the duration of ARN role session please change the parameter duration_seconds. By default duration_seconds is set to 3600 seconds (1 hour).

Usage

Basic Usage

Connect to athena, and send a query and return results back to R.

To retrieve query in 1 step.

dbGetQuery(con, "SELECT * FROM one_row")

Intermediate Usage

To create a tables in athena, dbExecute will send the query to athena and wait until query has been executed. This makes it and idea method to create tables within athena.

RAthena has 2 extra function to return extra information around Athena tables: dbGetParitiions and dbShow

dbGetPartitions will return all the partitions (returns data.frame):

RAthena::dbGetPartition(con, "impressions")

dbShow will return the table’s ddl, so you will able to see how the table was constructed in Athena (returns SQL character):

RAthena::dbShow(con, "impressions")

Tidyverse Usage

Creating a connection to Athena and query and already existing table iris that was created in previous example.

dplyr provides lazy querying with allows to short hand tbl(con, sql("SELECT * FROM iris")) to tbl(con, "iris"). For more information please look at https://db.rstudio.com/dplyr/.

tbl(con, "iris")

Querying Athena with profile_name instead of hard coding aws_access_key_id and aws_secret_access_key. By using profile_name extra Meta Data is returned in the query to give users extra information.

tbl(con, "iris") %>% 
  select(contains("sepal"), contains("petal"))

Upload data using dplyr function copy_to and compute.

Write data.frame to Athena table

Write Athena table from tbl_sql

Create athena with unique table name

Create athena with specified name and s3 location

Work Groups

Creating work group:

library(RAthena)
library(DBI)

con <- dbConnect(RAthena::athena(),
                profile_name = "your_profile",
                encryption_option = "SSE_S3",
                s3_staging_dir='s3://path/to/query/bucket/')

create_work_group(con, "demo_work_group", description = "This is a demo work group",
                  tags = tag_options(key= "demo_work_group", value = "demo_01"))

List work groups:

Update work group:

update_work_group(con, "demo_work_group", description = "This is a demo work group update")

Return work group meta data:

get_work_group(con, "demo_work_group")

Connect to Athena using work group:

Delete work group:

delete_work_group(con, "demo_work_group")

Similar Projects

Python:

R:

awsathena and metis family of packages are currently used in production every day to analyze petabytes of internet scan and honeypot data.

Comparison:

The reason why RAthena stands slightly apart from AWR.Athena is that AWR.Athena uses the Athena JDBC drivers and RAthena uses the Python AWS SDK Boto3. The ultimate goal is to provide an extra method for R users to interface with AWS Athena. As pyAthena is the most similar project, this project has used an appropriate name to reflect this … RAthena.