#ATHENA VS REDSHIFT SPECTRUM HOW TO#

Amazon Redshift Spectrum, AWS Athena, and the omnipresent, massively scalable. Redshift Spectrum is a logical extension of Redshift to query the data from Redshift as well as Amazon S3 data lakes whereas Athena is an exclusive tool to query data from Amazon S3 only. With Amazon Redshift Spectrum, you can extend the analytic power of Amazon Redshift beyond the data that is stored natively in Amazon Redshift.Īmazon Redshift Spectrum offers several capabilities that widen your possible implementation strategies. Redshift has been around since 2013 and has undergone several enhancements. For example, it expands the data size accessible to Amazon Redshift and enables you to separate compute from storage to enhance processing for mixed-workload use cases.Īmazon Redshift Spectrum also increases the interoperability of your data, because you can access the same S3 object from multiple compute platforms beyond Amazon Redshift. Such platforms include Amazon Athena, Amazon EMR with Apache Spark, Amazon EMR with Apache Hive, Presto, and any other compute platform that can access Amazon S3. You can query vast amounts of data in your Amazon S3 data lake without having to go through a tedious and time-consuming extract, transfer, and load (ETL) process. You can also join external Amazon S3 tables with tables that reside on the cluster’s local disk. Amazon Redshift Spectrum applies sophisticated query optimization and scales processing across thousands of nodes to deliver fast performance. In this post, we collect important best practices for Amazon Redshift Spectrum and group them into several different functional groups. We base these guidelines on many interactions and considerable direct project work with Amazon Redshift customers.īefore you get started, there are a few setup steps.

For more information about prerequisites to get started in Amazon Redshift Spectrum, see Getting started with Amazon Redshift Spectrum. To perform tests to validate the best practices we outline in this post, you can use any dataset. Amazon Redshift Spectrum supports many common data formats: text, Parquet, ORC, JSON, Avro, and more. You can query data in its original format or convert data to a more efficient one based on data access pattern, storage requirement, and so on. For example, if you often access a subset of columns, a columnar format such as Parquet and ORC can greatly reduce I/O by reading only the needed columns.

#ATHENA VS REDSHIFT SPECTRUM HOW TO#

How to convert from one file format to another is beyond the scope of this post. I'll recommend Redshift for now since it can address a wider range of use cases, but we could give you better advice if you described your use case in depth.The Amazon Redshift data lake export feature.For more information on how this can be done, see the following resources: Redshift spectrum vs athena how to# But when I run the same query in Redshift, it is taking over 3 minutes. Running that query in Athena directly, it executes in less than 10 seconds. select count (1) from logs.logsprod where partition1 '2019' and partition2 '03'. If you choose Redshift you'll need to ingest the data from your files into it and maybe carry out some tuning tasks for performance gain. I have a bucket in S3 with parquet files and partitioned by dates.

In the case you go for Athena you'd also proabably need to change your file format to Parquet or Avro and review your partition strategy depending on your most frequent type of query. In both cases you may need to adapt the data model to fit your queries better. Once you select the technology you'll need to optimize your data in order to get the queries executed as fast as possible.

If performance is not so critical and queries will be predictable somewhat I'd go for Athena. If performance is a key factor, users are going to execute unpredictable queries and direct and managing costs are not a problem I'd definitely go for Redshift. First of all you should make your choice upon Redshift or Athena based on your use case since they are two very diferent services - Redshift is an enterprise-grade MPP Data Warehouse while Athena is a SQL layer on top of S3 with limited performance.

Athena vs redshift spectrum