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 基本都可以适配，满足功能需求后，接下来会着重进行故障测试和性能测试。

展开阅读全文

页面更新：2024-04-20

标签：用户节点北京模型场景订单代码页面时间数据