Teradata Basics

Teradata Viewpoint User Interface

The Teradata Viewpoint user interface is shown below. The data is 100M rows from a department store called Dillards. The right-hand panel is the object browser, where various databases can be accessed. The upper left-hand panel is called the “query panel.” It is where queries are typed. The bottom left-hand panel is the “results panel.” It is where results are displayed.

Results images, such as the ones below, will focus on the results panel instead of the entire Teradata window from now on.

  • Results can be “pinned” for later viewing by pressing the pin icon in the upper-right corner of the results window.
  • Executing DATABASE ua_dillards sets the dillards database as the default. This allows the user to avoid referencing tables via the lengthy dot-notation (ua_dillards.DEPTINFO).
  • Queries can be saved and run later. Select the drop down arrow next to the Run button, and then select Save Selected Query.

Get to Know Your Teradata

Where MySQL uses SHOW and DESCRIBE to obtain basic information about the tables and columns, Teradata uses HELP TABLE [TABLE NAME] and HELP COLUMN [COLUMN NAME].

The key columns to pay attention to “Column Name” and “Nullable” which will have a “Y” if null values are permitted, and “N” otherwise.

HELP TABLE DEPTINFO;
HELP COLUMN DEPTINFO.DEPT;

SHOW TABLE [tablename] also works in Teradata, but functions differently than MySQL. It shows the code written to create the table. Among other useful information, it also shows which columns are the primary keys of the table.

Teradata versus MySQL

Another very visible difference between Teradata and MySQL is Teradata uses a TOP operator instead of LIMIT to restrict the length of a query output. TOP goes at the beginning of queries, as shown below. TOP does not have the equivalent of OFFSET, like MySQL does.

SELECT TOP 10 *
FROM strinfo;

Teradata has a SAMPLE keyword that returns a random subset of the available rows. The first query below returns a random set of 10 rows, and the second query returns a random 10% of the available rows.

SELECT *
FROM strinfo
SAMPLE 10;

SELECT *
FROM strinfo
SAMPLE .10;

A few other key differences:

  • Teradata only accepts single quotation marks, whereas MySQL accepts both single and double.
  • Teradata only accepts “<>” to indicate “does not equal,” whereas MySQL accepts “!=” as well.
  • Though Teradata uses YY/MM/DD as the output format for Datetime objects, queries must use the YYYY-MM-DD format.

Some content for this note is taken from the Coursera course “Managing Big Data with MySQL.”