Teradata数据仓库资深官方教材.ppt

Module 3:Teradata BasicsAfter completing this module,you will be able to:List and describe the major components of the Teradata architecture.Describe how the components interact to manage incoming and outgoing dataList 5 types of Teradata database objectsTeradata Storage ArchitectureNotes:The Parsing Engine dispatches request to insert a row.The Message Passing Layer insures that a row gets to the appropriate AMP(Access Module Processor).The AMP stores the row on its associated(logical)disk.An AMP manages a logical or virtual disk which is mapped to multiple physical disks in a disk array.TeradataAMP 4AMP 3AMP 1AMP 2Parsing Engine(s)Message Passing Layer1825441129075803266725Records From Client(in random sequence)2326712906547518258041Teradata Retrieval ArchitectureNotes:The Parsing Engine dispatches a request to retrieve one or more rows.The Message Passing Layer insures that the appropriate AMP(s)are activated.The AMP(s)locate and retrieve desired row(s)in parallel access.Message Passing Layer returns to retrieved rows to PE.The PE returns row(s)to requesting client application.TeradataAMP 4AMP 3AMP 1AMP 2Parsing Engine(s)Message Passing Layer1825441129075803266725Rows retrieved from table2326712906547518258041Multiple Tables on Multiple AMPsEMPLOYEE RowsDEPARTMENT RowsJOB RowsEMPLOYEE TableDEPARTMENT TableJOB TableParsing EngineAMP#1AMP#2AMP#3AMP#4Message Passing LayerNotes:Some rows from each table may be found on each AMP.Each AMP may have rows from all tables.Ideally,each AMP will hold roughly the same amount of data.EMPLOYEE RowsDEPARTMENT RowsJOB RowsEMPLOYEE RowsDEPARTMENT RowsJOB RowsEMPLOYEE RowsDEPARTMENT RowsJOB RowsLinear Growth and ExpandabilityAMPAMPParsingEngineAMPSESSIONSPARALLEL PROCESSINGDATADiskDiskDiskParsingEngineParsingEngineNotes:Teradata is a linearly expandable RDBMS.Components may be added as requirements grow.Linear scalability allows for increased workload without decreased throughput.Performance impact of adding components is shown below.USERSAMPsDATAPerformanceSameSameSameSameDoubleDoubleSameSameSameDoubleDoubleSameSameDoubleSameDoubleTeradata ObjectsThere are eight types of objects which may be found in a Teradata database/user.Tables rows and columns of data Views predefined subsets of existing tablesMacros predefined,stored SQL statementsTriggers SQL statements associated with a tableStored Procedures program stored within TeradataJoin and Hash Indexes separate index structures stored as objects within a databasePermanent Journals table used to store before and/or after images for recoveryDEFINITIONS OF ALL DATABASE OBJECTSDD/DThese objects are created,maintained and deleted using Structured Query Language(SQL).Object definitions are stored in the Data Dictionary/Directory(DD/D).DATABASE or USERTABLE 2TABLE 3TABLE 1VIEW 2VIEW 3VIEW 1MACRO 2MACRO 3MACRO 1TRIGGER 2TRIGGER 3TRIGGER 1Stored Procedure 1Stored Procedure 2Stored Procedure 2Join/Hash Index 1Join/Hash Index 2Join/Hash Index 3Permanent JournalThese arent directly accessed by users.The Data Dictionary Directory(DD/D)The DD/D.is an integrated set of system tables contains definitions of and information about all objects in the system is entirely maintained by the RDBMS is“data about the data”or“metadata”is distributed across all AMPs like all tables may be queried by administrators or support staff is accessed via Teradata supplied viewsExamples of DD/D views:DBC.Tables-information about all tablesDBC.Users-information about all usersDBC.AllRights-information about access rightsDBC.AllSpace-information about space utilizationStructured Query Language(SQL)SQL is a query language for Relational Database Systems.A fourth-generation languageA set-oriented languageA non-procedural language(e.g,doesnt have IF,GO TO,DO,FOR NEXT,or PERFORM statements)SQL consists of:Data Definition Language(DDL)Defines database structures(tables,users,views,macros,triggers,etc.)CREATEDROPALTERData Manipulation Language(DML)Manipulates rows and data valuesSELECTINSERTUPDATEDELETEData Control Language(DCL)Grants and revokes access rightsGRANTREVOKETeradata SQL also includes Teradata Extensions to SQLHELPSHOWEXPLAINCREATE MACROCREATE TABLE Example of DDLCREATE TABLEEmployee,FALLBACK(employee_number INTEGER NOT NULL,manager_emp_number INTEGER,dept_number SMALLINT,job_codeINTEGERCOMPRESS,last_nameCHAR(20)NOT NULL,first_nameVARCHAR(20),hire_dateDATE FORMAT YYYY-MM-DD,birth_dateDATE FORMAT YYYY-MM-DD,salary_amount DECIMAL(10,2)UNIQUE PRIMARY INDEX(employee_number),INDEX(dept_number);Other DDL ExamplesCREATE INDEX(job_code)ON Employee;DROP INDEX(job_code)ON Employee;DROP TABLE Employee;ViewsViews are pre-defined subsets of existing tables consisting of specified columns and/or rows from the table(s).A single table view:is a window into an underlying table allows users to read and update a subset of the underlying table has no data of its ownMANAGEREMPLOYEEEMPDEPTJOBLASTFIRSTHIREBIRTHSALARYNUMBERNUMBERNUMBERCODENAMENAMEDATEDATEAMOUNT10061019301312101SteinJohn861015 631015394500010081019301312102KanieskiCarol870201 680517392500010050801403431100RyanLoretta861015 650910412000010041003401412101JohnsonDarlene 861015 560423463000010071005403432101VillegasArnando 870102 470131597000010030801401411100TraderJames860731 5706194785000EMPLOYEE(Table)PKFKFKFK EMP NO DEPT NO LAST NAME FIRST NAME HIRE DATE 1005 403 Villegas Arnando 870102 801 403 Ryan Loretta 861015Emp_403(View)Multi-Table ViewsA multi-table view allows users to access data from multiple tables as if it were in a single table.Multi-table views are also called join views.Join views are used for reading only,not updating.EMPLOYEE(Table)10061019301312101SteinJohn861015 631015394500010081019301312102KanieskiCarol870201 680517392500010050801403431100RyanLoretta861015 650910412000010041003401412101JohnsonDarlene 861015 560423463000010071005403432101VillegasArnando 870102 470131597000010030801401411100TraderJames860731 5706194785000MANAGEREMPLOYEEEMPDEPTJOBLASTFIRSTHIREBIRTHSALARYNUMBERNUMBERNUMBERCODENAMENAMEDATEDATEAMOUNTPKFKFKFKMANAGERDEPTDEPARTMENTBUDGETEMPNUMBERNAMEAMOUNTNUMBER501marketing sales800500001017301research and development465600001019302product planning226000001016403education932000001005402software support308000001011401customer support982300001003201technical operations293800001025PKFKDEPARTMENT(Table)LAST DEPARTMENT NAME NAMEStein research&developmentKanieski research&developmentRyan educationJohnson customer supportVillegas educationTrader customer supportEmpDept(View)Joined TogetherSELECT Example of DMLThe SELECT statement is used to retrieve data from tables.Who was hired on October 15,1986?10061019301312101SteinJohn861015 631015394500010081019301312102KanieskiCarol870201 680517392500010050801403431100RyanLoretta861015 650910412000010041003401412101JohnsonDarlene 861015 560423463000010071005403432101VillegasArnando 870102 470131597000010030801401411100TraderJames860731 5706194785000EMPLOYEE(partial listing)MANAGEREMPLOYEEEMPDEPTJOBLASTFIRSTHIREBIRTHSALARYNUMBERNUMBERNUMBERCODENAMENAMEDATEDATEAMOUNTPKFKFKFKSELECT Last_Name,First_NameFROM EmployeeWHERE Hire_Date=1986-10-15;ResultLASTNAMEStein RyanJohnsonFIRSTNAMEJohnLorettaDarleneThe JOIN OperationA join operation is used when the SQL query requires information from multiple tables.Who works in Research and Development?EMPLOYEE10061019301312101SteinJohn861015 631015394500010081019301312102KanieskiCarol870201 680517392500010050801403431100RyanLoretta861015 650910412000010041003401412101JohnsonDarlene 861015 560423463000010071005403432101VillegasArnando 870102 470131597000010030801401411100TraderJames860731 5706194785000MANAGEREMPLOYEEEMPDEPTJOBLASTFIRSTHIREBIRTHSALARYNUMBERNUMBERNUMBERCODENAMENAMEDATEDATEAMOUNTPKFKFKFKMANAGERDEPTDEPARTMENTBUDGETEMPNUMBERNAMEAMOUNTNUMBER501marketing sales800500001017301research and development465600001019302product planning226000001016403education932000001005402software support308000001011401customer support982300001003201technical operations293800001025PKFKDEPARTMENTResultLASTNAMEStein KanieskiFIRSTNAMEJohnCarolMacros Teradata SQL ExtensionA MACRO is a predefined set of SQL statements which is logically stored in a database.Macros may be created for frequently occurring queries of sets of operations.Macros have many features and benefits:Simplify end-user accessControl which operations may be performed by usersMay accept user-provided parameter valuesAre stored on the RDBMS,thus available to all clientsReduces query size,thus reduces LAN/channel trafficAre optimized at execution timeMay contain multiple SQL statementsTo create a macro:CREATE MACRO Customer_List AS(SELECT customer_name FROM Customer;);To execute a macro:EXEC Customer_List;To replace a macro:REPLACE MACRO Customer_List AS (SELECT customer_name,customer_number FROM Customer;);HELP Commands Teradata SQL ExtensionDatabases and Users:HELPDATABASECustomer_Service;HELPUSERDave_Jones;Tables,Views,and Macros:HELPTABLEEmployee;HELPVIEWEmp;HELPMACROPayroll_3;HELPCOLUMNEmployee.*;Employee.last_name;Emp.*;Emp.last;HELPINDEXEmployee;HELPSTATISTICSEmployee;HELPCONSTRAINT Employee.over_21;Example of HELP DATABASEHELP DATABASE Customer_Service;*Help information returned.15 rows.*Total elapsed time was 1 second.Table/View/Macro nameKindCommentContactT?CustomerT?Cust_Comp_OrdersV?Cust_Pend_OrdersV?Cust_Order_ixI?DepartmentT?EmployeeT?Employee_PhoneT?JobT?LocationT?Location_EmployeeT?Location_PhoneT?OrdersT?Set_Ansidate_onM?Set_Integerdate_onM?Command:SHOW Command Teradata SQL ExtensionSHOW commands display how an object was created.CommandReturns statementSHOW TABLEtablename;CREATE TABLE statementSHOW VIEWviewname;CREATE VIEW.SHOW MACROmacroname;CREATE MACRO.SHOW TRIGGERtriggername;CREATE TRIGGER SHOW PROCEDURE procedurename;CREATE PROCEDURE SHOW TABLE employee;CREATE SET TABLE CUSTOMER_SERVICE.Employee,FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL(employee_number INTEGER,manager_employee_number INTEGER,department_number INTEGER,job_code INTEGER,:salary_amount DECIMAL(10,2)NOT NULL)UNIQUE PRIMARY INDEX(employee_number);EXPLAIN Facility Teradata SQL ExtensionThe EXPLAIN modifier in front of any SQL statement generates an English translation of the Parsers plan.The request is fully parsed and optimized,but not actually executed.EXPLAIN returns:Text showing how a statement will be processed(a plan)An estimate of how many rows will be involvedA relative cost of the request(in units of time)This information is useful for:predicting row countspredicting performancetesting queries before productionanalyzing various approaches to a problem EXPLAINEXPLAIN SELECT last_name,department_number FROM Employee;Explanation(partial):3)We do an all-AMPs RETRIEVE step from CUSTOMER_SERVICE.Employee by way of an all-rows scan with no residual conditions into Spool 1,which is built locally on the AMPs.The size of Spool 1 is estimated to be 24 rows.The estimated time for this step is 0.15 seconds.Teradata Features ReviewDesigned for decision-support and tactical queriesIdeal for data warehouse applicationsParallelism makes possible access to very large tablesPerformance increase is linear as components are addedUses standard SQLRuns as a“database server”to client applicationsRuns on multiple hardware platformsOpen architecture uses industry standard componentsWin XPWin 2000UNIXClientMainframeClientTeradataDATABASEReview Questions1.What language is used to access a Teradata table?2.What are five Teradata database objects?3.What are four major components of the Teradata architecture?4.What are views?5.What are macros?Module 3:Review Question Answers1.What language is used to access a Teradata table?2.What are five Teradata database objects?3.What are four major components of the Teradata architecture?4.What are views?5.What are macros?SQLTables,views,macros,triggers,and stored proceduresPE,AMPs,Vdisks,and Message Passing Layer-Subset of rows and columns or one or more tables-Virtual tables-Window into one or more tablesPredefined,stored set of SQL statements