but more importantly, we can join it with other non-external tables. we can query it just like any other redshift table. when we initially create the external table, we let redshift (and subsequently athena and presto) know how the data files are structured. That’s where the aforementioned “stored as” clause comes in. in order to do that, presto needs to know ahead of time how the data is structured, is it a that means that at least some of the computation, especially the low-level table scans, happens within presto, which needs to parse the raw data files into a tabular format. Virtually you can think of it this way: when you run a query against an external redshift table it goes through redshift => athena => presto.
#ATHENA VS REDSHIFT SPECTRUM CODE#
the latter is aws’s smart wrapper around apache presto, which is a query engine that allows sql code against arbitrary data files - be it hdfs, s3, local file-system, etc. i will not elaborate on it here, as it’s just a one-time technical setup step, but you can read more about itĪs stated above, this new feature-set is merely an integration between redshift and aws athena. quite cleverly, instead of having to define it on every table (like we do for everyĬommand), these details are provided once by creating an external schema and then assigning all tables to that schema. as you might’ve noticed, in no place did we provide redshift with the relevant credentials for accessing the s3 file. there’s one technical detail i’ve skipped: external schemas. The more experienced reader will notice that i’ve skipped the schema/database creation phase. if you’re not querying this table, you wouldn’t pay for it (well, beyond the s3 costs). this can easily be as big as s3 allows - exabytes in scale. you can scale as far as you’d like, both in data size and in computation power, independently of one another.
since we’re not storing the data in redshift, there’s a clear separation of storage and compute. querying this table is bound to be slower than querying data that resides within redshift as it involves reading the files and parsing them on every query.
we can start querying it as if it had all of the data pre-inserted into redshift via normal copy commands. let’s consider the following table definition:Ĭreate external table external_schema.click_stream (īasically, what we’ve told redshift is to create a new external table - a read-only table that contains the specified columns and has its data located in the provided s3 path as text files. “external table” is a term from the realm of data lakes and query engines, like apache presto, to indicate that the data in the table is stored externally - either with an s3 bucket or hive metastore.
This means that every table can either reside on redshift normally or be marked as an external table. in the meantime, panoply’sįeature provides an (almost) similar result for our customers. while this is not yet part of the new redshift features, i hope that it will be something that redshift team will consider in the future. then, you might want to have the rest of the data in s3 and have the capability to seamlessly query this table. the past three months, to reside in redshift, as that covers most of your queries. Where such separation would be necessary is when you have a massive table (think clickstream time series) but only want the most recent events, i.e. one limitation this setup currently has is that you can’t split a single table between redshift and s3. So, how does it all work? it starts by defining external tables. amazon just made redshiftīigger without compromising on performance or other database semantics. In essence, spectrum feels like a powerful integration between redshift andĪbility to query these external tables and join them with the rest of your redshift cluster. this option opens up a ton of new use-cases that were either impossible or prohibitively costly before.
Users to seamlessly query arbitrary files stored inĪs though they were normal redshift tables, delivering on the long-awaited requests for separation of storage and compute within redshift. spectrum offers a set of new capabilities that allow , amazon announced a powerful new feature:
0 kommentar(er)
