<div>
<p><a target="_blank" href="https://www.monetdb.org/blog/monetdblite-r">MonetDBLite</a> is a SQL database that runs inside the <a target="_blank" href="https://www.r-project.org/">R environment for statistical computing</a> and does not require the installation of any external software. MonetDBLite is based on free and open-source <a target="_blank" href="https://www.monetdb.org/Home">MonetDB</a>, a product of the <a target="_blank" href="http://www.cwi.nl/">Centrum Wiskunde &amp; Informatica</a>.</p>
<p>MonetDBLite is similar in functionality to <a target="_blank" href="http://cran.r-project.org/package=RSQLite">RSQLite</a>, but typically completes queries blazingly fast due to its <em>columnar</em> storage architecture and bulk query processing model. Since both of these embedded SQL options rely on the the R <a target="_blank" href="http://cran.r-project.org/package=DBI">DBI</a> interface, the conversion of legacy <code>RSQLite</code> project syntax over to <code>MonetDBLite</code> code should be a cinch.</p>
<p>MonetDBLite works seamlessly with <a target="_blank" href="https://github.com/hadley/dplyr">the dplyr grammar of data manipulation</a>. For a detailed tutorial of how to work with database-backed dplyr commands, see <a target="_blank" href="https://cran.r-project.org/web/packages/dplyr/vignettes/databases.html">the dplyr databases vignette</a>. To reproduce this vignette using MonetDBLite rather than RSQLite, simply replace the functions ending with <code>*_sqlite</code> with the suffix <code>*_monetdb</code> instead.</p>
<h2>Installation</h2>
<ul>
<li>
<p>the latest released version from <a target="_blank" href="http://cran.r-project.org/package=MonetDBLite">CRAN</a> with</p>
<pre><code><span>install.packages</span>(<span>&quot;MonetDBLite&quot;</span>)</code></pre>
</li>
<li>
<p>the latest development version from github with</p>
<pre><code>devtools::<span>install_github</span>(<span>&quot;hannesmuehleisen/MonetDBLite&quot;</span>)</code></pre>
</li>
</ul>
<p>If you encounter a bug, please file a minimal reproducible example on <a target="_blank" href="https://github.com/hannesmuehleisen/MonetDBLite/issues">github</a>. For questions and other discussion, please use <a target="_blank" href="http://stackoverflow.com/questions/tagged/monetdblite">stack overflow</a> with the tag <code>monetdblite</code>. The development version of MonetDBLite endures <a target="_blank" href="http://homepages.cwi.nl/~hannes/sisyphus/">sisyphean perpetual testing</a> on both unix and windows machines.</p>
<h2>Speed Comparisons</h2>
<p>MonetDBLite outperforms all other SQL databases currently accessible by the R language and ranks competitively among other <a target="_blank" href="https://cran.r-project.org/web/views/HighPerformanceComputing.html">High Performace Computing</a> options available to R users. For more detail, see <a target="_blank" href="https://github.com/szilard/benchm-databases">Szilard Pafka’s bennchmarks</a>.</p>
<p align="center"><img width="500" src="https://raw.githubusercontent.com/hannesmuehleisen/MonetDBLite/master/speed_comparisons.png"></p>
<h2>Painless Startup</h2>
<p>If you want to store a database permanently (or to reconnect to a previously-initiated one), set the <code>dbdir</code> to some folder path on your local machine. A new database that you would like to store permanently should be directed to an empty folder:</p>
<pre><code><span>library</span>(DBI)<br>dbdir &lt;-<span> &quot;C:/path/to/database_directory&quot;</span><br>con &lt;-<span> </span><span>dbConnect</span>(MonetDBLite::<span>MonetDBLite</span>(), dbdir)</code></pre>
<p>To create a temporary server, create a DBI connection as follows:</p>
<pre><code><span>library</span>(DBI)<br>con &lt;-<span> </span><span>dbConnect</span>(MonetDBLite::<span>MonetDBLite</span>())</code></pre>
<p>Note that the above temporary server command is equivalent to initiating the server in the <code>tempdir()</code> of your R session:</p>
<pre><code><span>library</span>(DBI)<br>dbdir &lt;-<span> </span><span>tempdir</span>()<br>con &lt;-<span> </span><span>dbConnect</span>(MonetDBLite::<span>MonetDBLite</span>(), dbdir)</code></pre>
<p>Note that MonetDB may hiccup when using network drives, use servers stored on the same machine as the R session.</p>
<h2>Versatile Data Importation</h2>
<p>To efficiently copy a <code>data.frame</code> object into a table within the MonetDBLite database, use <a target="_blank" href="http://www.inside-r.org/packages/cran/DBI/docs/dbWriteTable"><code>dbWriteTable</code></a>:</p>
<pre><code><span># directly copy a data.frame object to a table within the database</span><br><span>dbWriteTable</span>(con, <span>&quot;mtcars&quot;</span>, mtcars)</code></pre>
<p>To load a CSV file into a table within the database, provide the local file path of a <code>.csv</code> file to <code>dbWriteTable</code>:</p>
<pre><code><span># construct an example CSV file on the local disk</span><br>csvfile &lt;-<span> </span><span>tempfile</span>()<br><span>write.csv</span>(mtcars, csvfile, <span>row.names =</span> <span>FALSE</span>)<br><br><span># directly copy a csv file to a table within the database</span><br><span>dbWriteTable</span>(con, <span>&quot;mtcars2&quot;</span>, csvfile)<br><br><span># append the same table to the bottom of the previous table</span><br><span>dbWriteTable</span>(con, <span>&quot;mtcars2&quot;</span>, csvfile, <span>append=</span><span>TRUE</span>)<br><br><span># overwrite the table with a new table</span><br><span>dbWriteTable</span>(con, <span>&quot;mtcars2&quot;</span>, csvfile, <span>overwrite=</span><span>TRUE</span>)</code></pre>
<p>The SQL interface of MonetDBLite can also be used to manually create a table and import data:</p>
<pre><code><span># construct an example CSV file on the local disk</span><br>csvfile &lt;-<span> </span><span>tempfile</span>()<br><span>write.csv</span>(mtcars, csvfile, <span>row.names =</span> <span>FALSE</span>)<br><br><span># start a SQL transaction</span><br><span>dbBegin</span>(con)<br><br><span># construct an empty table within the database, using a manually-defined structure</span><br><span>dbSendQuery</span>(con, <span>&quot;CREATE TABLE mtcars3 (mpg DOUBLE PRECISION, cyl INTEGER, disp DOUBLE PRECISION, hp INTEGER, drat DOUBLE PRECISION, wt DOUBLE PRECISION, qsec DOUBLE PRECISION, vs INTEGER, am INTEGER, gear INTEGER, carb INTEGER)&quot;</span>)<br><br><span># copy the contents of a CSV file into the database, using the MonetDB COPY INTO command</span><br><span>dbSendQuery</span>(con, <span>paste0</span>(<span>&quot;COPY OFFSET 2 INTO mtcars3 FROM '&quot;</span>, csvfile, <span>&quot;' USING DELIMITERS ',','</span><span>\n</span><span>','</span><span>\&quot;</span><span>' NULL as ''&quot;</span>))<br><br><span># finalize the SQL transaction</span><br><span>dbCommit</span>(con)</code></pre>
<p>Note how we wrap the two commands in a transaction using <code>dbBegin</code> and <code>dbCommit</code>. This creates all-or-nothing semantics. See the MonetDB documentation for details on <a target="_blank" href="https://www.monetdb.org/Documentation/Manuals/SQLreference/Tables">how to create a table</a> and <a target="_blank" href="https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto">how to perform bulk input</a>.</p>
<h2>Reading and Writing (Queries and Updates)</h2>
<p>This section reviews how to pass SQL queries to an embedded server session and then pull those results into R. If you are interested in learning SQL syntax, perhaps review the <a target="_blank" href="http://www.w3schools.com/sql/">w3schools SQL tutorial</a> or the <a target="_blank" href="https://www.monetdb.org/Documentation/SQLreference">MonetDB SQL Reference Manual</a>.</p>
<p>The <code>dbGetQuery</code> function sends a <code>SELECT</code> statement to the server, then returns the result as a <code>data.frame</code>:</p>
<pre><code><span># calculate the average miles per gallon, grouped by number of cylinders</span><br><span>dbGetQuery</span>(con, <span>&quot;SELECT cyl, AVG(mpg) FROM mtcars GROUP BY cyl&quot;</span> )<br><br><span># calculate the number of records in the _mtcars_ table</span><br><span>dbGetQuery</span>(con, <span>&quot;SELECT COUNT(*) FROM mtcars&quot;</span> )</code></pre>
<p>The <code>dbSendQuery</code> function can open a connection to some read-only query. Once initiated, the <code>res</code> object below can then be accessed repeatedly with a <code>fetch</code> command:</p>
<pre><code>res &lt;-<span> </span><span>dbSendQuery</span>(con, <span>&quot;SELECT wt, gear FROM mtcars&quot;</span>)<br>first_sixteen_records &lt;-<span> </span><span>fetch</span>(res, <span>n=</span><span>16</span>)<br><span>dbHasCompleted</span>(res)<br>second_sixteen_records &lt;-<span> </span><span>fetch</span>(res, <span>n=</span><span>16</span>)<br><span>dbHasCompleted</span>(res)<br><span>dbClearResult</span>(res)</code></pre>
<p>The <code>dbSendQuery</code> function should also be used to make edits to tables within the database:</p>
<pre><code><span># add a new column of kilometers per liter</span><br><span>dbSendQuery</span>(con, <span>&quot;ALTER TABLE mtcars ADD COLUMN kpl DOUBLE PRECISION&quot;</span> )<br><br><span># populate that new column with kilometers per liter</span><br><span>dbSendQuery</span>(con, <span>&quot;UPDATE mtcars SET kpl = mpg * 0.425144&quot;</span> )</code></pre>
<h2>Glamorous Data Export</h2>
<p>The contents of an entire table within the database can be transferred to an R <code>data.frame</code> object with <a target="_blank" href="http://www.inside-r.org/packages/cran/DBI/docs/dbReadTable"><code>dbReadTable</code></a>. Since MonetDBLite is most useful for the storage and analysis of large datasets, there might be limited utility to copying an entire table into working RAM in R. The <code>dbReadTable</code> function and a SQL <code>SELECT * FROM tablename</code> command are equivalent:</p>
<pre><code><span># directly copy a table within the database to an R data.frame object</span><br>x &lt;-<span> </span><span>dbReadTable</span>(con, <span>&quot;mtcars&quot;</span>)<br><br><span># directly copy a table within the database to an R data.frame object</span><br>y &lt;-<span> </span><span>dbGetQuery</span>(con, <span>&quot;SELECT * FROM mtcars&quot;</span> )</code></pre>
<h2>Special database informational functions</h2>
<p>Certain administrative commands can be sent using either <code>dbSendQuery</code> or with a custom DBI function:</p>
<pre><code><span># remove the table `mtcars2` from the database</span><br><span>dbSendQuery</span>(con, <span>&quot;DROP TABLE mtcars2&quot;</span> )<br><br><span># remove the table `mtcars3` from the database</span><br><span>dbRemoveTable</span>(con, <span>&quot;mtcars3&quot;</span> )</code></pre>
<p>Other administrative commands can be sent using <code>dbGetQuery</code> or with a custom DBI function:</p>
<pre><code><span># list the column names of the mtcars table within the database</span><br><span>names</span>(<span>dbGetQuery</span>(con, <span>&quot;SELECT * FROM mtcars LIMIT 1&quot;</span> ))<br><br><span># list the column names of the mtcars table within the database</span><br><span>dbListFields</span>(con, <span>&quot;mtcars&quot;</span> )</code></pre>
<p>Still other administrative commands are much easier to simply use the custom DBI function:</p>
<pre><code><span># print the names of all tables within the current database</span><br><span>dbListTables</span>(con)</code></pre>
<h2>Shutdown</h2>
<p>MonetDBLite allows multiple concurrent <em>connections</em> to a single database, but does not allow more than one concurrent embedded server session (actively-running database). This is not an issue for most users since a single database can store thousands of individual tables. To switch between databases, however, the first server must be shut down before the second can be opened. To shutdown a server, include the <code>shutdown=TRUE</code> parameter:</p>
<pre><code><span>dbDisconnect</span>(con, <span>shutdown=</span><span>TRUE</span>)</code></pre>
<p>MonetDBLite does not allow multiple R sessions to connect to a single database concurrently. As soon as a single R session loads an embedded server, that server is locked down and inaccessible to other R consoles.</p>
<p></p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="http://www.r-bloggers.com/monetdblite-because-fast/"></div></div><div><a data-url="http://www.r-bloggers.com/monetdblite-because-fast/" data-counturl="http://www.r-bloggers.com/monetdblite-because-fast/" data-count="vertical" data-text="MonetDBLite because fast" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<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.asdfree.com/2016/06/monetdblite-because-fast.html"> asdfree</a></strong>.</div>
<hr>
<a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="http://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="http://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="http://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="http://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="http://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="http://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="http://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="http://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="http://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="http://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="http://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="http://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="http://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="http://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="http://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="http://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/muTAzzmAUpE"></a></div></div>