<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/a-modern-database-interface-for-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://blog.revolutionanalytics.com/2017/08/a-modern-database-interface-for-r.html"> Revolutions</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<p>At the useR! conference last month, Jim Hester <a rel="nofollow" href="https://channel9.msdn.com/Events/useR-international-R-User-conferences/useR-International-R-User-2017-Conference/odbc-A-modern-database-interface" target="_blank">gave a talk</a> about two packages that provide a modern database interface for R. Those packages are the <a rel="nofollow" href="http://db.rstudio.com/odbc/" target="_blank">odbc package</a> (developed by Jim and other members of the RStudio team), and the <a rel="nofollow" href="http://db.rstudio.com/dbi/" target="_blank">DBI package</a> (developed by Kirill Müller with support from the R Consortium).</p>
<p>To communicate with databases, a common protocol is <a rel="nofollow" href="https://docs.microsoft.com/en-us/sql/odbc/microsoft-open-database-connectivity-odbc" target="_blank">ODBC</a>. ODBC is an open, cross-platform standard for interfacing with databases, whatever the database software and variant of the SQL language it implements. R has long had the <a rel="nofollow" href="http://mran.microsoft.com/package/RODBC/" target="_blank">RODBC package</a> created by Brian Ripley, but the new odbc package provides an updated alternative.</p>
<p>The odbc package is a from-the-ground-up implementation of an ODBC interface for R that provides native support for additional data types (including dates, timestamps, raw binary, and 64-bit integers) and <a rel="nofollow" href="https://blogs.msdn.microsoft.com/sqlphp/2008/09/30/how-and-why-to-use-parameterized-queries/" target="_blank">parameterized queries</a>. The odbc package provides connections with any ODBC-compliant database, and has been <a rel="nofollow" href="http://mran.microsoft.com/web/packages/DBItest/vignettes/test.html" target="_blank">comprehensively tested</a> on SQL Server, PostgreSQL and MySQL. <a rel="nofollow" href="https://github.com/rstats-db/odbc#benchmarks" target="_blank">Benchmarks</a> show that it's also somewhat faster than RODBC: 3.2 times faster for reads, and 1.9 times faster for writes.</p>
<p>With the odbc package (and the DBI package, which provides low-level connectivity to the database), you create a database connection like this:</p>
<pre>con &lt;- <a rel="nofollow" href="https://www.rdocumentation.org/packages/DBI/versions/0.5-1/topics/dbConnect?tap_a=5644-dce66f&tap_s=10907-287229" target="_blank">dbConnect</a>( ... ) # provide server, username, password
</pre>
<p>With the connection established, you can the use functions like <a rel="nofollow" href="https://www.rdocumentation.org/packages/DBI/versions/0.5-1/topics/dbListTables?tap_a=5644-dce66f&tap_s=10907-287229" target="_blank">dbListTables</a> (get a list of tables in the database), <a rel="nofollow" href="https://www.rdocumentation.org/packages/DBI/versions/0.5-1/topics/dbReadTable?tap_a=5644-dce66f&tap_s=10907-287229" target="_blank">dbReadTable</a> (return the data from a table), and <a rel="nofollow" href="https://www.rdocumentation.org/packages/DBI/versions/0.5-1/topics/dbGetQuery?tap_a=5644-dce66f&tap_s=10907-287229" target="_blank">dbGetQuery</a> (execute a SQL query in the database, and return the results). That's pretty standard stuff. But the real power comes in being able to use high-level functions from the <a rel="nofollow" href="http://dplyr.tidyverse.org/" target="_blank">dplyr</a> package and have the data processing run <em>in the database</em>, instead of in the local R session.</p>
<p>When the <a rel="nofollow" href="http://mran.microsoft.com/package/dbplyr/" target="_blank">dbplyr</a> package is installed, you can create a &quot;<a rel="nofollow" href="http://tibble.tidyverse.org/" target="_blank">tibble</a>&quot; from a database connection, like this:</p>
<pre>salesdata &lt;- tbl(con, &quot;ussales&quot;)
</pre>
<p>It doesn't matter how big the <code>ussales</code> table is, because no data is brought into R until you perform an operation on the <code>salesdata</code> object. For example, you can use a dplyr pipe to get the first 20 records from the state of California, like this:</p>
<pre>tabledata %>% filter(state == &quot;CA&quot;) %>% head(20)
</pre>
<p>Behind the scenes, this creates a SQL query (which you can inspect with the <code>show_query</code> function, if you like) to move the computation from within R to the database. This process works for all the dplyr verbs: <code>filter</code> becomes <code>WHERE</code>, <code>left_join</code> becomes <code>LEFT JOIN</code>, <code>group_by</code> becomes <code>BY</code>, etc. In every case (including the example above) all the filtering happens in the database, and only 20 rows of data are actually returned to R. </p>
<p>You can see all of this in action in Jim's talk embedded below. (Jim's <a rel="nofollow" href="https://rawgit.com/jimhester/presentations/master/2017_07_06-UseR2017-odbc/2017_07_06-UseR2017-odbc.html#/" target="_blank">slides are also available here</a>.) In the demo starting at the 8:20 mark, Jim connects to SQL Server (here running in a VM on his laptop, in non-demo situations more likely to be on a remote machine). You'll then see him query tables on the database, and perform dplyr operations on the data in the process. You'll even get a lesson on the importance of sanitizing user inputs to prevent SQL injection, and a solution based on parameterized queries. </p>
<p><iframe width="450" allowfullscreen="" src="https://channel9.msdn.com/Events/useR-international-R-User-conferences/useR-International-R-User-2017-Conference/odbc-A-modern-database-interface/player" height="288"></iframe></p>
<p>The odbc package is built on top of the DBI package, which provides a low-level interface to databases and some of the functions used in Jim's demo above. The DBI package has been around for a while, but has recently undergone a major upgrade thanks to Kirill Müller and <a rel="nofollow" href="https://www.r-consortium.org/blog/2017/05/15/improving-dbi-a-retrospect" target="_blank">funding from the R Consortium</a>. If you're interested in the implementation of the DBI package and how it's evolving, check out Kirill's talk from useR! 2017: <a rel="nofollow" href="https://channel9.msdn.com/Events/useR-international-R-User-conferences/useR-International-R-User-2017-Conference/Improving-DBI" target="_blank">Improving DBI</a>.</p>
<p>The odbc and DBI packages are available on CRAN now. To use them with your database, you'll also need an ODBC driver installed on the local machine. Most likely you already have one (Windows includes an ODBC driver compatible with SQL Server, for example), but if not RStudio provides a useful guide to <a rel="nofollow" href="https://db.rstudio.com/" target="_blank">finding ODBC drivers</a> (which also come bundled with commercial RStudio products). </p>
<p>Channel9: <a rel="nofollow" href="https://channel9.msdn.com/Events/useR-international-R-User-conferences/useR-International-R-User-2017-Conference/odbc-A-modern-database-interface" target="_blank">odbc – A modern database interface</a></p>
</div>
	<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://blog.revolutionanalytics.com/2017/08/a-modern-database-interface-for-r.html"> Revolutions</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/XmH8wOIdPQs" height="1"></a></div>