hrbrmstr/metis

Helpers for Accessing and Querying Amazon Athena using R, Including a lightweight RJDBC shim

[](https://codecov.io/gh/hrbrmstr/metis

metis

Access and Query Amazon Athena via DBI/JDBC

Description

In Greek mythology, Metis was Athena’s “helper” so…

Methods are provided to connect to ‘Amazon’ ‘Athena’, lookup schemas/tables, perform queries and retrieve query results via the included JDBC DBI driver.

What’s Inside The Tin?

The following functions are implemented:

Easy-interface connection helper:

• athena_connect Simplified Athena JDBC connection helper

Custom JDBC Classes:

• Athena: AthenaJDBC (make a new Athena con obj)
• AthenaConnection-class: AthenaJDBC
• AthenaDriver-class: AthenaJDBC
• AthenaResult-class: AthenaJDBC

Custom JDBC Class Methods:

• dbConnect-method
• dbExistsTable-method
• dbGetQuery-method
• dbListFields-method
• dbListTables-method
• dbReadTable-method
• dbSendQuery-method

Pulled in from other cloudyr pkgs:

• read_credentials: Use Credentials from .aws/credentials File
• use_credentials: Use Credentials from .aws/credentials File

Installation

devtools::install_git("https://git.sr.ht/~hrbrmstr/metis")
# OR
devtools::install_gitlab("hrbrmstr/metis")
# OR
devtools::install_github("hrbrmstr/metis")

Usage

library(metis)

# current verison
packageVersion("metis")

## [1] '0.3.0'

library(rJava)
library(RJDBC)
library(metis)
library(magrittr) # for piping b/c I'm addicted

dbConnect(
  metis::Athena(),
  Schema = "sampledb",
  AwsCredentialsProviderClass = "com.simba.athena.amazonaws.auth.PropertiesFileCredentialsProvider",
  AwsCredentialsProviderArguments = path.expand("~/.aws/athenaCredentials.props")
) -> con

dbListTables(con, schema="sampledb")

## [1] "elb_logs"

dbExistsTable(con, "elb_logs", schema="sampledb")

## [1] TRUE

dbListFields(con, "elb_logs", "sampledb")

##  [1] "timestamp"             "elbname"               "requestip"             "requestport"          
##  [5] "backendip"             "backendport"           "requestprocessingtime" "backendprocessingtime"
##  [9] "clientresponsetime"    "elbresponsecode"       "backendresponsecode"   "receivedbytes"        
## [13] "sentbytes"             "requestverb"           "url"                   "protocol"

dbGetQuery(con, "SELECT * FROM sampledb.elb_logs LIMIT 10") %>% 
  dplyr::glimpse()

## Observations: 10
## Variables: 16
## $ timestamp             <chr> "2014-09-27T00:00:25.424956Z", "2014-09-27T00:00:56.439218Z", "2014-09-27T00:01:27.4417…
## $ elbname               <chr> "lb-demo", "lb-demo", "lb-demo", "lb-demo", "lb-demo", "lb-demo", "lb-demo", "lb-demo",…
## $ requestip             <chr> "241.230.198.83", "252.26.60.51", "250.244.20.109", "247.59.58.167", "254.64.224.54", "…
## $ requestport           <int> 27026, 27026, 27026, 27026, 27026, 27026, 27026, 27026, 27026, 27026
## $ backendip             <chr> "251.192.40.76", "249.89.116.3", "251.111.156.171", "251.139.91.156", "251.111.156.171"…
## $ backendport           <int> 443, 8888, 8888, 8888, 8000, 8888, 8888, 8888, 8888, 8888
## $ requestprocessingtime <dbl> 9.1e-05, 9.4e-05, 8.4e-05, 9.7e-05, 9.1e-05, 9.3e-05, 9.4e-05, 8.3e-05, 9.0e-05, 9.0e-05
## $ backendprocessingtime <dbl> 0.046598, 0.038973, 0.047054, 0.039845, 0.061461, 0.037791, 0.047035, 0.048792, 0.04572…
## $ clientresponsetime    <dbl> 4.9e-05, 4.7e-05, 4.9e-05, 4.9e-05, 4.0e-05, 7.7e-05, 7.5e-05, 7.3e-05, 4.0e-05, 6.7e-05
## $ elbresponsecode       <chr> "200", "200", "200", "200", "200", "200", "200", "200", "200", "200"
## $ backendresponsecode   <chr> "200", "200", "200", "200", "200", "400", "400", "200", "200", "200"
## $ receivedbytes         <S3: integer64> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
## $ sentbytes             <S3: integer64> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2
## $ requestverb           <chr> "GET", "GET", "GET", "GET", "GET", "GET", "GET", "GET", "GET", "GET"
## $ url                   <chr> "http://www.abcxyz.com:80/jobbrowser/?format=json&state=running&user=20g578y", "http://…
## $ protocol              <chr> "HTTP/1.1", "HTTP/1.1", "HTTP/1.1", "HTTP/1.1", "HTTP/1.1", "HTTP/1.1", "HTTP/1.1", "HT…

Check types

dbGetQuery(con, "
SELECT
  CAST('chr' AS CHAR(4)) achar,
  CAST('varchr' AS VARCHAR) avarchr,
  CAST(SUBSTR(timestamp, 1, 10) AS DATE) AS tsday,
  CAST(100.1 AS DOUBLE) AS justadbl,
  CAST(127 AS TINYINT) AS asmallint,
  CAST(100 AS INTEGER) AS justanint,
  CAST(100000000000000000 AS BIGINT) AS abigint,
  CAST(('GET' = 'GET') AS BOOLEAN) AS is_get,
  ARRAY[1, 2, 3] AS arr1,
  ARRAY['1', '2, 3', '4'] AS arr2,
  MAP(ARRAY['foo', 'bar'], ARRAY[1, 2]) AS mp,
  CAST(ROW(1, 2.0) AS ROW(x BIGINT, y DOUBLE)) AS rw,
  CAST('{\"a\":1}' AS JSON) js
FROM elb_logs
LIMIT 1
") %>% 
  dplyr::glimpse()

## Observations: 1
## Variables: 13
## $ achar     <chr> "chr "
## $ avarchr   <chr> "varchr"
## $ tsday     <date> 2014-09-29
## $ justadbl  <dbl> 100.1
## $ asmallint <int> 127
## $ justanint <int> 100
## $ abigint   <S3: integer64> 100000000000000000
## $ is_get    <lgl> TRUE
## $ arr1      <chr> "1, 2, 3"
## $ arr2      <chr> "1, 2, 3, 4"
## $ mp        <chr> "{bar=2, foo=1}"
## $ rw        <chr> "{x=1, y=2.0}"
## $ js        <chr> "\"{\\\"a\\\":1}\""

cloc::cloc_pkg_md()

Lang	# Files	(%)	LoC	(%)	Blank lines	(%)	# Lines	(%)
R	8	0.89	250	0.83	83	0.72	194	0.79
Rmd	1	0.11	50	0.17	32	0.28	53	0.21

Code of Conduct

Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms.

metis's Issues

Could not find function ".jcall"

Love the package.

However, for any call to dbGetQuery I get the error:

Error in .jcall(r@stat, "V", "close") : could not find function ".jcall"

Since JDBC depends on rJava, rJava::.jcall is not available unless rJava is explicitly loaded by the user.

The solution here is straightforward. Add rJava to the DESCRIPTION file imports section and add the royxygen2 importFrom tag. A PR with these changes is imminent, if you agree with this style.

REST API endpoints

https://github.com/aws/aws-sdk-cpp/blob/fd533bdd042dfba60765b1f0283aaa4e0644a943/code-generation/api-descriptions/athena-2017-05-18.normal.json

Error during connect

Just installed the package today since AWR.Athena is causing me too much grief. I'm getting the following error when trying to connect using

athenaConn <- RJDBC::dbConnect(metis::Athena(),
                        region = "us-east-1",
                        Schema = "cis.prod")

Error in .methodPackageSlots(def, label, table, dupl, isCurrent) : trying to get slot "target" from an object of a basic class ("environment") with no slots

7. .methodPackageSlots(def, label, table, dupl, isCurrent)
6. .cacheMethodInTable(fdef, sig, def, .getMethodsTable(fdef, ev, inherited = inherited))
5. cacheMethod(f, method@target, method, fdef = getGeneric(f), inherited = TRUE)
4. callNextMethod(drv, url = sprintf("jdbc:awsathena://athena.%s.amazonaws.com:443/", region), S3OutputLocation = S3OutputLocation, Schema = Schema, AWSCredentialsProviderClass = "com.simba.athena.amazonaws.auth.DefaultAWSCredentialsProviderChain", ...)
3. .local(drv, ...)
2. RJDBC::dbConnect(metis::Athena(), region = "us-east-1", Schema = "cis.prod")
1. RJDBC::dbConnect(metis::Athena(), region = "us-east-1", Schema = "cis.prod")

Using R 3.6.1

> sessionInfo()
R version 3.6.1 (2019-07-05)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Pop!_OS 19.04

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.8.0
LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.8.0

locale:
 [1] LC_CTYPE=en_US.UTF-8          LC_NUMERIC=C                  LC_TIME=en_US.UTF-8           LC_COLLATE=en_US.UTF-8        LC_MONETARY=en_US.UTF-8      
 [6] LC_MESSAGES=en_US.UTF-8       LC_PAPER=en_US.UTF-8          LC_NAME=en_US.UTF-8           LC_ADDRESS=en_US.UTF-8        LC_TELEPHONE=en_US.UTF-8     
[11] LC_MEASUREMENT=en_US.UTF-8    LC_IDENTIFICATION=en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] futile.logger_1.4.3  lazyeval_0.2.2       glue_1.3.1           dplyr_0.8.2          readr_1.3.1          DT_0.7               shinydashboard_0.7.1
 [8] openxlsx_4.1.0.1     aws.s3_0.3.12        shiny_1.3.2         

loaded via a namespace (and not attached):
 [1] zip_2.0.3            Rcpp_1.0.1           formatR_1.7          compiler_3.6.1       pillar_1.4.2         later_0.8.0          futile.options_1.0.1
 [8] RJDBC_0.2-7.1        base64enc_0.1-3      tools_3.6.1          digest_0.6.19        evaluate_0.14        jsonlite_1.6         tibble_2.1.3        
[15] pkgconfig_2.0.2      rlang_0.4.0          DBI_1.0.0            rstudioapi_0.10      crosstalk_1.0.0      curl_3.3             yaml_2.2.0          
[22] xfun_0.8             rJava_0.9-10         stringr_1.4.0        knitr_1.23           httr_1.4.0           xml2_1.2.0           sourcetools_0.1.7   
[29] htmlwidgets_1.3      hms_0.4.2            tidyselect_0.2.5     R6_2.4.0             rmarkdown_1.13       AWR.Athena_2.0.6-1   lambda.r_1.2.3      
[36] purrr_0.3.2          magrittr_1.5         promises_1.0.1       htmltools_0.3.6      shinythemes_1.1.2    assertthat_0.2.1     mime_0.7            
[43] aws.signature_0.5.0  xtable_1.8-4         httpuv_1.5.1         stringi_1.4.3        crayon_1.3.4     

Use aws.signature for credentials?

Looks like you're calling AWS credentials files internally. You might want to try importing aws.signature, which will give a use_credentials() function to call an arbitrary credentials file.

(Also, if you want to contribute this to cloudyr, let me know!)