Configure PXF to access Teradata Database

Products

• VMware Tanzu Data Suite
• Greenplum
• VMware Tanzu Greenplum
• Pivotal Data Suite Non Production Edition
• VMware Tanzu Data Suite

Issue/Introduction

This article describes how to configure PXF for accessing a Teradata database

Resolution

1. Copy the Teradata JAR file to ${PXF_BASE}/lib directory

[gpadmin@cdw lib]$ cd ${PXF_BASE}/lib
[gpadmin@cdw lib]$ ls -ltr
total 5960
-rw-r--r-- 1 gpadmin gpadmin 4036257 Feb 23  2022 ojdbc8.jar
-rw-r--r-- 1 gpadmin gpadmin 1159293 Feb 23  2022 terajdbc4.jar
-rw-r--r-- 1 gpadmin gpadmin  898868 Mar  1  2022 sqljdbc42.jar
drwxr--r-- 2 gpadmin gpadmin       6 Mar  4  2024 native

 

2. Create the server directory in ${PXF_BASE}/servers, example below will use "tera_test" 

[gpadmin@cdw lib]$ cd $PXF_BASE}/servers/
[gpadmin@cdw servers]$ ls -ltr
total 4
drwxr-xr-x 2 gpadmin gpadmin   44 Feb 25  2022 qdw_test
drwxr--r-- 2 gpadmin gpadmin  198 Mar  4  2024 default
drwxr-xr-x 2 gpadmin gpadmin 4096 Dec  3 20:01 tera_test

 

3.  In $PXF_BASE/servers/tera_test, create the file jdbc-site.xml. Replace highlighted text with appropriate values

[gpadmin@cdw tera_test]$ cat jdbc-site.xml
<property>    
        <name>jdbc.driver</name>
        <value>com.teradata.jdbc.TeraDriver</value>
        <description>Class name of the JDBC driver</description>
</property>    

<property>    
        <name>jdbc.url</name>
        <value>jdbc:teradata://FQDN-of-Teradata-hostname/DATABASE=DBname,SSLMODE=ALLOW,TMODE=TERA,LOGMECH=LDAP,DBS_PORT=PortNumber</value>
        <description>The URL that the JDBC driver can use to connect to the database</description>
</property>    

<property>    
        <name>jdbc.user</name>
        <value>teradata user</value>
        <description>User name for connecting to the database</description>
</property>    

<property>    
        <name>jdbc.password</name>
        <value>xxxxxxxxx</value>
        <description>Password for connecting to the database</description>
</property>    

[gpadmin@cdw tera_test]$

 

4. Checked for installed extensions and user privileges on protocol PXF in the Greenplum database  

gpdb=# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description
---------+---------+------------+-------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 pxf     | 2.1     | public     | Extension which allows to access unmanaged data
(2 rows)

gpdb=# grant select on protocol pxf to user01;
GRANT
gpdb=# grant insert on protocol pxf to user01;
GRANT

 

5. Synchronise the PXF configuration.

[gpadmin@cdw tera_test]$ pxf cluster sync
Syncing PXF configuration files from coordinator host to standby coordinator host and 8 segment hosts...

[gpadmin@cdw tera_test]$ pxf cluster stop
Stopping PXF on coordinator host, standby coordinator host, and 8 segment hosts...
PXF stopped successfully on 10 out of 10 hosts

[gpadmin@cdw tera_test]$ pxf cluster start
Starting PXF on coordinator host, standby coordinator host, and 8 segment hosts...
PXF started successfully on 10 out of 10 hosts
[gpadmin@cdw tera_test]$

 

6.  Create an external table to read the table "SCHEMA.TABLE" in the teradata database. Change the schema and tablename to appropriate values.

gpdb=# \d tera_btest
External table "public.tera_btest"
 Column | Type | Modifiers
--------+------+-----------
 eff_dt | text |
 src_id | text |
Type: readable
Encoding: UTF8
Format type: text
Format options: delimiter '     ' null '\N' escape '\'
External options: {}
External location: pxf://SCHEMA.TABLE?PROFILE=JDBC&SERVER=tera_test
Execute on: all segments

gpdb=# select * from tera_btest;

Additional Information

See Example: Reading From and Writing to a PostgreSQL Table in the Greenplum documentation for further information.