基于 Apache Doris 的用户画像数据模型设计方案

发布于：2025-09-12 ⋅ 阅读:(26) ⋅ 点赞:(0)
一、 需求分析与设计目标

  1. 数据源：

    • 用户基本信息：用户ID、性别、出生日期、注册时间、常驻地域（省、市、区）、职业等。

    • 用户体检报告：每次体检的报告ID、体检时间、各项指标（如血压、血糖、血脂、BMI等）。

    • 用户关注的保健品：用户ID、关注/浏览/收藏的保健品ID、行为时间、行为类型（点击、收藏、加购）、保健品品类等。

  2. 核心需求：

    • 支持以用户为主体的 360° 视图查询。

    • 支持在时间（年、季度、月、周、日）和地域（国家、省、市）维度上进行灵活的上卷（Roll-up）和下钻（Drill-down）分析。

    • 支持对用户标签（如“高血压风险人群”、“VC补充剂潜在客户”）的快速群体圈选和统计分析。

    • 查询响应速度快，即使在大数据量下也能亚秒级响应。

  3. 设计目标：

    • 高性能：利用 Doris 的预聚合、列式存储和索引技术实现极速查询。

    • 可扩展性：模型能够轻松应对数据量的持续增长。

    • 易维护性：表结构清晰，数据更新和ETL流程高效。

二、 技术选型与 Doris 特性利用

选择 Apache Doris 的原因及其核心特性的利用：

  • MPP 架构：天然支持大规模并行处理，适合复杂的分析查询。

  • 列式存储：高压缩比，查询时只需读取相关列，极大降低 I/O。

  • 智能预聚合（Aggregate 模型）：本方案核心，可预先计算常用维度的汇总数据，使聚合查询速度极快。

  • 分区与分桶：支持按时间分区，便于数据管理（如淘汰旧数据）和查询时剪枝。结合分桶（Hash分桶）实现数据均匀分布和高效点查。

  • 物化视图（Materialized View）：另一核心，可自动或手动为不同维度组合创建预聚合表，查询时路由到最优的物化视图，灵活支持上卷下钻。

  • Duplicate 模型：用于存储需要保留原始明细数据的表，如用户行为流水。

  • 高效数据导入：支持 Broker Load / Stream Load / Routine Load，便于从 Kafka、HDFS、MySQL 等数据源批量或实时导入数据。

三、 数据模型详细设计

建议采用维度建模的思想，构建雪花模型或星型模型。这里我们采用星型模型，以保持查询的简洁和高效。

1. ODS 层（操作数据层）

  • 目的：贴源数据层，结构与数据源保持一致。

  • 表设计：使用 Duplicate 模型，保留原始明细。

    • ods_user_basic (用户基本信息表)

    • ods_user_medical_report (用户体检报告表)

    • ods_user_behavior (用户保健品关注行为表)

  • 数据导入：使用 Routine Load 从 Kafka 实时接入用户行为数据，使用 Broker Load 每天批量导入体检报告和基本信息变更数据。

2. DWD 层（数据明细层）

  • 目的：对 ODS 层数据进行清洗、整合、轻度聚合，形成明细事实表和维度表。

  • 表设计：

    • 事实表：

      • dwd_user_medical_fact (用户体检事实表)

        • 模型：Aggregate 模型

        • 分区：按 exam_date (体检日期) 进行 Range Partitioning（按月分区）。

        • 分桶：按 user_id 分桶。

        • 排序键：(user_id, exam_date)

        • Schema:

          sql

          CREATE TABLE dwd_user_medical_fact (
    `user_id` BIGINT,
    `exam_date` DATE,           -- 衍生自exam_time
    `exam_time` DATETIME,
    `province` VARCHAR(20),
    `city` VARCHAR(20),
    `blood_pressure_high` SMALLINT REPLACE_IF_NOT_NULL, -- 替换式聚合
    `blood_pressure_low` SMALLINT REPLACE_IF_NOT_NULL,
    `blood_sugar` FLOAT REPLACE_IF_NOT_NULL,
    `bmi` FLOAT REPLACE_IF_NOT_NULL,
    -- ... 其他指标
    `is_high_blood_pressure` BOOLEAN REPLACE, -- 是否高血压，根据指标计算得出
    `is_high_blood_sugar` BOOLEAN REPLACE     -- 是否高血糖
) ENGINE=OLAP
AGGREGATE KEY(user_id, exam_date, exam_time, province, city)
PARTITION BY RANGE(exam_date) (...)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

      • dwd_user_behavior_fact (用户行为事实表)

        • 模型：Aggregate 模型

        • 分区：按 event_date (行为日期) 分区。

        • 分桶：按 user_id 分桶。

        • 排序键：(user_id, event_date,保健品品类)

        • Schema:

          sql

          CREATE TABLE dwd_user_behavior_fact (
    `user_id` BIGINT,
    `event_date` DATE,
    `event_time` DATETIME,
    `behavior_type` VARCHAR(20), -- 'click', 'favor', 'cart'
    `health_product_id` BIGINT,
    `product_category` VARCHAR(50),
    `click_count` BIGINT SUM,     -- 计数型聚合
    `is_latest_behavior` BOOLEAN REPLACE -- 是否为该商品最新行为
) ENGINE=OLAP
AGGREGATE KEY(user_id, event_date, event_time, behavior_type, health_product_id, product_category)
PARTITION BY RANGE(event_date) (...)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

    • 维度表：

      • dim_user (用户维度表)

        • 模型：Unique 模型（确保用户唯一性）

        • 分区：可按注册日期分区。

        • 分桶：按 user_id 分桶。

        • Schema:

          sql

          CREATE TABLE dim_user (
    `user_id` BIGINT,
    `gender` VARCHAR(10),
    `birth_date` DATE,
    `register_date` DATE,
    `常住省份` VARCHAR(20),
    `常住城市` VARCHAR(20),
    `occupation` VARCHAR(50),
    -- ... 其他属性
) ENGINE=OLAP
UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;
        • 时间维度表

CREATE TABLE dim_date (
    `date_id` DATE,                           -- 代理键，也是自然键，格式 'YYYY-MM-DD'
    `day` TINYINT,                            -- 本月中的第几天 (1-31)
    `month` TINYINT,                          -- 年份中的第几月 (1-12)
    `quarter` TINYINT,                        -- 年份中的第几季度 (1-4)
    `year` SMALLINT,                          -- 年份 (e.g., 2023)
    `day_of_week` TINYINT,                    -- 本周中的第几天 (1=Sunday, 7=Saturday)
    `week_of_year` TINYINT,                   -- 年份中的第几周 (ISO标准 1-53)
    `is_weekend` BOOLEAN,                     -- 是否是周末
    `is_holiday` BOOLEAN,                     -- 是否是法定节假日
    `holiday_name` VARCHAR(50),               -- 节假日名称 (e.g., '国庆节', '元旦')
    `is_workday` BOOLEAN,                     -- 是否是工作日（考虑调休后）
    `month_name` VARCHAR(10),                 -- 月份英文缩写 (e.g., 'Jan', 'Feb')
    `quarter_name` VARCHAR(2),                -- 季度名称 (e.g., 'Q1', 'Q2')
    `year_quarter` VARCHAR(7),                -- 年份季度 (e.g., '2023-Q1')
    `year_month` VARCHAR(7),                  -- 年份月份 (e.g., '2023-01')
    `year_week` VARCHAR(7),                   -- 年份周数 (e.g., '2023-W01')
    `last_year_same_day` DATE,                -- 去年同一天
    `last_month_same_day` DATE,               -- 上月同一天（可能为空）
    `day_num_in_epoch` INT,                   -- 自某个固定日期（如1970-01-01）以来的天数
    `week_begin_date` DATE,                   -- 本周第一天（周日或周一）
    `week_end_date` DATE,                     -- 本周最后一天
    `month_begin_date` DATE,                  -- 本月第一天
    `month_end_date` DATE,                    -- 本月最后一天
    `quarter_begin_date` DATE,                -- 本季度第一天
    `quarter_end_date` DATE,                  -- 本季度最后一天
    `year_begin_date` DATE,                   -- 本年第一天
    `year_end_date` DATE                      -- 本年最后一天
) ENGINE=OLAP
DUPLICATE KEY(date_id)
COMMENT "时间维度表"
DISTRIBUTED BY HASH(date_id) BUCKETS 1
PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "YEAR",
    "dynamic_partition.start" = "-3", -- 动态创建过去3年和未来2年的分区
    "dynamic_partition.end" = "2",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "1"
);

3. DWS 层（数据服务层 / 宽表层）

  • 目的：面向业务场景构建宽表，进一步聚合数据，减少多表关联，提升查询性能。这是支持用户画像上卷下钻的核心层。

  • 表设计：使用 Aggregate 模型，并创建物化视图。

    • dws_user_profile_wide (用户画像宽表)

      • 模型：Aggregate 模型

      • 分区：按 date (日期) 分区。

      • 分桶：按 user_id 分桶。

      • 排序键：(date, province, city, user_id)

      • Schema:

        sql

        CREATE TABLE dws_user_profile_wide (
    `date` DATE,                    -- 日期粒度
    `province` VARCHAR(20),
    `city` VARCHAR(20),
    `user_id` BIGINT,
    `age_group` VARCHAR(10) REPLACE, -- 年龄段，e.g., '20-30'
    `gender` VARCHAR(10) REPLACE,
    `latest_blood_pressure_status` VARCHAR(20) REPLACE, -- 最新血压状态
    `latest_blood_sugar_status` VARCHAR(20) REPLACE,
    `is_high_risk_cardio` BOOLEAN REPLACE, -- 是否心脑血管高风险
    `favorited_vc_product_count` BIGINT SUM, -- 收藏的VC类产品总数
    `clicked_protein_powder_count` BIGINT SUM, -- 点击的蛋白粉类产品次数
    -- ... 其他标签和聚合指标
) ENGINE=OLAP
AGGREGATE KEY(date, province, city, user_id)
PARTITION BY RANGE(date) (...)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

      • 物化视图创建示例：

        sql

        -- 为按省和日期聚合创建物化视图，加速上卷查询
CREATE MATERIALIZED VIEW province_date_agg AS
SELECT
    `date`,
    `province`,
    COUNT(DISTINCT user_id) AS total_users,
    SUM(CAST(is_high_risk_cardio AS INT)) AS high_risk_users,
    SUM(favorited_vc_product_count) AS total_vc_favorites
FROM dws_user_profile_wide
GROUP BY `date`, `province`;

-- 为按市、性别和日期聚合创建物化视图，支持下钻查询
CREATE MATERIALIZED VIEW city_gender_date_agg AS
SELECT
    `date`,
    `province`,
    `city`,
    `gender`,
    COUNT(DISTINCT user_id) AS total_users,
    ...
FROM dws_user_profile_wide
GROUP BY `date`, `province`, `city`, `gender`;

        Doris 的查询优化器会自动选择最优的物化视图来响应查询。

四、 ETL 数据处理流程

  1. 数据采集：用户行为数据通过埋点实时写入 Kafka。体检报告和用户信息通过业务数据库的 Binlog 或每日全量/增量导出文件到 HDFS。

  2. 数据导入：

    • 实时：使用 Routine Load 任务持续消费 Kafka 中的行为数据，导入到 ods_user_behavior 表。

    • 批量：每天凌晨，使用 Broker Load 从 HDFS 拉取体检报告和用户信息的增量文件，导入到对应的 ODS 表。

  3. 数据加工：
    * 通过INSERT INTO SELECT语句，定期（如每小时或每天）将 ODS 层数据清洗、转换、聚合到 DWD 层事实表和维度表。
    * 同样通过 INSERT INTO SELECT，将 DWD 层数据与维度表 JOIN 后，聚合到 DWS 宽表 dws_user_profile_wide。这个过程会计算用户标签（如 is_high_risk_cardio）和聚合指标。

  4. 数据服务：应用层直接通过 MySQL 协议查询 DWS 层的宽表及其物化视图，快速获取分析结果。

五、 示例查询

  1. 下钻分析：”2023年Q4，江苏省南京市，高血糖用户中最关注维生素C保健品的人群分布？“

    sql

    SELECT
    city,
    gender,
    age_group,
    COUNT(DISTINCT user_id) AS user_count
FROM dws_user_profile_wide
WHERE
    `date` >= '2023-10-01' AND `date` <= '2023-12-31'
    AND province = '江苏省'
    AND city = '南京市'
    AND latest_blood_sugar_status = 'high' -- 高血糖标签
    AND favorited_vc_product_count > 0     -- 关注过VC产品
GROUP BY
    city, gender, age_group -- 下钻到市、性别、年龄
ORDER BY user_count DESC;

    Doris 会尝试使用 province_date_agg 等物化视图快速定位到江苏省2023Q4的数据，然后再进行下钻计算。

  2. 上卷分析：”近一年，全国各季度，心脑血管高风险用户的比例趋势？“

    sql

    SELECT
    YEAR(date) AS year,
    QUARTER(date) AS quarter,
    SUM(CAST(is_high_risk_cardio AS INT)) / COUNT(DISTINCT user_id) AS risk_ratio
FROM dws_user_profile_wide
WHERE `date` >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
GROUP BY year, quarter
ORDER BY year, quarter;

    此查询可能会直接命中按日期聚合的物化视图，效率极高。

六、 总结与优化建议

  • 优势：本方案充分利用了 Doris 的聚合模型、物化视图、分区分桶等核心特性，完美支撑了用户画像的多维分析需求，预计能达到极快的查询响应速度。

  • 监控与调优：

    • 监控 dws_user_profile_wide 表的物化视图命中率，根据业务查询模式调整或增加物化视图。

    • 监控集群磁盘、内存、BE节点状态，随着数据增长水平扩展 Doris 集群。

    • 合理设置分桶数量，通常在10-100个之间，避免 Tablet 过多导致元数据压力过大。

  • 未来扩展：

    • 可以引入更复杂的用户标签算法（如机器学习模型打分），通过 ETL 过程将结果写入用户宽表。

    • 可以探索使用 Doris 的 Array 类型存储用户的行为标签序列，进行用户行为路径分析。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布