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

171–180 of 198 posts

Re: Practical SQL for Data Analysis

#171

Post by darksaints »

andrewmcwatters wrote: post #118
spamalot159 wrote: post #9

I think for a lot of people, SQL is a skill that doesn't stick. You learn enough to do the queries you need for your project, they work then you forget about them as you work on the rest of your project. These skills are perishable. Left outer join? Yeah, I knew what that was some time ago, but not anymore The days of dedicated SQL programers are mostly gone.

Exactly. I own a software consultancy, and I tried explaining this to an engineering manager and he just didn't get it. For some reason, he couldn't understand that when you write SQL queries for a project, you typically do it once, and basically never again, with the exception of maybe adding or removing columns from the query. The "hard work," if you can call it that, is all in the joins. Then, I completely forget…

The engineering manager was right. SQL is the most pervasive programming language in existence. It doesn't get that way because nobody uses it. Maybe you don't use it much, but unless your consultancy specializes in some tiny niche where SQL isn't actually needed, you might want to rethink that. I've seen far too many projects where developers looked down on SQL as some sort of not-so-necessary evil, and they've all ended up reinventing it. Extremely poorly.

Re: Practical SQL for Data Analysis

#174

Post by ivalm »

fifilura wrote: post #117
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 can't help thinking that with better processes and tools those 1400 analysts could instead be 50 analysts? (or even 5?) And that building an aggregation ETL pipeline, maybe inspired by this post, could be the solution?

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

Re: Practical SQL for Data Analysis

#175

Post by ivalm »

sagarm wrote: post #122
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…

How many TBs of data are we really talking here, if it's just 6M rows? Surely this processing could easily be done on a single machine.

I work for a large healthcare org and from personal experience these things can get large. 6m patients is prob 20m encounters, each encounter may have 10 different kinds of meta data (each one it’s own table) and each kind has 10-100 rows. So really you are doing joins between 10 tables each with ~1-10 billion rows. It actually does slow down even running on expensive hardware.

Re: Practical SQL for Data Analysis

#176

Post by afiodorov »

I have been working with data for more than 5 years now and my 2 cents is that Pandas shines with exploratory analysis. Because you have the data in memory it's easy to empirically arrive at the exact data transformation you need and figure out whether your analysis is worth pursuing further. At this stage of the development your ability to iterate and experiment quickly is most valuable. I see no value in worrying about the deployment of the work you are doing if you haven't got any results just yet.

Now when the analysis is done and there's a conclusion about putting some of it in production, I am yet to see a single company that relies on pandas to do data transforms "online", i.e. on the live production data stream. Typically in my experience I would end up rewriting my data transforms in either a compiled language or SQL - i.e. something much more performant and maintainable.

As a result as you progress in your career you become very proficient in both pandas and SQL - and you also start glancing at the source code of some of the transforms performed by pandas when you need to port them to the language of choice of the company you're working for.

Re: Practical SQL for Data Analysis

#177

Post by CRConrad »

protomyth wrote: post #86
solaxun wrote: post #84

Earlier quoted context omitted.

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?

Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.

May I introduce: Protomyth, meet DW and ETL.

Re: Practical SQL for Data Analysis

#178

Post by CRConrad »

disgruntledphd2 wrote: post #56
qsort wrote: post #51

Earlier quoted context omitted.

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

Re: Practical SQL for Data Analysis

#179

Post by CRConrad »

protomyth wrote: post #89
mistrial9 wrote: post #40

Earlier quoted context omitted.

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

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.

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

Re: Practical SQL for Data Analysis

#180

Post by fifilura »

ivalm wrote: post #174
fifilura wrote: post #117

Earlier quoted context omitted.

I can't help thinking that with better processes and tools those 1400 analysts could instead be 50 analysts? (or even 5?) And that building an aggregation ETL pipeline, maybe inspired by this post, could be the solution?

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 amount of persons in that field in our country.

Post reply on HN