<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/r%e2%81%b6-using-r-with-amazon-athena-aws-temporary-security-credentials/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2017/05/16/r%E2%81%B6-using-r-with-amazon-athena-awas-temporary-security-credentials/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Most of the examples of working with most of the AWS services show basic username &amp; password authentication. That’s all well-and-good, but many shops use the <a rel="nofollow" href="http://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_temp.html" target="_blank">AWS Security Token Service</a> to provide temporary credentials and session tokens to limit exposure and provide more uniform multi-factor authentication. At my workplace, Frank Mitchell created a nice <a rel="nofollow" href="https://github.com/rapid7/awsaml" target="_blank">electron app</a> to make it super easy to create and re-up these credentials. The downside of this is that all AWS service usage for work requires using these credentials and I was having the darndest time trying to get Athena’s JDBC driver working with it (but I wasn’t spending alot of time on it as I tend to mirror research data to a local, beefy Apache Drill server).</p>
<p>I finally noticed the </p>
<p><code>com.amazonaws.athena.jdbc.shaded.com.amazonaws.auth.EnvironmentVariableCredentialsProvider</code> </p>
<p>class and decided to give the following a go (you will need to point <code>fil</code> to wherever you have the Athena jar file):</p>
<pre><code>library(RJDBC)
library(tidyverse)

fil &lt;- &quot;~/Drivers/AthenaJDBC41-1.0.1.jar&quot;
drv &lt;- JDBC(driverClass=&quot;com.amazonaws.athena.jdbc.AthenaDriver&quot;, fil, identifier.quote=&quot;'&quot;)

aws &lt;- ini::read.ini(&quot;~/.aws/credentials&quot;)

Sys.setenv(AWS_ACCESS_KEY_ID = aws[Sys.getenv(&quot;AWS_PROFILE&quot;)][[1]]$aws_access_key_id)
Sys.setenv(AWS_SECRET_ACCESS_KEY = aws[Sys.getenv(&quot;AWS_PROFILE&quot;)][[1]]$aws_secret_access_key)
Sys.setenv(AWS_SESSION_TOKEN = aws[Sys.getenv(&quot;AWS_PROFILE&quot;)][[1]]$aws_session_token)

provider &lt;- &quot;com.amazonaws.athena.jdbc.shaded.com.amazonaws.auth.EnvironmentVariableCredentialsProvider&quot;

con &lt;- dbConnect(drv, 'jdbc:awsathena://athena.us-east-1.amazonaws.com:443/',
                 s3_staging_dir=Sys.getenv(&quot;AWS_S3_STAGING_DIR&quot;),
                 schema_name=&quot;DEFAULT_DB_SCHEMA_NAME&quot;,
                 aws_credentials_provider_class=provider)

dbListTables(con)

dbListFields(con, &quot;SOME_TABLE_IN_THE_DEFAULT_DB&quot;)

dbGetQuery(con, &quot;SELECT * FROM DEFAULT_DB_SCHEMA_NAME.SOME_TABLE_IN_THE_DEFAULT_DB limit 10;&quot;)</code></pre>
<p>YMMV on Windows (comments about what does and does not work on Windows are welcome).</p>
<p>The <code>provider</code> line was the key element I was missing prior to last night.</p>
<p>The Awsaml utility monitors/maintains entries under it’s purview <code>credentials</code> file and keeps consistent profile ids, so I keep that <code>AWS_PROFILE</code> setting in my <code>~/.Renviron</code>.</p>
<p>I also keep the default S3 Athena data staging bucket in an environment variable as well. </p>
<p>If you provide a default <code>schema_name</code> then you can list tables and fields but queries need fully qualified database (Amazon calls them “schemas”) dot table name.</p>
<p>Initial attempts to have this setup “just work” with <code>dplyr</code> 0.6.0 (the forthcoming EPIC release) were unsuccessful but I’ll poke at all this when I get time and likely write a small Athena package to help smooth over rougher areas.</p>
<p>Y’all likely figured all this out way before I did, but in the event someone else is looking for the information, it should be google-able now.</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/2017/05/16/r%E2%81%B6-using-r-with-amazon-athena-awas-temporary-security-credentials/"> 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 rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/TBeI6Oh3VFc" height="1"></a></div>