How is data returned from Spectrum to Redshift cluster?

Question

Seeking clarification on how the results of a Spectrum query are returned to the main Redshift cluster.  Presumably this would impact the sizing of the nodes in the Redshift cluster to ensure they have sufficient capacity to process the results.  Namely, are all results returned to the leader node, or is there some logic that maps data into the appropriate slice?  If the latter, how does the association of the data to the slice work?

AWS-User-2618129 · Accepted Answer

More detailed info is in my blog: https://aws.amazon.com/blogs/big-data/10-best-practices-for-amazon-redshift-spectrum/.  Spectrum layer will do what it needs to do (push-down operations).  Hopefully it will "filter" out majority of the rows off S3 before sending a small portion back to the main Redshift cluster for further processing (such as joins or DISTINCT).  No, a tiny 2 x dc2.large cluster would not be able to handle 1M of 1GB Parquet files in S3 and do joins on these large external tables.  Each slice of the main Redshift cluster can invoke up to a max of 10 Spectrum nodes per query.  Data post-Spectrum filtering will be sent to Redshift slices depending on the next step in the execution pipeline (as generated by Redshift Optimizer) and hashing values of the join/GBY columns, etc.  This is not much different from performing joins between a regular Redshift table that is using DISTSTYLE EVEN and another Redshift table that uses DISTKEY distribution.

How is data returned from Spectrum to Redshift cluster?

Relevant content