Basic Concepts On Teradata 1o71

Basic Concepts on Teradata

What is a Data Warehouse? • A data warehouse is a central, enterprise-wide database that contains information extracted from the operational data stores. • It is a technique to properly assemble and manage data from various sources to answer business questions

What is Teradata? • Teradata is a Relational Database Management System (RDBMS) for the world’s largest commercial databases. • Parallelism and scalability, are the main features of Teradata.

• Teradata is comparable to a large database server, with multiple client application making inquiries against it concurrently.

Architectural representation •

The Parsing Engine interprets the SQL command and converts the data record from the host into an AMP message.

•

The Message ing Layer distributes the row to the appropriate Access Module Processor (AMP).

•

The AMP formats the row and writes it to its associated disks.

•

The disk holds the row for subsequent access.

Teradata Objects • • • • • • • • •

There are nine types of objects which may be found in a Teradata database/. Tables are rows and columns of data Views – predefined subsets of existing tables Macros – predefined, stored SQL statements Triggers – SQL statements associated with a table Stored Procedures – program stored within Teradata -Defined Function – function (C program) to provide additional SQL functionality and Hash Indexes – separate index structures stored as objects within a database Permanent Journals – table used to store before and/or after images for recovery

These objects are created, maintained, and deleted using Structured Query Language (SQL).

Indexes in Teradata • Indexes are used to distribute and access rows from a table without having to search the entire table. • Primary Index is the mechanism for retrieving and asg a data row to an AMP and inturn to an AMP’s disks. • There are 2 types of primary index – unique (UPI) and non-unique (NUPI). • A secondary index is an alternate path to the data. • There are 2 types of secondary index – unique (USI) and non-unique (NUSI).

Teradata Application Utilities  Load utilities (e.g., BTEQ, FastLoad, MultiLoad, and TPump)  Export utilities (e.g., BTEQ and FastExport)

Bteq Method to start the TD utilities are: • Click on Start  programs  Teradata Client  bteq OR

• Go to unix prompt and type bteq • To run a file using bteq, the command is: .runfile = filenamepath • The logon command is : .logon hostname/name, ;

The SET Commands in Bteq [SET] SESSION TRANSACTION = BTET [SET] SESSIONS =8

Error handling methods: -----------------------------.SET ERRORLEVEL 2168 SEVERITY 4, .IF ERRORLEVEL >= 14 THEN .QUIT 17 ;

Sample Bteq import/export script Bteq export script: ----------------------

.export data file = C:/output_data.txt, limit=100 select * from au2.trans ; .export reset; Bteq import script: ----------------------

.IMPORT DATA file = e:\datain3 ; .QUIET ON .REPEAT * USING in_CustNo(INTEGER), in_SocSec(INTEGER), Filler(CHAR(30)), in_Lname(CHAR(20)), in_Fname(CHAR(10)) INSERT INTO Customer( Customer_Number, Last_Name, First_Name, Social_Security ) VALUES(:in_CustNo, :in_Lname, :in_Fname, :in_SocSec); .QUIT

Screen shot of BTEQ

SQL Assistant Methods to start the sql assistant are: • Go to the start  program  Teradata SQL Assistant OR • Go to Run and type in queryman • To , on the left hand side corner there is a green button (marked in Red) which is for connecting to the database. Click on that. • A window will open which will ask for name and . • Once logged in successfully one can start writing queries in the screen and press F5 to execute them.

Screen shot of SQL ASSISTANT

Sample script on mload 1)Firstly create a Teradata table for loading 2) A sample mload script named <mload scriptname>.mload: .LOGTABLE logtablename; .LOGON /<name>,<>; .begin import mload tables tablename errortables tablename_et tablename_uv tablename_wt; .layout layoutname; column name1 (column datatype) .dml label labelname; insert into test ( Column name1 ….. ) Values ( :column name1…); .import infile filename with the path format vartext '|' layout layoutname apply labelname; .end mload; .logoff;

Sample script on fastexport export script; .LOGTABLE logtablename; .RUNFILE logon ; .BEGIN EXPORT SESSIONS 4 ; .EXPORT OUTFILE export filename with the path; SELECT A._Number, C.Last_Name, C.First_Name, A.Balance_Current FROM s A INNER s_Customer AC INNER Customer C ON C.Customer_Number = AC.Customer_Number ON A._Number = AC._Number WHEREA.City =‘Bangalore’; .end export; .logoff;

To initiate the other utilities

• For Mload

Mload < inputscriptname > outputfilename

• For Fastload

Fastload < inputfilename > outputfilename

• For Fastexport

Fexp < inputscriptname > outputfilename