user2600629 user2600629 -4 years ago 258
R Question

R Connect to AWS Athena

I am attempting to connect to AWS Athena based upon what I have read online, but I am having issues.

Steps taking


  • Update Java

  • replace user/pass with accesskey/secretKey

  • pass accesskey/secretKey with user/pass as well



Any ideas?

Error Message:

Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1], :
java.sql.SQLException: AWS accessId/secretKey or AWS credentials provider must be provided

System Information

sysname release version
"Linux" "4.4.0-62-generic" "#83-Ubuntu SMP Wed Jan 18 14:10:15 UTC 2017"
nodename machine login
"ip-***-**-**-***" "x86_64" "unknown"
user effective_user
"rstudio" "rstudio"


Code https://www.r-bloggers.com/interacting-with-amazon-athena-from-r/

library(RJDBC)

URL <- 'https://s3.amazonaws.com/athena-downloads/drivers/AthenaJDBC41-1.0.0.jar'
fil <- basename(URL)
if (!file.exists(fil)) download.file(URL, fil)

drv <- JDBC(driverClass="com.amazonaws.athena.jdbc.AthenaDriver", fil, identifier.quote="'")

con <- jdbcConnection <- dbConnect(drv, 'jdbc:awsathena://athena.us-east-1.amazonaws.com:443/',
s3_staging_dir="s3://mybucket",
user=Sys.getenv("myuser"),
password=Sys.getenv("mypassword"))

Answer Source

The Athena JDBC driver is expecting your AWS Access Key Id as the user, and the Secret Key as the password:

accessKeyId <- "your access key id..."
secretKey <- "your secret key..."

jdbcConnection <- dbConnect(
  drv, 
  'jdbc:awsathena://athena.us-east-1.amazonaws.com:443/awsmarketplacereports',
  s3_staging_dir="s3://mybucket",
  user=accessKeyId,
  password=secretKey
)

The r-Bloggers article obtains those from environment variables using Sys.getenv("ATHENA_USER") and Sys.getenv("ATHENA_PASSWORD"), but that is optional.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download