Teradata Cookbook: Over 85 recipes to implement efficient data warehousing solutions

What if you want to install on your local machine/desktop without VMware? It's easy, all you need to do is create a login account at http://developer.teradata.com/ and then go to the download link. Under tools, search for Teradata Studio Express.

 For a Windows machine:

Here are some highlights of Studio and Express:

You need to have an Azure subscription. Log in to the Azure marketplace and create a free account. It is recommended to have a subscription-based account to have enough cores available to you. Following are some prerequisites:

Teradata on Azure gives you the same performance as on premises. You can get same power of analytic database as you get from an on-premises database. Once on the cloud you can configure your system and can take advantage of the additional Azure applications, as well, to monitor and create system alerts.

If you are stuck with any of the issues when performing deployment, it is advisable to contact the Azure help desk via your dashboard. A ticket will be created and the issue will be resolved based on your subscription type:

Once you deploy your Teradata Database, from time to time you need to upgrade or apply patches to the database. You need to download these from the Teradata access portal and, based on your license tiers, Teradata engineering will help you.

There are four license tiers available as of now:

We will be establishing a JDBC data connection to a local database:

If your site/client is using an LDAP to log on, you need to choose an LDAP mechanism and enter your LDAP credentials.

You are now connected to the Teradata Database; start firing up your queries using SLQ Editor.

/*Sample query*/
sel * from dbc.dbcinfo;

This is a heavy tool, so it will consume more resources from your PC compared to the handy and breezy SQL Assistant. We'll be exploring SQL Assistant in the later part of this chapter.

Humans and software—we judge them on the basis of their characteristics. Let's explore some of the important options available with Teradata Studio Express.

Using Studio tool options

Here are some of the tool options that we will cover:

• SQL compare: Comparing two text documents is the basic necessity of the coding community. There are many tools for this job. However, when you have a built-in feature for it, you don't need to go anywhere else. You ask how to use it? Click on the Project Explorer tab (next to the Data Explorer tab). Right-click on the SQL tab and create SQL text files that you need to compare. Select both files and select the Compare With | Each Other option. The text that is different will be highlighted and shown:

Different text highlighted in the Text Compare  

• Code assist and syntax checking: Forgot the names of the tables in the database, a column name, or the syntax of your statement? Express will prompt as you type. Type EXEC for macro execution with the code assist ON and you will get a series of options that will go with the main command. Now, when you write a database name followed by a dot, it will give you a list of tables to choose from that database:

• Object Viewer: This works in a similar to the traditional tool SQL Assistant; however, it can help to get more details for your object of interest. If you have Studio, it will have even more options for admin use. Right-click on any object from the Explorer. You will see all the options affiliated with the object you need:

Options affiliated with the selected option

There are many other options at your disposal that you can explore.

For SQLA to work, you need to download it from the developer.teradata.com website and download Teradata Tools and Utilities, also known as TTU. Once downloaded and installed, start making the connection using ODBC, .NET, or JDBC.

 Here, we will use SQLA 16.0. It is highly recommended that you use the TTU version that matches or is higher than the major release version number of the Teradata Database that you intend to connect with. So, if you are on Teradata Database 15.10, use TTU 15.10 or 16. TTU 13.10 is not recommended. Now that we have this covered, let's move on to the connection.

Use the following steps to establish a connection:

Wouldn't it be great to have an option to run SQL using the command-line interface with SQLA?

Teradata SQLA allows you to connect to a data source using the ODBC and .NET framework. When you install SQLA from TTU toolkit, make sure to check all the options. You will have two executables:

Here are some shortcuts to make your daily job easier:

You need to open the SQLA.

SQLA is a simple and efficient tool that helps users with their daily work. With better configuration, this tool can be used effectively.

SQLA also provides a way for you to automate your queries from a Windows machine. This recipe won't be covering the full process, but here are some commands that you can use to automate the scripts/jobs.

To execute SQLA from the command prompt, use the following:

Enter the following command on Run:

Sqla -c cookbook -f "c:\workbook\dbc.sql” -e "c:\workbook\resultset\dbc.log"

This will open SQLA, execute the queries, and close it afterwards. This is useful to schedule your jobs/queries on Windows machines.

Query builder is available both in SQLA and Teradata Studio Express. Express query builder is more interactive and has drag-and-drop features. Let's first cover SQLA, followed by Studio Express.

Query builder in SQLA

Here is how you can proceed with query builder in SQLA:

1. Query builder in SQLA is under the Help tab, or you can press F2. Here, you can click on any existing command or browse for the one you need. 
2. You can also add frequently used queries in query builder. Click on + as shown in the following screenshot and add the queries that you need most, so that you don't have to search for them: 

Query builder in Express

Here is how to proceed with query builder in Express:

1. In express, you can edit an existing query using the query builder. Selecting the query in the SQL Editor, right-click and select the Edit in Query Builder option:

   .

   
2. Once you click on the Edit option, your query will be opened in a new window with many other options. Here, you can add new tables to the existing query, creating a relationship between them. There is also a tab that gives you options to put conditions (where, or, and) in queries. You can also add the group by clause; refer to the screenshot for details:

There is an option to change the statement type when you click on the statement box.

The following are the prerequisites to import the data:

Let's begin with importing data using Studio first and then we will move on to SQLA. The following DDL was used to create the table in the new database. Now load the data from the external file. The following steps allow you to do so:

In Express

This one is also as easy as Studio Express. The prerequisites are the same as Studio: 

1. Select the Import data option from the File tab.
2. You need to write SQL to start importing the data.

ins into AP.countries (?,?)

3. ? here defines the number of columns in your file.
4. Turn off the import function and execute Sel * from the table name to verify the data:

Note

Use different formats to import files from Tools | Options |Export/Import.

To use the CSV file, select Options, as shown in the following screenshot:

Data transfer is a two-way street. We sometimes also have requirements to export the data to a file.

Teradata Studio Express and SQL Assistant both can be used to export data as well.

With Teradata Studio Express

Here is how wedo it for Studio Express:

With Teradata SQLA

To save the file in the Excel format, use "filename.xlsx" in double quotes:

"filename.xlsx"

The export function of the tool let's you copy data from variety of files. This is useful when you want to copy a table data onto your local machine. This is fast and effective way to move data from table to any local file type that you want. It is however recommended not to copy large amount of data set as it might crash the local machine or the application.

You can export data from a Teradata table (Source Type = Teradata):