<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/big-data-transforms/"></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/10/big-data-transforms/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>As part of our consulting practice <a rel="nofollow" href="http://www.win-vector.com/" target="_blank">Win-Vector LLC</a> has been helping a few clients stand-up advanced analytics and machine learning stacks using <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> and substantial data stores (such as relational database variants such as <code>PostgreSQL</code> or big data systems such as <code>Spark</code>).</p>
<p><center><br>
<a rel="nofollow" href="http://www.win-vector.com/" target="_blank"><img border="0" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/10/IMG_6061-3.jpg?w=660" alt="IMG 6061 3" title="IMG_6061-3.jpg" data-recalc-dims="1"></a><br>
</center></p>
<p>Often we come to a point where we or a partner realize: &quot;the design would be a whole lot easier if we could phrase it in terms of higher order data operators.&quot;</p>
<p><span></span></p>
<p>The <code>R</code> package <a rel="nofollow" href="https://cran.r-project.org/package=DBI" target="_blank"><code>DBI</code></a> gives us direct access to <code>SQL</code> and the package <a rel="nofollow" href="https://cran.r-project.org/package=dplyr" target="_blank"><code>dplyr</code></a> gives us access to a transform grammar that can either be executed or translated into <code>SQL</code>.</p>
<p>But, as we point out in the <a rel="nofollow" href="https://winvector.github.io/replyr/" target="_blank"><code>replyr</code></a> <a rel="nofollow" href="https://cran.r-project.org/web/packages/replyr/README.html" target="_blank"><code>README</code></a>: moving from in-memory <code>R</code> to large data systems is always a bit of a shock as you lose a lot of your higher order data operators or transformations. Missing operators include:</p>
<ul>
<li>union (binding by rows many data frames into a single data frame).</li>
<li>split (splitting a single data frame into many data frames).</li>
<li>pivot (moving row values into columns).</li>
<li>un-pivot (moving column values to rows).</li>
</ul>
<p>I can repeat this. If you are an <code>R</code> user used to using one of <code>dply::bind_rows()</code> , <code>base::split()</code>, <code>tidyr::spread()</code>, or <code>tidyr::gather()</code>: you will find these functions do not work on remote data sources, but have replacement implementations in the <code>replyr</code> package.</p>
<p>For example:</p>
<div>
<pre><code><span>library</span>(<span>&quot;RPostgreSQL&quot;</span>)</code></pre>
</div>
<pre><code>## Loading required package: DBI</code></pre>
<div>
<pre><code><span>suppressPackageStartupMessages</span>(<span>library</span>(<span>&quot;dplyr&quot;</span>))
isSpark &lt;-<span> </span><span>FALSE</span>

<span># Can work with PostgreSQL</span>
my_db &lt;-<span> </span>DBI::<span>dbConnect</span>(<span>dbDriver</span>(<span>&quot;PostgreSQL&quot;</span>),
                        <span>host =</span> <span>'localhost'</span>,
                        <span>port =</span> <span>5432</span>,
                        <span>user =</span> <span>'postgres'</span>,
                        <span>password =</span> <span>'pg'</span>)
 
<span># # Can work with Sparklyr</span>
<span># my_db &lt;-sparklyr::spark_connect(version='2.2.0', </span>
<span># master = &quot;local&quot;)</span>
<span># isSpark &lt;-TRUE</span>

d &lt;-<span> </span>dplyr::<span>copy_to</span>(my_db, <span>data.frame</span>(<span>x =</span>  <span>c</span>(<span>1</span>,<span>5</span>), 
                                      <span>group =</span> <span>c</span>(<span>'g1'</span>, <span>'g2'</span>),
                                      <span>stringsAsFactors =</span> <span>FALSE</span>), 
                    <span>'d'</span>)
<span>print</span>(d)</code></pre>
</div>
<pre><code>## # Source:   table</code></pre>