Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 47 ยท SQL and NoSQL on Hadoop

Interactive SQL: Presto/Trino and Impala

Learn how Presto/Trino and Impala run fast interactive SQL on Hadoop data: MPP architecture, connectors, federated queries, and how they differ from Hive.

Hive made SQL available on Hadoop, but its queries originally ran as MapReduce jobs and took minutes. Analysts who click through dashboards want answers in seconds. Two projects were built to deliver that on the same data and the same table definitions: Presto (now mostly known as Trino) from Facebook, and Impala from Cloudera. Both are massively parallel processing (MPP) SQL engines that skip MapReduce entirely. This lesson explains how they work, what a connector is, how federated queries combine several systems, and when to pick them over Hive.

Neither engine can run on this site, so the SQL and configuration are read-only. The runnable examples are Python stand-ins: one that executes a query as pipelined stages and one that joins two different "catalogs". Read Hive: SQL over Files and Hive Performance first.

The idea: MPP engines that stay in memory

A Hive-on-MapReduce query wrote intermediate results to disk between stages and started new JVM processes for every task, which is a large fixed cost. An MPP engine keeps long-running worker processes up all the time, sends the query's pieces to them, and streams rows from one stage to the next through memory and the network without touching disk. Latency drops from minutes to seconds, because nothing needs starting and nothing is materialized between steps.

flowchart TB
    C["Client (SQL)"] --> Co["Coordinator<br/>(parses, plans, schedules)"]
    Co --> W1["Worker 1<br/>scan + filter"]
    Co --> W2["Worker 2<br/>scan + filter"]
    Co --> W3["Worker 3<br/>scan + filter"]
    W1 --> A["Aggregate stage<br/>(in memory)"]
    W2 --> A
    W3 --> A
    A --> Co
    Co --> C

Workers scan data in parallel and stream partial results to the next stage; the coordinator gathers the final answer.

Presto and Trino

Presto began at Facebook in 2012 to query their Hive data warehouse interactively. In 2019 its original creators started a community fork, first named PrestoSQL and renamed Trino in 2020; the older line continues as PrestoDB. Concepts and most SQL are shared, but the projects have diverged, so always check which one a tutorial or cluster uses.

Architecture. One coordinator accepts queries, plans them and assigns work; many workers execute tasks on splits of data and exchange rows with each other. Queries are broken into stages that run pipelined: a downstream stage starts consuming rows before the upstream stage finishes. Everything is in memory, so a query that needs more memory than the cluster has fails, unless spilling to disk is enabled (spilling exists but is a slower fallback). Classic Presto and Trino do not recover a query when a worker dies mid-query; the query fails and you rerun it. Trino added an optional fault-tolerant execution mode that retries failed tasks, intended for long ETL-style queries.

Connectors. The engine knows nothing about files or databases itself. A connector is a plug-in that teaches it how to list tables, read splits and (optionally) write for one data source. The Hive connector reads Parquet, ORC and text files in HDFS or S3 using the Hive Metastore definitions; others read Iceberg or Delta tables, MySQL, PostgreSQL, Kafka, MongoDB, Elasticsearch and more. Each configured data source is a catalog, and a table is addressed as catalog.schema.table.

SQL
SHOW CATALOGS;
SHOW SCHEMAS FROM hive;

SELECT country, count(*) AS orders, sum(amount) AS revenue
FROM hive.sales.orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY country
ORDER BY revenue DESC
LIMIT 10;

Federated queries

Because every source is just a connector, one query can join across systems: orders in a data lake with customer rows in MySQL, without copying either one first.

SQL
SELECT c.segment, sum(o.amount) AS revenue
FROM hive.sales.orders AS o
JOIN mysql.crm.customers AS c ON o.customer_id = c.id
WHERE o.order_date >= DATE '2025-01-01'
GROUP BY c.segment;

The engine pushes filters and projections down into each connector where it can, pulls the needed rows into worker memory and does the join itself. This is convenient for exploration and for data that cannot be moved, but a join between a huge lake table and a slow remote database is only as fast as the remote side can deliver rows. For regular reporting, land the data in one place (see Getting Data In: Sqoop, Flume, NiFi and Kafka).

Python A federated join across two catalogs

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Impala

Impala (open source under Apache, driven by Cloudera) pursues the same goal with a different design, tightly bound to the Hadoop ecosystem. It is written in C++ with LLVM code generation. Instead of a single coordinator process type, every node runs an impalad daemon that can accept queries and execute fragments; a statestore (statestored) broadcasts cluster membership and health, and a catalog service (catalogd) caches table metadata from the Hive Metastore and distributes it. Impala reads HDFS, S3 and Apache Kudu (a storage engine for fast updates) and uses the same table definitions as Hive, so a table created in Hive is queryable in Impala. When tables change outside Impala, you refresh its cached metadata with INVALIDATE METADATA or REFRESH.

SQL
INVALIDATE METADATA sales.orders;     -- after Hive or Spark changed the table
COMPUTE STATS sales.orders;            -- gather statistics so the planner joins well
SELECT country, count(*) FROM sales.orders GROUP BY country;

Impala shines for many concurrent short queries on Parquet in a cluster that already runs Cloudera's stack. Like Presto, it assumes queries fit in memory (with some spill to disk) and does not recover a failed query midway.

Hive, Trino and Impala compared

Aspect Hive (Tez or MapReduce) Trino / Presto Impala
Best at long batch ETL, huge jobs interactive and ad hoc SQL, federation interactive SQL on Hadoop and Kudu
Latency seconds to hours sub-second to minutes sub-second to minutes
Fault tolerance task retries, survives node loss query fails (optional fault-tolerant mode in Trino) query fails
Data sources Hadoop tables many connectors, many systems HDFS, S3, Kudu, HBase
Writes tables, partitions, ACID limited, depends on connector inserts, Kudu updates
Memory model disk-backed stages in-memory pipelines, optional spill in-memory, some spill
Language HiveQL ANSI-style SQL SQL close to HiveQL

In practice they complement each other. The Hive Metastore is the shared catalog, Hive or Spark builds and compacts the tables in Parquet or ORC (see Parquet and ORC Internals), and Trino or Impala serve analysts and dashboards on top. Good table layout (partitioning and bucketing, see Partitioning, Bucketing and File Layout) speeds up all three, because the engines skip files they do not need. Use Hive or Spark for jobs that run for hours and must survive failures; use an MPP engine when a person is waiting for the result.

Python Why pipelining beats writing every stage to disk

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why are Presto/Trino and Impala faster than classic Hive on MapReduce for interactive queries?

    MPP engines avoid per-job startup and intermediate disk writes, so latency drops from minutes to seconds.
  2. 2. What is a Trino catalog?

    Each connector configuration becomes a catalog, which is why one query can join tables from different systems.
  3. 3. What happens in classic Presto or Impala when a worker dies in the middle of a long query?

    These engines favor speed over mid-query recovery, so long ETL jobs are better run on Hive or Spark (or Trino's optional fault-tolerant mode).
  4. 4. What role does the Hive Metastore play for these engines?

    The metastore is the common catalog describing schemas, partitions and file locations.

Summary

  • Presto/Trino and Impala are MPP SQL engines: always-on workers, in-memory pipelined stages and no MapReduce, giving interactive latency.
  • Trino and Presto use a coordinator plus workers and connectors; catalogs let one query join a lake table with MySQL or Kafka (federation).
  • Impala runs impalad on every node with a statestore and catalog service, reads the Hive Metastore, and needs REFRESH or INVALIDATE METADATA when tables change elsewhere.
  • Queries usually must fit in memory and fail if a worker dies; Hive and Spark remain the choice for long, fault-tolerant batch work.
  • A good setup combines them: shared metastore, tables built in Parquet or ORC, and an MPP engine for people waiting on answers.

Next: Pig Latin Dataflows.