Query Amazon S3 Tables from Amazon EMR Trino using the Iceberg REST endpoint

Figure 3: CloudFormation stack outputs

Understanding the deployment

The CloudFormation template performs several key tasks:

1. Infrastructure provisioning: Sets up the Amazon EMR cluster with Trino, VPC, subnet, security group, and S3 table bucket.
2. Configuration: Creates necessary Trino configuration files.
3. Integration configuration: Sets up the Iceberg REST connector for S3 Tables.

Part B: Connecting Trino to Amazon S3 Tables with Iceberg REST endpoint

The CloudFormation template automatically configures the S3 Tables catalog in Trino on Amazon EMR. In the next section, we examine the configuration that drives this integration.

1. Catalog configuration details

A catalog in Trino on Amazon EMR is the configuration that grants access to a specific data source. Each Trino on Amazon EMR cluster can have multiple catalogs configured, allowing access to different data sources simultaneously.

As part of this setup, the CloudFormation template creates a catalog properties file at /etc/trino/conf/catalog/s3tables_irc.properties with the following configuration:

connector.name=iceberg
iceberg.catalog.type=rest
iceberg.rest-catalog.uri=https://s3tables.<REGION>.amazonaws.com/iceberg
iceberg.rest-catalog.warehouse=arn:aws:s3tables:AwsRegion:<ACCOUNT-ID>:bucket/<BUCKET-NAME>
iceberg.rest-catalog.sigv4-enabled=true
iceberg.rest-catalog.signing-name=s3tables
iceberg.rest-catalog.view-endpoints-enabled=false
fs.hadoop.enabled=false
fs.native-s3.enabled=true
s3.region=us-east-1
s3.iam-role=arn:aws:iam::<ACCOUNT-ID>:role/service-role/<ROLE-NAME>

2. S3 Tables Iceberg REST endpoint configuration properties

The following table lists the key properties in the catalog configuration on Trino:

This configuration establishes a connection between Trino and the S3 Tables REST endpoint. You can have multiple catalogs registered, one per S3 table bucket, which is determined by the iceberg.rest-catalog.warehouse property.

3. Configure Amazon EMR service IAM role trust relationships

The Amazon EMR service role requires proper trust relationships to function correctly. Navigate to the IAM console and configure the trust policy for your Amazon EMR service role:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "elasticmapreduce.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        },
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<ACCOUNT-ID>:role/service-role/AmazonEMR-InstanceProfile"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

This trust policy establishes two critical relationships:

1. The Amazon EMR service can assume the role to manage cluster operations.
2. The EC2 instance profile can assume the role to access S3 Tables with elevated permissions.

4. Working with S3 Tables in Trino on Amazon EMR

Now that you have Trino on Amazon EMR set up and configured to work with S3 Tables, you can explore how to work with this integration.

4.1. Connecting to Trino on Amazon EMR

Navigate to Amazon EMR and select Connect to the primary node using AWS Systems Manager Session Manager for passwordless SSH.

Figure 4: Connecting to the primary node with Session Manager

When you’re connected, you can use the Trino CLI with your S3 Tables catalog:

sudo su - hadoop
trino-cli --catalog s3tables_irc

This connects you to the Trino on Amazon EMR using the S3 Tables integration you configured.

Figure 5: Trino CLI connected to the S3 Tables catalog

4.2. Examples: Creating and querying tables

In this section you run through some example queries to demonstrate the functionality.

4.2.1 Creating a namespace

First, you create a namespace (schema) in S3 Tables. A namespace in S3 Tables is a logical container or organizational unit that helps group related tables and objects together.

CREATE SCHEMA blog_namespace;
USE blog_namespace;

4.2.2 Creating a table

Create a table with various data types. You don’t need to specify the table type as Iceberg explicitly because you’re connecting to the Iceberg catalog. You can use all standard Iceberg capabilities, such as partitioning and sorting. Furthermore, some of the important Iceberg table properties that support table maintenance operations are configured with default values. You also have the option to edit the configurations using S3 Tables maintenance APIs.

CREATE TABLE IF NOT EXISTS customers (
customer_sk INT,
customer_id VARCHAR,
salutation VARCHAR,
first_name VARCHAR,
last_name VARCHAR,
preferred_cust_flag VARCHAR,
birth_day INT,
birth_month INT,
birth_year INT,
birth_country VARCHAR,
login VARCHAR
) WITH (
format = 'PARQUET',
sorted_by = ARRAY['customer_id']
);

Table property explanation:

• format = 'PARQUET': Specifies Parquet as the file format for optimal compression and query performance.
• sorted_by = ARRAY['customer_id']: Defines sort order within data files, improving query performance for customer_id filters.

Verify the table creation:

SHOW TABLES;

You should see customers in the output, confirming the table exists in the S3 Tables catalog.

4.2.3 Inserting data

You can insert some sample data into your table. You can also use an existing table in any of the catalogs configured in Trino on Amazon EMR to read data and write into the S3 table with an INSERT INTO ... SELECT statement.

INSERT INTO customers VALUES
(1, 'AAAAA', 'Mrs', 'Martha', 'Rivera', 'Y', 8, 4, 1984, 'US', 'mrivera'),
(2, 'AAAAB', 'Mr', 'Mateo', 'Jackson', 'N', 22, 6, 2001, 'US', 'mjackson'),
(3, 'BAAAA', 'Ms', 'Mary', 'Major', 'Y', 16, 2, 1999, 'US', 'mmajor'),
(4, 'BBAAA', 'Mr', 'Paulo', 'Santos', 'N', 30, 3, 1973, 'US', 'psantos'),
(5, 'AACAA', 'Ms', 'Ana', 'Silva', 'N', 2, 6, 1982, 'CA', 'asilva'),
(6, 'ABAAA', 'Mr', 'Alejandro', 'Rosalez', 'N', 5, 12, 1988, 'US', 'arosalez'),
(7, 'BBAAA', 'Ms', 'Nikki', 'Wolf', 'N', 6, 1, 2006, 'MX', 'nwolf'),
(8, 'ACAAA', 'Mr', 'Arnav', 'Desai', 'N', 15, 7, 1976, 'US', 'adesai');

This INSERT operation demonstrates Trino’s ability to write data to S3 Tables. Behind the scenes, Trino:

1. Writes data files in Parquet format to S3.
2. Communicates with the S3 Tables REST endpoint to register the new files.
3. Atomically commits the transaction, updating table metadata.

4.2.4 Querying data

Execute a SELECT query to retrieve and verify the inserted data:

SELECT * FROM customers LIMIT 10;

The query should return all eight customer records with proper formatting. You can also execute more complex analytical queries:

-- Count customers by country
SELECT birth_country, COUNT(*) as customer_count
FROM customers
GROUP BY birth_country
ORDER BY customer_count DESC;

-- Find customers born after 1990
SELECT first_name, last_name, birth_year
FROM customers
WHERE birth_year > 1990
ORDER BY birth_year;

These queries demonstrate Trino’s SQL capabilities and the integration with S3 Tables for both read and write operations.

4.3 Explore advanced features

S3 Tables with Iceberg provides several features for data management:

4.3.1 Time travel queries

Step 1: Check available snapshots.

-- Query table as of a specific timestamp. Check available snapshots
SELECT * FROM "customers$snapshots";

Step 2: Query the table as of a specific snapshot.

SELECT * FROM customers FOR VERSION AS OF <snapshot_id_from_step1>;

4.3.2 Schema evolution

-- Add a new column
ALTER TABLE customers ADD COLUMN email VARCHAR;

-- Rename a column
ALTER TABLE customers RENAME COLUMN login TO username;

Cleaning up

To clean up the resources, navigate to CloudFormation and delete the stack that you created.

Conclusion

This solution demonstrates an integration between Amazon EMR Trino and Amazon S3 Tables using the Apache Iceberg REST catalog specification. In this post, we showed you how to create and query S3 Tables from Trino on Amazon EMR. The architecture delivers several advantages for modern data platforms:

Operational simplicity: S3 Tables eliminates the complexity of managing Iceberg table metadata, compaction schedules, and snapshot lifecycle policies. The service handles these operations automatically, allowing data teams to focus on analytics rather than infrastructure maintenance.

Performance at scale: The architecture is designed for large-scale workloads. Trino distributes query execution across the cluster while Iceberg’s metadata layer helps the engine locate only the relevant data files. Features like partition pruning, predicate pushdown, and columnar file formats can help improve performance for both interactive and batch workloads.

Cost efficiency: This architecture separates compute and storage, so you can scale each independently based on workload requirements. S3 Tables automatically compacts small files to help reduce storage overhead, and Amazon EMR clusters can scale dynamically so you pay for compute only when needed.

Open standards and portability: By using Apache Iceberg’s open table format and REST catalog specification, this solution avoids vendor lock-in. Other Iceberg-compatible engines can access tables created in S3 Tables including Apache Spark, Apache Flink, and Dremio, providing flexibility in tool selection.

Fine-grained access control: Integration with IAM and resource-based policies provides access control at the table bucket, namespace, and table level. For fine-grained access at the column and row level, you can integrate with AWS Lake Formation. AWS Signature Version 4 authentication supports secure communication between Trino and S3 Tables.

ACID transactions: Iceberg’s transaction model guarantees atomicity, consistency, isolation, and durability for all table operations. This supports reliable concurrent reads and writes, making the platform suitable for production workloads requiring data consistency.

This architectural pattern is particularly well-suited for organizations building modern data lakehouses, migrating from traditional data warehouses, or consolidating multiple analytics platforms. The combination of the managed compute of Amazon EMR, Trino’s versatile query engine, and the automated table management of S3 Tables creates a strong foundation for data-driven decision making.

To learn more about the services and features discussed in this post, see the following resources:

• Amazon EMR
• Getting started with Amazon S3 Tables
• Trino on Amazon EMR

About the authors