5 ms·
SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no
by nerdponx 6y ago
SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc.
I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL.
For everything else, I'll continue using SQL to get the data from the database and use Pandas or Data.Table to actually analyze it.
That said, this is a very comprehensive review of SQL techniques which I think could be very useful for when you do have bigger datasets and/or just need to get data out of a database efficiently. Great writeup and IMO required reading for anyone looking to be a serious "independent" data scientist (i.e. not relying on data engineers to do basic ETL for you).
I'd be a huge fan of something like the PySpark DataFrame API that "compiles" to SQL* (but that doesn't require you to actually be using PySpark which is its own can of worms). I think this would be a lot nicer for data analysis than any traditional ORM style of API, at least for data analysis, while providing better composability and IDE support than writing raw SQL.
*I also want this for Numexpr: https://numexpr.readthedocs.io/en/latest/user_guide.html https://numexpr.readthedocs.io/en/latest/user_guide.html, but also want a lot of other things, like a Arrow-backed data frames and a Numexpr-like C library to interact with them.
- ramraj07 6y agoCompletely disagree. I have a choice of using snowflake and spark/pandas to do my EDA and I’ll choose sql every time. The code is significantly more readable once you get used to it and you can most definitely do things one step at a time using udfs and temp tables / cte s. I’ve come back to EDA I did a year back and it’s always easier to read a long sql script than a notebook with pandas code.
- disgruntledphd2 6y agoThere's pluses and minuses to both. That being said, how do I write a function in SQL which can abstract over something I do a lot (like the pivoting example earlier), or even some date function like iff(date>'important_date', 'before', 'after') as grouper. Honestly, that's what ends up making me move away from doing analytics in SQL.
- saltcured 6y agoEdit: oops, I think I replied a level deeper than intended. I was responding to the composition/abstraction topic. I think I should just leave this here now though? I assume you are talking about composition of generic set-processing routines, but I wonder if others realize that? It is easy enough to write a set-returning function and wrap it in arbitrary SQL queries to consume its output. But, it is not easy to write a set-consuming function that can be invoked on an arbitrary SQL query to define its input. Thus, you cannot easily build a library of set-manipulation functions and then compose them into different pipelines. I think different RDBMS dialects have different approaches here, but none feel like natural use of SQL. You might do something terrible with cursors. Or you might start passing around SQL string arguments to EXECUTE within the generic function, much like an eval() step in other interpreted languages. Other workarounds are to do everything as macro-processing (write your compositions in a different programming language and "compile" to SQL you pass to the query engine) or to abuse arrays or other variable-sized types (abuse some bloated "scalar" value in SQL as a quasi-set). What's missing is some nice, first-class query (closure) and type system. It would be nice to be able to write a CTE and use it as a named input to a function and to have a sub-query syntax to pass an anonymous input to a function. Instead, all we can do is expand the library of scalar and aggregate functions but constantly repeat ourselves with the boilerplate SQL query structures that orchestrate these row-level operations.
- disgruntledphd2 6y agoYeah, that's exactly the issue. One can do this in Python, but not in SQL, and this leads to boilerplate. I actually think that SparkSQL is a good solution here, as you can create easily reusable functions.
- rdedev 6y agoFor apache arrow backed dataframes check out Polars(https://github.com/ritchie46/polars https://github.com/ritchie46/polars)
- dreyfan 6y ago> SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc. You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.
- dunefox 6y agoThat could be said about any tool, framework, language, library, etc. Invest enough time and you can do everything with malbolge but that doesn't mean it doesn't suck.
- pandasusr 6y agoI am a pandas user considering refactoring part of my ETL pipeline to SQL. I see the trade off as memory efficiency vs expressiveness, and for simple queries on big data, SQL wins. Would you disagree that Pandas/Python is more expressive than SQL? I’m less experienced in SQL but based on my limited experience there, it seems Pandas is clearly more expressive. What is the SQL equivalent of Pandas .apply(lambda x) ?
- higeorge13 6y agoPlease define an example lambda function, in order to see whether there is an sql equivalent. Imo >90% of the data issues i have seen, can be solved with sql queries. I have seen some in the finance sector which would require super complex udfs, but other than these, sql is the first choise to solve a data issue.
- pandasusr 6y agoI do work in finance sector and need complex functions :) And it’s interesting that pandas was invented at a hedge fund, AQR. I agree that SQL may be better for vanilla BI.
- hodgesrm 6y agoClickHouse has lambdas for arrays. They are very useful. Here's an example. WITH ['a', 'bc', 'def', 'g'] AS array SELECT arrayFilter(v -> (length(v) > 1), array) AS filtered ┌─filtered─────┐ │ ['bc','def'] │ └──────────────┘ The lambda in this case is a selector for strings with more than one character. I would not argue that they are as general as Pandas, but they are might useful. More examples from the following article. https://altinity.com/blog/harnessing-the-power-of-clickhouse-arrays-part-2 https://altinity.com/blog/harnessing-the-power-of-clickhouse...
- disgruntledphd2 6y ago> (i.e. not relying on data engineers to do basic ETL for you). Is this actually a thing? Surely it can't be a thing.
- EForEndeavour 6y agoIn a sufficiently large org, why not?
- disgruntledphd2 6y agoMy concern is not that DE's do ETL, just that a data scientist could actually avoid learning how to do this. Certainly in all the successful orgs I've worked in, this would rarely be the case. Maybe it works if you have really well-standardized and static data, but I rarely work in this kind of environment.
- denimnerd42 6y agoof course, that's my job... we do basic ETL on hundreds of data sources to provide data to the analysts and quants
- hodgesrm 6y ago> I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL. This is not huge. This is quite small. Pandas can't handle data that runs into billions of rows or sub-second response on arbitrary queries. Both are common requirements in many analytic applications. I like Pandas. It's flexible and powerful if you have experience with it. But there's no question that SQL databases (especially data warehouses) handle large datasets and low latency response far better than anything in the Python ecosystem.
- nerdponx 6y agoThis is not huge. This is quite small. Pandas can't handle data that runs into billions of rows or sub-second response on arbitrary queries. Both are common requirements in many analytic applications. You're right. It's "huge" with respect to what you can expect to load into Pandas and get instant results from. But it's not even "medium" data on the small-medium-big spectrum. I like Pandas. It's flexible and powerful if you have experience with it. But there's no question that SQL databases (especially data warehouses) handle large datasets and low latency response far better than anything in the Python ecosystem. I agree with this 100%, but I think a lot of people missed it in my post.
- hodgesrm 6y agoOTOH Pandas/Numpy are great for results. One pattern that I'm hoping to try is running heavy lifting in SQL, transfer results via Apache Arrow, and manipulate the result set in Python with aforesaid libraries. I don't know enough about how Arrow handles streaming to understand how this would really work but it would break away from the single-threaded connectivity with expensive ser/deser databases have used since the days of Sybase Db-Library, the predecessor to ODBC. 36 years is probably long enough for that model.
- neurocean 6y agoYeah but that's not even close to being Pandas' value proposition. It's for the data scientist and analyst, not the db admin, data engineer or production applications.