Why are pandas/R/etc so common in a data scientist or analyst's workflow when one can resort to UDFs in an RDBMS?

Question

It seems common for an analyst to have this workflow when using an RDBMS: use SQL to get a subset of data from the database, export it or use a connector, and then apply a data mining / model algorithm on it (e.g. kNN, regression models, etc) using something like Pandas, R or Matlab. In other words one operates within the DBMS and then operates outside of it.

One can use User-defined Functions (UDF) to implement the algorithm of choice in many languages (build a UDF in C using e.g. PostgreSQL) and remain operational within the DBMS environment, i.e. one does not give up the benefits obtained of operating inside of a DBMS. Furthermore, one can seamlessly integrate the UDF with the SQL construct, which is very powerful if one requires to perform subsequent SQL operations on the data which passed through a model.

I was wondering what is the reason behind operating outside of the DBMS given that such functionality is available and that the DBMS is, ironically, good at managing data (supported by many years of research).

There are other discussions which seem to conclude that SQL is for preprocessing and Pandas is for data analysis; to clarify I am building on this conclusion: why did things end up this way, when the DBMS is very well-suited for data analysis as well?

Answer 1

I think the answer might be this:

The main difference between Python -and R- and SQL is that the first are Turing complete.

A given programming language is said to be Turing-complete if it can be shown that it is computationally equivalent to a Turing machine.

That is, any problem that can be solved on a Turing machine using a finite amount of resources (i.e., time and tape), can be solved with the other language using a finite amount of its resources.

Typically, one proves a given language is Turing-complete by providing a recipe for translating any given Turing machine program into an equivalent program in the language in question. Alternately, one can provide a translation scheme from another language, one that has already been proven to be Turing-complete.

Nearly every existing computer language is Turing-complete. About the only computer languages that aren't Turing-complete are a handful of special languages that are capable of LESS than a Turing machine - usually because some limitation is "hard-wired" into the language's structure or definition. (For example, Hofstadter's designed a language called BlooP so that it was impossible for it to have a iteration structure with an arbitrarily high upper bound.)

Turing Completeness definition

The question of yours is: Why we don't use SQL (or another DBMS) to do all the Data Science process? The answer is that because SQL lenguage is limited in certain aspects that Python and R are not. And one of those aspects is key to Data Science: Recursion.

Just imagine Decision Trees and all their variants without recursion! They are impossible!

Is SQL Turing Complete?

Answer 2

In my opinion there are multiple reasons, but I will narrow my answer down to a few:

1. Architecture: DBMS is not typically the place where serious computing should take place. Typically it's the role of the application servers, and there are solutions available that allow to scale such architectures easily. Therefore, the analysis and models are computed in the application servers, where computing technologies reside.

2. This stems from 1) a bit: there are many different DBMS servers and therefore to popularize data analysis frameworks, they should be as much DBMS-agnostic (or even data source-agnostic) as possibile. It's the separation of concerns that comes into play here. Imagine what would happen if you were to place an implementation of linear regression algorithm on, say PostgreSQL server and then wanted to use this on MySQL too. You would need to do some serious porting and end up maintaining two implementations of the same algorithm. But if you move the implementation away from the DBMS suddenly you just maintain one implementation that does not care about the place where the data comes from.

3. When you put this to the DBMS, you are limiting yourself in the number of technologies that you can use to actually implement your algorithms. There are more convenient languages than pure C to implement such solutions.

I would say, if you are concerned with great speed of execution, at the cost of maintainability, portability and perhaps scalability, then placing this on DBMS might be something worth considering.

Answer 3

For an analytic data scientist pulling an RDBS on a repetitive pattern basis, it's an excellent idea to enlist someone to write an UDF.

For an analytic data scientist employed by an enterprise to ask, "please, can you implement these 30,000 C and Fortran libraries as UDF" is probably not a career enhancing move.