画像表越来越宽，500列了——宽表 vs 标签表怎么选？

-- 宽表模式：一行一用户
CREATE TABLE ads_user_profile (
  user_id          STRING,
  gender           STRING,
  age_group        STRING,
  country          STRING,
  register_days    INT,
  login_cnt_7d     INT,
  login_cnt_30d    INT,
  total_amount     DECIMAL(12,2),
  value_level      STRING,
  churn_prob       DOUBLE,
  rfm_segment      STRING,
  -- ... 还有190列 ...
  dt               STRING
) PARTITIONED BY (dt)
STORED AS PARQUET;
 
-- 查询方便：一条SQL查到某用户的全部画像
SELECT * FROM ads_user_profile WHERE user_id = '10086' AND dt = '2024-01-15';

-- 标签表模式：一行一标签
CREATE TABLE ads_user_tag_kv (
  user_id     STRING COMMENT '用户ID',
  tag_name    STRING COMMENT '标签名，如value_level',
  tag_value   STRING COMMENT '标签值，统一存STRING',
  tag_type    STRING COMMENT '标签类型：string/int/float',
  update_time TIMESTAMP COMMENT '标签更新时间',
  dt          STRING COMMENT '日期分区'
) PARTITIONED BY (dt, tag_name)  -- 双分区：日期+标签名
STORED AS PARQUET;
 
-- 新增标签：不需要ALTER TABLE，直接写入新的tag_name
INSERT OVERWRITE TABLE ads_user_tag_kv PARTITION (dt='2024-01-15', tag_name='new_tag')
SELECT user_id, 'new_tag', new_tag_value, 'string', CURRENT_TIMESTAMP
FROM some_source;
 
-- 查询某用户的所有标签
SELECT tag_name, tag_value
FROM ads_user_tag_kv
WHERE user_id = '10086' AND dt = '2024-01-15';

缺点：查一个用户的全部画像需要查多行（性能差于宽表）；所有标签值统一存STRING，类型信息丢失；BI工具不友好（需要行转列才能展示）。

两种模式全面对比

维度	宽表模式	标签表模式
Schema变更	ALTER TABLE加列，成本高	直接写新partition，零成本
查一个用户的全部标签	一行搞定，非常快	需要查多行，性能略差
按标签圈选用户	WHERE tag_col = x，直接过滤	WHERE tag_name = x AND tag_value = y
标签独立更新	不支持，整表一起更新	支持，每个tag_name独立分区
标签独立回溯	困难，整表重跑	容易，只重跑指定tag_name分区
适合标签数量	<200个	200+个，可扩展到1000+
下游BI友好度	高（天然列式展示）	低（需要行转列PIVOT）
存储效率	Parquet列压缩好	KV模式有冗余（tag_name重复）

最佳实践：标签表+物化宽表——两全其美

我在出海硬件品牌后期做的架构改造，用标签表做底层存储，定期物化成宽表给查询用：

标签存储架构：标签表 + 物化宽表
----------------------------------------------
底层存储（标签表KV模式）
  ads_user_tag_kv
  - 每个标签独立分区，独立更新
  - 新增标签零成本
  - 标签回溯只重跑单个分区
       |
       v（物化任务，每天跑一次）
查询层（物化宽表）
  ads_user_profile_wide
  - 由标签表PIVOT生成
  - 一行一用户，下游BI友好
  - 只包含高频使用的核心标签（50-80列）
       |
       v
在线层（Redis / ES）
  - 从物化宽表同步热标签

-- 物化任务：标签表 -> 宽表（核心标签PIVOT）
INSERT OVERWRITE TABLE ads_user_profile_wide PARTITION (dt='${bizdate}')
SELECT
  user_id,
  MAX(CASE WHEN tag_name = 'gender' THEN tag_value END) AS gender,
  MAX(CASE WHEN tag_name = 'age_group' THEN tag_value END) AS age_group,
  MAX(CASE WHEN tag_name = 'country' THEN tag_value END) AS country,
  MAX(CASE WHEN tag_name = 'value_level' THEN tag_value END) AS value_level,
  MAX(CASE WHEN tag_name = 'rfm_segment' THEN tag_value END) AS rfm_segment,
  MAX(CASE WHEN tag_name = 'login_cnt_7d' THEN CAST(tag_value AS INT) END) AS login_cnt_7d,
  MAX(CASE WHEN tag_name = 'churn_prob' THEN CAST(tag_value AS DOUBLE) END) AS churn_prob
  -- 只物化核心标签，不是全部标签
FROM ads_user_tag_kv
WHERE dt = '${bizdate}'
GROUP BY user_id;

面试高频追问

追问一：标签表的tag_value全存STRING，不会有类型问题吗？

会有。数值型标签存成STRING后，比较和排序需要CAST，有性能损失。我们的做法是：标签表里加一个tag_type字段标记原始类型（string/int/float/boolean），在物化成宽表时做CAST转换。标签表是存储层面的妥协，宽表是查询层面的优化——各层做各层的事。

追问二：物化宽表不是又回到了原来的问题吗？

区别在于：物化宽表只包含核心标签（50-80列），不是全部标签。剩下的低频标签在标签表里按需查询。这样宽表的列数控制在一个合理范围内。而且物化宽表是「衍生数据」——标签表才是唯一数据源。标签口径变了只需要重跑标签表的对应分区，物化宽表自动重新生成。

追问三：标签表的分区怎么设计？双分区(dt, tag_name)不会分区太多吗？

这是个好问题。如果有200个标签 x 365天 = 73000个分区，确实很多。Hive对大量小分区处理不好。解决方案：①按标签分类做粗分区（dt, tag_category），基础属性标签/行为统计标签/模型预测标签各一个分区；②定期合并历史分区（3个月前的数据合成月粒度）。我在出海硬件品牌用的是粗分区方案，120个标签分成3个tag_category，分区数量可控。

实战踩坑

我在某出海硬件品牌做架构改造时，从纯宽表迁移到标签表+物化宽表的方案，迁移过程踩了一个坑：物化宽表的PIVOT SQL跑得非常慢。

原因是标签表里千万用户 x 120个标签 = 12亿行数据，做GROUP BY + MAX(CASE WHEN)的PIVOT操作需要大量Shuffle。12亿行的Shuffle，即使在Spark上也要跑30多分钟。

-- 优化前：全量PIVOT（30分钟）
SELECT user_id, MAX(CASE WHEN tag_name = 'gender' ...) ...
FROM ads_user_tag_kv WHERE dt = '${bizdate}'
GROUP BY user_id;  -- 12亿行的GROUP BY太慢了
 
-- 优化后：先按tag_name分区JOIN（8分钟）
SELECT
  COALESCE(g.user_id, v.user_id, r.user_id) AS user_id,
  g.tag_value AS gender,
  v.tag_value AS value_level,
  r.tag_value AS rfm_segment
FROM
  (SELECT user_id, tag_value FROM ads_user_tag_kv
   WHERE dt='${bizdate}' AND tag_name='gender') g
FULL OUTER JOIN
  (SELECT user_id, tag_value FROM ads_user_tag_kv
   WHERE dt='${bizdate}' AND tag_name='value_level') v ON g.user_id = v.user_id
FULL OUTER JOIN
  (SELECT user_id, tag_value FROM ads_user_tag_kv
   WHERE dt='${bizdate}' AND tag_name='rfm_segment') r ON g.user_id = r.user_id;

教训：标签表PIVOT成宽表时，如果标签数量多且数据量大，GROUP BY + CASE WHEN的方案会很慢。改用分区子查询JOIN的方式，每个子查询只扫描一个tag_name分区（分区裁剪），然后JOIN合并，性能好很多。从30分钟优化到8分钟。

总结

宽表适合标签少且查询以用户维度为主的场景，标签表适合标签多且需要灵活扩展的场景。最佳实践是标签表做底层存储+物化宽表做查询加速。面试时能讲清楚两种方案的取舍过程和迁移的踩坑经验，说明你做过有深度的画像架构设计。

下一篇聊一个软实力话题：标签上线了但没人用——怎么推动业务方使用画像数据？

你们的画像表有多少列？用的宽表还是标签表？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

用户画像面试通关手册

第11-15篇：业务落地、广告归因与系统设计

Wood聊大数据

9年大数据老兵，聊技术、聊职场、聊成长