基于 Apache Doris 的用戶畫像數據模型設計方案

一、需求分析與設計目標

數據源：
- 用戶基本信息：用戶ID、性別、出生日期、注冊時間、常駐地域（省、市、區）、職業等。
- 用戶體檢報告：每次體檢的報告ID、體檢時間、各項指標（如血壓、血糖、血脂、BMI等）。
- 用戶關注的保健品：用戶ID、關注/瀏覽/收藏的保健品ID、行為時間、行為類型（點擊、收藏、加購）、保健品品類等。
核心需求：
- 支持以用戶為主體的 360° 視圖查詢。
- 支持在時間（年、季度、月、周、日）和地域（國家、省、市）維度上進行靈活的上卷（Roll-up）和下鉆（Drill-down）分析。
- 支持對用戶標簽（如“高血壓風險人群”、“VC補充劑潛在客戶”）的快速群體圈選和統計分析。
- 查詢響應速度快，即使在大數據量下也能亞秒級響應。
設計目標：
- 高性能：利用 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 數據處理流程

數據采集：用戶行為數據通過埋點實時寫入 Kafka。體檢報告和用戶信息通過業務數據庫的 Binlog 或每日全量/增量導出文件到 HDFS。
數據導入：
- 實時：使用?Routine Load?任務持續消費 Kafka 中的行為數據，導入到?ods_user_behavior?表。
- 批量：每天凌晨，使用?Broker Load?從 HDFS 拉取體檢報告和用戶信息的增量文件，導入到對應的 ODS 表。
數據加工：
* 通過INSERT INTO SELECT語句，定期（如每小時或每天）將 ODS 層數據清洗、轉換、聚合到 DWD 層事實表和維度表。
* 同樣通過?INSERT INTO SELECT，將 DWD 層數據與維度表?JOIN?后，聚合到 DWS 寬表?dws_user_profile_wide。這個過程會計算用戶標簽（如?is_high_risk_cardio）和聚合指標。
數據服務：應用層直接通過?MySQL 協議查詢 DWS 層的寬表及其物化視圖，快速獲取分析結果。

五、示例查詢

下鉆分析：”2023年Q4，江蘇省南京市，高血糖用戶中最關注維生素C保健品的人群分布？“

sql

SELECTcity,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 BYcity, gender, age_group -- 下鉆到市、性別、年齡
ORDER BY user_count DESC;

Doris 會嘗試使用?province_date_agg?等物化視圖快速定位到江蘇省2023Q4的數據，然后再進行下鉆計算。

上卷分析：”近一年，全國各季度，心腦血管高風險用戶的比例趨勢？“

sql

SELECTYEAR(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 類型存儲用戶的行為標簽序列，進行用戶行為路徑分析。