Teradata数据仓库简介资料课件

Teradata数据库简介

Teradata数据仓库事业部

华南区

TeradataConfidentialAgenda?关于TERADATA?Teradata数据库原理

?Teradata数据库架构

?Teradata数据库工作原理

?Teradata特性

?Teradata数据仓库构建?基本概念

?常用工具介绍

?管理的一些约定

关于TERADATA?Teradata最初产生于1976年，由加州理工学院和花旗银行的高科技项目-创建一个能够分析10的12次方bytes数据的系统。

1Kilobyte1Megabyte1Gigabyte1Terabyte1Petabyte1Exabyte1Zetabyte1Yottabyte=103=1000bytes=106=1,000,000bytes=109=1,000,000,000bytes=1012=1,000,000,000,000bytes=1015=1,000,000,000,000,000bytes=1018=1,000,000,000,000,000,000bytes=1021=1,000,000,000,000,000,000,000bytes=1024=1,000,000,000,000,000,000,000,000bytes关于TERADATA?Teradata是全球最大的专注于数据仓库、咨询服务及企业分析方案的提供商，凭借业界领先的数据库、数据仓库解决方案、性能卓越的可扩展平台以及全球2000多个大型数据仓库项目的客户成功经验，成就了公司在数据仓库领域的创新领导地位。

Gartner评选Teradata为数据仓库领导厂商

2007challengersleaderschallengers2008leadersTeradataTeradataOracleability

to

execute

ability

to

execute

OracleIBMMicrosoftSybaseNetezzaGreenplumMySQLKognitioSandTechnologyDATAllegroIBMSybaseHPMicrosoft-DATAllegroNetezzaGreenplumVerticaKognitioSandTechnologySunMicrosystems-MySQLIngresIlluminateSolutions1010datanicheplayersvisionariespletenessfvisionasofSeptember2007completenessofvisionnicheplayersvisionariesasofDecember20085Teradata数据库原理

?Teradata数据库架构

?Teradata数据库工作原理

?Teradata特性

Teradata数据库架构

通

信

界

面

TCP/IP

网

关

BYNET信息传递网络

存

取

模

块

单

元AMPAMP1磁盘阵列

信

息

通

道

封

闭

主

机

系

统

PE1AMP2AMP3传

递

层

PE2

分

析

引

擎

PE

AMP4PDE(并

行

数

据

库

扩

展

环

境)UNIX

单

结

点SMPTERADATA的MPP架构

CPUCPUCPUCPUCPUCPUCPUCPUMPP系统与Teradata?多结点同时工作

?数据库由各结点共同拥有

MemoryMemory?MPP(MassiveParallelProcessing)海量并行处理服务器：由多个SMP服务器通过一定的结点互联网络进行连接，协同工作，完成相同的任务。从用户的角度来看是一个系统！

Teradata并行处理架构

V-PEV-PEBY-NetV-AMPV-AMPV-AMPV-AMP?PARSINGENGINE(PE)?SQLParser&Optimizer?QueryStepDispatcher

?NetworkDistribution

?AccessModuleProcessors(AMP)

?DiskPartitionsTeradata并行的机制

每个并行AMP单AMP元AMP只AMP1管理ReadingWritingSorting自AMP4Aggregating的数据

己BuildingRowLockingIndexesAMP3的数据TransactionJournalizing

的LoadingAMP2的数据

Backup&数Recovery据

AMP1的数据

并行处理性能

其他关系数据库

Teradata“有条件的并行”

“无条件的并行”

初始查询

查询优化

查询并行

扫描

链接

聚合

排序

收敛

最终结果集

时间

SharedNothingSoftware?线性扩展能力

>最大化的利用每个节点的资源

>可灵活配置

VPROCsVPROCsVPROCsVPROCsAmpsAmpsAmpsAmpsVPROCsVPROCsVPROCsVPROCsAmpsAmpsAmpsAmpsVPROCsVPROCsVPROCsVPROCsAmpsAmpsAmpsAmpsVPROCsVPROCsVPROCsVPROCsAmpsAmpsAmpsAmpsMPP小结

?TeradataMPP架构

>使用当前最快的CPU>最好的扩展性

>使用shared-nothingMPP架构以达到线性扩展

EffectiveCPUScalingPerformance10Effective

CPU

Performance864IBM/SUNTypical(CPU=0.67,88%SMPscaling)TeradataWorldMark(CPU=1.00,88%1-4CPUSMPscaling,98%pernode)IBM/SUNBestCase(CPU=0.78,91%SMPscaling)20123456789101112NumberofCPUsTeradata数据仓库构建

?基本概念

?常用工具介绍

?管理的一些约定

数据处理的演变

TypeExampleUpdateacheckingaccounttoreflectadeposit.HowmanychildsizebluejeansweresoldacrossallofourEasternstoresinthemonthofMarch?Instantcredit—Howmuchcreditcanbeextendedtothisperson?NumberofRowsResponseAccessedTimeSmallSeconds传

统

OLTPDSSLargeSecondsorminutesOLCP现在

OLAP

SmalltomoderateagainstmultipledatabasesMinutesShowthetoptensellingLargeofdetailSecondsoritemsacrossallstoresrowsormoderateminutesfor1997.ofsummaryrows数据仓库（DataWarehouse，可简写为DW

）

数据仓库是决策支持系统（DSS）和联机分析（OLAP）应用数据源的结构化数据环境。数据仓库研究和解决从数据库中获取信息的问题。数据仓库的特征在于面向主题、集成性、稳定性和时变性。

ATMPeopleSoftPOSOperationalDataTeradataRDBMSDataWarehouseAccessToolsCognosAccessBizObjectsEndUsersETLETL是Extraction-Transformation-Loading的缩写，负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。

ETL

ETL是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。

主索引（PrimaryIndex）

Table1Table2Table3PI:cust_id?主索引是表中的一个或多个字段，用于确定数据的物理分布

?每个表的数据根据PI(主索引)平均分布在不同的AMP>通过Hash算法实现数据自动分布

>无需数据重组、重新分区、数据分布管理

>可以是唯一或非唯一

>一个表不会有两个主索引

?主索引的选择，关系到能否很好的发挥Teradata数据库的优势-并行处理。

PrimaryIndexTeradataParallelHashFunctionVAMP1VAMP2VAMP3VAMP4………VAMPn

PPPPPPPPPPI:acc_idMDMDMDMDMDMDMDMDMDPI:cust_id主键和主索引

?????

Indexesareconceptuallydifferentfromkeys.APKisarelationalmodelingconventionwhichallowseachrowtobeuniquelyidentified.APIisaTeradataconventionwhichdetermineshowtherowwillbestoredandaccessed.AsignificantpercentageoftablesmayusethesamecolumnsforboththePKandthePI.Awell-designeddatabasewilluseaPIthatisdifferentfromthePKforsometables.PrimaryKeyLogicalconceptofdatamodelingTeradatadoesn'tneedtorecognize

NolimitonnumberofcolumnsDocumentedindatamodel(OptionalinCREATETABLE)MustbeuniqueIdentifieseachrowValuesshouldnotchangeMaybeuniqueornon-uniqueIdentifies1(unique)ormultiplerows(non-unique)Valuesmaybechanged(Delete+Insert)PrimaryIndexPhysicalmechanismforaccessandstorageEachtablemusthaveexactlyoneprimaryindex

16columnlimit(V2R4.1);64columnlimit(V2R5…)

DefinedinCREATETABLEstatement

MaynotbeNULL–requiresavalueDoesnotimplyanaccesspathChosenforlogicalcorrectnessMaybeNULLDefinesmostefficientaccesspathChosenforphysicalperformanceRowDistributionUsingaUPIOrderOrderNumberCustomerNumberOrderDateOrderStatusPKUPI7325732474157103722573847402718872022311213124/134/134/134/104/154/124/164/134/09OOCOCCCCCThePKcolumn(s)willoftenbeusedasaUPI.PIvaluesforOrder_Numberareknowntobeunique(it'saPK).

TeradatawilldistributedifferentindexvaluesevenlyacrossAMPs.ResultingrowdistributionamongAMPsisuniform.AMP1AMP2AMP3AMP4720224/09C73252710314/134/104/16CCC71881722524/134/15C73243C738414/12C4/13C741514/13C74023RowDistributionUsingaNUPIOrderNumberCustomerNumberOrderDateOrderStatusOrderPKNUPI7325732474157103722573847402718872022311213124/134/134/134/104/154/124/164/134/09OOCOCCCCCCustomer_NumbermaybethereferredaccesscolumnforORDERtable,thusagoodindexcandidate.ValuesforCustomer_Numberarenon-uniqueandthereforeaNUPI.RowswiththesamePIvaluedistributetothesameAMPcausingrowdistributiontobelessuniformorskewed.AMP1AMP2AMP3AMP4732524/130720224/09C738414/12C710314/10C741514/13C718814/13C740234/16C732434/130722524/15CRowDistributionUsingaHighlyNon-UniqueIndex

OrderOrderNumberCustomerNumberOrderDateOrderStatusPKNUPI7325732474157103722573847402718872022311213124/134/134/134/104/154/124/164/134/09OOCOCCCCCValuesforOrder_Statusarehighlynon-unique.Onlytwovaluesexist,soonlytwoMPswillbeusedinthistable.Thistablewillnotperformwellinparalleloperations.Highlynon-uniquecolumnsarepoorPIchoices.Thedegreeofuniquenessiscriticaltoefficiency.AMP1AMP2AMP3AMP4740234/16C72022722527415171881738414/09C4/15C4/13C4/13C4/12C710314/10O732434/130732524/130PartitionedPrimaryIndex

4AMPswithOrdersTableDefinedwithNPPI4AMPswithOrdersTableDefinedwithPPIonO_DateSecondaryIndexesAsecondaryindexisanalternatepathtotherowsofatable.Atablecanhavefrom0to32secondaryindexes.Secondaryindexes:?Donotaffecttabledistribution.?Addoverhead,bothintermsofdiskspaceandmaintenance.?Maybeaddedordroppeddynamicallyasneeded.?Arechosentoimprovetableperformance.

FullTableScansCUSTOMERCust_IDUSICust_NameNUSICust_PhoneNUPIEveryrowofthetablemustberead.AllAMPsscantheirportionofthetableinparallel.PrimaryIndexchoiceaffectsFTSperformance.Full-tablescanstypicallyoccurwheneither:?Theindexcolumnsarenotusedinthequery?Anindexisusedinanon-equalitytest?ArangeofvaluesisspecifiedfortheprimaryindexExamplesofFull-TableScans:SELECT*FROMcustomerWHERECust_PhoneLIKE‘524-';

SELECT*FROMcustomerWHERECust_Name<>‘Davis';

SELECT*FROMcustomerWHERECust_ID>1000;QuerySubmittingTools?BTEQ

>BasicTeradataQueryutility>Reportwritingandformattingfeatures>Interactiveandbatchqueries>Import/Exportacrossallplatforms

FastLoad?FastbatchmodeutilityforloadingnewtablesontotheTeradatadatabase?Canreloadpreviouslyemptiedtables?FullRestartcapability?ErrorLimitsandErrorTables,accessibleusingSQL?RestartableINMODroutinecapability?Abilitytoloaddatainseveralstages

FastLoadTeradataDatabaseHost

FastLoadCharacteristicsPurpose?Loadlargeamountsofdataintoanemptytableathighspeed.?ExecutefromTeradataservers,channel,ornetwork-attachedhosts.Concepts????Loadsintoanemptytablewithnosecondaryindexes.Hastwophases-createsanerrortableforeachphase.Statusofrunisdisplayed.Checkpointscanbetakenforrestarts.Restrictions?Onlyload1emptytablewith1FastLoadjob.?TheTeradataDatabasewillaccommodateupto15FL/ML/FEapplicationsatonetime.?TablesdefinedwithReferentialintegrity,secondaryindexes,JoinIndexes,HashIndexes,orTriggerscannotbeloadedwithFastLoad.–TableswithSoftReferentialIntegrity(V2R5)canbeloadedwithFastLoad.?DuplicaterowscannotbeloadedintoamultisettablewithFastLoad.?IfanAMPgoesdown,FastLoadcannotberestarteduntilitisbackonline.ASampleFastLoadScriptSETUPCreatethetable,ifitdoesn'talreadyexist.

LOGONtdpid/username,password;DROPTABLEAcct;DROPTABLEAcctErr1;DROPTABLEAcctErr2;

CREATETABLEAcct,FALLBACK(

AcctNumINTEGER

,NumberINTEGER

,Street

CHAR(25)

,City

CHAR(25)

,State

CHAR(2)

,Zip_CodeINTEGER)

UNIQUEPRIMARYINDEX(AcctNum);LOGOFF;

LOGONtdpid/username,password;

Starttheutility.BEGINLOADINGAcctErrorfilesmustbe

ERRORFILESAcctErr1,AcctErr2defined.

CHECKPOINT100000;

Checkpointis

optional.DEFINEin_AcctNum(INTEGER)

,in_Zip(INTEGER)

,in_Nbr(INTEGER)DEFINEtheinput;

,in_Street(CHAR(25))mustagreewith

,in_State(CHAR(2))hostdataformat.

,in_City(CHAR(25))FILE=data_infile1;INSERTmust

agreewithtableINSERTINTOAcctVALUES(definition.

:in_AcctNumPhase1begins.

,:in_NbrUnsortedblocks

,:in_Streetarewrittentodisk.

,:in_City

,:in_StatePhase2begins

,:in_Zip);withEND

LOADING.SortingENDLOADING;andwritingblocksLOGOFF;

todisk.MultiLoad??????Batchmodeutilitythatrunsonthehostsystem.FastLoad-liketechnology–TPump-likefunctionality.Supportsuptofivepopulatedtables.Multipleoperationswithonepassofinputfiles.Conditionallogicforapplyingchanges.SupportsINSERTs,UPDATEs,DELETEsandUPSERTs;typicallywithbatchinputsfromahostfile.Affecteddatablocksonlywrittenonce.HostandLANsupport.FullRestartcapability.TeradataDBHOSTINSERTsTABLEATABLEBTABLECTABLEDTABLEE????Errorreportingviaerrortables.?SupportforINMODs.MULTILOADUPDATEsDELETEsMultiLoadLimitations?Nodataretrievalcapability.?Concatenationofinputdatafilesisnotallowed.?Hostwillnotprocessarithmeticfunctions.?Hostwillnotprocessexponentiationoraggregates.?CannotprocesstablesdefinedwithUSI's,ReferentialIntegrity,JoinIndexes,HashIndexes,orTriggers.?ImporttasksrequireuseofPrimaryIndex.BasicMultiLoadStatements.LOGTABLE[restartlog_tablename];.LOGON[tdpid/userid,password];.BEGINMLOADTABLES[tablename1,...];.LAYOUT[layout_name];.FIELD…..;

.FILLER…..;

.DMLLABEL[label];.IMPORTINFILE[filename]

[FROMm][FORn][THRUk

]

[FORMATFASTLOAD|BINARY|TEXT|UNFORMAT|VARTEXT'c']

LAYOUT[layout_name]

APPLY[label][WHEREcondition];.ENDMLOAD;.LOGOFF;.FIELDfieldname{startposdatadesc}||fieldexp[NULLIFnullexpr]

[DROP{LEADING/TRAILING}{BLANKS/NULLS}

[[AND]{TRAILING/LEADING}{NULLS/BLANKS}]];

.FILLER[fieldname]startposdatadesc

;FastExport?ExportslargevolumesofformatteddatafromTeradatatoahostfileoruser-writtenapplication.?Takesadvantageofmultiplesessions.?Exportfrommultipletables.?UsesSupportEnvironment.?Fullyautomatedrestart.?Usesoneofthe“Loader”slots.

FastExportTeradataDatabaseHost

AFastExportScriptDefineRestartLogSpecifynumberofsessions