Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

In which use-case would i want to use Table buckets over Amazon Redshift?

0

We currently have some data in S3 that we query using Athena, we are looking into creating a datalake with tabular data (we call aggregated data) which is created by joining multiple tables using Athena.

What i don't understand, why would we go for S3 tables over Amazon Redshift, since both of them support iceberg files, INSERTS and store data in s3 buckets? A reference to the underling architecture behind these 2 services would also be helpful.

Thank you!

Topics
Analytics
Tags
AnalyticsAmazon RedshiftAmazon Athena
Language
English

asked a year ago495 views

3 Answers
0

Depending on what you mentioned, lets go throug each point :

  • First thing you should not ompare S3 table to redshift , s3 table is the new format to store tabular data into an s3 table bucket ( its different from usual s3 ) , so you can read that data using any service that supports iceberg and now we stalk about Athena and Redshift
  • Athena is a query engine for AD-HOC analysis requests its a data lake ( with athena or glue catalog) , on the other hand Amazon Redshift is a petabyte-scale data warehouse service => Redshift is way more performant for your use case to run aggregations if you are talking about aggregating big data , if not and its small data and would simply go with Athena
  • You didnt specify which Redshift ? You have Redshift serverless and Redshift provisioned cluster depending on how you want to serve your data , you budget , and how you many times you need to scale up when running your job ... but in both cases they both support Redshift Spectrum which will allow you to read from s3
  • The architecture behind both is advanced but way different , one is data lake ( athena) , redshift is a datawarehouse but can also act as a datalake with Spectrum

answered a year ago

  • Andreea
    a year ago

    Thank you for answering! So from what i understand with Redshift Spectrum i can run queries directly over the data in S3? The issue for us is we need to build reports aggregated by hour with data from various glue tables (clicks, impressions, valid_clicks, valid_impressions, valid_sales) These datasets are partitioned by hour but not by advertiser. We want to be able to extract reports with number of clicks, sales, impressions, for each advertiser who requests it. But we do not want to scan these large tables(clicks/impressions) everytime a user wants a specific type of report.

    To work around this issue we came up with the idea of building an aggregation dataset (currely exploring S3) that would basically construct these reports with the appropriate aggregated metrics (ex. cpc = clicks/impressions) partitioned by hour+ advertiser. And when an advertiser requests a report we just scan that aggregated dataset and apply the aggregation window he requested (maybe he wanted by day in the last 30 days) We are exploring table bucket for this as it seems much more easy to run an INSERT ... SELECT from the initial catalog into the s3 table bucket. We don't have a specific budget but we would want an extensible solution because we have a lot of reports that we will need to build, all on the same raw tables mentioned previously (clicks, impressions etc..). Dataset refresh/insertion will be done once a day and as for report generation, depends on the adoption, we expect maybe 50/day

  • Andreea
    a year ago

    I will also take a look into Spectrum as suggested!

0

Apart from repost agent answer, you should also consider the following

  • Redshift support row level security. Not available in S3 table
  • Redshift is high performant where S3 table is best for adhoc queries.
  • Cost - S3 table is cheaper than RedShift for irregular use.
  • S3 table supports more concurrency, Redshift will be limited by cluster size
EXPERT

answered a year ago

  • Andreea
    a year ago

    Thank you for answering!

0

Table buckets in Amazon S3 and Amazon Redshift serve different purposes and are optimized for different use cases, despite both supporting Apache Iceberg format and storing data in S3.

S3 Table buckets would be more suitable for your use case of creating a data lake with tabular data, especially if you're already using Athena for querying. Here's why:

  1. Data Lake Architecture: S3 Table buckets are designed specifically for data lake scenarios. They provide a scalable, cost-effective storage solution optimized for analytics workloads, making them ideal for storing aggregated data from multiple sources.

  2. Integration with Athena: Since you're already using Athena, S3 Table buckets integrate seamlessly with it. This allows you to continue using Athena for querying your data without significant changes to your existing workflow.

  3. Flexibility and Open Format: S3 Table buckets use the Apache Iceberg format, which is an open table format. This gives you more flexibility in terms of the tools and services you can use to access and analyze your data.

  4. Automatic Optimization: S3 Tables provide automated table optimization through maintenance operations like compaction and snapshot management, which can improve query performance and reduce storage costs without manual intervention.

  5. Serverless and Pay-per-use: With S3 Table buckets, you don't need to provision or manage any infrastructure. You pay only for the storage you use and the queries you run, which can be more cost-effective for data lakes with varying query patterns.

On the other hand, Amazon Redshift is more suitable for:

  1. Data Warehousing: Redshift is a fully managed data warehouse solution, optimized for complex queries and high-performance analytics on large datasets.

  2. Structured Data and OLAP: If your primary use case involves structured data and online analytical processing (OLAP) workloads, Redshift might be more appropriate.

  3. Frequent Complex Queries: If you need to run frequent, complex queries on your data, Redshift's columnar storage and query optimization features might provide better performance.

In terms of underlying architecture:

  • S3 Table buckets are built on top of Amazon S3's distributed storage system. They leverage S3's durability, availability, and scalability while adding features specific to tabular data storage and analytics.

  • Amazon Redshift uses a cluster-based architecture with leader and compute nodes. It stores data in a columnar format and uses massively parallel processing (MPP) to execute queries across multiple nodes.

Given your current use of S3 and Athena, and your goal of creating a data lake with aggregated data, S3 Table buckets seem to align well with your needs. They offer a natural extension to your existing setup while providing optimizations for analytics workloads and maintaining flexibility for future needs.
Sources
What is Amazon S3? - Amazon Simple Storage Service
Working with Amazon S3 Tables and table buckets - Amazon Simple Storage Service
New Amazon S3 Tables: Storage optimized for analytics workloads | AWS News Blog
Using Amazon S3 Tables with AWS analytics services - Amazon Simple Storage Service

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content