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值提取小时，分钟和秒：
  

[AppleScript] 纯文本查看 复制代码

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年添加到当前日期。

[AppleScript] 纯文本查看 复制代码

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

以下示例将3年和01个月添加到当前日期。

[AppleScript] 纯文本查看 复制代码

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

以下示例将01天，05小时和10分钟添加到当前时间戳。

[AppleScript] 纯文本查看 复制代码

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函数​     
就是管道函数
聚合​      
语法

[AppleScript] 纯文本查看 复制代码

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

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

[AppleScript] 纯文本查看 复制代码

#以下是查找工资表上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​   
数据导入工具
​    要求：目标表不应该有二级索引，连接索引和外键引用
脚本样例：

[AppleScript] 纯文本查看 复制代码

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​   
数据导出工具
脚本样例：

[AppleScript] 纯文本查看 复制代码

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

[AppleScript] 纯文本查看 复制代码

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] STATISTICSINDEX (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）

作者：八幡大老师
链接：https://www.jianshu.com/p/c40fe42060ae
来源：简书