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

71–80 of 198 posts

Re: Practical SQL for Data Analysis

#71

Post by beforeolives »

carabiner wrote: post #69
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 verbo…

> Can you honestly say you'd prefer to be debug thousands of lines of SQL versus the usual That's fair, I was using the opportunity to complain about pandas and didn't point out all the problems SQL has for some tasks. What I really want is a dataframe library for Python that's designed in a more sensible way than pandas.

Re: Practical SQL for Data Analysis

#72

Post by michelpp »

m16ghost wrote: post #29

>Pandas is a very popular tool for data analysis. It comes built-in with many useful features, it's battle tested and widely accepted. However, pandas is not always the best tool for the job. SQL is very useful, but there are some data manipulations which are much easier to perform in pandas/dplyr/data.table than in SQL. For example, the article discusses how to perform a pivot table, which takes data in a "long" for…

Does `tablefunc.crosstab()` do what you want?

https://www.postgresql.org/docs/13/tablefunc.html

Re: Practical SQL for Data Analysis

#73

Post by dragonwriter »

EForEndeavour wrote: post #65
dragonwriter wrote: post #49

Earlier quoted context omitted.

> 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, visualiza…

> 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

psql is a separate scriptable client tool from the postgres database server.

Re: Practical SQL for Data Analysis

#74

Post by crazygringo »

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.

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

Re: Practical SQL for Data Analysis

#75

Post by tarsinge »

tomnipotent wrote: post #37
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…

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

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.

Re: Practical SQL for Data Analysis

#76

Post by saltcured »

simonw wrote: post #12
jonas_b wrote: post #7

Question, if I have a CSV file that I'd like to do some quick SQL queries on before moving the results into Pandas. What would be good resource to do this? Preferably compatible with the rest of the Python-dataframe ecosystem and as simple as pd.read_csv()

If you want to try it in SQLite (pros: no need to run a server or install anything since it's in the Python standard library, cons: not nearly as many advanced statistical analysis features as PostgreSQL) my sqlite-utils CLI tool may help here: it can import from CSV/TSV/JSON into a SQLite database: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...

The sqlite3 connection object in Python allows you to register callables which you can use as scalar or aggregate functions in your SQL queries. With this, you can fill some of the gaps compared to PostgreSQL by essentially importing Python libraries. I just found this nice tutorial while looking for relevant docs:

https://wellsr.com/python/create-scalar-and-aggregate-functi...

However, I think the limited type system in SQLite means you would still want to extract more data to process in Python, whether via pandas, numpy, or scipy stats functions. Rather introducing new composite types, I think you might be stuck with just JSON strings and frequent deserialization/reserialization if you wanted to build up structured results and process them via layers of user-defined functions.

Re: Practical SQL for Data Analysis

#78

Post by default-kramer »

dreyfan wrote: post #25
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…

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

"No composability or code reuse" is definitely a valid criticism of SQL. Check out the very first example of my personal project https://docs.racket-lang.org/plisqin/Read_Me_First.html and let me know how you would implement something akin to `(CategoryName p)` and `(TotalSales p)` in SQL. A view does not count, because then you either have one view per derived field and that is very cumbersome to consume, or you have one view with all the derived fields and you will end up paying performance costs for fields that you are not using.

Re: Practical SQL for Data Analysis

#79

Post by jstrong »

tarsinge wrote: post #75
tomnipotent wrote: post #37

Earlier quoted context omitted.

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

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.

Re: Practical SQL for Data Analysis

#80

Post by shkkmo »

dragonwriter wrote: post #73
EForEndeavour wrote: post #65

Earlier quoted context omitted.

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, visualiza…

> 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 psql is a separate scriptable client tool from the postgres database server.

The point was about doing more work using the SQL DB engine, the client library you do that with seems irrelevant to making that point.
Post reply on HN