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

141–150 of 198 posts

Re: Practical SQL for Data Analysis

#141

Post by 1980phipsi »

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 -> government -> bloat

Re: Practical SQL for Data Analysis

#142

Post by crazygringo »

tomnipotent wrote: post #133
crazygringo wrote: post #74

Earlier quoted context omitted.

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

> But when most people think SQL and database We're not talking about most people, but data analysts. If we're just doing simple sum/count/average aggregated by a column or two with some basic predicates, SQL and an RDBMS are your best friend. Always better to keep compute + storage as close together as possible. > Traditional database client API's I'm not sure what point you're trying to make with this. Most data an…

I'm not talking about streaming live data, I'm talking about streaming query results if you re-read my comment.

If you're reading many MB's or GB's of data from a database, it's a lot more performant to stream it from the database directly into your local data structure, rather than rely on default processing of query results as a single chunk which will be a lot worse for memory and speed.

Re: Practical SQL for Data Analysis

#143

Post by shkkmo »

dragonwriter wrote: post #135
shkkmo wrote: post #80

Earlier quoted context omitted.

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.

> The point was about doing more work using the SQL DB engine The specific reference was to “psql script”, which runs in the psql client program, not the DB engine. Since I suspected that might be an error intending to reference SQL running on the server, I separately addressed, in my initial response, both what was literally said (psql script) and sql running in the DB.

Any psql script would be building and running SQL queries so I fail to see how that has any impact of the point being made.

Edit: The argument is that there are operations that should be done in the SQL layer and it is worth time learning enough about that layer to understand when and how to use it for computation. Once you learn that, it isn't really relevant if you are using psql or some other client library to build those queries.

Re: Practical SQL for Data Analysis

#144

Post by twobitshifter »

I now do most of my data analysis in Julia instead of pandas, but used pandas for a long time. SQL is a valuable skill and useful when you need to optimize a query for performance, which can sometimes happen with pandas and is much less likely with Julia.

However, even if a pandas query takes 5X longer to run than a SQL query you must consider the efficiency to a developer. You can chain pandas commands together that will accomplish something that takes 10x the lines of SQL. With SQL you’re more likely to end up with many intermediate CTEs along the way. So while you can definitely save processor cycles by using SQL, I don’t think you’ll save clock-face time by using it in most one off tasks. Datascience is usually column oriented and Julia and pandas allow you to stay in that world.

Re: Practical SQL for Data Analysis

#145

Post by kbelder »

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 iterative and frequently will get torn down, rebuilt, and joined to other sources, as they discover new aspects to the data. They need their hands on the raw data, not on a specifically summarized version of it.

Re: Practical SQL for Data Analysis

#146

Post by ok123456 »

default-kramer wrote: post #78
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.

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

Re: Practical SQL for Data Analysis

#147

Post by mistrial9 »

tomnipotent wrote: post #136
protomyth wrote: post #89

Earlier quoted context omitted.

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.

Data analysis workloads more often run into problems solved by partitioning rather than indexes.

yes and no - I trained on "larger than RAM datasets" intentionally, and subsequently took on projects that require "larger than RAM datasets". Two things happened on my way, companies led by Google invented and deployed datasets previously impossible e.g. BigTable, and secondly the equipment I worked on went from modest to mid-sized RAM (hard to define that). Granted that lots of very useful (and common?) tasks are not "larger than RAM datasets", and then have very different characteristics.. which is starting to sound like "Excel problems look like this, they always fit in RAM, they need partitions not indexes" and the like..

There is a blind-man-and-the-Elephant drift here, which is not terrible, but might need calling out to improve..

Re: Practical SQL for Data Analysis

#148

Post by hodgesrm »

nerdponx wrote: post #137
hodgesrm wrote: post #87

Earlier quoted context omitted.

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

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. You're right. It's "huge" with respect to what you can expect to load into Pandas and get instant results from. But it's not even "medium" data on the small-medium-big spectrum. I like Pandas. It's flexible and powerful…

OTOH Pandas/Numpy are great for results. One pattern that I'm hoping to try is running heavy lifting in SQL, transfer results via Apache Arrow, and manipulate the result set in Python with aforesaid libraries.

I don't know enough about how Arrow handles streaming to understand how this would really work but it would break away from the single-threaded connectivity with expensive ser/deser databases have used since the days of Sybase Db-Library, the predecessor to ODBC. 36 years is probably long enough for that model.

Re: Practical SQL for Data Analysis

#149

Post by nomel »

twobitshifter wrote: post #144

I now do most of my data analysis in Julia instead of pandas, but used pandas for a long time. SQL is a valuable skill and useful when you need to optimize a query for performance, which can sometimes happen with pandas and is much less likely with Julia. However, even if a pandas query takes 5X longer to run than a SQL query you must consider the efficiency to a developer. You can chain pandas commands together that…

For me, the main use case of a database server is so I can do operations that require more memory than I have on my local computer. Reading through this comment section makes me think my use case is rare or something. Maybe everyone is ok with chunked operations?

Re: Practical SQL for Data Analysis

#150

Post by punnerud »

tomrod wrote: post #104
protomyth wrote: post #86

Earlier quoted context omitted.

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.

> 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
Post reply on HN