Because PXF accesses Trino using the JDBC connector, this example works for all PXF 6.x versions.
In this example, you:
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');
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 coordinator host:
$ ssh gpadmin@<coordinator>
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
:
If you did not relocate $PXF_BASE
, run the following from the Greenplum coordinator:
gpadmin@coordinator$ cd /usr/local/pxf-gp<version>/lib
gpadmin@coordinator$ wget <url-to-trino-jdbc-driver>
If you relocated $PXF_BASE
, run the following from the Greenplum coordinator:
gpadmin@coordinator$ cd $PXF_BASE/lib
gpadmin@coordinator$ wget <url-to-trino-jdbc-driver>
Synchronize the PXF configuration, and then restart PXF:
gpadmin@coordinator$ pxf cluster sync
gpadmin@coordinator$ 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@coordinator$ pxf cluster sync
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)
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)