项目周期
56个月 需求分析1/3 开发1/3 测试上线1/3
1亿 100G
100万条 １G
城市大脑项目：
对政府下的各委办局数据进行数据采集汇总，
数据体量：
500张表 3400万条 50G 增量数据少
开发1/3：
1.选定Hadoop，hive等组件版本开始进行平台搭建
打开acl 精确进行权限认证
可以对每个用户对每个文件夹的权限控制
2.进行数据仓库的构建，进行仓库分层，控制访问权限
3.将数据推入到前置数据库MySQL中（提前建好表），使用datax，kafka，canal等组件进行数据采集，将数据存放在数仓的ODS层中
4.根据需求使用hive，spark，flink进行模型开发，指标计算（需求开发4个人 负责两个专题（主题内多个需求），一个主题5天，总共4个月）
5.结果导入关系型数据库中，进行数据展示应用
平台搭建-数仓搭建-数据采集-需求开发/模型开发
负责数据采集 某些需求的开发
n个专题：m各主题：（1：12）
开发阶段的人员五六个人
开发阶段的项目时间 五个月左右
1.实际用的是datax,但是如果问道为何不用sqoop?sqoop
sqoop是什么？
项目中确实没有用到，据我了解 sqoop官网已经不进行维护了，而且他太依赖于Hadoop
而datax非常灵活，上传安装解压配置变量就可以使用了
- 数据采集，采集来后进行轻微清洗，然后进行存储
数据采集到前置数据库，非常麻烦，因为数据来自于各个委办局，难点不在于量大，而在于源头太多了，
而且数据敏感，申用周期长
1年多
增量和全量数据采集（datax从前置库中采集）：因表而异 (100张左右，记住部分表)
全量：每天将表中一次性读取全量数据 ，每天一个分区每个分区数据都是全量的
增量：每天采集新的数据 有时间字段作为判断新数据的标识，或者主键是自增的
flume canal
datax 离线异构数据源同步工具 非常适合项目
hdfs 离线存储
kafka 实时存储
数据从前置数据库接入到数据仓库ODS层中 ，
ods层不做任何处理，保持和前置数据库一致，方便追溯数据
使用datax，将数据从前置数据库中增量或者全量采集数据
增量：表有时间字段且数据量大
全量：小表，每天一个分区，每个分区都是全部的数据
写datax配置文件 (alibaba datax github) 不压缩 目标ods库，表，路径要存在
datax:若有多个线程同时采集数据要指定分割的key,一个线程采集则不需要指定 channal
Linux中的换行符是/r windows中换行符是/r/n 换行符改成LF
数据仓库层级之间的导入
大数据是一个行业，岗位多个
围绕主题 根据需求 基于ODS层的表进行开发新表
提交spark任务时时要指明资源量的，所以你要清楚集群规模
集群多大？ 20台服务器 一台服务器40核128G内存 内存是2T左右
人口户籍是采用增量采集，有一个记录数据更新的时间字段
方案都有优缺点：
小表每天进行全量采集，每天生成一个分区，历史数据会定期清理，
或者直接创建非分区表，每天进行数据覆盖，但如果上层数据出现了问题很难进行回溯，我选择了前者
datax在采集数据的时候写一条SQL语句（增量只能做分区表）
数据采集的时候遇到一个问题：有一个数据量很大的表，我在按天进行增量数据采集的时候，这个表有数据更新时间字段，是精确到秒的
所以要对这个字段进行处理，字段中的类型日期之间是-分割的，我要先将日期取出，然后使用replace函数
对-分隔符转换成空格，因为脚本中的日期参数是没有分割符的，总的来说，我就是先用sub_str进行切分，然后使用
replace进行转化，来适应datax脚本中的参数，还有就是遇到数据更新的情况，要对主键进行分组按照时间降序排序取最新的
“where”:“replace(substr(字段，1，10),‘-’,‘’)=‘${ds}'”
MySQL单表上限五百万条
- 围绕个人财产信息主题进行DWD开发 hive spark
简单的进行数据清晰，统一字段名称，一致性脱敏,否则后续表关联时会关联不上
人口户籍表中公民身份证号码进行脱敏后 as id
spark快，离线还是比flink更高效的 DSL和SQL
spark和hive进行整合，代码中开启元数据支持后直接读取hive元数据信息，可以使用hive数据（spark on hive）
但此时不能在本地运行代码，注销local,使用client模式
读取ODS表，处理，写入
注意赋予DWD层的读取权限
val spark:SparkSession=SparkSession
.builder()
.enableHiveSupport()
.getOrElse()
导入隐式转换…
val sellbargain:DataFrame=spark
.table(“表”)
.where($“ds”===“20220803”)
sellbargain.select(upper(md5(%“id”)) as “id”)
脱敏：数字替换成* regrep_replace($“字段”，“\d”,“*”)
从ODS层读取数据处理后保存到DWD层中
ODS层的数据一般不能直接使用 DWD比较公有化,大家都可以使用
ODS层的数据只有读的权限，因为在前置数据库中可能定时会对数据进行清理，一旦出现误操作，难以再次计算
spark-submit --master yarn-client --class 主类名 --jars 指定第三方的包 main所在的jar包名
（这个命令是可以指定资源的，所以我们必须对集群规模有一定的了解）
spark任务的执行脚本
hive用于建表，spark用于计算，因为整合了spark on hive 所以可以使用hive中的数据
spark -sql 的方式进行数据处理
spark-sql --master yarn-client -e “insert overwrite table …”
目标表先创建好
财产信息明细表 每天一个分区，每个分区内都是全量数据，所以在读取ODS层的表的时候不需要添加分区
- 基于已有的DWD表，围绕主题，构建财产信息主题宽表
这个表是以身份证号码作为主键的，一个人只能有一条数据，但是实际情况中一个人可能会有多套房
所以我们将房产信息转换为json格式的数据，类似的处理方式还有公司等信息
表多的时候写SQL更方便进行局部测试
DSL 对于复杂的逻辑更有优势，更灵活
在hive中进行sql测试，两个job一个job就是一个MR 任务
人口户籍表中
牵扯到HSQL的优化
在房产信息表中，我们只显示有房产的人，但是主题表中我们统计的是所有人口的房产有无以及房产信息
一个身份证号会产生重复数据 ，户口迁入迁出都会产生新的数据，在与房产信息表进行关联的时候，数据会变多
所以我对身份证进行去重，对于这次的去重操作中有一个小的sql优化
在关联之前的去重操作中用select id … group by id 代替distinct( )，
这样将同一个key已经分到同一个reduce中,在接下来进行的表关联中不产生shuffle
同样的道理如果在统计数量的时候任然能用sum group by 来代替 count(distinct( ))
分组关联排序都会产生shuffle
spark-sql --conf spark.sql.shuffle.parrtitions=2 本地执行
对于left join 如果右表关联不上的情况下会默认补齐NULL，此处采用了case when函数进行处理
case when b.id is NULL then o else 1 end 新字段名称
一个人有多套房产，将各个房产信息合并成json格式放到一个字段中
name_struct 函数 多个字段组合到一起变成一个字段，列：列值 的形式组成一个字段，非常像json格式
根据id进行分组，count(1)进行计数统计，联合collect_list(name_struct()) 将组内的数据合并成一条数据
这样的话包含了具体的房产信息 to_json(collect_list(name_struct(…))) as info
至此，宽表的构建我们通过身份证号可以查询到哪个人有房，有几套房子，每套房子的具体信息
接下来还要进行新的字段开发
同样的方式将同一个人的公司信息进行合并，因为一个人可以有多个公司
此处，关联的方式我们采用 left join 而不采用inner join
如果使用的是inner join 那么只返还连接字段相等的行，有一些什么都没有的人的信息会消失，违背初衷
但是如果需求改为统计有房有车有公司有社保等什么都有的人，我们采用inner join 倒是一种合理的方式
所以关联方式还是根据需求来选择
我们基于人口户籍表进行 left join 数据量应该是不多不少等于人口数量的 即使右边的表在关联时没有关联上
甚至是没有数据，数据总量依然不会变多，一旦出现数据量增加的情况说明在去重阶段出现问题
- 几个记忆比较深刻的saprk-sql代码优化 问题：
我们采用的spark-sql进行数据分析 默认的并行度：spark.sql.shuffle.partitions=200
此时出现大量的小文件，总共两三百MB的数据量生成了200个小文件
在spark-sql代码中进行 -conf 设置
spark-sql
–master yarn-client
–num-executors=2 \ (公司项目上给到50-100)
–executor-cores=2
–executor-memory=4G
–conf spark.sql.shuffle.partitions=2
-f 构建宽表的sql语句.sql
-d ds=$ds
在spark-sql中进行表关联
大表join小表(10倍左右)，我们可以采用map join 将小表全部加载到内存中进行关联,将小表广播到内存中
使用hint语法在sql中添加设置 /+broadcast(b)/
但是因为广播的表放在内存中运行，对内存也是一种考验
- 为什么spark 比hive 快呢？
针对这个问题我可以结合当初的项目进行回答：
hive在spark-sql中没有问题，但是spark-sql在hive中还是有一些区别的
比如项目中的：name_struct()具体用法
- spark-client（多用于上线前测试） spark-cluster模式
spark-sql不允许用yarn-cluster模式提交，要想用yarn-cluster必须把SQL内容放到代码中进行运行
-
海豚调度：
离线数仓数据采集（T+1）
上调度后每天往数仓DWS层导入数据
原子指标：不能再拆分的度量值
房价
房均价
衍生指标：多少人有房
哪个地区的多少人有房子
维度：地区
粒度：乡镇
13.从人口户籍表中提取人员信息构建人员信息维度，从DW层中取字段组成相应的维表
维度表，一个人只有一条最新的信息
- 指标计算 某地区多少人有房，有公司想对应的比例等
在统计每个人有公司的数量时（这个不上计算有多少家公司，而是统计有公司的人的数量），遇到一个问题，业务想过的问题不能从字面上的意思去猜
这个基于他们构建出来 宽表来进行计算，人口财产信息主题宽表中的1代表为股东的或者为公司法人的
所以在这个指标统计的时候直接进行sum()就可以了，但是具体又牵扯到公司法人是否视为有公司
的人，最后结论是有公司或者为公司法人都视为有公司，所以在sum()中套了case when
就是为股东或者为法人都视为有公司
count(1)表示在乡镇分组后的每个乡镇的总人数，然后进行比例计算
15.将分区过滤写到子查询中然后进行关联
数据准备好，使用Datax将结果推到数据库（Mysql）中供于BI展示
指标计算的结果非常少，放到mysql中小数据量延迟低
接入数据 准本数据 仪表盘
17.数据量 3亿多 100多G
扫一扫
1706
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
