Athena vs Redshift Spectrum

Question

I am kind of evaluating Athena & Redshift Spectrum. Both serve the same purpose, Spectrum needs a Redshift cluster in place whereas Athena is pure serverless. Athena uses Presto and Spectrum uses its Redshift's engine

Are there any specific disadvantages for Athena or Redshift spectrum? Any limitations on using Athena or Spectrum ?

Solution 1

I have used both across a few different use cases and conclude:

Advantages of Redshift Spectrum:

• Allows creation of Redshift tables
• Able to join Redshift tables with Redshift spectrum tables efficiently

If you do not need those things then you should consider Athena as well

Athena differences from Redshift spectrum:

• Billing. This is the major difference and depending on your use case you may find one much cheaper than the other
• Performance. I found Athena slightly faster.
• SQL syntax and features. Athena is derived from presto and is a bit different to Redshift which has its roots in postgres.
• Connectivity. Its easy enough to connect to Athena using API,JDBC or ODBC but many more products offer "standard out of the box" connection to Redshift

Also, for either solution, make sure you use the AWS Glue metadata, rather than Athena as there are fewer limitations.

Solution 2

This question has been up for quite a time, but still, I think I can contribute something to the discussion.

What is Athena?

Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to manage, and you pay only for the queries that you run. (From the Doc)

Pretty straight forward, right?

Then comes the question of what is Redshift Spectrum and why Amazon folks made it when Athena was pretty much a solution for external table queries?

So, AWS folks wanted to create an extension to Redshift (which is pretty popular as a managed columnar datastore at this time) and give it the capability to talk to external tables(typically S3). But they wanted to make life easier for Redshift users, mostly analytics people. Many analytics tools don't support Athena but support Redshift at this time. But creating your Reshift cluster and storing data was a bottleneck. Again Redshift isn't that horizontally scalable and it takes some downtime in case of adding new machines. If you are a Redshift user, making your storage cheaper makes your life so much easier basically.

I suggest you use Redshift spectrum in the following cases:

• You are an existing Redshift user and you want to store more data in Redshift.

• You want to move colder data to an external table but still, want to join with Redshift tables in some cases.

• Spark unloading of your data and if you just want to import data to Pandas or any other tools for analyzing.

And Athena can be useful when:

• You are a new user and don't have Redshift cluster. Access to Spectrum requires an active, running Redshift instance. So Redshift Spectrum is not an option without Redshift.
• As Spectrum is still a developing tool and they are kind of adding some features like transactions to make it more efficient.
• BTW Athena comes with a nice REST API , so go for it you want that.

All to say Redshift + Redshift Spectrum is indeed powerful with lots of promises. But it has still a long way to go to be mature.

Solution 3

If you are using Redshift database then it will be wise to use Spectrum along with redshift to get the required performance.

However, if you are beginning to explore options then we can consider Athena as a tool to go ahead.

Solution 4

I had learned (from Adrian Cantril's/LA's 2019 SA Pro course) that Redshift Spectrum would use one's own Redshift cluster to provide more consistent performance than is available by leveraging the shared capacity which AWS makes available to Athena queries. I appreciate this information might only be useful for the exam, I didn't find his argument convincing.

I wrote this answer because I wasn't satisfied with the leading answer's treatment of Athena outperforming Redshift Spectrum. The rest of that answer is good and I do not mean to directly copy any of that here (without references it hadn't registered with me when I wrote this).

I (again, based solely on my hands-off research) would choose Spectrum when the majority of my data is in S3, which would typically be for the larger data sets. The recent RA3 instances seem to overlap this niche though. So I say Spectrum is most suited to where we have long term Redshift clusters that, being OLAP nodes, have spare capacity to query S3.

Why would you use your own estate to perform the queries that Athena would do without such an investment from you? Caching, where it fits. And consistent performance, if I am to believe Adrian Cantrill more than Jon Scott. This made me suspect RA3 might be edging Spectrum out; that and the lack of decent literature on Spectrum. Why would Amazon offer a serverless product in Athena that outperforms Redshift Spectrum which is more expensive? This is how they are choosing to deprecate RRS. I can't believe Spectrum is deprecated so must offer this answer to contest this. Just look at https://aws.amazon.com/redshift/whats-new/.

I think the picture below (from https://d1.awsstatic.com/events/Summits/AMER2020/May13SummitOnline/Modernize_your_data_warehouse.pdf) is fairly clear that compute nodes are influential here, and perhaps contrary to @JonScott's valuable insights above.

One final big difference is Athena is limited to IAM for authentication, as depicted in this reinvent 2018 (ANT201-R1) slide:

View more solutions