Teradata AWS Access
It was previously explained by ourselves here at VLDB how to setup a Teradata MPP (multi-node) system on Amazon Web Services (AWS). Well, now we’re back to offer a simple step-by-step guide on how to setup basic SSH connections, install Teradata’s Tools & Utilities (TTU) and setup a desktop SQL client for a Teradata instance running on AWS.
SSH & Key Pairs Identity
When you first setup your server on AWS, you are required to have created a key pairs identity. With this, you can start accessing your server straight away. There are multiple ways to connect, though one of the more common is via the SSH tool.
– Mac –
If you are connecting via a terminal, then the command will be similar to the usual connection string:
ssh -i KEY_PAIRS_FILE ec2-user@AWS_SERVER_IP
However, you may need to first alter the permissions of the key pairs file. If the access is open to any user the connection will reject the file input. This can be done as follows:
chmod 400 KEY_PAIRS_FILE
Note that the ec2-user is the default user for most Linux systems. Once logged on as ec2-user, you can gain access to root with the command sudo su -.
If you find yourself logging on and off a lot, it may be conveniant to set your key pairs file path as an environment variable for ease of use.
– Windows –
If you are connecting via PuTTY on Windows, then you are required to first modify the key pairs file using PuTTY-Gen. Typically this piece of software is downloaded alongside PuTTY, but you may need to download it separately.
Once you’ve loaded up the PuTTY-Gen software, click Load on the right and then select your key pairs file. In the bottom right you may need to change the file type that is visible (default is .ppk and the key pairs file is a .pem).
Once loaded, it should pop up with a message saying Successfully Imported… Once you have this, click Save Private Key in the bottom right and save the file. This will now be a usable key pairs file for PuTTY’s use.
Now, open up PuTTY.
On the left side in the list, expand Connection, and then SSH. Now click on Auth and you should see a view on the right titled Options controlling SSH authentication.
From here, click on Browse in the bottom right and then select the key pairs file you just created.
Now go back to the main page by clicking on Session at the top of the list on the left. Here, put in your server IP address as normal and hit Open.
Note that it may be of convenience to save the session details before opening the connection for ease of use at a later time.
– Password Authenticated Login –
If you require password authentication to be enabled, then it will be a simple edit of a system config file and a restart of the SSH service on the AWS server.
First, edit the file /etc/ssh/sshd_config, and then find and change the following line:
Note that you should also remove the hash (#) symbol from in front of the parameter if there is one. Once the file is edited as above, you will then need to restart the SSH service as follows to commit the change.
service sshd restart
Once the service is restarted, password authentication should be updated and enabled on your system.
Teradata’s Tools and Utilities (TTU)
It is very useful to understand that all Teradata servers on AWS come with the equivalent TTU version packaged for Linux, Mac and Windows. With your key pairs file to hand, you can use it, similarly to SSH access, to download the file using SCP.
But first you will need to make the file(s) available to the required user for download (ec2-user in our example), as by default only root can access the files.
First, logon onto the server using your SSH access as ec2-user. Change to the root user and then do [something similar to] the following commands:
cp /var/opt/teradata/TTU_pkgs/Teradata*MACOSX* /home/ec2-user/
chown ec2-user /home/ec2-user/Teradata*MACOSX*
Note that you will need to replace the name MACOSX with Linux or windows if you require those instead.
– Mac –
Once done, use the following command to retrieve the file for your Mac via terminal:
scp -i KEY_PAIRS_FILE ec2-user@SERVER_IP:/home/ec2-user/Teradata*MACOSX* .
– Windows –
The simplest way to download the package on Windows is to use the WinSCP software. For this you will again require the key pairs (.ppk) file that you created using PuTTY-Gen.
First, open up WinSCP.
From here, click on Advanced on the right which will pop up a new window. In the list on the left, click on Authentication. Similarly to PuTTY, click on the […] icon under Private key file and select the key pairs .ppk file, and then click OK.
Now you can input your IP address and user name as per usual, and then Login. Once logged in, you can freely move around your local and server directories to download the files as required.
Note that it may be of convenience, similarly to PuTTY, to save the session details before opening the connection for ease of use at later time.
– Installation –
Once you have downloaded the required packages, it is a simple matter of unpackaging the files and running the setup script contained within to install the TTU software.
For example, on a Mac terminal locate the TTU package and then run the following commands:
tar -xvf Teradata*MACOSX*
Note that using tar as above will result in all files being placed in the directory you are located in. Additionally, using the a parameter for the setup script will install all available TTU software.
Once installation is complete, you should be able to use BTEQ from your terminal to connect to your Teradata instance (however you may need to re-load your terminal first).
Teradata Studio Express
Whilst there are a wide variety of desktop SQL clients available that are compatible with Teradata, and you may very well have your preference out of them all, this will be a quick setup and overview of Teradata Studio Express.
Teradata Studio Express is openly available for download on Windows, Mac and Linux platforms from the following website address:
Note that for this software to install successfully, Java Developer Kit must be installed.
Once downloaded, simply unzip the package and run the installer. This will take you through a basic on-screen installation process that typically takes less than a minute. Once finished, the software is ready to use.
When first launching Teradata Studio Express you will be greeted with a Quick Tour guide for the studio. Quickly run through the guide to help you understand the basics (if required) and then continue to the main view.
From the main view, simply right-click Database Connections at the top of the far left panel and click New…. In this window, click Teradata, name the connection as you wish at the bottom under Name: and then hit Next.
In this next view you will see various parameters to fill in, and some pre-defined as well. Unless required, we would recommend leaving the pre-set parameters as default, only altering the following:
- Database Server Name – The IP or server name to connect too.
- User Name – The name of the user that you wish to connect to Teradata with.
- Password – The password for the respective user chosen above.
- Database – Defaults to user’s default if not specified. All database hierarchy existing under the specified (or default) database will be available for viewing once the connection is made.
Once the details required have been completed, you may click Next to get an overview of the details you’ve specified, or you may directly Finish the connection setup. At this point, you should have a successful connection in the far left panel for you to view. Any changes required to an existing connection can be made by simply right-clicking the connection name and clicking Properties.
With the connection now open, you may easily run queries against the database via the SQL Editor in the top panel on the right. Our basic example shows the following query being run:
The query may be executed by simply clicking on the green triangle in the top right (or by using the shortcut CTRL + ALT + X on a Windows environment, or CMD + ALT + X on a Mac).
Results may then be viewed in the middle panel titled Teradata Result Set Viewer, with history available for all previously executed queries in the bottom panel, suitably titled Teradata SQL History.
Whilst this should cover the basics required to use Teradata Studio Express, there are of course many useful features that can be utilised: One such simple feature is the Reexecute Statement command. By right-clicking on any single row in the Teradata SQL History, you may commit Reexecute Statement, which, as the term suggests, re-executes the selected query statement against the database, and will return results as per usual in the Teradata Result Set Viewer.
And there you have it! A simple step-by-step run through on the basic setup of connections, utilities and client tools that may be required by yourselves for any Teradata instance running on AWS.