离线数仓的经验 – 源码巴士

ods层

经验:
1.保持数据原貌不做任何修改，起到备份数据的作用
2.创建分区表，防止后续的全表扫描,提高效率
3.可以采用lzo压缩, 减少磁盘空间的使用

相关技术:

CREATE EXTERNAL TABLE ods_event_log(`line` string)
PARTITIONED BY (`dt` string)
STORED AS
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/warehouse/gmall/ods/ods_event_log';

创建lzo压缩索引文件:

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_event_log/dt=2020-03-10

dwd层

经验:
1.对数据作清洗(清除一些脏数据,空值)
2. 对维度表进行维度退化, 尽可能的使用星型模型
3. 对事实表,可以在维度的角度上做一些宽表化处理

相关技术:
创建表时,可以使用列式存储+lzo, 列式存储是支持切片的,可以不创建索引文件

CREATE EXTERNAL TABLE dwd_start_log(
`mid_id` string,
`user_id` string, 
`version_code` string, 
)
PARTITIONED BY (dt string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_start_log/'
TBLPROPERTIES('parquet.compression'='lzo');

创建自定义函数: 可能会需要使用自定义函数

语法: temporary 是创建临时函数 , 没有的话,是创建永久函数

create [temporary] function 函数名别名 as '全类名' using jar 'hdfs://hadoop101:8020/user/hive/jars/hiveFunction-1.0-SNAPSHOT.jar'; #最后一个是自定义函数所存放的位置

注意:如果修改了自定义函数重新生成jar包怎么处理？只需要替换HDFS路径上的旧jar包，然后重启Hive客户端即可。

dwd层的几种表:

维度表: 对现实世界中的物品的抽象描述,为了减少维度层级, 一般会在dwd层对维度表做维度退化 更新方式: 全量
事务性事实表: 指的是一些一旦提交就无法修改的表,比如:退款,订单,支付 更新的方式: 增量更新,每个分区记录的就是当天的数据
周期性事实表: 指的是一些数据量不大, 且会状态发生变化需要修改,间隔周期内的度量统计 更新方式: 一般直接为全量
> 缺点: 存储的数据量大
>解决方案：周期型快照事实表存储的数据比较讲究时效性，时间太久了的意义不大，可以删除以前的数据
累积型事实表: 指的是有着明确的开始和结束状态的过程的生命周期,且数据一般以 开始时间为分区更新方式: 新增+ 变化
拉链表: 用户表中的数据每日既有可能新增，也有可能修改，但修改频率并不高，属于缓慢变化维度，此处采用拉链表存储用户维度数据。更新方式: 也是新增+变化

dws层

说明: 站在维度的角度,对事实表的度量值进行描述

单位是以天为单位的行为数据,进行建宽表

dwt层

说明: 一般都是累积型的表
1.不需要分区,因为都是一些统计好的一些指标
2.不需要压缩,压缩虽然会减少磁盘空间的使用,但是使用需要解压和再压缩,会浪费性能
3. 站在维度的角度, 对事实表的度量值进行分析

sql语句的书写思路:

写SQL的规律：
1、先找到目标表
2、分析一下，需要哪些表能满足目标表的所有字段
即准备所有输入表。
3、写逻辑
3.1 insert overwrite table 目标包名称
观察目标表是否需要考虑分区
3.2 固定格式，如果需要多表join,
先把整体大框写出，再具体去写一个一个子查询
select
from
(
)b1
join
(
)b2
on
3.3 遇到统计什么次数用count
3.4 遇到统计什么金额用sum
3.5 如果是累积表，获取旧表（目标表）再获取新表（输入表）数据如: 累积性快照事实表,dwt层的表
3.6 遇到统计累积值，旧的 + 新的
3.7 累积表中获取首次时间()
旧的时间为null,取当前时间，否则取旧的
3.8 累积表中获取末次时间（最近时间）
新的id不为空，取当前时间，否则取旧的
3.9 天数和次数的转换。
if(new.login_count>0,1,0)
3.10 使用group by时要注意：
查询的字段：1.分组里面有；2.常量；3.聚合函数
3.11 累积30天等指标
在新数据表new中进行累加 where最近30天
3.12 如果涉及的表比较多可以采用with tmp的方法(企业级的用法)
3.13 对于累积型的表

上面为借鉴尚硅谷的经验: 有理解不对, 请指出来,谢谢,共同进步!!!

原文链接：https://blog.csdn.net/First_____/article/details/121505737