Amazon Athena Elevates Data Management with Support for CREATE TABLE AS SELECT (CTAS) with Amazon S3 Tables
Amazon Web Services (AWS) has announced a significant enhancement to Amazon Athena, its interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Effective August 15, 2025, Athena now fully supports the CREATE TABLE AS SELECT (CTAS) functionality when working with tables residing in Amazon S3. This new capability promises to streamline data transformation, data preparation, and the creation of curated datasets directly within your S3 data lake.
For data professionals and analysts working with large volumes of data stored in Amazon S3, the ability to easily transform and materialize query results into new, optimized tables has long been a desired feature. Previously, users could query data in S3 using Athena, but creating new tables from those query results often involved intermediate steps, potentially increasing complexity and processing time.
The introduction of CTAS with Amazon S3 tables fundamentally changes this workflow, offering a more integrated and efficient approach to data management within the AWS ecosystem.
What does this mean for you?
At its core, CTAS allows you to define a new table based on the results of a SELECT statement. With this update, you can now execute a query in Athena against your existing S3-based tables and directly write the results of that query into a new table, also stored in Amazon S3. This opens up a wealth of possibilities for how you manage and leverage your data.
Key benefits and use cases include:
- Data Transformation and Preparation: Easily transform raw data in S3 into more refined and structured formats. For instance, you can filter, aggregate, join, or apply complex transformations to your data and store the results in a new table optimized for specific analytical workloads.
- Creating Curated Datasets: Build “gold standard” datasets from your raw data lake. This is invaluable for creating aggregated views, materialized dashboards, or specific datasets required by downstream applications and business intelligence tools.
- Performance Optimization: Generate query results in formats or partitions that are optimized for faster retrieval. You can choose specific file formats (like Parquet or ORC) and partitioning schemes that align with your typical query patterns, leading to significant performance gains.
- Simplifying Data Pipelines: Reduce the complexity of your data pipelines by eliminating the need for intermediate storage or separate processing steps for data transformation. CTAS allows for a more direct and efficient flow from querying to materialized data.
- Cost Efficiency: By enabling more efficient data handling and potentially reducing query times on transformed data, CTAS can contribute to cost optimization within your AWS environment.
- Democratizing Data Access: Empowering analysts and data engineers to create and share derived datasets more readily can foster greater data accessibility and collaboration within your organization.
How it works:
The syntax for CTAS in Athena remains familiar to those accustomed to SQL. You’ll use a structure similar to this:
sql CREATE TABLE new_s3_table WITH ( format = 'PARQUET', -- Or ORC, JSON, CSV, AVRO partitioned_by = (partition_column_1, partition_column_2), -- Optional partitioning external_location = 's3://your-bucket-name/path/to/new/table/' ) AS SELECT column1, column2, SUM(column3) as aggregated_value FROM existing_s3_table WHERE some_condition;
This statement will execute the SELECT query against existing_s3_table, process the results, and then write those results as a new table named new_s3_table in Amazon S3, stored in Parquet format and partitioned as specified. The external_location clause is crucial for defining where the new table’s data will reside in S3.
AWS continues to demonstrate its commitment to enhancing the capabilities of its data analytics services, and this latest update to Amazon Athena is a testament to that. The introduction of CTAS with Amazon S3 tables provides a powerful, flexible, and efficient way to manage and transform data directly within your S3 data lake, making it an even more indispensable tool for modern data analytics. This advancement empowers users to derive greater value from their data with increased agility and ease.
Amazon Athena now supports CREATE TABLE AS SELECT with Amazon S3 Tables
AI has delivered the news.
The answer to the following question is obtained from Google Gemini.
Amazon published ‘Amazon Athena now supports CREATE TABLE AS SELECT with Amazon S3 Tables’ at 2025-08-15 18:44. Please write a detailed article about this news in a pol ite tone with relevant information. Please reply in English with the article only.