<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/rqdatatable-rquery-powered-by-data-table/"></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/06/rqdatatable-rquery-powered-by-data-table/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><a rel="nofollow" href="https://github.com/WinVector/rquery" target="_blank"><code>rquery</code></a> is an <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> package for specifying data transforms using piped <a rel="nofollow" href="https://en.wikipedia.org/wiki/Relational_algebra" target="_blank">Codd-style operators</a>. It has already shown great performance on <a rel="nofollow" href="https://www.postgresql.org/" target="_blank"><code>PostgreSQL</code></a> and <a rel="nofollow" href="https://spark.apache.org/" target="_blank"><code>Apache Spark</code></a>. <a rel="nofollow" href="https://github.com/WinVector/rqdatatable" target="_blank"><code>rqdatatable</code></a> is a new package that supplies a screaming fast implementation of the <code>rquery</code> system in-memory using the <a rel="nofollow" href="https://github.com/Rdatatable/data.table/wiki" target="_blank"><code>data.table</code></a> package.</p>
<p>
<p><code>rquery</code> is already <em>one of</em> the <em>fastest</em> and <em>most teachable</em> (due to deliberate conformity to Codd’s influential work) tools to wrangle data on databases and big data systems. And now <code>rquery</code> is also <em>one of</em> the fastest methods to wrangle data in-memory in <code>R</code> (thanks to <code>data.table</code>, via a thin adaption supplied by <code>rqdatatable</code>).</p>
<p>
<p><span></span></p>
<p>Teaching <code>rquery</code> and fully benchmarking it is a big task, so in this note we will limit ourselves to a single example and benchmark. Our intent is to use this example to promote <code>rquery</code> and <code>rqdatatable</code>, but frankly the biggest result of the benchmarking is how far out of the pack <code>data.table</code> itself stands at small through large problem sizes. This is already known, but it is a much larger difference and at more scales than the typical non-<code>data.table</code> user may be aware of.</p>
<p>
<p>The <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> package development candidate <a rel="nofollow" href="https://github.com/WinVector/rquery" target="_blank"><code>rquery</code> 0.5.0</a> incorporates a number of fixes and improvements. One interesting new feature is the <code>DBI</code> package is now suggested or optional, instead of required. This means <code>rquery</code> is ready to talk to non-<code>DBI</code> big data systems such as <a rel="nofollow" href="https://spark.apache.org/docs/latest/sparkr.html" target="_blank"><code>SparkR</code></a> (example <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/SparkR.md" target="_blank">here</a>) and it let us recruit a very exciting new <code>rquery</code> service provider: <a rel="nofollow" href="https://github.com/Rdatatable/data.table/wiki" target="_blank"><code>data.table</code></a>!</p>
<p>
<p><code>data.table</code> is, by far, the fastest way to wrangle data at scale in-memory in <code>R</code>. Our experience is that it starts to outperform base <code>R</code> internals and all other packages at moderate data sizes such as mere tens or hundreds of rows. Of course <code>data.table</code> is most famous for its performance in the millions of rows and gigabytes of data range.</p>
<p>
<p>However, because of the different coding styles there are not as many comparative benchmarks as one would like. So performance is often discussed as anecdotes or rumors. As a small step we are going to supply a single benchmark based on our “score a logistic regression by hand” problem from <a rel="nofollow" href="http://www.win-vector.com/blog/2017/08/lets-have-some-sympathy-for-the-part-time-r-user/" target="_blank">“Let’s Have Some Sympathy For The Part-time R User”</a> (what each coding solution looks like can be found <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/data_table.md" target="_blank">here</a>).</p>
<p>
<p>In this note we compare idiomatic solutions to the example problem using: <code>rquery</code>, <a rel="nofollow" href="https://github.com/Rdatatable/data.table/wiki" target="_blank"><code>data.table</code></a>, base <code>R</code> (using <code>stats::aggregate()</code>), and <a rel="nofollow" href="https://cran.r-project.org/package=dplyr" target="_blank"><code>dplyr</code></a>. <code>dplyr</code> is included due to its relevance and popularity. Full details of the benchmarking can be found <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/data_table.md" target="_blank">here</a> and full results <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/data_table_replot.md" target="_blank">here</a>. One can always do more benchmarking and control for more in experiments. One learns more from a diversity of benchmarks than from critiquing any one benchmark, so we will work this example briefly and provide links to a few others benchmarks. Our measurements confirm the common (correct) observation and conclusion: that <code>data.table</code> is very fast. Our primary new observation is that the overhead from the new <code>rqdatatable</code> adapter is not too large and <code>rqdatatable</code> is issuing reasonable <code>data.table</code> commands.</p>
<p>
<p>Both the <code>rquery</code> and <code>dplyr</code> solutions can be run in multiple modalities: allowing the exact same code to be used in memory or on a remote big data system (a great feature, critical for low-latency rehearsal and debugging). These two systems can be run as follows.</p>
<p>
<ul>
<li>
<code>rquery</code> is a system for describing operator trees. It deliberately does not implement the data operators, but depends on external systems for implementations. Previously any sufficiently standard <code>SQL92</code> database that was <code>R</code> <code>DBI</code> compliant could serve as a back-end or implementation. This already includes the industrial scale database <code>PostgreSQL</code> and the big data system <code>Apache Spark</code> (via the <a rel="nofollow" href="https://cran.r-project.org/package=sparklyr" target="_blank"><code>SparklyR</code></a> package). The 0.5.0 development version of <code>rquery</code> relaxes the <code>DBI</code> requirement (allowing <code>rquery</code> to be used directly with <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/SparkR.md" target="_blank"><code>SparkR</code></a>) and admits the possibility of non-<code>SQL</code> based implementations. We have a new <code>data.table</code> based implementation in development as the <a rel="nofollow" href="https://github.com/WinVector/rqdatatable" target="_blank"><code>rqdatatable</code></a> package.
</li>
<li>
<code>dplyr</code> also allows multiple implementations (in-memory, <code>DBI</code> <code>SQL</code>, or <code>data.table</code>). We tested all three, and the <code>dplyr</code> pipeline worked identically in-memory and with <code>PostgreSQL</code>. However, the <code>dtplyr</code> pipeline did not generate valid <code>data.table</code> commands, due to an issue with window functions or ranking, so we were not able to time it using <code>data.table</code>.
</li>
</ul>
<p>
<p>We are thus set up to compare to following solutions to the logistic scoring problem:</p>
<p>
<ul>
<li>A direct <code>data.table</code> solution running in memory.</li>
<li>The base <code>R</code> <code>stats::aggregate()</code> solution working on in-memory data.frames.</li>
<li>The <code>rquery</code> solution using the <code>data.table</code> service provider <code>rqdatatable</code> to run in memory.</li>
<li>The <code>rquery</code> solution sending data to <code>PostgreSQL</code>, performing the work in database, and then pulling results back to memory.</li>
<li>The <code>dplyr</code> solution working directly on in-memory <code>data.frame</code>s.</li>
<li>The <code>dplyr</code> solution working directly on in-memory <code>tibble:tbl</code>s (we are not counting any time for conversion).</li>
<li>The <code>dplyr</code> solution sending data to <code>PostgreSQL</code>, performing the work in database, and then pulling results back to memory.</li>
</ul>
<p>
<p>Running a 1,000,000 row by 13 column example can be summarized with the following graph.</p>
<p>
<p><img border="0" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/06/presenttimings-18.png?resize=660%2C471" alt="Presenttimings 18" width="450" title="presenttimings-18.png" data-recalc-dims="1"></p>
<p>
<p>The vertical dashed line is the median time that repeated runs of the base <code>R</code> <code>stats::aggregate()</code> solution took. We can consider results to the left of it as “fast” and results to the right of it as “slow.” Or in physical terms: <code>data.table</code> and <code>rquery</code> using <code>data.table</code> each take about 1.5 seconds on average. Whereas <code>dplyr</code> takes over 20 seconds on average. These two durations represent vastly different user experiences when attempting interactive analyses.</p>
<p>
<p>We have run some more tests to try to see how this is a function of problem scale (varying the number of rows of the data). Due to the large range (2 to 10,000,000 rows) we are using log scales, but they unfortunately are just not as readable as the linear scales.</p>
<p>
<p><img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/06/presenttimings-22.png?resize=660%2C471" alt="Presenttimings 22" width="450" title="presenttimings-22.png" data-recalc-dims="1"><br>
What we can read off this graph includes:</p>
<p>
<ul>
<li><code>data.table</code> is always the fastest system (or at worst indistinguishable from the fastest system) for this example,<br>
at the scales of problems tested, and for this configuration and hardware.</li>
<li>The <code>data.table</code> backed version of <code>rquery</code> becomes comparable to native <code>data.table</code> itself at around 100,000 rows. This is evidence the translation overhead is not too bad for this example and that the sequence<br>
of <code>data.table</code> commands issued by <code>rqdatatable</code> are fairly good practice <code>data.table</code>.</li>
<li>The database backed version of <code>rquery</code> starts to outperform <code>dplyr</code> at around 10,000 rows. Note: all database measurements include the overhead of moving the data to the database and then moving the results back to <code>R</code>. This is slower than how one would normally use a database in production: with data starting and ending on the database and no data motion between <code>R</code> and the database.</li>
<li><code>dplyr</code> appears to be slower than the base <code>R</code> <code>stats::aggregate()</code> solution at all measured scales (it is always above the shaded region).</li>
<li>It is hard to read, but changes in heights are ratios of runtimes. For example the <code>data.table</code> based solutions are routinely over 10 times faster that the <code>dplyr</code> solutions once we get to 100,000 rows or more. This is an object size of only about 10 megabytes and is well below usual “use <code>data.table</code> once you are in the gigabytes range” advice.</li>
</ul>
<p>
<p>Of course benchmarks depend on the example problems, versions, and machines- so results will vary. That being said, large differences often have a good chance of being preserved across variations of tests (and we share another grouped example <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/pipeline_example.md" target="_blank">here</a>, and a join example <a rel="nofollow" href="https://github.com/WinVector/rquery/blob/master/extras/join_example.md" target="_blank">here</a>; for the join example <code>dplyr</code> is faster at smaller problem sizes- so results do depend on task and scale).</p>
<p>
<p>We are hoping to submit the <a rel="nofollow" href="https://github.com/WinVector/rquery" target="_blank"><code>rquery</code></a> update to <code>CRAN</code> in August and then submit <a rel="nofollow" href="https://github.com/WinVector/rqdatatable" target="_blank"><code>rqdatatable</code></a> as a new <code>CRAN</code> package soon after. Until then you can try both packages by a simple application of:</p>
<p>
<pre>devtools::install_github(&quot;WinVector/rqdatatable&quot;)</pre>
<p>
<p>These are new packages, but we think they can <em>already</em> save substantial development time, documentation time, debugging time, and machine investment in <a rel="nofollow" href="http://www.win-vector.com/blog/tag/r-and-big-data/" target="_blank">“R and big data”</a> projects. Our group (<a rel="nofollow" href="http://www.win-vector.com/" target="_blank">Win-Vector LLC</a>) is offering private training in <code>rquery</code> to get teams up to speed quickly.</p>
<p>
<p>Note: <code>rqdatatable</code> is an implementation of <code>rquery</code> supplied by <code>data.table</code>, not a <code>data.table</code> scripting tool (as <code>rqdatatable</code> does not support important <code>data.table</code> features not found in <code>rquery</code>, such as rolling joins).</p>
<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/06/rqdatatable-rquery-powered-by-data-table/"> 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/GJ4QSZDVyiI" height="1"></a></div>