<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/driving-drill-dynamically-with-docker-and-updating-storage-configurations-on-the-fly-with-sergeant/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2018/09/09/driving-drill-dynamically-with-docker-and-updating-storage-configurations-on-the-fly-with-sergeant/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The <a rel="nofollow" href="https://rud.is/b/2018/09/09/driving-drill-dynamically-with-docker-and-updating-storage-configurations-on-the-fly-with-sergeant/gitlab.com/hrbrmstr/sergeant" target="_blank"><code>sergeant</code><img alt="🔗" src="https://i0.wp.com/s.w.org/images/core/emoji/11/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> package has a minor update that adds REST API coverage for <a rel="nofollow" href="https://drill.apache.org/docs/rest-api-introduction/" target="_blank">two “new” <code>storage</code> endpoints</a> that make it possible to add, update and remove storage configurations on-the-fly without using the GUI or manually updating a config file.</p>
<p>This is an especially handy feature when paired with Drill’s new, official <a rel="nofollow" href="https://drill.apache.org/docs/running-drill-on-docker/" target="_blank">Docker container</a> since that means we can:</p>
<ul>
<li>fire up a clean Drill instance</li>
<li>modify the storage configuration (to, say, point to a local file system directory)</li>
<li>execute SQL ops</li>
<li>destroy the Drill instance</li>
</ul>
<p>all from within R.</p>
<p>This is even more handy for those of us who prefer handling JSON data in Drill than in R directly or with <code>sparklyr</code>.</p>
<h3>Quick Example</h3>
<p>In a few weeks most of the following verbose-code-snippets will have a more diminutive and user-friendly interface within <code>sergeant</code>, but for now we’ll perform the above bulleted steps with some data that was used in <a rel="nofollow" href="https://gitlab.com/hrbrmstr/dnshelpers/" target="_blank">a recent new package</a> which was also generated by another <a rel="nofollow" href="https://gitlab.com/hrbrmstr/zdnsr" target="_blank">recent new package</a>. The <code>zdnsr::zdns_exec()</code> function ultimately generates a deeply nested JSON file that I really prefer working with in Drill before shunting it into R. Said file is stored, say, in the <code>~/drilldat</code> directory.</p>
<p>Now, I have Drill running all the time on almost every system I use, but we’ll pretend I don’t for this example. I’ve run <code>zdns_exec()</code> and generated the JSON file and it’s in the aforementioned directory. Let’s fire up an instance and connect to it:</p>
<pre><code>library(sergeant) # git[hu|la]b:hrbrmstr/sergeant
library(dplyr)

docker &lt;- Sys.which(&quot;docker&quot;) # you do need docker. it's a big dependency, but worth it IMO

(system2(
  command = docker,  
  args = c(
    &quot;run&quot;, &quot;-i&quot;, 
    &quot;--name&quot;, &quot;drill-1.14.0&quot;, 
    &quot;-p&quot;, &quot;8047:8047&quot;, 
    &quot;-v&quot;, paste0(c(path.expand(&quot;~/drilldat&quot;), &quot;/drilldat&quot;), collapse=&quot;:&quot;),
    &quot;--detach&quot;, 
    &quot;-t&quot;, &quot;drill/apache-drill:1.14.0&quot;,
    &quot;/bin/bash&quot;
  ),
  stdout = TRUE
) -> drill_container)
## [1] &quot;d6bc79548fa073d3bfbd32528a12669d753e7a19a6258e1be310e1db378f0e0d&quot;</code></pre>
<p>The above snippet fires up a Drill Docker container (downloads it, too, if not already local) and wires up a virtual directory to it.</p>
<p>We should wait a couple seconds and make sure we can connect to it:</p>
<pre><code>drill_connection() %>% 
  drill_active()
## [1] TRUE</code></pre>
<p>Now, we need to add a storage configuration so we can access our virtual directory. Rather than modify <code>dfs</code> we’ll add a <code>drilldat</code> plugin that will work with the local filesystem just like <code>dfs</code> does:</p>
<pre><code>drill_connection() %>%
  drill_mod_storage(
    name = &quot;drilldat&quot;,
    config = '
{
  &quot;config&quot; : {
    &quot;connection&quot; : &quot;file:///&quot;, 
    &quot;enabled&quot; : true,
    &quot;formats&quot; : null,
    &quot;type&quot; : &quot;file&quot;,
    &quot;workspaces&quot; : {
      &quot;root&quot; : {
        &quot;location&quot; : &quot;/drilldat&quot;,
        &quot;writable&quot; : true,
        &quot;defaultInputFormat&quot;: null
      }
    }
  },
  &quot;name&quot; : &quot;drilldat&quot;
}
')
## $result
## [1] &quot;success&quot;</code></pre>
<p>Now, we can perform all the Drill ops <code>sergeant</code> has to offer, including ones like this:</p>
<pre><code>(db &lt;- src_drill(&quot;localhost&quot;))
## src:  DrillConnection
## tbls: cp.default, dfs.default, dfs.root, dfs.tmp, drilldat.default, drilldat.root,
##   INFORMATION_SCHEMA, sys

tbl(db, &quot;drilldat.root.`/*.json`&quot;)
## # Source:   table&lt;drilldat.root.`/*.json`> [?? x 10]
## # Database: DrillConnection
##    data                                              name   error class status timestamp          
##    </code></pre>