Teradata & KADB数据库对象兼容说明(二）

• Teradata和KADB表兼容

Teradata和KADB创建表语句及表的结构差异较大，迁移时需要进行DML语句的人工修改和校验，以下为建表语句的差异对比说明及详细介绍。

	Teradata	KADB
表类型	volatile(内存中），persistent(磁盘中) MULTISET：允许数据重复 SET：不允许数据重复	persistent
数据分布	使用MAP机制	hash、random、replication
数据副本	fallback，journal	流复制
数据压缩	支持	支持
指定数据块大小	支持	支持
约束	支持	支持
主键	支持	支持
外键	支持	支持
唯一索引	支持	支持

1. Teradata表

表类型

类型Teradata支持不同类型的表。

1. 永久表 - 这是默认表，它包含用户插入的数据并永久存储数据。
2. 易失性表 - 插入到易失性表中的数据仅在用户会话期间保留。在会话结束时删除表和数据。这些表主要用于在数据变换期间保存中间数据。
3. 全局临时表 - 全局临时表的定义是持久的，但是在用户会话结束时删除表中的数据。

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

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

1. 派生表 - 派生表保存查询中的中间结果。它们的生命周期在创建，使用和丢弃它们的查询中。

Teradata支持以下表类型来保存临时数据。

1. Derived Table
2. Volatile Table

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

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;

1. Global Temporary Table
2. Derived Table：Derived Table在查询中创建，使用和删除。这些用于在查询中存储中间结果。

SELECT

Emp.EmployeeNo,

Emp.FirstName,

Empsal.NetPay

FROM

Employee Emp,

(select EmployeeNo , NetPay

from Salary

where NetPay >= 75000) Empsal

where Emp.EmployeeNo = Empsal.EmployeeNo;

设置与多重集

Teradata基于如何处理重复记录将表分类为SET或MULTISET表。定义为SET表的表不存储重复记录，而MULTISET表可存储重复记录。

1.  KADB表

KADB数据库的表与任何一种关系型数据库中的表类似，不过其表中的行被分布在系统中的不同Segment上。当用户创建一个表时，用户会指定该表的分布策略。

CREATE TABLE命令创建一个表并且定义它的结果。当用户创建一个表示，用户需要定义：

1. 该表的列以及它们的数据类型。
2. 任何用于限制列或者表中能包含的数据的表或者列约束。
3. 表的分布策略，这决定了KADB数据库如何在Segment之间划分数据。
4. 表存储在磁盘上的方式。
5. 大型表的表分区策略。

选择列的数据类型

一个列的数据类型决定了该列能包含的数据的类型。选择的数据类型应使用最少的空间，但仍能容纳用户的数据并且能最好地约束数据。例如，对字符串使用character数据类型，对于日期使用date或者timestamp数据类型，而对数字使用numeric数据类型。

对于包含文本数据的表列，应指定数据类型为 VARCHAR或者TEXT。不推荐指定数据类型为 CHAR。在KADB数据库中数据类型 VARCHAR或者TEXT会把增加到数据的填充（在最后一个非空白字符后面增加的空白字符）处理为有效字符，而数据类型CHAR不会这样做。

使用能容纳用户的数字型数据的且允许未来扩张的最小数字数据类型。例如，为适合INT or SMALLINT的数据使用BIGINT会浪费存储空间。如果用户预期用户的数据值将会随着时间扩张，应该考虑到在装载大量数据后从较小的数据类型更改成较大的数据类型需要很大的代价。例如，如果用户当期的数据值适合SMALLINT，但是很可能值会扩张，这样INT就是更好的长期选择。

对用户计划要用在交叉表连接中的列使用相同的数据类型。交叉表连接通常使用一个表中的主键和其他表中的外键。当数据类型不同时，数据库必须转换其中之一以便数据值能被正确地比较，这会增加不必要的开销。

设置表和列约束

用户可以在列和表上定义约束来限制表中的数据。KADB数据库支持和PostgreSQL相同的约束，但是有一些限制，包括：

1. CHECK约束只能引用它所在的表。
2. UNIQUE和PRIMARY KEY约束必须和它们所在表的分布键和分区键（如果有）兼容。
3. 注意： 在追加优化表上不允许UNIQUE和PRIMARY KEY约束，因为追加优化表上不允许这些约束创建的UNIQUE索引。
4. 允许FOREIGN KEY约束，但不会被强制。
5. 用户在分区表上定义的约束将作为整体应用到分区表上。用户不能在该表的单独的部分上定义约束。

1. 检查约束

检查约束允许用户指定一个特定列中的值必须满足一个布尔（真值）表达式。例如，要求正的产品价格：

=> CREATE TABLE products 

            ( product_no integer, 

              name text, 

              price numeric CHECK (price > 0) );

1. 非空约束

非空约束指定一个列不能有空值。非空约束总是被写作为列约束。例如：

=> CREATE TABLE products 

       ( product_no integer NOT NULL,

         name text NOT NULL,

         price numeric );

1. 唯一约束

唯一约束确保一列或者一组列中包含的数据对于表中所有的行都是唯一的。该表必须是哈希分布（非DISTRIBUTED RANDOMLY）的，并且约束列必须是该表的分布键列（或者是一个超集）。例如：

=> CREATE TABLE products 

       ( product_no integer UNIQUE, 

         name text, 

         price numeric)

      DISTRIBUTED BY (product_no);

1. 主键

主键约束是一个UNIQUE约束和一个 NOT NULL约束的组合。该表必须是哈希分布（非DISTRIBUTED RANDOMLY）的，并且约束列必须是该表的分布键列（或者是一个超集）。如果一个表具有主键，这个列（或者这一组列）会被默认选中为该表的分布键。例如：

=> CREATE TABLE products 

       ( product_no integer PRIMARY KEY, 

         name text, 

         price numeric)

      DISTRIBUTED BY (product_no);

1. 外键

不支持外键。用户可以声明它们，但是参照完整性不会被实施。

外键约束指定一列或者一组列中的值必须匹配出现在另一个表的某行中的值，以此来维护两个相关表之间的参照完整性。参照完整性检查不能在一个KADB数据库的分布表段之间实施。

选择表分布策略

所有的KADB数据库表都会被分布。当用户创建或者修改一个表时，用户可以有选择地指定DISTRIBUTED BY（哈希分布）或者 DISTRIBUTED RANDOMLY（循环分布）来决定该表的行分布。

注意：如果创建表时没有指定DISTRIBUTED BY，KADB数据库服务器配置参数gp_create_table_random_default_distribution控制表的分布策略。

在决定表分布策略时，请考虑以下几点。

1. 均匀数据分布 — 为了最好的性能，所有的Segment应该包含等量的数据。如果数据不平衡或者倾斜，具有更多数据的Segment就必须做更多工作来执行它那一部分的查询处理。请选择对于每一个记录都唯一的分布键，例如主键。
2. 本地和分布式操作 — 本地操作比分布式操作更快。在Segment层面上，如果与连接、排序或者聚集操作相关的工作在本地完成，查询处理是最快的。在系统层面完成的工作要求在Segment之间分布元组，其效率会低些。当表共享一个共同的分布键时，在它们共享的分布键列上的连接或者排序工作会在本地完成。对于随机分布策略来说，本地连接操作就行不通了。
3. 均匀查询处理 — 为了最好的性能，所有的Segment应该处理等量的查询负载。如果一个表的数据分布策略与查询谓词匹配不好，查询负载可能会倾斜。例如，假定一个销售事务表按照客户ID列（分布键）分布。如果查询中的谓词引用了一个单一的客户ID，该查询处理工作会被集中在一个Segment上。

声明分布键

CREATE TABLE的可选子句DISTRIBUTED BY和DISTRIBUTED RANDOMLY指定一个表的分布策略。默认是使用PRIMARY KEY（如果表有主键）或者表的第一个列作为分布键的哈希分布策略。几何或者用户定义数据类型的列不能作为KADB分布键列。如果一个表没有符合要求的列，KADB会以随机或者循环方式分布行。

为了确保数据的均匀分布，应该选择对每个记录都唯一的分布键。如果做不到，可选择DISTRIBUTED RANDOMLY。例如：

=> CREATE TABLE products

  (name varchar(40),

  prod_id integer,

  supplier_id integer)

  DISTRIBUTED BY (prod_id);

=> CREATE TABLE random_stuff

  (things text,

  doodads text,

  etc text)

  DISTRIBUTED RANDOMLY;

重要：主键总是表的分布键。如果不存在主键，但是存在唯一键，那么唯一键就是该表的分布键。

• 结论

本文从数据类型、函数和DDL语句三个方面说明Teradata和KADB两款产品在迁移时的兼容性。同作为MPP架构的两款产品理论上可以使用KADB作为Teradata的替代，以支撑数仓、BI、数据分析、批量加工等场景。但因为两款产品在底层存储实现、表设计等方面的异构特性，在进行迁移和替代时需要进行详细的调研、测试（应用和数据库）。本文从数据库对象的角度对比两款产品的兼容情况，结论如下：

1. 数据类型：KADB兼容Teradata绝大多数的数据类型，但不支持period数据类型，建表DDL语句在进行移植时需要人工介入进行修改和测试
2. 函数：KADB兼容Teradata的多大多数函数，但存在个别不常用函数不兼容的情况，在进行数据库迁移前需要对源库使用的函数进行统计适配，改写。必要时创建自定义函数做替代。
3. 建表DDL：KADB和Teradata同时兼容ANSI SQL:2011标准，但Teradata对该标准进行了扩展，扩展部分功能KADB不兼容。所以在做数据库替换时建表DDL需人工进行调整，以适应KADB数据库环境。调整后的SQL需要进行详细验证测试
4. 存储过程：Teradata支持存储过程，KADB不支持存储过程。在进行替换时需要调研源库是否使用了存储过程，在KADB可以使用函数替代存储过程
5. 触发器：Teradata支持触发器，KADB不支持触发器。在进行替换时需要调研源库是否使用了触发器。对触发器的替换需应用进行改造
6. 行级安全策略：Teradata支持行级安全策略，KADB不支持行级安全策略。在进行替换时需要调研源库是否使用了行级安全策略。对行级安全策略的替换需应用进行改造