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

151–160 of 198 posts

Re: Practical SQL for Data Analysis

#151

Post by neurocean »

ok123456 wrote: post #146
default-kramer wrote: post #78

Earlier quoted context omitted.

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

Don't views and materialized views give you reuse? Can't you do composibility with foreign references?

Sure, while views may be fine for small projects, using them for larger projects like data warehousing is usually a mistake you'll come to regret.

SQL views are rarely unit tested so you always end up in a regression nightmare when you need to make updates.

If you're going to go pure SQL, you should use something like dbt.

Re: Practical SQL for Data Analysis

#152

Post by neurocean »

hodgesrm wrote: post #87
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 guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL. This is not huge. This is quite small. Pandas can't handle data that runs into billions of rows or sub-second response on arbitrary queries. Both are common requirements in many analytic applications. I like Pandas. It's flexible and powerful if you have experience with it. But there's…

Yeah but that's not even close to being Pandas' value proposition. It's for the data scientist and analyst, not the db admin, data engineer or production applications.

Re: Practical SQL for Data Analysis

#153

Post by Planktonne »

ziml77 wrote: post #127
nojito wrote: post #106

Earlier quoted context omitted.

Setting Inplace=True is not recommended and should be used with caution https://github.com/pandas-dev/pandas/issues/16529

I've never seen anything about inplace being a poor idea to use. Is that documented anywhere or is that ticket the only info about it?

It's not particularly front-and-centre, but it's all over various discussion boards if you go looking for it directly. I'd like the debate to be more visible, personally, particularly whenever the argument gets deprecated for a particular method.

Essentially, `inplace=True` rarely actually saves memory, and causes problems if you like chaining things together. The people who maintain the library/populate the discussion boards are generally pro-chaining, so `inplace` is slowly and quietly on its way out.

Re: Practical SQL for Data Analysis

#154

Post by tomrod »

punnerud wrote: post #150
tomrod wrote: post #104

Earlier quoted context omitted.

> why isn't someone rolling those tables into something a bit easier with some backend process. You'd identified why we're all going to have jobs in 100 years. Automation sounds great. It's exponentially augmenting to some users in a defined user space. Until you get to someone like me, who looks at the production structure and goes: "this is wholly insufficient for what I need to build, but it has good bones, so I'm…

Tree pillars where you only can choose two to be perfect. Just like databases and CAP theorem

Technology is the easiest to adjust, ironically.

Re: Practical SQL for Data Analysis

#155

Post by tharkun__ »

solaxun wrote: post #84
WhompingWindows wrote: post #53

Earlier quoted context omitted.

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…

I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?

Disclaimer: It's been a while, but my very first job out of uni was dealing with large queries like that for reporting and import/export out of our OLTP database directly.

15 tables would've been not very common for us, but something between 5 and 10 was normal. Many of those tables would've had millions upon millions of rows (while some were simple 'key tables' with only hundreds or a few thousand records)

The one thing that I learned really fast is that yes, "prefiltering/subgrouping" as the OP calls it, is very important. If you cut down on the number of rows that your query "starts out with" was very very important, as it cuts down on the amount of data that needs to be dealt with in the rest of the query. This was for an old Sybase ASE based system. IIRC, ASE would only be able to automatically optimize this across 4 clauses (might misremember the number and I left before they upgraded to the new newer version with a better optimizer), so ordering of your join clauses was important. If the filtering that cut down on the amount of data needed from other tables came first, your query would run way faster, than if the filters were way down with the rest of the joins.

Just think about it, if you start out with getting 6 million patient records and then start collecting 6 million records from the next table for a join and so on and so forth, that's way more data that needs to be read and churned through than if you can 'start on the other end' so to speak, whittle it down to say 50000 records that now need to be looked up in the patient table.

Re: Practical SQL for Data Analysis

#156

Post by ojosilva »

I'm not a Pandas dev or a Pythonista for that matter but I'm pretty sure Pandas Dataframe should be able to handle SQL cursors or materializing data JIT for the aggregation. Getting all rows in with a fetchall is an antipattern and just completely impractical for a lot of use cases! Didn't Pandas have a SQL Alchemy integration already? Although I'm not sure it would run the aggregation progressively or again with fetchall internally...

Re: Practical SQL for Data Analysis

#157

Post by derefr »

jnsie wrote: post #116
protomyth wrote: post #85

Earlier quoted context omitted.

I don't disagree with writing solid SQL. I would go so far as to say some things (most) need to be in stored procedures that are reviewed by competent people. But, some folks don't think about usage sometimes. This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have som…

At its simplest its OLTP vs OLAP. Separate the data entry/transactional side of things from the reporting part. Make it efficient for data analysts do do their jobs.

But sometimes, your line-of-business application does analytics queries. Which means you need your app developers to understand how to do OLAP, and you also need a schema design that can run arbtrary OLAP queries within a few orders of magnitude of OLTP speeds (e.g. <10s.)

Re: Practical SQL for Data Analysis

#158

Post by [deleted] »

ojosilva wrote: post #156

I'm not a Pandas dev or a Pythonista for that matter but I'm pretty sure Pandas Dataframe should be able to handle SQL cursors or materializing data JIT for the aggregation. Getting all rows in with a fetchall is an antipattern and just completely impractical for a lot of use cases! Didn't Pandas have a SQL Alchemy integration already? Although I'm not sure it would run the aggregation progressively or again with fet…

[deleted]

Re: Practical SQL for Data Analysis

#159

Post by closed »

EForEndeavour wrote: post #68
disgruntledphd2 wrote: post #60

Earlier quoted context omitted.

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?

In case you're interested in what's missing--I maintain a port of dplyr from R to python called siuba, and gave a talk recently on why pandas might be hard to use:

https://www.rstudio.com/resources/rstudioglobal-2021/bringin...

Re: Practical SQL for Data Analysis

#160

Post by mr_toad »

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…

> There are some SQL dialects which provide pivot functions like in pandas, but they are not universal.

And you need to define the columns in advance anyway, because query planners can’t handle columns that change at runtime.

Post reply on HN