Debunking Misleading Benchmarks Of Redshift vs BigQuery(aws.amazon.com)
aws.amazon.com
Debunking Misleading Benchmarks Of Redshift vs BigQuery
https://aws.amazon.com/blogs/big-data/fact-or-fiction-google-big-query-outperforms-amazon-redshift-as-an-enterprise-data-warehouse/
95 comments
>> Well, yeah. That's 28,108.80 a month
If you can't (or don't want to!) afford that kind of money for data analytics, please consider giving the FOSS alternative EventQL [0] a try some time.
It's super simple to set up and tries to be efficient on commodity hardware, so you can run large clusters (>100TB scale) for a couple hundred dollars a month.
[0] https://eventql.io/
DISC: I'm one of the EventQL authors
If you can't (or don't want to!) afford that kind of money for data analytics, please consider giving the FOSS alternative EventQL [0] a try some time.
It's super simple to set up and tries to be efficient on commodity hardware, so you can run large clusters (>100TB scale) for a couple hundred dollars a month.
[0] https://eventql.io/
DISC: I'm one of the EventQL authors
How fast are group bys? Like say I have 120 billion rows, 25 not-sparse columns, and want to group by between 2-20 columns (5 of which are varchar), aggregating the other 5 columns?
What kind of hardware would I need to do that interactively? Or consistently sub-10 second, with 100s of queries per minute.
I have built a thing on Redshift that can do some of this, but it has been new territory for me and I am not sure I've done it "right". Constantly looking for alternatives.
What kind of hardware would I need to do that interactively? Or consistently sub-10 second, with 100s of queries per minute.
I have built a thing on Redshift that can do some of this, but it has been new territory for me and I am not sure I've done it "right". Constantly looking for alternatives.
Have you tried this on BigQuery yet? It's built for this kind of extremely large dataset.
You can also look at MemSQL for a distributed relational database with a columnstore. Run enough nodes and you might be able to hit your performance goals.
You can also look at MemSQL for a distributed relational database with a columnstore. Run enough nodes and you might be able to hit your performance goals.
BigQuery makes it pretty easy to calculate how much you'll spend based on your bandwidth needs; unfortunately, if you do the math for the above use case, the answer is pretty discouraging... $5 / TB data processed, and the size in this context is explicitly the uncompressed size. Even if we're extremely generous and assume 4 bytes / column (almost certainly an underestimate given how many bytes they reserve for integers and timestamps), that is potentially 12 TB for the database, so you're paying $60 for a single query that hits the entire table. If you have 500 queries per month that hit the entire table, you're already paying as much as you would be for Redshift on 8 nodes, without even looking at any of the other queries.
The flat rate may improve things dramatically, of course, but the documentation's ambiguity about what you get with a "slot" makes it hard to say. And none of this is taking required bandwidth into account, because AFAICT there are no promises made on query response time... so I don't know if any of this would satisfy the 10 second requirement.
Either way, no matter how Google, MemSQL, or anyone else might try to satisfy these requirements, they can't get around the required hardware costs. At best, they can amortize them by buying in bulk and partitioning all their clients across lots of servers.
I don't work for any of these database providers, BTW, or use any of their products; I have no skin in this game. And I'm not even saying that paying $60 for that kind of query is necessarily a bad deal (when you consider what may be required under the hood). I'm just saying if you're looking for a cheap solution here you're not going to find it.
The flat rate may improve things dramatically, of course, but the documentation's ambiguity about what you get with a "slot" makes it hard to say. And none of this is taking required bandwidth into account, because AFAICT there are no promises made on query response time... so I don't know if any of this would satisfy the 10 second requirement.
Either way, no matter how Google, MemSQL, or anyone else might try to satisfy these requirements, they can't get around the required hardware costs. At best, they can amortize them by buying in bulk and partitioning all their clients across lots of servers.
I don't work for any of these database providers, BTW, or use any of their products; I have no skin in this game. And I'm not even saying that paying $60 for that kind of query is necessarily a bad deal (when you consider what may be required under the hood). I'm just saying if you're looking for a cheap solution here you're not going to find it.
> I'm just saying if you're looking for a cheap solution here you're not going to find it.
That's a given. I think in this context the user was asking how to meet performance goals with cost possibly secondary or not a concern... and in that case BQ has proven to be incredible at churning through large datasets within seconds.
That's a given. I think in this context the user was asking how to meet performance goals with cost possibly secondary or not a concern... and in that case BQ has proven to be incredible at churning through large datasets within seconds.
What you're asking for is just really difficult. Even with a fairly good compression ratio (e.g. 57 bits / tuple, which I chose at random but is similar to what's achievable on real data according to http://i.stanford.edu/~adityagp/courses/cs598/papers/constan...) you're talking 83.4 GB / s of read memory bandwidth just to read the tuples at all within 10 seconds for a single query. The state of the art in terms of what you're likely to be able to actually buy (IBM Power8 machines) can do about 91.5 GB / s. in Stream Triad benchmarks. That's still nowhere close to supporting 100s of queries a minute (more like 6 or 7) and you haven't even started calculating anything yet (Triad is pretty simplistic). It also assumes you can actually achieve that data rate while keeping everything in memory (which is why GPUs probably won't help much for your use case, despite NVLink; they don't have enough RAM).
Your biggest issue with aggregation / group by is going to be the memory bandwidth to/from a hash table to store all the results. Once the hash table no longer fits in L3, its access time will rise dramatically, so your query's performance will depend heavily on how many buckets you need (if you're grouping by 2 columns, maybe not that many; if you're grouping by 20, you'll probably have almost no buckets with more than one entry). Another potential issue is going to be having to look up the values for that column in a hash table if they are highly compressed (since you need to perform aggregation on them, presumably something like sum; if it's count, this doesn't matter).
If you can find a total attribute order such that the columns you group by are always to the left of the columns you aggregate by, you can sort the rows to enable efficient delta encoding; you can also then perform aggregation in the same order as your scan, which eliminates the hash table lookup problem for output (not input, though). You can also pre-materialize the results for common subsets.
To do this with multiple queries at once, you'd probably need to batch query execution (because of the memory bandwidth issue I alluded to earlier). While the data access requirements would remain similar on read, they'd get worse and worse on write (again, depending on how many buckets you had and how cleverly sorted your data were).
Alternately, you could get a bunch of Power8s (or commodity machines, but to maximize bang for your hardware buck you really want stuff with tons of memory bandwidth) and give each of them a slice of the data (but still apply all the above optimizations). The commodity version of this is the Redshift solution. If you went this route, you could also look at specialized solutions like GPUs with NVLink or the KNL Xeon Phis, which have "fast" memory with tons of extra bandwidth which help mitigate the aforementioned hash table / query result access costs.
I still haven't talked about how you're supposed to actually get the results back. If you're trying to do ethernet through anything commodity you're going to be very limited in terms of data rate. Even 100 Gbps Infiniband only gets you 12.5 GB/s out, and even if you hook two of those up to each machine you're still only at 25 GB/s out. So either you get even more machines, your clients process the data on the machine, or you have to limit the output somehow... and if you're thinking "we'll sort it!" guess what that's going to be bound by (unless you can store the input data presorted)? Probably memory bandwidth (assuming radix sort)!
tl;dr One way or another, you're paying out the nose to satisfy the requirements you just outlined. Also worth noting that you're paying for this read performance on write.
Your biggest issue with aggregation / group by is going to be the memory bandwidth to/from a hash table to store all the results. Once the hash table no longer fits in L3, its access time will rise dramatically, so your query's performance will depend heavily on how many buckets you need (if you're grouping by 2 columns, maybe not that many; if you're grouping by 20, you'll probably have almost no buckets with more than one entry). Another potential issue is going to be having to look up the values for that column in a hash table if they are highly compressed (since you need to perform aggregation on them, presumably something like sum; if it's count, this doesn't matter).
If you can find a total attribute order such that the columns you group by are always to the left of the columns you aggregate by, you can sort the rows to enable efficient delta encoding; you can also then perform aggregation in the same order as your scan, which eliminates the hash table lookup problem for output (not input, though). You can also pre-materialize the results for common subsets.
To do this with multiple queries at once, you'd probably need to batch query execution (because of the memory bandwidth issue I alluded to earlier). While the data access requirements would remain similar on read, they'd get worse and worse on write (again, depending on how many buckets you had and how cleverly sorted your data were).
Alternately, you could get a bunch of Power8s (or commodity machines, but to maximize bang for your hardware buck you really want stuff with tons of memory bandwidth) and give each of them a slice of the data (but still apply all the above optimizations). The commodity version of this is the Redshift solution. If you went this route, you could also look at specialized solutions like GPUs with NVLink or the KNL Xeon Phis, which have "fast" memory with tons of extra bandwidth which help mitigate the aforementioned hash table / query result access costs.
I still haven't talked about how you're supposed to actually get the results back. If you're trying to do ethernet through anything commodity you're going to be very limited in terms of data rate. Even 100 Gbps Infiniband only gets you 12.5 GB/s out, and even if you hook two of those up to each machine you're still only at 25 GB/s out. So either you get even more machines, your clients process the data on the machine, or you have to limit the output somehow... and if you're thinking "we'll sort it!" guess what that's going to be bound by (unless you can store the input data presorted)? Probably memory bandwidth (assuming radix sort)!
tl;dr One way or another, you're paying out the nose to satisfy the requirements you just outlined. Also worth noting that you're paying for this read performance on write.
Yeah, that is what it seems like.
Using a column store and doing a bunch of pre-aggregation is the only way we've been able to to come close to these requirements, but I keep hoping there is a system that will do what we've done without having to write code. Trading space for computation is the only thing that has worked reliably.
We also have the benefit that most queries just want a small subset of time and have things sorted to take advantage of that. But occasionally people want to do something over a large range and if isn't a set of group-bys we've pre-aggregated then they just have to be patient.
Using a column store and doing a bunch of pre-aggregation is the only way we've been able to to come close to these requirements, but I keep hoping there is a system that will do what we've done without having to write code. Trading space for computation is the only thing that has worked reliably.
We also have the benefit that most queries just want a small subset of time and have things sorted to take advantage of that. But occasionally people want to do something over a large range and if isn't a set of group-bys we've pre-aggregated then they just have to be patient.
Hadn't heard of EventQL before. Took a look at the documentation, so I have a brief idea of what it offers and how it works. But I couldn't find anything in way of performance numbers.
While I might try it out myself to see how well it performs, it would be nice if some figures were readily available.
I've been keeping an eye on Postgres-XL and CitusDB for distributed SQL. Would be interesting to compare.
While I might try it out myself to see how well it performs, it would be nice if some figures were readily available.
I've been keeping an eye on Postgres-XL and CitusDB for distributed SQL. Would be interesting to compare.
Add MemSQL to the list.
Proprietary. Even the free community edition is closed source. EventQL, Postgres-XL, and CitusDB are all open source.
Honestly, why is that a problem? What open-source benefits are you taking advantage of?
Running an open source stack means you're not completely at the mercy of someone else. I'd cite Windows 10 desktop editions as a paragon of "you buy it, but you don't own it, then you suffer" proprietary products, but it's not a server component. So I have to cite FoundationDB, the proprietary database, which was seriously awesome, but so awesome that the downloadable binaries vanished overnight.
I have at least the assurance that such a thing cannot happen with Postgres (there are a multitude of for-profit companies working on it full-time, in addition to pro-bono community contributors), or with Apache Cassandra; and it's a similar assurance that keeps users of RethinkDB able to continue to operate despite the company being bought and folded.
----
Other advantages of open source: the very freedom to inspect and modify the software we run. This freedom is how OpenResty was born. In fact, Postgres itself comes from another GPLed database.
On a minor note: this freedom is what allowed us at my last job to create a patch for an internally required function in Nginx, in ten minutes. Had Nginx been closed source, we'd have had to request the makers, wait days/weeks/months, and if accepted they'd include it.
I have at least the assurance that such a thing cannot happen with Postgres (there are a multitude of for-profit companies working on it full-time, in addition to pro-bono community contributors), or with Apache Cassandra; and it's a similar assurance that keeps users of RethinkDB able to continue to operate despite the company being bought and folded.
----
Other advantages of open source: the very freedom to inspect and modify the software we run. This freedom is how OpenResty was born. In fact, Postgres itself comes from another GPLed database.
On a minor note: this freedom is what allowed us at my last job to create a patch for an internally required function in Nginx, in ten minutes. Had Nginx been closed source, we'd have had to request the makers, wait days/weeks/months, and if accepted they'd include it.
> 28,108.80 a month
maybe i'm completely out of touch, but i'm really wondering who can afford this kind of stuff without $20M series A money in the bank. and i'm also wondering what they're going to do when they run out of that money and have zero database expertise in-house because they outsourced everything to amazon.
maybe i'm completely out of touch, but i'm really wondering who can afford this kind of stuff without $20M series A money in the bank. and i'm also wondering what they're going to do when they run out of that money and have zero database expertise in-house because they outsourced everything to amazon.
Maybe I'm jaded, but 330k a year or so for the very backbone of your entire real time query analytics infrastructure just doesn't seem unreasonable? This is highly specialized software, tuned and architected for the purpose of running relatively complex analytics queries and aggregates fast enough to allow interactive real time exploratory analysis.
I dont think it's insane to believe that a profitable mature data informed business should be expected to spend 1/3 of a million a year for the ability to have all that data live and available without deep latency.
I can also agree that this is quite a bit for a startup, but if the unit economics of the startup require that this kind of data be available for interactive exploring, there's going to be a deep challenge down the road in scaling it I think?
To your point, if a startup runs through 20 million and their biggest problem is paying AWS bills, then they are probably a failed startup and folks should move on towards whatever next exciting thing is on the horizon.
I dont think it's insane to believe that a profitable mature data informed business should be expected to spend 1/3 of a million a year for the ability to have all that data live and available without deep latency.
I can also agree that this is quite a bit for a startup, but if the unit economics of the startup require that this kind of data be available for interactive exploring, there's going to be a deep challenge down the road in scaling it I think?
To your point, if a startup runs through 20 million and their biggest problem is paying AWS bills, then they are probably a failed startup and folks should move on towards whatever next exciting thing is on the horizon.
With 10TB of data, you could also do it a hell of a lot cheaper and faster on premise.
One engineer/DBA to deal with your cluster is those 330k/year.
Baby startups don't have a DBA, they have a dev that kinda knows SQL.
Don't those computers cost a lot? Like $10k per blade? Granted it's probably amortized but still...
On the other hand the Redshift cluster doesn't run itself, despite what Amazon says. You still need at least a part time DBA guy.
On the other hand the Redshift cluster doesn't run itself, despite what Amazon says. You still need at least a part time DBA guy.
Last time I worked with physical servers the rule was to avoid blades and run rackmount until space or termal was an issue.
And as long as you were not into GPUs you could get some really decent HP servers at somewhere around $2000 - $7000 depending on your storage needs.
And as long as you were not into GPUs you could get some really decent HP servers at somewhere around $2000 - $7000 depending on your storage needs.
In your part of the world, maybe. The Valley does not reflect average DBA salaries around the world.
there are DBA services that do an excellent job for a fraction of that price. they also do AWS stuff too.
full-time DBA's work for either enormous corporations, or database consulting firms. it's very rare you will see some random small-medium company with even one DBA on payroll.
full-time DBA's work for either enormous corporations, or database consulting firms. it's very rare you will see some random small-medium company with even one DBA on payroll.
You dont really need a full time DBA to handle 10TB these days.
I give you just one example: a security vendor collects multiple terabytes per day worth of antivirus data and sells the insight that can be gathered from this to large enterprises to support their security operations. I do not know exactly how much they make out of this but is guess it is in the 200K-500K / year / customer range.
Startups on the other side, rarely have the amount of data that could not be hosted on anything for more than 1000-2000 USD/month.
Startups on the other side, rarely have the amount of data that could not be hosted on anything for more than 1000-2000 USD/month.
[deleted]
Redshift absolutely dominated the other benchmarks, so while the author chose the largest instance, it's not likely to be necessary to still beat BQ.
I don't work for or have any relationship with either, but I do use Redshift.
I don't work for or have any relationship with either, but I do use Redshift.
> That's 28,108.80 a month
That is right. We had ~100K / month with on prem Hadoop (energy, networking, OPS, enterprise support, etc. included) that could have been moved to Redshift to save money. However, when we did the comparison a different cloud based solution came out as the winer for our DWH needs. The point is, showing a single number without showing how much it would be with other solutions is pointless. There are several companies that can easily afford a ~29K/month DWH in the cloud.
That is right. We had ~100K / month with on prem Hadoop (energy, networking, OPS, enterprise support, etc. included) that could have been moved to Redshift to save money. However, when we did the comparison a different cloud based solution came out as the winer for our DWH needs. The point is, showing a single number without showing how much it would be with other solutions is pointless. There are several companies that can easily afford a ~29K/month DWH in the cloud.
edit: My original comment's data was misleading due to conversion error. I edited out the comment to avoid confusion for anyone else who might not spot the error and be misinformed. Thank you for those who pointed it out!
You're math is wrong - DC1.8XL costs $4.80 per hour, not $4800 (which would be ridiculous pricing).
8 instances of DC1.8XL = $38.4 which means you can run the redshift cluster for 28 hours (edit: for $1100, which is the cost of scanning 220TB of data with BQ, used in previous comment).
Redshift does win on price when you are continuously running queries and scanning entire datasets, although there is enterprise pricing available for BQ if you hit a certain amount.
8 instances of DC1.8XL = $38.4 which means you can run the redshift cluster for 28 hours (edit: for $1100, which is the cost of scanning 220TB of data with BQ, used in previous comment).
Redshift does win on price when you are continuously running queries and scanning entire datasets, although there is enterprise pricing available for BQ if you hit a certain amount.
BQ offers a flat rate pricing package now.
https://cloud.google.com/bigquery/pricing#flat_rate_pricing
https://cloud.google.com/bigquery/pricing#flat_rate_pricing
That's what I meant by enterprise pricing.
Completely correct! I apologize, used to seeing a different notation and the extra 0 in 4.800 threw me off. I removed the misleading data because judging by the points, not everyone caught it.
> and a DC1.8XL costs $4800/hour to "compute" the data.
Is this a typo? A single DC1.8XL is $4.80/hour. 8, like the article, would be $38.40/hour.
Is this a typo? A single DC1.8XL is $4.80/hour. 8, like the article, would be $38.40/hour.
As stated, on demand pricing was used. Both products have special pricing at higher usages and flat-pricing tiers. It would start to get compilicated to compare them at that point.
Numbers on https://aws.amazon.com/redshift/pricing/ used.
dc1.8xlarge - $4.800 per Hour
Numbers on https://aws.amazon.com/redshift/pricing/ used.
dc1.8xlarge - $4.800 per Hour
> $4.800 per Hour
That's four point eight dollars, not four thousand eight hundred.
That's four point eight dollars, not four thousand eight hundred.
Fixed! Used to seeing 4.800 being 4800 apologies!
I figured. It's easy to forget that there's really no international/intercultural agreement on number punctuation, particularly combined with a service that wants to price down to tenths of cents.
[deleted]
I don't understand how you calculated how much it costs to compute on the Redshift cluster. $1100 would be ~28 hours of the 8-node cluster.
I give it our data and it just works
Could you please talk a bit more about your use cases? what domain, volume of data, how you manage updates etc, if it is possible for you to share?
Could you please talk a bit more about your use cases? what domain, volume of data, how you manage updates etc, if it is possible for you to share?
Before this thread turns into a vim-v-emacs-esque flame war.
I really think it's a good thing that AWS and GCP are punching each other in the cloud data warehousing market. It means that the market is maturing, and we are all benefiting from their arms race against each other.
I am of the opinion that Redshift and BigQuery are philosophically different enough that performance differences, while important, shouldn't be the deciding factor. I've written about this in a blog post awhile back, and it might be relevant for folks weighing their options
https://blog.treasuredata.com/blog/2016/06/09/redshift-bigqu...
Disclosures:
1. I don't work at either.
2. My employer, however, partners with both.
I really think it's a good thing that AWS and GCP are punching each other in the cloud data warehousing market. It means that the market is maturing, and we are all benefiting from their arms race against each other.
I am of the opinion that Redshift and BigQuery are philosophically different enough that performance differences, while important, shouldn't be the deciding factor. I've written about this in a blog post awhile back, and it might be relevant for folks weighing their options
https://blog.treasuredata.com/blog/2016/06/09/redshift-bigqu...
Disclosures:
1. I don't work at either.
2. My employer, however, partners with both.
I agree that they were both created with different problem domains. I mainly take issue with the fact that they selected a single query in a large benchmarking suite and used that to claim that BigQuery was categorically better than redshift. Hell, I actually really like BigQuery! I do find the pricing for queries a tad opaque and difficult to estimate... and the variability in performance is fine for my pet projects but I imagine a larger enterprise would prefer something more predictable. I agree with your statement that they are two very very different products. I enjoyed reading your post.
Disclosures: I wrote the parent article.
Disclosures: I wrote the parent article.
> I do find the pricing for queries a tad opaque and difficult to estimate
BigQuery just introduced flat-rate pricing for this exact reason: https://cloud.google.com/bigquery/pricing#flat_rate_pricing
Disclosure: I work for Google Cloud
BigQuery just introduced flat-rate pricing for this exact reason: https://cloud.google.com/bigquery/pricing#flat_rate_pricing
Disclosure: I work for Google Cloud
You're still dealing with query slots though -- which remains difficult to estimate. As far as I know the only way to figure that out is to actually run the query and then check how many slots it used. Am I wrong? I'm certainly not a BigQuery expert.
If you're downvoting do you mind leaving a comment on why I'm wrong? I'm interested in knowing more. The documentation for BigQuery doesn't really explain how slots are allocated and what they're capable of.
If you're downvoting do you mind leaving a comment on why I'm wrong? I'm interested in knowing more. The documentation for BigQuery doesn't really explain how slots are allocated and what they're capable of.
Best part is that what you get for a slot can and does change over time.
Yeah, this is a great move. That said, again, here's the philosophical difference: with BQ, you need to "Contact your sales representative if you are interested in flat-rate pricing". There's nothing wrong with this, but it reflects their philosophy that compute should be something pay-per-use, not pay-to-procure.
Again, I expect this "talk to sales" part to change in the future as BQ matures, but I imagine this kind of CIO friendly thinking to be new (if not de-prioritized) at Google.
Again, I expect this "talk to sales" part to change in the future as BQ matures, but I imagine this kind of CIO friendly thinking to be new (if not de-prioritized) at Google.
Umm, you didn't even opine on anything. Why would it matter where you work to anyone? All this "disclosing" is annoying.
Disclosures: I don't disclose things when it makes no sense to.
Disclosures: I don't disclose things when it makes no sense to.
When you promote, or generally talk about a product, it is good manner to disclose your affiliation to it.
Don't shit on people for being polite.
Edit: That said, it works a lot better on reddit with flairs.
Don't shit on people for being polite.
Edit: That said, it works a lot better on reddit with flairs.
When you promote, or generally talk about a product, it is good manner to disclose your affiliation to it.
Nonsense. In what way does this help the reader? Facts are facts, whether they come from a Google employee or a clown. He/she didn't share an opinion that needs to be taken with a grain of salt.
Nonsense. In what way does this help the reader? Facts are facts, whether they come from a Google employee or a clown. He/she didn't share an opinion that needs to be taken with a grain of salt.
Great article. Data driven.
We use BigQuery after trying all other options including running our own VMs with database software, 3rd party data vendors and the big managed cloud services.
BigQuery's no-ops model makes a huge difference. It saves a lot of effort when all you have to do is just load up your data and run queries, no other cluster starting, sizing, tweaking or maintenance necessary.
It has real-time streaming input, nested/repeated records, powerful RegEx support and UDFs so you can run some really complex queries that you can't do with traditional SQL. It also has the cheapest storage at $0.01/gb after 90 days. They even have DML statements in beta now to support update/delete statements.
If you have lots of data to store (TBs to PBs), which can archived by time or other dimension as it gets older, but also needs to be available at any time, and have a decent amount of queries or but also need to run massive scans, and don't need sub-second response times, and want really complex query logic, BQ wins.
BigQuery's no-ops model makes a huge difference. It saves a lot of effort when all you have to do is just load up your data and run queries, no other cluster starting, sizing, tweaking or maintenance necessary.
It has real-time streaming input, nested/repeated records, powerful RegEx support and UDFs so you can run some really complex queries that you can't do with traditional SQL. It also has the cheapest storage at $0.01/gb after 90 days. They even have DML statements in beta now to support update/delete statements.
If you have lots of data to store (TBs to PBs), which can archived by time or other dimension as it gets older, but also needs to be available at any time, and have a decent amount of queries or but also need to run massive scans, and don't need sub-second response times, and want really complex query logic, BQ wins.
My team uses AWS for all of our servers, CDN, SQL and cache boxes but use BQ for all of our telemetry data - about 20 billion metrics a month.
Not having to manage a redshift cluster and just let BQ do all the work for us is worth the query time; even then, most of our complex queries run sub 30 seconds over hundreds of GB. If your data can be queried using a date-range then use date-partitioned tables. There is a 1000 table query limit so there are some pit-falls but BQ makes it easy for you to aggregate data into weekly and monthly tables to query against.
Disclosures:
1. I don't work at either.
2. My employer, however, partners with both.
Not having to manage a redshift cluster and just let BQ do all the work for us is worth the query time; even then, most of our complex queries run sub 30 seconds over hundreds of GB. If your data can be queried using a date-range then use date-partitioned tables. There is a 1000 table query limit so there are some pit-falls but BQ makes it easy for you to aggregate data into weekly and monthly tables to query against.
Disclosures:
1. I don't work at either.
2. My employer, however, partners with both.
It would be great if AWS would publish the code necessary to reproduce this benchmark. They mentioned applying sort and dist keys; it matters A LOT how these are chosen. For example, if you know in advance how you're going to join, you can distribute both tables on the foreign key column so the join is a local operation. This isn't always an option in real data warehouses, where you do lots of different joins on the same tables. So it represents an opportunity to "cheat" the benchmark. It's also important whether they used BigQuery partitioned tables, which are the equivalent of SORTKEY in some ways.
Having said all this, my company is a fully-managed data pipeline that supports both data warehouses, and we find they both work extremely well for our customers real datasets.
Having said all this, my company is a fully-managed data pipeline that supports both data warehouses, and we find they both work extremely well for our customers real datasets.
George - fan of Fivetran. What, in your opinion, are common cases where customers choose BQ over Redshift? Or is it really a function of which cloud you're on?
If you are already familiar with one, that is actually a really good reason to use it. If you store a lot of data but rarely query it, BQ will be WAY cheaper because you pay for compute and storage separately. On the other hand, you will never see sub-second queries in BQ.
Another option absolutely worth checking out is Snowflake, which compromises between the approach of BQ and Redshift in a very interesting way.
Another option absolutely worth checking out is Snowflake, which compromises between the approach of BQ and Redshift in a very interesting way.
> You can’t just cherry-pick the one query where a given product is the fastest. The workloads in the database domain have broad requirements
Well you can if that test matches the use-case of the database. Big Data databases are not like transnational databases, tend to perform column oriented analytics and tend to not join very well (they may lack indexes altogether).
I find this post to be more misleading than Google - he seems to be conceitedly ignoring the fact that many specialised databases exist that do certain things really well - and selecting tests that match their use-case is entirely appropriate.
Well you can if that test matches the use-case of the database. Big Data databases are not like transnational databases, tend to perform column oriented analytics and tend to not join very well (they may lack indexes altogether).
I find this post to be more misleading than Google - he seems to be conceitedly ignoring the fact that many specialised databases exist that do certain things really well - and selecting tests that match their use-case is entirely appropriate.
I'm primarily trying to point out that google's repeated claims that their performance on TPC-H #22 means that BQ is categorically better than Amazon Redshift are misleading.
Also I think you meant transactional not transnational
Also I think you meant transactional not transnational
Seems that Periscope Data Research from 10/21/2016 agrees with the article's premise. Data driven information is always best when making any comparisons, and since these data points are from a 3rd party I figured this article would be great to share:
https://adtmag.com/articles/2016/10/21/periscope-touts-redsh...
I'm not the parent poster, but I agree with his/her sentiment.
I understand what your purpose was, but the blog entry would have felt more balanced if you'd said something like:
It's true that for a small subset of queries, BigQuery out-performed RedShift. If your workload is similar to those queries then BigQuery may be a good match for you, but our testing shows that for general purpose datawarehousing / analytics workloads, RedShift is the better performing database and would be more suitable for most customers.
I understand what your purpose was, but the blog entry would have felt more balanced if you'd said something like:
It's true that for a small subset of queries, BigQuery out-performed RedShift. If your workload is similar to those queries then BigQuery may be a good match for you, but our testing shows that for general purpose datawarehousing / analytics workloads, RedShift is the better performing database and would be more suitable for most customers.
Performance aside, we've found BigQuery to be far cheaper than Redshift.
With BQ you pay for the amount of data that flows through the system, as opposed to how many instances are running with Redshift.
We also don't really need Redshift running all the time so something like BQ or Azure SQL Datawarehouse where you can pause and scale up/down fits our usage patterns and budget much better.
With BQ you pay for the amount of data that flows through the system, as opposed to how many instances are running with Redshift.
We also don't really need Redshift running all the time so something like BQ or Azure SQL Datawarehouse where you can pause and scale up/down fits our usage patterns and budget much better.
Just FYI you can pause and scale with Redshift as well... but if you query very infrequently (or low volume) cost structure of BQ might be on your side.
Yeah, this didn't debunk much of anything (to me) and the "caveats" at the end were laughably minor. I'm not sure why they listed them other than to say "but they aren't ANSI SQL compatible!" as FUD.
+1. They'd have a much stronger case if they didn't drag in silly complaints such as "[Google BQ] does not support the ANSI SQL syntax of 'substring'. Need to change to 'substr'".
It might seem petty, but I can't see how it hurt their case. If you're migrating hundreds of queries or reports from a local SQL environment to the cloud, being 100% query compatible is nice.
Or automatically generated queries coming from third party tools you don't control...
I disagree.
In order to run the benchmark they had to modify the SQL in it. I much prefer it when a benchmarking exercise is sufficiently transparent for another party to come along an replicate the result.
That implies that they should provide details on all SQL changes that they made. Some of the phrasing was a little petty - BigQuery in “Legacy SQL” mode supports this but then joins don’t really work - but being clear about what they had to change seems totally appropriate to me.
In order to run the benchmark they had to modify the SQL in it. I much prefer it when a benchmarking exercise is sufficiently transparent for another party to come along an replicate the result.
That implies that they should provide details on all SQL changes that they made. Some of the phrasing was a little petty - BigQuery in “Legacy SQL” mode supports this but then joins don’t really work - but being clear about what they had to change seems totally appropriate to me.
We use reserved Redshift instances for close to 3 years now. There were a few moments that we were slightly screwed by new features and bugs in Redshift, but most of the time, we don't need to deal with any maintenance, and because we are fully on AWS, Redshift works way better with other systems we build or use (we have a few use cases of bigquery). We do have lots of regular queries running every hour, and we did lots of optimizations on Redshift. Overall, we are happy with Redshift.
I think in term of EDW solutions, it is really not a one size fits all situation. I am happy for the people (especially small companies who can't afford running Redshift 24/7) who went with BigQuery, but I am also happy that we chose Redshift and so far, it has been great.
I think in term of EDW solutions, it is really not a one size fits all situation. I am happy for the people (especially small companies who can't afford running Redshift 24/7) who went with BigQuery, but I am also happy that we chose Redshift and so far, it has been great.
No link to the original comparison by Google? It was only presented on a private event?
Well, it's impossible to know the context in which this comparison was made. BigQuery is very different from the rest of the data warehouse solutions, I wouldn't think a standard benchmark suite would be appropriate for it.
Well, it's impossible to know the context in which this comparison was made. BigQuery is very different from the rest of the data warehouse solutions, I wouldn't think a standard benchmark suite would be appropriate for it.
We use Amazon Redshift a lot. The biggest pain point is its lack of elasticity and strong coupling between compute and storage. You can see this pattern emerging in warehouses from Azure and Snowflake. I fear that Redshift will never get there because it's based on old Paraccel tech that was never really designed for the cloud to begin with.
Is Redshift worth it? Yes, it's a powerful EDW system. That said, it's going to cost you more than BQ because of always-on nodes.
Is it better than BQ? It really depends on your analytics workload.
Is Redshift worth it? Yes, it's a powerful EDW system. That said, it's going to cost you more than BQ because of always-on nodes.
Is it better than BQ? It really depends on your analytics workload.
Google is pretty transparent about the underlying technologies such as Dremel X, ColumnIO, Capacitor, etc. Even if there is no source code, there is quite a bit in the form of papers, talks, peer OSS projects (e.g. Apache Drill), etc.
But I can't say the same about RedShift, it's a complete black box. (Other than that it's a mod of an ancient version of Postgres)
But I can't say the same about RedShift, it's a complete black box. (Other than that it's a mod of an ancient version of Postgres)
redshift uses paraccel
While their raw benchmark data may be ok, the summary chart makes no sense. They calculated the average and median runtime of all the queries, without first normalizing the results, despite the fact that there is a very huge runtime variance between these queries. Therefore the final number comes mostly from a few most costly queries and almost totally ignores the others.
Database researcher here: TPC-H sucks. TPC-DS is the thing to use but sadly practically nobody does it.
See point 9
http://www.exasol.com/en/newsroom/blog/10-questions-the-tpc-...
Note: Exasol has been dominating TPC-H for a while but it's kind of werid they don't publish much about what's under the hood (unlike Actian Vectorwise, for example, who has a huge library of amazing papers)
See point 9
http://www.exasol.com/en/newsroom/blog/10-questions-the-tpc-...
Note: Exasol has been dominating TPC-H for a while but it's kind of werid they don't publish much about what's under the hood (unlike Actian Vectorwise, for example, who has a huge library of amazing papers)
Why did you say TPC-H sucks and TPS-DS is the thing to use? My impression is that while newer, TPC-DS is actually easier to optimize for.
> unlike Actian Vectorwise, for example, who has a huge library of amazing papers
Because Vectorwise is Marcin Zukowski's brainchild and started with his PhD theses. He's an academic through and through, looks like!
Because Vectorwise is Marcin Zukowski's brainchild and started with his PhD theses. He's an academic through and through, looks like!
* Actian Vectorwise, for example, who has a huge library of amazing papers*
Any chance you could sure a link? I couldn't find this on Google. Thanks!
Any chance you could sure a link? I couldn't find this on Google. Thanks!
That's why we ran TPC-DS as well.
Yeah, and all queries. Good stuff. It'd be nice to have some kind of instructions to reproduce, though. And some explanation on the technology behind it. For example, do you guys scale indexed search or is it just partitioned without indexing?
Do you mind shooting me an email (in profile) with questions you would like to see answered? I'll try to do that in a followup post.
Would have been much nicer to read if it wasn't written in such tactless way. Could have stayed classy, but they chose not to, and it came off as petty. For me at least.
Present the numbers. Show the trade-offs involved. Any good engineer will then be able to make up their own mind. The narrative was somewhat defensive for benchmarking analysis.
I don't have a pony in this race since either one at large enough scale will cost an arm and a leg and both seem to be within same order of magnitude with a constant factor here and there.
I don't have a pony in this race since either one at large enough scale will cost an arm and a leg and both seem to be within same order of magnitude with a constant factor here and there.
Anyone else find it interesting that AWS is so concerned about GCP?
I would recommend checking out SnowFlake who by the way just announced a big price cut.
http://www.zdnet.com/article/will-snowflake-spark-a-cloud-da...
http://www.zdnet.com/article/will-snowflake-spark-a-cloud-da...
So, genuinely curious, how much did the author spend to run the benchmarks on each service?
Amz employee so half was free. Half was maybe a few seconds of Amz profits.
the pricing models for bigquery and redshift are different enough that it is hard to compare on price. redshift is fixed cost (hourly per node like ec2) while on bigquery you pay proportional to data scanned during queries
if you do only occaisonal queries on mostly static data bigquery is probably fine but redshift is a fraction of the cost for more frequent use
if you do only occaisonal queries on mostly static data bigquery is probably fine but redshift is a fraction of the cost for more frequent use
[deleted]
>It’s not surprising to see old guard companies (like Oracle) doing this, but we were kind of surprised to see Google take this approach, too.
Indeed it is, I wonder if that benchmark claim is being taken out of context of that private talk.
Indeed it is, I wonder if that benchmark claim is being taken out of context of that private talk.
Well, yeah. That's 28,108.80 a month if you're running queries on demand and don't want a delay/coordination in Amazon instance creation/destruction.
BQ may or may not be as fast, but it's truly a managed service; I give it our data and it just works. I don't have to worry about instances, boot up time, maintenance, hourly costs, etc. It's silly to focus just on query speed when there's a whole layer of management and cost that comes with it.