
- Teradata 教程
- Teradata - 首页
- Teradata 基础知识
- Teradata - 简介
- Teradata - 安装
- Teradata - 架构
- Teradata - 关系型概念
- Teradata - 数据类型
- Teradata - 表
- Teradata - 数据操作
- Teradata - SELECT 语句
- 逻辑与条件运算符
- Teradata - 集合运算符
- Teradata - 字符串操作
- Teradata - 日期/时间函数
- Teradata - 内置函数
- Teradata - 聚合函数
- Teradata - CASE 与 COALESCE
- Teradata - 主索引
- Teradata - 连接
- Teradata - 子查询
- Teradata 高级
- Teradata - 表类型
- Teradata - 空间概念
- Teradata - 次要索引
- Teradata - 统计信息
- Teradata - 压缩
- Teradata - EXPLAIN
- Teradata - 哈希算法
- Teradata - 连接索引
- Teradata - 视图
- Teradata - 宏
- Teradata - 存储过程
- Teradata - 连接策略
- Teradata - 分区主索引
- Teradata - OLAP 函数
- Teradata - 数据保护
- Teradata - 用户管理
- Teradata - 性能调优
- Teradata - FastLoad
- Teradata - MultiLoad
- Teradata - FastExport
- Teradata - BTEQ
- Teradata 有用资源
- Teradata - 问答
- Teradata 快速指南
- Teradata - 有用资源
- Teradata - 讨论
Teradata 快速指南
Teradata - 简介
什么是 Teradata?
Teradata 是流行的关系型数据库管理系统之一。它主要适用于构建大型数据仓库应用程序。Teradata 通过并行处理的概念实现这一点。它是由 Teradata 公司开发的。
Teradata 的历史
以下是 Teradata 历史的简要概述,列出了主要的里程碑。
1979 − Teradata 成立。
1984 − 发布首个数据库计算机 DBC/1012。
1986 − 《财富》杂志将 Teradata 评为“年度产品”。
1999 − 使用 Teradata 的全球最大数据库,拥有 130TB 数据。
2002 − Teradata V2R5 发布,包含分区主索引和压缩功能。
2006 − 推出 Teradata 主数据管理解决方案。
2008 − Teradata 13.0 发布,包含主动数据仓库功能。
2011 − 收购 Teradata Aster,进军高级分析领域。
2012 − Teradata 14.0 发布。
2014 − Teradata 15.0 发布。
Teradata 的特性
以下是 Teradata 的一些特性:
无限并行性 − Teradata 数据库系统基于大规模并行处理 (MPP) 架构。MPP 架构将工作负载均匀地分配到整个系统。Teradata 系统将任务分配到其进程中,并并行运行它们,以确保任务快速完成。
共享无架构 − Teradata 的架构称为共享无架构。Teradata 节点、其访问模块处理器 (AMP) 和与 AMP 关联的磁盘独立工作。它们不与其他共享。
线性可扩展性 − Teradata 系统具有高度可扩展性。它们可以扩展到 2048 个节点。例如,您可以通过将 AMP 数量加倍来使系统的容量加倍。
连接性 − Teradata 可以连接到通道连接系统(例如大型机)或网络连接系统。
成熟的优化器 − Teradata 优化器是市场上最成熟的优化器之一。它从一开始就被设计为并行的,并且在每个版本中都得到了改进。
SQL − Teradata 支持行业标准 SQL 来与存储在表中的数据交互。除此之外,它还提供自己的扩展。
强大的实用程序 − Teradata 提供强大的实用程序来导入/导出 Teradata 系统中的数据,例如 FastLoad、MultiLoad、FastExport 和 TPT。
自动分布 − Teradata 自动将数据均匀地分布到磁盘,无需任何手动干预。
Teradata - 安装
Teradata 为 VMware 提供 Teradata Express,这是一个功能齐全的 Teradata 虚拟机。它提供高达 1 TB 的存储空间。Teradata 提供 40GB 和 1TB 两个版本的 VMware。
先决条件
由于虚拟机是 64 位的,因此您的 CPU 必须支持 64 位。
Windows 安装步骤
步骤 1 − 从以下链接下载所需的 VM 版本: https://downloads.teradata.com/download/database/teradata-express-for-vmware-player
步骤 2 − 解压缩文件并指定目标文件夹。
步骤 3 − 从以下链接下载 VMware Workstation Player: https://my.vmware.com/web/vmware/downloads。它适用于 Windows 和 Linux。下载适用于 Windows 的 VMware Workstation Player。

步骤 4 − 下载完成后,安装软件。
步骤 5 − 安装完成后,运行 VMware 客户端。
步骤 6 − 选择“打开虚拟机”。浏览解压缩的 Teradata VMware 文件夹,然后选择扩展名为 .vmdk 的文件。

步骤 7 − Teradata VMware 已添加到 VMware 客户端。选择添加的 Teradata VMware 并单击“播放虚拟机”。

步骤 8 − 如果出现软件更新弹出窗口,您可以选择“稍后提醒我”。
步骤 9 − 输入用户名 root,按 Tab 键,然后输入密码 root,再次按 Enter 键。

步骤 10 − 桌面出现以下屏幕后,双击“root 的主目录”。然后双击“Genome 的终端”。这将打开 Shell。

步骤 11 − 在以下 Shell 中,输入命令 /etc/init.d/tpa start。这将启动 Teradata 服务器。

启动 BTEQ
BTEQ 实用程序用于交互式提交 SQL 查询。以下是启动 BTEQ 实用程序的步骤。
步骤 1 − 输入命令 /sbin/ifconfig 并记下 VMware 的 IP 地址。
步骤 2 − 运行命令 bteq。在登录提示符下,输入命令。
Logon <ipaddress>/dbc,dbc; 然后按Enter键。在密码提示符下,输入密码 dbc;

您可以使用 BTEQ 登录 Teradata 系统并运行任何 SQL 查询。
Teradata - 架构
Teradata 架构基于大规模并行处理 (MPP) 架构。Teradata 的主要组件是解析引擎、BYNET 和访问模块处理器 (AMP)。下图显示了 Teradata 节点的顶层架构。

Teradata 的组件
Teradata 的关键组件如下:
节点 − 它是 Teradata 系统中的基本单元。Teradata 系统中的每个单独服务器都称为节点。节点包含其自己的操作系统、CPU、内存、Teradata RDBMS 软件的副本以及磁盘空间。机柜包含一个或多个节点。
解析引擎 − 解析引擎负责接收来自客户端的查询并准备有效的执行计划。解析引擎的职责是:
接收来自客户端的 SQL 查询
解析 SQL 查询并检查语法错误
检查用户是否对 SQL 查询中使用的对象具有所需的权限
检查 SQL 中使用的对象是否实际存在
准备执行 SQL 查询的执行计划并将其传递给 BYNET
接收来自 AMP 的结果并发送给客户端
消息传递层 − 消息传递层称为 BYNET,是 Teradata 系统中的网络层。它允许 PE 和 AMP 之间以及节点之间的通信。它接收来自解析引擎的执行计划并发送给 AMP。类似地,它接收来自 AMP 的结果并发送给解析引擎。
访问模块处理器 (AMP) − AMP(称为虚拟处理器 (vproc))是实际存储和检索数据的组件。AMP 接收来自解析引擎的数据和执行计划,执行任何数据类型转换、聚合、过滤、排序并将数据存储在其关联的磁盘中。表中的记录均匀地分布在系统中的 AMP 之间。每个 AMP 都与一组存储数据的磁盘相关联。只有该 AMP 才能读取/写入磁盘上的数据。
存储架构
当客户端运行查询以插入记录时,解析引擎将记录发送到 BYNET。BYNET 检索记录并将行发送到目标 AMP。AMP 将这些记录存储在其磁盘上。下图显示了 Teradata 的存储架构。

检索架构
当客户端运行查询以检索记录时,解析引擎会向 BYNET 发送请求。BYNET 将检索请求发送到相应的 AMP。然后 AMP 并行搜索其磁盘并识别所需的记录,并将其发送到 BYNET。然后 BYNET 将记录发送到解析引擎,解析引擎又将记录发送到客户端。以下是 Teradata 的检索架构。

Teradata - 关系型概念
关系型数据库管理系统 (RDBMS) 是一种 DBMS 软件,有助于与数据库交互。它们使用结构化查询语言 (SQL) 与存储在表中的数据交互。
数据库
数据库是逻辑相关的数据的集合。许多用户出于不同的目的访问它们。例如,销售数据库包含存储在许多表中的关于销售的全部信息。
表
表是 RDBMS 中存储数据的基本单元。表是行和列的集合。以下是一个员工表的示例。
员工编号 | 名字 | 姓氏 | 出生日期 |
---|---|---|---|
101 | Mike | James | 1/5/1980 |
104 | Alex | Stuart | 11/6/1984 |
102 | Robert | Williams | 3/5/1983 |
105 | Robert | James | 12/1/1984 |
103 | Peter | Paul | 4/1/1983 |
列
列包含类似的数据。例如,员工表中的 BirthDate 列包含所有员工的出生日期信息。
出生日期 |
---|
1/5/1980 |
11/6/1984 |
3/5/1983 |
12/1/1984 |
4/1/1983 |
行
行是所有列的一个实例。例如,在员工表中,一行包含单个员工的信息。
员工编号 | 名字 | 姓氏 | 出生日期 |
---|---|---|---|
101 | Mike | James | 1/5/1980 |
主键
主键用于唯一标识表中的一行。主键列不允许重复值,也不能接受 NULL 值。它是表中的必填字段。
外键
外键用于在表之间建立关系。子表中的外键定义为父表中的主键。一个表可以有多个外键。它可以接受重复值和 NULL 值。外键在表中是可选的。
Teradata - 数据类型
表中的每一列都与一个数据类型相关联。数据类型指定将在列中存储哪种值。Teradata 支持多种数据类型。以下是某些常用数据类型。
数据类型 | 长度(字节) | 值的范围 |
---|---|---|
BYTEINT | 1 | -128 到 +127 |
SMALLINT | 2 | -32768 到 +32767 |
INTEGER | 4 | -2,147,483,648 到 +2,147,483,647 |
BIGINT | 8 | -9,233,372,036,854,775,808 到 +9,233,372,036,854,775,807 |
DECIMAL | 1-16 | |
NUMERIC | 1-16 | |
FLOAT | 8 | IEEE 格式 |
CHAR | 固定格式 | 1-64,000 |
VARCHAR | 可变 | 1-64,000 |
DATE | 4 | YYYYMMDD |
TIME | 6 或 8 | HHMMSS.nnnnnn **或** HHMMSS.nnnnnn+HHMM |
TIMESTAMP | 10 或 12 | YYMMDDHHMMSS.nnnnnn **或** YYMMDDHHMMSS.nnnnnn+HHMM |
Teradata - 表
关系模型中的表定义为数据的集合。它们表示为行和列。
表类型
类型 Teradata 支持不同类型的表。
**永久表** - 这是默认表,它包含用户插入的数据并永久存储数据。
**临时表** - 插入临时表中的数据仅在用户会话期间保留。会话结束时,表和数据将被删除。这些表主要用于在数据转换期间保存中间数据。
**全局临时表** - 全局临时表的定义是持久的,但表中的数据在用户会话结束时会被删除。
**派生表** - 派生表保存查询中的中间结果。它们的生存期在创建、使用和删除它们的查询内。
集合与多集
Teradata 根据处理重复记录的方式将表分类为集合表或多集表。定义为集合表的表不存储重复记录,而多集表可以存储重复记录。
序号 | 表命令和描述 |
---|---|
1 | 创建表
CREATE TABLE 命令用于在 Teradata 中创建表。 |
2 | 修改表
ALTER TABLE 命令用于向现有表添加或删除列。 |
3 | 删除表
DROP TABLE 命令用于删除表。 |
Teradata - 数据操作
本章介绍用于操作 Teradata 表中存储的数据的 SQL 命令。
插入记录
INSERT INTO 语句用于将记录插入表中。
语法
以下是 INSERT INTO 的通用语法。
INSERT INTO <tablename> (column1, column2, column3,…) VALUES (value1, value2, value3 …);
示例
以下示例将记录插入员工表。
INSERT INTO Employee ( EmployeeNo, FirstName, LastName, BirthDate, JoinedDate, DepartmentNo ) VALUES ( 101, 'Mike', 'James', '1980-01-05', '2005-03-27', 01 );
插入上述查询后,可以使用 SELECT 语句查看表中的记录。
员工编号 | 名字 | 姓氏 | 加入日期 | 部门编号 | 出生日期 |
---|---|---|---|---|---|
101 | Mike | James | 3/27/2005 | 1 | 1/5/1980 |
从另一个表插入
INSERT SELECT 语句用于从另一个表插入记录。
语法
以下是 INSERT INTO 的通用语法。
INSERT INTO <tablename> (column1, column2, column3,…) SELECT column1, column2, column3… FROM <source table>;
示例
以下示例将记录插入员工表。在运行以下插入查询之前,创建一个名为 Employee_Bkup 的表,其列定义与员工表相同。
INSERT INTO Employee_Bkup ( EmployeeNo, FirstName, LastName, BirthDate, JoinedDate, DepartmentNo ) SELECT EmployeeNo, FirstName, LastName, BirthDate, JoinedDate, DepartmentNo FROM Employee;
执行上述查询后,它将员工表中的所有记录插入 employee_bkup 表。
规则
VALUES 列表中指定的列数应与 INSERT INTO 子句中指定的列数匹配。
NOT NULL 列的值是必填的。
如果未指定值,则为可为空字段插入 NULL。
VALUES 子句中指定的列的数据类型应与 INSERT 子句中列的数据类型兼容。
更新记录
UPDATE 语句用于更新表中的记录。
语法
以下是 UPDATE 的通用语法。
UPDATE <tablename> SET <columnnamme> = <new value> [WHERE condition];
示例
以下示例将员工 101 的员工部门更新为 03。
UPDATE Employee SET DepartmentNo = 03 WHERE EmployeeNo = 101;
在以下输出中,您可以看到员工编号 101 的 DepartmentNo 已从 1 更新为 3。
SELECT Employeeno, DepartmentNo FROM Employee; *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo DepartmentNo ----------- ------------- 101 3
规则
您可以更新表的一个或多个值。
如果未指定 WHERE 条件,则表的所有行都会受到影响。
您可以使用另一个表中的值更新表。
删除记录
DELETE FROM 语句用于更新表中的记录。
语法
以下是 DELETE FROM 的通用语法。
DELETE FROM <tablename> [WHERE condition];
示例
以下示例从员工表中删除员工 101。
DELETE FROM Employee WHERE EmployeeNo = 101;
在以下输出中,您可以看到员工 101 已从表中删除。
SELECT EmployeeNo FROM Employee; *** Query completed. No rows found. *** Total elapsed time was 1 second.
规则
您可以更新表的一个或多个记录。
如果未指定 WHERE 条件,则将删除表的所有行。
您可以使用另一个表中的值更新表。
Teradata - SELECT 语句
SELECT 语句用于从表中检索记录。
语法
以下是 SELECT 语句的基本语法。
SELECT column 1, column 2, ..... FROM tablename;
示例
考虑以下员工表。
员工编号 | 名字 | 姓氏 | 加入日期 | 部门编号 | 出生日期 |
---|---|---|---|---|---|
101 | Mike | James | 3/27/2005 | 1 | 1/5/1980 |
102 | Robert | Williams | 4/25/2007 | 2 | 3/5/1983 |
103 | Peter | Paul | 3/21/2007 | 2 | 4/1/1983 |
104 | Alex | Stuart | 2/1/2008 | 2 | 11/6/1984 |
105 | Robert | James | 1/4/2008 | 3 | 12/1/1984 |
以下是 SELECT 语句的示例。
SELECT EmployeeNo,FirstName,LastName FROM Employee;
执行此查询时,它将从员工表中提取 EmployeeNo、FirstName 和 LastName 列。
EmployeeNo FirstName LastName ----------- ------------------------------ --------------------------- 101 Mike James 104 Alex Stuart 102 Robert Williams 105 Robert James 103 Peter Paul
如果要从表中提取所有列,可以使用以下命令,而无需列出所有列。
SELECT * FROM Employee;
上述查询将从员工表中提取所有记录。
WHERE 子句
WHERE 子句用于筛选 SELECT 语句返回的记录。WHERE 子句与一个条件相关联。只有满足 WHERE 子句中条件的记录才会被返回。
语法
以下是带有 WHERE 子句的 SELECT 语句的语法。
SELECT * FROM tablename WHERE[condition];
示例
以下查询提取 EmployeeNo 为 101 的记录。
SELECT * FROM Employee WHERE EmployeeNo = 101;
执行此查询时,它将返回以下记录。
EmployeeNo FirstName LastName ----------- ------------------------------ ----------------------------- 101 Mike James
ORDER BY
执行 SELECT 语句时,返回的行没有任何特定顺序。ORDER BY 子句用于按任何列的升序/降序排列记录。
语法
以下是带有 ORDER BY 子句的 SELECT 语句的语法。
SELECT * FROM tablename ORDER BY column 1, column 2..;
示例
以下查询从员工表中提取记录,并按 FirstName 排序结果。
SELECT * FROM Employee ORDER BY FirstName;
执行上述查询时,将产生以下输出。
EmployeeNo FirstName LastName ----------- ------------------------------ ----------------------------- 104 Alex Stuart 101 Mike James 103 Peter Paul 102 Robert Williams 105 Robert James
GROUP BY
GROUP BY 子句与 SELECT 语句一起使用,并将相似的记录排列成组。
语法
以下是带有 GROUP BY 子句的 SELECT 语句的语法。
SELECT column 1, column2 …. FROM tablename GROUP BY column 1, column 2..;
示例
以下示例按 DepartmentNo 列对记录进行分组,并标识每个部门的总数。
SELECT DepartmentNo,Count(*) FROM Employee GROUP BY DepartmentNo;
执行上述查询时,将产生以下输出。
DepartmentNo Count(*) ------------ ----------- 3 1 1 1 2 3
Teradata - 逻辑和条件运算符
Teradata 支持以下逻辑和条件运算符。这些运算符用于执行比较和组合多个条件。
语法 | 含义 |
---|---|
> | 大于 |
< | 小于 |
>= | 大于或等于 |
<= | 小于或等于 |
= | 等于 |
BETWEEN | 如果值在范围内 |
IN | 如果值在 <表达式> 中 |
NOT IN | 如果值不在 <表达式> 中 |
IS NULL | 如果值为 NULL |
IS NOT NULL | 如果值不为 NULL |
AND | 组合多个条件。只有在所有条件都满足时才评估为 true |
OR | 组合多个条件。只有在其中任何一个条件满足时才评估为 true。 |
NOT | 反转条件的含义 |
BETWEEN
BETWEEN 命令用于检查值是否在值的范围内。
示例
考虑以下员工表。
员工编号 | 名字 | 姓氏 | 加入日期 | 部门编号 | 出生日期 |
---|---|---|---|---|---|
101 | Mike | James | 3/27/2005 | 1 | 1/5/1980 |
102 | Robert | Williams | 4/25/2007 | 2 | 3/5/1983 |
103 | Peter | Paul | 3/21/2007 | 2 | 4/1/1983 |
104 | Alex | Stuart | 2/1/2008 | 2 | 11/6/1984 |
105 | Robert | James | 1/4/2008 | 3 | 12/1/1984 |
以下示例提取员工编号在 101、102 和 103 范围内的记录。
SELECT EmployeeNo, FirstName FROM Employee WHERE EmployeeNo BETWEEN 101 AND 103;
执行上述查询时,它将返回员工编号在 101 和 103 之间的员工记录。
*** Query completed. 3 rows found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName ----------- ------------------------------ 101 Mike 102 Robert 103 Peter
IN
IN 命令用于根据给定的值列表检查值。
示例
以下示例提取员工编号为 101、102 和 103 的记录。
SELECT EmployeeNo, FirstName FROM Employee WHERE EmployeeNo in (101,102,103);
上述查询返回以下记录。
*** Query completed. 3 rows found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName ----------- ------------------------------ 101 Mike 102 Robert 103 Peter
NOT IN
NOT IN 命令反转 IN 命令的结果。它提取与给定列表不匹配的值的记录。
示例
以下示例提取员工编号不为 101、102 和 103 的记录。
SELECT * FROM Employee WHERE EmployeeNo not in (101,102,103);
上述查询返回以下记录。
*** Query completed. 2 rows found. 6 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName LastName ----------- ------------------------------ ----------------------------- 104 Alex Stuart 105 Robert James
Teradata - 集合运算符
SET 运算符组合来自多个 SELECT 语句的结果。这可能类似于连接,但连接组合来自多个表的列,而 SET 运算符组合来自多个行的行。
规则
每个 SELECT 语句的列数应相同。
每个 SELECT 的数据类型必须兼容。
ORDER BY 应仅包含在最终的 SELECT 语句中。
UNION
UNION 语句用于组合来自多个 SELECT 语句的结果。它忽略重复项。
语法
以下是 UNION 语句的基本语法。
SELECT col1, col2, col3… FROM <table 1> [WHERE condition] UNION SELECT col1, col2, col3… FROM <table 2> [WHERE condition];
示例
考虑以下员工表和薪资表。
员工编号 | 名字 | 姓氏 | 加入日期 | 部门编号 | 出生日期 |
---|---|---|---|---|---|
101 | Mike | James | 3/27/2005 | 1 | 1/5/1980 |
102 | Robert | Williams | 4/25/2007 | 2 | 3/5/1983 |
103 | Peter | Paul | 3/21/2007 | 2 | 4/1/1983 |
104 | Alex | Stuart | 2/1/2008 | 2 | 11/6/1984 |
105 | Robert | James | 1/4/2008 | 3 | 12/1/1984 |
员工编号 | 总额 | 扣除 | 净薪 |
---|---|---|---|
101 | 40,000 | 4,000 | 36,000 |
102 | 80,000 | 6,000 | 74,000 |
103 | 90,000 | 7,000 | 83,000 |
104 | 75,000 | 5,000 | 70,000 |
以下 UNION 查询组合来自 Employee 和 Salary 表的 EmployeeNo 值。
SELECT EmployeeNo FROM Employee UNION SELECT EmployeeNo FROM Salary;
执行查询后,它将产生以下输出。
EmployeeNo ----------- 101 102 103 104 105
UNION ALL
UNION ALL 语句类似于 UNION,它组合来自多个表的结果,包括重复行。
语法
以下是 UNION ALL 语句的基本语法。
SELECT col1, col2, col3… FROM <table 1> [WHERE condition] UNION ALL SELECT col1, col2, col3… FROM <table 2> [WHERE condition];
示例
以下是 UNION ALL 语句的示例。
SELECT EmployeeNo FROM Employee UNION ALL SELECT EmployeeNo FROM Salary;
执行上述查询后,它将产生以下输出。您可以看到它也返回重复项。
EmployeeNo ----------- 101 104 102 105 103 101 104 102 103
INTERSECT
INTERSECT 命令也用于组合来自多个 SELECT 语句的结果。它返回第一个 SELECT 语句中与第二个 SELECT 语句中具有相应匹配的行。换句话说,它返回同时存在于两个 SELECT 语句中的行。
语法
以下是 INTERSECT 语句的基本语法。
SELECT col1, col2, col3… FROM <table 1> [WHERE condition] INTERSECT SELECT col1, col2, col3… FROM <table 2> [WHERE condition];
示例
以下是 INTERSECT 语句的示例。它返回同时存在于两个表中的 EmployeeNo 值。
SELECT EmployeeNo FROM Employee INTERSECT SELECT EmployeeNo FROM Salary;
执行上述查询后,它将返回以下记录。EmployeeNo 105 被排除在外,因为它不存在于 SALARY 表中。
EmployeeNo ----------- 101 104 102 103
MINUS/EXCEPT
MINUS/EXCEPT 命令组合来自多个表的行,并返回第一个 SELECT 中但不在第二个 SELECT 中的行。两者都返回相同的结果。
语法
以下是 MINUS 语句的基本语法。
SELECT col1, col2, col3… FROM <table 1> [WHERE condition] MINUS SELECT col1, col2, col3… FROM <table 2> [WHERE condition];
示例
以下是 MINUS 语句的示例。
SELECT EmployeeNo FROM Employee MINUS SELECT EmployeeNo FROM Salary;
执行此查询时,它将返回以下记录。
EmployeeNo ----------- 105
Teradata - 字符串操作
Teradata 提供多个函数来操作字符串。这些函数与 ANSI 标准兼容。
序号 | 字符串函数和描述 |
---|---|
1 | ||
将字符串连接在一起 |
2 | SUBSTR
提取字符串的一部分(Teradata 扩展) |
3 | SUBSTRING
提取字符串的一部分(ANSI 标准) |
4 | INDEX
查找字符在字符串中的位置(Teradata 扩展) |
5 | POSITION
查找字符在字符串中的位置(ANSI 标准) |
6 | TRIM
修剪字符串中的空格 |
7 | UPPER
将字符串转换为大写 |
8 | LOWER
将字符串转换为小写 |
示例
下表列出了一些字符串函数及其结果。
字符串函数 | 结果 |
---|---|
SELECT SUBSTRING(‘warehouse’ FROM 1 FOR 4) | ware |
SELECT SUBSTR(‘warehouse’,1,4) | ware |
SELECT ‘data’ || ‘ ‘ || ‘warehouse’ | data warehouse |
SELECT UPPER(‘data’) | DATA |
SELECT LOWER(‘DATA’) | data |
Teradata - 日期/时间函数
本章讨论Teradata中可用的日期/时间函数。
日期存储
日期在内部以整数形式存储,使用以下公式。
((YEAR - 1900) * 10000) + (MONTH * 100) + DAY
您可以使用以下查询来检查日期的存储方式。
SELECT CAST(CURRENT_DATE AS INTEGER);
由于日期存储为整数,因此您可以对其执行一些算术运算。Teradata提供函数来执行这些运算。
EXTRACT
EXTRACT函数从DATE值中提取日、月和年的部分。此函数还用于从TIME/TIMESTAMP值中提取小时、分钟和秒。
示例
以下示例显示如何从日期和时间戳值中提取年、月、日、时、分和秒值。
SELECT EXTRACT(YEAR FROM CURRENT_DATE); EXTRACT(YEAR FROM Date) ----------------------- 2016 SELECT EXTRACT(MONTH FROM CURRENT_DATE); EXTRACT(MONTH FROM Date) ------------------------ 1 SELECT EXTRACT(DAY FROM CURRENT_DATE); EXTRACT(DAY FROM Date) ------------------------ 1 SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP); EXTRACT(HOUR FROM Current TimeStamp(6)) --------------------------------------- 4 SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP); EXTRACT(MINUTE FROM Current TimeStamp(6)) ----------------------------------------- 54 SELECT EXTRACT(SECOND FROM CURRENT_TIMESTAMP); EXTRACT(SECOND FROM Current TimeStamp(6)) ----------------------------------------- 27.140000
INTERVAL
Teradata提供INTERVAL函数来对DATE和TIME值执行算术运算。INTERVAL函数有两种类型。
年-月间隔
- YEAR
- YEAR TO MONTH
- MONTH
日-时间隔
- DAY
- DAY TO HOUR
- DAY TO MINUTE
- DAY TO SECOND
- HOUR
- HOUR TO MINUTE
- HOUR TO SECOND
- MINUTE
- MINUTE TO SECOND
- SECOND
示例
以下示例将当前日期加上3年。
SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03' YEAR; Date (Date+ 3) -------- --------- 16/01/01 19/01/01
以下示例将当前日期加上3年零1个月。
SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03-01' YEAR TO MONTH; Date (Date+ 3-01) -------- ------------ 16/01/01 19/02/01
以下示例将当前时间戳加上1天5小时10分钟。
SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP + INTERVAL '01 05:10' DAY TO MINUTE; Current TimeStamp(6) (Current TimeStamp(6)+ 1 05:10) -------------------------------- -------------------------------- 2016-01-01 04:57:26.360000+00:00 2016-01-02 10:07:26.360000+00:00
Teradata - 内置函数
Teradata提供内置函数,这些函数是SQL的扩展。以下是常见的内置函数。
函数 | 结果 |
---|---|
SELECT DATE; | 日期 -------- 16/01/01 |
SELECT CURRENT_DATE; | 日期 -------- 16/01/01 |
SELECT TIME; | 时间 -------- 04:50:29 |
SELECT CURRENT_TIME; | 时间 -------- 04:50:29 |
SELECT CURRENT_TIMESTAMP; | 当前时间戳(6) -------------------------------- 2016-01-01 04:51:06.990000+00:00 |
SELECT DATABASE; | 数据库 ------------------------------ TDUSER |
Teradata - 聚合函数
Teradata支持常见的聚合函数。它们可以与SELECT语句一起使用。
COUNT − 计数行数
SUM − 将指定列的值加起来
MAX − 返回指定列的最大值
MIN − 返回指定列的最小值
AVG − 返回指定列的平均值
示例
考虑以下薪资表。
员工编号 | 总额 | 扣除 | 净薪 |
---|---|---|---|
101 | 40,000 | 4,000 | 36,000 |
104 | 75,000 | 5,000 | 70,000 |
102 | 80,000 | 6,000 | 74,000 |
105 | 70,000 | 4,000 | 66,000 |
103 | 90,000 | 7,000 | 83,000 |
COUNT
以下示例计算薪资表中记录的数量。
SELECT count(*) from Salary; Count(*) ----------- 5
MAX
以下示例返回员工净薪的最大值。
SELECT max(NetPay) from Salary; Maximum(NetPay) --------------------- 83000
MIN
以下示例返回薪资表中员工净薪的最小值。
SELECT min(NetPay) from Salary; Minimum(NetPay) --------------------- 36000
AVG
以下示例返回表中员工净薪的平均值。
SELECT avg(NetPay) from Salary; Average(NetPay) --------------------- 65800
SUM
以下示例计算薪资表中所有记录的员工净薪总和。
SELECT sum(NetPay) from Salary; Sum(NetPay) ----------------- 329000
Teradata - CASE 和 COALESCE
本章解释Teradata的CASE和COALESCE函数。
CASE表达式
CASE表达式根据条件或WHEN子句评估每一行,并返回第一个匹配项的结果。如果没有匹配项,则返回ELSE部分的结果。
语法
以下是CASE表达式的语法。
CASE <expression> WHEN <expression> THEN result-1 WHEN <expression> THEN result-2 ELSE Result-n END
示例
考虑以下员工表。
员工编号 | 名字 | 姓氏 | 加入日期 | 部门编号 | 出生日期 |
---|---|---|---|---|---|
101 | Mike | James | 3/27/2005 | 1 | 1/5/1980 |
102 | Robert | Williams | 4/25/2007 | 2 | 3/5/1983 |
103 | Peter | Paul | 3/21/2007 | 2 | 4/1/1983 |
104 | Alex | Stuart | 2/1/2008 | 2 | 11/6/1984 |
105 | Robert | James | 1/4/2008 | 3 | 12/1/1984 |
以下示例评估DepartmentNo列,如果部门编号为1,则返回1;如果部门编号为3,则返回2;否则返回无效部门。
SELECT EmployeeNo, CASE DepartmentNo WHEN 1 THEN 'Admin' WHEN 2 THEN 'IT' ELSE 'Invalid Dept' END AS Department FROM Employee;
执行上述查询时,将产生以下输出。
*** Query completed. 5 rows found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo Department ----------- ------------ 101 Admin 104 IT 102 IT 105 Invalid Dept 103 IT
上述CASE表达式也可以写成以下形式,这将产生与上述相同的结果。
SELECT EmployeeNo, CASE WHEN DepartmentNo = 1 THEN 'Admin' WHEN DepartmentNo = 2 THEN 'IT' ELSE 'Invalid Dept' END AS Department FROM Employee;
COALESCE
COALESCE是一个语句,它返回表达式的第一个非空值。如果表达式的所有参数都计算为NULL,则返回NULL。以下是语法。
语法
COALESCE(expression 1, expression 2, ....)
示例
SELECT EmployeeNo, COALESCE(dept_no, 'Department not found') FROM employee;
NULLIF
如果参数相等,NULLIF语句返回NULL。
语法
以下是NULLIF语句的语法。
NULLIF(expression 1, expression 2)
示例
如果DepartmentNo等于3,则以下示例返回NULL。否则,它返回DepartmentNo值。
SELECT EmployeeNo, NULLIF(DepartmentNo,3) AS department FROM Employee;
上述查询返回以下记录。您可以看到员工105的部门编号为NULL。
*** Query completed. 5 rows found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo department ----------- ------------------ 101 1 104 2 102 2 105 ? 103 2
Teradata - 主索引
主键用于指定数据在Teradata中的位置。它用于指定哪个AMP获取数据行。Teradata中的每个表都需要定义一个主键。如果没有定义主键,Teradata会自动分配主键。主键提供了访问数据的最快方式。主键最多可以有64列。
创建表时定义主键。主键有两种类型。
- 唯一主键(UPI)
- 非唯一主键(NUPI)
唯一主键 (UPI)
如果表被定义为具有UPI,则被视为UPI的列不应有任何重复值。如果插入任何重复值,它们将被拒绝。
创建唯一主键
以下示例创建Salary表,其中EmployeeNo列作为唯一主键。
CREATE SET TABLE Salary ( EmployeeNo INTEGER, Gross INTEGER, Deduction INTEGER, NetPay INTEGER ) UNIQUE PRIMARY INDEX(EmployeeNo);
非唯一主键 (NUPI)
如果表被定义为具有NUPI,则被视为UPI的列可以接受重复值。
创建非唯一主键
以下示例创建employee accounts表,其中EmployeeNo列作为非唯一主键。EmployeeNo被定义为非唯一主键,因为员工可以在表中拥有多个帐户;一个用于工资帐户,另一个用于报销帐户。
CREATE SET TABLE Employee _Accounts ( EmployeeNo INTEGER, employee_bank_account_type BYTEINT. employee_bank_account_number INTEGER, employee_bank_name VARCHAR(30), employee_bank_city VARCHAR(30) ) PRIMARY INDEX(EmployeeNo);
Teradata - 连接
连接用于组合来自多个表的记录。表根据这些表的公共列/值进行连接。
有不同类型的连接可用。
- 内连接
- 左外连接
- 右外连接
- 全外连接
- 自连接
- 交叉连接
- 笛卡尔积连接
INNER JOIN
内连接组合来自多个表的记录,并返回同时存在于两个表中的值。
语法
以下是INNER JOIN语句的语法。
SELECT col1, col2, col3…. FROM Table-1 INNER JOIN Table-2 ON (col1 = col2) <WHERE condition>;
示例
考虑以下员工表和薪资表。
员工编号 | 名字 | 姓氏 | 加入日期 | 部门编号 | 出生日期 |
---|---|---|---|---|---|
101 | Mike | James | 3/27/2005 | 1 | 1/5/1980 |
102 | Robert | Williams | 4/25/2007 | 2 | 3/5/1983 |
103 | Peter | Paul | 3/21/2007 | 2 | 4/1/1983 |
104 | Alex | Stuart | 2/1/2008 | 2 | 11/6/1984 |
105 | Robert | James | 1/4/2008 | 3 | 12/1/1984 |
员工编号 | 总额 | 扣除 | 净薪 |
---|---|---|---|
101 | 40,000 | 4,000 | 36,000 |
102 | 80,000 | 6,000 | 74,000 |
103 | 90,000 | 7,000 | 83,000 |
104 | 75,000 | 5,000 | 70,000 |
以下查询根据公共列EmployeeNo连接Employee表和Salary表。每个表都被分配了一个别名A & B,并且列用正确的别名引用。
SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay FROM Employee A INNER JOIN Salary B ON (A.EmployeeNo = B. EmployeeNo);
执行上述查询时,它将返回以下记录。员工105未包含在结果中,因为它在Salary表中没有匹配的记录。
*** Query completed. 4 rows found. 3 columns returned. *** Total elapsed time was 1 second. EmployeeNo DepartmentNo NetPay ----------- ------------ ----------- 101 1 36000 102 2 74000 103 2 83000 104 2 70000
OUTER JOIN
LEFT OUTER JOIN和RIGHT OUTER JOIN也组合来自多个表的结果。
LEFT OUTER JOIN返回左表的所有记录,只返回右表中的匹配记录。
RIGHT OUTER JOIN返回右表的所有记录,只返回左表中的匹配行。
FULL OUTER JOIN组合LEFT OUTER JOIN和RIGHT OUTER JOIN的结果。它返回连接表中的匹配行和不匹配行。
语法
以下是OUTER JOIN语句的语法。您需要使用LEFT OUTER JOIN、RIGHT OUTER JOIN或FULL OUTER JOIN中的一个选项。
SELECT col1, col2, col3…. FROM Table-1 LEFT OUTER JOIN/RIGHT OUTER JOIN/FULL OUTER JOIN Table-2 ON (col1 = col2) <WHERE condition>;
示例
考虑以下LEFT OUTER JOIN查询示例。它返回Employee表的所有记录和Salary表中的匹配记录。
SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay FROM Employee A LEFT OUTER JOIN Salary B ON (A.EmployeeNo = B. EmployeeNo) ORDER BY A.EmployeeNo;
执行上述查询时,它将产生以下输出。对于员工105,NetPay值为NULL,因为它在Salary表中没有匹配的记录。
*** Query completed. 5 rows found. 3 columns returned. *** Total elapsed time was 1 second. EmployeeNo DepartmentNo NetPay ----------- ------------ ----------- 101 1 36000 102 2 74000 103 2 83000 104 2 70000 105 3 ?
CROSS JOIN
交叉连接将左表中的每一行与右表中的每一行连接。
语法
以下是CROSS JOIN语句的语法。
SELECT A.EmployeeNo, A.DepartmentNo, B.EmployeeNo,B.NetPay FROM Employee A CROSS JOIN Salary B WHERE A.EmployeeNo = 101 ORDER BY B.EmployeeNo;
执行上述查询时,它将产生以下输出。Employee表中的员工编号101与Salary表中的每个记录连接。
*** Query completed. 4 rows found. 4 columns returned. *** Total elapsed time was 1 second. EmployeeNo DepartmentNo EmployeeNo NetPay ----------- ------------ ----------- ----------- 101 1 101 36000 101 1 104 70000 101 1 102 74000 101 1 103 83000
Teradata - 子查询
子查询根据另一个表中的值返回一个表中的记录。它是在另一个查询中的SELECT查询。名为内部查询的SELECT查询首先执行,其结果由外部查询使用。它的一些显著特征是:
一个查询可以有多个子查询,子查询可能包含另一个子查询。
子查询不返回重复记录。
如果子查询只返回一个值,您可以使用=运算符将其与外部查询一起使用。如果它返回多个值,您可以使用IN或NOT IN。
语法
以下是子查询的通用语法。
SELECT col1, col2, col3,… FROM Outer Table WHERE col1 OPERATOR ( Inner SELECT Query);
示例
考虑以下薪资表。
员工编号 | 总额 | 扣除 | 净薪 |
---|---|---|---|
101 | 40,000 | 4,000 | 36,000 |
102 | 80,000 | 6,000 | 74,000 |
103 | 90,000 | 7,000 | 83,000 |
104 | 75,000 | 5,000 | 70,000 |
以下查询确定薪水最高的员工编号。内部SELECT执行聚合函数以返回最大NetPay值,外部SELECT查询使用此值返回具有此值的员工记录。
SELECT EmployeeNo, NetPay FROM Salary WHERE NetPay = (SELECT MAX(NetPay) FROM Salary);
执行此查询时,它将产生以下输出。
*** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo NetPay ----------- ----------- 103 83000
Teradata - 表类型
Teradata支持以下表类型来保存临时数据。
- 派生表
- 易失表
- 全局临时表
派生表
派生表在查询中创建、使用和删除。这些用于存储查询中的中间结果。
示例
以下示例构建一个派生表EmpSal,其中包含薪水大于75000的员工记录。
SELECT Emp.EmployeeNo, Emp.FirstName, Empsal.NetPay FROM Employee Emp, (select EmployeeNo , NetPay from Salary where NetPay >= 75000) Empsal where Emp.EmployeeNo = Empsal.EmployeeNo;
执行上述查询时,它将返回薪水大于75000的员工。
*** Query completed. One row found. 3 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName NetPay ----------- ------------------------------ ----------- 103 Peter 83000
易失表
易失表在用户会话中创建、使用和删除。它们的定义未存储在数据字典中。它们保存经常使用的查询的中间数据。以下是语法。
语法
CREATE [SET|MULTISET] VOALTILE TABLE tablename <table definitions> <column definitions> <index definitions> ON COMMIT [DELETE|PRESERVE] ROWS
示例
CREATE VOLATILE TABLE dept_stat ( dept_no INTEGER, avg_salary INTEGER, max_salary INTEGER, min_salary INTEGER ) PRIMARY INDEX(dept_no) ON COMMIT PRESERVE ROWS;
执行上述查询时,将产生以下输出。
*** Table has been created. *** Total elapsed time was 1 second.
全局临时表
全局临时表的定义存储在数据字典中,许多用户/会话可以使用它们。但是,仅在会话期间保留加载到全局临时表中的数据。每个会话最多可以实现2000个全局临时表。以下是语法。
语法
CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename <table definitions> <column definitions> <index definitions>
示例
CREATE SET GLOBAL TEMPORARY TABLE dept_stat ( dept_no INTEGER, avg_salary INTEGER, max_salary INTEGER, min_salary INTEGER ) PRIMARY INDEX(dept_no);
执行上述查询时,将产生以下输出。
*** Table has been created. *** Total elapsed time was 1 second.
Teradata - 空间概念
Teradata中有三种类型的空间可用。
永久空间
永久空间是用户/数据库可用于保存数据行的最大空间量。永久表、日志、回退表和辅助索引子表使用永久空间。
数据库/用户不会预分配永久空间。它们只是定义为数据库/用户可以使用的最大空间量。永久空间量除以AMP的数量。每当每个AMP限制超过时,都会生成错误消息。
缓冲池空间
缓冲池空间是未使用的永久空间,系统使用它来保存SQL查询的中间结果。没有缓冲池空间的用户无法执行任何查询。
与永久空间类似,缓冲池空间定义了用户可以使用的最大空间量。缓冲池空间除以AMP的数量。每当每个AMP限制超过时,用户将收到缓冲池空间错误。
临时空间
临时空间是全局临时表使用的未使用的永久空间。临时空间也除以AMP的数量。
Teradata - 次要索引
表只能包含一个主键。更常见的是,您会遇到表包含其他列的情况,使用这些列可以频繁地访问数据。Teradata将对这些查询执行全表扫描。辅助索引解决了这个问题。
辅助索引是访问数据的替代路径。主键和辅助索引之间存在一些差异。
辅助索引不参与数据分发。
辅助索引值存储在子表中。这些表在所有AMP中构建。
辅助索引是可选的。
它们可以在创建表期间或表创建后创建。
它们占用额外的空间,因为它们构建子表,并且它们也需要维护,因为需要为每一行更新子表。
辅助索引有两种类型:
- 唯一辅助索引 (USI)
- 非唯一辅助索引 (NUSI)
唯一辅助索引 (USI)
唯一辅助索引只允许为定义为USI的列提供唯一值。通过USI访问行是两AMP操作。
创建唯一辅助索引
以下示例在employee表的EmployeeNo列上创建USI。
CREATE UNIQUE INDEX(EmployeeNo) on employee;
非唯一辅助索引 (NUSI)
非唯一二级索引允许为定义为 NUSI 的列包含重复值。通过 NUSI 访问行是全 AMP 操作。
创建非唯一二级索引
以下示例在员工表的 FirstName 列上创建 NUSI。
CREATE INDEX(FirstName) on Employee;
Teradata - 统计信息
Teradata 优化器会为每个 SQL 查询制定执行策略。此执行策略基于在 SQL 查询中使用的表上收集的统计信息。表上的统计信息是使用 COLLECT STATISTICS 命令收集的。优化器需要环境信息和数据特征才能制定最佳执行策略。
环境信息
- 节点、AMP 和 CPU 数量
- 内存大小
数据特征
- 行数
- 行大小
- 表中值的范围
- 每个值的行数
- 空值数量
有三种方法可以收集表上的统计信息。
- 随机 AMP 采样
- 完整统计信息收集
- 使用 SAMPLE 选项
收集统计信息
COLLECT STATISTICS 命令用于收集表上的统计信息。
语法
以下是收集表上统计信息的的基本语法。
COLLECT [SUMMARY] STATISTICS INDEX (indexname) COLUMN (columnname) ON <tablename>;
示例
以下示例收集员工表 EmployeeNo 列的统计信息。
COLLECT STATISTICS COLUMN(EmployeeNo) ON Employee;
执行上述查询时,将产生以下输出。
*** Update completed. 2 rows changed. *** Total elapsed time was 1 second.
查看统计信息
可以使用 HELP STATISTICS 命令查看已收集的统计信息。
语法
以下是查看已收集统计信息的语法。
HELP STATISTICS <tablename>;
示例
以下是如何查看员工表已收集统计信息的示例。
HELP STATISTICS employee;
执行上述查询时,会产生以下结果。
Date Time Unique Values Column Names -------- -------- -------------------- ----------------------- 16/01/01 08:07:04 5 * 16/01/01 07:24:16 3 DepartmentNo 16/01/01 08:07:04 5 EmployeeNo
Teradata - 压缩
压缩用于减少表使用的存储空间。在 Teradata 中,压缩最多可以压缩 255 个不同的值,包括 NULL 值。由于存储空间减少了,Teradata 可以在一个块中存储更多记录。这将提高查询响应时间,因为任何 I/O 操作都可以每块处理更多行。可以使用 CREATE TABLE 命令在表创建时添加压缩,也可以使用 ALTER TABLE 命令在表创建后添加压缩。
限制
- 每列最多只能压缩 255 个值。
- 主键列不能压缩。
- 易失表不能压缩。
多值压缩 (MVC)
下表压缩了值为 1、2 和 3 的 DepatmentNo 字段。当对列应用压缩时,该列的值不会与行一起存储。相反,这些值存储在每个 AMP 的表头中,并且仅将存在位添加到行中以指示该值。
CREATE SET TABLE employee ( EmployeeNo integer, FirstName CHAR(30), LastName CHAR(30), BirthDate DATE FORMAT 'YYYY-MM-DD-', JoinedDate DATE FORMAT 'YYYY-MM-DD-', employee_gender CHAR(1), DepartmentNo CHAR(02) COMPRESS(1,2,3) ) UNIQUE PRIMARY INDEX(EmployeeNo);
当大型表中有一列具有有限的值时,可以使用多值压缩。
Teradata - EXPLAIN
EXPLAIN 命令以英文返回解析引擎的执行计划。它可以与任何 SQL 语句一起使用,但不能用于另一个 EXPLAIN 命令。当查询前带有 EXPLAIN 命令时,解析引擎的执行计划将返回给用户,而不是 AMP。
EXPLAIN 示例
考虑具有以下定义的 Employee 表。
CREATE SET TABLE EMPLOYEE,FALLBACK ( EmployeeNo INTEGER, FirstName VARCHAR(30), LastName VARCHAR(30), DOB DATE FORMAT 'YYYY-MM-DD', JoinedDate DATE FORMAT 'YYYY-MM-DD', DepartmentNo BYTEINT ) UNIQUE PRIMARY INDEX ( EmployeeNo );
以下是一些 EXPLAIN 计划示例。
全表扫描 (FTS)
如果 SELECT 语句中未指定任何条件,则优化器可能会选择使用全表扫描,其中访问表的每一行。
示例
以下是一个示例查询,其中优化器可能会选择 FTS。
EXPLAIN SELECT * FROM employee;
执行上述查询时,会产生以下输出。可以看出,优化器选择访问所有 AMP 和 AMP 中的所有行。
1) First, we lock a distinct TDUSER."pseudo table" for read on a RowHash to prevent global deadlock for TDUSER.employee. 2) Next, we lock TDUSER.employee for read. 3) We do an all-AMPs RETRIEVE step from TDUSER.employee by way of an all-rows scan with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 2 rows (116 bytes). The estimated time for this step is 0.03 seconds. 4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. → The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.03 seconds.
唯一主键索引
使用唯一主键索引访问行时,这是一个 AMP 操作。
EXPLAIN SELECT * FROM employee WHERE EmployeeNo = 101;
执行上述查询时,会产生以下输出。可以看出,这是一个单 AMP 检索,优化器正在使用唯一主键索引来访问行。
1) First, we do a single-AMP RETRIEVE step from TDUSER.employee by way of the unique primary index "TDUSER.employee.EmployeeNo = 101" with no residual conditions. The estimated time for this step is 0.01 seconds. → The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.01 seconds.
唯一二级索引
使用唯一二级索引访问行时,这是一个双 AMP 操作。
示例
考虑具有以下定义的 Salary 表。
CREATE SET TABLE SALARY,FALLBACK ( EmployeeNo INTEGER, Gross INTEGER, Deduction INTEGER, NetPay INTEGER ) PRIMARY INDEX ( EmployeeNo ) UNIQUE INDEX (EmployeeNo);
考虑以下 SELECT 语句。
EXPLAIN SELECT * FROM Salary WHERE EmployeeNo = 101;
执行上述查询时,会产生以下输出。可以看出,优化器使用唯一二级索引在两个 AMP 操作中检索行。
1) First, we do a two-AMP RETRIEVE step from TDUSER.Salary by way of unique index # 4 "TDUSER.Salary.EmployeeNo = 101" with no residual conditions. The estimated time for this step is 0.01 seconds. → The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.01 seconds.
附加术语
以下是 EXPLAIN 计划中常见的术语列表。
…(最后使用)…
不再需要临时文件,此步骤完成后将释放。
…没有残留条件…
所有适用的条件都已应用于行。
…END TRANSACTION…
事务锁被释放,更改被提交。
…消除重复行…
重复行仅存在于临时文件中,而不存在于集合表中。正在执行 DISTINCT 操作。
…通过遍历索引 #n 仅提取行 ID 的方式…
构建一个包含在二级索引(索引 #n)中找到的行 ID 的临时文件。
…我们执行 SMS(集合操作步骤)…
使用 UNION、MINUS 或 INTERSECT 运算符组合行。
…它通过哈希码重新分布到所有 AMP。
重新分布数据以准备连接。
…它在所有 AMP 上复制。
复制来自较小表(就 SPOOL 而言)的数据以准备连接。
…(one_AMP)或(group_AMPs)
指示将使用一个 AMP 或 AMP 子集,而不是所有 AMP。
Teradata - 哈希算法
行根据主键值分配给特定的 AMP。Teradata 使用哈希算法来确定哪个 AMP 获取该行。
以下是关于哈希算法的高级图表。

以下是插入数据的步骤。
客户端提交查询。
解析器接收查询并将记录的 PI 值传递给哈希算法。
哈希算法对主键值进行哈希处理并返回一个 32 位数字,称为行哈希。
行哈希的高位(前 16 位)用于标识哈希映射条目。哈希映射包含一个 AMP 编号。哈希映射是一个桶数组,包含特定的 AMP 编号。
BYNET 将数据发送到已识别的 AMP。
AMP 使用 32 位行哈希在其磁盘中定位行。
如果存在任何具有相同行哈希的记录,则它会递增唯一性 ID(一个 32 位数字)。对于新的行哈希,唯一性 ID 被分配为 1,并且每当插入具有相同行哈希的记录时都会递增。
行哈希和唯一性 ID 的组合称为行 ID。
行 ID 在磁盘中为每条记录添加前缀。
AMP 中的每个表行按其行 ID 逻辑排序。
表的存储方式
表按其行 ID(行哈希 + 唯一性 ID)排序,然后存储在 AMP 中。行 ID 与每条数据行一起存储。
行哈希 | 唯一性 ID | 员工编号 | 名字 | 姓氏 |
---|---|---|---|---|
2A01 2611 | 0000 0001 | 101 | Mike | James |
2A01 2612 | 0000 0001 | 104 | Alex | Stuart |
2A01 2613 | 0000 0001 | 102 | Robert | Williams |
2A01 2614 | 0000 0001 | 105 | Robert | James |
2A01 2615 | 0000 0001 | 103 | Peter | Paul |
Teradata - JOIN 索引
JOIN 索引是物化视图。其定义永久存储,并且每当 JOIN 索引中引用的基表更新时,数据也会更新。JOIN 索引可能包含一个或多个表,也可能包含预聚合数据。JOIN 索引主要用于提高性能。
有不同类型的 JOIN 索引可用。
- 单表 JOIN 索引 (STJI)
- 多表 JOIN 索引 (MTJI)
- 聚合 JOIN 索引 (AJI)
单表 JOIN 索引
单表 JOIN 索引允许基于与基表不同的主键列对大型表进行分区。
语法
以下是 JOIN 索引的语法。
CREATE JOIN INDEX <index name> AS <SELECT Query> <Index Definition>;
示例
考虑以下 Employee 和 Salary 表。
CREATE SET TABLE EMPLOYEE,FALLBACK ( EmployeeNo INTEGER, FirstName VARCHAR(30) , LastName VARCHAR(30) , DOB DATE FORMAT 'YYYY-MM-DD', JoinedDate DATE FORMAT 'YYYY-MM-DD', DepartmentNo BYTEINT ) UNIQUE PRIMARY INDEX ( EmployeeNo ); CREATE SET TABLE SALARY,FALLBACK ( EmployeeNo INTEGER, Gross INTEGER, Deduction INTEGER, NetPay INTEGER ) PRIMARY INDEX ( EmployeeNo ) UNIQUE INDEX (EmployeeNo);
以下示例创建名为 Employee_JI 的 Employee 表的 JOIN 索引。
CREATE JOIN INDEX Employee_JI AS SELECT EmployeeNo,FirstName,LastName, BirthDate,JoinedDate,DepartmentNo FROM Employee PRIMARY INDEX(FirstName);
如果用户提交的查询包含关于 EmployeeNo 的 WHERE 子句,则系统将使用唯一主键索引查询 Employee 表。如果用户使用 employee_name 查询员工表,则系统可以使用 employee_name 访问 JOIN 索引 Employee_JI。JOIN 索引的行在 employee_name 列上进行哈希处理。如果没有定义 JOIN 索引并且 employee_name 没有定义为二级索引,则系统将执行全表扫描来访问行,这将非常耗时。
您可以运行以下 EXPLAIN 计划并验证优化器计划。在以下示例中,您可以看到当表使用 Employee_Name 列进行查询时,优化器使用 JOIN 索引而不是基表 Employee。
EXPLAIN SELECT * FROM EMPLOYEE WHERE FirstName='Mike'; *** Help information returned. 8 rows. *** Total elapsed time was 1 second. Explanation ------------------------------------------------------------------------ 1) First, we do a single-AMP RETRIEVE step from EMPLOYEE_JI by way of the primary index "EMPLOYEE_JI.FirstName = 'Mike'" with no residual conditions into Spool 1 (one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with low confidence to be 2 rows (232 bytes). The estimated time for this step is 0.02 seconds. → The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.02 seconds.
多表 JOIN 索引
多表 JOIN 索引是通过连接多个表创建的。多表 JOIN 索引可用于存储经常连接的表的結果集以提高性能。
示例
以下示例通过连接 Employee 和 Salary 表创建名为 Employee_Salary_JI 的 JOIN 索引。
CREATE JOIN INDEX Employee_Salary_JI AS SELECT a.EmployeeNo,a.FirstName,a.LastName, a.BirthDate,a.JoinedDate,a.DepartmentNo,b.Gross,b.Deduction,b.NetPay FROM Employee a INNER JOIN Salary b ON(a.EmployeeNo = b.EmployeeNo) PRIMARY INDEX(FirstName);
每当基表 Employee 或 Salary 更新时,JOIN 索引 Employee_Salary_JI 也会自动更新。如果您正在运行连接 Employee 和 Salary 表的查询,则优化器可能会选择直接从 Employee_Salary_JI 访问数据,而不是连接表。查询的 EXPLAIN 计划可用于验证优化器是否会选择基表或 JOIN 索引。
聚合 JOIN 索引
如果表始终在某些列上进行聚合,则可以在表上定义聚合 JOIN 索引以提高性能。聚合 JOIN 索引的一个限制是它仅支持 SUM 和 COUNT 函数。
示例
在以下示例中,Employee 和 Salary 连接以识别每个部门的总薪水。
CREATE JOIN INDEX Employee_Salary_JI AS SELECT a.DepartmentNo,SUM(b.NetPay) AS TotalPay FROM Employee a INNER JOIN Salary b ON(a.EmployeeNo = b.EmployeeNo) GROUP BY a.DepartmentNo Primary Index(DepartmentNo);
Teradata - 视图
视图是由查询构建的数据库对象。视图可以使用单个表或通过连接使用多个表来构建。它们的定义永久存储在数据字典中,但它们不存储数据的副本。视图的数据是动态构建的。
视图可能包含表的行子集或表的列子集。
创建视图
视图是使用 CREATE VIEW 语句创建的。
语法
以下是创建视图的语法。
CREATE/REPLACE VIEW <viewname> AS <select query>;
示例
考虑以下员工表。
员工编号 | 名字 | 姓氏 | 出生日期 |
---|---|---|---|
101 | Mike | James | 1/5/1980 |
104 | Alex | Stuart | 11/6/1984 |
102 | Robert | Williams | 3/5/1983 |
105 | Robert | James | 12/1/1984 |
103 | Peter | Paul | 4/1/1983 |
以下示例在 Employee 表上创建一个视图。
CREATE VIEW Employee_View AS SELECT EmployeeNo, FirstName, LastName, FROM Employee;
使用视图
您可以使用常规 SELECT 语句从视图检索数据。
示例
以下示例从 Employee_View 检索记录;
SELECT EmployeeNo, FirstName, LastName FROM Employee_View;
执行上述查询时,将产生以下输出。
*** Query completed. 5 rows found. 3 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName LastName ----------- ------------------------------ --------------------------- 101 Mike James 104 Alex Stuart 102 Robert Williams 105 Robert James 103 Peter Paul
修改视图
可以使用 REPLACE VIEW 语句修改现有视图。
以下是修改视图的语法。
REPLACE VIEW <viewname> AS <select query>;
示例
以下示例修改 Employee_View 视图以添加其他列。
REPLACE VIEW Employee_View AS SELECT EmployeeNo, FirstName, BirthDate, JoinedDate DepartmentNo FROM Employee;
删除视图
可以使用 DROP VIEW 语句删除现有视图。
语法
以下是 DROP VIEW 的语法。
DROP VIEW <viewname>;
示例
以下是如何删除 Employee_View 视图的示例。
DROP VIEW Employee_View;
视图的优点
视图通过限制表的行或列来提供额外的安全级别。
用户可以只被授予对视图的访问权限,而不是对基表的访问权限。
通过使用视图预先连接多个表来简化多个表的使用。
Teradata - 宏
宏是一组存储的SQL语句,通过调用宏名称来执行。宏的定义存储在数据字典中。用户只需要 EXEC 权限即可执行宏。用户不需要对宏中使用的数据库对象拥有单独的权限。宏语句作为一个事务执行。如果宏中的一个SQL语句失败,则所有语句都将回滚。宏可以接受参数。宏可以包含DDL语句,但这应该是宏中的最后一条语句。
创建宏
使用 CREATE MACRO 语句创建宏。
语法
以下是 CREATE MACRO 命令的通用语法。
CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( <sql statements> );
示例
考虑以下员工表。
员工编号 | 名字 | 姓氏 | 出生日期 |
---|---|---|---|
101 | Mike | James | 1/5/1980 |
104 | Alex | Stuart | 11/6/1984 |
102 | Robert | Williams | 3/5/1983 |
105 | Robert | James | 12/1/1984 |
103 | Peter | Paul | 4/1/1983 |
以下示例创建一个名为 Get_Emp 的宏。它包含一个 select 语句,用于从员工表检索记录。
CREATE MACRO Get_Emp AS ( SELECT EmployeeNo, FirstName, LastName FROM employee ORDER BY EmployeeNo; );
执行宏
使用 EXEC 命令执行宏。
语法
以下是 EXECUTE MACRO 命令的语法。
EXEC <macroname>;
示例
以下示例执行名为 Get_Emp 的宏;执行以下命令时,它将检索员工表中的所有记录。
EXEC Get_Emp; *** Query completed. 5 rows found. 3 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName LastName ----------- ------------------------------ --------------------------- 101 Mike James 102 Robert Williams 103 Peter Paul 104 Alex Stuart 105 Robert James
参数化宏
Teradata 宏可以接受参数。在宏中,这些参数用 ;(分号)引用。
以下是一个接受参数的宏示例。
CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS ( SELECT EmployeeNo, NetPay FROM Salary WHERE EmployeeNo = :EmployeeNo; );
执行参数化宏
使用 EXEC 命令执行宏。您需要 EXEC 权限才能执行宏。
语法
以下是 EXECUTE MACRO 语句的语法。
EXEC <macroname>(value);
示例
以下示例执行名为 Get_Emp 的宏;它接受员工编号作为参数,并提取该员工的员工表记录。
EXEC Get_Emp_Salary(101); *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo NetPay ----------- ------------ 101 36000
Teradata - 存储过程
存储过程包含一组SQL语句和过程语句。它们可能只包含过程语句。存储过程的定义存储在数据库中,参数存储在数据字典表中。
优点
存储过程减少了客户端和服务器之间的网络负载。
提供更好的安全性,因为数据是通过存储过程访问的,而不是直接访问。
提供更好的维护,因为业务逻辑已在服务器上进行测试和存储。
创建过程
使用 CREATE PROCEDURE 语句创建存储过程。
语法
以下是 CREATE PROCEDURE 语句的通用语法。
CREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] ) BEGIN <SQL or SPL statements>; END;
示例
考虑以下薪资表。
员工编号 | 总额 | 扣除 | 净薪 |
---|---|---|---|
101 | 40,000 | 4,000 | 36,000 |
102 | 80,000 | 6,000 | 74,000 |
103 | 90,000 | 7,000 | 83,000 |
104 | 75,000 | 5,000 | 70,000 |
以下示例创建一个名为 InsertSalary 的存储过程,用于接受值并插入到 Salary 表中。
CREATE PROCEDURE InsertSalary ( IN in_EmployeeNo INTEGER, IN in_Gross INTEGER, IN in_Deduction INTEGER, IN in_NetPay INTEGER ) BEGIN INSERT INTO Salary ( EmployeeNo, Gross, Deduction, NetPay ) VALUES ( :in_EmployeeNo, :in_Gross, :in_Deduction, :in_NetPay ); END;
执行过程
使用 CALL 语句执行存储过程。
语法
以下是 CALL 语句的通用语法。
CALL <procedure name> [(parameter values)];
示例
以下示例调用存储过程 InsertSalary 并将记录插入到 Salary 表中。
CALL InsertSalary(105,20000,2000,18000);
执行上述查询后,它将产生以下输出,您可以在 Salary 表中看到插入的行。
员工编号 | 总额 | 扣除 | 净薪 |
---|---|---|---|
101 | 40,000 | 4,000 | 36,000 |
102 | 80,000 | 6,000 | 74,000 |
103 | 90,000 | 7,000 | 83,000 |
104 | 75,000 | 5,000 | 70,000 |
105 | 20,000 | 2,000 | 18,000 |
Teradata - 连接策略
本章讨论 Teradata 中可用的各种连接策略。
连接方法
Teradata 使用不同的连接方法执行连接操作。一些常用的连接方法是:
- 合并连接
- 嵌套连接
- 笛卡尔积连接
合并连接
当连接基于相等条件时,将发生合并连接。合并连接要求连接行位于相同的 AMP 上。行根据其行哈希进行连接。合并连接使用不同的连接策略将行带到相同的 AMP。
策略 #1
如果连接列是相应表的主索引,则连接行已位于相同的 AMP 上。在这种情况下,不需要重新分布。
考虑以下员工和薪资表。
CREATE SET TABLE EMPLOYEE,FALLBACK ( EmployeeNo INTEGER, FirstName VARCHAR(30) , LastName VARCHAR(30) , DOB DATE FORMAT 'YYYY-MM-DD', JoinedDate DATE FORMAT 'YYYY-MM-DD', DepartmentNo BYTEINT ) UNIQUE PRIMARY INDEX ( EmployeeNo );
CREATE SET TABLE Salary ( EmployeeNo INTEGER, Gross INTEGER, Deduction INTEGER, NetPay INTEGER ) UNIQUE PRIMARY INDEX(EmployeeNo);
当这两个表根据 EmployeeNo 列连接时,不会发生重新分布,因为 EmployeeNo 是正在连接的两个表的主索引。
策略 #2
考虑以下员工和部门表。
CREATE SET TABLE EMPLOYEE,FALLBACK ( EmployeeNo INTEGER, FirstName VARCHAR(30) , LastName VARCHAR(30) , DOB DATE FORMAT 'YYYY-MM-DD', JoinedDate DATE FORMAT 'YYYY-MM-DD', DepartmentNo BYTEINT ) UNIQUE PRIMARY INDEX ( EmployeeNo );
CREATE SET TABLE DEPARTMENT,FALLBACK ( DepartmentNo BYTEINT, DepartmentName CHAR(15) ) UNIQUE PRIMARY INDEX ( DepartmentNo );
如果这两个表根据 DeparmentNo 列连接,则需要重新分布行,因为 DepartmentNo 在一个表中是主索引,而在另一个表中是非主索引。在这种情况下,连接行可能不在相同的 AMP 上。在这种情况下,Teradata 可能会根据 DepartmentNo 列重新分布员工表。
策略 #3
对于上述员工和部门表,如果部门表的大小较小,Teradata 可能会在所有 AMP 上复制部门表。
嵌套连接
嵌套连接不使用所有 AMP。为了进行嵌套连接,其中一个条件应该是对一个表唯一主键的相等条件,然后将此列连接到另一个表的任何索引。
在这种情况下,系统将使用一个表唯一主键获取一行,并使用该行哈希从另一个表中获取匹配的记录。嵌套连接是所有连接方法中最有效的。
笛卡尔积连接
笛卡尔积连接将一个表中的每个合格行与另一个表中的每个合格行进行比较。由于以下一些因素,可能会发生笛卡尔积连接:
- 缺少 where 条件。
- 连接条件不是基于相等条件。
- 表别名不正确。
- 多个连接条件。
Teradata - 分区主索引
分区主键索引 (PPI) 是一种索引机制,可用于提高某些查询的性能。当将行插入表中时,它们将存储在 AMP 中并按其行哈希顺序排列。当表使用 PPI 定义时,行按其分区号排序。在每个分区内,它们按其行哈希排列。行根据定义的分区表达式分配给分区。
优点
避免对某些查询进行全表扫描。
避免使用需要额外物理结构和额外 I/O 维护的辅助索引。
快速访问大型表的子集。
快速删除旧数据并添加新数据。
示例
考虑以下具有 OrderNo 主索引的 Orders 表。
StoreNo | OrderNo | OrderDate | OrderTotal |
---|---|---|---|
101 | 7501 | 2015-10-01 | 900 |
101 | 7502 | 2015-10-02 | 1,200 |
102 | 7503 | 2015-10-02 | 3,000 |
102 | 7504 | 2015-10-03 | 2,454 |
101 | 7505 | 2015-10-03 | 1201 |
103 | 7506 | 2015-10-04 | 2,454 |
101 | 7507 | 2015-10-05 | 1201 |
101 | 7508 | 2015-10-05 | 1201 |
假设记录在 AMP 之间分布,如以下表所示。记录存储在 AMP 中,并根据其行哈希排序。
RowHash | OrderNo | OrderDate |
---|---|---|
1 | 7505 | 2015-10-03 |
2 | 7504 | 2015-10-03 |
3 | 7501 | 2015-10-01 |
4 | 7508 | 2015-10-05 |
RowHash | OrderNo | OrderDate |
---|---|---|
1 | 7507 | 2015-10-05 |
2 | 7502 | 2015-10-02 |
3 | 7506 | 2015-10-04 |
4 | 7503 | 2015-10-02 |
如果您运行查询以提取特定日期的订单,则优化器可能会选择使用全表扫描,然后可能会访问 AMP 中的所有记录。为了避免这种情况,您可以将订单日期定义为分区主键索引。当行插入订单表时,它们将按订单日期进行分区。在每个分区内,它们将按其行哈希排序。
以下数据显示了如果按订单日期进行分区,记录将如何存储在 AMP 中。如果运行查询以按订单日期访问记录,则只访问包含该特定订单记录的分区。
分区 | RowHash | OrderNo | OrderDate |
---|---|---|---|
0 | 3 | 7501 | 2015-10-01 |
1 | 1 | 7505 | 2015-10-03 |
1 | 2 | 7504 | 2015-10-03 |
2 | 4 | 7508 | 2015-10-05 |
分区 | RowHash | OrderNo | OrderDate |
---|---|---|---|
0 | 2 | 7502 | 2015-10-02 |
0 | 4 | 7503 | 2015-10-02 |
1 | 3 | 7506 | 2015-10-04 |
2 | 1 | 7507 | 2015-10-05 |
以下是如何创建具有分区主键索引的表的示例。PARTITION BY 子句用于定义分区。
CREATE SET TABLE Orders ( StoreNo SMALLINT, OrderNo INTEGER, OrderDate DATE FORMAT 'YYYY-MM-DD', OrderTotal INTEGER ) PRIMARY INDEX(OrderNo) PARTITION BY RANGE_N ( OrderDate BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY );
在上面的示例中,表按 OrderDate 列进行分区。每天将有一个单独的分区。
Teradata - OLAP 函数
OLAP 函数类似于聚合函数,除了聚合函数只返回一个值,而 OLAP 函数除了聚合之外还会提供各个行。
语法
以下是 OLAP 函数的通用语法。
<aggregate function> OVER ([PARTITION BY] [ORDER BY columnname][ROWS BETWEEN UNBOUDED PRECEDING AND UNBOUNDED FOLLOWING)
聚合函数可以是 SUM、COUNT、MAX、MIN、AVG。
示例
考虑以下薪资表。
员工编号 | 总额 | 扣除 | 净薪 |
---|---|---|---|
101 | 40,000 | 4,000 | 36,000 |
102 | 80,000 | 6,000 | 74,000 |
103 | 90,000 | 7,000 | 83,000 |
104 | 75,000 | 5,000 | 70,000 |
以下是如何查找 Salary 表上 NetPay 的累积和或运行总计的示例。记录按 EmployeeNo 排序,并计算 NetPay 列的累积和。
SELECT EmployeeNo, NetPay, SUM(Netpay) OVER(ORDER BY EmployeeNo ROWS UNBOUNDED PRECEDING) as TotalSalary FROM Salary;
执行上述查询时,将产生以下输出。
EmployeeNo NetPay TotalSalary ----------- ----------- ----------- 101 36000 36000 102 74000 110000 103 83000 193000 104 70000 263000 105 18000 281000
RANK
RANK 函数根据提供的列对记录进行排序。RANK 函数还可以根据排名过滤返回的记录数。
语法
以下是使用 RANK 函数的通用语法。
RANK() OVER ([PARTITION BY columnnlist] [ORDER BY columnlist][DESC|ASC])
示例
考虑以下员工表。
员工编号 | 名字 | 姓氏 | 加入日期 | DepartmentID | 出生日期 |
---|---|---|---|---|---|
101 | Mike | James | 3/27/2005 | 1 | 1/5/1980 |
102 | Robert | Williams | 4/25/2007 | 2 | 3/5/1983 |
103 | Peter | Paul | 3/21/2007 | 2 | 4/1/1983 |
104 | Alex | Stuart | 2/1/2008 | 2 | 11/6/1984 |
105 | Robert | James | 1/4/2008 | 3 | 12/1/1984 |
以下查询按加入日期对员工表记录进行排序,并根据加入日期分配排名。
SELECT EmployeeNo, JoinedDate,RANK() OVER(ORDER BY JoinedDate) as Seniority FROM Employee;
执行上述查询时,将产生以下输出。
EmployeeNo JoinedDate Seniority ----------- ---------- ----------- 101 2005-03-27 1 103 2007-03-21 2 102 2007-04-25 3 105 2008-01-04 4 104 2008-02-01 5
PARTITION BY 子句按 PARTITION BY 子句中定义的列对数据进行分组,并在每个组内执行 OLAP 函数。以下是用 PARTITION BY 子句的查询示例。
SELECT EmployeeNo, JoinedDate,RANK() OVER(PARTITION BY DeparmentNo ORDER BY JoinedDate) as Seniority FROM Employee;
执行上述查询后,它将产生以下输出。您可以看到每个部门的排名都会重置。
EmployeeNo DepartmentNo JoinedDate Seniority ----------- ------------ ---------- ----------- 101 1 2005-03-27 1 103 2 2007-03-21 1 102 2 2007-04-25 2 104 2 2008-02-01 3 105 3 2008-01-04 1
Teradata - 数据保护
本章讨论 Teradata 中可用的数据保护功能。
瞬态日志
Teradata 使用瞬态日志来保护数据免受事务失败的影响。每当运行任何事务时,瞬态日志都会保留受影响行的之前映像的副本,直到事务成功或成功回滚。然后,将丢弃之前映像。瞬态日志保存在每个 AMP 中。这是一个自动过程,无法禁用。
回退
回退通过将表的行副本存储在另一个称为回退 AMP 的 AMP 上来保护表数据。如果一个 AMP 失败,则访问回退行。这样,即使一个 AMP 失败,数据仍然可以通过回退 AMP 访问。回退选项可以在创建表时或创建表后使用。回退确保表的行副本始终存储在另一个 AMP 中,以保护数据免受 AMP 故障的影响。但是,回退会占用插入/删除/更新的两倍存储空间和 I/O。
下图显示了如何在另一个 AMP 中存储行的回退副本。

宕机 AMP 恢复日志
当 AMP 失败并且表受到回退保护时,将激活宕机 AMP 恢复日志。此日志跟踪对失败 AMP 数据的所有更改。该日志在集群中的其余 AMP 上激活。这是一个自动过程,无法禁用。一旦失败的 AMP 恢复运行,则宕机 AMP 恢复日志中的数据将与 AMP 同步。完成后,将丢弃该日志。

集群
集群是 Teradata 用于保护数据免受节点故障影响的机制。集群只不过是一组共享常用磁盘阵列的 Teradata 节点。当一个节点发生故障时,来自失败节点的 vproc 将迁移到集群中的其他节点,并继续访问其磁盘阵列。
热备节点
热备节点是不参与生产环境的节点。如果一个节点发生故障,则来自失败节点的 vproc 将迁移到热备节点。一旦失败的节点恢复,它将成为热备节点。热备节点用于在节点发生故障时保持性能。
RAID
独立磁盘冗余阵列 (RAID) 是一种用于保护数据免受磁盘故障影响的机制。磁盘阵列由一组磁盘组成,这些磁盘组合成一个逻辑单元。对于用户而言,此单元可能看起来像单个单元,但它们可能分布在多个磁盘上。
RAID 1 通常用于 Teradata。在 RAID 1 中,每个磁盘都与一个镜像磁盘关联。对主磁盘中数据的任何更改也都会反映在镜像副本中。如果主磁盘发生故障,则可以访问镜像磁盘中的数据。

Teradata - 用户管理
本章讨论了 Teradata 中用户管理的各种策略。
用户
使用 CREATE USER 命令创建用户。在 Teradata 中,用户也类似于数据库。两者都可以分配空间并包含数据库对象,只是用户分配了密码。
语法
以下是 CREATE USER 的语法。
CREATE USER username AS [PERMANENT|PERM] = n BYTES PASSWORD = password TEMPORARY = n BYTES SPOOL = n BYTES;
创建用户时,用户名、永久空间和密码的值是必需的。其他字段是可选的。
示例
以下是如何创建用户 TD01 的示例。
CREATE USER TD01 AS PERMANENT = 1000000 BYTES PASSWORD = ABC$124 TEMPORARY = 1000000 BYTES SPOOL = 1000000 BYTES;
帐户
创建新用户时,可以将用户分配给帐户。CREATE USER 中的 ACCOUNT 选项用于分配帐户。用户可以分配到多个帐户。
语法
以下是带有帐户选项的 CREATE USER 的语法。
CREATE USER username PERM = n BYTES PASSWORD = password ACCOUNT = accountid
示例
以下示例创建用户 TD02 并将其帐户分配为 IT 和 Admin。
CREATE USER TD02 AS PERMANENT = 1000000 BYTES PASSWORD = abc$123 TEMPORARY = 1000000 BYTES SPOOL = 1000000 BYTES ACCOUNT = (‘IT’,’Admin’);
用户可以在登录 Teradata 系统时或使用 SET SESSION 命令登录系统后指定帐户 ID。
.LOGON username, passowrd,accountid OR SET SESSION ACCOUNT = accountid
授予权限
GRANT 命令用于将一个或多个数据库对象的权限赋予用户或数据库。
语法
以下是 GRANT 命令的语法。
GRANT privileges ON objectname TO username;
权限可以是 INSERT、SELECT、UPDATE、REFERENCES。
示例
以下是一个 GRANT 语句的示例。
GRANT SELECT,INSERT,UPDATE ON Employee TO TD01;
撤销权限
REVOKE 命令用于从用户或数据库中删除权限。REVOKE 命令只能删除显式权限。
语法
以下是 REVOKE 命令的基本语法。
REVOKE [ALL|privileges] ON objectname FROM username;
示例
以下是一个 REVOKE 命令的示例。
REVOKE INSERT,SELECT ON Employee FROM TD01;
Teradata - 性能调优
本章讨论 Teradata 中性能调优的过程。
解释
性能调优的第一步是对查询使用 EXPLAIN。EXPLAIN 计划详细说明了优化器如何执行查询。在 Explain 计划中,检查诸如置信度级别、使用的连接策略、临时文件大小、重新分配等关键字。
收集统计信息
优化器使用数据统计信息来制定有效的执行策略。COLLECT STATISTICS 命令用于收集表的统计信息。确保收集的列统计信息是最新的。
收集 WHERE 子句中使用的列以及连接条件中使用的列的统计信息。
收集唯一主键列的统计信息。
收集非唯一二级索引列的统计信息。优化器将决定是否可以使用 NUSI 或全表扫描。
尽管收集了基表的统计信息,但也要收集连接索引的统计信息。
收集分区列的统计信息。
数据类型
确保使用了正确的数据类型。这将避免使用超过所需的空间。
转换
确保连接条件中使用的列的数据类型兼容,以避免显式数据转换。
排序
除非需要,否则删除不必要的 ORDER BY 子句。
临时空间问题
如果查询超过该用户的每个 AMP 临时空间限制,则会生成临时空间错误。验证解释计划并识别消耗更多临时空间的步骤。这些中间查询可以拆分并单独放置以构建临时表。
主键
确保为表正确定义了主键。主键列应均匀分布数据,并且应经常用于访问数据。
SET 表
如果定义 SET 表,则优化器将检查为每个插入的记录是否重复。要删除重复检查条件,可以为表定义唯一二级索引。
更新大型表
更新大型表将非常耗时。与其更新表,不如删除记录并插入包含修改行的记录。
删除临时表
如果不再需要,请删除临时表(暂存表)和易失表。这将释放永久空间和临时空间。
MULTISET 表
如果您确定输入记录不会有重复记录,则可以将目标表定义为 MULTISET 表,以避免 SET 表使用的重复行检查。
Teradata - FastLoad
FastLoad 实用程序用于将数据加载到空表中。因为它不使用暂存日志,所以可以快速加载数据。即使目标表是 MULTISET 表,它也不会加载重复行。
限制
目标表不应具有二级索引、连接索引和外键引用。
FastLoad 的工作原理
FastLoad 分两个阶段执行。
阶段 1
解析引擎从输入文件中读取记录并将一个块发送到每个 AMP。
每个 AMP 存储记录块。
然后,AMP 对每个记录进行哈希处理并将其重新分配到正确的 AMP。
在阶段 1 结束时,每个 AMP 都有其行,但它们并非按行哈希顺序排列。
阶段 2
当 FastLoad 接收到 END LOADING 语句时,阶段 2 开始。
每个 AMP 对记录进行行哈希排序并将其写入磁盘。
目标表的锁被释放,错误表被删除。
示例
创建一个包含以下记录的文本文件,并将文件命名为 employee.txt。
101,Mike,James,1980-01-05,2010-03-01,1 102,Robert,Williams,1983-03-05,2010-09-01,1 103,Peter,Paul,1983-04-01,2009-02-12,2 104,Alex,Stuart,1984-11-06,2014-01-01,2 105,Robert,James,1984-12-01,2015-03-09,3
以下是一个将上述文件加载到 Employee_Stg 表的 FastLoad 脚本示例。
LOGON 192.168.1.102/dbc,dbc; DATABASE tduser; BEGIN LOADING tduser.Employee_Stg ERRORFILES Employee_ET, Employee_UV CHECKPOINT 10; SET RECORD VARTEXT ","; DEFINE in_EmployeeNo (VARCHAR(10)), in_FirstName (VARCHAR(30)), in_LastName (VARCHAR(30)), in_BirthDate (VARCHAR(10)), in_JoinedDate (VARCHAR(10)), in_DepartmentNo (VARCHAR(02)), FILE = employee.txt; INSERT INTO Employee_Stg ( EmployeeNo, FirstName, LastName, BirthDate, JoinedDate, DepartmentNo ) VALUES ( :in_EmployeeNo, :in_FirstName, :in_LastName, :in_BirthDate (FORMAT 'YYYY-MM-DD'), :in_JoinedDate (FORMAT 'YYYY-MM-DD'), :in_DepartmentNo ); END LOADING; LOGOFF;
执行 FastLoad 脚本
创建输入文件 employee.txt 并将 FastLoad 脚本命名为 EmployeeLoad.fl 后,可以使用以下命令在 UNIX 和 Windows 中运行 FastLoad 脚本。
FastLoad < EmployeeLoad.fl;
执行上述命令后,FastLoad 脚本将运行并生成日志。在日志中,您可以看到 FastLoad 处理的记录数量和状态代码。
**** 03:19:14 END LOADING COMPLETE Total Records Read = 5 Total Error Table 1 = 0 ---- Table has been dropped Total Error Table 2 = 0 ---- Table has been dropped Total Inserts Applied = 5 Total Duplicate Rows = 0 Start: Fri Jan 8 03:19:13 2016 End : Fri Jan 8 03:19:14 2016 **** 03:19:14 Application Phase statistics: Elapsed time: 00:00:01 (in hh:mm:ss) 0008 LOGOFF; **** 03:19:15 Logging off all sessions
FastLoad 术语
以下是 FastLoad 脚本中常用的术语列表。
LOGON − 登录 Teradata 并启动一个或多个会话。
DATABASE − 设置默认数据库。
BEGIN LOADING − 标识要加载的表。
ERRORFILES − 标识需要创建/更新的 2 个错误表。
CHECKPOINT − 定义何时进行检查点。
SET RECORD − 指定输入文件格式是格式化、二进制、文本还是非格式化。
DEFINE − 定义输入文件布局。
FILE − 指定输入文件名和路径。
INSERT − 将输入文件中的记录插入目标表。
END LOADING − 启动 FastLoad 的阶段 2。将记录分配到目标表。
LOGOFF − 结束所有会话并终止 FastLoad。
Teradata - MultiLoad
MultiLoad 可以在一次加载多个表,并且还可以执行不同的任务,例如 INSERT、DELETE、UPDATE 和 UPSERT。它一次最多可以加载 5 个表,并在脚本中执行多达 20 个 DML 操作。MultiLoad 不需要目标表。
MultiLoad 支持两种模式:
- 导入
- 删除
除了目标表外,MultiLoad 还需要一个工作表、一个日志表和两个错误表。
日志表 − 用于维护加载过程中进行的检查点,这些检查点将用于重新启动。
错误表 − 发生错误时,这些表在加载过程中被插入。第一个错误表存储转换错误,而第二个错误表存储重复记录。
日志表 − 维持 MultiLoad 各阶段的结果,用于重新启动。
工作表 − MultiLoad 脚本为每个目标表创建一个工作表。工作表用于保存 DML 任务和输入数据。
限制
MultiLoad 有一些限制。
- 目标表不支持唯一二级索引。
- 不支持引用完整性。
- 不支持触发器。
MultiLoad 的工作原理
MultiLoad 导入有五个阶段:
阶段 1 − 预备阶段 – 执行基本的设置活动。
阶段 2 − DML 事务阶段 – 验证 DML 语句的语法并将它们带入 Teradata 系统。
阶段 3 − 获取阶段 – 将输入数据带入工作表并锁定表。
阶段 4 − 应用阶段 – 应用所有 DML 操作。
阶段 5 − 清理阶段 – 释放表锁。
MultiLoad 脚本涉及的步骤:
步骤 1 − 设置日志表。
步骤 2 − 登录 Teradata。
步骤 3 − 指定目标表、工作表和错误表。
步骤 4 − 定义输入文件布局。
步骤 5 − 定义 DML 查询。
步骤 6 − 命名导入文件。
步骤 7 − 指定要使用的布局。
步骤 8 − 启动加载。
步骤 9 − 完成加载并终止会话。
示例
创建一个包含以下记录的文本文件,并将文件命名为 employee.txt。
101,Mike,James,1980-01-05,2010-03-01,1 102,Robert,Williams,1983-03-05,2010-09-01,1 103,Peter,Paul,1983-04-01,2009-02-12,2 104,Alex,Stuart,1984-11-06,2014-01-01,2 105,Robert,James,1984-12-01,2015-03-09,3
以下示例是一个 MultiLoad 脚本,它从 employee 表读取记录并加载到 Employee_Stg 表中。
.LOGTABLE tduser.Employee_log; .LOGON 192.168.1.102/dbc,dbc; .BEGIN MLOAD TABLES Employee_Stg; .LAYOUT Employee; .FIELD in_EmployeeNo * VARCHAR(10); .FIELD in_FirstName * VARCHAR(30); .FIELD in_LastName * VARCHAR(30); .FIELD in_BirthDate * VARCHAR(10); .FIELD in_JoinedDate * VARCHAR(10); .FIELD in_DepartmentNo * VARCHAR(02); .DML LABEL EmpLabel; INSERT INTO Employee_Stg ( EmployeeNo, FirstName, LastName, BirthDate, JoinedDate, DepartmentNo ) VALUES ( :in_EmployeeNo, :in_FirstName, :in_Lastname, :in_BirthDate, :in_JoinedDate, :in_DepartmentNo ); .IMPORT INFILE employee.txt FORMAT VARTEXT ',' LAYOUT Employee APPLY EmpLabel; .END MLOAD; LOGOFF;
执行 MultiLoad 脚本
创建输入文件 employee.txt 并将 multiload 脚本命名为 EmployeeLoad.ml 后,可以使用以下命令在 UNIX 和 Windows 中运行 Multiload 脚本。
Multiload < EmployeeLoad.ml;
Teradata - FastExport
FastExport 实用程序用于将 Teradata 表中的数据导出到平面文件中。它还可以生成报表格式的数据。可以使用连接从一个或多个表中提取数据。由于 FastExport 以 64K 块导出数据,因此它对于提取大量数据非常有用。
示例
考虑以下员工表。
员工编号 | 名字 | 姓氏 | 出生日期 |
---|---|---|---|
101 | Mike | James | 1/5/1980 |
104 | Alex | Stuart | 11/6/1984 |
102 | Robert | Williams | 3/5/1983 |
105 | Robert | James | 12/1/1984 |
103 | Peter | Paul | 4/1/1983 |
以下是一个 FastExport 脚本示例。它导出 employee 表中的数据并写入 employeedata.txt 文件。
.LOGTABLE tduser.employee_log; .LOGON 192.168.1.102/dbc,dbc; DATABASE tduser; .BEGIN EXPORT SESSIONS 2; .EXPORT OUTFILE employeedata.txt MODE RECORD FORMAT TEXT; SELECT CAST(EmployeeNo AS CHAR(10)), CAST(FirstName AS CHAR(15)), CAST(LastName AS CHAR(15)), CAST(BirthDate AS CHAR(10)) FROM Employee; .END EXPORT; .LOGOFF;
执行 FastExport 脚本
编写脚本并将其命名为 employee.fx 后,可以使用以下命令执行脚本。
fexp < employee.fx
执行上述命令后,您将在 employeedata.txt 文件中收到以下输出。
103 Peter Paul 1983-04-01 101 Mike James 1980-01-05 102 Robert Williams 1983-03-05 105 Robert James 1984-12-01 104 Alex Stuart 1984-11-06
FastExport 术语
以下是 FastExport 脚本中常用的术语列表。
LOGTABLE − 指定用于重新启动的日志表。
LOGON − 登录 Teradata 并启动一个或多个会话。
DATABASE − 设置默认数据库。
BEGIN EXPORT − 指示导出的开始。
EXPORT − 指定目标文件和导出格式。
SELECT − 指定导出数据的 select 查询。
END EXPORT − 指定 FastExport 的结束。
LOGOFF − 结束所有会话并终止 FastExport。
Teradata - BTEQ
BTEQ 实用程序是 Teradata 中一个功能强大的实用程序,可在批处理模式和交互模式下使用。它可用于运行任何 DDL 语句、DML 语句、创建宏和存储过程。BTEQ 可用于将数据从平面文件导入 Teradata 表,也可用于将数据从表提取到文件或报表中。
BTEQ 术语
以下是 BTEQ 脚本中常用的术语列表。
LOGON − 用于登录 Teradata 系统。
ACTIVITYCOUNT − 返回前一个查询影响的行数。
ERRORCODE − 返回前一个查询的状态代码。
DATABASE − 设置默认数据库。
LABEL − 将标签分配给一组 SQL 命令。
RUN FILE − 执行文件中包含的查询。
GOTO − 将控制转移到标签。
LOGOFF − 从数据库注销并终止所有会话。
IMPORT − 指定输入文件路径。
EXPORT − 指定输出文件路径并启动导出。
示例
以下是一个 BTEQ 脚本示例。
.LOGON 192.168.1.102/dbc,dbc; DATABASE tduser; CREATE TABLE employee_bkup ( EmployeeNo INTEGER, FirstName CHAR(30), LastName CHAR(30), DepartmentNo SMALLINT, NetPay INTEGER ) Unique Primary Index(EmployeeNo); .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; SELECT * FROM Employee Sample 1; .IF ACTIVITYCOUNT <> 0 THEN .GOTO InsertEmployee; DROP TABLE employee_bkup; .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; .LABEL InsertEmployee INSERT INTO employee_bkup SELECT a.EmployeeNo, a.FirstName, a.LastName, a.DepartmentNo, b.NetPay FROM Employee a INNER JOIN Salary b ON (a.EmployeeNo = b.EmployeeNo); .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; .LOGOFF;
上述脚本执行以下任务:
登录 Teradata 系统。
设置默认数据库。
创建一个名为 employee_bkup 的表。
从 Employee 表中选择一条记录以检查表中是否有任何记录。
如果表为空,则删除 employee_bkup 表。
将控制转移到标签 InsertEmployee,该标签将记录插入 employee_bkup 表。
检查 ERRORCODE 以确保语句成功,每个 SQL 语句之后都执行此操作。
ACTIVITYCOUNT 返回前一个 SQL 查询选择/影响的记录数。