{"id":2087,"date":"2015-09-18T00:00:00","date_gmt":"2015-09-14T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-and-r\/"},"modified":"2021-05-03T14:02:04","modified_gmt":"2021-05-03T14:02:04","slug":"sql-and-r","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/sql-and-r\/","title":{"rendered":"SQL and R"},"content":{"rendered":"
\n

The R platform and programming language supports a vast array of data science techniq. With decades of history and over 7,000 packages<\/a> available on CRAN it can be overwhelming to determine where to start. The R-Basics<\/a> and Visualizing Data with R<\/a> articles provide initial direction, but don’t go into much detail about how to manipulate datasets within R.  <\/p>\n

Fortunately, database professionals can be productive quickly in this realm by leveraging their well-honed SQL skills.  As you might expect, R supports the use of SQL to retrieve data from centrally located relational databases. However, several packages in R allow you to go beyond this realm and create and query ad-hoc datasets on the fly in the midst of processing and analyzing data, regardless of the data’s original source or final destination. In this article, we will look at several different approaches that involve manipulating data with SQL using various R packages.<\/p>\n

There is a great deal of excitement regarding Microsoft’s acquisition of Revolution Analytics that subsequently lead to R being integrated into SQLServer 2016.  SQLServer 2016 is available as a preview, but is still subject to changes before its official release. Simple-talk is filled with excellent articles that focus specifically on SQLServer. Once the details of the release are finalized, you can expect more upcoming articles that uniquely address R as implemented within SQLServer. But since the final version of SQLServer is not yet available at this time, and Simple-Talk has already covered SQL Server Access from R<\/a>, this article will demonstrate open source R in RStudio using SQL with other relational databases.<\/p>\n

Part of the appeal of SQL is its availability in so many different products.  Most technical professionals find themselves having to support and interact with a range of technologies from both open source and commercial vendors. Despite vendor-specific features and extensions, SQL is relatively standard across implementations so its use as presented here will be familiar even to those who currently work exclusively with SQLServer. This article will include references to two databases, SQLite, a small freely available (public domain) database that many people use without even knowing it and Oracle, a large commercial offering and competitor to SQLServer. The use of RODBC to access SQL Server has already been covered in the Simple-Talk article ‘<\/a>Making Data Analytics Simpler: SQL Server and R’<\/a><\/p>\n

The RSQLite package<\/h2>\n

SQLite is the most widely deployed database<\/a> in the world. It is included on Android, iPhone and IOS devices and in Firefox, Chrome and Safari web browsers. Apple and Microsoft<\/a> include it in their OSX and Windows 10 operating systems respectively and there are many other products that include SQLite. It is extremely easy to use, and can be of great value to developers who need a database available but want to avoid the overhead often associated with installing and configuring an external database. In this demonstration, we will download and install the RSQLite package which will integrate SQLite into R running in RStudio.  <\/p>\n

This example will show how data sets can seamlessly be written into a SQLite database for subsequent analysis using familiar SQL statements (the data in question pertains to automobile performance and fuel efficiency). The dataset is included with R during its initial installation. Since the included data is readily available within R, it is not necessary to import the data from a spreadsheet or other external source. Such datasets are used in examples in R documentation, so they are available within R during its initial installation, or are added along with code when new packages are installed.<\/p>\n

The “mtcars” dataset is included by default in R. It consists of data from the 1974 Motor Trend US magazine (hence the “mt” in the dataset name). The dataset describes fuel consumption and various aspects of automobile design and performance for 32 automobiles. The help function will display a document that contains in-depth description of the structure and contents of this dataset.<\/p>\n

help(mtcars)\n<\/pre>\n
In order to access this dataset, it must first be loaded.  This “attaches” the dataset to the user’s current R session.   <\/p>\n
data(mtcars)\n<\/pre>\n
The dataset is represented in an object called a data frame  comprised of rows and columns. The  data frame is small enough to be easily perused in a spreadsheet-like display  using the  View<\/b> command.  <\/p>\n
View(mtcars)\n<\/pre>\n
 \"2282-clip_image002.jpg\"<\/p>\n
If you want to remove the dataset from your session at a  later point to free up resources without exiting R, you can run the rm function. When you run this command, you will notice the mtcars variable listed in  the Environment pane will disappear.<\/p>\n
rm(mtcars)\n<\/pre>\n
Within RStudio, the SQLite package must be installed unless  it had been previously installed. The  library function then can be called to load the package into the current working  environment. <\/p>\n
install.packages(\"RSQLite\")\nlibrary(RSQLite)\n<\/pre>\n
Next we will create a new, empty SQLite database where we  can store the cars data. SQLite has  a rather simple data storage mechanism, all data for a database is installed  within a single file.  The name of  this file must be specified when the database is created, and a connection to  this database is returned and used in subsequent commands to access and  manipulate the data and data structures within the database.<\/p>\n
conn <- dbConnect(SQLite(),'mycars.db')\n<\/pre>\n
This command creates a file named “mycars.db” in the  current working directory. If you  aren’t sure where this is, run  getwd()<\/b> to print  out your working directory and  setwd(“some-directory”)<\/b> to navigate to a  different directory. To actually  create a table, we will read data from the mtcars dataset and write it to the  new database.<\/p>\n
With the data loaded, and an active database connection to  the SQLite database, we can write the data by specifying the connection, the  name of the table, and the name of the data frame that contains the data to be  persisted.<\/p>\n
dbWriteTable(conn, \"cars\", mtcars)\n<\/pre>\n
This simple statement created a table in the database with  data types analogous to the R data frame columns. The names of the table columns were based on the names of the columns in  the data frame. No complicated  CREATE TABLE statement was required with explicit definitions of column names,  data types, precision, storage configuration or other options. This level of  detail is unnecessary when the focus is on performing an ad-hoc exploratory data  analysis within R rather than defining a schema in a centralized database that  is intended for long-term use.   However, it is possible to run CREATE TABLE statements if you like to using  standard SQL DDL.<\/p>\n
dbGetQuery(conn, 'CREATE TABLE test_table(id int, name text)')\n<\/pre>\n
SQLite, like other relational database stores metadata  describing the objects it contains. The tables in the database can be listed by a single function call.  <\/p>\n
dbListTables(conn)\n<\/pre>\n
Likewise, the field names for a given table can be listed  referencing the connection and the table name.<\/p>\n
dbListFields(conn, \"cars\")\n<\/pre>\n
With a connection available, a database created and a table  populated with data, queries can now be executed using the dbGetQuery<\/b>  function.<\/p>\n
dbGetQuery(conn, \"SELECT * FROM cars WHERE mpg > 20\")\n<\/pre>\n
Standard SQL syntax is available, though as in other  contexts where SQL is embedded in strings, you need to consider your usage of  quotes. Often it is simplest to  surround your queries with double quotes so that strings in a SQL statement can  be surrounded in single quotes.<\/b><\/p>\n
dbGetQuery(conn, \"SELECT * FROM cars WHERE row_names LIKE 'Merc%'\")\n<\/pre>\n
As you might expect, modifying tables using SQL is also  possible using RSQLite. But there is  a shortcut if the case where a data frame is modified in the context of R with  the intention of overwriting a table previously created. The following example extracts the “make” of the car from the row name  where the make and model are concatenated.  <\/p>\n
mtcars$make <- gsub(' .*$', '', rownames(mtcars))\n<\/pre>\n
This statement says in essence, “create a new column for  the data frame named “make” and populate each row’s make value by taking the row  name, finding the substring that starts with the first space and ends at the end  of the string, and removing that substring.” What remains is the first word in the string. The resulting data frame can be viewed to reveal the new make column  appended as the last column.<\/p>\n
 \"2282-clip_image004.jpg\"<\/p>\n
The new column can now be used in queries like any of the  other columns.<\/p>\n
> dbGetQuery(conn, \"SELECT make, count(*) FROM cars GROUP BY make HAVING count(*) > 1 ORDER BY 2 DESC, 1\")\n<\/pre>\n
<\/p>\n
    make count(*)\n1   Merc        7\n2   Fiat        2\n3 Hornet        2\n4  Mazda        2\n5 Toyota        2\n<\/pre>\n
The RSQLite package makes it easy to take data from files,  spreadsheets, or other sources and quickly integrate it into a SQL accessible  database. It allows you to do a  great deal of data processing without requiring the additional time, resources  and effort needed to set up and maintain an external database. As convenient as this is, another package called sqldf simplifies this  type of processing even further. It  allows you to use SQL on data frames without giving any thought to setting up a  database at all. <\/p>\n
The sqldf package<\/h2>\n
There is tremendous value in consistently using SQL (or  SQL-like) languages to explore and process data.  Data Science professionals are frequently faced with the challenge of  integrating data from disparate data sources. Many of these are relational databases, and so require SQL to retrieve  data. In addition, NoSQL data  sources often support high level, declarative, SQL-like languages. For example, Hadoop users can use Hive and Pig. Cassandra provides access to data stored in column families (analogous to  relational tables) using Cassandra Query Language (CQL). In many cases data in arbitrary text files is structured enough to be  easily imported into a database, and a variety of utilities are commonly used to  make semi-structured data SQL accessible. Thinking about data in relational terms lends itself towards  Tidy data formatting<\/a> that has value even outside of the relational realm.<\/p>\n
The sqldf package allows you to access data frames using  SQL. Regardless of where data  originates, it can be queried as long as it is contained within a data frame. This means that data can  be  read in from a variety of data sources (delimited files, a web pages, web APIs,  a relational databases, NoSQL datasoures, etc) and subsequently queried and  manipulated as if it were all in a single relational database. To see how straightforward it is, open a new R Session, install the  package, load it and the mtcars data.<\/p>\n
install.packages(\"sqldf\")\nlibrary(sqldf)\ndata(mtcars)\n<\/pre>\n
SQLDF allows you to query the data frame as if it were a  table and it is often as simple as passing the query as a string to the sqldf  function.<\/p>\n
sqldf(\"SELECT * FROM mtcars WHERE mpg > 20\")\n<\/pre>\n
If you are following along and executed this statement in  RStudio, the number of rows is correct, but the row names containing the name of  each car are missing.<\/p>\n
 \"2282-clip_image006.jpg\"<\/p>\n
The reason is that row names are not standard columns and  are ignored by default by sqldf. To  include these rows in the output, specify  row.names=TRUE<\/b> when making the call.<\/p>\n
sqldf(\"SELECT * FROM mtcars WHERE mpg > 20\", row.names=TRUE)\n<\/pre>\n
Within R, there are many ways to create new data frames. The base language contains support, and packages like dplyr and reshape  are in common use. With sqldf, you  can bypass the use of all of this. In fact, the sqldf call itself returns a dataframe. With this in mind, you can do sequences of calls to sqldf to  incrementally process or summarize a data set.<\/p>\n
df <- sqldf(\"SELECT * FROM mtcars WHERE mpg > 20\", row.names=TRUE)\n<\/pre>\n
The df object now contains a dataframe with the results of  the query. If you are going to  process a data frame in this way, you are better off making the row names an  ordinary column value.<\/p>\n
df$make_model<-row.names(df)\n<\/pre>\n
The new column is now available in the dataframe. And any result of a query, even if it varies widely from the original is  simply returned as a new data frame.<\/p>\n
mpgSummary <- sqldf(\"select avg(mpg) avg, min(mpg) min, max(mpg) max from df where make_model like 'Merc%'\")\n<\/pre>\n
The judicious use of aliases to have meaningful column  names in each new dataset will make it easier to do subsequent processing. Limiting yourself to alphanumeric characters for column names is  recommended.<\/p>\n
mpgSummary\n<\/pre>\n
<\/p>\n
   avg  min  max\n1 23.6 22.8 24.4\n<\/pre>\n
The SQL used with sqldf will mirror that used by RSQLite  since behind the scenes, data is being written to SQLite tables for querying.   <\/p>\n
The mtcars data set internally available in R is convenient  for examples. Although it is great  for quickly demonstrating or learning functionality, it is not sufficient for  real world applications where data must be retrieved from an external source.<\/p>\n
File Imports<\/h2>\n
Before looking at making direct connections to a database,  it is important to recognize how simple and straightforward it is to read a  delimited file into RStudio. This  might be somewhat offensive to software developers who are accustomed to  creating applications that connect directly to databases using ODBC or JDBC.  But R Users frequently need to integrate data from several different data  sources. Rather than expend time and  energy configuring specific packages and loading drivers, it be worth  considering exporting data from a query to a data file and reading the file into  RStudio. This practice can also  circumvent the need to run resource intensive SQL statements multiple times on a  database.<\/p>\n
Exporting data as CSV is a well supported option in many  relational database systems. SQLServer’s Management Studio has a “Results To Text” dialog where “Comma  delimited” can be specified as an output format. MySQL has a non-standard SQL SELECT clause to specify an OUTFILE clause. Many SQL Clients include an option to export data in this way. CSVs exported from databases can be  quickly validated using any spreadsheet program.<\/p>\n
R itself can import data from a wide variety of file  formats. This flexibility results in  additional complexity, specifically a long list of functions, many of which have  a large number of parameters that can be set to alter their behavior. RStudio masks this complexity and provides a simple dialog for importing  files.  If you don’t have a CSV file  handy, you can start by creating one  using R based on the mtcars dataset we saw earlier.<\/p>\n
write.csv(mtcars, 'mtcars.csv')\n<\/pre>\n
Import this by selecting the “Import Dataset” option in the  Environment pane and choosing “From Text File”.<\/p>\n
 \"2282-clip_image008.jpg\"<\/p>\n
A dialog is opened that provides a preview of how the data  will be imported based on the options selected. In most cases, uncheck stringsAsFactors and the defaults selected will  suffice.  <\/p>\n
 \"2282-clip_image010.jpg\"<\/p>\n
As the data is imported, the actual R commands required to  read in and view the data are displayed inline in the console. The data is first read into R.<\/p>\n
mtcars <- read.csv(\"~\/Desktop\/r_art\/simple-talk-SQL-and-R\/mtcars.csv\", stringsAsFactors=FALSE)\n<\/pre>\n
The View function is then called on the new data frame to  display its contents.<\/p>\n
View(mtcars)\n<\/pre>\n
If you wish, the read.csv command can be set aside and used  in a script making it unnecessary to import data interactively using the dialog  in the future.<\/p>\n
Because of data can often be conveniently be exported to  simple text files, they are frequently the simplest way to get data into  RStudio. This is not always the case  of course. At times, when working  with a relational database the amount of data being processed is prohibitive, or  the number of data frames to be created makes it unwieldy to manually export and  import multiple data files. Under  these circumstances, a direct connection to the database is the best option. There are a number of database specific packages that support direct  connections. Most of these packages are  based on the RJDBC package, and the RJDBC package can be used independently to  access a large variety of databases.<\/p>\n
The RJava and RJDBC Package<\/h2>\n
JDBC is the Java analog to the ODBC standard that was  original developed by Microsoft. A  JDBC driver is software that enables a Java application to interact with a  specific database. The RJDBC package  therefore relies upon the RJava package and consequently on an underlying Java  installation on an R User’s machine. At times, the behavior of the Java Virtual Machine must be addressed to prevent  problems. For instance, if you  encounter OutOfMemoryExceptions, the solution is to increase the amount of  memory available to the JVM.<\/p>\n
options( java.parameters = \"-Xmx4g\" )\n<\/pre>\n
Suffice it to say that there are a wide range of  configuration possibilities that can result in problems due to the myriad of  possible permutations of versions of R, Java, RJDBC, particular JDBC drivers and  a specific database. These are  virtually unheard of when using a vendor-specific version of R included with a  particular database (as is the case with OracleR and Microsoft SQLServer 2016),  but they are not uncommon when using RJDBC to make database connections. Again, the previous section describes exporting\/importing csvs as a  viable workaround worth considering if you encounter these sorts of issues. RJDBC problems are not unsolvable, just time-consuming. Projects that involve a one-time data dump don’t merit the attention  required to fix them.<\/p>\n
Install and load the RJDBC library.<\/p>\n
install.packages(\"RJDBC\")\nlibrary(RJDBC)\n<\/pre>\n
This example uses the free  Oracle XE<\/a> database and a pre-installed demo account called HR. Note that in this case, the database installation itself is on a local  workstation, but completely external to R. Before running the R code, you must ensure that the HR user account is  available. From an operating system  prompt, connect as a user with DBA privileges.  <\/p>\n
sqlplus \/ as sysdba\n<\/pre>\n
Within SQL*Plus, unlock the HR account and set the password  to match the username.<\/p>\n
alter user HR account unlock;\nalter user HR identified by HR;\n<\/pre>\n
RJDBC requires the use of a JDBC driver, in this case an  Oracle JDBC driver that was installed as part of  Oracle’s Instant Client<\/a> is used. The code below includes a Java CLASSPATH that is specific to my personal  machine that must be modified to match your setup. It loads the JDBC driver which is then used to make a database  connection.<\/p>\n
drv <- JDBC(\"oracle.jdbc.OracleDriver\",\n            classPath=\"\/Users\/cas\/oracle\/product\/instantclient_11_2\/ojdbc6.jar\", \" \")\n<\/pre>\n
A connection is established using the loaded driver, a JDBC  URL, and the user and password associated with the HR account.<\/p>\n
con <- dbConnect(drv, \"jdbc:oracle:thin:@localhost:1521:XE\", \"HR\", \"HR\")\n<\/pre>\n
The connection is then passed in each call along with the  query.<\/p>\n
hrTables  <- dbGetQuery(con, \"select * from tab\")\n\t<\/pre>\n
This query displays all of the tables available within the  HR user’s schema.<\/p>\n
hrTables\n<\/pre>\n
<\/p>\n
             TNAME TABTYPE CLUSTERID\n1        COUNTRIES   TABLE        NA\n2      DEPARTMENTS   TABLE        NA\n3        EMPLOYEES   TABLE        NA\n4 EMP_DETAILS_VIEW    VIEW        NA\n5             JOBS   TABLE        NA\n6      JOB_HISTORY   TABLE        NA\n7        LOCATIONS   TABLE        NA\n8          REGIONS   TABLE        NA\n<\/pre>\n
Conclusion<\/h1>\n
There is plenty that is novel and perhaps foreign to a new  R user. But there is no reason to  abandon your hard-earned SQL skills. The RSQLite, SQLDF and RJDBC packages allow you to leverage your SQL skills  whether accessing external data or simply cleaning, filtering and modifying data  already loaded. When SQLServer is  released, the ease of integration of using the version R included will likely  enhance this synergy. R’s  flexibility in accessing other data sources will provide new opportunities to  create data-centric products and services that are far beyond what was  traditionally possible using SQL alone.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
Not only can you easily retrieve data from SQL Sources for analysis and visualisation in R, but you can also use SQL to create, clean, filter, query and otherwise manipulate datasets within R, using a wide choice of relational databases. There is no reason to abandon your hard-earned SQL skills!…<\/p>\n","protected":false},"author":177915,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538],"tags":[4143,4168,6011,4179,4150],"coauthors":[],"class_list":["post-2087","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-database","tag-software-tools","tag-source-control","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2087","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/177915"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2087"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2087\/revisions"}],"predecessor-version":[{"id":90790,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2087\/revisions\/90790"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2087"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2087"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2087"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2087"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}