Teradata is an RDBMS that provides high performance and scalability for data warehousing and analytics. Familiarity with its SQL dialect, including standard SQL and specific extensions, is crucial. This article outlines the essential SQL statements and functions for beginners and experienced users.
Teradata SQL Statements
1. CREATE TABLE
The CREATE TABLE statement is used to create a new table in Teradata. The syntax is similar to standard SQL but with some Teradata-specific options:
CREATE TABLE table_name (
column_name data_type,
...
);
2. INSERT INTO
The INSERT INTO statement is used to insert new rows into a table. You can either insert a single row or multiple rows at once:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
3. SELECT
The SELECT statement is used to retrieve data from one or more tables. You can specify the columns you want to retrieve, use aliases, and apply various conditions to filter the data:
SELECT column1, column2, …
FROM table_name
WHERE condition;
4. UPDATE
The UPDATE statement is used to modify existing rows in a table. You can update one or more columns and use a WHERE clause to apply conditions:
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
5. DELETE
The DELETE statement is used to remove rows from a table. You can use a WHERE clause to apply conditions and delete specific rows:
DELETE FROM table_name
WHERE condition;
Teradata SQL Functions
Teradata SQL offers a variety of functions that allow you to perform calculations, manipulate strings, and work with dates and times. Here are some essential functions you should know:
1. Aggregate Functions
Aggregate functions perform calculations on a set of values, typically used with the GROUP BY clause. Some common aggregate functions include:
COUNT(*)counts rows;COUNT(column)counts non-NULL values.SUM(): Calculates the sum of values for a given column.AVG(): Calculates the average of values for a given column.MIN(): Finds the minimum value of a column.MAX(): Finds the maximum value of a column.
2. String Functions
String functions help you manipulate and work with text data. Some important string functions include:
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookSUBSTRING(): Extracts a substring from a string.TRIM(): Removes leading and/or trailing characters from a string.UPPER(): Converts a string to uppercase.LOWER(): Converts a string to lowercase.POSITION(): Finds the position of a substring within a string.
3. Date and Time Functions
Date and time functions allow you to work with date and time values, perform calculations, and format them as needed. Some key functions are:
CURRENT_DATE: Returns the current date.CURRENT_TIME: Returns the current time.CURRENT_TIMESTAMP: Returns the current timestamp.EXTRACT(): Extracts a specific part of a date or time value, such as the year, month, or day.DATE arithmetic: use expressions such asCURRENT_DATE + 1for one day or the documented interval/date functions for the required unit. DATEADD syntax from other databases is not portable to the tested Teradata release.
4. Numeric Functions
Numeric functions help you perform various mathematical operations and calculations on numeric data. Some commonly used numeric functions include:
ABS(): Returns the absolute value of a number.ROUND(): Rounds a number to a specified number of decimal places.CEILING(): Returns the smallest integer value greater than or equal to a given number.FLOOR(): Returns the largest integer value less than or equal to a given number.POWER(): Raises a number to a specified power.SQRT(): Returns the square root of a number.MOD(): Returns the remainder of a division operation.
5. Conversion Functions
Conversion functions allow you to convert data from one data type to another, which can be useful when working with different data types or formatting data for output. Some important conversion functions are:
CAST(): Converts a value from one data type to another.TO_CHAR(): Converts a date, time, or numeric value to a string.TO_DATE(): Converts a string to a date value.TO_NUMBER(): Converts a string to a numeric value.
6. Window Functions
Window functions enable you to perform calculations across a set of rows related to the current row, without needing a GROUP BY clause. Some useful window functions include:
ROW_NUMBER(): Assigns a unique number to each row within the result set.RANK()gives tied rows the same rank and leaves gaps after ties.DENSE_RANK()gives tied rows the same rank without gaps.NTILEsyntax from other platforms was not accepted in the tested Teradata release. Choose a documented release-supported ranking or quantile expression and verify its boundary and tie semantics.LEAD(): Accesses data from a subsequent row in the result set.LAG(): Accesses data from a previous row in the result set.
7. Teradata-Specific Functions
Teradata also provides table operators, temporal syntax and administrative facilities. Their names and calling conventions differ; do not treat every TD-prefixed name as a scalar function.
TD_UNPIVOTis a table operator, not a scalar function call. Use its documented ON/USING syntax, or supported UNPIVOT syntax for the target release.TDMapscontains map-management facilities; it is not a geospatial scalar function. Use documented spatial data types and methods for geographic calculations.WIDTH_BUCKET(value, lower_bound, upper_bound, bucket_count)assigns values to equal-width buckets. Check the documented behavior below and above the bounds.NORMALIZEis a temporal SQL facility for combining qualifying periods. It does not automatically normalize or denormalize a relational data model; the scalar TD_NORMALIZE/TD_DENORMALIZE functions previously listed here are not valid substitutes.- For an exact distinct-value count, use
COUNT(DISTINCT column_name). Do not use the previously listed TD_CARDINALITY name as an approximate-distinct function. FIRST_VALUE(column_name) OVER (...)returns the first value in the specified window frame. Define ordering and frame boundaries explicitly.LAST_VALUE(column_name) OVER (...)returns the last value in the specified window frame, which is not necessarily the last row of the whole partition.
By incorporating these Teradata-specific functions into your SQL queries, you can unlock even more powerful data manipulation and analysis capabilities on the Teradata platform. As always, practice is key to mastering these functions and maximizing your Teradata SQL skills.
8. Teradata Hashing Functions
Teradata uses hashing and maps to place primary-index rows. Hash collisions and skew are possible; hashing does not guarantee perfectly even distribution. Map-aware interpretation matters when predicting AMP placement.
HASHROW(): The HASHROW function calculates the hash value of a given input value or set of input values. It returns a 32-bit hash value to determine the row’s location within the system. The input values are typically the primary index columns for a table. Example:SELECT HASHROW(column_name) FROM table_name;HASHBUCKET(): The HASHBUCKET function calculates the bucket number for a given hash value. This function is useful in understanding the data distribution across the system. It inputs the hash value (from HASHROW) and returns the bucket number. Example:SELECT HASHBUCKET(HASHROW(column_name)) FROM table_name;HASHAMP(): The HASHAMP function calculates the AMP number (Access Module Processor) for a given hash value or bucket number. It helps in determining which AMP is responsible for processing a specific row. Example:SELECT HASHAMP(HASHBUCKET(HASHROW(column_name))) FROM table_name;
These Teradata hashing functions are critical in managing data distribution and ensuring the system operates efficiently. Understanding how these functions work can help you optimize your Teradata environment and improve query performance.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book