通过JDBC连接到S3雅典娜R通过

Question

我试图用JDBC连接到亚马逊的雅典娜。 R中使用RJDBC库，我有以下几点：

download.file('https://s3.amazonaws.com/athena-downloads/drivers/AthenaJDBC41-1.0.0.jar','AthenaJDBC41-1.0.0.jar') 

jdbcDriver <- JDBC(driverClass="com.amazonaws.athena.jdbc.AthenaDriver", 'AthenaJDBC41-1.0.0.jar', 
       identifier.quote="'") 

然后用凭据运行：

jdbcConnection <- dbConnect(jdbcDriver, 'jdbc:awsathena://athena.us-east-1.amazonaws.com:443/', 
       "s3_staging_dir URL", "s3://testbucket/"," 
       "USERNAME"," USERKEY","PASSWORD","PASSWORDKEY") 

，但我一直收到此错误：

Error in .jcall([email protected], "Ljava/sql/Connection;", "connect", as.character(url)[1], : 
    java.sql.SQLException: property s3_staging_dir must be set 

我试着设置在s3_staging_dr连接呼叫，但它没有工作。

任何指导将不胜感激。

Answer 1

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://yourbucket", 
            user=Sys.getenv("ATHENA_USER"), 
            password=Sys.getenv("ATHENA_PASSWORD")) 


dbListTables(con) 
## [1] "elb_logs" 

把你的访问密钥和seekrit在.Renviron（在明显名为ENV瓦尔），重启R和尝试上面（与你的水桶是访问。

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.441734Z", "2014-09-27T00:01:58.366715Z", "2014-09-27T00:02:29.446363Z", "2014-09-2... 
## $ elbname    <chr> "lb-demo", "lb-demo", "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", "245.195.140.77", "245.195.140.77", "243.71.49.173", "240.139.5.14", "251.192.4... 
## $ requestport   <dbl> 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", "254.64.224.54", "254.64.224.54", "250.244.20.109", "247.65.176.249", "250.2... 
## $ backendport   <dbl> 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.045724, 0.029918 
## $ 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   <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 
## $ sentbytes    <dbl> 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://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", "HTTP/1.1", "HTTP/1.1", "HTTP/1.1"