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

31–40 of 198 posts

Re: Practical SQL for Data Analysis

#31

Post by alexilliamson »

beforeolives wrote: post #24
nerdponx wrote: post #5

Earlier quoted context omitted.

I much prefer writing actual functions in a real programming language to the verbose non-reusable non-composable relic that is SQL syntax. Give me a better query language and I will gladly drop Pandas and Data.Table.

Yes, SQL does have the problem of bad composeability and some things being less explicit than they are when working with dataframes. Dplyr is probably the most sane API out of all of them.

+1 for dplyr. I've used both pandas and dplyr daily for a couple years at different times in my career, and there is no comparison in mind when it comes to usability/verbosity/number of times I need to look at the documentation.

Re: Practical SQL for Data Analysis

#32

Post by nvilcins »

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 least as efficiently as from comments of any level of specificity, or from what other languages (that I have had experience with) would've looked like.

Re: Practical SQL for Data Analysis

#33

Post by higeorge13 »

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

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

Re: Practical SQL for Data Analysis

#34

Post by password4321 »

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…

I would appreciate pointers to any other low/no-copy db -> browser technologies.

I was thinking it would be cool to use parts of JavaScript db libraries to parse query results passed straight through in (compressed?) db wire protocol format via WebRTC.

Re: Practical SQL for Data Analysis

#35

Post by mixmastamyk »

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.

A more useful comment would be to illustrate how. I'd like to know as well as I need to reuse queries in SQL occasionally but am not an expert. Currently I believe learning pl/pgsql is the answer, but even it reeks of punch-cards and other sixtiesisms :-). Tough sell when you're used to Python etc.

Re: Practical SQL for Data Analysis

#36

Post by [deleted] »

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…

[deleted]

Re: Practical SQL for Data Analysis

#37

Post by tomnipotent »

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

Re: Practical SQL for Data Analysis

#38

Post by mixmastamyk »

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…

Interesting. I'm guessing the extra layer may be needed for some manipulations of data at the application layer, but perhaps that is almost always avoidable?

Have you ever tried Hasura? I'm thinking of giving it a go in a future project.

Re: Practical SQL for Data Analysis

#39

Post by pandasusr »

higeorge13 wrote: post #33
pandasusr wrote: post #28

Earlier quoted context omitted.

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

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

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

Re: Practical SQL for Data Analysis

#40

Post by mistrial9 »

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

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

Post reply on HN