Access and Query Amazon Athena via DBI/JDBC
Nevar pievienot vairāk kā 25 tēmas Tēmai ir jāsākas ar burtu vai ciparu, tā var saturēt domu zīmes ('-') un var būt līdz 35 simboliem gara.
boB Rudis 439ca3503c
log
9 mēnešus atpakaļ
R log 9 mēnešus atpakaļ
inst/java log 9 mēnešus atpakaļ
man restored contributors 9 mēnešus atpakaļ
tests log 9 mēnešus atpakaļ
vignettes restored contributors 9 mēnešus atpakaļ
.Rbuildignore restored contributors 9 mēnešus atpakaļ
.bash_profile beta-update 1 gadu atpakaļ
.codecov.yml pre-travis check 9 mēnešus atpakaļ
.gitignore restored contributors 9 mēnešus atpakaļ
.travis.yml pre-travis check 9 mēnešus atpakaļ
CONDUCT.md beta-update 1 gadu atpakaļ
DESCRIPTION pre-travis check 9 mēnešus atpakaļ
LICENSE update 10 mēnešus atpakaļ
LICENSE.md update 10 mēnešus atpakaļ
NAMESPACE passed checks 10 mēnešus atpakaļ
NEWS.md log 9 mēnešus atpakaļ
README.Rmd pre-travis check 9 mēnešus atpakaļ
README.md pre-travis check 9 mēnešus atpakaļ
metis.Rproj passed checks 10 mēnešus atpakaļ

README.md

Travis-CI Build Status [Coverage Status](https://codecov.io/gh/hrbrmstr/metis CRAN_Status_Badge

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.