<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/databases-using-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rviews.rstudio.com/2017/05/17/databases-using-r/"> R Views</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<h1>Current State</h1>
<p>Using databases is unavoidable for those who analyze data as part of their jobs. As R developers, our first instinct may be to approach databases the same way we do regular files. We may attempt to read the data either all at once or as few times as possible. The aim is to reduce the number of times we go back to the data ‘well’, so our queries extract as much data as possible. After that, we spend cycles analyzing the data in memory. Here is what this model looks like:</p>
<p><img src="https://i0.wp.com/rviews.rstudio.com/post/2017-05-11-databases-using-r_files/today.png?resize=400%2C400&ssl=1" data-recalc-dims="1"></p>
<p>Because the volume of data is significant with this approach, we usually attempt to come up with strategies to minimize the resources and time it takes to analyze the data. We may try to retrieve all rows of a few columns, or a few rows of several of columns. Another tactic is to save the query results into individual files for later analysis.</p>
<p>An improvement to the current approach would be to use the database’s SQL Engine to perform as much of the data exploration as possible. An enterprise-grade SQL server will have more power, and will be better tuned, to execute transformations of large amounts of data. Our goal would then be to bring into R a more targeted data set that will be used for visualization and modeling.</p>
<p>This improvement comes at a cost: we will need to know how to write SQL queries, and will have to switch between both languages. We may also end up using an external querying tool that is able to provide a list of tables and inline SQL code helpers. Of course, this involves switching between tools. On a personal note, I used to switch from R to Microsoft SQL Management Studio. After I that, I would bring the finalized query back into my code in R.</p>
</div>
<div>
<h1>A better way</h1>
<p><img src="https://i0.wp.com/rviews.rstudio.com/post/2017-05-11-databases-using-r_files/better.png?resize=400%2C400&ssl=1" data-recalc-dims="1"></p>
<p>The <code>dplyr</code> package simplifies data transformation. It provides a consistent set of functions, called verbs, that can be used in succession and interchangeably to gain understanding of the data iteratively. The first time I re-wrote R code using <code>dplyr</code>, the new script was at least half as long and much easier to understand.</p>
<p>Another nice thing about <code>dplyr</code> is that it can interact with databases directly. It accomplishes this by translating the <code>dplyr</code> verbs into SQL queries. This incredibly convenient feature allows us to ‘speak’ directly with the database from R, thus resolving the issues brought up in the previous section:</p>
<ul>
<li>
<p><strong>Run data exploration over all of the data</strong> – Instead of coming up with a plan to decide what data to import, we can focus on analyzing the data inside the database, which in turn should yield faster insights.</p>
</li>
<li>
<p><strong>Use the SQL Engine to run the data transformations</strong> – We are, in effect, pushing the computation to the database because <code>dplyr</code> is sending SQL queries to the database.</p>
</li>
<li>
<p><strong>Collect a targeted dataset</strong> – After become familiar with the data and choosing the data points that will either be shared or modeled, a final query can then be used to bring back only that data into memory in R.</p>
</li>
<li>
<p><strong>All your code is in R!</strong> – Because we are using <code>dplyr</code> to communicate with the database, there is no need to change language, or tools, to perform the data exploration.</p>
</li>
</ul>
</div>
<div>
<h1>Example</h1>
<p>There are three things that we will need to get started:</p>
<ul>
<li>
<p>A database we can access</p>
</li>
<li>
<p>A database driver installed in either our workstation or RStudio Server</p>
</li>
<li>
<p>All of the required packages installed in R</p>
</li>
</ul>
<p>In this section, we will demonstrate how to access a Microsoft SQL Server database from a workstation that is running on Microsoft Windows.</p>
<div>
<h2>Database Driver</h2>
<p>A <strong>database driver</strong> is a program that allows the workstation and the database to communicate. In Microsoft Windows, the drivers that connect to MS SQL databases are installed by default. We need the <strong>name</strong> of the driver that will be used inside our code in R. The easiest way to do this is to open the ODBC Data Source Administrator. To find it in your, system please refer to this article: <a target="_blank" rel="nofollow" href="https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/check-the-odbc-sql-server-driver-version-windows">Check the ODBC SQL Server Driver Version (Windows)</a> . Once the administrator program is open, click on the <strong>Drivers</strong> tab. In my laptop, these are the drivers available. I will use <strong>SQL Server</strong> for the Driver argument in my connection in R.</p>
<p><img src="https://i0.wp.com/rviews.rstudio.com/post/2017-05-11-databases-using-r_files/odbc.png?resize=400%2C400&ssl=1" data-recalc-dims="1"></p>
</div>
<div>
<h2>R packages</h2>
<p>Besides <code>dplyr</code>, the following packages are required:</p>
<ul>
<li><code>odbc</code> – This is the interface between the database driver and R</li>
<li><code>DBI</code> – Standardizes the functions related to database operations</li>
<li><code>dbplyr</code> – Enables <code>dplyr</code> to interact with databases. It also contains the vendor-specific SQL translations.</li>
</ul>
<p>The database accessibility feature is still being developed, so we will use the development versions of <code>dbplyr</code> and <code>dplyr</code>.</p>
<pre><code>devtools::install_github(&quot;tidyverse/dplyr&quot;)
devtools::install_github(&quot;tidyverse/dbplyr&quot;)
install.packages(&quot;rstats-db/odbc&quot;)
install.packages(&quot;DBI&quot;)</code></pre>
</div>
<div>
<h2>Connect to the database</h2>
<p>We will use the <code>dbConnect()</code> function from the <code>DBI</code> package to connect to the database. The value for the <code>Driver</code> argument is the name we determined in the <em>Database Driver</em> section above.</p>
<pre><code>library(DBI)

con &lt;- dbConnect(odbc::odbc(),
                   Driver    = &quot;SQL Server&quot;, 
                   Server    = &quot;localhost&quot;,
                   Database  = &quot;airontime&quot;,
                   UID       = [My User ID],
                   PWD       = [My Password],
                   Port      = 1433)</code></pre>
<p>A very useful function in <code>DBI</code> is <code>dbListTables()</code>, which retrieves the names of available tables.</p>
<pre><code>dbListTables(con)</code></pre>
<p><code>[1] &quot;airlines&quot; &quot;airport&quot;  &quot;airports&quot; &quot;faithful&quot; &quot;flights&quot;  &quot;iris&quot;</code></p>
<p>Another useful function is the <code>dbListFields</code>, which returns a vector with all of the column names in a table.</p>
<pre><code>dbListFields(con, &quot;flights&quot;)</code></pre>
<p><code>[1] &quot;year&quot;           &quot;month&quot;          &quot;day&quot;            &quot;dep_time&quot;       &quot;sched_dep_time&quot;  [6] &quot;dep_delay&quot;      &quot;arr_time&quot;       &quot;sched_arr_time&quot; &quot;arr_delay&quot;      &quot;carrier&quot;        [11] &quot;flight&quot;         &quot;tailnum&quot;        &quot;origin&quot;         &quot;dest&quot;           &quot;air_time&quot;       [16] &quot;distance&quot;       &quot;hour&quot;           &quot;minute&quot;         &quot;time_hour&quot;</code></p>
</div>
<div>
<h2>Interacting with the data using dplyr</h2>
<p>Using <code>dplyr</code>, we can easily preview a database. The <code>tbl()</code> command creates a reference to the table.</p>
<pre><code>library(dplyr)
tbl(con, &quot;flights&quot;)</code></pre>
<p></p>
<pre><code>Source:     table</code></pre></div></div>