4 ms·

BigQuery charges you for the storage so it's not just the queries. It also charges for the streaming inserts which is also not that cheap for large volumes of d

BigQuery charges you for the storage so it's not just the queries. It also charges for the streaming inserts which is also not that cheap for large volumes of data. I might be biased since I'm running an analytics company and this is my core business but here's our alternative: The query and storage layer should be separated because often the expensive one the is the query layer. We have API servers which ingest the data from SDKs, enrich/sanitize and send it to a commit log such as Kinesis and Kafka. One API server is capable of handling 10k r/q. We have the Kinesis/Kafka consumers that consume the data in micro-batches, convert them to ORC files, store it on S3 and index the metadata in a single Mysql server. The throughput is around 15k r/q. S3 or Cloud Storage is cheap, reliable and can be used for many other use-cases as well but you need high-memory nodes for the query layer if you're dealing large volumes of data. We have a Presto cluster which spins up when you need to run ad-hoc queries, allows you to pre-materialize the data so that you can power your dashboards. If you're running expensive queries on raw data, you can spin up 10 Presto worker, execute the queries and then just shut down them when you don't need. That way, we're able to access all the historical data, no extra cost for the data ingestion and storage and it's even better than serverless since it will be much cheaper. The system can be automatic based on the hour of the day (during your analysts working hours) the CPU and memory load so unfortunately "BigQuery's on-demand model" is not a killing feature anymore.

vgt 9y ago
Thanks, can we continue this discussion? Super interesting to me. One clarification. BigQuery has two methods of ingest. The Streaming API you mention does carry additional cost, but for the added benefit of having your data appear in BigQuery in real-time. Batch load, however, is entirely free, as in - it doesn't use your "query capacity", and we don't charge for it. I am rather certain this is a very compelling offering. Batch loads encode your data, replicate it, secure it, convert it into our format, and fix any issues your files may have, like optimal file sizes, optimal number of files, optimal file groupings for your queries - lots of subtle things that either slow you down or cause you headaches otherwise. We also manage all the metadata for you (your MySQL instance). We also burn a good chunk of resources post-load re-materializing and optimizing your dataset. We also maintain upgrades, downtime, and so on. It sounds like your use case is micro-batch, so perhaps you could benefit from our free batch loads? It also sounds like you don't mind the extra operational overhead, and would rather operate your own stack, run your own upgrades, fix your own file issues, and so on. This is a personal preference, I agree. Customers who prefer our model love the ease of use and would rather focus their energy elsewhere. It is a stated goal of BigQuery to abstract away complexity and make BigQuery as easy to use as possible for everyone, and if we're failing somewhere, we certainly want to know :) Finally, if I had to equate BigQuery's on-demand pricing, it gives you the ability to go from 0 cores to thousands and back to 0 in sub-second intervals, scoped down to individual query size. This is exactly what's happening under the hood, but all that is abstracted away behind a "run query" button.
- buremba 9y ago
  Our system is also near-realtime. The default batch duration is 3 minutes, it's also configurable but it will also affect the background processes (shard compaction, data organization jobs etc.) so our customers usually prefer 5 minutes which is also near real-time for 10B events per month. Please note that this is our core business. Of course, we maintain these services but we also try hard to push more work to cloud providers. For example, both AWS and GC offers managed Mysql servers, auto scale groups for nodes, object stores such as S3 and Cloud Storage so we actually maintain the software and help the customers to upgrade the software when they need. I agree that BigQuery will save time if the users are not familiar with distributed systems and big-data but again, "ability to go from 0 cores to thousands" doesn't make sense to me because in practice I have never experienced such case. Since we need to start the instances it may take up to 2 - 3 minutes but this is often acceptable for data analysts. Could you please elaborate the part "re-materializing and optimizing your dataset."? We do a number of optimizations for compacting ORC files, bucketing etc. but I would love to hear how BigQuery does post-processing. As a note, I usually tend to simplify things but most of the BigQuery customers that I see usually do overengineering because of the cost optimization. For example in the article the author uses Redshift for the dashboard data the solution they use for moving data from BigQuery to Redshift also needs to be maintained and it's not that easy. If I'm going to adopt my whole system to the way how BigQuery works and push hard to save costs, then I expect it to be pretty cheap but 40K for reserved slots doesn't sound like cheap to me. We maintain similar size clusters for 20% of this price for the same data volume and it's much more flexible.
  - manigandham 9y ago
    Have you looked at Snowflake Data? They have a similar pay-per-compute setup as your Presto cluster with automatic pause/resume to save any idle time. Good middleground between traditional Redshift and flexible BigQuery.
    - buremba 9y ago
      Yes, they have their own query engine but the idea is same; separate storage and query layer, scale the query layer on demand and charge for it. I like their approach but not everyone is willing to depend on a third party company for their company data.

vgt 9y ago

Thanks, can we continue this discussion? Super interesting to me. One clarification. BigQuery has two methods of ingest. The Streaming API you mention does carry additional cost, but for the added benefit of having your data appear in BigQuery in real-time. Batch load, however, is entirely free, as in - it doesn't use your "query capacity", and we don't charge for it. I am rather certain this is a very compelling offering. Batch loads encode your data, replicate it, secure it, convert it into our format, and fix any issues your files may have, like optimal file sizes, optimal number of files, optimal file groupings for your queries - lots of subtle things that either slow you down or cause you headaches otherwise. We also manage all the metadata for you (your MySQL instance). We also burn a good chunk of resources post-load re-materializing and optimizing your dataset. We also maintain upgrades, downtime, and so on. It sounds like your use case is micro-batch, so perhaps you could benefit from our free batch loads? It also sounds like you don't mind the extra operational overhead, and would rather operate your own stack, run your own upgrades, fix your own file issues, and so on. This is a personal preference, I agree. Customers who prefer our model love the ease of use and would rather focus their energy elsewhere. It is a stated goal of BigQuery to abstract away complexity and make BigQuery as easy to use as possible for everyone, and if we're failing somewhere, we certainly want to know :) Finally, if I had to equate BigQuery's on-demand pricing, it gives you the ability to go from 0 cores to thousands and back to 0 in sub-second intervals, scoped down to individual query size. This is exactly what's happening under the hood, but all that is abstracted away behind a "run query" button.

buremba 9y ago
Our system is also near-realtime. The default batch duration is 3 minutes, it's also configurable but it will also affect the background processes (shard compaction, data organization jobs etc.) so our customers usually prefer 5 minutes which is also near real-time for 10B events per month. Please note that this is our core business. Of course, we maintain these services but we also try hard to push more work to cloud providers. For example, both AWS and GC offers managed Mysql servers, auto scale groups for nodes, object stores such as S3 and Cloud Storage so we actually maintain the software and help the customers to upgrade the software when they need. I agree that BigQuery will save time if the users are not familiar with distributed systems and big-data but again, "ability to go from 0 cores to thousands" doesn't make sense to me because in practice I have never experienced such case. Since we need to start the instances it may take up to 2 - 3 minutes but this is often acceptable for data analysts. Could you please elaborate the part "re-materializing and optimizing your dataset."? We do a number of optimizations for compacting ORC files, bucketing etc. but I would love to hear how BigQuery does post-processing. As a note, I usually tend to simplify things but most of the BigQuery customers that I see usually do overengineering because of the cost optimization. For example in the article the author uses Redshift for the dashboard data the solution they use for moving data from BigQuery to Redshift also needs to be maintained and it's not that easy. If I'm going to adopt my whole system to the way how BigQuery works and push hard to save costs, then I expect it to be pretty cheap but 40K for reserved slots doesn't sound like cheap to me. We maintain similar size clusters for 20% of this price for the same data volume and it's much more flexible.
- manigandham 9y ago
  Have you looked at Snowflake Data? They have a similar pay-per-compute setup as your Presto cluster with automatic pause/resume to save any idle time. Good middleground between traditional Redshift and flexible BigQuery.
  - buremba 9y ago
    Yes, they have their own query engine but the idea is same; separate storage and query layer, scale the query layer on demand and charge for it. I like their approach but not everyone is willing to depend on a third party company for their company data.