1、Teradata數據庫簡介,Teradata數據倉庫事業(yè)部 華南區(qū),Agenda,關于TERADATA Teradata數據庫原理 Teradata數據庫架構 Teradata數據庫工作原理 Teradata特性 Teradata數據倉庫構建 基本概念 常用工具介紹 管理的一些約定,關于TERADATA,Teradata最初產生于1976年，由加州理工學院和花旗銀行的高科技項目-創(chuàng)建一個能夠分析10的12次方 bytes 數據的系統(tǒng)。,1 Kilobyte = 103 = 1000 bytes 1 Megabyte= 106 = 1,000,000 bytes 1 Gigabyte= 109

2、= 1,000,000,000 bytes 1 Terabyte= 1012 = 1,000,000,000,000 bytes 1 Petabyte= 1015 = 1,000,000,000,000,000 bytes 1 Exabyte= 1018 = 1,000,000,000,000,000,000 bytes 1 Zetabyte= 1021 = 1,000,000,000,000,000,000,000 bytes 1 Yottabyte= 1024 = 1,000,000,000,000,000,000,000,000 bytes,關于TERADATA,Teradata 是全球

3、最大的專注于數據倉庫、咨詢服務及企業(yè)分析方案的提供商，憑借業(yè)界領先的數據庫、數據倉庫解決方案、性能卓越的可擴展平臺以及全球2000多個大型數據倉庫項目的客戶成功經驗，成就了公司在數據倉庫領域的創(chuàng)新領導地位。,niche players,visionaries,DATAllegro,Greenplum,Netezza,MySQL,Kognitio,Sand Technology,Sybase,Microsoft,IBM,Teradata,Oracle,leaders,challengers,as of September 2007,2007,2008,Microsoft-DATAllegro,n

4、iche players,visionaries,leaders,challengers,as of December 2008,Gartner 評選Teradata為數據倉庫領導廠商,5,Teradata數據庫原理,Teradata數據庫架構 Teradata數據庫工作原理 Teradata特性,通 信 界 面,存 取 模 塊 單 元 AMP,分 析 引 擎 PE,網 關,通 道,單 結 點 SMP,UNIX,PDE (并 行 數 據 庫 擴 展 環(huán) 境),TCP/IP,封 閉 主 機 系 統(tǒng),信 息 傳 遞 層,磁盤陣列,Teradata數據庫架構,BYNET信息傳遞網絡,TERADATA

5、的MPP架構,MPP系統(tǒng)與Teradata 多結點同時工作 數據庫由各結點共同擁有,MPP (Massive Parallel Processing) 海量并行處理服務器：由多個SMP服務器通過一定的結點互聯網絡進行連接，協同工作，完成相同的任務。從用戶的角度來看是一個系統(tǒng)！,TERADATA的MPP架構,高速并行處理的BYNET技術 更適合復雜/即席查詢 通過硬件技術實現Teradata的多個子任務均衡并行,SMP 處理器節(jié)點,光纖I/O連接,MPP 數據高速交換層,BYNET,2,DA Controllers,(w/Cache),BYNET,高性能磁盤陣列,DA Controllers,(

6、w/Cache),DA Controllers,(w/Cache),DA Controllers,(w/Cache),Memory,Memory,Memory,Memory,CLIQUE,Teradata并行處理架構,每個并行單元只管理自己的數據,AMP1的數據,AMP1,Teradata并行的機制,其他關系數據庫 “有條件的并行”,Teradata “無條件的并行”,時間,最終結果集,并行處理性能,線性擴展能力 最大化的利用每個節(jié)點的資源 可靈活配置,Shared Nothing Software,MPP小結,Teradata MPP 架構 使用當前最快的CPU 最好的擴展性 使用 shar

7、ed-nothing MPP架構以達到線性擴展,Teradata數據倉庫構建,基本概念 常用工具介紹 管理的一些約定,數據處理的演變,數據倉庫（Data Warehouse，可簡寫為DW ）,數據倉庫是決策支持系統(tǒng)（DSS）和聯機分析（OLAP）應用數據源的結構化數據環(huán)境。數據倉庫研究和解決從數據庫中獲取信息的問題。數據倉庫的特征在于面向主題、集成性、穩(wěn)定性和時變性。,ATM,People Soft,POS,Cognos,Access,BizObjects,Teradata RDBMS,ETL,ETL是Extraction-Transformation-Loading的縮寫，負責將分布的、異構

8、數據源中的數據如關系數據、平面數據文件等抽取到臨時中間層后進行清洗、轉換、集成，最后加載到數據倉庫或數據集市中，成為聯機分析處理、數據挖掘的基礎。,ETL,ETL是構建數據倉庫的重要一環(huán)，用戶從數據源抽取出所需的數據，經過數據清洗,最終按照預先定義好的數據倉庫模型，將數據加載到數據倉庫中去。,主索引是表中的一個或多個字段，用于確定數據的物理分布 每個表的數據根據 PI (主索引)平均分布在不同的AMP 通過Hash算法實現數據自動分布 無需數據重組、重新分區(qū)、數據分布管理 可以是唯一或非唯一 一個表不會有兩個主索引 主索引的選擇，關系到能否很好的發(fā)揮Teradata數據庫的優(yōu)勢-并行處理。,V

9、AMP1 VAMP2 VAMP3 VAMP4 VAMPn,Table 1 Table 2 Table 3,Primary Index,Teradata Parallel Hash Function,主索引（Primary Index）,PI:cust_id,PI:cust_id,PI:acc_id,主鍵和主索引,Accessing via a Unique Primary Index,AMP 1,AMP 2,AMP 3,AMP 4,Cust,Name,Phone,UPI,Base Table,Cust,Name,Phone,UPI,Base Table,Cust,Name,Phone,UPI,

10、Base Table,Cust,Name,Phone,UPI,Base Table,CREATE TABLE Customer (Cust INT ,Name CHAR(10) ,Phone CHAR(8) ) UNIQUE PRIMARY INDEX (Cust);,SELECT * FROM customer WHERE cust = 45;,PE,BYNET,Cust,Name,Phone,CUSTOMER table,37White555-4444 98Brown333-9999 74Smith555-6666 95Peters555-7777 27Jones222-8888 56Sm

11、ith555-7777 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-3333,UPI,PK,Row Distribution Using a UPI,AMP 1,AMP 2,AMP 3,AMP 4,7202,2,4/09,C,7402,3,4/16,C,7325,2,4/13,C,7225,2,4/

12、15,C,7188,1,4/13,C,7384,1,4/12,C,7324,3,4/13,C,7103,1,4/10,C,Order,7415,1,4/13,C,The 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 different index values evenly across AMPs. Resulting row distribution among AMPs is uni

13、form.,Row Distribution Using a NUPI,Order,7225,2,4/15,C,7325,2,4/13,0,7415,1,4/13,C,7384,1,4/12,C,7324,3,4/13,0,7402,3,4/16,C,7103,1,4/10,C,AMP 1,AMP 2,AMP 4,7202,2,4/09,C,7188,1,4/13,C,AMP 3,Customer_Number may be the referred access column for ORDER table, thus a good index candidate. Values for C

14、ustomer_Number are non-unique and therefore a NUPI. Rows with the same PI value distribute to the same AMP causing row distribution to be less uniform or skewed.,Row Distribution Using a Highly Non-Unique Index,Order,Values for Order_Status are highly non-unique. Only two values exist, so only two M

15、Ps will be used in this table. This table will not perform well in parallel operations. Highly non-unique columns are poor PI choices. The degree of uniqueness is criticalto efficiency.,Partitioned Primary Index,4 AMPs with Orders Table Defined with NPPI,4 AMPs with Orders Table Defined with PPI on

16、O_Date,Secondary Indexes,A secondary index is an alternate path to the rows of a table. A table can have from 0 to 32 secondary indexes. Secondary 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

17、chosen to improve table performance.,Full Table Scans,SELECT * FROM customer WHERE Cust_Phone LIKE 524-; SELECT * FROM customer WHERE Cust_Name Davis; 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 tabl

18、e 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 index is used in a non-equality test A range of values is specified for the primary index,Query Submitting Tools,BTEQ Basic Teradata Query utility

19、 Report writing and formatting features Interactive and batch queries Import/Export across all platforms,FastLoad,FastLoad Characteristics,A Sample FastLoad Script,MultiLoad,MultiLoad Limitations,No data retrieval capability. Concatenation of input data files is not allowed. Host will not process arithmetic functions. Host will not process exponentiation or aggregates. Cannot process tables defined with USIs, Referential Integrity, Join Indexes, Hash Indexes, or Triggers. Import tasks requir