5 ms·

Summarizing Data in SQL

36 points by elisebreda 11y ago

d33 11y ago
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?
- TheLogothete 11y ago
  MPPs do this. Azure Data Warehouse, Amazon Redshift, Google BigQuery.
  - jmsmistral 11y ago
    Doesn't have to be a DB-as-a-service though - Oracle and Greenplum are MPP, for example.
  - vgt 11y ago
    Shameless plug: https://cloud.google.com/blog/big-data/2016/01/anatomy-of-a-bigquery-query https://cloud.google.com/blog/big-data/2016/01/anatomy-of-a-...
    - TheLogothete 11y ago
      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.
- evook 11y ago
  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.
  - jmsmistral 11y ago
    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/ https://www.citusdata.com/
frik 11y ago
> 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.
alfanick 11y ago
Cool article, but please update code snippets to some consistent codestyle :)

36 points by elisebreda 11y ago

d33 11y ago

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?

TheLogothete 11y ago
MPPs do this. Azure Data Warehouse, Amazon Redshift, Google BigQuery.
- jmsmistral 11y ago
  Doesn't have to be a DB-as-a-service though - Oracle and Greenplum are MPP, for example.
- vgt 11y ago
  Shameless plug: https://cloud.google.com/blog/big-data/2016/01/anatomy-of-a-bigquery-query https://cloud.google.com/blog/big-data/2016/01/anatomy-of-a-...
  - TheLogothete 11y ago
    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.
evook 11y ago
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.
- jmsmistral 11y ago
  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/ https://www.citusdata.com/

frik 11y ago

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

alfanick 11y ago

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