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

191–198 of 198 posts

Re: Practical SQL for Data Analysis

#191

Post by tomnipotent »

mistrial9 wrote: post #147
tomnipotent wrote: post #136

Earlier quoted context omitted.

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

> companies led by Google invented and deployed datasets previously impossible e.g. BigTable

BigTable, Redshift, Snowflake and every other "big data" storage system rely heavily on partitioning to achieve the scales they're able to achieve. Some of these systems don't even support indexes.

Re: Practical SQL for Data Analysis

#192

Post by tomnipotent »

crazygringo wrote: post #142
tomnipotent wrote: post #133

Earlier quoted context omitted.

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

Streaming aggregation relies on knowing the problem beforehand, so it's pointless in the context of the day-to-day work of analysts where most of the project is spent figuring out the right questions to ask.

If I need to stream data because the size of data is a constraint, I'm either working on the 0.1% of projects that require it or on an analytical product rather than an analytical project.

Re: Practical SQL for Data Analysis

#193

Post by mixmastamyk »

arcticfox wrote: post #88
mixmastamyk wrote: post #38

Earlier quoted context omitted.

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.

As a Hasura user, I highly recommend it for OLTP workloads. And for the specific subject at hand (memory usage) it is fantastic. Its (in-database) serialization speed just completely runs circles around anything we could do before in Ruby.

Sounds correct to me. Although I've heard Ruby is slow, I suspect json serialization is done in C? At least it is in Python which I'm more familiar with. Simple cutting out the extra layers is where the big savings likely are.

Re: Practical SQL for Data Analysis

#194

Post by sim_singh »

ivalm wrote: post #175
sagarm wrote: post #122

Earlier quoted context omitted.

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.

Hi, I saw your comment on the wechat page, I was wondering if you have the account and can verify me?

Re: Practical SQL for Data Analysis

#195

Post by tomrod »

nojito wrote: post #183
tomrod wrote: post #105

Earlier quoted context omitted.

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.

Absolutely. On top of that, the Pandas object type (most people's default for strings) can house any scalar type, including ints and floats. This has hit a LOT of immature adoption of pyspark, for example, where the datatypes get screwy.

Re: Practical SQL for Data Analysis

#196

Post by dr_kiszonka »

jplr8922 wrote: post #185
timita wrote: post #162

Earlier quoted context omitted.

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

I usually use Python and R for analysis. However, when dealing with larger datasets, e.g., 0.5 - 2 PB, I have to rely on SQL/BigQuery because I can't get Python and R to deal such workloads in reasonable time. I tried Dask, but I couldn't resolve a few bugs it had at the time.

If you were to find outliers in a 1 PB table, what tools would you use?

Re: Practical SQL for Data Analysis

#197

Post by sagarm »

ivalm wrote: post #175
sagarm wrote: post #122

Earlier quoted context omitted.

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.

Ah yes that does sound painful. I'd generally recommend that data be denormalized in the warehouse so the joins don't need to be done at query time. That can make the queries more complex, however.

Re: Practical SQL for Data Analysis

#198

Post by lysecret »

Hmm one thing that people forget about when it comes to Pandas vs SQL is that for the kind of data you would use in pandas, 1 day and sometimes even 1 week or longer doesn't make a big difference. So say you are building some complicated data processing pipeline and you base it on a bunch of SQL queries. You are hitting the Database every single time.

While if you just get the tables and to most (or at least some) of the merging and processing in pandas you can do the loading of the data in the middle of the night and that is the only time you are hitting the db.

I have personally experienced big analytical SQL queries hitting the db and busy times...

Post reply on HN