Do modern R and/or Python libraries make SQL obsolete?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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 custom intersection operations, custom aggregations, and custom scoring methods.

Answer 6

I have the exact same problem as OP except I have a lot of experience in SQL. SQL is appropriate periodically, but our database is very regular: it grows less than 6000 rows of data per year! I would guess the absolute total of all available disaggregated rows from the beginning of history is less than 700000. Overrelying on SQL to ask questions of small data is like passing around a magic eightball. Depending on the script you'll draw wildly different results for the same question.

Applying tidy principles to SQL is critical, and minimizing it's use for pre-analysis is key. Yes R is preferred once you run SQL to get broad data, but if you don't know R, you can build what you want in Power BI for free and accelerate Queries there in PowerQuery.

SQL is hammer, but data cleaning isn't a nail.

Obviously for truly big data operations, the initial tables necessitate complex timesuck queries. You need to know how to code that, but as a data scientist I need to be careful to reduce the reliance on SQL and incorporate tidy principles without reinventing the wheel.