Redshift Spectrum – Exabyte-Scale In-Place Queries of S3 Data

And you can actually materialize a query in redshift since Athena doesn't have create table as select. This is neat, though I wonder if opening Athena to connect to Postgres(presto can) and do the join inside Athena would have been better

It's also the exact same cost, $5 per terabyte read.

The underlying tech stacks are totally different.

Yeah, I think filtering is a big part of it. If you want to answer a statistical question about the entire dataset, then a random sample is probably good enough. If you want to drill down and do an analysis that only looks at a particular narrow slice of the data, then it's likely that the corresponding subset of your sample isn't big enough to be meaningful.

(You can pre-filter or pre-aggregate before sampling, but that assumes you know a priori what types of queries you'll want to do.)

Redshift is designed to fill the classic accounting datawarehouse role in an organisation. Whilst I'm sure there aren't too many companies with account ledgers that large (or any), I doubt too many accountants would be happy with statistical inference of their books... ;)

This new model of processing directly on S3 is pretty much aimed specifically at eliminating the "Load" part of the ETL process. Just dump to csv from whatever sources you originally had, and don't worry about the schema conversion/loading into a DB. The fact that it happens to scale to exabytes is just good marketing fluff.

See BlinkDb

it really depends on what you are doing. A large data set shouldn't be limited to longitudinal analysis. If you're storing every log record or every stock bid/ask, there may be times that you need to understand the specifics of what exactly was going on. There may be a lot of filtering on the underlying corpus for these sorts of exact match queries, but data set sizes continue to grow.

So this is a good question - the answer is that you're comparing apples to oranges. This is all about in-situ data processing, which basically means that you can run reasonably efficient, optimized queries on just regular old files that you have lying around, without having to do anything special. This is as opposed to a situation where you've spent a ton of time, effort and expense actually ingesting the data into whatever data system you're running (in this case redshift). So it's usually said that these systems are good for ad-hoc queries, i.e. one-off queries on files and datasets that you want to explore more, but it doesn't make sense to invest the hours upon hours of waiting and storage / cpu resources to bring them into your database just to make a few queries.

This is an interesting problem space. More and more companies are dealing with much more data than they've ever had to process. Off the top of my head, I know a handful of people whose companies are dealing with 10s of trillions of rows of data. But, they don't have the staff on hand to help them analyze and develop custom indices to improve analysts' query time.

So, something like this is much better (and cheaper) than nothing (and, yes, aws makes a killing). There's gonna be a lot of money to be made by the smart people who help crack this problem.

On a dataset where all values are 0, the answer can be found in 10 nanoseconds, if not faster. What's your point?

I wonder where the bottleneck was. Right below that result they have the performance plot that shows how it used up almost no CPU or network bandwidth. I'd expect S3 to be faster than this?

FWIW, I've been playing with parquet and Kudu lately and on a reasonably sized single machine you can run similar queries on similar scale data in <5 seconds.

Just for comparison's sake, I recreated Jeff's example queries on my in-house table that's ~8B rows. COUNT(*) takes 6s and the SELECT with 2 groups, 2 sums, and 2 averages takes 52s. The downside is that the table takes 600GB of disk space compressed which is a major chunk of our cluster. But here's the kicker - I would never query this table for data I needed in under 5 min so my current performance is meaningless.

Given our access patterns for this table, I'm going to investigate using Redshift Spectrum for it. It seems like a huge win for us.

> Very BigQuery like system.

What makes you say that?

The main innovation in BigQuery was the ability to store and query nested data.

Amazon Redshift doesn't support querying nested data. It only has some convenience functions for loading flat data from nested JSON files hosted on S3.

And what I assume Spectrum does is just perform that loading step behind the scenes.

It seems to me that this is a bridge between Redshift/S3, so you can join data from both sources. I believe Athena is S3 only

If you are already invested in Redshift this is a really nice feature to have.

I used to work in an environment where only data for the last few months was stored in Redshift (to save costs since storage is expensive). Whenever someone need old data, we need to make room for it unloading tables to S3. Now this is not needed anymore, and is is awesome.

Another nice reason is that some BI tools doenst work with Athena (and PrestoDB) yet (Metabase and Pentaho for instance), so it is now viable to use Redshift to expose data inside S3 to these tools.

Don't think that's disingenuous, if that's what you're implying. Companies that have an exabyte of data usually know how much it's costing them to have it.

Fun fact: Using any other public US AWS region would save 3 million dollars per month!

Aside from that: Using infrequent access storage for the parts of the data which don't get frequently accessed would save a lot and I'm pretty sure at that scale AWS would be happy to discuss possible discounts as well.

> What am I missing?

Nothing. Any query processing on sufficiently large amounts of data is going to be expensive in time, space, energy, and money, and Amazon doesn't buy custom hardware for this purpose and intends to make a profit doing it so it's going to be even more expensive.

An exabyte is an incredible amount of data. It will always cost millions of dollars to store it safely.

Looks like Google BigQuery which is based on the Dremel paper.

what for if you have parquet? orc feels like a "me too" technology from Horton

We plan to add additional formats over time. I know that ORC is on the list.