• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Teradata Tutorials

Welcome to Teradata Tutorials. The intent of these tutorials is to provide in depth understanding of Teradata Database. In addition to Teradata Tutorials, we will look at common interview questions, how to tutorials, issues and their resolutions

Teradata Introduction

Teradata database now able to connect Hadoop with this QueryGrid so it’s called as Teradata Database-to-Hadoop also referred as Teradata-to-Hadoop connector.

It provides a SQL interface for transferring data between Teradata Database and remote Hadoop hosts.

-Import Hadoop data into a temporary or permanent Teradata table.

-Export data from temporary or permanent Teradata tables into existing Hadoop tables.

-Create or drop tables in Hadoop from Teradata Database.

-Reference tables on the remote hosts in SELECT and INSERT statements.

-Select Hadoop data for use with a business tool.

-Select and join Hadoop data with data from independent data warehouses for analytical use.

Teradata Objects

Tables: A table in a relational database management system is a two-dimensional structure made up of columns and physical rows stored in data blocks on the disk drives.

Views: A view is like a “window” into tables that allows multiple users to look at portions of the same base data. A view may access one or more tables, and may show only a subset of columns from the table(s).

Macros: Macros are predefined, stored sets of one or more SQL commands and/or report-formatting (BTEQ) commands. Macros can also contain comments.

Triggers: A trigger is a set of SQL statements usually associated with a column or table that are programmed to be run (or “fired”) when specified changes are made to the column or table. The pre-defined change is known as a triggering event, which causes the SQL statements to be processed.

Stored Procedures: A stored procedure is a pre-defined set of statements invoked through a single CALL statement in SQL. While a stored procedure may seem like a macro, it is different in that it can contain:

Teradata SQL data manipulation statements (non-procedural)

Procedural statements (in Teradata, referred to as Stored Procedure Language)

Interested in mastering Teradata Training? Enroll now for FREE demo 
on Teradata Training.

Teradata Databases

-A logical repository forTables, Views, Macros, Stored Procedures

-Database may own objects

-Perm Space – max amount of space available for objects

-Spool Space – max amount of work space available for requests (like tempdb)

-Database is empty until objects created in it

Creating Database:

CREATE DATABASE financeFROM sysadminASPERMANENT = 60000000,SPOOL = 120000000,FALLBACK PROTECTION,AFTER JOURNAL,BEFORE JOURNAL

Teradata Users:

-User is a database with an assigned password

-May own objects

-User may logon to Teradata and access objects within itself and other database where user has access rights

-A user is empty until objects are created within it.

-SA equivalent user is DBC (Database Computer).

Creating a User:

CREATE USER testuser FROM MyApplASPERM=2000000,SPOOL=5000000 ,PASSWORD=SECRET,DEFAULT DATABASE = Finance,NO FALLBACK

The Hierarchy of Databases

Teradata basics - Hierarchy of Databases

-A new database or user must be created from an existing database or user.

-All Perm space specifications are subtracted from the immediate owner or parent.

-Perm space is a zero sum game – the total of all Perm Space allocations must equal the total amount of disk space available.

-Perm space is used for tables only.

-Perm space currently unassigned is available to be used as Spool

Teradata Applications

Enterprise Data ware house

Active Data ware house

Internet And E-Commerce

CRM[Customer Relationship Management]

Data Mart Appliance etc

Teradata Enterprise Architecture

Teradata Enterprise Architecture

Teradata Architecture

Teradata Database system are 2 types

SMP

MPP

a) SMP[Symmetric Multi- Processing]

Teradata database system is called SMP System it have a single NODE That contains multiple CPU sharing Memory pool.

b) MPP[Massively parallel Processing]

Here multiple NODES are connected together via a component called as BYNET

Here all the NODES communication with each other with the help of vistual processes

Real time usage

To perform mode operation and to have better storage this is recommended. 

 

NODE ARCHITECTURE

Teradata - Node Architecture

PE  2  Passing  Engine

PDF 2 Parallel Data Base Extension

AMP 2 Access Module Processor

VDISK 2 Virtual Disk

VPRDC 2 Virtual Processor

NODE

It is important building block of the Teradata Database system it is collection of hardware and software components.

A server can also called as a node

Channel Attached system and network attached systems can be connect to a NODE

Channel driver and Teradata GATEWAY are the application RUN under the operating system as processes

Remaining component runs under PDE

PE and AMP Virtual processor, where BYNET Is an Internal Layer between PE and AMP

PDE[Parallel Data Base Extension]

It Runs Teradata component in parallel

TPA[Trusted parallel Application]

A data base is called pure parallel application (or) Trusted parallel application, it’s Runs under PDE

Teradata is a database, which is running under PDE, so we call it. Teradata has pure parallel Data base and trusted data base 

SMP ARCHITECTURE[Symmetric Multi- Processing]

Single NODE Architecture can also be called as SMP Architecture, Here BYNET Can also referred as BROAD LESS BYNET(or) VIRTUAL BYNET(or)S OFT Ware BYNET

Recommended

For GIGA BYTES of data processing with minimum operation, this is recommended

MPP Architecture(Massively parallel processor)

Teradata - MPP Architecture

Collection of NODE which make larger configuration which Is called MPP

All these nodes are connected via component called BYNET,

Which allows multiple virtual processors and multiple NODES Communicate with each other

This BYNET can also called as BOARD ORIENTED BYNET(OR) HARD WARE BYNET.

Terdata BYNET Features

Fault Tolerance

Each BYNET having network path to connected to the NODES, if there is a failure in any net work path simple it reconfiguration Itself and avoids the un- usable or failed path, in this way it is fault occurrence. It tolerates it self

Load Balances

It BYNET O is not able to reconfiguration or not able to handle traffic, than all instructions redirected to BYNET1 and it is Balance the Load.

High scalability

If we increase the Number of NODES Teradata Doesn’t sacrifice any perform are and it’s scales Linearly.

NOTE

V2 R5 à512 NODES

V2R6, TD12à1024 NODES

Latest Versionà2048 NODES be can connect to BYNET upcoming

Some companies And Number of the NODES

JPMC — 40 NODES

BOA — 40 NODES

DBS — 8 NODES

ICICI Productial — 4 NODES

CISCO — 44 NODES

WALL MART — 340 NODES

Barclays — 11 NODES (or)

Recommendation

If Processing Tera BYTES of data and many operations the MPP is recommended.

Check out the top Teradata Interview Questions now!

Difference between Teradata And Other RDBMS

           TERA DATA      Other RDBMS
Architectures Shared Nothing Shared Every thing
Processes MIPS[Millions of Instructions/sec KIPS[Thousand of institutions/sec]
Indexes Better Distribution And Retrieval Only FASI Retrieval
Parallelism Un-conditional conditional
Bulk Load Many Limited
Facilities Enterprise wide Data ware housing OLTP More
stores TERA BYTES[Billions of rows] GIGA BYTES[Millions of rows]

 

Screenshot_1

CRM[Customer, Relationship Management]

Teradata CRM

Teradata Advantages

Automatic, Even Data Distribution

In Other RDBMS Sequential distribution is Automatic, But in Teradata Even(or) Uniform (or)Random distribution is Automatic

High Scalability

NODE                         NODE

WORK                        WORK

USERS                        USERS

Other, RDBMS Linear Scalability Application Teradata

If increase Number of Node, USERS (OR) WORK, Teradata Doesn’t sacrifice any performance and it’s scales linearly

 

Matura optimizer

Older Version of Oracle/SQL Server

-32 Joins/Queries

-32 Sub Queries/Queries

Older Version of Tera data

-64 Joins/Queries

-64 Sub Queries/Queries

-Formatting Commands

-Analytical Commands

-Aggregate Commands etc.

As Teradata is having  powerful optimizer so that it is able to perform above operations.

Models The Business

Teradata highly flexible to 3NF[3rd Normal Form] and it’s supports the below models also

-Normalization

-Star schema

-Snow Flake schema

-Bus schema

-Galaxy Schema

-Hybrid Schema

 

Acts Like a Single Data Store

Teradata - Single Data Store

Tera data is warehouse, where we can stoke both CHANNEL ATTACHED Data and NETWORK ATTACHED Data.

Low cost TCO[total cost of ownership]

Screenshot_4

Many Bulk Load Facilities

There are many facilities in Teradatato load and un-load

BTEQ-Load/Un- load

FAST LOAD-LOAD

MULTI LOAD- LOAD

FAST EXPORT- UN-LOAD

T PUMP-LOAD

OLE LOAD- LOAD/UN-LOAD

TeradataPARALLEL TRANSPORTER (TPT)-LOAD/UN-LOAD

TeradataPARALLEL TRANSPORTER API etc.

Many appliances added supported from teradata 13 onwards those are

Extreme data appliance1550/1555

Active Enterprise Data ware house 5555/5550

Data mart appliance 2550/2555/2500 etc

Teradata Installation

Installing Teradata is very simple. It asks 2 things.

Storage space

Custom and typical option

Un zip folder, zip folder

Go to setup.exe

Click Main Menu

Install TeradataExpress

Installation Requirement

Minimum32-bit system Requirement

Micro soft windows XP with SP3

Micro soft windows Server 2003 with SP2

Micro soft windows Server 2008

Micro soft windows vista

Micro soft windows 7

Minimum hard ware Requirement

1 GB Memory

5 GB to 12GB of Free Disk space

Click Next install Menu and install below component one by one

Teradata BYNET DRIVER

Teradata Database

Teradata Express tool

Teradata tools and utilities

Note

It takes Max 20 to 25 minutes for entire installation

For Indepth knowledge on Teradata click on:

 


 

Summary
Review Date
Reviewed Item
Useful Teradata Tutorial and Interview Questions to review
Author Rating
4

Related Trainings

Contact US

Need an Instructor Led Training, Contact Us





Enter this code: captcha