Open
Description
TIS的数据流分析（EMR） 功能基本可用，需要结合TPC-H（https://www.tpc.org/tpch/default5.asp） 通过数据流分析（EMR）来构建离线T+1 宽表供业务系统使用
相关文档
- https://help.aliyun.com/zh/hologres/user-guide/test-plan#li-fiz-p0h-7uq
- 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 - 订单项细节:
D67C
part_key,supplier_key,quantity,extended_price等 - 客户信息:
customer_name,account_balance,market_segment - 地理信息:
nation_name,region_name
典型分析场景
- 按区域统计订单总额：
SELECT region_name, SUM(total_price) FROM order_customer_wide GROUP BY region_name - 客户分群（市场细分 + 区域）：
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（按零件类型分区，加速查询）
典型分析场景
- 按区域统计供应商平均供应成本：
SELECT supplier_region, AVG(supply_cost) FROM supply_chain_wide GROUP BY supplier_region - 高成本零件筛选：
SELECT part_name, supplier_name, supply_cost FROM supply_chain_wide WHERE supply_cost > 1000
关键设计点
-
数据冗余与查询效率
- 宽表通过冗余存储减少 JOIN 操作，适合 OLAP 场景，但需权衡存储成本。
- 使用
ORC格式 + 压缩（如SNAPPY）优化存储和查询性能。
-
分区策略
- 按高频过滤字段（如
p_type、order_date）分区，加速查询。
- 按高频过滤字段（如
-
字段命名规范
- 对同名字段（如
N_NAME）添加前缀（如supplier_nation），避免歧义。
- 对同名字段（如
-
数据一致性
- 确保原始表外键关联正确（如
partsupp必须关联到有效的supplier）。
- 确保原始表外键关联正确（如
注意事项
-
数据生成与导入
- 若 TPC-H 数据在 MySQL 中，需先导出为 CSV，再通过 Hive 的
LOAD DATA或hdfs put+ 外部表加载。
- 若 TPC-H 数据在 MySQL 中，需先导出为 CSV，再通过 Hive 的
-
性能调优
- 调整 Hive 参数：
set hive.exec.parallel=true;（并行执行） - 对大表启用 MapJoin：
set hive.auto.convert.join=true;
- 调整 Hive 参数：
-
宽表更新
- 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-H2. 编译数据生成工具 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
make3. 生成测试数据
- 生成数据文件（
.tbl）：会生成# 生成 1GB 数据（调整 -s 参数控制大小，如 -s 10 生成 10GB） ./dbgen -s 1 -fcustomer.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 表结构
-
调整 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）。
- TPC-H 的默认 DDL 可能包含非 MySQL 语法（如
-
在 MySQL 中执行 DDL
mysql -u root -p
CREATE DATABASE tpch; USE tpch; SOURCE /path/to/modified_dss.ddl;
三、导入数据到 MySQL
-
处理
.tbl文件- 确保数据文件的分隔符是
|，且末尾有分隔符（MySQL 的LOAD DATA需要）。 - 可选：将
.tbl转换为.csv（若需要）：sed 's/|$//' customer.tbl > customer.csv
- 确保数据文件的分隔符是
-
使用
LOAD DATA导入-- 示例：导入 customer 表 LOAD DATA INFILE '/path/to/customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
- 重复此步骤导入所有表。
四、验证数据
- 检查行数
SELECT COUNT(*) FROM customer; -- 预期结果：150,000 行（当 -s 1 时）
- 检查外键约束
SHOW CREATE TABLE orders; -- 确保外键如 O_CUSTKEY 正确关联
常见问题
-
权限问题
- 确保 MySQL 用户有
FILE权限：GRANT FILE ON *.* TO 'user'@'localhost';
- 启动 MySQL 时添加
--local-infile=1：mysql --local-infile=1 -u root -p
- 确保 MySQL 用户有
-
日期格式
- 如果日期报错，使用
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 数据）。
Metadata
Metadata
Assignees
Labels
No labels