# Trino Data Lake Query Engine

> Trino distributed SQL with MinIO object storage.

- Category: Database Stacks
- Difficulty: advanced
- Components: trino, minio, hive-metastore, postgres
- Tags: trino, data-lake, sql, minio
- Source: https://docker.recipes/databases/trino-data-lake

## Overview

Trino is a distributed SQL query engine originally developed at Facebook (as Presto) and designed to run interactive analytical queries against data sources of all sizes. It excels at federating queries across multiple data sources, from gigabytes to petabytes, enabling organizations to query data where it lives without moving it. Trino's architecture separates compute from storage, making it ideal for cloud-native data lake architectures where data is stored in object storage systems.

This stack combines Trino with MinIO object storage, PostgreSQL-backed Hive Metastore, creating a complete data lake query engine. MinIO provides S3-compatible object storage for your data files (Parquet, ORC, JSON), while the Hive Metastore running on PostgreSQL maintains schema information and table definitions. Trino acts as the query coordinator, enabling SQL queries across data stored in MinIO while leveraging metadata from the Hive Metastore for schema-on-read operations.

Data engineers building analytics platforms, organizations migrating from traditional data warehouses, and companies implementing lakehouse architectures will find this stack particularly valuable. The combination provides enterprise-grade SQL capabilities over object storage at a fraction of the cost of cloud data warehouses, while maintaining compatibility with popular data formats and BI tools that support standard JDBC/ODBC connections.

## Features

- Distributed SQL query engine supporting ANSI SQL with complex joins, window functions, and aggregations across petabyte-scale datasets
- S3-compatible object storage with MinIO providing high-performance data lake foundation with erasure coding and encryption
- Hive Metastore integration enabling schema-on-read for Parquet, ORC, Avro, and JSON file formats
- Query federation capabilities allowing joins between data in MinIO and external sources like MySQL, PostgreSQL, or Elasticsearch
- Connector architecture supporting 50+ data sources including Kafka, MongoDB, Cassandra, and cloud storage systems
- Cost-based query optimizer with predicate pushdown and partition pruning for optimal performance
- JDBC/ODBC connectivity enabling integration with popular BI tools like Tableau, Power BI, and Superset
- Fault-tolerant query execution with automatic retry and cluster coordinator failover capabilities

## Use cases

- Building self-hosted data lakes for analytics teams requiring SQL access to object storage data
- Migrating from expensive cloud data warehouses while maintaining SQL compatibility and BI tool integration
- Creating development and testing environments for big data analytics without cloud storage costs
- Implementing lakehouse architectures for machine learning feature stores and model training datasets
- Running ad-hoc analytical queries across large datasets stored in cost-effective object storage
- Federating queries between operational databases and analytical data stored in data lake formats
- Prototyping data mesh architectures with decentralized data ownership and SQL query capabilities

## Prerequisites

- Minimum 4GB RAM available (Trino coordinator requires 2GB+, MinIO needs 512MB+, PostgreSQL needs 256MB+)
- Docker and Docker Compose with at least 10GB free disk space for data and metadata storage
- Understanding of SQL and data lake concepts including Parquet/ORC file formats and partitioning strategies
- Network access to ports 8080 (Trino), 9000 (MinIO API), and 9001 (MinIO Console)
- Basic knowledge of Hive Metastore concepts and table schemas for data catalog management
- Familiarity with S3 API concepts for bucket and object management in MinIO

## docker-compose.yml

```yaml
services:
  trino:
    image: trinodb/trino:latest
    container_name: trino
    restart: unless-stopped
    ports:
      - "${TRINO_PORT:-8080}:8080"
    volumes:
      - ./trino/catalog:/etc/trino/catalog:ro

  metastore-db:
    image: postgres:15-alpine
    container_name: metastore-db
    restart: unless-stopped
    environment:
      - POSTGRES_USER=hive
      - POSTGRES_PASSWORD=${METASTORE_DB_PASSWORD}
      - POSTGRES_DB=metastore
    volumes:
      - metastore_db_data:/var/lib/postgresql/data

  minio:
    image: minio/minio:latest
    container_name: trino-minio
    restart: unless-stopped
    ports:
      - "${MINIO_PORT:-9000}:9000"
      - "${MINIO_CONSOLE:-9001}:9001"
    environment:
      - MINIO_ROOT_USER=${MINIO_USER}
      - MINIO_ROOT_PASSWORD=${MINIO_PASSWORD}
    volumes:
      - minio_data:/data
    command: server /data --console-address ":9001"

volumes:
  metastore_db_data:
  minio_data:
```

## .env

```bash
# Trino Data Lake
TRINO_PORT=8080
METASTORE_DB_PASSWORD=hive_password
MINIO_PORT=9000
MINIO_CONSOLE=9001
MINIO_USER=minioadmin
MINIO_PASSWORD=minioadmin
```

## Usage notes

- Trino UI at http://localhost:8080
- MinIO Console at http://localhost:9001
- Configure catalogs in /etc/trino/catalog
- Create buckets for data lake storage

## Troubleshooting

- Trino query fails with 'Catalog not found': Verify catalog configuration files are mounted in /etc/trino/catalog and contain correct MinIO/Hive connection details
- MinIO connection refused errors: Check MINIO_ROOT_USER and MINIO_ROOT_PASSWORD environment variables match Trino catalog configuration credentials
- Hive Metastore connection timeouts: Ensure PostgreSQL metastore-db container is healthy and METASTORE_DB_PASSWORD is set correctly
- Out of memory errors during large queries: Increase Docker memory limits and configure Trino JVM heap size in jvm.config
- Table not found errors despite data in MinIO: Verify Hive Metastore contains table definitions and partition information matches actual file structure in MinIO buckets
- Permission denied accessing MinIO buckets: Check bucket policies and ensure Trino's MinIO credentials have read/write access to required buckets

