<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/rquery-fast-data-manipulation-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/01/rquery-fast-data-manipulation-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>Win-Vector LLC <a rel="nofollow" href="http://www.win-vector.com/blog/2017/12/announcing-rquery/" target="_blank">recently announced</a> the <a rel="nofollow" href="https://winvector.github.io/rquery/" target="_blank"><code>rquery</code></a> <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> package, an operator based query generator. </p>
<p>In this note I want to share some exciting and favorable initial rquery benchmark timings.</p>
<p><span></span></p>
<p>Let’s take a look at rquery’s new <a rel="nofollow" href="https://winvector.github.io/rquery/articles/AdHocQueries.html#ad-hoc-mode" target="_blank">“ad hoc” mode</a> (made convenient through <a rel="nofollow" href="https://winvector.github.io/wrapr/index.html" target="_blank">wrapr</a>‘s <a rel="nofollow" href="http://www.win-vector.com/blog/2018/01/new-wrapr-r-pipeline-feature-wrapr_applicable/" target="_blank">new “wrapr_applicable” feature</a>). This is where rquery works on in-memory data.frame data by sending it to a database, processing on the database, and then pulling the data back. We concede this is a strange way to process data, and not rquery’s primary purpose (the primary purpose being generation of safe high performance SQL for big data engines such as Spark and PostgreSQL). However, our experiments show that it is in fact a competitive technique.</p>
<p>We’ve summarized the results of several experiments (experiment details <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/QTiming.md" target="_blank">here</a>) in the following graph (graphing code <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/plotexample.md" target="_blank">here</a>). The benchmark task was hand implementing logistic regression scoring. This is <a rel="nofollow" href="http://www.win-vector.com/blog/2017/08/lets-have-some-sympathy-for-the-part-time-r-user/" target="_blank">an example query we have been using for some time</a>.</p>
<p><img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/01/unnamed-chunk-1-1.png?w=660" alt="Unnamed chunk 1 1" title="unnamed-chunk-1-1.png" data-recalc-dims="1"></p>
<p>The graph above the distribution of repeated run times for:</p>
<ul>
<li><strong>data.table in memory</strong>: How long it takes <a rel="nofollow" href="https://cran.r-project.org/package=data.table" target="_blank">data.table</a> to complete the task starting from an in-memory data.frame. We will take data.table’s 256MS average runtime as the definition of “fast”, as data.table is commonly seen as the fastest system for in-memory data manipulation in R.</li>
<li><strong>rquery in memory</strong>: How long it takes rquery to complete the task starting from an in-memory data.frame and returning to an in-memory data.frame. The actual implementation is performed by moving data to a PostgreSQL database for processing and then collecting the result back. The observed 352MS average runtime is 38% slower than data.table. However, as we will see, that is actually a good result.</li>
<li><strong>dplyr tbl in memory</strong>: A standard dplyr in-memory pipeline working on a data.frame that is first converted into a tbl structure (to see if this is a significant speedup).</li>
<li><strong>dplyr in memory no grouped filter</strong>: A re-structured dplyr pipeline avoiding filtering inside a grouped context. </li>
<li><strong>dplyr from memory to db and back</strong>: Moved the data to a database, perform dplyr steps there, and then copy the data back. The runtime average is 602MS making it much faster than in-memory dplyr, but still 135% slower than data.table, 71% slower than rquery.
</ul>
<p>As is detailed in the <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/QTiming.md" target="_blank">experiment backing materials</a> the task is processing 40,000 records through either of the following non-trivial pipelines (the first one for dplyr, and the second one for rquery):</p>
<pre>
dplyr_pipeline &lt;- . %>%
  group_by(subjectID) %>%
    mutate(probability =
             exp(assessmentTotal * scale)/
             sum(exp(assessmentTotal * scale), na.rm = TRUE)) %>%
    arrange(probability, surveyCategory) %>%
    filter(row_number() == n()) %>%
    ungroup() %>%
    rename(diagnosis = surveyCategory) %>%
    select(subjectID, diagnosis, probability) %>%
    arrange(subjectID)
</pre>
<pre>
rquery_pipeline &lt;- . := {
    extend_nse(.,
             probability :=
               exp(assessmentTotal * scale)/
               sum(exp(assessmentTotal * scale)),
             count := count(1),
             partitionby = 'subjectID') %.>%
    extend_nse(.,
               rank := rank(),
               partitionby = 'subjectID',
               orderby = c('probability', 'surveyCategory'))  %.>%
    rename_columns(., 'diagnosis' := 'surveyCategory') %.>%
    select_rows_nse(., rank == count) %.>%
    select_columns(., c('subjectID', 
                        'diagnosis', 
                        'probability')) %.>%
    orderby(., 'subjectID') 
}
</pre>
<p>The primary purpose of rquery is pure in-database processing; in-memory processing is only a convenience. So let’s look at database only timings. In these tasks the data starts in the database (as is typical for large data projects) and is either consumed (by a row count called “count”) or saved as a database table (called “land”). In neither case does data move in or out of the database (skipping those overheads).</p>
<p><img border="0" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/01/unnamed-chunk-1-2.png?w=660" alt="Unnamed chunk 1 2" title="unnamed-chunk-1-2.png" data-recalc-dims="1"></p>
<p>And, as we demonstrated earlier, rquery’s query generator has <a rel="nofollow" href="http://www.win-vector.com/blog/2017/12/how-to-greatly-speed-up-your-spark-queries/" target="_blank">additional features</a> (not shown here) that can yield additional speedup in production environments (and also <a rel="nofollow" href="https://winvector.github.io/rquery/articles/AssigmentPartitioner.html" target="_blank">increase query safety</a> and <a rel="nofollow" href="https://winvector.github.io/rquery/reference/if_else_block.html" target="_blank">query power</a>).</p>
<p>I think results this good this early in the project are <em>very</em> promising. They are the product of some design decisions that I think will continue to pay off and some intentional scope restrictions (including that rquery only targets SQL engines, and initially only a few including Spark, PostgreSQL, and with some caveats SQLite). More work needs to be done to add all the SQL translation features needed for general production use, but we think we have demonstrated a good foundation to build on.</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="http://www.win-vector.com/blog/2018/01/rquery-fast-data-manipulation-in-r/"> R – Win-Vector Blog</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 rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/UXSNA4N4dDg" height="1"></a></div>