<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/working-with-r-and-big-data-use-replyr/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2017/07/working-with-r-and-big-data-use-replyr/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>In our latest <a rel="nofollow" href="http://www.win-vector.com/blog/tag/r-and-big-data/" target="_blank">R and Big Data</a> article we discuss <a rel="nofollow" href="https://cran.r-project.org/package=replyr" target="_blank">replyr</a>. </p>
<h1>Why <code>replyr</code></h1>
<p><code>replyr</code> stands for <strong>RE</strong>mote <strong>PLY</strong>ing of big data for <strong>R</strong>.</p>
<p>Why should <a rel="nofollow" href="https://www.r-project.org/" target="_blank">R</a> users try <a rel="nofollow" href="https://cran.r-project.org/package=replyr" target="_blank"><code>replyr</code></a>? Because it lets you take a number of common working patterns and apply them to remote data (such as databases or <a rel="nofollow" href="https://spark.apache.org/" target="_blank"><code>Spark</code></a>).</p>
<p><code>replyr</code> allows users to work with <code>Spark</code> or database data similar to how they work with local <code>data.frame</code>s. Some key capability gaps remedied by <code>replyr</code> include:</p>
<ul>
<li>Summarizing data: <code>replyr_summary()</code>.</li>
<li>Combining tables: <code>replyr_union_all()</code>.</li>
<li>Binding tables by row: <code>replyr_bind_rows()</code>.</li>
<li>Using the split/apply/combine pattern (<code>dplyr::do()</code>): <code>replyr_split()</code>, <code>replyr::gapply()</code>.</li>
<li>Pivot/anti-pivot (<code>gather</code>/<code>spread</code>): <code>replyr_moveValuesToRows()</code>/ <code>replyr_moveValuesToColumns()</code>.</li>
<li><a rel="nofollow" href="http://www.win-vector.com/blog/2017/06/managing-intermediate-results-when-using-rsparklyr/" target="_blank">Handle tracking</a>.</li>
<li>A <a rel="nofollow" href="http://www.win-vector.com/blog/2017/06/use-a-join-controller-to-document-your-work/" target="_blank">join controller</a>.</li>
</ul>
<p>You may have already learned to decompose your local data processing into steps including the above, so retaining such capabilities makes working with <code>Spark</code> and <a rel="nofollow" href="http://spark.rstudio.com/" target="_blank"><code>sparklyr</code></a> <em>much</em> easier. Some of the above capabilities will <a rel="nofollow" href="https://github.com/tidyverse/dplyr/issues/2923" target="_blank">likely come to the <code>tidyverse</code></a>, but the above implementations are build purely on top of <code>dplyr</code> and are the ones <a rel="nofollow" href="https://github.com/rstudio/sparklyr/issues/783" target="_blank">already being vetted and debugged</a> at production scale (I think these will be ironed out and reliable sooner).</p>
<p><span></span></p>
<p><a rel="nofollow" href="https://github.com/WinVector/replyr" target="_blank"><code>replyr</code></a> is the product of collecting experience applying <code>R</code> at production scale with many clients, collecting issues and filling-in gaps.</p>
<p>Below are some examples.</p>
<hr>
<h1>Examples</h1>
<hr>
<p>Things are changing fast right now, so let’s use the development versions of the packages for our examples.</p>
<div>
<pre><code>base::<span>date</span>()</code></pre>
</div>
<pre><code>## [1] &quot;Thu Jul  6 15:56:28 2017&quot;</code></pre>
<div>
<pre><code><span># devtools::install_github('rstudio/sparklyr')</span>
<span># devtools::install_github('tidyverse/dplyr')</span>
<span># devtools::install_github('tidyverse/dbplyr')</span>
<span># install.packages(&quot;replyr&quot;)</span>
<span>suppressPackageStartupMessages</span>(<span>library</span>(<span>&quot;dplyr&quot;</span>))
<span>packageVersion</span>(<span>&quot;dplyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.7.1.9000'</code></pre>
<div>
<pre><code><span>packageVersion</span>(<span>&quot;dbplyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '1.1.0.9000'</code></pre>
<div>
<pre><code><span>library</span>(<span>&quot;tidyr&quot;</span>)
<span>packageVersion</span>(<span>&quot;tidyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.6.3'</code></pre>
<div>
<pre><code><span>library</span>(<span>&quot;replyr&quot;</span>)
<span>packageVersion</span>(<span>&quot;replyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.4.2'</code></pre>
<div>
<pre><code><span>suppressPackageStartupMessages</span>(<span>library</span>(<span>&quot;sparklyr&quot;</span>))
<span>packageVersion</span>(<span>&quot;sparklyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.5.6.9012'</code></pre>
<div>
<pre><code><span># more memory as suggested in https://github.com/rstudio/sparklyr/issues/783</span>
config &lt;-<span> </span><span>spark_config</span>()
config[[<span>&quot;sparklyr.shell.driver-memory&quot;</span>]] &lt;-<span> &quot;8G&quot;</span>
sc &lt;-<span> </span>sparklyr::<span>spark_connect</span>(<span>version=</span><span>'2.1.0'</span>, 
                              <span>hadoop_version =</span> <span>'2.7'</span>,
                              <span>master =</span> <span>&quot;local&quot;</span>,
                              <span>config =</span> config)</code></pre>
</div>
<h2><code>summary</code></h2>
<p>Standard <code>summary()</code> and <code>glance()</code>, all fail on <code>Spark</code>.</p>
<div>
<pre><code>mtcars_spark &lt;-<span> </span><span>copy_to</span>(sc, mtcars)

<span># gives summary of handle, not data</span>
<span>summary</span>(mtcars_spark)</code></pre>
</div>
<pre><code>##     Length Class          Mode
## src 1      src_spark      list
## ops 2      op_base_remote list</code></pre>
<div>
<pre><code><span>packageVersion</span>(<span>&quot;broom&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.4.2'</code></pre>
<div>
<pre><code>broom::<span>glance</span>(mtcars_spark)</code></pre>
</div>
<pre><code>## Error: glance doesn't know how to deal with data of class tbl_sparktbl_sqltbl_lazytbl</code></pre>
<p><code>replyr_summary</code> works.</p>
<div>
<pre><code><span>replyr_summary</span>(mtcars_spark) %>%
<span> </span><span>select</span>(-lexmin, -lexmax, -nunique, -index)</code></pre>
</div>
<pre><code>##    column   class nrows nna    min     max       mean          sd
## 1     mpg numeric    32   0 10.400  33.900  20.090625   6.0269481
## 2     cyl numeric    32   0  4.000   8.000   6.187500   1.7859216
## 3    disp numeric    32   0 71.100 472.000 230.721875 123.9386938
## 4      hp numeric    32   0 52.000 335.000 146.687500  68.5628685
## 5    drat numeric    32   0  2.760   4.930   3.596563   0.5346787
## 6      wt numeric    32   0  1.513   5.424   3.217250   0.9784574
## 7    qsec numeric    32   0 14.500  22.900  17.848750   1.7869432
## 8      vs numeric    32   0  0.000   1.000   0.437500   0.5040161
## 9      am numeric    32   0  0.000   1.000   0.406250   0.4989909
## 10   gear numeric    32   0  3.000   5.000   3.687500   0.7378041
## 11   carb numeric    32   0  1.000   8.000   2.812500   1.6152000</code></pre>
<hr>
<h2><code>gather</code>/<code>spread</code></h2>
<p><code>tidyr</code> pretty much only works on local data.</p>
<div>
<pre><code>mtcars2 &lt;-<span> </span>mtcars %>%
<span> </span><span>mutate</span>(<span>car =</span> <span>row.names</span>(mtcars)) %>%
<span> </span><span>copy_to</span>(sc, ., <span>'mtcars2'</span>)

<span># errors out</span>
mtcars2 %>%<span> </span>
<span> </span>tidyr::<span>gather</span>(<span>'fact'</span>, <span>'value'</span>)</code></pre>
</div>
<pre><code>## Error in UseMethod(&quot;gather_&quot;): no applicable method for 'gather_' applied to an object of class &quot;c('tbl_spark', 'tbl_sql', 'tbl_lazy', 'tbl')&quot;</code></pre>
<div>
<pre><code>mtcars2 %>%
<span> </span><span>replyr_moveValuesToRows</span>(<span>nameForNewKeyColumn=</span> <span>'fact'</span>, 
                          <span>nameForNewValueColumn=</span> <span>'value'</span>, 
                          <span>columnsToTakeFrom=</span> <span>colnames</span>(mtcars),
                          <span>nameForNewClassColumn=</span> <span>'class'</span>) %>%
<span> </span><span>arrange</span>(car, fact)</code></pre>
</div>
<pre><code>## # Source:     lazy query [?? x 4]
## # Database:   spark_connection
## # Ordered by: car, fact
##            car  fact  value   class
##          </code></pre>