数据仓库概念及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优化的一些经验，对于日常需要用到数仓的伙伴们会有些帮助。

发表回复

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