通过JDBC连接到R中的S3 Athena [英] Connecting to S3 Athena in R via JDBC
本文介绍了通过JDBC连接到R中的S3 Athena的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用JDBC连接到Amazon的Athena.我在R中使用RJDBC库的过程如下:
I'm trying to connect with JDBC to Amazon's Athena. Using RJDBC library in R i have the following:
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" )
但是我不断收到此错误:
but i keep on getting this error:
Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1], :
java.sql.SQLException: property s3_staging_dir must be set
我尝试在连接调用中设置s3_staging_dr,但是它不起作用.
I tried setting the s3_staging_dr in the connection call but it did not work.
任何指导将不胜感激.
推荐答案
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 vars中),重新启动R并尝试以上操作(使用您可以访问的存储桶).
Put your access key and seekrit in .Renviron
(in the obviously named env vars), restart R and try the above (with a bucket of yours that is accessible.
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"
这篇关于通过JDBC连接到R中的S3 Athena的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文