Installing QueryGrid T2T on Teradata AWS Instance

Teradata Database on AWS
Teradata Employee

Installing QueryGrid T2T on Teradata AWS Instance

QueryGrid packages are included with the Teradata DDBS Instance, but are not pre-installed. Location of the RPM files:

Teradata QueryGrid Package

Location

Teradata to Teradata

/var/opt/teradata/querygrid_pkgs/t2t

Teradata to Hadoop

/var/opt/teradata/querygrid_pkgs/t2h


These packages can be installed manually or by using PUT. This document will provide step-by-step guide on installing QueryGrid T2T manually.

Current included T2T connector version is 15.00.01. T2T connector must also be installed on remote Teradata system (can be AWS or on-premise).

No data encryption in current generation of QueryGrild connectors.

    • If both systems are in AWS, use Amazon Virtual Private Cloud (VPC).
    • If one system is in AWS and other is outside AWS, use VPN connection.


Installation Instructions

1. Change directory to location of T2T packages.

# cd /var/opt/teradata/querygrid_pkgs/t2t

2. Install the base license package then the SQLT connector package.

# rpm -ivh tdsqlt_lic-01.00.00.00-1.x86_64.rpm

# rpm -ivh tdsqlt-15.00.01.02-1.x86_64.rpm

3. Change to the sqlt installation directory labeled with the .rpm distribution number.

# cd /opt/teradata/sqlt/15.00.01.02

4. Run the config.sh script.

# ./config.sh -l dbc -p dbcpassword > output.txt

This script installs SQLT functions and stored procedures to the database. Review the output file and ensure no errors.


Network Configuration

Use the following guidelines when defining Teradata to Teradata connections in AWS.


Teradata to Teradata within AWS

Network settings in the AWS security group:

    • Add an outbound rule allowing a local TD instance to connect to the remote TD instance on AWS, if none exists.
    • Add an inbound rule for the local TD instance's PTL listen port (default is 5000) allowing a remote TD instance to connect and transfer data to the local TD instance on the AWS.
    • Use the private IP address of the TD instance on AWS in the inbound rules.

Foreign server object definition:

    • Use the private IP address of the remote and local TD instances on AWS when creating foreign server objects.


Teradata Database on AWS to On-Premises Teradata

Network settings in AWS Virtual Private Cloud's (VPC) Access Control List (ACL):

    • Add an outbound rule into the AWS VPC's ACL to allow the TD instance in the network to connect to the on-premises TD system, if none exists.
    • Add an inbound rule into the AWS VPC’s ACL to allow the on-premises TD system to connect to the TD instance in the network.

Network settings in the AWS Security Group:

    • Add an outbound rule allowing a TD instance on AWS to connect to a remote TD system outside AWS, if none exists.
    • Add an inbound rule for the local TD instance’s PTL listen port (default is 5000) allowing a remote TD system outside AWS to connect and transfer data to the local TD instance on AWS.

Foreign server object definition:

    • Use the public IP address of the TD instance on AWS and the IP address of the on-premises Teradata system when creating foreign server objects.


Validating T2T Connection

1. On the remote system, with user DBC, create a proxy user, a test user, and grant CONNECT THROUGH on the proxy user to the test user.

.logon localhost/dbc,dbcpassword

create user tdawsproxyuser as password = tdawsproxyuser, permanent = 0;

create user tdawsuser as password = tdawsuser, permanent = 1E8;

grant connect through tdawsproxyuser to permanent tdawsuser without role;

grant all privileges on tdawsuser to tdawsuser with grant option;

2. With the newly created user, create a test table with sample data.

.logon localhost/tdawsuser,tdawsuser

create database tdawsuserdb from tdawsuser as permanent = 5E7 ;

database tdawsuserdb;

create table person (id int, name varchar(100), salary int);

insert into person values (1, 'john', '50000');

insert into person values (2, 'mary', '60000');

insert into person values (3, 'jane', '70000');

insert into person values (4, 'jim',  '80000');

insert into person values (5, 'kate', '90000');

select * from person order by id;

3. On the local system, create an authorization object which contains the proxy credentials.

.logon localhost/dbc,dbcpassword

GRANT CREATE SERVER ON TD_SERVER_DB TO dbc;

CREATE AUTHORIZATION TD_SERVER_DB.remote_tdaws_dbc_auth AS DEFINER TRUSTED USER 'tdawsproxyuser' PASSWORD 'tdawsproxyuser';

4. Create a foreign server object.

CREATE FOREIGN SERVER TD_SERVER_DB.remote_tdaws

EXTERNAL SECURITY DEFINER TRUSTED remote_tdaws_dbc_auth

USING Hosttype('Teradata')

-- Private IP of remote instance

remotehost ('172.31.17.18')

-- Private IP of local instance

local_ips('172.31.17.106')

-- Port on the local system to which the remote system can connect

port('5000')

read_timeout(200)

listen_timeout(120)

concurrentstreams(1)

DO IMPORT WITH syslib.load_from_td,

DO EXPORT WITH syslib.load_to_td ;

5. Create a user and grant SELECT and INSERT on the foreign server to this user.

create user tdawsuser as password = tdawsuser, permanent = 1E8 ;

GRANT SELECT ON TD_SERVER_DB.remote_tdaws TO tdawsuser;

GRANT INSERT ON TD_SERVER_DB.remote_tdaws TO tdawsuser;

6. Logon as the newly created user and run the following to validate T2T connection.

.logon localhost/tdawsuser,tdawsuser

-- Get foreign server information

HELP FOREIGN SERVER TD_SERVER_DB.remote_tdaws;

-- Import from a foreign server

SELECT * FROM tdawsuserdb.person@remote_tdaws order by id;

-- Export to a foreign server

INSERT INTO tdawsuserdb.person@remote_tdaws VALUES (6, 'tom', 10000);

-- Import from a foreign server

SELECT * FROM tdawsuserdb.person@remote_tdaws order by id;

1 REPLY
Teradata Employee

Re: Installing QueryGrid T2T on Teradata AWS Instance

We should install the below Teradata QueryGrid Packages on both the systems (remote and local).

# rpm -ivh tdsqlt_lic-01.00.00.00-1.x86_64.rpm


# rpm -ivh tdsqlt-15.00.01.02-1.x86_64.rpm

If we install the packages on only local system, then after completing the QueryGrid T2T installation, if you try to insert or select any data from remote system then you can see 3707 "syntax error" as below.

BTEQ -- Enter your SQL request or BTEQ command:

sel * from  vt1.test@aws1;

*** Error 7825 in UDF/XSP/UDM SYSLIB.contract_load_from_td: SQLSTATE 38U01: Import contract failed:  [Server Error] [Error 3707] [SQLState 42000] Syntax error, expected something like ';' between the word 'load_to_tdRemote' and  '('

 BTEQ -- Enter your SQL request or BTEQ command:

ins into vt1.test@aws1(1);

*** Error 7825 in UDF/XSP/UDM SYSLIB.contract_load_to_td: SQLSTATE 38U01:  Export contract failed: Server Error] [Error 3707] [SQLState 42000] Syntax error, expected something like ';' between the word 'load_from_tdRemote' and   '('