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

181–190 of 198 posts

Re: Practical SQL for Data Analysis

#181

Post by disgruntledphd2 »

EForEndeavour wrote: post #102
disgruntledphd2 wrote: post #63

Earlier quoted context omitted.

> (i.e. not relying on data engineers to do basic ETL for you). Is this actually a thing? Surely it can't be a thing.

In a sufficiently large org, why not?

My concern is not that DE's do ETL, just that a data scientist could actually avoid learning how to do this.

Certainly in all the successful orgs I've worked in, this would rarely be the case. Maybe it works if you have really well-standardized and static data, but I rarely work in this kind of environment.

Re: Practical SQL for Data Analysis

#182

Post by disgruntledphd2 »

CRConrad wrote: post #178
disgruntledphd2 wrote: post #56

Earlier quoted context omitted.

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), a…

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

Re: Practical SQL for Data Analysis

#183

Post by nojito »

tomrod wrote: post #105
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-…

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

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.

Re: Practical SQL for Data Analysis

#184

Post by jplr8922 »

kbelder wrote: post #145
jplr8922 wrote: post #67

tldr ; if you hear ''but we can do this in SQL'', RUN!!! My eyes hurt as I read this article. There are reasons why analysts dont use SQL to do their job, and it has nothing to do with saving RAM and memory. 1) Data analysis is not a linear process, it involve playing and manipulating the data in different way and letting your mind drift a bit. You want your project in an IDE made for that purpose, the ability to cre…

Familiar. I think the biggest issue is understanding workflow. My hunch is that some developers view a data analysis project as a program... it needs to have these data processing steps performed, summarized in this certain way, and saved off in that certain format. Once those queries are defined, the program is ran and the analysis done. The actual work the analyst is doing is far more ad-hoc than that. It's iterati…

Yup. That is exactly it. You cannot believe the number of programmer I've met who thinks that ''data science is easy'' because ''I can code a neural net too''.

Data analysis is a soft skill that you usually learn by getting your hands dirty in a graduate or professional environment. Its not something you can put on a CV like like ''X years of experience with Y programming language''.

Re: Practical SQL for Data Analysis

#185

Post by jplr8922 »

timita wrote: post #162
jplr8922 wrote: post #132

Earlier quoted context omitted.

What do you mean by exploratory analysis? Lets assume that my dataset contains features which are not normally distributed, and I want to check for coskewness and cokurtosis matrices before and after I remove outliers with a method specific to my field of research, found in a academic paper. Am I supposed to code all of this in SQL? What is your definition of exporatory analysis, and which metrics do you have in mind…

> Am I supposed to use... excel to do that? This is gratuitous. You have a clear bias, granted, because it seems your domain is so specific, only a procedural language will do. But it seems you are unfamiliar with modern SQL tools. Some of the obvious ones that come to mind: Metabase[0] for visualisation or Apache MADlib[1] for in-database statistics and machine learning. [0] https://github.com/metabase/metabase [1]…

I humbly disagree. Its not that ''my domain is so specific'', but that data analysis in itself is a discovery process which is not straightforward. If you want to explore the jungle, you need a machete.

As for Metabase of MADlib, you are right ; I was not aware of these new tools. They look great, and I'm certain they can help a lot of people. However you assume that they are available! Not all IT departments are open to the idea of buying new software, and if the suggestion comes from an outsider it will be perceived as an insult (been there, done that many many time). And when they refuse, now what? You go back to the usual procedural languages (R, Python, Julia, etc) which are free and don't require the perpetual oversight of some DBA who thinks that all you need is an AVG(X) and GROUP BY since kurtosis is domain specific anyway.

I've meet some Excel-VBA users who couldn't care less about pro devs or decorators since ''they can already do everything by themselves''. Same thing with the SQL only, Python only, Tableau only or wathever-only crowd.

Re: Practical SQL for Data Analysis

#186

Post by thinkharderdev »

steve-chavez wrote: post #126
thinkharderdev wrote: post #121

Earlier quoted context omitted.

It's not turing complete which is I think why it doesn't feel like a "real" programming language.

SQL is turing complete. A demonstration with recursive CTEs is done here[1]. [1]: https://wiki.postgresql.org/wiki/Cyclic_Tag_System

Oh wow. I stand corrected. Thanks!

Re: Practical SQL for Data Analysis

#187

Post by WhompingWindows »

fifilura wrote: post #180
ivalm wrote: post #174

Earlier quoted context omitted.

VA is a massive org, this is not a particularly large number of analysts for a healthcare system of this size (or honestly any complex org).

Yes, but the answer that it is a massive organization does not satisfy my curiosity regarding what they are actually doing. I come from a small country, in total comparable to the 6 million veterans mentioned here. 1400 analysts is just a lot and I wouldn't imagine for example our national healthcare service could ever employ that amount of analysts? Hm, or would they? It would make a pretty big dent in the total amo…

6 million Veterans are just the subgroup from one of the studies I did. In reality, the VA system serves 15-20 million patients, given there are 17.4 million Vets, some who use private health care, and some whose families also use VA.

The reason there are 1400 analysts: research studies each require one or two analysts. At this very moment, there are thousands of research studies taking place in the US medical system. Without these number of analysts, you'd have to completely revamp the system, killing all current projects, all current code, and creating a HUGE HUGE headache for everyone, not to mention laying off 1000+ through a system which it is NOT easy to layoff individuals through.

As a matter of fact, they want to transition to a new data infrastructure at the VA, but it's been delayed many times and the logistics have been very vague.

Re: Practical SQL for Data Analysis

#188

Post by tarsinge »

jstrong wrote: post #79
tarsinge wrote: post #75

Earlier quoted context omitted.

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

Re: Practical SQL for Data Analysis

#189

Post by omarhaneef »

WhompingWindows wrote: post #187
fifilura wrote: post #180

Earlier quoted context omitted.

Yes, but the answer that it is a massive organization does not satisfy my curiosity regarding what they are actually doing. I come from a small country, in total comparable to the 6 million veterans mentioned here. 1400 analysts is just a lot and I wouldn't imagine for example our national healthcare service could ever employ that amount of analysts? Hm, or would they? It would make a pretty big dent in the total amo…

6 million Veterans are just the subgroup from one of the studies I did. In reality, the VA system serves 15-20 million patients, given there are 17.4 million Vets, some who use private health care, and some whose families also use VA. The reason there are 1400 analysts: research studies each require one or two analysts. At this very moment, there are thousands of research studies taking place in the US medical system…

Is it even feasible to revamp the system efficiently?

We really should commission 2 more analysts to figure this out...

Re: Practical SQL for Data Analysis

#190

Post by EForEndeavour »

closed wrote: post #159
EForEndeavour wrote: post #68

Earlier quoted context omitted.

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

This is super cool, thank you!

Wait... I had no idea dplyr's database support was this good. https://db.rstudio.com/dplyr/

Post reply on HN