5 ms·

We're trialling BigQuery at the moment, pushed about 130TB into it last week (about ~300 billion rows) and have been blown away by the performance of it. It's

We're trialling BigQuery at the moment, pushed about 130TB into it last week (about ~300 billion rows) and have been blown away by the performance of it. It's a bit of a shame they never released a paper on how it works, well, nothing since Dremel anyway. And Collosus isn't public either. I can already see this in built ML stuff being useful for trialling models, especially as it's built right into the SQL.

puzzle 8y ago
There is information about Colossus: the original GFS paper, an interview with Sean Quinlan with McKusick on Colossus improvements, as well as a talk by Denis Serenyi on more of the gory details, e.g. rebalancing. There was also a public talk on D, the GFS chunkserver replacement, but there don't seem to be recordings or slides from that.
kyrra 8y ago
There have been some blog posts about it, but you are right, no whitepapers. https://cloud.google.com/blog/big-data/2016/04/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/big-data/2016/04/inside-capaci... https://cloud.google.com/blog/big-data/2016/01/bigquery-under-the-hood https://cloud.google.com/blog/big-data/2016/01/bigquery-unde...
minimaxir 8y ago
At the Reddit talk at Google Next, the VP of Engineering noted that they moved to BigQuery and saved in both performance and cost. We at BuzzFeed have seen similar results for migrating to BigQuery (and we did a talk about it at Next too, although it’s not online yet)
- minimaxir 8y ago
  Reddit talk: https://youtu.be/tKISLQ87GO8 https://youtu.be/tKISLQ87GO8 BuzzFeed talk: https://youtu.be/74PKZmL-Qjs https://youtu.be/74PKZmL-Qjs
  - fhoffa 8y ago
    I really enjoyed this slide from the BuzzFeed talk "Hundreds of terabytes across 300+ tables, 100K+ queries a day" on BigQuery. I quoted the reddit talk on the thread above/below :).
    - gipp 8y ago
      Having recently moved to data science at another big gcp early adopter, BQ was a complete game changer having come from redshift and hive
antonvs 8y ago
How do you find the querying costs? It seems like for many use cases, you'd have to pay quite a bit of attention to partitioning the database in a way that minimizes query costs.
- minimaxir 8y ago
  The relatively new date-partitioning feature for tables has cut down query costs/time significantly.
  - fhoffa 8y ago
    And with the just announced clustering now you get even better costs and performance. Disclosure: I'm https://twitter.com/felipehoffa https://twitter.com/felipehoffa and I work for Google Cloud. And I'm really excited to reprocess all public tables into clustered ones.
- gipp 8y ago
  Cost (or rather bytes scanned) is sent back from the API on any queries or dry run/validation calls. There's a common chrome extension to put it in the legacy ui, and I believe the beta ui has it baked in (as part of validation)
- djhworld 8y ago
  For this trial we are transferring some data from AWS, which is already partitioned in S3 by quite a few dimensions, including DAY. As BQ only supports DAY partitioning, I had to create multiple tables that represent the other dimensions, but it seems to work OK, if a little clunky when you have to use wildcards/_TABLE_SUFFIX
vgt 8y ago
Bq pm here. No papers, yes, but lots of talks and blog posts. Here's a list: https://medium.com/@thetinot/bigquery-required-reading-list-71945444477b https://medium.com/@thetinot/bigquery-required-reading-list-... Happy to answer any questions!
- polskibus 8y ago
  How would you compare it to a traditional OLAP solutions (MS SQL Analysis Services in OLAP model (not tabular), Essbase etc.) in terms of : - multidimensional / hierarchy modeling for analytical purposes - permissioning model (roles with element-level granularity, ie. User A - allow Country=USA, User B - allow Product=Bike) - historical modeling (built-in slowly-changing dimensions support) What is the underlying storage model? Is it a column store ? Is it closer to traditional row-based stores? Are you taking advantage of GPUs or other dedicated hardware to accelerate BQ?
  - kornish 8y ago
    If you haven't read it already, I strongly suggest reading the original Dremel paper [0]. It's no doubt somewhat out of date, but I believe BQ is based in Dremel. tl;dr for underlying storage model: distributed column store which pushes computation down a tree to leaf nodes to parallelize disk I/O. Parent nodes aggregate computations before returning to the client. [0]: https://ai.google/research/pubs/pub36632 https://ai.google/research/pubs/pub36632
    - vgt 8y ago
      This video gives a walkthrough of the new version of Dremel that's been running in production for past 3 years (no paper sorry!): https://www.youtube.com/watch?v=UueWySREWvk https://www.youtube.com/watch?v=UueWySREWvk
- nrjames 8y ago
  Did you all work together with the Firebase team on the recent schema change? We're scrambling to mitigate the ~2x cost increase that caused for us, since it blew our analytics budget out of the water.
ceocoder 8y ago
We (Sojern) have been using BigQuery since late 2015, we moved from Hive. It has been an amazing experience in terms of cost savings (managed infra, operational cost of a hadoop cluster), and performance. We use it across the org from analytics, ingestion, reporting, ML, and many more uses.

puzzle 8y ago

There is information about Colossus: the original GFS paper, an interview with Sean Quinlan with McKusick on Colossus improvements, as well as a talk by Denis Serenyi on more of the gory details, e.g. rebalancing. There was also a public talk on D, the GFS chunkserver replacement, but there don't seem to be recordings or slides from that.

kyrra 8y ago

There have been some blog posts about it, but you are right, no whitepapers. https://cloud.google.com/blog/big-data/2016/04/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/big-data/2016/04/inside-capaci... https://cloud.google.com/blog/big-data/2016/01/bigquery-under-the-hood https://cloud.google.com/blog/big-data/2016/01/bigquery-unde...

minimaxir 8y ago

At the Reddit talk at Google Next, the VP of Engineering noted that they moved to BigQuery and saved in both performance and cost. We at BuzzFeed have seen similar results for migrating to BigQuery (and we did a talk about it at Next too, although it’s not online yet)

minimaxir 8y ago
Reddit talk: https://youtu.be/tKISLQ87GO8 https://youtu.be/tKISLQ87GO8 BuzzFeed talk: https://youtu.be/74PKZmL-Qjs https://youtu.be/74PKZmL-Qjs
- fhoffa 8y ago
  I really enjoyed this slide from the BuzzFeed talk "Hundreds of terabytes across 300+ tables, 100K+ queries a day" on BigQuery. I quoted the reddit talk on the thread above/below :).
  - gipp 8y ago
    Having recently moved to data science at another big gcp early adopter, BQ was a complete game changer having come from redshift and hive

antonvs 8y ago

How do you find the querying costs? It seems like for many use cases, you'd have to pay quite a bit of attention to partitioning the database in a way that minimizes query costs.

minimaxir 8y ago
The relatively new date-partitioning feature for tables has cut down query costs/time significantly.
- fhoffa 8y ago
  And with the just announced clustering now you get even better costs and performance. Disclosure: I'm https://twitter.com/felipehoffa https://twitter.com/felipehoffa and I work for Google Cloud. And I'm really excited to reprocess all public tables into clustered ones.
gipp 8y ago
Cost (or rather bytes scanned) is sent back from the API on any queries or dry run/validation calls. There's a common chrome extension to put it in the legacy ui, and I believe the beta ui has it baked in (as part of validation)
djhworld 8y ago
For this trial we are transferring some data from AWS, which is already partitioned in S3 by quite a few dimensions, including DAY. As BQ only supports DAY partitioning, I had to create multiple tables that represent the other dimensions, but it seems to work OK, if a little clunky when you have to use wildcards/_TABLE_SUFFIX

vgt 8y ago

Bq pm here. No papers, yes, but lots of talks and blog posts. Here's a list: https://medium.com/@thetinot/bigquery-required-reading-list-71945444477b https://medium.com/@thetinot/bigquery-required-reading-list-... Happy to answer any questions!

polskibus 8y ago
How would you compare it to a traditional OLAP solutions (MS SQL Analysis Services in OLAP model (not tabular), Essbase etc.) in terms of : - multidimensional / hierarchy modeling for analytical purposes - permissioning model (roles with element-level granularity, ie. User A - allow Country=USA, User B - allow Product=Bike) - historical modeling (built-in slowly-changing dimensions support) What is the underlying storage model? Is it a column store ? Is it closer to traditional row-based stores? Are you taking advantage of GPUs or other dedicated hardware to accelerate BQ?
- kornish 8y ago
  If you haven't read it already, I strongly suggest reading the original Dremel paper [0]. It's no doubt somewhat out of date, but I believe BQ is based in Dremel. tl;dr for underlying storage model: distributed column store which pushes computation down a tree to leaf nodes to parallelize disk I/O. Parent nodes aggregate computations before returning to the client. [0]: https://ai.google/research/pubs/pub36632 https://ai.google/research/pubs/pub36632
  - vgt 8y ago
    This video gives a walkthrough of the new version of Dremel that's been running in production for past 3 years (no paper sorry!): https://www.youtube.com/watch?v=UueWySREWvk https://www.youtube.com/watch?v=UueWySREWvk
nrjames 8y ago
Did you all work together with the Firebase team on the recent schema change? We're scrambling to mitigate the ~2x cost increase that caused for us, since it blew our analytics budget out of the water.

ceocoder 8y ago

We (Sojern) have been using BigQuery since late 2015, we moved from Hive. It has been an amazing experience in terms of cost savings (managed infra, operational cost of a hadoop cluster), and performance. We use it across the org from analytics, ingestion, reporting, ML, and many more uses.