Summarizing Data in SQL | Hacker News Reader

Summarizing Data in SQL

blog.yhat.com

36 pointselisebreda9 comments

> Percentile

> In MySQL, we can use local variables to keep track of the order (...) Here's the Postgres version.

I would be interested in the MySQL percentile snip.

Nice! Does anybody know a project that combines this kind of techniques in a program that could be ran across billions of rows, most preferably something that scales to multiple nodes?

MPPs do this. Azure Data Warehouse, Amazon Redshift, Google BigQuery.

Doesn't have to be a DB-as-a-service though - Oracle and Greenplum are MPP, for example.

Shameless plug:

https://cloud.google.com/blog/big-data/2016/01/anatomy-of-a-...

Heh, I'm gonna use this opportunity to make my case then. Why is Google Analytics data export available only to premium customers? I'm sure it's a compelling feature to nudge some clients to upgrade, but allowing everybody to export GA data to BigQuery would create quite a lot more revenue. BQ consumption, VMs for R/Python/RapidMiner/Knime, databases, importing data from other sources, which in turns means even higher BQ consumption, people moving ERPs and other systems to be closer. I actually think that it will increase the demand for GA premium too since it will make companies feed an ever increasing volume of data to GA, requiring custom dimensions and more event volume. GA is very useful, consolidating multiple sources of marketing data. However this data is practically locked beyond retrieval. Hell, even charge for exports, everybody will pounce on the opportunity. The current workflows are very frustrating, requiring you to have multiple tools, locking your data in silos.

Every database I could think of at the moment. Even SQL-Lite is able to handle billions of rows.

Probably some xargs sed awk hacks are also sufficient for results in milliseconds.

You start getting problems when dealing with >128 TB databases like the legacy crm databases at Bayer and Siemens e.g.

I think ">128TB" is stretching the upper-limit for single-node database. When you're dealing with > 10TB in a single node, it's probably wise to 'shard' or partition the data over more than one node (think backups/archiving, query performance,...).

To answer the initial question - take a look at CitusDB that runs over PostgreSQL. They seem to be doing some cool work in real-time analytics/scalability: https://www.citusdata.com/

Cool article, but please update code snippets to some consistent codestyle :)

Reply on news.ycombinator.com