How do I connect to Presto?
A Presto connection, connections to and from Presto, fall into two main categories:
1) The Presto connectors that provide access to source data in various databases and file systems. The connectors are listed here: https://prestodb.io/docs/current/connector.html. One of the most commonly used Presto connectors is Hive which gives query access to HDFS and S3 data stored in a variety of formats.
2) Presto’s interfaces that allow Presto clusters to be accessed by tools and applications. There are four primary interfaces:
API
Presto’s HTTP API is the communication protocol between server and client. It’s used to send query statements for execution on the server and to receive results back to the client. See https://github.com/prestodb/presto/wiki/HTTP-Protocol for details and usage notes.
As an example., you can make a simple REST call to Presto to get a JSON dump of recently run queries using the syntax:
http://<prestoServerHost>:<port>/v1/query
The default port is 8080.
You can optionally specify a query ID – in this example the query ID is 20200926_204458_00000_68x9u:
Presto JDBC Driver
Presto can be accessed using SQL from Java using the JDBC driver. Download link is in the documentation: https://prestodb.io/docs/current/installation/jdbc.html. The following Presto JDBC connector URL connection string formats are supported:
http://myHost:8080/v1/query/20200926_204458_00000_68x9u
jdbc:presto://host:port
jdbc:presto://host:port/catalog
jdbc:presto://host:port/catalog/schema
Here’s example Java code to establish a connection to a Presto cluster:
String sql = "SELECT * FROM sys.node";
String url = "jdbc:presto://localhost:8080/catalog/schema";
try (Connection connection =
DriverManager.getConnection(url, "test", null)) {
try (Statement statement = connection.createStatement()) {
try (ResultSet rs = statement.executeQuery(sql)) {
while (rs.next()) {
System.out.println(rs.getString("node_id"));
}
}
}
}
Presto ODBC
Several free and paid-for options exist:
- Free: Prestogres is a gateway server that allows clients to use PostgreSQL protocol to run queries on Presto: https://github.com/treasure-data/prestogres/blob/master/README.md
- Paid: https://www.simba.com/drivers/presto-odbc-jdbc/
- Paid: https://www.cdata.com/drivers/presto/odbc/
How to Connect to Presto Using Java
Connecting to Presto with Java requires the Presto JDBC driver (see above). By using the Presto JDBC driver, you can easily access and query your Presto cluster from Java. It allows you to run Presto queries from Java applications and provides an easy-to-use API for managing your Presto cluster.
After downloading and adding the jar file to your development environment, you can make a connection to the Presto cluster. To do this, you’ll need to provide the connection string and credentials.
Here is an example of a connection string: jdbc:presto://host:port/catalog
You can also provide additional parameters such as the catalog and schema, for example: jdbc:presto://host:port/catalog/schema
Once connected, you can use a Statement object to execute SQL queries against the Presto cluster.
How to Connect Presto With Python
Python can be used to access Presto clusters, as long as the proper libraries are installed. The most popular library for connecting to Presto with Python is the PrestoDB library. This library provides a Python interface to the Presto HTTP API, allowing you to make queries and retrieve results from the Presto cluster.
The library can be installed with pip: pip install presto-python-client
Once this is done, you can use the library to make a connection to the Presto cluster. You will need to provide the URL of the Presto server and the catalog and schema to use. Then you can use the provided session object to execute SQL statements against the Presto cluster.
You can find this example in GitHub, to query the sys.node table:
import prestodb
conn=prestodb.dbapi.connect(
host=’localhost’,
port=8080,
user=’the-user’,
catalog=’the-catalog’,
schema=’the-schema’,
)
cur = conn.cursor()
cur.execute(‘SELECT * FROM system.runtime.nodes’)
rows = cur.fetchall()
Client libraries
Presto libraries for C, Go, Java, node.js, PHP, Python, R, Ruby are available at https://prestodb.io/resources.html#libraries
We hope the above information helps you with creating Presto Connections.