Presto Endpoint

Okera offers many different ways to access managed datasets. One of those options is the Presto Endpoint, which is powered by an embedded PrestoDB instance that is co-located on ODAS clusters.

The following sections explain how to access the Presto endpoint information and provide example client integrations.

Endpoint Information

The System Tab provides the necessary information about the endpoint:

Presto Endpoint Info

The two important values provided are:

  • Presto endpoint

    The embedded Presto cluster is listening to web requests at the specified HTTP(S) URL. It can be used for custom client integrations and to access the Presto WebUI. See Command-line Tools for another way to determine the Presto endpoint information.

  • JDBC url

    This info field contains the fully-specified JDBC database connection URL. It can be used for client integrations that require JDBC details.

    The URL is constructed like so: jdbc:presto://<presto-coord-fqdn>:<presto-coord-port>/<catalog-name> Note that the <catalog-name> varies for versions of ODAS: okera is used for version 2.x and newer, while recordservice is used for all 1.x versions of ODAS.

With the information provided in the ODAS WebUI, you can proceed to the client integration example, or keep reading the next sections for more detailed information.

Command-line Tools

Alternatively, you can use the okctl command-line tool to get the Presto endpoint details like so:

$ ./okctl endpoints
...
2020/03/19 10:48:22 presto-coordinator:api (type: NodePort)
2020/03/19 10:48:22   10.1.10.4:14050

Using the command-line tool may return the IP address instead of a DNS FQDN. Talk to your administrators to get the proper DNS entry or IP address to access the Presto endpoint. For instance, the cluster could be configured with an FQDN (as shown in the earlier screenshot) using specific cluster configuration keys:

$ ./okctl export-config 2>&1 | grep -i presto
  OKERA_EXTERNAL_PRESTO_HOST: odas.it-infra.corp.internal
  OKERA_EXTERNAL_PRESTO_PORT: "14050"
  PRESTO_API: 14050

Using this information developers could automated the way the Presto endpoint is determined, and use that in scripts to communicate with Presto, or set the endpoint and/or JDBC URL as values for generated client access configuration files.

Presto WebUI

Since Presto is providing its own web-based UI, you can use the provided web URL to access the coordinator interface using a web browser. See Presto WebUI

In this example https://odas.it-infra.corp.internal:14050 is the URL for the Presto UI and can be copied to the clipboard using the icon next to the Presto endpoint heading:

Copy Endpoint To Clipboard

Before you can access the UI, you need to authenticate with a username and password from your configured corporate directory (for example, MS Active Directory):

WebUI Login

If you have authenticated successfully, you are presented with the Presto WebUI, providing details about the Presto cluster itself as well as the currently and recently executed queries:

Presto Coordinator UI

The UI is mostly informational, but could be used to debug query issues. Since it requires a certain amount of knowledge and Presto training, the UI is usually only accessible to Database Administrators or other administrative staff members.

Client Integration

After having gathered the information about the Presto endpoint and its database connection URL, it is now possible to connect generic JDBC clients.

Note

The following applies to installations where the ODAS cluster is configured with a properly signed TLS/SSL certificate. If you are using the self-signed certificate in a non-production environment, also see the steps in Custom JDBC JAR.

The following shows how to use the JDBC endpoint from popular SQL clients.

DBeaver

DBeaver is a free Universal Database Tool that can be used to connect to the ODAS JDBC endpoint and query ODAS managed databases.

After installing DBeaver, you are presented with the main window, where you can click on the "New Database Connection" button, which is the first in the toolbar on the top left. It will open the "Connect to database" wizard, where you need to enter "presto" to narrow down the choice.

DBeaver Main Window

Select "PrestoDB" and click on the "Next>" button, which will proceed the wizard to the PrestoDB details page. Enter the "Host", "Port", "Database/Schema", "User name", and "Password" information as per the ODAS cluster of your choice. The credentials are your personal credentials as configured in your corporate directory (that is, MS Active Directory or similar):

PrestoDB Details

On the same page, click the "Driver properties" tab. When this is done for the first after installing DBeaver, a dialog will open asking to download the Presto JDBC JAR file. Click on "Download" and wait for the process to complete:

Download Presto JDBC JAR

In the "Driver properties" tab click on the plus button at the bottom left of the list. In the "Property Name" dialog enter SSL and press the "OK" button:

Create Driver Property

Click on the "Value" column next to the newly created property and enter true, followed by pressing the "Enter" button. This should result in the following:

Enter Property Value

Now, click on the "Finish" button of the wizard and return to the main window of DBeaver, with the "PrestoDB - okera" connection added. Click on the little arrow next to it to open the list of catalogs and databases/schemas:

Main Window With Connection

You can now use DBeaver to query any dataset like okera_sample.users (as shown in the previous screenshot).

In the Advanced parameters of the Presto driver, make sure you have the Escape LIKE masks in search queries checked (as shown in the screenshot below).

escape like mask param

DBeaver with Self-Signed Certificates

While the above works for all clients talking to ODAS with properly signed certificates (that is, the client can verify the certificate chain to a trusted root CA) installed, it may not work for all clients. This is surfaced as a certificate validation error and will cause the client to stop work any further.

For Presto, the original JDBC JAR file that contains the driver code, there is unfortunately no way to configure the support of self-signed certificates.

Note

When using Tableau, which is supplying a custom JDBC driver to its users, the steps to disable the certificate check is explained in our JDBC and Tableau blog post.

Okera provides a modified version of the Presto JDBC JAR that is the same as the original but adds the support to disable the certificate check. The driver can be downloaded using the following URL:

https://okera-release-useast.s3.amazonaws.com/1.5.0/client/presto-jdbc-0.214.jar

It adds an additional driver property called SSLAllowSelfSignedCert, which must be set to true in the case DBeaver (or any other tool using the original Presto JDBC JAR file).

The first step is to replace the original driver with the Okera provided one. You can either go the "Database - Driver Manager" menu and modify the global PrestoDB settings, or simply create a new connection as explained above, while clicking on "Edit Driver Settings" in the main page of the connection wizard. Delete the original value, pointing to the downloaded Maven repo version:

Default Driver JAR

Then add the downloaded driver provided by Okera by clicking on the "Add File" button and selecting the JAR file from the location where it is stored:

Custom Driver JAR

Then in the "Driver properties" tab, also add the new property as explained above:

Extra Driver Properties

Once you confirm the wizard or dialog, you can now connect to ODAS clusters with self-signed certificates.

Note

Okera strongly advises all users of the software to install a properly signed and company managed certificate. Not doing so effectively disables the SSL check and leaves the connection vulnerable for possible exploits.

SQLWorkbench/J

Another popular tool is the free SQLWorkbench/J, which is written in Java and requires a working Java runtime to be installed. You then download the SQLWorkbench/J archive as a single file and unpack it into a place of our choice.

Once started, it presents the main window and also opens the "Select Connection Profile" dialog so that you can set up a first database connection:

SQLWorkbench Main Window

The first step is to provide a driver JAR file so that SQLWorkbench/J can connect to it via JDBC. Okera suggests to its own Presto driver JAR file, as it adds additional options (see below). You can download the driver file from the following URL:

https://okera-release-useast.s3.amazonaws.com/1.5.0/client/presto-jdbc-0.214.jar

In the "Select Connection Profile" dialog, click on the "Manage Drivers" button at the bottom left. Then click on the "New Driver" button (the white paper icon) at the top left and enter a new name for the driver, for example, Okera. Next click on the "File Open" button (the yellow folder with green arrow icon) at the right side and select the location and JAR file of the Presto driver you downloaded.

Ensure that the "Classname" is set to com.facebook.presto.jdbc.PrestoDriver and click on the "OK" button:

Manage Driver

Back in the "Select Connection Profile" dialog, enter a name for the connection in the main field, for example, ODAS Cluster. Select the newly created driver, for instance, Okera from the dropdown list, and paste the JDBC connection URL as copied from the ODAS cluster WebUI (see Endpoint Information). Also enter a username and password from your corporate directory service.

Note

You need to check the "Autocommit" box underneath the "Password" field, or else SQLWorkbench/J will report pending transaction errors.

Finally, click on the "Extended Properties" button and create a new property SSL with the value true by clicking on the "New Property" button (the white paper icon at the top left of the dialog), then click the "OK" button:

Connection Details

If all is correct, you can, for example, use the "Tools - Show Database Explorer" to browse the ODAS managed datasets and look at the data:

Database Explorer

SQLWorkbench/J with Self-Signed Certificates

As explained for DBeaver earlier, the above will work as-is with ODAS clusters using properly signed TLS/SSL certificates. For non-production cluster it may be that self-signed certificates are used for the sake of simplicity (ODAS can automatically create those).

Note

Okera strongly advises all users of the software to install a properly signed and company managed certificate. Not doing so effectively disables the SSL check and leaves the connection vulnerable for possible exploits.

In that case, you need to add the same extra property to the connection to ignore errors when the self-signed certificate is validated.

Select the "Open - Connect window" menu and select the ODAS Cluster connection. Click on the "Extended Properties" button again and add the property named SSLAllowSelfSignedCert with the value of true, then click the "OK" button for this and the main dialog to accept the change:

Connection Details

After that SQLWorkbench/J will be able to work with cluster using self-signed certificates.