Chapter 1: Introduction to Distributed SQL and Trino

In the modern data landscape, the days of storing all your information in a single, monolithic database are long gone. Today, data is scattered across data lakes (S3, GCS, ADLS), relational databases (PostgreSQL, MySQL), and NoSQL systems (MongoDB, Cassandra). As a data engineer or analyst, the challenge isn't just storing this data—it’s querying it efficiently without moving it around.

This is where Trino (formerly PrestoSQL) shines. Trino is a distributed SQL query engine designed for high-performance, interactive analytics on massive datasets.

The Paradigm Shift: Query-in-Place

Traditionally, if you wanted to join data from an S3-based data lake with a production PostgreSQL database, you had to perform an ETL (Extract, Transform, Load) process. You would extract the data, clean it, and load it into a data warehouse. This creates latency, increases infrastructure costs, and introduces complexity.

Trino enables federated querying. Instead of moving the data to the engine, Trino moves the computation to the data. It treats your various storage systems as "catalogs," allowing you to write a single SQL statement that joins tables residing in entirely different systems.

"The true power of Trino isn't just its speed; it’s its ability to abstract the underlying storage layer, allowing engineers to focus on the business logic of their SQL queries rather than the physical location of the bits and bytes."

How Trino Works: The Architecture

Trino is designed for massive parallelism. To understand why it’s fast, you need to understand its components:

1. Coordinator: The brain of the operation. It receives SQL statements, parses them, plans the execution, and manages the worker nodes.
2. Worker: The muscle. Workers execute the tasks assigned by the coordinator. They fetch data from connectors, process the data (joins, aggregations, filtering), and return the results to the coordinator.
3. Connector: The bridge. Connectors are the SPI (Service Provider Interface) that allows Trino to talk to different data sources. Whether it’s Hive, Kafka, or Elasticsearch, the connector tells Trino how to talk to that specific system.

A Practical Example: Cross-Source Join

Imagine you have user profile metadata in a PostgreSQL database and clickstream events stored in Parquet files on AWS S3.

In standard SQL, you might write:

sql
SELECT 
    u.user_id, 
    u.email, 
    COUNT(c.event_id) as total_events
FROM postgres.users.profiles u
JOIN hive.analytics.clickstream c ON u.user_id = c.user_id
WHERE c.event_date = '2023-10-01'
GROUP BY u.user_id, u.email;

In this query, Trino automatically:

1. Connects to the PostgreSQL connector to fetch user profiles.
2. Connects to the Hive connector to read the Parquet files from S3.
3. Performs a distributed join between the two sources in memory.
4. Returns the result without you ever having to manually sync the tables.

Why Trino for Intermediate Users?

If you are already familiar with basic SQL, you might wonder why Trino feels different. Unlike traditional databases (like MySQL), Trino is MPP (Massively Parallel Processing). Every query is broken down into stages and tasks, which are distributed across all available workers.

Performance Tip: The Importance of Partitioning

Because Trino reads data directly from your storage, your query performance is heavily tied to how that data is stored.

• Partitioning: If your S3 data is stored in folders like
  text

  year=2023/month=10/

  , ensure your queries filter by these partitions. Trino will perform "partition pruning," skipping entire folders of data that aren't needed, saving significant time and I/O costs.
• File Formats: Prefer columnar formats like Apache Parquet or ORC. Trino can perform "predicate pushdown," where it reads only the columns required by your
  text

  SELECT

  statement rather than the entire row.

Best Practices for Production

1. Avoid
   text

   SELECT *

   : Trino is a columnar engine. Fetching every column forces the engine to read unnecessary data from your storage layer, wasting bandwidth and memory.
2. Filter Early: Use
   text

   WHERE

   clauses to reduce the dataset size as early as possible. If you are joining two large tables, ensure they are filtered before the join.
3. Monitor via the UI: Trino comes with a built-in web UI (usually on port 8080). Use it to inspect query plans. If you see a query taking too long, look for "Join Distribution" issues. If a join is uneven, you may need to use
   text

   JOIN ... ON

   columns that are more uniformly distributed.
4. Use
   text

   EXPLAIN

   : Before running a massive, expensive query, always run
   text

   EXPLAIN

   or
   text

   EXPLAIN ANALYZE

   . This shows you the query plan, helping you identify potential performance bottlenecks.

sql
-- Use EXPLAIN to see how Trino intends to execute your query
EXPLAIN (TYPE DISTRIBUTED)
SELECT count(*) 
FROM hive.web_logs.page_views 
WHERE event_date > '2023-01-01';

Summary

Trino transforms the database from a siloed box into a flexible, integrated query layer. By leveraging distributed processing, it allows organizations to break free from the constraints of rigid data warehouses.

In the chapters to follow, we will dive deeper into writing optimized SQL for Trino, managing catalogs, and fine-tuning the cluster for enterprise-grade workloads.

Key Takeaways:

• Trino is not a database; it is a distributed query engine.
• It does not "own" the data; it queries it where it lives.
• Understanding the storage layer (partitioning, file formats) is just as important as writing efficient SQL.
• Always use the
  text

  EXPLAIN

  command to visualize how your queries will be executed.

Exercise for the reader: Access your Trino cluster UI. Identify a query that has run recently, copy it into your query editor, and prefix it with

EXPLAIN (TYPE DISTRIBUTED)