通过构建TPC-H数据集之上的宽表说明TIS 数据流分析（EMR）的使用方式

[baisui1981]

TIS的数据流分析（EMR） 功能基本可用，需要结合TPC-H（https://www.tpc.org/tpch/default5.asp） 通过数据流分析（EMR）来构建离线T+1 宽表供业务系统使用

相关文档

1. https://help.aliyun.com/zh/hologres/user-guide/test-plan#li-fiz-p0h-7uq
2. https://www.tpc.org/tpch/default5.asp

宽表构建

以下是两个基于 TPC-H 数据集的 Hive 宽表构建示例，覆盖典型的业务分析场景（如订单分析、供应链分析）。假设 TPC-H 的原始表已通过 Hive 外部表或导入方式存储在 Hive 中。

---

示例 1：订单-客户-地理宽表

目标

将 orders、lineitem、customer、nation、region 表整合为一个大宽表，用于分析 订单详情、客户信息及地理分布。

Hive SQL

-- 创建宽表（使用 ORC 格式优化性能）
CREATE TABLE IF NOT EXISTS order_customer_wide
STORED AS ORC
AS
SELECT
  o.O_ORDERKEY AS order_key,
  o.O_ORDERSTATUS AS order_status,
  o.O_TOTALPRICE AS total_price,
  o.O_ORDERDATE AS order_date,
  o.O_ORDERPRIORITY AS order_priority,
  l.L_PARTKEY AS part_key,
  l.L_SUPPKEY AS supplier_key,
  l.L_QUANTITY AS quantity,
  l.L_EXTENDEDPRICE AS extended_price,
  l.L_DISCOUNT AS discount,
  l.L_TAX AS tax,
  l.L_SHIPDATE AS ship_date,
  c.C_NAME AS customer_name,
  c.C_ADDRESS AS customer_address,
  c.C_PHONE AS customer_phone,
  c.C_ACCTBAL AS account_balance,
  c.C_MKTSEGMENT AS market_segment,
  n.N_NAME AS nation_name,
  r.R_NAME AS region_name
FROM
  orders o
JOIN
  lineitem l ON o.O_ORDERKEY = l.L_ORDERKEY
JOIN
  customer c ON o.O_CUSTKEY = c.C_CUSTKEY
JOIN
  nation n ON c.C_NATIONKEY = n.N_NATIONKEY
JOIN
  region r ON n.N_REGIONKEY = r.R_REGIONKEY;

宽表字段说明

• 订单核心字段: order_key, order_status, total_price, order_date
• 订单项细节: part_key, supplier_key, quantity, extended_price 等
• 客户信息: customer_name, account_balance, market_segment
• 地理信息: nation_name, region_name

典型分析场景

1. 按区域统计订单总额：SELECT region_name, SUM(total_price) FROM order_customer_wide GROUP BY region_name
2. 客户分群（市场细分 + 区域）：SELECT market_segment, region_name, COUNT(DISTINCT customer_name) FROM ... GROUP BY ...

---

示例 2：供应链-零件-供应商宽表

目标

整合 part、partsupp、supplier、nation、region 表，用于分析 零件供应成本、供应商地域分布。

Hive SQL

-- 创建宽表（使用分区优化查询，按 p_type 分区）
CREATE TABLE IF NOT EXISTS supply_chain_wide
PARTITIONED BY (p_type STRING)
STORED AS ORC
AS
SELECT
  p.P_PARTKEY AS part_key,
  p.P_NAME AS part_name,
  p.P_MFGR AS manufacturer,
  p.P_BRAND AS brand,
  p.P_SIZE AS size,
  p.P_CONTAINER AS container,
  p.P_RETAILPRICE AS retail_price,
  ps.PS_SUPPKEY AS supplier_key,
  ps.PS_AVAILQTY AS available_quantity,
  ps.PS_SUPPLYCOST AS supply_cost,
  s.S_NAME AS supplier_name,
  s.S_ADDRESS AS supplier_address,
  s.S_PHONE AS supplier_phone,
  n.N_NAME AS supplier_nation,
  r.R_NAME AS supplier_region,
  p.P_TYPE AS p_type  -- 用作分区字段
FROM
  part p
JOIN
  partsupp ps ON p.P_PARTKEY = ps.PS_PARTKEY
JOIN
  supplier s ON ps.PS_SUPPKEY = s.S_SUPPKEY
JOIN
  nation n ON s.S_NATIONKEY = n.N_NATIONKEY
JOIN
  region r ON n.N_REGIONKEY = r.R_REGIONKEY;

宽表字段说明

• 零件信息: part_key, part_name, manufacturer, retail_price
• 供应关系: supplier_key, available_quantity, supply_cost
• 供应商地理: supplier_nation, supplier_region
• 分区字段: p_type（按零件类型分区，加速查询）

典型分析场景

1. 按区域统计供应商平均供应成本：SELECT supplier_region, AVG(supply_cost) FROM supply_chain_wide GROUP BY supplier_region
2. 高成本零件筛选：SELECT part_name, supplier_name, supply_cost FROM supply_chain_wide WHERE supply_cost > 1000

---

关键设计点

1. 数据冗余与查询效率

   • 宽表通过冗余存储减少 JOIN 操作，适合 OLAP 场景，但需权衡存储成本。
   • 使用 ORC 格式 + 压缩（如 SNAPPY）优化存储和查询性能。

2. 分区策略

   • 按高频过滤字段（如 p_type、order_date）分区，加速查询。

3. 字段命名规范

   • 对同名字段（如 N_NAME）添加前缀（如 supplier_nation），避免歧义。

4. 数据一致性

   • 确保原始表外键关联正确（如 partsupp 必须关联到有效的 supplier）。

---

注意事项

1. 数据生成与导入

   • 若 TPC-H 数据在 MySQL 中，需先导出为 CSV，再通过 Hive 的 LOAD DATA 或 hdfs put + 外部表加载。

2. 性能调优

   • 调整 Hive 参数：set hive.exec.parallel=true;（并行执行）
   • 对大表启用 MapJoin：set hive.auto.convert.join=true;

3. 宽表更新

   • TPC-H 是静态数据集，无需更新。若需增量更新，可结合 Hive 事务表（ACID 特性）。

---

通过这两个宽表，可覆盖 TPC-H 中 80% 的典型分析场景，同时减少复杂 JOIN 带来的性能开销。

TPC-H 在MySQL之上安装

使用 TPC-H 生成 MySQL 表需要以下步骤，分为 生成测试数据 和 导入到 MySQL 两部分：

---

一、生成 TPC-H 测试数据

以下是基于 TPC-H 构建 MySQL 测试数据库的详细步骤：

---

1. 下载并解压 TPC-H 工具包

wget https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp -O TPC-H.zip
unzip TPC-H.zip -d TPC-H
cd TPC-H

---

2. 编译数据生成工具 dbgen

• 安装依赖：

  # Ubuntu/Debian
  sudo apt-get install build-essential gcc make
  
  # CentOS/RHEL
  sudo yum install gcc make
  

• 编译 dbgen：

  cd dbgen
  cp makefile.suite makefile
  

  编辑 makefile，修改以下配置：

  CC      = gcc
  DATABASE= MYSQL
  MACHINE = LINUX
  WORKLOAD = TPCH
  

  编译：

  make
  

执行过程会出错

在 TPC-H V3.0.1 目录的dbgen 目录中 执行make 命令 ，报告以下错误信息：
qgen.c: 在函数‘qsub’中:
qgen.c:175:22: 错误：‘SET_ROWCOUNT’未声明(在此函数内第一次使用)
         fprintf(ofp, SET_ROWCOUNT, rowcnt);
                      ^
qgen.c:175:22: 附注：每个未声明的标识符在其出现的函数内只报告一次
qgen.c:191:45: 错误：‘START_TRAN’未声明(在此函数内第一次使用)
                         fprintf(ofp,"%s\n", START_TRAN);
                                             ^
qgen.c:197:38: 错误：‘SET_DBASE’未声明(在此函数内第一次使用)
                         fprintf(ofp, SET_DBASE, db_name);
                                      ^
qgen.c:203:45: 错误：‘END_TRAN’未声明(在此函数内第一次使用)
                         fprintf(ofp,"%s\n", END_TRAN);
                                             ^
qgen.c:218:54: 错误：‘SET_OUTPUT’未声明(在此函数内第一次使用)
                         fprintf(ofp,"%s '%s/%s.%d'", SET_OUTPUT, osuff,
                                                      ^
qgen.c:235:46: 错误：‘GEN_QUERY_PLAN’未声明(在此函数内第一次使用)
                         fprintf(ofp, "%s\n", GEN_QUERY_PLAN);

问题原因

这些错误是由于 TPC-H 的 qgen 工具在生成查询时依赖的数据库特定宏（如 SET_ROWCOUNT、START_TRAN 等）未在 MySQL 配置中定义。这些宏通常用于适配不同数据库的语法（如 SQL Server 的事务命令），但 MySQL 不需要它们，因此需要手动禁用或调整代码。

---

解决方案

步骤 1：编辑 tpcd.h 头文件

在 dbgen 目录中找到 tpcd.h 头文件，添加缺失的宏定义：

cd TPC-H/dbgen
vim tpcd.h  # 或使用其他编辑器

在文件末尾添加以下内容：

/* MySQL 不需要这些宏，直接定义为空 */
#define SET_ROWCOUNT ""
#define START_TRAN  ""
#define SET_DBASE   ""
#define END_TRAN    ""
#define SET_OUTPUT  ""
#define GEN_QUERY_PLAN ""

保存并退出。

---

步骤 2：修改 qgen.c 代码（可选）

如果仍然报错，可以注释掉相关代码行。例如：

// 在 qgen.c 中找到以下代码并注释：
// fprintf(ofp, SET_ROWCOUNT, rowcnt);       // 第175行附近
// fprintf(ofp,"%s\n", START_TRAN);          // 第191行附近
// fprintf(ofp, SET_DBASE, db_name);        // 第197行附近
// fprintf(ofp,"%s\n", END_TRAN);           // 第203行附近
// fprintf(ofp,"%s '%s/%s.%d'", SET_OUTPUT, osuff, ...);  // 第218行附近
// fprintf(ofp, "%s\n", GEN_QUERY_PLAN);    // 第235行附近

---

步骤 3：重新编译

清理之前的编译结果并重新编译：

make clean
make

---

---

3. 生成测试数据

• 生成数据文件（.tbl）：

  # 生成 1GB 数据（调整 -s 参数控制大小，如 -s 10 生成 10GB）
  ./dbgen -s 1 -f
  

  会生成 customer.tbl, orders.tbl, lineitem.tbl 等文件。

---

4. 创建 MySQL 数据库

-- 登录 MySQL
mysql -u root -p

-- 创建数据库
CREATE DATABASE tpch;
USE tpch;

---

5. 创建表结构

• 从 TPC-H 工具包中获取 DDL 脚本（dss.ddl），并适配 MySQL 语法：

  CREATE TABLE nation (
    n_nationkey  INTEGER PRIMARY KEY,
    n_name       CHAR(25),
    n_regionkey  INTEGER,
    n_comment    VARCHAR(152)
  );
  
  -- 类似地创建其他表（region, part, supplier, partsupp, customer, orders, lineitem）
  

---

6. 转换并导入数据

• 转换 .tbl 文件为 MySQL 兼容格式：

  sed -i 's/|$//' *.tbl  # 删除每行末尾的 | 分隔符
  

• 使用 LOAD DATA 导入数据：

  -- 示例：导入 nation 表
  LOAD DATA LOCAL INFILE 'nation.tbl'
  INTO TABLE nation
  FIELDS TERMINATED BY '|'
  LINES TERMINATED BY '\n';
  
  -- 重复导入其他表数据
  

---

7. 添加索引与约束

-- 示例：为 orders 表添加主键
ALTER TABLE orders ADD PRIMARY KEY (o_orderkey);

-- 为 lineitem 添加外键
ALTER TABLE lineitem 
  ADD FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey);

---

8. 验证数据完整性

-- 检查表行数
SELECT COUNT(*) FROM lineitem;
-- 预期结果：6,001,215 行（-s 1 时）

---

常见问题解决

• 权限问题：

  chmod +x dbgen
  

• MySQL 安全模式限制： 在 my.cnf 中添加：

  [mysqld]
  secure_file_priv = ""
  

• 日期格式问题： 修改 dss.ddl 中的日期字段类型为 DATE。

---

9. 运行 TPC-H 查询

• 使用 qgen 工具生成查询（需额外编译）：

  ./qgen -s 1 > queries.sql
  

• 在 MySQL 中执行生成的 SQL。

---

通过以上步骤，您将获得一个完整的 TPC-H 测试数据库。可根据硬件资源调整 -s 参数生成不同规模的数据集。

二、创建 MySQL 表结构

1. 调整 TPC-H 的 DDL 脚本

   • TPC-H 的默认 DDL 可能包含非 MySQL 语法（如 DISTRIBUTED BY）。修改 dss.ddl（示例）：

     CREATE TABLE CUSTOMER (
       C_CUSTKEY     INTEGER NOT NULL,
       C_NAME        VARCHAR(25) NOT NULL,
       C_ADDRESS     VARCHAR(40) NOT NULL,
       C_NATIONKEY   INTEGER NOT NULL,
       C_PHONE       CHAR(15) NOT NULL,
       C_ACCTBAL     DECIMAL(15,2) NOT NULL,
       C_MKTSEGMENT  CHAR(10) NOT NULL,
       C_COMMENT     VARCHAR(117) NOT NULL,
       PRIMARY KEY (C_CUSTKEY)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     

   • 同理修改其他表（orders, lineitem, part, supplier, partsupp, nation, region）。

2. 在 MySQL 中执行 DDL

   mysql -u root -p
   

   CREATE DATABASE tpch;
   USE tpch;
   SOURCE /path/to/modified_dss.ddl;
   

---

三、导入数据到 MySQL

1. 处理 .tbl 文件

   • 确保数据文件的分隔符是 |，且末尾有分隔符（MySQL 的 LOAD DATA 需要）。
   • 可选：将 .tbl 转换为 .csv（若需要）：

     sed 's/|$//' customer.tbl > customer.csv
     

2. 使用 LOAD DATA 导入

   -- 示例：导入 customer 表
   LOAD DATA INFILE '/path/to/customer.tbl'
   INTO TABLE customer
   FIELDS TERMINATED BY '|'
   LINES TERMINATED BY '\n';
   

   • 重复此步骤导入所有表。

---

四、验证数据

1. 检查行数

   SELECT COUNT(*) FROM customer;
   -- 预期结果：150,000 行（当 -s 1 时）
   

2. 检查外键约束

   SHOW CREATE TABLE orders;  -- 确保外键如 O_CUSTKEY 正确关联
   

---

常见问题

1. 权限问题

   • 确保 MySQL 用户有 FILE 权限：

     GRANT FILE ON *.* TO 'user'@'localhost';
     

   • 启动 MySQL 时添加 --local-infile=1：

     mysql --local-infile=1 -u root -p
     

2. 日期格式

   • 如果日期报错，使用 STR_TO_DATE：

     LOAD DATA INFILE '/path/to/orders.tbl'
     INTO TABLE orders
     FIELDS TERMINATED BY '|'
     (O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE, ..., @O_ORDERDATE)
     SET O_ORDERDATE = STR_TO_DATE(@O_ORDERDATE, '%Y-%m-%d');
     

---

通过以上步骤，你可以在 MySQL 中成功生成 TPC-H 表并导入测试数据。如果需要更小规模的数据测试，可调整 -s 参数（如 -s 0.1 生成 100MB 数据）。

[baisui1981]

导入过程，可以构建一个shell脚本，一键搞定：

#!/bin/bash

# 配置参数（根据实际情况修改）
DB_NAME="tpch"         # 数据库名
MYSQL_USER="root"      # MySQL 用户名
TBL_DIR="./dbgen"      # .tbl 文件所在目录（默认在dbgen目录下）

# 1. 清理 .tbl 文件格式（删除末尾的 |）
echo "[1/3] 清理 .tbl 文件格式..."
cd "$TBL_DIR" || exit 1
sed -i 's/|$//' *.tbl  # 删除每行末尾的 |
echo "✅ .tbl 文件格式处理完成！"

# 2. 生成 MySQL LOAD DATA 导入脚本
echo "[2/3] 生成 MySQL 导入脚本..."
LOAD_SQL="load_data.sql"
cat << EOF > "$LOAD_SQL"
USE $DB_NAME;

-- 按依赖顺序导入表（先导入小表，再导入大表）
LOAD DATA LOCAL INFILE 'nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'region.tbl' INTO TABLE region FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'part.tbl' INTO TABLE part FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
EOF
echo "✅ 导入脚本生成完成：$LOAD_SQL"

# 3. 执行导入操作
echo "[3/3] 导入数据到 MySQL（需要输入密码）..."
mysql -u "$MYSQL_USER" -p 123456 --local-infile=1 < "$LOAD_SQL"
echo "✅ 数据导入完成！"