hive数据仓库经验()

First_____
173 阅读4分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

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.对数据作清洗(清除一些脏数据,空值)

  1. 对维度表进行维度退化, 尽可能的使用星型模型

  2. 对事实表,可以在维度的角度上做一些宽表化处理

相关技术:

创建表时,可以使用列式存储+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 对于累积型的表

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

avatar
文章
阅读
粉丝