eradata数据仓库简介课件

《eradata数据仓库简介课件》由会员分享，可在线阅读，更多相关《eradata数据仓库简介课件（43页珍藏版）》请在装配图网上搜索。

1、Teradata ConfidentialTeradata数据库简介Teradata数据仓库事业部Agendal关于关于TERADATAlTeradata数据库原理数据库原理lTeradata数据库架构数据库架构lTeradata数据库工作原理数据库工作原理lTeradata特性特性lTeradata数据仓库构建数据仓库构建l基本概念基本概念l常用工具介绍常用工具介绍l管理的一些约定管理的一些约定关于关于TERADATA Teradata最初产生于1976年，由加州理工学院和花旗银行的高科技项目-创建一个能够分析10的12次方 bytes 数据的系统。1 Kilobyte = 103 = 10

2、00 bytes1 Megabyte= 106 = 1,000,000 bytes1 Gigabyte= 109 = 1,000,000,000 bytes1 Terabyte= 1012 = 1,000,000,000,000 bytes1 Petabyte= 1015 = 1,000,000,000,000,000 bytes1 Exabyte= 1018 = 1,000,000,000,000,000,000 bytes1 Zetabyte= 1021 = 1,000,000,000,000,000,000,000 bytes1 Yottabyte= 1024 = 1,000,000,0

3、00,000,000,000,000,000 bytes关于关于TERADATATeradata 是全球最大的专注于数据仓库、咨询服务及企业分析方案的提供商，凭借业界领先的数据库、数据仓库解决方案、性能卓越的可扩展平台以及全球2000多个大型数据仓库项目的客户成功经验，成就了公司在数据仓库领域的创新领导地位。 ability to executecompleteness of visionniche playersvisionariesDATAllegroGreenplumNetezzaMySQLKognitioSand TechnologySybaseMicrosoftIBMTeradata

4、Oracleleaderschallengersas of September 200720072008ability to executecompleteness of visionSybaseOracleIBMTeradataMicrosoft-DATAllegroNetezzaGreenplumHPSun Microsystems-MySQLKognitioSand TechnologyVerticaIngresIlluminate Solutions1010dataniche playersvisionariesleaderschallengersas of December 2008

5、Gartner 评选Teradata为数据仓库领导厂商 5Teradata数据库原理数据库原理lTeradata数据库架构数据库架构lTeradata数据库工作原理数据库工作原理lTeradata特性特性 通通 信信 界界 面面 存存 取取 模模 块块 单单 元元 AMP 分分 析析 引引 擎擎PEPE1PE2AMP1AMP2AMP4AMP3 网网 关关 通通 道道 单单 结结 点点 SMPUNIXPDE (并并 行行 数数 据据 库库 扩扩 展展 环环 境境)TCP/IP 封封 闭闭 主主 机机 系系 统统 信息传递层 磁盘阵列磁盘阵列Teradata数据库架构数据库架构BYNET信息信息传

6、递网络传递网络 TERADATA的MPP架构CPUCPUCPUCPUMemoryCPUCPUCPUCPUMemoryMPP系统与系统与Teradata多结点同时工作数据库由各结点共同拥有MPP (Massive Parallel Processing)海量并行处理服务器：由多个SMP服务器通过一定的结点互联网络进行连接，协同工作，完成相同的任务。从用户的角度来看是一个系统！从用户的角度来看是一个系统！TERADATA的MPP架构 高速并行处理的BYNET技术 更适合复杂/即席查询 通过硬件技术实现Teradata的多个子任务均衡并行SMP 处理器节点处理器节点光纤光纤I/O连接连接MPP 数据

7、高速交换层数据高速交换层 BYNET 2DA Controllers(w/Cache) BYNET 高性能磁盘阵列高性能磁盘阵列DA Controllers(w/Cache)DA Controllers(w/Cache)DA Controllers(w/Cache) Memory CPU CPU CPU CPU Memory Memory CPU CPU CPU CPU Memory Memory CPU CPU CPU CPU Memory Memory CPU CPU CPU CPU MemoryCLIQUE PARSING ENGINE (PE)SQL Parser & Optimize

8、rQuery Step Dispatcher Access Module Processors (AMP)V-PE BY-NetV-AMPV-AMPV-AMPV-AMPV-PETeradata并行处理架构AMP4的数据AMP3的数据AMP2的数据每每个个并并行行单单元元只只管管理理自自己己的的数数据据Building IndexesBackup & RecoveryRow LockingTransactionJournalizingSortingReading WritingLoadingAggregatingAMPAMPAMPAMP1的数据AMP1BYNETTeradata并行的机制其他关系

9、数据库其他关系数据库“有条件的并行有条件的并行”TeradataTeradata“无条件的并行无条件的并行”时间时间初始查询查询优化查询并行扫描链接聚合排序收敛最终结果集并行处理性能 线性扩展能力 最大化的利用每个节点的资源 可灵活配置VPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsVPROCsAmpsShared Nothing Softwar

10、eEffective CPU Scaling Performance0246810123456789101112Number of CPUsEffective CPU PerformanceTeradata WorldMark (CPU = 1.00, 88% 1-4 CPU SMP scaling, 98% pernode)IBM/SUN Best Case (CPU = 0.78, 91% SMP scaling)IBM/SUN Typical (CPU = 0.67, 88% SMP scaling)MPP小结 Teradata MPP 架构 使用当前最快的CPU 最好的扩展性 使用 s

11、hared-nothing MPP架构以达到线性扩展Teradata数据仓库构建数据仓库构建l基本概念基本概念l常用工具介绍常用工具介绍l管理的一些约定管理的一些约定数据处理的演变数据仓库（数据仓库（Data Warehouse，可简写为，可简写为DW ）数据仓库是决策支持系统（DSS）和联机分析（OLAP）应用数据源的结构化数据环境。数据仓库研究和解决从数据库中获取信息的问题。数据仓库的特征在于面向主题、集成性、稳定性和时变性。Operational DataData WarehouseAccess ToolsEnd UsersATMPeople SoftPOSCognosAccessBiz

12、ObjectsTeradata RDBMSETLETL是是Extraction-Transformation-Loading的缩写，负的缩写，负责将分布的、异构数据源中的数据如关系数据、平面数据文件责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后加载到数等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。ETLETL是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数据

13、仓库中去。 主索引是表中的一个或多个字段，用于确定数据的物理分布每个表的数据根据 PI (主索引)平均分布在不同的AMP 通过Hash算法实现数据自动分布 无需数据重组、重新分区、数据分布管理 可以是唯一或非唯一 一个表不会有两个主索引主索引的选择，关系到能否很好的发挥Teradata数据库的优势-并行处理。VAMP1 VAMP2 VAMP3 VAMP4 VAMPnTable 1 Table 2 Table 3Primary IndexTeradata Parallel Hash FunctionPDMPDMPDMPDMPDMPDMPDMPDMPDM主索引（Primary Index）PI:c

14、ust_idPI:cust_idPI:acc_id主键和主索引Indexes are conceptually different from keys.A PK is a relational modeling convention which allows each row to be uniquely identified.A PI is a Teradata convention which determines how the row will be stored and accessed.A significant percentage of tables may use the s

15、ame columns for both the PK and the PI.A well-designed database will use a PI that is different from the PK for some tables.Primary KeyPrimary IndexLogical concept of data modelingPhysical mechanism for access and storageTeradata doesnt need to recognize Each table must have exactly one primary inde

16、xNo limit on number of columns 16 column limit (V2R4.1); 64 column limit (V2R5 )Documented in data model Defined in CREATE TABLE statement(Optional in CREATE TABLE)Must be uniqueMay be unique or non-uniqueIdentifies each rowIdentifies 1 (unique) or multiple rows (non-unique)Values should not change

17、Values may be changed (Delete + Insert)May not be NULL requires a value May be NULLDoes not imply an access path Defines most efficient access pathChosen for logical correctness Chosen for physical performanceAccessing via a Unique Primary IndexAMP 1AMP 2AMP 3AMP 4CustNamePhoneUPIBase TableCustNameP

18、honeUPIBase TableAdams 111-222231Smith555-666674Brown 333-999998Young 777-444412CustNamePhoneUPIBase TableAdams 444-666645Smith111-666649Smith555-777756Marsh 888-222251Rice666-555584Black444-555562Jones 777-666677Peters 555-777795CustNamePhoneUPIBase TableSmith222-333340Adams 666-777772Jones 222-888

19、827White555-444437CREATE TABLE Customer(Cust INT,Name CHAR(10),Phone CHAR(8) )UNIQUE PRIMARY INDEX (Cust);SELECT * FROM customer WHERE cust = 45;PEUPI = 45HashingAlgorithmBYNETCustNamePhoneCUSTOMER table 37White555-4444 98Brown333-9999 74Smith555-6666 95Peters555-7777 27Jones222-8888 56Smith555-7777

20、 45Adams444-6666 84 Rice 666-5555 49 Smith 111-6666 51 Marsh 888-2222 31 Adams 111-2222 62 Black 444-5555 12 Young 777-4444 77 Jones 777-6666 72 Adams 666-7777 40 Smith 222-3333UPIPKRow Distribution Using a UPIAMP 1AMP 2AMP 3AMP 4OrderNum berCustom erNum berOrderDateOrderStatusPKUPI73257324741571037

21、22573847402718872022311213124/134/134/134/104/154/124/164/134/09OOCOCCCCC720224/09C740234/16C732524/13C722524/15C718814/13C738414/12C732434/13C710314/10COrder741514/13CThe PK column(s) willoften be used as a UPI.PI values for Order_Number are known to be unique (its a PK).Teradata will distribute di

22、fferent index values evenly across AMPs.Resulting row distribution among AMPs is uniform.Row Distribution Using a NUPIOrderNum berCustom erNum berOrderDateOrderStatusPKNUPI7325732474157103722573847402718872022311213124/134/134/134/104/154/124/164/134/09OOCOCCCCCOrder7225 24/15C7325 24/1307415 14/13C

23、7384 14/12C7324 34/1307402 34/16C7103 14/10CAMP 1AMP 2AMP 47202 24/09C7188 14/13CAMP 3Customer_Number may be the referred access column for ORDER table, thus a good index candidate.Values for Customer_Number are non-unique and therefore a NUPI.Rows with the same PI value distribute to the same AMP c

24、ausing row distribution to be less uniform or skewed.Row Distribution Using a Highly Non-Unique Index OrderNum berCustom erNum berOrderDateOrderStatusPKNUPI7325732474157103722573847402718872022311213124/134/134/134/104/154/124/164/134/09OOCOCCCCCOrderAMP 1AMP 2AMP 47325 24/1307324 34/1307103 14/10O7

25、325 24/1307225 24/15C7415 14/13C7384 14/12C7402 34/16C7202 24/09C7188 14/13CAMP 3Values for Order_Status are highly non-unique.Only two values exist, so only two MPs will be used in this table.This table will not perform well in parallel operations.Highly non-unique columns are poor PI choices.The d

26、egree of uniqueness is criticalto efficiency.Partitioned Primary Index 4 AMPs with Orders Table Defined with NPPI4 AMPs with Orders Table Defined with PPI on O_DateSecondary IndexesA secondary index is an alternate path to the rows of a table.A table can have from 0 to 32 secondary indexes.Secondary

27、 indexes: Do not affect table distribution. Add overhead, both in terms of disk space and maintenance. May be added or dropped dynamically as needed. Are chosen to improve table performance.Full Table ScansSELECT * FROM customer WHERE Cust_Phone LIKE 524-;SELECT * FROM customer WHERE Cust_Name Davis

28、;SELECT * FROM customer WHERE Cust_ID 1000;Examples of Full-Table Scans:Every row of the table must be read.All AMPs scan their portion of the table in parallel.Primary Index choice affects FTS performance.Full-table scans typically occur when either: The index columns are not used in the query An i

29、ndex is used in a non-equality test A range of values is specified for the primary indexCUSTOMERCust_IDCust_NameCust_PhoneUSINUPINUSIQuery Submitting Tools BTEQ Basic Teradata Query utility Report writing and formatting features Interactive and batch queries Import/Export across all platformsFastLoa

30、dFast batch mode utility for loading new tables onto the Teradata databaseCan reload previously emptied tablesFull Restart capabilityError Limits and Error Tables, accessible using SQLRestartable INMOD routine capabilityAbility to load data in several stagesTeradata DatabaseHostFastLoadFastLoad Char

31、acteristicsLoad large amounts of data into an empty table at high speed.Execute from Teradata servers, channel, or network-attached hosts.Loads into an empty table with no secondary indexes.Has two phases - creates an error table for each phase.Status of run is displayed.Checkpoints can be taken for

32、 restarts.Only load 1 empty table with 1 FastLoad job. The Teradata Database will accommodate up to 15 FL/ML/FE applications at one time.Tables defined with Referential integrity, secondary indexes, Join Indexes, Hash Indexes, or Triggers cannot be loaded with FastLoad.Tables with Soft Referential I

33、ntegrity (V2R5) can be loaded with FastLoad.Duplicate rows cannot be loaded into a multiset table with FastLoad. If an AMP goes down, FastLoad cannot be restarted until it is back online.A Sample FastLoad ScriptLOGON tdpid/username,password;DROP TABLE Acct;DROP TABLE AcctErr1;DROP TABLE AcctErr2;CRE

34、ATE TABLE Acct, FALLBACK ( AcctNumINTEGER,NumberINTEGER,StreetCHAR(25),CityCHAR(25),StateCHAR(2),Zip_CodeINTEGER)UNIQUE PRIMARY INDEX (AcctNum);LOGOFF; LOGON tdpid/username,password;BEGIN LOADING AcctERRORFILES AcctErr1, AcctErr2CHECKPOINT 100000;DEFINE in_AcctNum (INTEGER),in_Zip(INTEGER),in_Nbr(IN

35、TEGER),in_Street(CHAR(25),in_State(CHAR(2),in_City(CHAR(25) ;INSERT INTO Acct VALUES ( :in_AcctNum,:in_Nbr,:in_Street,:in_City,:in_State,:in_Zip);END LOADING;LOGOFF; SETUPCreate the table, if it doesnt already exist. Start the utility.Error files must be defined.Checkpoint is optional.DEFINE the inp

36、ut; must agree with host data format.INSERT must agree with table definition. Phase 1 begins. Unsorted blocks are written to disk.Phase 2 begins with END LOADING. Sorting and writing blocks to disk.MultiLoadBatch mode utility that runs on the host system. FastLoad-like technology TPump-like function

37、ality.Supports up to five populated tables.Multiple operations with one pass of input files.Conditional logic for applying changes.Supports INSERTs, UPDATEs, DELETEs and UPSERTs; typically with batch inputs from a host file.Affected data blocks only written once.Host and LAN support.Full Restart cap

38、ability.Error reporting via error tables.Support for INMODs.MULTILOADHOSTTeradata DBUPDATEsINSERTsDELETEsTABLE ATABLE BTABLE CTABLE DTABLE EMultiLoad LimitationsNo data retrieval capability.Concatenation of input data files is not allowed.Host will not process arithmetic functions.Host will not proc

39、ess exponentiation or aggregates. Cannot process tables defined with USIs, Referential Integrity, Join Indexes, Hash Indexes, or Triggers.Import tasks require use of Primary Index.Basic MultiLoad Statements.LOGTABLE restartlog_tablename ;.LOGON tdpid/userid, password ;.BEGIN MLOAD TABLES tablename1,

40、 . ;.LAYOUT layout_name ;.FIELD . ;.FILLER . ;.DML LABEL label ;.IMPORT INFILE FROM m FOR n THRU k FORMAT FASTLOAD | BINARY | TEXT | UNFORMAT | VARTEXT c LAYOUT layout_name APPLY label WHERE condition ;.END MLOAD ;.LOGOFF ;.FIELD fieldname startpos datadesc | fieldexp NULLIF nullexpr DROP LEADING /

41、TRAILING BLANKS / NULLS AND TRAILING / LEADING NULLS / BLANKS ;.FILLER fieldname startpos datadesc ;FastExportExports large volumes of formatted data from Teradata to a host user-written application.Takes advantage of multiple sessions.Export from multiple tables.Uses Support Environment.Fully autom

42、ated restart. Uses one of the “Loader” slots.Teradata DatabaseHostFastExportA FastExport Script.LOGTABLE RestartLog1_fxp; .RUN ; .SETCityName TO Los Angeles;.SETZipCode TO 90666;.BEGIN EXPORT SESSIONS 4 ;.EXPORT OUTFILE custacct_data;SELECT A.Account_Number, C.Last_Name, C.First_Name, A.Balance_Curr

43、entFROM Accounts AINNER JOINAccounts_Customer ACINNER JOINCustomer CONC.Customer_Number = AC.Customer_NumberONA.Account_Number = AC.Account_NumberWHEREA.City = &CityNameANDA.Zip_Code = &ZipCodeORDER BY 1 ;.END EXPORT ;.LOGOFF ;Define Restart LogSpecify number of sessionsDestination fileSend request.

44、Terminate sessionsVia a SELECT, specify the columns and rows to export.其他常用工具其他常用工具Teradata AdministratorTeradata SQL AssistantTeradata ManagerTeradata Ole Load管理中的一些约束管理中的一些约束数据库和用户的管理数据库对象的管理数据库空间的管理数据库和用户的管理 User DBCUser SYSDBAUser AUser C User D 用户和数据库分别归属到两个用户下面。用户和数据库分别归属到两个用户下面。 针对不同类型的对象，分别创

45、建库，便于管理。针对不同类型的对象，分别创建库，便于管理。 最好创建一个专门用于存放临时表的库。最好创建一个专门用于存放临时表的库。User BDatabase ADatabase B 数据库对象的管理对象名称的约束Prefix_subject_body_suffix_YYYYMMDD说明：Prefix是表明这个对象的类型，如TB表示对象为表，VW表示对象为视图等等，subject一般是数据模型中所定义的。例如：TB_cust_cust_his,这是一个客户域里面的客户历史表。对于用户创建的临时表，最好使用用户名（实名用户）作为前缀，并加上日期后缀，以便在清理空间时确认。表名最多不要超过32个字符(64位系统将解决这个问题) 对于FastLoad，MultiLoad中定义的表名不要超过25个字符。 数据库空间的管理制定数据仓库数据存储策略 比如历史表保存多久的数据,明细表和汇总表保存多久的数据及时清理临时表，长期不使用的表。定期检查库表的数据分布情况，对分布不均匀的表进行重新分 布。 谢谢 谢谢 ！