Connecting to Presto server

Pre-requisites

Connecting to Presto engine using Presto CLI (Remote)

1. Download the Presto executable jar from https://prestodb.io/docs/current/installation/cli.html

2. Rename the downloaded file as presto. Make it executable with chmod +x and run it.

3. To check whether Presto CLI is installed, run ./presto --version. Presto cli version displays. For example, Presto CLI 0.281-cfbc6eb

4. Run one of the following commands in the system where Presto CLI is installed.

   <your-username> is optional if you have multiple connections with different users and want to differentiate them.

   1. If you are using API key, run the following command.

      ./presto --server <https://Prestoengine host details> --catalog iceberg_data --schema default --user ibmlhapikey_<your-username> --password
      

   2. If you are using IBM IAM token, run the following command.

      ./presto --server <https://Prestoengine host details> --catalog iceberg_data --schema default --user ibmlhtoken_<your-username> --password
      

5. Enter your IBM API key or IBM IAM token at the prompt.

6. At the Presto prompt, type show catalogs;. The catalog list appears. Now you are connected to Presto engine in watsonx.data through Presto CLI.

   presto:default> show catalogs;
   Catalog
   --------------
   iceberg_data
   jmx
   system
   tpcds
   tpch
   (5 rows)
   

Connecting to Presto engine using Java/JDBC

1. Download and install the latest JDBC drivers on the client machine.

2. Add the downloaded jar file to the class path of your Java application.

3. Use your ibmlhapikey as username and API key as password. For more information, see Getting IBM API Key.

4. Get the hostname and port. For more information, see Getting the Presto engine hostname and port details.

5. Create a Java application by using JDBC interface. Following is an example for Java snippet:

   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.Statement;
   import java.util.Properties;
   
   public class PrestoJdbcSample
   {
       public static void main(String[] args) throws Exception
       {
           /*
            * example of fetching the location and credentials needed to connect, from
            * environment variables
            */
           String username = System.getenv("ENG_USERNAME");
           String password = System.getenv("ENG_PASSWORD");
           String hostname = System.getenv("ENG_HOST");
           String portnumber = System.getenv("ENG_PORT");
           String presto_url = "jdbc:presto://" + hostname + ":" + portnumber;
           Connection connection = null;
           Statement statement = null;
           ResultSet resultSet = null;
           try
           {
               /* load the Presto JDBC Driver class  */
               String driverClass = "com.facebook.presto.jdbc.PrestoDriver";
               Class.forName(driverClass);
               /* Set the connection properties */
               Properties properties = new Properties();
               properties.setProperty("user", username);
               properties.setProperty("password", password);
               properties.setProperty("SSL", "true");
               /* Connect */
               connection = DriverManager.getConnection(presto_url, properties);
               /* Issue a Query */
               String query = "SELECT * FROM tpch.tiny.customer LIMIT 10";
               statement = connection.createStatement();
               resultSet = statement.executeQuery(query);
               /* iterate through the results */
               while (resultSet.next())
               {
                   String phone = resultSet.getString("phone");
                   String name = resultSet.getString("name");
                   System.out.println("phone = " + phone + ", name = " + name);
               }
           }
           catch (Exception e)
           {
               e.printStackTrace();
           }
           finally
           {
               /* clean up at the end always **/
               if (resultSet != null)
               {
                   resultSet.close();
               }
               if (statement != null)
               {
                   statement.close();
               }
               if (connection != null)
               {
                   connection.close();
               }
           }
       }
   }
   

   Replace the parameters in the command with the following: <PRESTO_URL> Identifies the jdbc URL to the Presto server. <EMAIL_ID> with your email ID <API_KEY> with the API key that you downloaded from IBM Cloud. If you are using IBM IAM token, replace ibmapikey with ibmlhtoken and pass the token.

6. Compile and run the command.

Connecting to Presto engine using Python scripts

1. Install python 3.x (3.10 or later recommended) and pip3 on your client workstation.

2. Use the DBAPI interface to query Presto. Following is a sample python script.

   import os
   import prestodb
   username=os.environ["ENG_USERNAME"]
   password=os.environ["ENG_PASSWORD"]
   hostname=os.environ["ENG_HOST"]
   portnumber=os.environ["ENG_PORT"]
      with prestodb.dbapi.connect(
      host=hostname,
      port=portnumber,
      user=username,
      catalog='tpch',
      schema='tiny',
      http_scheme='https',
      auth=prestodb.auth.BasicAuthentication(username,password)
      ) as conn:
   cur = conn.cursor()
   cur.execute('select * from tpch.tiny.customer limit 10')
   rows = cur.fetchall()
   print(rows)
   
   

   The command queries the system.runtime.nodes system tables that show the nodes in the Presto cluster. The DBAPI implementation in prestodb.dbapi provides methods to retrieve few rows. For example, Cursorfetchone() or Cursor.fetchmany(). By default Cursor.fetchmany() fetches one row. Set prestodb.dbapi.Cursor.arraysize accordingly.

3. Compile and run the command.