文章目录
案例一 城市关系打平
1.1 数据源:
id,city,parentId
1,北京市,0
2,山东省,0
3,昌平区,1
4,海淀区,1
5,沙河镇,3
6,马池口镇,3
7,中关村,4
8,上地,4
9,烟台市,2
10,青岛市,2
11,单平区,9
12,芝果区,9
13,即墨区,10
14,城阳,10
注意: 一个id 唯一对应一个ParentId
一个ParentId可以对应多个id
数据结构：这其实就是一种树型结构
1.2 需要结果:
一级 二级 三级
0 1 3
1 北京市 昌平区 沙河镇
2 北京市 昌平区 马池口镇
4
3 北京市 海淀区 中关村
4 北京市 海淀区 上地
0 2 9
5 山东省 烟台市 单平区
6 山东省 烟台市 芝果区
10
7 山东省 青岛市 即墨区
8 山东省 青岛市 城阳
1.3 hive sql
方式一 使用自关联join
优点：写法简单
缺点：树的层次越多，join越多。如树的高度为3，需要join2次。若树的高度为10，则需要join 9 次，此种方式不通用。
-- 创建表
create table if not exists city(
id int,
name string,
parentId int
)
row format delimited fields terminated by ',';
-- 初始化数据
load data local inpath '/opt/data/city.txt' overwrite into table city;
# 自关联join方式
with t as (select id,name,parentId from city)
select c.name level1,b.name level2,a.name level3 from t a
join t b on a.parentId=b.id
join t c on b.parentId=c.id;
level1 level2 level3
北京市 昌平区 沙河镇
北京市 昌平区 马池口镇
北京市 海淀区 中关村
北京市 海淀区 上地
山东省 烟台市 单平区
山东省 烟台市 芝果区
山东省 青岛市 即墨区
山东省 青岛市 城阳
Time taken: 9.515 seconds, Fetched: 8 row(s)
# 过程
with t as (select id,name,parentId from city)
select a.*,b.* from t a
join t b on a.parentId=b.id;
a.id a.name a.parentid b.id b.name b.parentid 关联 city.id city.name city.parentid
3 昌平区 1 1 北京市 0 1 北京市 0
4 海淀区 1 1 北京市 0 2 山东省 0
9 烟台市 2 2 山东省 0 3 昌平区 1
10 青岛市 2 2 山东省 0 4 海淀区 1
5 沙河镇 3 3 昌平区 1 5 沙河镇 3
6 马池口镇 3 3 昌平区 1 6 马池口镇 3
7 中关村 4 4 海淀区 1 7 中关村 4
8 上地 4 4 海淀区 1 8 上地 4
11 单平区 9 9 烟台市 2 9 烟台市 2
12 芝果区 9 9 烟台市 2 10 青岛市 2
13 即墨区 10 10 青岛市 2 11 单平区 9
14 城阳 10 10 青岛市 2 12 芝果区 9
Time taken: 16.461 seconds, Fetched: 12 row(s) 13 即墨区 10
with t as (select id,name,parentId from city)
select a.*,b.*,c.* from t a
join t b on a.parentId=b.id
join t c on b.parentId=c.id;
a.id a.name a.parentid b.id b.name b.parentid c.id c.name c.parentid
5 沙河镇 3 3 昌平区 1 1 北京市 0
6 马池口镇 3 3 昌平区 1 1 北京市 0
7 中关村 4 4 海淀区 1 1 北京市 0
8 上地 4 4 海淀区 1 1 北京市 0
11 单平区 9 9 烟台市 2 2 山东省 0
12 芝果区 9 9 烟台市 2 2 山东省 0
13 即墨区 10 10 青岛市 2 2 山东省 0
14 城阳 10 10 青岛市 2 2 山东省 0
也可以参考另外一个案例 flink/hive sql 实现递归 查询人员组织架构
方式二 使用Hive SQL中的递归查询功能 with recursive
方式三 自定义UDAF(递归算法)
import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
public class GetSubordinates extends UDAF {
public static class Evaluator implements UDAFEvaluator {
private List<String> resultList; // 结果集合
@Override
public void init() {
resultList = new ArrayList<>();
}
public boolean iterate(Integer employeeId) {
if (resultList == null || resultList.isEmpty()) {
return false;
} else {
//从employees表中根据当前employeeId查找员工名称的逻辑
String currentEmployeeName = getCurrentEmployeeName();
if (!currentEmployeeName.equals("")) {
resultList.add(currentEmployeeName);
}
//员工id找到所有的父id
addSubordinatesToList(employeeId);
return true;
}
}
public List<String> terminatePartial() {
return resultList;
}
public boolean merge(List<String> otherResultList) {
if (otherResultList != null && !otherResultList.isEmpty()) {
resultList.addAll(otherResultList);
return true;
} else {
return false;
}
}
public List<String> terminate() {
return resultList;
}
private void addSubordinatesToList(Integer employeeId) {
Set<Integer> visitedEmployees = new HashSet<>();
while (true) {
//从manager_employee表中根据employeeId查找下一个管理者ID的逻辑
Integer nextManagerId = findNextManagerId(employeeId);
if (nextManagerId == null) {
break;
}
if (visitedEmployees.contains(nextManagerId)) {
throw new RuntimeException("Circular dependency detected!");
}
//集合中增加员工管理者ID
visitedEmployees.add(nextManagerId);
//将父id值赋值给id，进行下一轮循环
employeeId = nextManagerId;
}
}
private Integer findNextManagerId(Integer employeeId) {
// 在此处添加从manager_employee表中根据employeeId查找下一个管理者ID的逻辑
// ...
return null;
}
private String getCurrentEmployeeName() {
// 在此处添加从employees表中根据当前employeeId查找员工名称的逻辑
// ...
return "";
}
}
}
方式四 UDF
1.4 输出结果
Total MapReduce CPU Time Spent: 0 msec
OK
c.level1 c.level2 level3
北京市 昌平区 沙河镇
北京市 昌平区 马池口镇
北京市 海淀区 中关村
北京市 海淀区 上地
山东省 烟台市 单平区
山东省 烟台市 芝果区
山东省 青岛市 即墨区
山东省 青岛市 城阳
Time taken: 37.589 seconds, Fetched: 8 row(s)
案例二 根据员工上下级关系来计算统计
假设有一张员工表employee，里面记录了员工的ID、上级ID和员工姓名
详细查看
2.1 准备数据
假设有以下个层级结构的数据
张三6
|
| |
李四4 王五5
｜ ｜ ｜
郑六1 赵七2 钱八3
drop table if exists employee;
create table employee
(
id bigint COMMENT '员工id'
,name STRING COMMENT '员工姓名'
,supervisor_id bigint COMMENT '上级id'
) COMMENT '员工与上级关系表';
INSERT INTO employee values(1,'郑六',4);
INSERT INTO employee values(2,'赵七',4);
INSERT INTO employee values(3,'钱八',4);
INSERT INTO employee values(4,'李四',6);
INSERT INTO employee values(5,'王五',6);
INSERT INTO employee values(6,'张三',0); --顶部节点的父节点设置为0
2.2 方式二：hive的recursive计算
使用withrecursive实现递归查询自身关联表树形结构
WITH RECURSIVE category_tree(id,name,parent_id,level,pathinfo) AS (
SELECT id,name,parent_id, 1, cast(id as varchar(255)) as pathinfo FROM category WHERE parent_id is null
UNION ALL
SELECT category.id,category.name,category.parent_id, category_tree.level+1, pathinfo || ',' || category.id FROM category,category_tree WHERE category.parent_id = category_tree.id
)
SELECT id,name,level,pathinfo FROM category_tree ORDER BY pathinfo;
2.3 方式四：使用UDF
import com.aliyun.odps.udf.UDF;
import java.util.HashMap;
public class Recursive extends UDF {
public HashMap<Integer, Integer> hashMap;
public static final String SPLIT = "-";
public String evaluate(int id) {
//1.读取hive表数据到内存
//...... 假设读取到MAP , 如果数据量过大可能会出现OOM
hashMap = new HashMap<>();
hashMap.put(1, 4);
hashMap.put(2, 4);
hashMap.put(3, 4);
hashMap.put(4, 6);
hashMap.put(5, 6);
hashMap.put(6, 0); //无父id节点用0表示
//2,根据子id,递归查询父id
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(id);
while (true) {
if (0 == hashMap.get(id) || null == hashMap.get(id)) {
return stringBuffer.reverse().toString();
} else {
stringBuffer.append(SPLIT).append(hashMap.get(id));
id=hashMap.get(id);
}
}
}
public static void main(String[] args) {
String evaluate1 = new Recursive().evaluate(1);
String evaluate2 = new Recursive().evaluate(2);
String evaluate3 = new Recursive().evaluate(3);
String evaluate4 = new Recursive().evaluate(4);
String evaluate5 = new Recursive().evaluate(5);
String evaluate6 = new Recursive().evaluate(6);
System.out.println(evaluate1);
System.out.println(evaluate2);
System.out.println(evaluate3);
System.out.println(evaluate4);
System.out.println(evaluate5);
System.out.println(evaluate6);
}
}
所有可能的树路径如下
6-4-1
6-4-2
6-4-3
6-4
6-5
6
案例三 视频转发求视频转发深度
3.1 数据源
MetaQ转发数据流，实时计算视频转发深度
user_id1 user_id2 video_id
1 2 a
1 3 a
2 4 a
3 5 a
3 6 a
1 2 b
1 3 b
video_id a 转发树
1
|
2 3
| |
4 5 6
video_id b 转发树
1
|
2 3
3.2 解决方案
3.3 伪代码实现
import org.apache.flink.table.functions.ScalarFunction;
import java.util.HashMap;
import java.util.Map;
public class Recursive extends ScalarFunction {
public HashMap<String, String> hashMap;
public HashMap<String, String> hashMapResult;
public static final String SPLIT = "-";
public static final String CONCAT = ":";
public String eval(String id) {
//Flink Source写如redis
//1.MetaQ的source数据写入到redis
hashMap = new HashMap<>();
//注意每个转发人id要加上video-id，否则会混淆不同video_id数据
hashMap.put("4-a", "2-a");
hashMap.put("2-a", "1-a");
hashMap.put("5-a", "3-a");
hashMap.put("6-a", "3-a");
hashMap.put("3-a", "1-a");
hashMap.put("1-a", "0-a"); //无父id节点用0表示
hashMap.put("2-b", "1-b");
hashMap.put("3-b", "1-b");
hashMap.put("1-b", "0-b"); //无父id节点用0表示
//Flink process UDF递归处理逻辑
//2.1 根据子id,递归查询父id
StringBuffer stringBuffer = new StringBuffer();
hashMapResult=new HashMap<>();
String video_id=id.split("-")[1];
stringBuffer.append(id.split("-")[0]);
while (true) {
if ("0".equals(hashMap.get(id).split("-")[0]) || null == hashMap.get(id)) {
return video_id+":"+stringBuffer.reverse().toString();
} else {
stringBuffer.append("-").append(hashMap.get(id).split("-")[0]);
id = hashMap.get(id);
}
}
}
public static void main(String[] args) {
String evaluate1 = new Recursive().eval("1-a");
String evaluate2 = new Recursive().eval("2-a");
String evaluate3 = new Recursive().eval("3-a");
String evaluate4 = new Recursive().eval("4-a");
String evaluate5 = new Recursive().eval("5-a");
String evaluate6 = new Recursive().eval("6-a");
System.out.println(evaluate1);
System.out.println(evaluate2);
System.out.println(evaluate3);
System.out.println(evaluate4);
System.out.println(evaluate5);
System.out.println(evaluate6);
String evaluate7 = new Recursive().eval("1-b");
String evaluate8 = new Recursive().eval("2-b");
String evaluate9 = new Recursive().eval("3-b");
System.out.println(evaluate7);
System.out.println(evaluate8);
System.out.println(evaluate9);
}
}
//a:1
//a:1-2
//a:1-3
//a:1-2-4
//a:1-3-5
//a:1-3-6
//b:1
//b:1-2
//b:1-3
这里输出结果的处理方式
（1）sink到OLAP库，通过SQL来计算每个video_id的最深深度。
（2）将输出结果用窗口计算每个video_id的深度，通过取TopN输出。
436
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
