数据仓库概念及HQL优化你需要知道的点
By: Date: 2022年11月6日 Categories: 程序 标签:,

双十一了,老板们都在忙着看大屏,看单量,看业绩。技术人员都在忙着看监控,看tps,看tp99。随着这些数字不断的变动,代表的是数据量的不断增长。长此以往,这些不同的业务系统,海量的业务数据被隔离出来最终就形成了数据仓库。这些数据经过清洗加工,汇总分析,又为上层的业务系统或决策系统提供服务,因此对于数仓的了解及使用就变的极其的重要。这里简单总结了数据仓库的基础概念以及HQL优化的思路和部分方法例子供参考。

1. 数据仓库

1.1 数仓概念

数据仓库(data warehouse,也称为企业数据仓库)是用于报告和数据分析的系统,被认为是商业智能的核心组件。数据仓库是来自一个或多个不同源的集成数据的中央存储库。数据仓库将当前和历史数据存储在一起,用于为整个企业的员工创建分析报告。

数据仓库具有如下特点:

  • 主题导向:着重将资料按其意义归类至相同的主题区(subject area),因此称为主题导向
  • 集成性:资料来自企业各OLTP系统,在数据仓库中是集成过且一致的。
  • 时间差异性:资料的变动,在数据仓库中是能够被纪录以及追踪变化的,有助于能反映出能随着时间变化的资料轨迹。
  • 不变动性:资料一旦确认写入后是不会被取代或删除的,即使资料是错误的亦同。

1.2 数据仓库的分层

数仓分层可以隔离原始数据,使得分析数据与实际生产数据隔离,实现数据层面的解耦。通过主体建模等使得数据结构清晰,减少重复开发工作,重复的计算,复用计算结果。规范数据分层,把复杂的问题简单化,使用多个层次,每个层次只处理简单的任务,来逐步完成复杂的计算。通常情况下数仓分为ODS,DM以及ADS层。

  1. ODS(Operational Data Store,数据运营层):存放各业务系统抽取的原始数据,并保持原貌不做任何处理。
  2. DM(Data Warehouse,数据仓库层):完成数据加工、整合,实现主题数据的标准化。该层包含DWD,DWS,DIM层。
    1. DWD(Data Warehouse Detail 细节数据层)层:业务层和数据仓库的隔离层,对ODS数据层做一些数据的清洗和规范化的操作。
    2. DWM(Data Warehouse Middle,数据中间层):在DWD层的数据基础上,对数据做轻度的聚合操作,生成一系列的中间表,提升公共指标的复用性,减少重复加工。
    3. DWS(Data Warehouse Service 服务数据层):基于DWD的基础数据,整合汇总成分析某一个主题域的服务数据。
    4. DIM(公共维度层):基于维度建模理念思想,建立一致性维度。
  3. ADS(Application Data Service,数据应用层):为后续的各种业务系统提供查询数据

1.3 模型设计中的表

我们的数仓在模型设计过程中,会常用到几种类型的表:

  1. 拉链表:记录一条数据从开始一直到当前状态的所有变化信息。一条记录一天只有一条变更信息。可以记录数据的完整声明周期,可以快速的还原任意天的历史快照。
    1. 获取最新数据:
      select * from fdm_xx_table_chain where dp='ACTIVE';
      select * from fdm_xx_table_chain where start_date<=sysdate(-1) and end_date>sysdate(-1);
    2. 获取历史数据:
      select * from fdm_xx_table_chain where start_date<='2022-12-01' and end_date >'2022-12-01';
    3. 拉链表不建议使用dt分区,start_date不是分区字段
  2. 流水表:存放用户的每一条变更记录,一条记录一天之内可能有多条变更记录,这里与拉链表不同。
    1. 获取当前日期数据:
      select * from fdm_xx_table where dt='2022-01-02';
    2. 获取2022-01-01 到现在的数据
      select * from fdm_xx_table where dt>='2022-01-01';
  3. 全量表:记录每天所有的最新状态的全量数据,只有一个分区或者没有分区,每次写数都会覆盖之前的数据。
    1. 获取2022-01-01全量数据:
      select * from gdm_xx_table_da where dt='2022-01-01'
    2. 获取最新全量数据:
      select * from gdm_xx_table_da where dt=sysdate(-1)
    3. 注意一定要指定分区
  4. 增量表:记录每天增加的以及更新变化的数据在当前日期的分区中
    1. 增量表默认分区dt,使用与流水表一致
  5. 快照表:记录截止日期的全量数据,每个分区都是记录截止当前分区日期的全量数据,注意使用中的数据的冗余和存储浪费。

2. Hive优化

Hive的两种模式

  1. local model:不会生成MapReduce(初学者本地搭建的模式)
  2. 集群提交的形式

2.1 Hive建表

表的组成:1. 元数据部分(表定义,存储在关系型数据库上,如mysql),2. 数据部分(存储在HDFS上)

建表从文件存储上分为:1. 压缩表,2. 非压缩表
从内外部分类:

  1. 内部表:删除表时,元数据和数据部分全部删除
  2. 外部表:删除表时,只删除元数据部分,HDFS是不删的。(一般都建外部表,防止表被误删除)
    用external关键字指定,就是外部表,没有使用则为内部表,外部表需要指定Location。
CREATE EXTERNAL TABLE IF NOT EXISTS user_login(
    userid BIGINT,
    username STRING
    ip STRING,
    logintime STRING
) COMMENT 'user login table' 
PARTITION BY(dt STRING)
ROT FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/user/hadoop/wh/login';

Hive的日期函数
sysdate(), sysdate(-1) 昨天,sysdate(-2) 前天

2.2 Hive的优化思路:

Hive计算原理:转换成MapReduce任务执行并输出。

优化思路:

  1. 尽量尽早的过滤数据,减少每个阶段的数据量。
  2. 减少任务job的数量
  3. 解决数据倾斜问题:假设输入被分成100个任务,99个任务在1台机器上,另一个任务在1台机器上,那就是倾斜问题。
    数据倾斜的特征:map已经100%,而一个或者部分reduce长时间99%,则可能是因为这个reduce和其他的reduce处理的数据量差别过大,通常达到2-3倍,甚至更多。

2.3 Job优化

列裁剪
查询字段时只选择需要的字段进行查询,节省数据读取的开销。

分区裁剪
查询过程中减少不必要的分区,即使用dt分区字段进行查询。
explain dependency关键字可以获取使用的表以及所要用到的分区。
即查看扫描了多少分区:explain dependency select count(1) from table where dt>='2022-01-01'

用GROUP BY替代DISTINCT进行排重
DISTINCT对数据去重可能会造成数据倾斜,所以尽可能使用GROUP BY代替DISTINCT。

利用Hive机制减少Job数量
三个或三个以上的表关联,如果JOIN ON后关联的字段相同,都会合并成一个MapReduce任务。
如:a join b on a.key=b.key1 join c on b.key1= c.key 会产生1个job。
a join b on a.key=b.key1 join c on b.key2= c.key 会产生两个job。

  • job输入输出优化:同一个的表的union all相当于map一次输出多条,可以使用 如下方式:
    from table
    insert overwrite table tmp1
        select... where 条件1
    insert overwrite table tmp2
        select... where 条件2

避免笛卡尔积
笛卡尔积会将两个表的记录放在一起排列组合所有的情况,所以笛卡尔积会增加计算的数据量,需要避免。

数据过滤
尽量在JOIN前过滤掉不需要的数据,使得计算的数据量变小。
如果是多个表的JOIN,可以使用子查询+过滤条件,减少每一个表的数据量后再进行JOIN。

小表放前大表放后
数据量小的表放在JOIN的前面,因为JOIN左边表的数据会被加载进内存,增加多余的计算。而加载小表占用内存小,减少内存溢出。

mapjoin()
当小表JOIN大表时,在Map阶段JOIN,避免数据倾斜问题,减少Reduce处理的数据量。
SELECT /*+ MAPJOIN(b)*/ a.key,a.value FROM a JOIN b ON a.key = b.key

LEFT SEMI JOIN 左半连接
LEFT SEMI JOIN 是 IN/EXISTS的一种高效实现,可以代替IN及EXISTS。只能查询ON左侧表的字段,即当无需查询ON关键字右侧表字段时可以使用,如下:
SELECT a.key FROM a LEFT SEMI JOIN b ON a.key = b.key;

UNION ALL 优化
小表的话可以先UNION ALL再GROUP等,可减少job数,如:

SELECT * FROM (
    SELECT c1,c2 FROM a 
    UNION ALL
    SELECT c1,c2 FROM b
)
GROUP BY c1,c3

多个UNION ALL优化
如果有多个UNION ALL,则可以新创建临时分区表,将多个表的查询结果写入到不同的分区中,最后再查询这些分区

合理使用动态分区
动态分区会根据具体的值动态的写入相应的分区。
启用hive动态分区,只需要在hive会话中设置两个参数:

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dynamic_table1 partition(dt)
select id, dt from a

合理使用UDTF函数
如跨表跨列的函数,如explode等

排序优化
全局排序ORDER BY是一个Reduce实现,不能并发,效率偏低,也很容易数据倾斜。局部排序SORT BY是单个Reduce有序,通常和DISTRIBUTE BY一起使用;CLUSTER BY col1不能指定排序规则,不常用。

先到这里,本文主要总结了数仓相关的一些概念及HQL优化的一些经验,对于日常需要用到数仓的伙伴们会有些帮助。

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注