TPC-DS标准规范(2)

TPC-DS是一套决策支持系统测试基准，主要针对零售行业。提供99个SQL查询（SQL99或2003），分析数据量大，测试数据与实际商业数据高度相似，同时具有各种业务模型（分析报告型，数据挖掘型等等）。国内目前相关的翻译文章较少。本文尝试对官网的TPC BENCHMARK DS Standard Specification（下称“原文”）进行翻译。翻译主要参照的是2017年发布的2.6.0版本。

由于原文一共137页，本文在翻译的时候会进行一定的压缩，突出较为关键的信息。本文章节名称，序号，小标题等均严格按照原文翻译排序。

2 逻辑数据库设计

2.1 Schema概述

TPC-DS的schema对销售及退货流程进行了建模。包括销售三大渠道：商店，目录，以及网络。Schema包含七个事实表：

1. 负责三大渠道中，每项的产品销售与退货事实表共六个

2. 一个针对目录及网络销售的清单建模的事实表

除此之外，schema中还包含了17个维度表。这些维度表与所有销售渠道关联，接下来将逐一阐述它们的以下几项设计逻辑：

1. 表名及其缩写

2. 每个事实表与相关的维度表之间的逻辑图

3. 每个表及它与其他表之间的关系的高级定义

4. 每一列的规格与基数的信息

2.2 列的定义

2.2.1 列名

2.2.1.1 列名之间不能重复，且以所在表的缩写为列名开头

2.2.1.2 该列如果是表的主键列或部分主键，则命名为Primary Key。如果这个表使用的是复合主键，那么该列列名后面要跟括号，括号内标注其序号。

2.2.1.3 该列如果是业务键的一部分，则列名后面要有“(B)”。业务键在数据仓库的schema中既不是主键，也不是外键，仅仅是为了在数据维护时，将新数据与源表的更新数据进行区别。

2.2.2 数据类型

2.2.2.1 每列使用的数据都是以下数据类型中的一种：

a) Indentifier，生成的任何该列键值都能被这一列使用

b) Integer，该列可以准确表示任何整数，值域为-2^63到2^63-1

c) Decimal(d, f)，该列可以精确表示范围内任何小数，范围是整数加小数最多d位，小数点后一共f位

d) Char(N)，该列可以表示固定长度为N的字符串。不足N的后面自动补空格存储，或者在检索时自动补空格以保证CHAR_LENGTH()函数返回值为N。

e) Varchar(N)，该列可以表示最大长度为N的字符串。Varchar(N)可以转char(N)。

f) Date，日期范围1900年1月1日到2199年12月31日

2.2.2.2 本基准的数据类型不与任何特定SQL的数据类型相对应，因此给出了上述定义，阐明了每个数据类型的特点。实现基准时，可以视情况用符合对应条件的SQL数据类型。

2.2.2.3 除标识符外，使用者如果选择某种数据类型V实现了基准中的一种数据类型T，则基准中所有相同数据类型T的实例都要由V来实现。

2.2.3 NULLs

如果NULL列中包含“N”，则每行都会填入所有比例因子。如果该字段为空，则此列可能包含NULL。

2.2.4 外键

如果表内某列的值与其他表的某列相关联，则外部列的名称将出现在“外键”字段中。

2.3 事实表定义

2.3.1 商店销售(SS)

2.3.1.1 商店销售ER图

2.3.1.2 商店销售列定义

表中每行代表一个单独的订单项，这些订单项都是通过店铺渠道销售的，并且记录在事实表store_sales中。

2.3.2 商店退货(SR)

2.3.2.1 商店退货ER图

2.3.2.2 商店退货列定义

表中每行代表一个单独的退货项，这些退货项都是通过店铺渠道销售的，并且记录在事实表store_returns中。

2.3.3 目录销售(CS)

2.3.3.1 目录销售ER图

2.3.3.2 目录销售列列定义

表中每行代表一个单独的订单项，这些订单项都是通过目录渠道销售的，并且记录在事实表catalog_sales中。

2.3.4 目录退货(CR)

2.3.4.1 目录退货ER图

2.3.4.2 目录退货列定义

表中每行代表一个单独的退货项，这些退货项都是通过目录渠道销售的，并且记录在事实表catalog_returns中。

2.3.5 网络销售(WS)

2.3.5.1 网络销售ER图

2.3.5.2 网络销售列定义

表中每行代表一个单独的订单项，这些订单项都是通过网络渠道销售的，并且记录在事实表web_sales中。

2.3.6 网络退货(WR)

2.3.6.1 网络退货ER图

2.3.6.2 网络退货列定义

表中每行代表一个单独的退货项，这些退货项都是通过网络渠道销售的，并且记录在事实表web_returns中。

2.3.7 库存(INV)

2.3.7.1 库存ER图

2.3.7.2 库存列定义

表中每行表示每周特定仓库中，该商品的数量。

2.4 维度表定义

2.4.1 Store(S)

该维度表中每行显示一个商店的细节信息。

2.4.2 Call Center(CC)

该维度表中每行显示一个呼叫中心的细节信息。

2.4.3 Catalog_page(CP)

该维度表中每行显示一个目录页的细节信息。

2.4.4 Web_site(WEB)

该维度表中每行显示一个网站的细节信息。

2.4.5 Web_page(WP)

该维度表中每行显示一个网页的细节信息。

2.4.6 Warehouse(W)

该维度表中代表显示一个存货仓库的信息。

2.4.7 Customer(C)

该维度表中每行代表一个顾客的信息。

2.4.8 Customer_address(CA)

该维度表中每行代表一个唯一的顾客地址信息（有些顾客会有不止一个地址）。

2.4.9 Customer_demographics(CD)

顾客人群统计表中，有一行是用来显示特定的人群信息组合。

The customer demographics table contains one row for each unique combination of customer demographic
information

2.4.10 Date_dim(D)

该表中，每一行代表一个公历日。该行的儒略日可以用作代理关键字(d_date_sk)。

2.4.11 Household_demographics(HD)

该表中，每一行表示一个家庭人群状况。

2.4.12 Item(I)

该表中，每一行表示一个特定产品的构成（例如，尺寸，颜色，制造商等）。

2.4.13 Income_band(IB)

该表中的每一行表示一个收入范围的信息。

2.4.14 Promotion(P)

该表中，每行表示一个特定商品的促销信息（例如广告，销售，公关）。

2.4.15 Reason(R)

此表中的每一行表示一个被退货的商品的退货原因。

2.4.16 Ship_mode(SM)

此表中的每一行表示一种运送模式。

2.4.17 Time_dim(T)

该表中每行表示一秒。

2.4.18 dsdgen_version

基准测试时不会用到这个表，dsdgen会生成一个flat file（见附录F），这个文件可以确保你在使用时，当前的数据集是由正确版本的TPC-DS搭建的。

2.5 实现需求

2.5.1 术语定义

2.5.1.1 在2.2和2.3中定义的表被称为基表(base tables)，由dsdgen生成的flat file数据与每个基表对应并被加载到每个基表中称为基表数据，包含基表数据的结构被称为基表数据结构。

2.5.1.2 除了基表数据结构之外，任何数据库结构，如果含有基表数据的复制、对基表数据的引用、或由基表数据计算得到的数据，都属于辅助数据结构(ADS)。ADS中的数据来自于基表，可以通过引用的方式实现ADS。基表数据结构中的数据，与其ADS中的数据，有着本质区别。ADS由于是对基表数据结构的一种复制或引用，因此对ADS做的删除并不会作用到基表数据结构上，只有在基表数据结构上做的删除才会导致基表自身的数据丢失。

2.5.1.3 ADS有两种类型：显性ADS(EADS)与隐形ADS(IADS)。EADS的创建是一些指令导致的结果（例如DDL，会话选项，全局配置参数），这些指令被称为EADS指令。EADS不会在没有指令出现的情况下被创建。不属于EADS的ADS就被定义为IADS了。

2.5.1.4 将表或EADS中的行，分配给不同的文件、磁盘、或区域的过程，被称为水平分区(horizontal partitioning)。

2.5.1.5 将列分配给不同的文件、磁盘、或区域的过程，就是垂直分区(vertical partitioning)。

2.5.1.6 主键是唯一标志行的一个或多个列。主键列不管有几列，里面的值都不能为空，而且一个表最多只能有一个主键，即使有多个主键列也只能有一个主键存在。

2.5.1.7 外键是用于在两个表中的数据之间建立链接的列或列的组合。通过将a表的主键列添加到b表中，为两个表建立连接，则a表的主键列为b表中的外键。因此一个表的外键实际上就是别的表的主键。

2.5.1.8 主键和外键的定义灵活。

2.5.1.9 本规范提及的主键和外键，指的是在第2.3和2.4节中定义的主键和外键。

2.5.2 数据处理系统与表

2.5.2.1 数据处理系统应使用通用的系统进行实现(DBMS)。

2.5.2.2 用于实现逻辑schema定义的，SQL数据定义语句和相关脚本，被定义为DDL。

2.5.2.3 进行查询验证测试的数据库被定义为资格数据库(qualification database)。

2.5.2.4 进行性能报告的数据库被定义为测试数据库(test database)。

2.5.2.5 如果一个聚类不会改变表之间的逻辑关系，则可以对数据库中不同表进行聚类。

2.5.2.6 表名应符合2.3以及2.4中的规定。如果数据处理系统不支持符合规定的命名，那么可以对名称进行修改，并且保证：

1. 名称改变很小

2. 名称的改变不会对性能造成影响

3. 根据4.2.3对查询集进行修改

2.5.2.7 根据第2.3条和第2.4条列出的每个表格，都应按照以上定义来实现列。

2.5.2.8 列名应符合2.3以及2.4中的规定。如果数据处理系统不支持符合规定的命名，那么可以对名称进行修改，并且保证：

1. 名称改变很小

2. 遵循实现基准的系统中，使用记录的命名约定

3. 名称的改变不会对性能造成影响

4. 根据4.2.3对查询集进行修改

2.5.2.9 给定表格中的列可以以任何顺序实现，但是表定义中列出的所有列都应该被实现，只是不必都添加到表中。

2.5.2.10 第2.3和2.4条定义的每个列应为离散列，可以由数据处理系统独立访问。特别注意：

1. 列不能被合并。比如不能将C_LOGIN和C_EMAIL_ADDRESS合并为一个C_DATA实现。

2. 列不能被分裂。比如P_TYPE不能以两个离散列P_TYPE_SUBSTR1和P_TYPE_SUBSTR2的方式实现。

2.5.2.11 数据库应允许插入符合列数据类型，且符合约束条件的任意数据值。约束条件需遵循第2.5.4条定义。

2.5.3 显性辅助数据结构(EADS)

2.3.5.1 除本节规定外，禁止复制数据库对象（即表，行或列）。

2.3.5.2 如果一个EADS不包括从Catalog_Sales或Catalog_Returns实现的数据，则此EADS受以下限制：

1) 它可以实现的数据来自最多一个基表。

2) 它可能会实现以下全部或部分内容：

1. 如果PK是复合键，则可以实现主键或PK列的任何子集

2. 实现对相应基表的行的引用或指针（例如“行ID”）

3. 实现以下最多一个：

a) 如果FK是复合键，则可以实现外键或FK列的任何子集

b) 实现具有日期数据类型的列

c) 实现业务键列

2.3.5.3 如果一个EADS包含从Catalog_Sales或Catalog_Returns的数据，那么可能不包括Store_Sales，Store_Returns，Web_Sales，Web_Returns或Inventory中的任何数据。

2.3.5.4 如果一个EADS的数据来源于事实表和维度表，则此EADS必须有FK-PK相关列。

2.3.5.5 除非是2.5.3.2中许可的特殊情况，否则实现如果EADS的数据来自一个或多个维度表，必须同时实现Catalog_Sales和/或Catalog_Returns的数据的EADS。实现来自一个或多个维度表的数据的EADS时，必须为每个事实表行实现至少一个维度行，除非维度行的外键值为空。

2.3.5.6 在以下情况时，实现EADS时可以对基表数据复制：

1. 所有复制数据由用于基准测试的系统管理

2. 所有复制对所有数据操作都是透明的

2.5.3.7 所有EADS的创建必须包含在数据库加载测试中（见7.4.3）。性能测试期间可能不会创建或删除EADS。

2.5.3.8 分区

2.5.3.8.1 逻辑表空间(logical table space)是指，逻辑相邻且不可被分的实体存储设备的集合。

2.5.3.8.2 DDL的语法，可以实现将表存储在特定逻辑表空间中。

2.5.3.8.3 基表或EADS可以水平分区。如果分区是表或ADS中数据的函数，则分配应基于分区列中的值。只能使用主键，外键，日期列和日期代理键作为分区列。如果分区DDL为分区列指定了显式分区值，则它们应满足以下条件：

1. 它们不关注存储在分区列中的数据，仅仅关注这些列的最小值和最大值以及列的数据类型。

2. 它们将定义每个分区的范围，该分区可以容纳在最小值到最大值之间的所有值。

3. 对于基于日期的分区，可以根据天，周，月或年的整数粒度分配到相同的域中，所有这些都使用公历（例如30天，4周，1个月，1年等）。对于日期以外的基于日期的分区粒度，分区边界可能会超出该表的数据特征中确定的最小或最大边界，可见第3.4节。

4. 可以给分区列中插入超出最小值/最大值范围的值，见1.5。

如果使用任何指令或DDL进行水平分区，则应显示所需的指令、DDL、以及其他详细信息。

仅当每级分区满足上述条件时才允许基本表或ADS进行多级分区。

2.5.3.8.4 当满足以下所有要求时，可以对基表或EADS垂直分区：

1. SQL DDL禁止数据显性垂直分区。

2. SQL DDL的分区指令不能影响数据的物理存放位置。

3. 逻辑上，行必须体现为列内元素的集合。

这意味着不需要显性分区指令的垂直分区是允许的。显性分区指令是将一行中的列分配到文件，磁盘或不同于存储该行中其他列的区域的区域。

2.5.4 约束

2.5.4.1 可以使用强制约束或非强制约束。如果使用约束条件，应满足以下要求：

1. 强制约束应在声明(statement)或交易(transaction)级执行。

2. 在加载测试之后和性能测试之前，必须验证非强制约束。

3. 它们仅限于主键，外键和NOT NULL约束。

4. 可以对EADS和表使用NOT NULL约束。只有逻辑表定义中标记为“N”的列（或从这些列导出的EADS中的列）可以使用NOT NULL约束。

2.5.4.2 如果定义和执行外键约束，当执行约束（例如，ANSI SQL RESTRICT，CASCADE，NO ACTION）时，没有特殊的删除/更新操作的要求。

2.6 数据访问透明度要求

2.6.1 数据访问透明度是一个系统属性，可以从查询文本中删除任何分区数据。尽可能多的测试可以保证系统的数据访问透明度。下面列出了实现透明数据访问系统所需的最低配置，使用水平分区的基准应满足第2.6.2和2.6.3条所述的透明数据访问要求。

2.6.2 第2.3和2.4条中的每个表格，都可以通过名称区分，这些名称与分区无关。EQT中，所有数据操作（见第3条）仅可使用这些名称。

2.6.3 使用符合第2.6.2条的名称，任何非TPC-DS的查询都应能够引用任何一行或列，即：

1. 可以被基础系统支持的任意条件识别

2. 使用第2.6.2条中描述的名称，并对所有表使用相同的数据操作语义和语法

例如，用于查询任何一个表中的任意一行的语义和语法在查询任何其他表中的另一个任意行时也可以使用。此条主要为了TPC-DS的查询在访问数据库中的数据时具有通用性。