<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/database-queries-with-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rviews.rstudio.com/2017/10/18/database-queries-with-r/"> R Views</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>There are many ways to query data with R. This post shows you three of the most common ways:</p>
<ol>
<li>Using <code>DBI</code></li>
<li>Using <code>dplyr</code> syntax</li>
<li>Using R Notebooks</li>
</ol>
<div>
<h3>Background</h3>
<p>Several recent package improvements make it easier for you to use databases with R. The query examples below demonstrate some of the capabilities of these R packages.</p>
<ul>
<li><a rel="nofollow" href="https://rstats-db.github.io/DBI//index.html" target="_blank">DBI</a>. The <code>DBI</code> specification has gone through many <a rel="nofollow" href="https://www.r-consortium.org/blog/2017/05/15/improving-dbi-a-retrospect" target="_blank">recent improvements</a>. When working with databases, you should always use packages that are <code>DBI</code>-compliant.</li>
<li><a rel="nofollow" href="http://dplyr.tidyverse.org/" target="_blank">dplyr</a> &amp; <a rel="nofollow" href="http://dbplyr.tidyverse.org/" target="_blank">dbplyr</a>. The <code>dplyr</code> package now has a generalized SQL backend for talking to databases, and the new <code>dbplyr</code> package translates R code into database-specific variants. As of this writing, SQL variants are supported for the following databases: Oracle, Microsoft SQL Server, PostgreSQL, Amazon Redshift, Apache Hive, and Apache Impala. More will follow over time.</li>
<li><a rel="nofollow" href="https://github.com/rstats-db/odbc" target="_blank">odbc</a>. The <code>odbc</code> R package provides a standard way for you to connect to any database as long as you have an ODBC driver installed. The <code>odbc</code> R package is <code>DBI</code>-compliant, and is recommended for ODBC connections.</li>
</ul>
<p>RStudio also made recent improvements to its products so they work better with databases.</p>
<ul>
<li><a rel="nofollow" href="https://blog.rstudio.com/2017/10/09/rstudio-v1.1-released/" target="_blank">RStudio IDE (v1.1)</a>. With the latest version of the RStudio IDE, you can connect to, explore, and view data in a variety of databases. The IDE has a wizard for setting up new connections, and a tab for exploring established connections. These new features are extensible and will work with any R package that has a <a rel="nofollow" href="https://rstudio.github.io/rstudio-extensions/connections-contract.html" target="_blank">connections contract</a>.</li>
<li><a rel="nofollow" href="https://www.rstudio.com/products/drivers/" target="_blank">RStudio Professional Drivers</a>. If you are using RStudio professional products, you can download RStudio Professional Drivers for no additional cost. The examples below use the Oracle ODBC driver. If you are using open-source tools, you can bring your own driver or use community packages – many open-source drivers and community packages exist for connecting to a variety of databases.</li>
</ul>
<p>Using databases with R is a broad subject and there is more work to be done. An earlier blog post discussed <a rel="nofollow" href="https://blog.rstudio.com/2017/06/27/dbplyr-1-1-0/" target="_blank">our vision</a>. Part of that vision was to create a website where you can find everything about databases and R in one place. To learn more, visit our site at <a rel="nofollow" href="http://db.rstudio.com/best-practices/drivers" target="_blank">db.rstudio.com</a>.</p>
</div>
<div>
<h3>Example: Query bank data in an Oracle database</h3>
<p>In this example, we will query bank data in an Oracle database. We connect to the database by using the <code>DBI</code> and <code>odbc</code> packages. This specific connection requires a database driver and a data source name (DSN) that have both been configured by the system administrator. Your connection might use another method.</p>
<pre><code>library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)
con &lt;- dbConnect(odbc::odbc(), &quot;Oracle DB&quot;)</code></pre>
</div>
<div>
<h3>1. Query using <code>DBI</code></h3>
<p>You can query your data with <code>DBI</code> by using the <code>dbGetQuery()</code> function. Simply paste your SQL code into the R function as a quoted string. This method is sometimes referred to as <em>pass through SQL code</em>, and is probably the simplest way to query your data. Care should be used to escape your quotes as needed. For example, <code>'yes'</code> is written as <code>\'yes\'</code>.</p>
<pre><code>dbGetQuery(con,'
  select &quot;month_idx&quot;, &quot;year&quot;, &quot;month&quot;,
  sum(case when &quot;term_deposit&quot; = \'yes\' then 1.0 else 0.0 end) as subscribe,
  count(*) as total
  from &quot;bank&quot;
  group by &quot;month_idx&quot;, &quot;year&quot;, &quot;month&quot;
')</code></pre>
</div>
<div>
<h3>2. Query using dplyr syntax</h3>
<p>You can write your code in <code>dplyr</code> syntax, and <code>dplyr</code> will translate your code into SQL. There are several benefits to writing queries in <code>dplyr</code> syntax: you can keep the same consistent language both for R objects and database tables, no knowledge of SQL or the specific SQL variant is required, and you can take advantage of the fact that <code>dplyr</code> uses <a rel="nofollow" href="http://dbplyr.tidyverse.org/articles/dbplyr.html" target="_blank">lazy evaluation</a>. <code>dplyr</code> syntax is easy to read, but you can always inspect the SQL translation with the <code>show_query()</code> function.</p>
<pre><code>q1 &lt;- tbl(con, &quot;bank&quot;) %>%
  group_by(month_idx, year, month) %>%
  summarise(
    subscribe = sum(ifelse(term_deposit == &quot;yes&quot;, 1, 0)),
    total = n())
show_query(q1)</code></pre>
<p></p>
<pre><code></code></pre></div>