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:
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 coordinator host:
-
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: -
If you relocated
$PXF_BASE
, run the following from the Greenplum coordinator:
-
-
Synchronize the PXF configuration, and then restart PXF:
-
Create a JDBC server configuration for Trino as described in Example Configuration Procedure, naming the server directory
trino
. Thejdbc-site.xml
file contents should look similar to the following (substitute your Trino host system fortrinoserverhost
): -
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 thatpxf cluster sync
copies the certificate to all segment hosts.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
: -
Synchronize the PXF server configuration to the Greenplum Database cluster:
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 theLOCATION
URL. The following example reads thenames
table located in thedefault
schema of thememory
catalog: -
Display all rows of the
pxf_trino_memory_names
table:
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: -
Insert some data into the
pxf_trino_memory_names_w
table. For example: -
Use the
pxf_trino_memory_names
readable external table that you created in the previous section to view the new data in thenames
Trino table:
Content feedback and comments