14
$\begingroup$

I work in an office where SQL Server is the backbone of everything we do, from data processing to cleaning to munging. My colleague specializes in writing complex functions and stored procedures to methodically process incoming data so that it can be standardized and put to work in reports, visualizations, and analytics projects. Prior to starting here, I had very little experience with SQL, aside from writing the most basic of queries. The vast majority of my analysis prep work was all done in R. My boss insists that I improve my SQL skills, even though there seem to be very few assignments that can't be done more efficiently and with far fewer lines of code using R packages like dplyr, data.table, and tidyr (to name a few). My question is--does this make sense?

A couple weeks ago, I found myself faced with the task of getting a list of column names for each row in a table that met certain criteria and concatenating them into a vector of strings. There was a tight deadline and at the time, I was experiencing some blockage and couldn't quite wrap my head around the problem. I asked my boss, who in turn asked my colleague to write a script TSQL to solve the problem. While he was working on it, I figured out a way to do it in R writing a fairly simple function and applying it over the data frame. My colleague came back with his script about two hours later. It was at least 75 lines comprising two nested for loops. I asked him to tell notify when it was finished running and he said it would take several hours. Meanwhile my R script was able to loop over the ~45,000 records in about 30 seconds.

Am I right to assume that R is a much better choice for cleaning and munging data? Maybe the SQL developer in my office is just inept? I'm curious if anyone who has worked with both R and SQL (or Python and SQL for that matter) have any thoughts on this.

$\endgroup$
  • 2
    $\begingroup$ If your database is small enough and static, you can load it into memory and use your preferred ETL tool, like dplyr. Your approach simply will not work when you have big data in the cloud. I regularly run queries that make BigQuery (Google) complain. I write queries directly in SQL but I could use Spark as a middle layer to operate in dataframes if I wanted. $\endgroup$ – Emre Feb 24 '17 at 22:41
  • 1
    $\begingroup$ So is SQL inherently more efficient than R in terms of the way the data is stored, or is it just that SQL servers tend to have more built-in memory and processing power? $\endgroup$ – AffableAmbler Feb 25 '17 at 3:45
  • 1
    $\begingroup$ You can't make a blanket statement -- it depends on implementation -- but good databases have query optimizers, and some of them (like BigQuery) support multicore execution. Maybe what you want is a dataframe or ORM abstraction on top of your database to avoid SQL. It seems dplyr already does this to some extent (cf. SQL translation). You could benchmark the same query in dplyr against raw SQL to find out. What some do is to take a small sample of data for prototyping, then whip out the big data tools for production $\endgroup$ – Emre Feb 25 '17 at 4:50
  • 3
    $\begingroup$ You can simply run R inside SQL Server and have the best of both worlds $\endgroup$ – Gaius Jul 14 '17 at 20:26
13
$\begingroup$

R and SQL are two completely different beasts. SQL is a language that you can use to query data that is stored in databases as you already experienced. The benefits of SQL versus R lays mostly in the fact of the database server (MS SQL, Oracle, PostgreSQL, MySQL, etc.).

Most, if not all, modern database servers permit multiple users to query data from the same data source and insert, update and delete data in the same tables all while ensuring that the data remains consistent. This is essential for say recording a bank transaction. Can you imagine running a bank on R? That's where database servers come in. They ensure ACID properties of procedures run on the database. ACID stands for Atomicity, concurrency, isolation and durability (see ACID description on wikipedia). R is a single user platform where everything happens in memory. So, if your computer stops working halfway in a big operation, your data will not be stored. You are also the only person who can access the data. To be clear, R is not considered an alternative for database servers and/or SQL.

Another main advantage of database servers is that a good database design will ensure that you can query your database fast by performing query optimization. To achieve this database servers keep track of the design of a table. See for a full discussion of this topic the wiki page. R cannot perform query optimization. Poor database design, can lead to slow execution of your queries. Database servers can also perform optimization over queries that query multiple tables if foreign keys are properly used in the database design.

The SQL language has a very different syntax and I share your experience that it is shorter to write data munging steps using the data table or dplyr syntax. However, sometimes your data is too big for R or you need to store the results in the database as part of a periodic batch job, which will require to code your logic in SQL.

In my experience there are particular use cases for SQL and R/Python. SQL is great for storing business critical data and for allowing multiple people to access, modify, insert and delete data in a centralized environment. For any one-off data munging R and Python are great. If your data munging needs to be periodically executed, you will need to port your R/Python script to SQL.

$\endgroup$
3
$\begingroup$

These aren't even comparable, really. SQL is a language meant for accessing data, R is a language meant for working with data.

SQL isn't an effective tool for munging because it it's difficult to see intermediate steps and when it throws errors, it isn't likely to address the form/quality/structure of your data.

My workflow is typically:

  1. Get raw data from SQL query (in R)
  2. Build munging routine
  3. If possible, re-write SQL query to accomplish munging I accomplished in R

Also realize that not all consumers of data use R, but many still interface their platform of choice with data using SQL.

$\endgroup$
  • 1
    $\begingroup$ This is the same process that I follow (much to my supervisor's dislike). I agree that performing complex munging tasks such as the one I describe above seem to be a lot more efficiently done in a language like R. (Appreciate the affirmation). But if the only purpose of SQL is to be a giant hard drive for your data, why not just have an R server? It seems like all the functions (mapping, setting up keys to link tables, grouping, and joining data) can now all be done very effectively in R. Is a SQL table more efficient in terms memory use than an R data frame? $\endgroup$ – AffableAmbler Feb 25 '17 at 3:42
  • 1
    $\begingroup$ @Noah because not all people use R. $\endgroup$ – HEITZ Feb 25 '17 at 17:52
2
$\begingroup$

library(dbplyr) has the correct approach: write everything in R (using the tidyverse) and let the library just-in-time "compile" the R code to low-level SQL.

Since not all munging is translatable, another approach is the one taken by SQL Server: let R code snippets be invoked from SQL "select" commands.

$\endgroup$
1
$\begingroup$

The 1., 2., 3. approach mentioned by HEITZ is in my experience possible extending with an alternative for 3. where you write your data from R (data.table) back into MySQL.

So full steps are MySQL->data.table->MySQL

If you ensure you use data.table syntax where you don't copy the DT its also RAM-friendly.

$\endgroup$
1
$\begingroup$

In a word NO . SQL is a powerful concise and flexible way to describe and summarize structured semi structured and even unstructured data - when an appropriate interpreter layer is placed atop it. By the way sql is considered a nearly must-have for data scientists.

SQL is a concise and powerful way to perform its core operations of:

  • projections (select ..)
  • filtering ( where ..)
  • grouping / filtering ( group by and having)
  • basic aggregations ( count, sum , avg ..)
  • joins

The real power comes when combining results using inline views . When I need to do that I will use one of sqldf, pandasql, pysparkSql/sparkSql or a direct rdbms connection. Writing the same in the most concise manner possible with data.table (much better than data.frame) or datatable (better than pandas) is still more clunky, much more clunky or nearly impossible depending on the complexity of the queries attempted.

For data munging: that is a different story: some operations are easily expressed in sql and some not so much. When however you incorporate UDFs there is a wider latitude of what can be achieved. My current task includes a number of UDFs to do such things as customer intersection operations, custom aggregations, and custom scoring methods.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.