Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Practical SQL for Data Analysis

hakibenita.com

Post reply on HN
Print view View original

61–70 of 198 posts

Re: Practical SQL for Data Analysis

#61

Post by EForEndeavour »

tomnipotent wrote: post #41
mistrial9 wrote: post #40

Earlier quoted context omitted.

parent post says "devs do SELECT *" and ... relational databases can have a lot more data in one table, or related tables, than one query needs. It is often very wasteful of RAM to get ALL and filter again

Not for data analysis, it's a pointless constraint unless you're having issues. Most data analysis isn't against datasets larger than memory, and I'd rather have more data than I need to spend time waiting to bring it all local again because I forgot a few columns that turn out to be useful later on.

I totally get what you're saying because most of my datasets also used to be smaller than my laptop's memory.

Used to.

I'm in the process of moving more and more processing "upstream" from local pandas to the DBMS and it's already proving to be a bit of a superpower. I regret not learning it earlier, but the second-best time is now.

Re: Practical SQL for Data Analysis

#62

Post by disgruntledphd2 »

musingsole wrote: post #52
nvilcins wrote: post #32

Earlier quoted context omitted.

You might be thinking of specific functionality that you find is being implemented in an overly long/verbose fashion.. But generally speaking, how are > long lines of().chained().['expressions'].like_this(0) a _bad thing_? IMHO these pandas chains are easy to read and communicate quite clearly what's being done. If anything, I've found that in my day-to-day while reading pandas I parse the meaning of those chains at…

People don't like them because the information density of pandas chains is soooo much higher than the rest of the surrounding code. So, they're reading along at a happy place, consuming a few concepts per statement ...and then BOOM, pandas chain! One statement containing 29+ concepts and their implications. Followed by more low density code. The rollercoaster leads to complaints because it feels harder. Not because o…

Hmmm, interesting. I don't mind the information density, coming from R which is even more terse, but the API itself is just not that well thought out (which is fair enough, he was learning as he went).

Re: Practical SQL for Data Analysis

#63

Post by disgruntledphd2 »

nerdponx wrote: post #2

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…

> (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.

Re: Practical SQL for Data Analysis

#64

Post by disgruntledphd2 »

ramraj07 wrote: post #11
nerdponx wrote: post #2

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…

Completely 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.

There'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.

Re: Practical SQL for Data Analysis

#65

Post by EForEndeavour »

dragonwriter wrote: post #49
michelpp wrote: post #26

This is an excellent example of what I call the Copy-Object-Copy effect. It's particularly apparent in frameworks with ORMs like Django. In Pandas case, devs will do 'SELECT *' and use pandas as a sort of pseudo ORM. You run a query to get your data as a bunch of objects, but you're copying the data over the db connection from the postgres wire protocol into Python objects in memory, which are typically then garbage…

> If psql can't do what you want, resist the temptation to 'SELECT *' into a data frame and break the problem up into stages where you get the database to do the maximum work before it gets to the data frame. Why are we introducing an additional tool (psql) here unnecessarily? Sure, if it can be a simpler postgres query, that can be useful, but introducing psql and psql scripting into a workflow that is still going t…

I'm pretty sure the premise here is that the many gigabytes/terabytes of data reside in an RDBMS to begin with, so we aren't introducing another tool -- we're saying it makes sense to leverage multiple decades' worth of database optimizations by doing a bunch of data filtering, processing, and analysis in the database software and then exporting intermediate results to a pandas environment for final tweaks, visualization, etc.

Re: Practical SQL for Data Analysis

#66

Post by shankr »

disgruntledphd2 wrote: post #60
isoprophlex wrote: post #45

Earlier quoted context omitted.

This really grinds my gears too. There's something about the pandas API that makes it impossible for me to do basic ops without tedious manual browsing to get inplace or index arguments right... assignments and conditionals are needlessly verbose too. Pyspark on the other hand just sticks in my brain, somehow. Chained pyspark method calls looks much neater.

Pandas is just a bad API, to be honest. I know base-R very, very well (which was one of the inspirations, I believe) and I still spend most of my time looking stuff up. It's such a shame that python doesn't have a better DF library.

I also switched from R to Python/pandas. I remember always being frustrated with pandas since it tries to emulate data.frame, but then just does its own thing without being consistent.

Re: Practical SQL for Data Analysis

#67

Post by jplr8922 »

tldr ; if you hear ''but we can do this in SQL'', RUN!!!

My eyes hurt as I read this article. There are reasons why analysts dont use SQL to do their job, and it has nothing to do with saving RAM and memory.

1) Data analysis is not a linear process, it involve playing and manipulating the data in different way and letting your mind drift a bit. You want your project in an IDE made for that purpose, the ability to create charts, source control, export and share the information, ect. Pandas is just a piece of that puzzle which is not possible to replicate in pure SQL.

2) In 2020, there are numerical methods you want to try beyond a traditional regression. Most real world data problems are not made for stats101 tools included in sql. Kurtosis? Autocorrelation?

3) Politics. Most database administrator are control freaks who hate the idea of somebody else doing stuff in their DB. Right now were I work we still have to use SSIS-2013 instead of stored procedures in order to avoid the DBA refusal bureaucratic process.

4) Eventual professional development. If your analysis is good and creates value, chances are it will become a 'real' program and you will have to explain what you are doing to turn in into an OOP tool. If you have CS101, good coding in python will make this process much easier than a 3000 lines spagetti-SQL SQL script.

5) Data cleaning. Dealing with outliers, NAN and all that jazz really depends on the problem you try to solve. The absence of a one size fits all solutions is a good case for R/pandas/etc. These issue will break an SQL script in no time.

6) Debugging in SQL. Hahahahahahahaha

If you are still preocupied with the ram usage of your PC to do your project, here are two solutions which infuriate a lot of DBAs I've worked with.

A) https://www.amazon.ca/s?k=ram&__mk_fr_CA=%C3%85M%C3%85%C5%BD...

B) https://aws.amazon.com/

Re: Practical SQL for Data Analysis

#68

Post by EForEndeavour »

disgruntledphd2 wrote: post #60
isoprophlex wrote: post #45

Earlier quoted context omitted.

This really grinds my gears too. There's something about the pandas API that makes it impossible for me to do basic ops without tedious manual browsing to get inplace or index arguments right... assignments and conditionals are needlessly verbose too. Pyspark on the other hand just sticks in my brain, somehow. Chained pyspark method calls looks much neater.

Pandas is just a bad API, to be honest. I know base-R very, very well (which was one of the inspirations, I believe) and I still spend most of my time looking stuff up. It's such a shame that python doesn't have a better DF library.

I've used pandas regularly for the past ~5 years and find its API intuitive enough not to complain. I can write and read decently long pandas chained expressions fluently, but I barely know any R. Am I unwittingly a hostage of an inferior API and don't know what I'm missing?

Re: Practical SQL for Data Analysis

#69

Post by carabiner »

beforeolives wrote: post #3

Nice article. Pandas gets the job done but it's such a step backwards in terms of useability, API consistency and code feel. You can do anything that you can possibly need with it but you regularly have to look up things that you've looked up before because the different parts of the library are patched up together and don't work consistenly in an intuitive way. And then you end up with long lines of().chained().['ex…

Can you honestly say you'd prefer to be debug thousands of lines of SQL versus the usual I learned Pandas first. I have no issue with indexing, different ways of referencing cells, modifying individual rows and columns, numerous ways of slicing and dicing. It gets a little sprawling but there's a method to the madness. I can come back to it months later and easily debug. With SQL, it's just madness and 10x more verbose.

Re: Practical SQL for Data Analysis

#70

Post by hodgesrm »

pandasusr wrote: post #28
dreyfan wrote: post #25

Earlier quoted context omitted.

> 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.

I 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) ?

ClickHouse 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...

Post reply on HN