<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/interacting-with-amazon-athena-from-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2016/12/05/interacting-with-amazon-athena-from-r/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>This is a short post for those looking to test out <a target="_blank" rel="nofollow" href="https://aws.amazon.com/athena/">Amazon Athena</a> with R.</p>
<p>Amazon makes Athena available via JDBC, so you can use <code>RJDBC</code> to query data. All you need is their JAR file and some setup information. Here’s how to get the JAR file to the current working directory:</p>
<pre><code>URL &lt;- 'https://s3.amazonaws.com/athena-downloads/drivers/AthenaJDBC41-1.0.0.jar'
fil &lt;- basename(URL)
if (!file.exists(fil)) download.file(URL, fil)</code></pre>
<p>To avoid putting credentials in code, you can store the AWS key and secret you’re using for the queries in <code>ATHENA_USER</code> and <code>ATHENA_PASSWORD</code> environment variables via <code>~/.Renviron</code>. You’ll also need an S3 bucket writable by those credentials for the Athena staging directory. With that info in hand, it’s easy to connect:</p>
<pre><code>library(RJDBC)
library(dplyr)

drv &lt;- JDBC(driverClass=&quot;com.amazonaws.athena.jdbc.AthenaDriver&quot;, fil, identifier.quote=&quot;'&quot;)

con &lt;- jdbcConnection &lt;- dbConnect(drv, 'jdbc:awsathena://athena.us-east-1.amazonaws.com:443/',
                                   s3_staging_dir=&quot;s3://accessible-bucket&quot;,
                                   user=Sys.getenv(&quot;ATHENA_USER&quot;),
                                   password=Sys.getenv(&quot;ATHENA_PASSWORD&quot;))</code></pre>
<p>Even if you have no data configured in Athena, you can check out the test data available to all:</p>
<pre><code>dbListTables(con)
## [1] &quot;elb_logs&quot;</code></pre>
<p>If that worked, then you should be able to query data (using the fully qualified table name in this case):</p>
<pre><code>dbGetQuery(con, &quot;SELECT * FROM sampledb.elb_logs LIMIT 10&quot;) %>% 
  dplyr::glimpse()
## Observations: 10
## Variables: 16
## $ timestamp             &lt;chr> &quot;2014-09-27T00:00:25.424956Z&quot;, &quot;2014-09-27T00:00:56.439218Z&quot;, &quot;2014-09-27T00:01:27.441734Z&quot;, &quot;2014-09-27T00:01:58.366715Z&quot;, &quot;2014-09-27T00:02:29.446363Z&quot;, &quot;2014-09-2...
## $ elbname               &lt;chr> &quot;lb-demo&quot;, &quot;lb-demo&quot;, &quot;lb-demo&quot;, &quot;lb-demo&quot;, &quot;lb-demo&quot;, &quot;lb-demo&quot;, &quot;lb-demo&quot;, &quot;lb-demo&quot;, &quot;lb-demo&quot;, &quot;lb-demo&quot;
## $ requestip             &lt;chr> &quot;241.230.198.83&quot;, &quot;252.26.60.51&quot;, &quot;250.244.20.109&quot;, &quot;247.59.58.167&quot;, &quot;254.64.224.54&quot;, &quot;245.195.140.77&quot;, &quot;245.195.140.77&quot;, &quot;243.71.49.173&quot;, &quot;240.139.5.14&quot;, &quot;251.192.4...
## $ requestport           &lt;dbl> 27026, 27026, 27026, 27026, 27026, 27026, 27026, 27026, 27026, 27026
## $ backendip             &lt;chr> &quot;251.192.40.76&quot;, &quot;249.89.116.3&quot;, &quot;251.111.156.171&quot;, &quot;251.139.91.156&quot;, &quot;251.111.156.171&quot;, &quot;254.64.224.54&quot;, &quot;254.64.224.54&quot;, &quot;250.244.20.109&quot;, &quot;247.65.176.249&quot;, &quot;250.2...
## $ backendport           &lt;dbl> 443, 8888, 8888, 8888, 8000, 8888, 8888, 8888, 8888, 8888
## $ requestprocessingtime &lt;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 &lt;dbl> 0.046598, 0.038973, 0.047054, 0.039845, 0.061461, 0.037791, 0.047035, 0.048792, 0.045724, 0.029918
## $ clientresponsetime    &lt;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       &lt;chr> &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;
## $ backendresponsecode   &lt;chr> &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;, &quot;400&quot;, &quot;400&quot;, &quot;200&quot;, &quot;200&quot;, &quot;200&quot;
## $ receivedbytes         &lt;dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
## $ sentbytes             &lt;dbl> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2
## $ requestverb           &lt;chr> &quot;GET&quot;, &quot;GET&quot;, &quot;GET&quot;, &quot;GET&quot;, &quot;GET&quot;, &quot;GET&quot;, &quot;GET&quot;, &quot;GET&quot;, &quot;GET&quot;, &quot;GET&quot;
## $ url                   &lt;chr> &quot;http://www.abcxyz.com:80/jobbrowser/?format=json&amp;state=running&amp;user=20g578y&quot;, &quot;http://www.abcxyz.com:80/jobbrowser/?format=json&amp;state=running&amp;user=20g578y&quot;, &quot;http:/...
## $ protocol              &lt;chr> &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;, &quot;HTTP/1.1&quot;</code></pre>
<p>And, you can disconnect when done:</p>
<pre><code>dbDisconnect(con)</code></pre>
<p>You should probably store the JAR file in a central location and refer to it that way in “production” scripts.</p>
<p>Now, you can go crazy querying data and racking up AWS charges <img alt="😀" class="wp-smiley" src="https://s.w.org/images/core/emoji/2/72x72/1f600.png">.</p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://rud.is/b/2016/12/05/interacting-with-amazon-athena-from-r/"> R – rud.is</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/7IeWRND43ag"></a></div>