Run Presto SQL Across Data Sources

This guide shows you how to use Presto (now Trino) to run a single analytical SQL query that joins data from multiple sources—like a MySQL database, a CSV file in S3, and a Kafka stream—without moving or copying the data. You’ll learn the core steps, common pitfalls, and how to fix issues when they arise.

What You’ll Need

• A running Presto/Trino cluster (single-node works for testing) with coordinator and workers accessible.
• Access to the data sources you want to query (database credentials, file paths, or stream endpoints).
• The Presto CLI or a SQL client (like DBeaver) configured to connect to your Presto coordinator.
• Basic familiarity with SQL (JOIN, SELECT, GROUP BY).

Step-by-Step

1. Start Presto and verify it’s running. If you installed it locally, run bin/launcher start and check the log at var/log/server.log for “SERVER STARTED”. For Docker, use docker run -p 8080:8080 trinodb/trino.
2. Connect to Presto. Use the CLI: ./presto --server localhost:8080 --catalog mysql --schema yourdb. You should see the presto:yourdb> prompt.
3. Configure connectors. Presto uses connectors to talk to different data sources. Each connector has a catalog name. For example, to query MySQL, create etc/catalog/mysql.properties with:

   connector.name=mysql
   connection-url=jdbc:mysql://localhost:3306
   connection-user=root
   connection-password=secret

   For Hive (for files in S3/HDFS), create hive.properties with your metastore URI. For Kafka, create kafka.properties with broker addresses.

4. Verify each catalog is visible. Run SHOW CATALOGS; in the CLI. You should see mysql, hive, kafka, and system. If a catalog is missing, check the connector properties file and restart Presto.
5. Explore the tables in each catalog. Run SHOW TABLES FROM mysql.yourdb; and SHOW TABLES FROM hive.default; to confirm the tables you need are accessible.
6. Write a cross-catalog query. Use the fully qualified table name: catalog.schema.table. For example:

   SELECT u.id, u.name, o.total
   FROM mysql.yourdb.users u
   JOIN hive.default.orders o ON u.id = o.user_id;

   Presto will push down filters and aggregations where possible, but the join happens in Presto’s engine.

7. Run the query. Execute it in the CLI. Presto will plan the query, assign tasks to workers, and return results. Watch the output for row counts and execution time.
8. Optimize for performance. If the query is slow, check if you can push down more work. Use EXPLAIN to see the plan. For large data, consider using WITH clauses to pre-aggregate each side before joining.

Common Mistakes

• Forgetting the catalog prefix. In Presto, you must always use catalog.schema.table. Using just schema.table will fail.
• Mixing case sensitivity. Presto is case-sensitive for table and column names. If your MySQL table uses lowercase, but Hive uses uppercase, you must quote them with double quotes.
• Assuming pushdown happens. Not all connectors support pushdown of joins or aggregations. If you join two huge tables, Presto will pull all data to its workers, which can be slow.
• Ignoring type mismatches. Joining a VARCHAR column to an INT column will fail. Use CAST to convert types.

Tips

• Use EXPLAIN to understand how Presto executes your query. Look for remote exchange to see where data is shuffled.
• For repetitive queries, create a view in Presto: CREATE VIEW my_view AS SELECT ... to hide the catalog complexity.
• If you only need a few columns, select only those—Presto will push down column pruning to many connectors.
• Use the --catalog flag in the CLI to set a default catalog, so you can write schema.table for that catalog.
• Test with a small LIMIT first to verify the query works before running it on the full dataset.

Troubleshooting

• “Catalog does not exist” error. Check that the connector properties file is in etc/catalog with a .properties extension. Restart Presto after adding a catalog.
• Connection refused to data source. Verify the data source is reachable from the Presto coordinator and workers. Test with a simple query like SELECT * FROM mysql.yourdb.table LIMIT 1.
• Query hangs or is extremely slow. Check if the data source is under load. For Hive, ensure the metastore is running. For Kafka, check consumer group offsets. Try a simpler query to isolate the issue.
• Type mismatch errors. Use CAST to convert types. For example, CAST(u.id AS VARCHAR) to join with a string column.
• “Table not found” despite it existing. Confirm the schema name. In Hive, the default schema is often default. In MySQL, it’s the database name. Run SHOW SCHEMAS FROM catalog; to list schemas.

Frequently Asked Questions

Can Presto query data from a CSV file without Hive?

Yes, you can use the memory connector to load a CSV into memory, but for production use the Hive connector with a metastore, or the file connector for local files (though it’s limited).

Do I need to move data to Presto?

No, Presto queries data in place. It does not store data itself; it only reads from the source systems.

Is Presto the same as Trino?

Presto was renamed to Trino in 2020. The original Presto project continues as PrestoDB. This guide applies to both, but check your version’s documentation for any differences.

Can I write to the data sources?

Presto is primarily for analytics; write support is limited. Some connectors (like MySQL) support INSERT, but it’s not recommended for heavy writes.

How do I secure cross-source queries?

Use Presto’s authentication and authorization features. Each connector can have its own credentials, and you can restrict access via views and grants.

What if my data source doesn’t have a connector?

You can write a custom connector using the Presto SPI, or use the generic JDBC connector if your source provides a JDBC driver.

Related

• Old Phone Robot: Secure & Prep Guide
• iOS 18 Apps Crashing? Fixes That Work
• How to Set a Windows Service to Start Manually