<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/painless-odbc-dplyr-connections-to-amazon-athena-and-apache-drill-with-r-odbc/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2018/04/20/painless-odbc-dplyr-connections-to-amazon-athena-and-apache-drill-with-r-odbc/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I spent some time this morning upgrading the JDBC driver (and changing up some supporting code to account for changes to it) for my <a rel="nofollow" href="https://github.com/hrbrmstr/metis" target="_blank"><code>metis</code> package<img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> which connects R up to Amazon Athena via RJDBC. I’m used to JDBC and have to deal with Java separately from R so I’m also comfortable with Java, JDBC and keeping R working with Java. I notified the #rstats Twitterverse about it and it started this thread (click on the embed to go to it — and, yes, this means Twitter is tracking you via this post unless you’ve blocked their JavaScript):</p>
<blockquote class="twitter-tweet" data-lang="en">
<p lang="en" dir="ltr">The (GitHub only for now) <a rel="nofollow" href="https://twitter.com/hashtag/rstats?src=hash&ref_src=twsrc%5Etfw" target="_blank">#rstats</a> metis package for wiring up R to <a rel="nofollow" href="http://twitter.com/amazonathema" target="_blank">@amazonathema</a> via RJDBC now uses &amp; includes the new Simba Athena JDBC Driver 2.0.2 JAR <a rel="nofollow" href="https://t.co/wvwV6IxCNd" target="_blank">https://t.co/wvwV6IxCNd</a> (cc: <a rel="nofollow" href="https://twitter.com/dabdine?ref_src=twsrc%5Etfw" target="_blank"><a rel="nofollow" href="http://twitter.com/dabdine" target="_blank">@dabdine</a></a>)</p>
<p>— hrbrmstr (<a rel="nofollow" href="http://twitter.com/hrbrmstr" target="_blank">@hrbrmstr</a>) <a rel="nofollow" href="https://twitter.com/hrbrmstr/status/987279127299358720?ref_src=twsrc%5Etfw" target="_blank">April 20, 2018</a></p>
</blockquote>
<p></p>
<p>If you do scroll through the thread you’ll see <a rel="nofollow" href="http://twitter.com/hadleywickham" target="_blank">@hadleywickham</a> suggested using the <code>odbc</code> package with the ODBC driver for Athena.</p>
<p>I, and others, have noted that ODBC on macOS (and — for me, at least — Linux) never really played well together for us. Given that I’m familiar with JDBC, I just gravitated towards using it after trying it out with raw Java and it worked fine in R.</p>
<p>Never one to discount advice from Hadley, I quickly <a rel="nofollow" href="https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html" target="_blank">grabbed the Athena ODBC driver</a> and installed it and wired up an <code>odbc</code> + <code>dplyr</code> connection almost instantly:</p>
<pre><code>library(odbc)
library(tidyverse)

DBI::dbConnect(
  odbc::odbc(), 
  driver = &quot;Simba Athena ODBC Driver&quot;, 
  Schema = &quot;redacted&quot;,
  AwsRegion = &quot;us-east-1&quot;,
  AuthenticationType = &quot;Default Credentials&quot;,
  S3OutputLocation = &quot;s3://aws-athena-query-results-redacted&quot;
) -> con


some_tbl &lt;- tbl(con, &quot;redacted&quot;)</code></pre>
<p>Apologies for the redaction and lack of output but we’ve removed the default example databases from our work Athena environment and I’m not near my personal systems, so a more complete example will have to wait until later.</p>
<p>The TLDR is that I can now use 100% <code>dplyr</code> idioms with Athena vs add one to the RJDBC driver I made for <code>metis</code>. The <code>metis</code> package will still be around to support JDBC on systems that do have issues with ODBC and to add other methods that work with the AWS Athena API (managing Athena vs the interactive queries part).</p>
<p>The downside is that I’m now even more likely to run up the AWS bill <img alt="😉" src="https://s.w.org/images/core/emoji/2.4/72x72/1f609.png" class="wp-smiley"></p>
<h3>What About Drill?</h3>
<p>I also maintain the <a rel="nofollow" href="https://github.com/hrbrmstr/sergeant" target="_blank"><code>sergeant</code> package<img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> which provides REST API and REST query access to Apache Drill along with a REST API <code>DBI</code> driver and an RJDBC interface for Drill. I remember trying to get the MapR ODBC client working with R a few years ago so I made the package (which was also a great learning experience).</p>
<p>I noticed there was a <a rel="nofollow" href="http://package.mapr.com/tools/MapR-ODBC/MapR_Drill/MapRDrill_odbc_v1.3.16.1049/" target="_blank">very recent MapR Drill ODBC driver released</a>. Since I was on a roll, I figured why not try it one more time, especially since the RStudio team has <a rel="nofollow" href="https://db.rstudio.com/" target="_blank">made it dead simple</a> to work with ODBC from R.</p>
<pre><code>library(odbc)
library(tidyverse)

DBI::dbConnect(
  odbc::odbc(), 
  driver = &quot;/Library/mapr/drill/lib/libdrillodbc_sbu.dylib&quot;,
  ConnectionType = &quot;Zookeeper&quot;,
  AuthenticationType = &quot;No Authentication&quot;,
  ZKCLusterID = &quot;CLUSTERID&quot;,
  ZkQuorum = &quot;HOST:2181&quot;,
  AdvancedProperties = &quot;CastAnyToVarchar=true;HandshakeTimeout=30;QueryTimeout=180;TimestampTZDisplayTimezone=utc;
ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;&quot;
) -> drill_con

(employee &lt;- tbl(drill_con, sql(&quot;SELECT * FROM cp.`employee.json`&quot;)))
## # Source:   SQL [?? x 16]
## # Database: Drill 01.13.0000[@Apache Drill Server/DRILL]
##    employee_id   full_name    first_name last_name position_id   position_title   store_id  
##    </code></pre>