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

51–60 of 198 posts

Re: Practical SQL for Data Analysis

#51

Post by qsort »

tomnipotent wrote: post #43
qsort wrote: post #42

Earlier quoted context omitted.

Pandas is great when you're working on the data manually because it lets you interleave python code and "queries", but it's strictly worse than a plain SQL statement if you're writing, say, a REST service that needs to output some data. SQL executed by the database is orders of magnitude more efficient, way more expressive, and doesn't require you to memorize pandas' absurd API. And I say this as someone who is by no…

> a REST service This is a post about data analysis, and everyone wants to point out that pandas isn't good at real-time service requests. > SQL executed by the database is orders of magnitude more efficient Compared to pandas? No, it's not. Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database.

> Compared to pandas? No, it's not.

I'm not saying you shouldn't use pandas, it depends on the size of the data. I'm working right now on a project where a SELECT * of the entire fact table would be a couple hundred gigabytes.

The flow is SQL -> pandas -> manipulation, and as always in pipelines like those, the most work you can do at the earliest stage, the better.

Re: Practical SQL for Data Analysis

#52

Post by musingsole »

nvilcins wrote: post #32
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…

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 of any actual change in difficulty.

/that's my current working theory, anyway

Re: Practical SQL for Data Analysis

#53

Post by WhompingWindows »

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…

Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know you'll throw out NA's on certain variables, if you need a certain date or age range, or even the way you format these filters: these all lead to important savings. And this saves R from being full on memory, which would often happen if you fed it way too much data.

Within a system of 1400 analysts, it makes a big difference if everyone's taking 2X or 4X the time pulling that they could be. Then, even the efficiently written pulls get slowed down, so you have to run things overnight...and what if you had an error on that overnight pull? Suffice to say, it'd have been much simpler if people wrote solid SQL from the start.

Re: Practical SQL for Data Analysis

#54

Post by dmitrykoval »

Following similar observations I was wondering if one can actually execute SQL queries inside of a Python process with the access to native Python functions and Numpy as UDFs. Thanks to Apache Arrow one can essentially combine DataFrame API with SQL within data analysis workflows, without the need to copy the data and write operators in a mix of C++ and Python, all within the confines of the same Python process.

So I implemented Vinum, which allows to execute queries which may invoke Numpy or Python functions as UDFs available to the interpreter. For example: "SELECT value, np.log(value) FROM t WHERE ..".

https://github.com/dmitrykoval/vinum

Finally, DuckDB makes a great progress integrating pandas dataframes into the API, with UDFs support coming soon. I would certainly recommend giving it a shot for OLAP workflows.

Re: Practical SQL for Data Analysis

#55

Post by ziml77 »

isoprophlex wrote: post #45
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…

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.

Setting inplace=True isn't too bad, but I definitely have had many issues with working with indexes in Pandas. I don't understand why they didn't design it so that the index can be referenced like any other columns. It overcomplicates things like having to know the subtle difference between join() and merge().

Re: Practical SQL for Data Analysis

#56

Post by disgruntledphd2 »

qsort wrote: post #51
tomnipotent wrote: post #43

Earlier quoted context omitted.

> a REST service This is a post about data analysis, and everyone wants to point out that pandas isn't good at real-time service requests. > SQL executed by the database is orders of magnitude more efficient Compared to pandas? No, it's not. Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database.

> Compared to pandas? No, it's not. I'm not saying you shouldn't use pandas, it depends on the size of the data. I'm working right now on a project where a SELECT * of the entire fact table would be a couple hundred gigabytes. The flow is SQL -> pandas -> manipulation, and as always in pipelines like those, the most work you can do at the earliest stage, the better.

Yeah, speaking as a data person, the SQL argument is correct. Python/R are much, much, much slower for this kind of work.

OTOH, SQL is super limiting for a lot of data analysis tasks and you'll inevitably need the data in weird forms that require lots of munging.

Personally, I'm a big fan of using SQL/Airflow/whatever to generate whatever data I'll need all the time at a high level of granularity (user/action etc), and then just run a (very quick) SQL query to get whatever you need into your analytics environment.

Gives you the best of both worlds, IME.

Re: Practical SQL for Data Analysis

#57

Post by disgruntledphd2 »

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.

True, but in that kind of exploratory environment, you'd normally reduce data load and speed up iteration by using sampling, which is super easy in SQL.

> Not for data analysis, it's a pointless constraint unless you're having issues.

This will always happen, as time spent on the project scales up (unless you use some kind of autoscaling magic I suppose).

Re: Practical SQL for Data Analysis

#59

Post by geraneum »

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…

Well it's true that bringing row(s) of data from database to python and then serializing it for any reason as has extra overhead.

But in Django's case, the same arguments can be made as for Pandas. Django is a big complex framework and an application using it might consume more memory due to countless number of other reasons. There are also best practices to use Django ORM.

But to say if a given Django instance consumes more memory it is only because of "Copy-Object-Copy effect"... I don't think so.

Re: Practical SQL for Data Analysis

#60

Post by disgruntledphd2 »

isoprophlex wrote: post #45
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…

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.

Post reply on HN