Apache Doris 使用者行為分析實踐

1. OLAP 應用場景

最近調研 Doris，設想了針對行為分析、使用者分析的應用場景，看看 Doris 比對程度。

2. Doris 介紹

2.1 關鍵詞

• MPP 架構：Massively Parallel Processing 大規模并行處理，節點并行計算。
• 海量資料
• 實時分析
• 列式存儲
• 2022 年 6 月 Apache 頂級項目

2.2 使用場景

• 報表分析：毫秒以内
• 即席查詢：分鐘以内
• 資料倉庫
• 聯邦查詢：外表關聯 Hive、IceBerg、Hudi

2.3 技術架構

2.3.1 Frontend（FE）

• 使用者請求處理
• SQL 解析校驗
• SQL 執行計劃建立
• 中繼資料管理：庫、表、任務定義
• 運維：節點管理、任務監控

2.3.2 Backend（BE）

• 資料存儲
• 資料聚合
• 執行查詢計劃

2.4 高性能

2.4.1 豐富索引

• Sorted Compound Key Index：最多 3 列複合排序鍵高并發場景分析
• Z-order Index：字段任意組合的範圍查詢
• Min/Max ：數值範圍查詢
• Bloom Filter ：高基數列去重
• Invert Index ：反向索引字段快速檢索

2.4.2 物化視圖

自動更新保持保持資料的一緻性，減少維護成本。

2.4.3 查詢引擎

• 複雜的大表分布式的 Shuffle Join
• 利用 CPU SIMD 支援向量化計算
• Adaptive Query Execution ： Runtime Statistics 動态調整執行計劃過濾大幅資料

3. 安裝部署

3.1 限制和建議

# 限制
Centos >= 7.1
Java >= 1.8
GCC >= 4.8.2


# 檔案句柄
vi /etc/security/limits.conf 
* soft nofile 65536
* hard nofile 65536


# 其他
時鐘同步
關閉交換分區（swap）
Linux（ext4）

           

複制代碼

3.2 生産環境推薦

3.3 主機資源

3.4 安裝 FE

# 1. 下載下傳FE
wget --no-check-certificate https://mirrors.tuna.tsinghua.edu.cn/apache/doris/1.1/1.1.4-rc01/apache-doris-fe-1.1.4-bin.tar.gz
# 2. 解壓
tar -xvf apache-doris-fe-1.1.4-bin.tar.gz
# 3. 啟動
cd apache-doris-fe-1.1.4-bin && bin/start_fe.sh --daemon


# 4. 安裝MySql Client
wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
rpm -ivh mysql57-community-release-el7-8.noarch.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum install mysql-community-client


# 5. 連接配接FE
mysql -h 172.30.144.1  -P 9030 -uroot
# 6. 檢視FE節點狀态
SHOW PROC '/frontends'\G;           

複制代碼

3.5 安裝 BE

# 1. 下載下傳
wget --no-check-certificate https://mirrors.tuna.tsinghua.edu.cn/apache/doris/1.1/1.1.4-rc01/apache-doris-be-1.1.4-bin-x86_64.tar.gz
# 2. 解壓
tar -xvf apache-doris-be-1.1.4-bin-x86_64.tar.gz
# 3. 啟動：同樣啟動其他節點
cd apache-doris-be-1.1.4-bin-x86_64 && bin/start_be.sh --daemon


# 4. 連接配接FE
mysql -h 172.30.144.1  -P 9030 -uroot
# 5. 添加BE
ALTER SYSTEM ADD BACKEND "172.30.144.2:9050";
ALTER SYSTEM ADD BACKEND "172.30.144.3:9050";
ALTER SYSTEM ADD BACKEND "172.30.144.4:9050";
# 6. 檢視BE狀态
SHOW PROC '/backends'\G           

複制代碼

3.6 安裝 Broker

# 1. 啟動
cd /root/apache-doris-fe-1.1.4-bin/apache_hdfs_broker
bin/start_broker.sh --daemon


# 2. 連接配接FE
mysql -h 172.30.144.1  -P 9030 -uroot
# 3. 添加Broker
ALTER SYSTEM ADD BROKER broker_name "172.30.144.5:8000";
# 4. 檢視Broker節點
SHOW PROC "/brokers"\G

           

複制代碼

3.7 主要端口

4. 資料模型

4.1 聚合模型

資料寫入時，按照次元列對其他名額列進行聚合操作，操作結果：多條資料如果所有次元列相同，那麼會對所有名額列進行聚合。

-- 1. 建立表
CREATE TABLE IF NOT EXISTS zgg.user
(
    `city` VARCHAR(20) COMMENT "城市",
    `age` SMALLINT COMMENT "年齡",
    `sex` TINYINT COMMENT "性别",
    `pv` BIGINT SUM DEFAULT "0" COMMENT "pv",
    `min_time` INT MIN DEFAULT "0" COMMENT "最小停留時間",
    `max_time` INT MAX DEFAULT "0" COMMENT "最大停留時間"
)
AGGREGATE KEY(`city`, `age`, `sex`)
DISTRIBUTED BY HASH(`city`) BUCKETS 3;


-- 2. 寫入資料
INSERT INTO zgg.user VALUES('北京', 25, 1, 1, 50, 100);
INSERT INTO zgg.user VALUES('北京', 20, 0, 1, 150, 300);

           

複制代碼

-- 3. 寫入相同資料
INSERT INTO zgg.user VALUES('北京', 25, 1, 1, 15, 3100);

           

複制代碼

4.2 Unique 模型

通過定義主鍵保證資料的唯一性，Unique 模型簡化了資料導入流程，能夠更好地支撐實時和頻繁更新的場景。

-- 1. 建立表
CREATE TABLE IF NOT EXISTS zgg.user_unique
(
    `user_id` LARGEINT NOT NULL COMMENT "使用者id",
    `city` VARCHAR(20) COMMENT "城市",
    `age` SMALLINT COMMENT "年齡",
    `sex` TINYINT COMMENT "性别"
)
UNIQUE KEY(`user_id`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 3;


-- 2. 寫入資料
INSERT INTO zgg.user_unique VALUES(1, '北京', 25, 1);
INSERT INTO zgg.user_unique VALUES(2, '上海', 35, 0);

           

複制代碼

-- 3. 寫入主鍵相同資料
INSERT INTO zgg.user_unique VALUES(1, '沈陽', 15, 1);

           

複制代碼

4.3 Duplicate 模型

用于存儲原始資料，允許重複資料

-- 1. 建立表
CREATE TABLE IF NOT EXISTS zgg.user_duplicate
(
    `user_id` LARGEINT NOT NULL COMMENT "使用者id",
    `city` VARCHAR(20) COMMENT "城市",
    `age` SMALLINT COMMENT "年齡",
    `sex` TINYINT COMMENT "性别"
)
DUPLICATE KEY(`user_id`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 3;


-- 2. 寫入資料
INSERT INTO zgg.user_duplicate VALUES(1, '北京', 25, 1);
INSERT INTO zgg.user_duplicate VALUES(2, '上海', 35, 0);

           

複制代碼

-- 3. 寫入主鍵相同資料
INSERT INTO zgg.user_duplicate VALUES(1, '沈陽', 15, 1);

           

複制代碼

5. 事件分析适配：分頁面 PV、分頁面 UV

步驟 1：使用 Duplicate 模型存儲原始資料

CREATE TABLE IF NOT EXISTS zgg.user_all
(
    `date` DATE NOT NULL COMMENT "時間",
    `page` VARCHAR(20) NOT NULL COMMENT '頁面',
    `user_id` LARGEINT NOT NULL COMMENT "使用者id"
)
DUPLICATE KEY(`date`, `page`, `user_id`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;


-- 插入測試資料
INSERT INTO zgg.user_all VALUES('2022-11-18', 'login', 1);
INSERT INTO zgg.user_all VALUES('2022-11-18','login', 2);
INSERT INTO zgg.user_all VALUES('2022-11-18','order', 1);
INSERT INTO zgg.user_all VALUES('2022-11-18','order', 1);
INSERT INTO zgg.user_all VALUES('2022-11-18','order', 2);
INSERT INTO zgg.user_all VALUES('2022-11-18','pay', 1);
INSERT INTO zgg.user_all VALUES('2022-11-18','pay', 1);

           

複制代碼

步驟 2：使用聚合模型計算分頁面 PV

-- 1. 建立表
CREATE TABLE IF NOT EXISTS zgg.user_pv
(
    `date` DATE NOT NULL COMMENT "時間",
    `page` VARCHAR(20) NOT NULL COMMENT '頁面',
    `pv` BIGINT SUM DEFAULT "0" COMMENT "pv"
)
AGGREGATE KEY(`date`, `page`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;


-- 2. 使用INSERT INTO table SELECT ...
INSERT INTO zgg.user_pv SELECT `date`, `page`, 1 FROM zgg.user_all;

           

複制代碼

步驟 3：使用 Unique 模型計算分頁面 UV

-- 1. 建立表
CREATE TABLE IF NOT EXISTS zgg.user_uv
(
    `date` DATE NOT NULL COMMENT "時間",
    `page` VARCHAR(20) NOT NULL COMMENT '頁面',
    `user_id` LARGEINT NOT NULL COMMENT "使用者id"
)
UNIQUE KEY(`date`, `page`, `user_id`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;


-- 2. 使用INSERT INTO table SELECT ...
INSERT INTO zgg.user_uv SELECT `date`, `page`, `user_id` FROM zgg.user_all;


-- 3. 使用COUNT查詢UV
SELECT date, page, count(user_id) uv 
  FROM  zgg.user_uv 
 GROUP BY date, page;

           

複制代碼

6. 留存分析适配：使用者注冊後 7 日内下訂單比重

使用 Unique 模型

-- 1. 系統資料庫
CREATE TABLE IF NOT EXISTS zgg.user_register
(
    `date` DATE NOT NULL COMMENT "時間",
    `user_id` LARGEINT NOT NULL COMMENT "使用者id"
)
UNIQUE KEY(`date`, `user_id`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;


-- 2. 訂單表
CREATE TABLE IF NOT EXISTS zgg.user_order
(
    `date` DATE NOT NULL COMMENT "時間",
    `user_id` LARGEINT NOT NULL COMMENT "使用者id"
)
UNIQUE KEY(`date`, `user_id`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;


-- 3. 擷取近7天注冊總使用者
SELECT COUNT(t1.user_id) cnt
  FROM  zgg.user_register t1
   AND  t1.`date` BETWEEN '2022-11-12' AND '2022-11-18';
   
-- 4. 擷取近7天注冊使用者中下訂單的使用者
SELECT COUNT(t2.user_id)
  FROM  zgg.user_register t1, zgg.user_order t2
 WHERE  t1.user_id = t2.user_id
   AND  t1.`date` BETWEEN '2022-11-12' AND '2022-11-18';

           

複制代碼

7. 漏鬥分析适配：訂單支付：使用者注冊 > 挑選商品 > 送出訂單 > 支付

使用聚合模型 + Bitmap

-- 1. 建立表
CREATE TABLE IF NOT EXISTS zgg.user_funnel
(
    `date` DATE NOT NULL COMMENT "時間",
    `page` VARCHAR(20) NOT NULL COMMENT '頁面',
    `user_id` BITMAP BITMAP_UNION NULL COMMENT "使用者id"
)
AGGREGATE KEY(`date`, `page`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;


-- 2. 寫入模拟資料
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'register', to_bitmap(1));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'register', to_bitmap(2));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'register', to_bitmap(3));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'register', to_bitmap(4));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'shopping', to_bitmap(1));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'shopping', to_bitmap(2));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'shopping', to_bitmap(3));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'order', to_bitmap(1));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'order', to_bitmap(2));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'pay', to_bitmap(1));


-- 3. 通過bitmap函數：bitmap_count和bitmap_union組合以及intersect_count
SELECT 'register' page, bitmap_count(bitmap_union(user_id)) cnt   
  FROM  zgg.user_funnel
 UNION ALL
-- register -> shopping
SELECT 'register -> shopping' page, intersect_count(user_id, page, 'register', 'shopping') cnt
  FROM zgg.user_funnel 
 UNION ALL
-- register -> shopping -> order
SELECT 'register -> shopping -> order' page, intersect_count(user_id, page, 'register', 'shopping', 'order') cnt
  FROM zgg.user_funnel 
 UNION ALL
-- register -> shopping -> order -> 'pay'
SELECT 'register -> shopping -> order-> pay' page, intersect_count(user_id, page, 'register', 'shopping', 'order', 'pay') cnt
  FROM zgg.user_funnel;

           

複制代碼

其餘場景使用 Doris 基本都可以适配，滿足功能需求後，接下來會着重進行故障測試和性能測試。