<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/sergeant-an-r-boot-camp-for-apache-drill/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2016/12/20/sergeant-a-r-boot-camp-for-apache-drill/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I <a target="_blank" rel="nofollow" href="https://rud.is/b/2016/12/16/minding-the-zookeeper-with-r/">recently mentioned</a> that I’ve been working on a development version of an <a target="_blank" rel="nofollow" href="https://drill.apache.org/">Apache Drill</a> R package called <a target="_blank" rel="nofollow" href="https://github.com/hrbrmstr/sergeant"><code>sergeant</code></a>. Here’s a lifted “TLDR” on Drill:</p>
<div>
<p>Drill supports a variety of NoSQL databases and file systems, including HBase, MongoDB, MapR-DB, HDFS, MapR-FS, Amazon S3, Azure Blob Storage, Google Cloud Storage, Swift, NAS and local files. A single query can join data from multiple datastores. For example, you can join a user profile collection in MongoDB with a directory of event logs in Hadoop.</p>
<p>Drill’s datastore-aware optimizer automatically restructures a query plan to leverage the datastore’s internal processing capabilities. In addition, Drill supports data locality, so it’s a good idea to co-locate Drill and the datastore on the same nodes.</p>
</div>
<p>It also supports reading formats such as: </p>
<ul>
<li>Avro</li>
<li>[CTP]SV ([C]omma-, [T]ab-, [P]ipe-Separated-Values)</li>
<li>Parquet</li>
<li>Hadoop Sequence Files</li>
</ul>
<p>It’s a <em>bit</em> like Spark in that you can run it on a single workstation and scale up to a YUGE cluster. It lacks the ML components of Spark, but it connects to <em>everything</em> without the need to define a schema up front. Said “everything” includes parquet files on local filesystems, so if you need to slice through GBs of parquet data and have a beefy enough Linux workstation (I believe Drill runs on Windows and know it runs on macOS fine, too, but that’s $$$ for a bucket of memory &amp; disk space) you can take advantage of the optimized processing power Drill offers on a single system (while also joining the data with any other data format you can think of). You can also seamlessly move the data to a cluster and barely tweak your code to support said capacity expansion.</p>
<h3>Why <code>sergeant</code>?</h3>
<p>There’s already an R package on CRAN to work with Drill: <a target="_blank" rel="nofollow" href="https://cran.r-project.org/package=DrillR"><code>DrillR</code></a>. It’s S4 class-based, has a decent implementation and interfaces with the REST API. However, it sticks <code>httr::verbose()</code> <em>everywhere</em>: <a target="_blank" rel="nofollow" href="https://github.com/cran/DrillR/search?utf8=%E2%9C%93&q=verbose">https://github.com/cran/DrillR/search?utf8=%E2%9C%93&amp;q=verbose</a>.</p>
<p>The <code>sergeant</code> package interfaces with the REST API as well, but also works with the JDBC driver (the dev version includes the driver with the package, but this will be removed for the eventual CRAN submission) and includes some other niceties around Drill options viewing and setting and some other non-SQL bits. Of note: the REST API version shows an <code>httr</code> progress bar for data downloading and you can wrap the calls with <code>httr::with_verbose(…)</code> if you <em>really</em> like seeing cURL messages.</p>
<p>The other thing <code>sergeant</code> has going for it is a nascent <code>dplyr</code> interface. Presently, this is a hack-ish wrapper around the <code>RJDBC</code> <code>JDBCConnection</code> presented by the Drill JDBC driver. While basic functionality works, I firmly believe Drill needs it’s own DBI driver (like is second-cousin Preso has) to avoid collisions withy any other JDBC connections you might have open, plus more work needs to be done under the covers to deal with quoting properly and exposing more Drill built-in SQL functions.</p>
<h3>SQL vs <code>dplyr</code></h3>
<p>For some truly complex data machinations you’re going to want to work at the SQL level and I think it’s important to know SQL if you’re ever going to do data work outside JSON &amp; CSV files <em>just to appreciate how much gnashing of teeth</em> <code>dplyr</code> <em>saves you from</em>. Using SQL for many light-to-medium aggregation tasks that feed data to R can feel like you’re banging rocks together to make fire when you could just be using your R precision welder. What would you rather write:</p>
<pre><code>SELECT  gender ,  marital_status , COUNT(*) AS  n 
FROM  cp.`employee.json` 
GROUP BY  gender ,  marital_status</code></pre>
<p>in a <code>drill-embedded</code> or <code>drill-localhost</code> SQL shell? Or:</p>
<pre><code>library(RJDBC)
library(dplyr)
library(sergeant)

ds &lt;- src_drill(&quot;localhost:31010&quot;, use_zk=FALSE)

db &lt;- tbl(ds, &quot;cp.`employee.json`&quot;) 

count(db, gender, marital_status) %>% collect()</code></pre>
<p>(NOTE: that SQL statement is what ultimately gets sent to Drill from <code>dplyr</code>)</p>
<p>Now, <code>dplyr</code> <code>tbl_df</code> idioms don’t translate 1:1 to all other <code>src_</code>es, but they are much easier on the eyes and more instructive in analysis code (and, I fully admit that said statement is more opinion than fact). </p>
<h3><code>sergeant</code> and <code>dplyr</code></h3>
<p>The <code>src_drill()</code> function uses the JDBC Drill driver and, hence, has an <code>RJDBC</code> dependency. The Presto folks (a “competing” offering to Drill) wrapped a <a target="_blank" rel="nofollow" href="https://github.com/prestodb/RPresto"><code>DBI</code> interface</a> around their REST API to facilitate the use of <code>dplyr</code> idioms. I’m not sold on whether I’ll continue with a lightweight DBI wrapper using RJDBC or go the <code>RPresto</code> route, but for now the basic functionality works and changing the back-end implementation should not break anything (much).</p>
<h3>You’ve said “parquet” alot…</h3>
<p>Yes. Yes, I have. Parquet is a “big data” compressed columnar storage format that is generally used in Hadoop shops. Parquet is <a target="_blank" rel="nofollow" href="https://github.com/wesm/feather/issues/188">different from ‘feather’</a> (‘feather’ is based on another Apache foundation project: <a target="_blank" rel="nofollow" href="https://arrow.apache.org/">Arrow</a>). Arrow/feather is great for things that fit in memory. Parquet and the idioms that sit on top of it enable having large amounts data available in a cluster for processing with Hadoop / Spark / Drill / Presto (etc). Parquet is great for storing all kinds of data, including log and event data which I have to work with quite a bit and it’s great being able to prototype on a single workstation then move code to hit a production cluster. Plus, it’s super-easy to, say, convert an entire, nested directory tree of daily JSON log files into parquet with Drill:</p>
<pre><code>CREATE TABLE dfs.destination.`source/2016/12/2016_12_source_event_logs.parquet` AS
  SELECT src_ip, dst_ip, src_port, dst_port, event_message, ts 
  FROM dfs.source.`/log/dir/root/2016/12/*/event_log.json`;</code></pre>
<h3>Kick the tyres</h3>
<p>The REST and JDBC functions are solid (I’ve been using them at work for a while) and the <code>dplyr</code> support has handled some preliminary production work well (though, remember, it’s not fully-baked). There are plenty of examples — including a <code>dplyr::left_join()</code> between parquet and JSON data — in the <a target="_blank" rel="nofollow" href="https://github.com/hrbrmstr/sergeant">README</a> and all the exposed functions have documentation. </p>
<p>File <a target="_blank" rel="nofollow" href="https://github.com/hrbrmstr/sergeant/issues">an issue</a> with a feature request or bug report. </p>
<p>I expect to have this CRAN-able in January, 2017.</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/20/sergeant-a-r-boot-camp-for-apache-drill/"> 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/291FRjjQ7JI"></a></div>