1

I connected RStudio to Amazon Athena, and the database connection was successful.

con <- dbConnect(noctua::athena(),
                 aws_access_key_id = "***",
                 aws_secret_access_key = "***",
                 s3_staging_dir = "s3://bucket-folder/",
                 region_name = '***')

However, when I attempt to use dbGetQuery(), I receive the following error message despite having DBI installed.

This is the code that I am running.

querytest <- dbGetQuery(con, "SELECT * FROM database.table")
# Error in (function (classes, fdef, mtable)  : unable to find an inherited method
# for function ‘dbSendQuery’ for signature ‘"AthenaConnection", "character"’.

What am I doing wrong in this situation?

r2evans
  • 141,215
  • 6
  • 77
  • 149
  • Does `dbConnect` return an error? [RStudio docs](https://db.rstudio.com/databases/athena/) indicate this API uses an `odbc` driver connection. Where is documentation for `noctua::athena()` types? – Parfait Aug 21 '20 at 21:16
  • ```dbConnect``` is working and I can see that it is connected to Athena. It is the ```dbGetQuery``` that is returning the error. – datascientist2117 Aug 21 '20 at 21:26
  • Try `library(noctua)` before running your query. With many drivers they work without this, but it looks like its S3 method is not found. An alternative is switching to the `odbc` package, though that means (1) you would need the [OS-level ODBC driver for Athena](https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html), and there would be a *slight* overhead associated with ODBC when compared with native drivers. – r2evans Aug 21 '20 at 21:55
  • The ```library(noctua)``` method didn't work, but I will try out the ODBC method. Thank you! – datascientist2117 Aug 21 '20 at 22:14
  • Hmmm... [CRAN docs](https://cran.r-project.org/web/packages/noctua/noctua.pdf) show `dbGetQuery` should work with a `noctua::athena()` connection. Consider reaching out to authors/maintainers on this issue. Be sure to be using latest packages: `DBI (>= 0.7)` – Parfait Aug 21 '20 at 22:21
  • As per your question https://stackoverflow.com/questions/63531052/how-to-connect-to-amazon-athena-using-simba-odbc you still not able to establish an connection? – Prabhakar Reddy Aug 22 '20 at 03:13
  • @PrabhakarReddy I still have not been able to establish a connection. – datascientist2117 Aug 24 '20 at 17:16
  • Dyfan Jones has resolved my question! Thank you all. – datascientist2117 Aug 24 '20 at 18:23

2 Answers2

0

Hi @datascientist2117,

I am the author of noctua. Currently I am unable to replicate your issue. I believe their is a conflict with one of your packages you have running in your R session. I am more than happy to problem solve this :) please raise a ticket at: https://github.com/DyfanJones/noctua/issues

noctua makes it's connection to AWS Athena through the R SDK paws. Which is similar to how python's boto3 connects to AWS Athena. noctua documentation is store on it's github page: https://dyfanjones.github.io/noctua/

Dyfan Jones
  • 229
  • 2
  • 9
0

This one worked for me;

dbGetQuery(con, "SELECT * FROM \"database\".table limit 10")
Choc_waffles
  • 518
  • 1
  • 4
  • 15