<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/collecting-expressions-in-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2018/08/collecting-expressions-in-r/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Not a full <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> article, but a quick note demonstrating by example the advantage of being able to collect many expressions and pack them into a single <a rel="nofollow" href="https://winvector.github.io/rquery/reference/extend_se.html" target="_blank"><code>extend_se()</code></a> node.</p>
<p><span></span></p>
<p>This example may seem extreme or unnatural. However we have seen once you expose a system to enough users you see a lot more extreme use cases than you would at first expect. We have actually seen large tens of columns added to a mart in a large irregular block (so not the same transform for each columns) by building up long pipelines, so this simplified example is in fact relevant to production deployments.</p>
<p>First set up our packages, database connection, and remote table.</p>
<div>
<pre><code><span>library</span>(<span>&quot;dplyr&quot;</span>)</code></pre>
</div>
<pre><code>## Warning: package 'dplyr' was built under R version 3.5.1

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union</code></pre>
<div>
<pre><code><span>library</span>(<span>&quot;rquery&quot;</span>)
<span>library</span>(<span>&quot;microbenchmark&quot;</span>)
<span>library</span>(<span>&quot;ggplot2&quot;</span>)
<span>library</span>(<span>&quot;WVPlots&quot;</span>)
<span>library</span>(<span>&quot;rqdatatable&quot;</span>)
<span>library</span>(<span>&quot;cdata&quot;</span>)

use_spark &lt;-<span> </span><span>TRUE</span>
<span># connect</span>
if(use_spark) {
  conf &lt;-<span> </span>sparklyr::<span>spark_config</span>()
  conf$spark.yarn.am.cores &lt;-<span> </span><span>2</span>
  conf$spark.executor.cores &lt;-<span> </span><span>2</span>
  mem_size &lt;-<span> &quot;4G&quot;</span>
  conf$spark.executor.memory &lt;-<span> </span>mem_size
  conf$spark.yarn.am.memory &lt;-<span> </span>mem_size 
  conf$<span>`</span><span>sparklyr.shell.driver-memory</span><span>`</span> &lt;-<span> </span>mem_size
  conf$<span>`</span><span>sparklyr.shell.executor-memory</span><span>`</span> &lt;-<span> </span>mem_size
  conf$<span>`</span><span>spark.yarn.executor.memoryOverhead</span><span>`</span> &lt;-<span> </span>mem_size
  con &lt;-<span> </span>sparklyr::<span>spark_connect</span>(<span>version=</span><span>'2.2.0'</span>, 
                                 <span>master =</span> <span>&quot;local&quot;</span>,
                                 <span>config =</span> conf)
} else {
  con &lt;-<span> </span>DBI::<span>dbConnect</span>(RPostgreSQL::<span>PostgreSQL</span>(),
                        <span>host =</span> <span>'localhost'</span>,
                        <span>port =</span> <span>5432</span>,
                        <span>user =</span> <span>'johnmount'</span>,
                        <span>password =</span> <span>''</span>)
}

<span># configure rquery connection options</span>
dbopts &lt;-<span> </span><span>rq_connection_tests</span>(con)
db_hdl &lt;-<span> </span><span>rquery_db_info</span>(
  <span>connection =</span> con,
  <span>is_dbi =</span> <span>TRUE</span>,
  <span>connection_options =</span> dbopts)
<span>print</span>(db_hdl)</code></pre>
</div>
<pre><code>## [1] &quot;rquery_db_info(DBIConnection_spark_connection_spark_shell_connection, is_dbi=TRUE, note=\&quot;\&quot;)&quot;</code></pre>
<div>
<pre><code>nrow &lt;-<span> </span><span>1000000</span>
td &lt;-<span> </span><span>rq_copy_to</span>(db_hdl, 
                 <span>&quot;d&quot;</span>,
                 <span>data.frame</span>(<span>x =</span> <span>seq_len</span>(nrow)),
                 <span>overwrite =</span> <span>TRUE</span>,
                 <span>temporary =</span> <span>TRUE</span>)

tbl &lt;-<span> </span>dplyr::<span>tbl</span>(con, <span>&quot;d&quot;</span>)

ncol &lt;-<span> </span><span>100</span></code></pre>
</div>
<p><a rel="nofollow" href="https://cran.r-project.org/package=rquery" target="_blank"><code>rquery</code></a> torture function: add 100 columns to a 1000000 row table.</p>
<div>
<pre><code>rquery_fn &lt;-<span> </span>function(db_hdl, td, ncol, <span>return_sql =</span> <span>FALSE</span>) {
  expressions &lt;-<span> </span><span>character</span>(<span>0</span>)
  for(i in <span>seq_len</span>(ncol)) {
    expri &lt;-<span> </span><span>paste0</span>(<span>&quot;x_&quot;</span>, i) %:=%<span> </span><span>paste0</span>(<span>&quot;x + &quot;</span>, i)
    expressions &lt;-<span> </span><span>c</span>(expressions, expri)
  }
  ops &lt;-<span> </span>td %.>%
<span> </span><span>extend_se</span>(., expressions) %.>%
<span> </span><span>select_rows_nse</span>(., x ==<span> </span><span>3</span>)
  if(return_sql) {
    <span>return</span>(<span>to_sql</span>(ops, db_hdl))
  }
  <span># force execution</span>
  db_hdl %.>%<span> </span>ops
}

<span>cat</span>(<span>rquery_fn</span>(db_hdl, td, <span>5</span>, <span>return_sql =</span> <span>TRUE</span>))</code></pre>
</div>
<pre><code>## SELECT * FROM (
##  SELECT
##   `x`,
##   `x` + 1  AS `x_1`,
##   `x` + 2  AS `x_2`,
##   `x` + 3  AS `x_3`,
##   `x` + 4  AS `x_4`,
##   `x` + 5  AS `x_5`
##  FROM (
##   SELECT
##    `x`
##   FROM
##    `d`
##   ) tsql_97238965696940256963_0000000000
## ) tsql_97238965696940256963_0000000001
## WHERE `x` = 3</code></pre>
<div>
<pre><code><span>rquery_fn</span>(db_hdl, td, <span>5</span>)</code></pre>
</div>
<pre><code>##   x x_1 x_2 x_3 x_4 x_5
## 1 3   4   5   6   7   8</code></pre>
<p>The row-selection step is cut down on the in-memory cost of bringing the result back to <code>R</code>. Obviously we could optimize the example away by pivoting the filter to earlier in the example pipeline. We ask the reader to take this example as a stand-in for a more complicated (though nasty) real-world example where such optimizations are not available.</p>
<p>Same torture for <a rel="nofollow" href="https://cran.r-project.org/package=dplyr" target="_blank"><code>dplyr</code></a>.</p>
<div>
<pre><code>dplyr_fn &lt;-<span> </span>function(tbl, ncol, <span>return_sql =</span> <span>FALSE</span>) {
  pipeline &lt;-<span> </span>tbl
  xvar &lt;-<span> </span>rlang::<span>sym</span>(<span>&quot;x&quot;</span>)
  for(i in <span>seq_len</span>(ncol)) {
    res_i &lt;-<span> </span>rlang::<span>sym</span>(<span>paste0</span>(<span>&quot;x_&quot;</span>, i))
    pipeline &lt;-<span> </span>pipeline %>%
<span> </span><span>mutate</span>(., !!res_i :<span>=</span><span> </span>!!xvar +<span> </span>i)
  }
  pipeline &lt;-<span> </span>pipeline %>%
<span> </span><span>filter</span>(., x ==<span> </span><span>3</span>)
  if(return_sql) {
    <span>return</span>(dbplyr::<span>remote_query</span>(pipeline))
  }
  <span># force execution</span>
  pipeline %>%<span> </span><span>collect</span>(.)
}

<span>cat</span>(<span>dplyr_fn</span>(tbl, <span>5</span>, <span>return_sql =</span> <span>TRUE</span>))</code></pre>
</div>
<pre><code>## SELECT *
## FROM (SELECT `x`, `x_1`, `x_2`, `x_3`, `x_4`, `x` + 5 AS `x_5`
## FROM (SELECT `x`, `x_1`, `x_2`, `x_3`, `x` + 4 AS `x_4`
## FROM (SELECT `x`, `x_1`, `x_2`, `x` + 3 AS `x_3`
## FROM (SELECT `x`, `x_1`, `x` + 2 AS `x_2`
## FROM (SELECT `x`, `x` + 1 AS `x_1`
## FROM `d`) `tyiyhhxjag`) `gshhunpiup`) `teowzjcshb`) `hdrfwlzycc`) `lsniejpwft`
## WHERE (`x` = 3.0)</code></pre>
<div>
<pre><code><span>dplyr_fn</span>(tbl, <span>5</span>)</code></pre>
</div>
<pre><code>## # A tibble: 1 x 6
##       x   x_1   x_2   x_3   x_4   x_5
##   </code></pre>