Example: Reading From and Writing to a Trino (formerly Presto SQL) Table

Because PXF accesses Trino using the JDBC connector, this example works for all PXF 6.x versions.

In this example, you:

Create an in-memory Trino table and insert data into the table
Configure the PXF JDBC connector to access the Trino database
Create a PXF readable external table that references the Trino table
Read the data in the Trino table using PXF
Create a PXF writable external table the references the Trino table
Write data to the Trino table using PXF
Read the data in the Trino table again

Create a Trino Table

This example assumes that your Trino server has been configured with the included memory connector. See Trino Documentation - Memory Connector for instructions on configuring this connector.

Create a Trino table named names and insert some data into this table:

> CREATE TABLE memory.default.names(id int, name varchar, last varchar);
> INSERT INTO memory.default.names(1, 'John', 'Smith'), (2, 'Mary', 'Blake');

Configure the Trino Connector

You must create a JDBC server configuration for Trino, download the Trino driver JAR file to your system, copy the JAR file to the PXF user configuration directory, synchronize the PXF configuration, and then restart PXF.

This procedure will typically be performed by the Greenplum Database administrator.

Log in to the Greenplum Database master host:
```
$ ssh gpadmin@<gpmaster>
```
Download the Trino JDBC driver and place it under $PXF_BASE/lib. If you relocated $PXF_BASE, make sure you use the updated location. See Trino Documentation - JDBC Driver for instructions on downloading the Trino JDBC driver. The following example downloads the driver and places it under $PXF_BASE/lib:
1. If you did not relocate $PXF_BASE, run the following from the Greenplum master:
```
gpadmin@gpmaster$ cd /usr/local/pxf-gp<version>/lib
gpadmin@gpmaster$ wget <url-to-trino-jdbc-driver>
```
2. If you relocated $PXF_BASE, run the following from the Greenplum master:
```
gpadmin@gpmaster$ cd $PXF_BASE/lib
gpadmin@gpmaster$ wget <url-to-trino-jdbc-driver>
```

Synchronize the PXF configuration, and then restart PXF:

gpadmin@gpmaster$ pxf cluster sync
gpadmin@gpmaster$ pxf cluster restart

Create a JDBC server configuration for Trino as described in Example Configuration Procedure, naming the server directory trino. The jdbc-site.xml file contents should look similar to the following (substitute your Trino host system for trinoserverhost):

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>io.trino.jdbc.TrinoDriver</value>
        <description>Class name of the JDBC driver</description>
    </property>
    <property>
        <name>jdbc.url</name>
        <value>jdbc:trino://trinoserverhost:8443</value>
        <description>The URL that the JDBC driver can use to connect to the database</description>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>trino-user</value>
        <description>User name for connecting to the database</description>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>trino-pw</value>
        <description>Password for connecting to the database</description>
    </property>

    <!-- Connection properties -->
    <property>
        <name>jdbc.connection.property.SSL</name>
        <value>true</value>
        <description>Use HTTPS for connections; authentication using username/password requires SSL to be enabled.</description>
    </property>
</configuration>

If your Trino server has been configured with a Globally Trusted Certificate, you can skip this step. If your Trino server has been configured to use Corporate trusted certificates or Generated self-signed certificates, PXF will need a copy of the server’s certificate in a PEM-encoded file or a Java Keystore (JKS) file.

Note: You do not need the Trino server’s private key.

Copy the certificate to $PXF_BASE/servers/trino; storing the server’s certificate inside $PXF_BASE/servers/trino ensures that pxf cluster sync copies the certificate to all segment hosts.

$ cp <path-to-trino-server-certificate> /usr/local/pxf-gp<version>/servers/trino

Add the following connection properties to the jdbc-site.xml file that you created in the previous step. Here, trino.cert is the name of the certificate file that you copied into $PXF_BASE/servers/trino:

<configuration>
...
    <property>
        <name>jdbc.connection.property.SSLTrustStorePath</name>
        <value>/usr/local/pxf-gp<version>/servers/trino/trino.cert</value>
        <description>The location of the Java TrustStore file that will be used to validate HTTPS server certificates.</description>
    </property>
    <!-- the following property is only required if the server's certificate is stored in a JKS file; if using a PEM-encoded file, it should be omitted.-->
    <!--
    <property>
        <name>jdbc.connection.property.SSLTrustStorePassword</name>
        <value>java-keystore-password</value>
        <description>The password for the TrustStore.</description>
    </property>
    -->
</configuration>

Synchronize the PXF server configuration to the Greenplum Database cluster:
```
gpadmin@gpmaster$ pxf cluster sync
```

Read from a Trino Table

Perform the following procedure to create a PXF external table that references the names Trino table and reads the data in the table:

Create the PXF external table specifying the jdbc profile. Specify the Trino catalog and schema in the LOCATION URL. The following example reads the names table located in the default schema of the memory catalog:
```
CREATE EXTERNAL TABLE pxf_trino_memory_names (id int, name text, last text)
LOCATION('pxf://memory.default.names?PROFILE=jdbc&SERVER=trino')
FORMAT 'CUSTOM' (formatter='pxfwritable_import');
```

Display all rows of the pxf_trino_memory_names table:

gpadmin=# SELECT * FROM pxf_trino_memory_names;
 id | name | last
----+------+-------
  1 | John | Smith
  2 | Mary | Blake
(2 rows)

Write to the Trino Table

Perform the following procedure to insert some data into the names Trino table and then read from the table. You must create a new external table for the write operation.

Create a writable PXF external table specifying the jdbc profile. For example:

gpadmin=# CREATE WRITABLE EXTERNAL TABLE pxf_trino_memory_names_w (id int, name text, last text)
          LOCATION('pxf://memory.default.names?PROFILE=jdbc&SERVER=trino')
          FORMAT 'CUSTOM' (formatter='pxfwritable_export');

Insert some data into the pxf_trino_memory_names_w table. For example:

gpadmin=# INSERT INTO pxf_trino_memory_names_w VALUES (3, 'Muhammad', 'Ali');

Use the pxf_trino_memory_names readable external table that you created in the previous section to view the new data in the names Trino table:

gpadmin=# SELECT * FROM pxf_trino_memory_names;
 id |   name   | last
----+----------+-------
  1 | John     | Smith
  2 | Mary     | Blake
  3 | Muhammad | Ali
(3 rows)