Hacker News Reader

Comment by tomnipotent | Hacker News Reader

> it's that people way overuse it for SQL tasks as this article points out

I'm very confused by this. I've used pandas for ever a decade, and in most cases it's a massive time saver. I can do a single query to bring data into local memory in a Jupyter notebook, and from there re-use that memory across hundreds or more executions of pandas functions to further refine an analysis or whatever task I'm up to.

Your "copy-object-copy" is not relevant in data analysis use cases, and exists in pretty much any system that pulls data from an external service be it SQL or not.

View on HN

I know a team that would read in multi-GB CSVs from an FTP site into an orchestrator server using Pandas, write to locsl CSV in the orchestator, validate data after write, reload into pandas, painfully and manually check every data type, then use a pandas connector to a database.

Every step along the way here is wrong. The database came with SFTP connectors. The orchestrator should have simply told the database server to pull from the SFTP site into a landing table. Even if not, Pandas has datatypes you can specify on ingestion, which is heaven if your CSV or other files don't record filetypes yet are consistent in structure. Further, if you have custom validation you're applying (not a bad thing!) you likely rarely even need pandas; the native CSV library or (XLRD/openpyxl) for Excel are fine.

Ultimately, it is a training issue. The toolspace is too complex, with too many buzzwords to describe simple things, that people get lost in the whirlwind.

Uses pandas to infer datatypes leads to nasty coercions. Also, pandas --> objects --> chunk based reading yields the same performance as any other library you mentioned.

Absolutely. On top of that, the Pandas object type (most people's default for strings) can house any scalar type, including ints and floats. This has hit a LOT of immature adoption of pyspark, for example, where the datatypes get screwy.

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 means a SQL wizard, and fully acknowledging all of SQL's blemishes.

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

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

OTGH, to some (I suspect often rather large) extent, that's because most people (I suspect including many "data scientists") are pretty bad at doing their data munging in SQL.

Perhaps (although this tends to be a core skill for any experienced DS - I 100% would not hire anyone without basic SQL, as it's just setting them up for failure). SQL is the only tool I've used everywhere I worked.

But, SQL is bad at lots of stuff. For example, if you need to compare id1 and id2 (with some kind of locality sensitive hash or something). This requires a full join, which is prohibitive in any large data environment.

And honestly, writing 50 case when statements when I could write a function in R or Python is not my idea of a good time.

I love SQL, and do a lot with it, but there are definitely cases where it's the wrong tool. As an example, retention analyses are really annoying to do in SQL, but pretty easy in R/Python (to be fair, the article actually provides a solution here, but it's not standard).

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

Both of you are right.

Sure, if you need to grab a huge chunk of the entire database and then do tons of processing on every row that SQL simply cannot do, then you're right.

But when most people think SQL and database, they're thinking of grabbing a tiny fraction of rows, sped up by many orders of magnitude because it utilizes indexes, and doing all calculations/aggregations/joins server-side. Where it absolutely is going to be orders of magnitude more efficient.

Traditional database client API's often aren't going to be particularly performant in your case, because they're usually designed to read and store the entire result of a query in-memory before you can access it. If you're lucky you can enable streaming of results that bypasses this. Other times you'll be far better off accessing the data via some kind of command-line table export tool and streaming its output directly into your program.

I agree, they're both right.

I've been doing data science since around 2008 and it's a balance between local needs and repeated analysis and an often more efficient one off query. Sure the SQL optimizer is going to read off the index for a count(*), but it doesn't really help if I need all the rows locally for data mining anyway. The counts need to line up! So I'll take the snapshot of the data locally for the one off analysis and call it a day. If I need this type of report to be run nightly, it will be off of the data warehouse infrastructure not the production DB server.

Shrug. These things take type and experience to fully internalize and appreciate.

> But when most people think SQL and database

We're not talking about most people, but data analysts. If we're just doing simple sum/count/average aggregated by a column or two with some basic predicates, SQL and an RDBMS are your best friend. Always better to keep compute + storage as close together as possible.

> Traditional database client API's

I'm not sure what point you're trying to make with this. Most data analysts are not working against streaming data, but performing one-off analyses for business counterparts that can be as simple as "X,Y,Z by A,B,C" reports to "which of three marketing treatments for our snail mail catalogue was most effective at converting customers".

I'm not talking about streaming live data, I'm talking about streaming query results if you re-read my comment.

If you're reading many MB's or GB's of data from a database, it's a lot more performant to stream it from the database directly into your local data structure, rather than rely on default processing of query results as a single chunk which will be a lot worse for memory and speed.

Streaming aggregation relies on knowing the problem beforehand, so it's pointless in the context of the day-to-day work of analysts where most of the project is spent figuring out the right questions to ask.

If I need to stream data because the size of data is a constraint, I'm either working on the 0.1% of projects that require it or on an analytical product rather than an analytical project.

This is a great point and way of looking at it: pandas and SQL live at opposite ends of the maturation level of data wrangling pipelines.

The point is that if you only need to join and aggregate data it's easier and more efficient to do it directly in SQL. Also in production or when your database doesn't fit in memory the idea is to first use SQL to generate an optimized view of your data from the database before further analysis and transformation.

> The point is that if you only need to join and aggregate data it's easier and more efficient to do it directly in SQL

citation needed? I've seen plenty of cases where it would have taken the db ages to do something that pandas does fast, and I don't consider pandas to be particularly fast.

TFA

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

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.

If your query does " * " it gets all the columns in all the tables. Often, the optimizer when all the columns you need are on the index, never visits the actual table (I remember the term covered index). " * " basically screws this up. "Select *" should never be used in anything in an actual production environment.

Data analysis workloads more often run into problems solved by partitioning rather than indexes.

yes and no - I trained on "larger than RAM datasets" intentionally, and subsequently took on projects that require "larger than RAM datasets". Two things happened on my way, companies led by Google invented and deployed datasets previously impossible e.g. BigTable, and secondly the equipment I worked on went from modest to mid-sized RAM (hard to define that). Granted that lots of very useful (and common?) tasks are not "larger than RAM datasets", and then have very different characteristics.. which is starting to sound like "Excel problems look like this, they always fit in RAM, they need partitions not indexes" and the like..

There is a blind-man-and-the-Elephant drift here, which is not terrible, but might need calling out to improve..

> companies led by Google invented and deployed datasets previously impossible e.g. BigTable

BigTable, Redshift, Snowflake and every other "big data" storage system rely heavily on partitioning to achieve the scales they're able to achieve. Some of these systems don't even support indexes.

Covering index is right.

Select * has a few valid use cases.

If you're selecting from a cte or subquery then writing the same column list twice is redundant and increases complexity/risk of mistakes.

* is also harmless for count and exists.

I got the impression the problem is not so much the "SELECT *" as the missing "WHERE" clause.

Reply on news.ycombinator.com