Teradata数据库配置说明
Teradata RDBMS教程说明书
About the T utorialTeradata is a popular Relational Database Management System (RDBMS) suitable for large data warehousing applications. It is capable of handling large volumes of data and is highly scalable. This tutorial provides a good understanding of Teradata Architecture, various SQL commands, Indexing concepts and Utilities to import/export data.AudienceThis tutorial is designed for software professionals who are willing to learn Teradata concepts and become a Teradata developer. By the end of this tutorial, you will have gained intermediate level of expertise in Teradata.PrerequisitesYou should have a basic understanding of Relational concepts and basic SQL. It will be good if you have worked with any other RDBMS product.Copyright & DisclaimerCopyright 2018 by Tutorials Point (I) Pvt. Ltd.All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher.We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or inthistutorial,******************************************T able of ContentsAbout the Tutorial (i)Audience (i)Prerequisites (i)Copyright & Disclaimer (i)Table of Contents (ii)PART 1: TERADATA BASICS (1)1.Teradata - Introduction (2)What is Teradata? (2)History of Teradata (2)Features of Teradata (2)2.Teradata – Installation (4)Installation Steps for Windows (4)Starting BTEQ (8)3.Teradata – Architecture (9)Components of Teradata (9)Storage Architecture (10)Retrieval Architecture (11)4.Teradata – Relational Concepts (12)5.Teradata – Data Types (14)6.Teradata – Tables (16)Table Types (16)Create Table (16)Alter Table (18)Drop Table (19)7.Teradata – Data Manipulation (20)Insert Records (20)Insert from Another Table (21)Update Records (22)Delete Records (23)8.Teradata – SELECT Statement (24)WHERE Clause (25)ORDER BY (25)GROUP BY (26)9.Teradata – Logical & Conditional Operators (27)BETWEEN (28)IN (28)NOT IN (29)10.Teradata – SET Operators (30)UNION (30)UNION ALL (31)MINUS/EXCEPT (33)11.Teradata – String Manipulation (35)12.Teradata – Date/Time Functions (36)Date Storage (36)EXTRACT (36)INTERVAL (37)13.Teradata – Built-in Functions (39)14.Teradata – Aggregate Functions (40)15.Teradata – CASE & COALESCE (42)CASE Expression (42)COALESCE (43)NULLIF (44)16.Teradata – Primary Index (45)Unique Primary Index (UPI) (45)Non Unique Primary Index (NUPI) (46)17.Teradata – Joins (47)INNER JOIN (47)OUTER JOIN (48)CROSS JOIN (50)18.Teradata – SubQueries (51)PART 2: TERADATA ADVANCED (53)19.Teradata – Table Types (54)Derived Table (54)Volatile Table (55)Global Temporary Table (55)20.Teradata – Space Concepts (57)Permanent Space (57)Spool Space (57)Temp Space (57)21.Teradata – Secondary Index (58)Unique Secondary Index (USI) (58)Non Unique Secondary Index (NUSI) (58)22.Teradata – Statistics (59)Collecting Statistics (59)Viewing Statistics (60)23.Teradata – Compression (61)24.Teradata – EXPLAIN (62)Full Table Scan (FTS) (62)Unique Primary Index (63)Unique Secondary Index (63)Additional Terms (64)25.Teradata – Hashing Algorithm (65)26.Teradata – JOIN INDEX (67)Single Table Join Index (67)Multi Table Join Index (69)Aggregate Join Index (69)27.Teradata – Views (71)Create a View (71)Using Views (72)Modifying Views (72)Drop View (73)28.Teradata – Macros (74)Create Macros (74)Executing Macros (75)Parameterized Macros (76)Executing Parameterized Macros (76)29.Teradata – Stored Procedure (77)Creating Procedure (77)Executing Procedures (78)30.Teradata – JOIN Strategies (80)Join Methods (80)Merge Join (80)Nested Join (82)Product Join (82)31.Teradata – Partitioned Primary Index (83)32.Teradata – OLAP Functions (86)33.Teradata – Data Protection (89)Transient Journal (89)Fallback (89)Down AMP Recovery Journal (90)Cliques (90)Hot Standby Node (90)RAID (91)34.Teradata – User Management (92)Users (92)Accounts (93)Grant Privileges (93)Revoke Privileges (94)35.Teradata – Performance Tuning (95)36.Teradata – FastLoad (97)How FastLoad Works (97)Executing a FastLoad Script (98)FastLoad Terms (99)37.Teradata – MultiLoad (100)Limitation (100)How MultiLoad Works (100)Executing a MultiLoad Script (102)38.Teradata – FastExport (103)Executing a FastExport Script (104)FastExport Terms (104)39.Teradata – BTEQ (105)40.Teradata – Questions & Answers (108)Part 1: Teradata Basics1.TeradataWhat is T eradata?Teradata is one of the popular Relational Database Management System. It is mainly suitable for building large scale data warehousing applications. Teradata achieves this by the concept of parallelism. It is developed by the company called Teradata.History of T eradataFollowing is a quick summary of the history of Teradata, listing major milestones.1979– Teradata was incorporated1984– Release of first database computer DBC/10121986–Fortune magazine names Teradata as ‘Product of the Year’1999– Largest database in the world using Teradata with 130 Terabytes2002– Teradata V2R5 released with Partition Primary Index and compression2006– Launch of Teradata Master Data Management solution2008– Teradata 13.0 released with Active Data Warehousing2011– Acquires Teradata Aster and enters into Advanced Analytics Space2012– Teradata 14.0 introduced2014– Teradata 15.0 introducedFeatures of T eradataFollowing are some of the features of Teradata:●Unlimited Parallelism: Teradata database system is based on Massively ParallelProcessing (MPP) Architecture. MPP architecture divides the workload evenly across the entire system. Teradata system splits the task among its processes and runs them in parallel to ensure that the task is completed quickly.●Shared Nothing Architecture:Teradata’s architecture is called as SharedNothing Architecture. Teradata Nodes, its Access Module Processors (AMPs) and the disks associated with AMPs work independently. They are not shared with others.●Linear Scalability:Teradata systems are highly scalable. They can scale up to2048 Nodes. For example, you can double the capacity of the system by doubling the number of AMPs.●Connectivity:Teradata can connect to Channel-attached systems such asMainframe or Network-attached systems.Teradata ●Mature Optimizer:Teradata optimizer is one of the matured optimizer in themarket. It has been designed to be parallel since its beginning. It has been refined for each release.●SQL: Teradata supports industry standard SQL to interact with the data stored intables. In addition to this, it provides its own extension.●Robust Utilities: Teradata provides robust utilities to import/export data from/toTeradata system such as FastLoad, MultiLoad, FastExport and TPT.●Automatic Distribution: Teradata automatically distributes the data evenly to thedisks without any manual intervention.Teradata Teradata provides Teradata express for VMWARE which is a fully operational Teradata virtual machine. It provides up to 1 terabyte of storage. Teradata provides both 40GB and 1TB version of VMware.PrerequisitesSince the VM is 64 bit, your CPU must support 64-bit.Installation Steps for WindowsStep 1: Download the required VM version from the link, /download/database/teradata-express-for-vmware-playerStep 2: Extract the file and specify the target folder.Step 3: Download the VMWare Workstation player from the link, https:///web/vmware/downloads . It is available for both Windows and Linux. Download the VMWARE workstation player for Windows.Step 4: Once the download is complete, install the software.Step 5: After the installation is complete, run the VMWARE client.Step 6: Select 'Open a Virtual Machine'. Navigate through the extracted Teradata VMWare folder and select the file with extension .vmdk.2.Step 7: Teradata VMWare is added to the VMWare client. Select the added Teradata VMware and click ‘Play Virtual Machine’.Step 8: If you get a popup on software updates, you can select ‘Remind Me Later’.Step 9: Enter the user name as root, press tab and enter password as root and again press Enter.Step 10: Once the following screen appears on the desktop, double-click on ‘root’s home’. Then double-click on ‘Genome’s Terminal’. This will open the Shell.Step 11: From the following shell, enter the command /etc/init.d/tpa start. This will start the Teradata server.Starting BTEQBTEQ utility is used to submit SQL queries interactively. Following are the steps to start BTEQ utility.Step 1: Enter the command /sbin/ifconfig and note down the IP address of the VMWare. Step 2: Run the command bteq. At the logon prompt, enter the command.Logon <ipaddress>/dbc,dbc; and enter At the password prompt, enter password as dbc; You can log into Teradata system using BTEQ and run any SQL queries.TeradataTeradata architecture is based on Massively Parallel Processing (MPP) architecture. The major components of Teradata are Parsing Engine, BYNET and Access Module Processors (AMPs). The following diagram shows the high level architecture of a Teradata Node.Components of T eradataThe key components of Teradata are as follows:∙Node: It is the basic unit in Teradata System. Each individual server in a Teradata system is referred as a Node. A node consists of its own operating system, CPU, memory, own copy of Teradata RDBMS software and disk space. A cabinet consists of one or more Nodes.∙Parsing Engine: Parsing Engine is responsible for receiving queries from the client and preparing an efficient execution plan. The responsibilities of parsing engine are:o Receive the SQL query from the client.o Parse the SQL query check for syntax errors.o Check if the user has required privilege against the objects used in the SQLquery.3.o Check if the objects used in the SQL actually exists.o Prepare the execution plan to execute the SQL query and pass it to BYNET.o Receives the results from the AMPs and send to the client.∙Message Passing Layer:Message Passing Layer called as BYNET, is the networking layer in Teradata system. It allows the communication between PE and AMP and also between the nodes. It receives the execution plan from Parsing Engine and sends to AMP. Similarly, it receives the results from the AMPs and sends to Parsing Engine.∙Access Module Processor (AMP): AMPs, called as Virtual Processors (vprocs) are the one that actually stores and retrieves the data. AMPs receive the data and execution plan from Parsing Engine, performs any data type conversion, aggregation, filter, sorting and stores the data in the disks associated with them.Records from the tables are evenly distributed among the AMPs in the system. Each AMP is associated with a set of disks on which data is stored. Only that AMP can read/write data from the disks.Storage ArchitectureWhen the client runs queries to insert records, Parsing engine sends the records to BYNET. BYNET retrieves the records and sends the row to the target AMP. AMP stores these records on its disks. Following diagram shows the storage architecture of Teradata.Retrieval ArchitectureWhen the client runs queries to retrieve records, the Parsing engine sends a request to BYNET. BYNET sends the retrieval request to appropriate AMPs. Then AMPs search their disks in parallel and identify the required records and sends to BYNET. BYNET then sends the records to Parsing Engine which in turn will send to the client. Following is the retrieval architecture of Teradata.End of ebook previewIf you liked what you saw…Buy it from our store @ https://。
Teradata数据仓库产品介绍v1.0
最优性能 = CPU 和 I/O 带宽的平衡
只增加CPU: 没有足够的磁盘保证 CPU得到充分利用
CPU处理能力增强
当前系统
只增加磁盘: 没有足够的 CPU来充分利用这些磁盘
磁盘个数
Teradata 的推荐配置
Balanced Price/Performance Configuration
• 推荐配置需要找到节点CPU和内存、磁盘阵列的I/O（磁盘的多少 ）之间一种平衡
• 目标就是为系统提供一种性价比合理的配置
CPU Utilization
100%
I/O
Constrained
80%
60%
Balanced System
Super Saturated
40%
20%
Increase in number of drives
I/O = Number Disk Drives and Controllers
F
SOLARIS
ORACLE DBMS
8
10
15
13
10
8
17
14
11.9
4
5 IBM BLADECENTER
B
AIX
DB2
11
9
11
12
10
9
17
14
11.6
5
6 IBM SYSTEM z9
F
ZOS
DB2
11
14
10
8
10
9
17
14
11.5
6
7 HP BLADESYSTEM
B
HP-UX
ORACLE DBMS
数据仓库系统不是简单的 主机、存储、数据库的堆积
Teradata基础教程
Teradata基础教程1. 数据仓库(Data Warehouse)：Teradata是一种专门用于构建和管理数据仓库的系统。
数据仓库是一个集成、主题导向、可变和持续的数据集，用于支持企业的决策制定过程。
2. 分布式架构：Teradata采用分布式架构，将数据存储在多个节点上，使得数据的访问和处理更加高效和可扩展。
3. AMP(Access Module Processor)：AMP是Teradata的核心组件，负责存储和处理数据。
每个节点上都有多个AMP，它们负责将数据分片存储在磁盘上，并处理查询请求。
4. Vantage：Vantage是Teradata的最新版本，提供了集成分析引擎、存储、数据管理和高级分析功能。
1. 创建数据库：使用CREATE DATABASE语句可以创建数据库。
例如，CREATE DATABASE mydatabase;2. 创建表格：使用CREATE TABLE语句可以创建表格。
例如，CREATE TABLE mytable (column1 INT, column2 VARCHAR(100));3. 插入数据：使用INSERT INTO语句可以插入数据到表格中。
例如，INSERT INTO mytable VALUES (1, 'data1');4. 查询数据：使用SELECT语句可以查询数据。
例如，SELECT * FROM mytable;5. 更新数据：使用UPDATE语句可以更新表格中的数据。
例如，UPDATE mytable SET column1 = 2 WHERE column2 = 'data1';6. 删除数据：使用DELETE语句可以删除表格中的数据。
例如，DELETE FROM mytable WHERE column1 = 2;7. 删除表格：使用DROP TABLE语句可以删除表格。
例如，DROP TABLE mytable;1. 数据分区：可以根据特定的列将数据进行分区存储，以提高查询性能。
queryband teradata 的用法
queryband teradata 的用法
Queryband是Teradata数据库中的一个特性，用于在会话级别
设置和控制查询属性。
通过设置Queryband，可以在查询过程
中添加一些额外的信息，例如查询类型、查询来源等，以便于查询监控和性能优化。
使用Queryband可以通过以下步骤：
1. 设置Queryband标识：在查询之前，需要设置一个Queryband标识，可以是一个字符串，用于标识该查询的信息。
2. 设置Queryband值：为Queryband标识设置一个值，可以是
一个字符串，表示该查询的具体信息。
3. 添加Queryband信息到查询：在查询之前，使用SET QUERY_BAND命令将Queryband信息添加到查询中，语法如下：
```
SET QUERY_BAND = 'queryband标识=queryband值;' FOR SESSION;
```
4. 执行查询：执行相应的查询语句，包含了设置的Queryband
信息。
通过以上步骤，Queryband信息将与查询一起传递到Teradata
数据库，并可以在数据库端进行查询监控和性能优化。
需要注意的是，Queryband的使用需要具备适当的权限。
java--teradata
具体上面嘛~ 呵呵 虽然都是连接同样的数据库 但是ODBC跟官方提供的JAR 提供的操作数据库信息是不一样的
那我们采用DatabaseMetaData来看看 数据库更方面的信息
JDBC:ODBC
DataBaseName:Teradata
DataBaseVersion:05.01.0105 V2R
the System Function:CHARACTERS, BYTES, SUM, CSUM, MSUM, AVERAGE, MAVG, COUNT, MINIMUM, MAXIMUM, MLINREG, QUALIFY, QUANTILE, RANK
采用ODBC的时候还列举了蛮多的存储过程.....
the Numeric Function:ABS,EXP,LOG,MOD,PI,SQRT
the String Function:CONCAT,LEFT,LENGTH,LOCATE,LTRIM,eDate Function:CURDATE,CURTIME,DAYOFMONTH,DAYOFWEEK,DAYOFYEAR,HOUR,MINUTE,MONTH,MONTHNAME,NOW,QUARTER,SECOND,TIMESTAMPADD,TIMESTAMPDIFF,WEEK,YEAR
the String Function:TRIM, SUBSTRING, SUBSTR, MSUBSTR, INDEX,MINDEX, VARGRAPHIC, CHAR2HEXINT, UPPER
the TimeDate Function:EXTRACT(<year/month/day> FROM <date_value>),EXTRACT(<hour/minute/second> FROM <real_value>),ADD-MONTHS(<date_expr>, <integer_expr>)
Teradata基础教程(中文)
Teradata SQL基础教程第一章关系数据库基础1.1关系数据库模型关系数据库理论最早是由Codd博士提出的，一个关系的数学描述其实就是一个二维表，这些二维表按照业务运行的规律组合起来，就是关系数据库模型。
这种模型可以简洁地表达出企业或机构的业务运作规律，抓住事物本质，因此非常实用。
每个二维表被称为一个实体(Entity)，它可以是人、地点或者某种事物等。
表中的每个列被称为属性(Attribute)或者字段(Field)，表中的每一行代表了该实体的一个特定实例，称为记录(Record)。
表1-1、1-2和1-3分别给出了一个雇员表、部门表和工作表的实例。
表1-1 雇员表(Employee Table)EMPLOYEE NUMBER MANAGEREMPLOYEENUMBERDEPARTMENT NUMBERJOBCODELASTNAMEFIRSTNAMEHIREDATEBIRTHDATESALARYAMOUNTPK FK FK FK1018 1017 501 512101RatzlaffLarry1978-07-151954-05-3154000.00 1022 1003 401 412102MachadoAlbert1979-03-011957-07-1432300.00 1014 1011 402 422101CraneRobert1978-01-151960-07-0424500.00 1003 801 401 411100TraderJames1976-07-311947-06-1937850.00 1007 1005 403 432101VillegasArnando1977-01-021937-01-3149700.00 1010 1003 401 412101RogersFrank1977-03-011935-04-2346000.00 表1-2 部门表(Department Table). 1 .department_number department_name budget_amount manager_employee_number PK FK308000.001011support402 software982300.001003support401 customer1025293800.00201 technicaloperations801100 president 400000.001017308000.00501 marketingsales1005403 education 932000.00表1-3 工作表(Job Table)job_code description hourly_billing_rate hourly_cost_rate PK421100 Manager - Software Support 0.00 0.00Rep 0.00 0.00512101 Sales511100 Manager - Marketing Sales 0.00 0.00Engineer 0.00 0.00312101 Software411100 Manager - Customer Support 0.00 0.00431100 Manager - Education 0.00 0.00413201 Dispatcher 0.00 0.00432101 Instructor 0.00 0.00Analyst 0.00 0.00422101 Software321100 Manager - Product Planning 0.00 0.00在一个关系数据库模型中，表和表之间是有关联的，这种关联常用所谓的E-R 图(Entity-Relationship Diagram)来表示。
Teradata数据库的架构组成
Teradata数据库的架构组成Teradata在整体上是按Shared Nothing 架构体系进行组织的(关于Shared Nothing及其它并行数据库体系结构请参考我的另一篇文章“并行数据库的基本体系结构”)，由于Teradata通常被用于OLAP应用，因此单机的Teradata系统很少见，即使是单机系统，Teradata也建议使用SMP结构以尽可能地提供更好的数据库性能，我在后面的介绍中，都是按多机系统进行说明的。
根据Shared Nothing的组成结构特点，在物理布局上，Teradata系统主要包括三个部分:处理节点(Node)、用于节点间通信的内部高速互联(InterConnection)和数据存储介质(通常是磁盘阵列)。
每个节点都是SMP结构的单机，节点的物理和逻辑结构如图2所示，多个节点一起构成一个MPP系统，多个节点之间的内部高速互联是通过一种被称为BYNET的硬件来实现的，整个系统的组成如图1所示。
单个节点的硬件结构Teradata系统中的每个节点在物理上都是一个SMP处理单元，事实上就是一台多CPU或多核的计算机。
节点硬件包括CPU、内存、用于安装操作系统和应用软件的本地磁盘、与外界交互的网卡及BYNET端口。
节点的网卡根据具体的网络环境而不同，通常包括两种，一种是与IBM MainFrame连接的Channel Adapter，另一种就是我们熟悉的局域网网卡。
通常情况下，一个节点上只会使用一种网卡，但会有多块网卡，分别用于不同的连接和冗余。
单个节点的软件结构在软件结构上，每个节点自下向上包括操作系统软件(OS)、Teradata并行数据库扩展(PDE)和相关应用程序，其中PDE的主要职责是管理和运行虚拟处理器，其中主要包括PE和AMPs。
(1)Teradata并行数据库扩展(PDE，Parallel Database Extensions)，是直接架构在操作系统之上的一个接口层，用于为Teradata提供并行环境，并保证这个并行环境的可运行性和健壮性。
TERADATA数据库
TERADATA数据库1.表属性: Set / Multiset●Set Table 不允许记录重复●MultiSet Table 允许记录重复●默认值：Set Table> Create Table... AS ... 生成的目标表属性默●对SET Table 进行INSERT 操作，需要检查是否存在重复记录> 相当的耗资源> 若真要限定唯一性，可以通过UPI 或USI 实现●建议：Teradata中都用MultiSet2.主索引(PI) 设置●PI 影响数据的存储与访问，其选择标准：> 不同值尽量多的字段(More Unique Values)> 使用频繁的字段：包括值访问和连接访问> 少更新> PI 字段不宜太多> 最好是手动指定PI3.分区索引(PPI) 设置●PPI （Partition Primary Index ，分区索引），把具有相同分区值的数据聚簇存放在一起；类似于SQL Server 的聚簇索引（Cluster Index ），Oracle 的聚簇表（Cluster Table ）。
●利用PPI ，可以快速插入/ 访问同一个Partition （分区）的数据。
●Partition 上不要使用表达式，否则Partition 不能被正确使用。
> Substring(T1. tx_date from 1 for 6) ='200709'> cast( '200710' || '01' as date) 写法错误，PPI 不起作用4.临时表1)可变临时表●在spool缓冲区中物化。
●不使用数据字典和交易锁。
●在cache中保留表的定义。
●在一个会话中，能够被多个查询使用。
●可以随时被手动删除，会话结束时自动删除。
●使用CREATE VOLATILE TABLE语句创建。
●使用ON COMMIT PRESERVE ROWS，允许会话中的其他查询使用这个可变临时表。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1．Teradata Client安装说明
1、安装程序地址：ftp://10.3.7.141/
2、点击Setup.exe，开始运行安装程序
3、出现如下安装启动画面：
4、选择Custom安装方式（请不要选择Typical）
5、选择安装组件
请依次选择以下程序：
6、点击Next直至完成
2．Teradata数据库配置说明
1）测试数据库说明
2）配置ODBC
1、打开odbc数据源管理器、选择系统dsn页点击【添加】按钮
2、选择T eradata驱动程序，点击【Finish】按钮
3、填DB Source、T eradata Info、Uername、Password等选项点击【ok】按钮完成。
说明：
●Data Source：odbc的名称
●Teradata Server Info Name(s)：Teradata数据库的IP地址
●Username：用户名
●Password：密码
●Default Database：默认数据库
3）配置HOST文件
1、打开系统目录－system32－Drivers－etc（如XP系统：
C:\Windows\system32\drivers\etc）下的hosts文件
2、填写T eradata数据库的CLI接口地址：如
127.0.0.1 localhost
10.3.7.9 cpcimtcop1
说明：
第一部分为Teradata的ip地址，后面为任意名字和cop1、cop2的组合
3．Teradata客户端配置
1） Teradata Administrator配置
选择菜单T ools/Options
修改General选项，选中用SQL Assistant代替查询窗口选项。
完成OK后可以选择查询按钮进入T eradata SQL Assistant
2） Teradata SQL Assistant配置
选择菜单T ools/Options
修改查询选项卡，选中只提交选中的查询语句选项。