TD建模系列（一）-TERADATA关键技术知识点

TERADATA关键知识点梳理

架构

架构图

组件

节点

​ Teradata系统中的每个单独的服务器都称为节点。 节点由自己的操作系统，CPU，内存，自己的Teradata RDBMS软件副本和磁盘空间组成

解析引擎

​ 解析引擎负责从客户端接收查询并准备有效的执行计划： 从客户端接收SQL查询 解析SQL查询检查语法错误 检查用户是否具有针对SQL查询中使用的对象的所需权限 检查在SQL中使用的对象是否确实存在 准备执行计划以执行SQL查询并将其传递给BYNET 从AMP接收结果并发送到客户端

消息传递层

​ BYNET
​ PE和AMP之间以及节点之间的通信
​ 从解析引擎接收执行计划并发送到AMP； 从AMP接收结果并发送到解析引擎

访问模块处理器

​ AMP
​ 虚拟处理器（vprocs）
​ AMP从解析引擎接收数据和执行计划，执行任何数据类型转换，聚合，过滤，排序并将数据存储在与其关联的磁盘中
​ 表中的记录均匀分布在系统中的AMP之间
​ 每个AMP与存储数据的一组磁盘相关联。 只有该AMP可以从磁盘读取/写入数据

存储架构

​ 当客户端运行查询以插入记录时，解析引擎将记录发送到BYNET。 BYNET检索记录并将该行发送到目标AMP

检索体系结构

​ 当客户端运行查询以检索记录时，解析引擎会向BYNET发送请求。 BYNET将检索请求发送到适当的AMP
​ 然后AMPs并行搜索其磁盘并识别所需的记录并发送到BYNET
​ BYNET然后将记录发送到解析引擎，解析引擎又将发送到客户端

Space概念

Permanent Space

​ Permanent Space是用户/数据库保存数据行的最大可用空间量
​ 永久表，日志，回退表和辅助索引子表使用Permanent Space

Spool Space

​ 关键，经常会出现spool不足错误
​ 定义了用户可以使用的最大空间量
​ Spool Space除以AMP的数量。 每当每个AMP限制超过，用户将得到Spool Space错误
​ SpoolSpace是未使用的永久空间，由系统用于保留SQL查询的中间结果。 没有Spool Space的用户无法执行任何查询

Temp Space

​ 临时数据空间
​ 未使用的永久空间，由Global Temporary tables使用

数据保护

​ Teradata使用瞬态日志来保护数据免受事务故障的影响。无论何时运行任何事务，Transient journal都会保留受影响行的before映像的副本，直到事务成功或回滚成功。然后，丢弃之前的图像。瞬时日志保存在每个AMP中。这是一个自动过程，不能禁用。
​ Fallback
​ Down AMP Recovery Journal
​ Cliques
​ Hot Standby Node
​ RAID

访问工具BETO

​ LOGON - 用于登录Teradata系统。
​ ACTIVITYCOUNT - 返回受上一个查询影响的行数。
​ ERRORCODE - 返回上一个查询的状态代码。
​ DATABASE - 设置默认数据库。
​ LABEL - 为一组SQL命令分配一个标签。
​ RUN FILE - 执行文件中包含的查询。
​ GOTO - 将控制转移到标签。
​ LOGOFF - 从数据库注销并终止所有会话。
​ IMPORT - 指定输入文件路径。
​ EXPORT - 指定输出文件路径并启动导出。

表类型

Derived Table

​ 子查询表
​ 在查询中创建，使用和删除。 这些用于在查询中存储中间结果

Volatile Table

​ 会话级临时表
​ 在用户会话中创建，使用和删除Volatile Table。 它们的定义不存储在数据字典中。 它们保存经常使用的查询的中间数据

Global Temporary Table

​ 全局临时表
​ Table的定义存储在数据字典中，并且它们可以被许多用户/会话使用。 但是加载到Global Temporary Table中的数据仅在会话期间保留。 每个会话最多可以实现2000个GlobalTemporaryTable

数据类型

表中的每个列都与数据类型相关联。 数据类型指定将在列中存储什么类型的值。 Teradata支持多种数据类型。 以下是一些常用的数据类型：

数据类型	长度（字节）	值的范围
BYTEINT	1	-1288到+127
SMALLINT	2	-32768到+32767
INTEGER	4	-2,147,483,648到+2147,483,647
BIGINT	8	-9,233,372,036,854,775,80 8到+9,233,372,036,854,775,8 07
DECIMAL	1-16
NUMERIC	1-16
FLOAT	8	IEEE格式
CHAR	固定格式	1-64,000
VARCHAR	变量	1-64,000
DATE	4	YYYYYMMDD
TIME	6或8	HHMMSS.nnnnnn 或 HHMMSS.nnnnnn+HHMM
TIMESTAMP	10或12	YYMMDDHHMMSS.nnnnnn 或YYMMDDHHMMSS.nnnnnn +HHMM

语法

合并运算符

​ union 或集去重
​ union all 或集
​ intersect 并集
​ minus/except 差集

字符串函数

Teradata提供了几个函数来操作字符串。 这些功能与ANSI标准兼容：

编号	字符串功能和说明
1	||将字符串连接在一起
2	SUBSTR提取字符串的一部分（Teradata扩展）
3	SUBSTRING提取字符串的一部分（ANSI标准）
4	INDEX查找字符在字符串中的位置（Teradata扩展）
5	POSITION定位字符在字符串中的位置（ANSI标准）
6	TRIM修剪字符串中的空格
7	UPPER将字符串转换为大写
8	LOWER将字符串转换为小写

日期/时间函数

• 日期存储
  日期使用以下公式在内部存储为整数
  ((YEAR - 1900) * 10000) + (MONTH * 100) + DAY
  以使用以下查询来检查日期的存储方式:
  SELECT CAST(CURRENT_DATE AS INTEGER)
• 日期提取
  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

• 间隔
  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年和01个月添加到当前日期。

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03-01' YEAR TO MONTH; 
 Date     (Date+ 3-01) 
--------  ------------ 
16/01/01    19/02/01

以下示例将01天，05小时和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

COALESCE函数

返回表达式的第一个非空值的语句

表联接 特别的

​ Self Join
​ Cross Join 交叉连接将左表中的每一行连接到右表中的每一行
​ Cartesian Production Join

存储过程

使用call调用

宏

​ 宏是一组SQL语句，通过调用宏名称来存储和执行。 宏的定义存储在数据字典中。 用户只需要EXEC权限来执行宏。 用户不需要对宏中使用的数据库对象具有单独的权限
​ 宏语句作为单个事务执行。 如果宏中的某个SQL语句失败，则所有语句都将回滚。 宏可以接受参数。 宏可以包含DDL语句，但应该是宏中的最后一个语句。

创建语法

CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( <sql statements> );

执行语法

EXEC <macroname>;

参数化宏

​ 类似动态sql引入参数，使用：绑定参数变量
​ 执行 EXEC <macroname>(value);

OLAP函数

​ 就是管道函数

聚合

​ 语法

<aggregate function> OVER   
([PARTITION BY][ORDER BY columnname][ROWS BETWEEN  UNBOUDED PRECEDING AND UNBOUNDED FOLLWOING) 

其聚合函数可以是SUM，COUNT，MAX，MIN，AVG
​ 样例

#以下是查找工资表上NetPay的累计金额的示例。 记录按EmployeeNo排序，累积和计算在NetPay列上
SELECT   EmployeeNo, NetPay,  SUM(Netpay) OVER(ORDER BY EmployeeNo ROWS   UNBOUNDED PRECEDING) as TotalSalary  FROM Salary;

rank

​ 语法

RANK() OVER  ([PARTITION BY columnnlist][ORDER BY columnlist][DESC|ASC])

PARTITION BY子句按照在PARTITION BY子句中定义的列对数据进行分组，并在每个组中执行OLAP功能
​ 样例

以下查询通过加入日期对雇员表的记录进行排序，并在“加入日期”中分配排名。
SELECT EmployeeNo, JoinedDate,RANK()  OVER(ORDER BY JoinedDate) as Seniority  FROM Employee;
以下是使用PARTITION BY子句的查询，每个部门都重置了排名
SELECT EmployeeNo, JoinedDate,RANK()  OVER(PARTITION BY DeparmentNo ORDER BY JoinedDate) as Seniority  FROM Employee;

PI

主索引，分为：

• 唯一主索引（UPI）
• 非唯一主索引（NUPI）
  关键：相同PI分布在同一个AMP中

PPI

数据分区
分区主索引
建表时使用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);

SI

二级索引
​ 用于访问数据的备用路径，根据实际情况慎用
差异:
​ 次级索引不涉及数据分发
​ 二级索引值存储在子表中。这些表是在所有AMP中内置的
​ 二级索引是可选的
​ 可以在创建表期间或在创建表之后创建
​ 占用额外的空间，因为它们构建子表，并且它们还需要维护，因为需要为每个新行更新子表
​ 两种类型
​ 唯一二级指数（USI）
​ 非唯一二级指数（NUSI）

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 < EmployeeLoad.fl
​ 命令
​ LOGON - 登录到Teradata并启动一个或多个会话。
​ DATABASE - 设置默认数据库。
​ BEGIN LOADING - 标识要加载的表。
​ ERRORFILES - 标识需要创建/更新的2个错误表。
​ CHECKPOINT - 定义何时采取检查点。
​ SET RECORD - 指定输入文件格式是格式化，二进制，文本还是未格式化。
​ DEFINE - 定义输入文件布局。
​ FILE - 指定输入文件名和路径。
​ INSERT - 将输入文件中的记录插入目标表中。
​ END LOADING - 启动FastLoad的第2阶段。 将记录分发到目标表中。
​ LOGOFF - 结束所有会话并终止FastLoad。

FastExport

​ 数据导出工具
脚本样例：

.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;

执行 fexp < employee.fx
命令
​ LOGTABLE - 指定用于重新启动目的的日志表。
​ LOGON - 登录到Teradata并启动一个或多个会话。
​ DATABASE - 设置默认数据库。
​ BEGIN EXPORT - 表示导出的开始。
​ EXPORT - 指定目标文件和导出格式。
​ SELECT - 指定要导出数据的选择查询。
​ END EXPORT - 指定FastExport的结束。
​ LOGOFF - 结束所有会话并终止FastExport。

MultiLoad

性能优化

执行计划

​ 性能调整的第一步是在查询中使用EXPLAIN
​ EXPLAIN计划提供优化程序如何执行查询的详细信息
​ 在解释计划中，检查关键字，如置信度级别，使用的连接策略，假脱机文件大小，重新分配等
​ EXPLAIN命令以英语返回解析引擎的执行计划
​ 当查询前面有EXPLAIN命令时，解析引擎的执行计划将返回给用户，而不是AMP

全表扫描（FTS）

​ 当在SELECT语句中没有指定条件时，优化器可以选择在访问表的每一行时使用全表扫描
EXPLAIN SELECT * FROM employee;

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;

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.

唯一二级索引

​ 当使用唯一二级索引访问行时，它是一个双放大操作
EXPLAIN SELECT * FROM Salary WHERE EmployeeNo = 101;

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计划中常见的术语列表。
​ ... (Last Use) …
​ 不再需要假脱机文件，并且将在此步骤完成后释放。
​ ... with no residual conditions …
​ 所有适用的条件已应用于行。
​ ... END TRANSACTION …
​ 将释放事务锁，并提交更改。
​ ... eliminating duplicate rows ...
​ 重复行仅存在于假脱机文件中，而不是设置表。执行DISTINCT操作。
​ ... by way of a traversal of index #n extracting row ids only …
​ 构建了一个假脱机文件，其中包含在辅助索引（索引#n）中找到的行标识
​ ... we do a SMS (set manipulation step) …
​ 使用UNION，MINUS或INTERSECT运算符组合行。
​ ... which is redistributed by hash code to all AMPs.
​ 重新分配数据以准备加入。
​ ... which is duplicated on all AMPs.
​ 在准备加入时，从较小的表（根据SPOOL）复制数据。
​ ... (one_AMP) or (group_AMPs)
​ 表示将使用一个AMP或AMP子集而不是所有AMP。

统计优化

​ Teradata优化器提出了每个SQL查询的执行策略。 此执行策略基于在SQL查询中使用的表收集的统计信息。 使用COLLECT STATISTICS命令收集表上的统计信息。 优化器需要环境信息和数据人口统计数据来制定最佳执行策略
​ 收集在WHERE子句中使用的列以及在连接条件中使用的列上的统计信息。 收集唯一主索引列的统计信息。 收集非唯一二级索引列的统计信息。优化器将决定是否可以使用NUSI或全表扫描。 收集关于联接索引的统计信息，尽管收集了基表上的统计信息。 收集分区列上的统计信息。

语法

​ 收集语法

COLLECT [SUMMARY] STATISTICS
INDEX (indexname) COLUMN (columnname)
ON <tablename>;

例子:COLLECT STATISTICS COLUMN(EmployeeNo) ON Employee;
查看统计:HELP STATISTICS <tablename>;

其他优化方法举例

Data Types

​ 确保使用正确的数据类型。这将避免使用超过所需的过量存储。

Conversion

​ 确保连接条件中使用的列的数据类型兼容，以避免显式数据转换。

Sort

​ 删除不必要的ORDER BY子句，除非必需。

Spool Space Issue

​ 如果查询超过该用户的每个AMP卷轴空间限制，则会生成假脱机空间错误。 验证解释计划并识别占用更多假脱机空间的步骤。 这些中间查询可以拆分并单独放置以构建临时表。

Primary Index

​ 确保为表正确定义了主索引。主索引列应均匀分布数据，应经常用于访问数据。

SET Table

​ 如果定义了SET表，那么优化器将检查记录是否与插入的每个记录重复。要删除重复检查条件，可以为表定义唯一辅助索引。

UPDATE on Large Table

​ 更新大表将是耗时的。可以删除记录，并插入带有修改行的记录，而不是更新表。

Dropping Temporary Tables

​ 删除临时表（临时表）和挥发性（如果不再需要它们）。这将释放永久空间和假脱机空间。

MULTISET Table

​ 如果确定输入记录不具有重复记录，则可以将目标表定义为MULTISET表，以避免SET表使用重复行检查。

压缩

​ 减少表所使用的存储
​ 可以在使用CREATE TABLE创建表时，或使用ALTER TABLE命令创建表之后添加压缩
​ 限制：
​ 每列只能压缩255个值。
​ 主索引列不能压缩。
​ 不能压缩易失性表（临时表）。

多值压缩（MVC）