Teradata Cookbook

Abhinav Khandelwal, Rajsekhar Bhamidipati

• Publisher: Packt Publishing
• Year: 2018
• ISBN:
  9781787282339
• Edition: 1
• Topic: Informatique
• Subtopic: Bases de données

Data management and analytics simplified with TeradataAbout This Book• Take your understanding of Teradata to the next level and build efficient data warehousing applications for your organization• Covers recipes on data handling, warehousing, advanced querying and the administrative tasks in Teradata.• Contains practical solutions to tackle common (and not-so-common) problems you might encounter in your day to day activitiesWho This Book Is ForThis book is for Database administrator's and Teradata users who are looking for a practical, one-stop resource to solve all their problems while handling their Teradata solution. If you are looking to learn the basic as well as the advanced tasks involved in Teradata querying or administration, this book will be handy. Some knowledge of relational database concepts will be helpful to get the best out of this book.What You Will Learn• Understand Teradata's competitive advantage over other RDBMSs. • Use SQL to process data stored in Teradata tables. • Leverage Teradata's available application utilities and parallelism to play with large datasets• Apply various performance tuning techniques to optimize the queries.• Acquire deeper knowledge and understanding of the Teradata Architecture.• Easy steps to load, archive, restore data and implement Teradata protection features • Gain confidence in running a wide variety of Data analytics and develop applications for the Teradata environmentIn DetailTeradata is an enterprise software company that develops and sells its eponymous relational database management system (RDBMS), which is considered to be a leading data warehousing solutions and provides data management solutions for analytics. This book will help you get all the practical information you need for the creation and implementation of your data warehousing solution using Teradata.The book begins with recipes on quickly setting up a development environment so you can work with different types of data structuring and manipulation function. You will tackle all problems related to efficient querying, stored procedure searching, and navigation techniques. Additionally, you'll master various administrative tasks such as user and security management, workload management, high availability, performance tuning, and monitoring.This book is designed to take you through the best practices of performing the real daily tasks of a Teradata DBA, and will help you tackle any problem you might encounter in the process.Style and approachThis book is a rich collection of recipes that will come in handy when you are working with Teradata. It addresses your common and not-so-common pain points, and this is a book that you must have on the shelf.

Advanced SQL with Backup and Restore

The following are the recipes we will be covering in this chapter:

• Exploring ordered analytic functions
• Using CASE statements
• Working with correlated subqueries
• Experimenting with JSON
• Partitioning tables column wise
• Archiving DD
• Archiving databases
• Archiving PPI tables
• Restoring a table
• Generating a unique row number

Introduction

This chapter will cover two broad areas of Teradata: one will be advanced SQL and the other will be backup and recovery.

Structured query language (SQL) is the relational database language used to store, retrieve, and manipulate data in databases. Teradata also uses SQL to work with datasets stored in the database.

Teradata SQL is basically divided into three parts:

• Data definition language (DDL): These like CREATE and DROP
• Data manipulation language (DML): These are like SELECT, INSERT, and DELETE
• Data control language (DCL): These are like GRANT and REVOKE

Other than these basic SQL functions, Teradata Database has advanced SQL at its disposal. Advanced SQL helps users to execute complex cases and run more advanced data analytic functions.

Some of the advanced Teradata SQL features are:

• Windows aggregate function
• Ordered analytic function
• Extended time and date function
• Advanced ranking function
• Advanced sampling and random function
• Extended grouping function
• Merge into function
• Large binary object

In this chapter, we will cover some of the advanced features of Teradata. We will also cover some features of Teradata backup and recovery.

Backup and recovery

The purpose of the ARC utility is to allow for the archiving, restoring, and copying of database objects. To maintain data security and integrity, backing up data is a must. And when you damage or lose or want to restore the previous version of data, restore is required to be performed.

The following figure shows the high-level architecture of backup and recovery, or the BAR system:

We will start with advanced SQL and then will cover some recipes on Teradata ARC.

Exploring ordered analytic functions

The Online Analytical Processing (OLAP) function, according to Wikipedia, is used to analyze multi-dimensional analytical queries quickly and efficiently. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining. The typical applications of OLAP are business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting, and similar areas.

The term OLAP was created as a slight modification of the traditional database term OLTP (Online Transaction Processing). Databases configured for OLAP use a multidimensional data model, allowing for complex analytical and ad hoc queries with a rapid execution time.

Like traditional aggregate functions, window aggregate functions operate on groups of rows and permit the qualification and filtering of the group result. Unlike aggregations, OLAP functions also return individual detail rows, not just aggregations. The following are some OLAP functions available in Teradata:

Functions	Used For
RANK	For ranking of the answer set
QUANTILES	For quantiles
CSUM	For cumulation
MAVG	For moving averages
MSUM	For moving sums
MDIFF	For moving differences
MLINREG	For moving regression
ROW_NUMBER	Returns the sequential row number of a group starting with the number o...