<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/a-new-boto3-amazon-athena-client-wrapper-with-dplyr-async-query-support/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2018/07/20/a-new-boto3-amazon-athena-client-wrapper-with-dplyr-async-query-support/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>A <a rel="nofollow" href="https://rud.is/b/2018/07/14/alleviating-aws-athena-aggravation-with-asynchronous-assistance/" target="_blank">previous post</a> explored how to deal with Amazon Athena queries asynchronously. The function presented is a <em>beast</em>, though it is on purpose (to provide options for folks).</p>
<p>In reality, nobody really wants to use <code>rJava</code> wrappers much anymore and dealing with icky Python library calls directly just feels wrong, plus Python functions often return truly daft/ugly data structures. R users deserve better than that.</p>
<p>R is not a first-class citizen in Amazon-land and while the <a rel="nofollow" href="https://github.com/cloudyr" target="_blank"><code>cloudyr</code></a> project does a fine job building native-R packages for various Amazon services, the fact remains that the <em>official</em> Amazon SDKs are in other languages. The <code>reticulate</code> package provides an elegant interface to Python so it seemed to make sense to go ahead and wrap the <a rel="nofollow" href="https://boto3.readthedocs.io/en/latest/reference/services/athena.html" target="_blank"><code>boto3</code></a> Athena client into something more R-like and toss in the <code>collect_async()</code> function for good measure.</p>
<h3>Dependencies</h3>
<p>I forced a dependency on Python 3.5 because friends don’t let friends rely on dated, fragmented ecosystems. Python versions can gracefully (mostly) coexist so there should be no pain/angst associated with keeping an updated Python 3 environment around. As noted in the package, I highly recommend adding <code>RETICULATE_PYTHON=/usr/local/bin/python3</code> to your R environment (<code>~/.Renviron</code> is a good place to store it) since it will help <code>reticulate</code> find the proper target.</p>
<p>If <code>boto3</code> is not installed, you will need to do <code>pip3 install boto3</code> to ensure you have the necessary Python module available and associated with your Python 3 installation.</p>
<p>It may seem obvious, but an Amazon AWS account is also required and you should be familiar with the Athena service and AWS services in general. Most of the <code>roto.athena</code> functions have a set of optional parameters:</p>
<ul>
<li><code>aws_access_key_id</code></li>
<li><code>aws_secret_access_key</code></li>
<li><code>aws_session_token</code></li>
<li><code>region_name</code></li>
<li><code>profile_name</code></li>
</ul>
<p>Ideally, these should be in setup in the proper configuration files and you should let <code>boto3</code> handle the details of retrieving them. One parameter you will see used in many of my examples is `profile_name = “personal”. I have numerous AWS accounts and manage them via the profile ids. By ensuring the AWS configuration files are thoroughly populated, I avoid the need to load and pass around the various keys and/or tokens most AWS SDK API calls require. You can read more about profile management in the official docs: <a rel="nofollow" href="https://docs.aws.amazon.com/cli/latest/userguide/cli-config-files.html" target="_blank">1</a>, <a rel="nofollow" href="https://docs.aws.amazon.com/cli/latest/topic/config-vars.html" target="_blank">2</a>.</p>
<h3>Usage</h3>
<p>The project README and package manual pages are populated and have a smattering of usage examples. It is likely you will really just want to execute a manually prepared SQL query and retrieve the results <em>or</em> do the <code>dplyr</code> dance and collect the results asynchronously. We’ll cover both of those use-cases now, starting with a manual SQL query.</p>
<p>If you have not deleted it, your Athena instance comes with a <code>sampledb</code> that contains an <code>elb_logs</code> table. We’ll use that for our example queries. First, let’s get the packages we’ll be using out of the way:</p>
<pre><code>library(odbc)
library(DBI) # for dplyr access later
library(odbc) # for dplyr access later
library(roto.athena) # hrbrmstr/roto.athena on gh or gl
library(tidyverse) # b/c it rocks</code></pre>
<p>Now, we’ll prepare and execute the query. This is a super-simple one:</p>
<pre><code>query &lt;- &quot;SELECT COUNT(requestip) AS ct FROM elb_logs&quot;

start_query_execution(
  query = query,
  database = &quot;sampledb&quot;,
  output_location = &quot;s3://aws-athena-query-results-redacted&quot;,
  profile = &quot;personal&quot;
) -> qex_id</code></pre>
<p>The <code>qex_id</code> contains the query execution id. We can pass that along to get information on the status of the query:</p>
<pre><code>get_query_execution(qex_id, profile = &quot;personal&quot;) %>%
  glimpse()
## Observations: 1
## Variables: 10
## $ query_execution_id  </code></pre>