15 ms·
Redshift Spectrum – Exabyte-Scale In-Place Queries of S3 Data
- bsg75 9y agoDoes anyone know what this is based on? As a fork of Postgres v8, I would not expect it to be a foreign data wrapper.
- filereaper 9y agoLooks like Google BigQuery which is based on the Dremel paper.
- jeffbarr 9y agoThis is not BigQuery.
- puzzle 9y agoIt's not, but it resembles BQ a lot in the central part of the query execution, where it transparently performs work on your behalf on thousands of other machines that you don't have to be aware of. Is that correct?
- openasocket 9y ago1. No, you still need a Redshift cluster, which performs the work. A better example would be something like Athena. 2. That's not really a big similarity. Having many machines coordinate for data processing is a really common thing. So if BQ and Redshift Spectrum are similar, than so is Athena, Presto, any mapreduce-based system, Spark, etc.
- puzzle 9y agoMy main point is that there is work behind the scenes happening that you don't have to worry about, on an undefined number of machines that you don't have to care about. Yes, there is still a cluster which does some kind of planning and post-processing now, plus there is obviously a lot of coordination, but it's not like Hadoop or classic Redshift, where you are constrained only to the hardware that you paid for and set up. It's the provisioning in the central phase that resembles BQ the most.
- openasocket 9y agoIn that case Athena is more what you're talking about. Redshift Spectrum is weirder. You still have to have a Redshift cluster you provision and pay for, but for some S3 processing it calls out to other, independent, servers. I guess I'd consider that a hybrid approach.
- filereaper 9y agoJeff, Could you highlight the differences?
- johns 9y agoCan anyone clarify the when or why you would use this instead of or with Athena?
- bladeaod 9y agoIt 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
- agacera 9y agoIf 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.
- filereaper 9y agoVery BigQuery like system. Has anything been sacrificed for this type of scale-out? BQ and other Dremel based systems are weak at joins for star based schemas, and frequently advise denormalization of data.
- scribu 9y ago> 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.
- deleted 9y ago[deleted]
- puzzle 9y agoAt a first read, it sounded like you didn't need to run a cluster ("Spectrum scales to thousands of instances"), which is BigQuery's big advantage. Later, it says that you still need a cluster, but it's only used in the final processing stages. So it looks like it's 3/4 of the way to BQ.
- nieksand 9y agoFrom the blog post, it's very unclear what the positioning is between Redshift Spectrum and Athena.
- georgewfraser 9y agoThis appears to be Amazon Athena / Presto, embedded into Redshift. The syntax for CREATE EXTERNAL TABLE is exactly the same, and the supported file formats and compression encodings are a subset of Athena. The big advantage of an approach like this versus running Redshift and Athena separately is that you can write a single query that joins data stored in Redshift with data stored in Athena.
- idunno246 9y agoAnd 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
- openasocket 9y agoI'd say that I have more confidence in the Redshift query planner and execution engine than that of presto. And having the option to join that with data stored in-memory on redshift nodes is very attractive.
- vhold 9y agoIt's also the exact same cost, $5 per terabyte read.
- awgupta 9y agoThe underlying tech stacks are totally different.
- teddyknox 9y agoWhen I think exabyte scale queries on a columnar datastore I think aggregations, but then I have this question: Why do we need to do exabyte scale queries in the first place? Wouldn't statistical inference via random sampling be faster and accurate enough? (Granted, often times aggregations are happening after some filtering, at which point the relation being aggregated might be considerably smaller than exabyte scale.)
- teraflop 9y agoYeah, 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.)
- adwf 9y agoRedshift 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.
- sixdimensional 9y agoSee BlinkDb
- awgupta 9y agoit 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.
- buremba 9y agoORC support?
- fs111 9y agowhat for if you have parquet? orc feels like a "me too" technology from Horton
- buremba 9y agoWe have 300tb of orc data and can't convert it to Parquet. Also, ORC performs better for our use-case.
- hcoyote 9y agoWhat, specifically, is preventing the conversion? I've converted hundreds of TBytes to Parquet (including moving away from that HIVE ACID stuff).
- buremba 9y agoMainly the development cost. We trust OCR and have good experience with it, it's stable, fast and compact. We don't have any experience with Parquet and it's just too hard/risky to make this kind of conversion on a production system.
- idunno246 9y agoparquet is much less well supported by presto. Facebook is ORC, so ORC gets implemented and optimized first
- jeffbarr 9y agoWe plan to add additional formats over time. I know that ORC is on the list.
- jnordwick 9y agoThis seems really slow for queries especially when taking into account all the computing power being thrown at it: Over 6 billion rows (not huge by modern standards), a relatively common aggregation query with 4 basic aggregates (2 sum, 2 avg), one where, and two group by clauses, over 1 table (no joins) takes about 4.25 minutes (254.650 seconds). On some column databases on good hardware with a single machine you can probably get a couple seconds, probably faster.
- makmanalp 9y agoSo 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.
- jnordwick 9y agoSo these ingested files are JSON or something semi-structured? I saw how the table was created, so maybe is was doing the schema-less key-value pair thing on table creation and running queries against that? I'm sure this is all in the docs somewhere, but while I have a lot of big db experience, I have about 2 hours of AWS.
- nside 9y agoGreat PR! How much does it cost to store 1 exabyte on S3? 1 exabyte = 1,000,000,000 GB Cost of storage 1GB on S3 in us-west-2 = $0.024 That's 24 millions dollars. What am I missing?
- sudhirj 9y agoDon'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.
- Dunedan 9y agoFun 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.
- Jweb_Guru 9y ago> 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.
- manigandham 9y agoAn exabyte is an incredible amount of data. It will always cost millions of dollars to store it safely.