Hive

数据处理方法论

数据敏感度？数据思维？

step1 了解产品/运营的需求

1. 明确需求的背景
2. 解决什么问题
3. 是否产生可以量化的价值
4. 业务方使用该需求的数据后续会应用到哪些具体的应用
5. 特别是涉及到金额类数据，一定要准确（decimal）
6. 涉及到哪些指标（可以量化.eg.订单总金额）或者维度（id、性别维度等）、具体的口径信息
   数据同学是数据的第一负责人

step2 数据收集（数据准备）

步骤

1. 处理需求，明确需要哪些数据
2. 获取数据（接口调取、消息队列、爬虫、文件、数据库、数据仓库等等）

注意事项
检查数据是否都有存储或者数据都有落库操作！！！
在工作中要给自己留buffer

step3 数据处理（数据调研）

清洗数据（ETL）

1. 缺失值
2. 异常值
3. 重复值
4. 规范化
5. 数据类型变化
6. 合并数据集等等

业务场景：统计每个用户同时加入购物车并下单的订单金额
数据库中有两个表：

1. 购物车表：tableA(uid string, order_amt decimal(10,6))
2. 下单表：tableB(uid string, order_amt decimal(10,6))

-- 先调研
select * from tableA limit 10;
select * from tableB limit 10;
select a.uid, sum (a.order_amt) as sum_amt
from tableA a inner join tableB b 
on a.uid=b.uid 
group by a.uid

要做好充足的数据调研
注意：

1. 一定要细扣，细扣到代码都可以写出来为止
2. 邮件周知给相关方
3. 工作中做事情要讲究闭环

step4 数据分析

如何确保数据的准确性？！！

1. 抽样验证（抽取样例数据和系统对比验证，无法保证100%）
2. 汇总验证（选择粗粒度【年、月维度】的维度进行指标验证）
3. 对比验证（和历史老模型进行对比验证）

数据决策和业务优化

Hive背景

Hive简介

1. Hive是Hadoop大数据仓库Hive。在数据仓库中，SQL是最常用的分析工具。
2. Hive本质是一个SQL解析引擎，将SQL语句转译成MR Job，达到快速开发的目的。
3. hive的表为纯逻辑表，只是表的定义等，即表的元数据。本质为Hadoop的目录/文件，达到了元数据与数据存储分离的目的。
4. hive可之间将结构化的数据文件映射为一张数据库表。
5. hive元数据存在MySQL中，为什么没有存放在derby？
   * 只支持单线程操作，不支持并发，导致整个的执行效率偏低
6. hive的内容是读多写少，不支持对数据的改写和删除（本身性能）

引入原因

1. 对存在HDFS上的文件或HBase中的表进行查询时，要手写一堆MapReduce代码
2. 对于统计任务，只能由懂MapReduce的程序员才能搞定
3. 耗时耗力，更多精力没有有效的释放出来
4. Hive基于一个统一的查询分析曾，通过SQL语句的方式对HDFS上的数据进行查询、统计和分析

安装hive注意事项

1. 拷贝链接驱动jar包：mysql-connector-java-5.1.49-bin.jar
2. 配置hive-site.xml
3. 在master节点上安装mysql（并启动）

一些代码

show tables;
selelct * from article_as limit 3;
--查看表的类型
show create tavle article_as;--创建表
create table 'article'('sentence' string);--数据的导入
load data local inpath '/usr/local/src/badou_code/mr/mr_wc/The_Man_of_Property.txt' overwrite into table article;

统计单词个数
源数据：as the reader may as
结果数据：
as 2
the 1
may 1
···

列转行：explode

select
word, count(*) cnt
from 
(select 
explode(split(sentece,' ')) as word 
from article 
) t
group by word
limit 10;

Hive SQL VS SQL

UDF：可以直接用到select语句中，比如做一些数据格式化处理
1. 特点：一进一出，一对一的关系
2. UDAF：多对一的关系，sum max min
3. UDTF：一对多的关系，explode

Hive的数据类型

注意：要根据数据本身的特点，选择合适的数据类型进行存储

数据类型

1. 比如枚举值 0、1建议采用tinyint
2. 如果涉及到订单号，uid建议采用bigint、integer
3. 涉及到小数，可以使用double、float。准确而言，double
4. 涉及到和金钱打交道，建议使用decimal(10,6)!!!
5. 时间类型：data、timestamp
6. 字符串类型：string
7. 复杂类型：maps、structs 常应用于流量埋点日志中，便于解析使用
8. 其它类型：常见于0、1，替代True、False

Hive架构

Hive数据管理

Hive中有4种数据模型：内部表、外部表、分区表、分桶

如何区分分区表？

1. desc udata_partition;
2. show partitions udata_partition;
3. show create table udata_partition;
4. hadoop fs -ls /usr/hive/warehouse/badou.db/udata_partition

Hive的内部表和外部表

内部表与外部表的创建

1. 内部表的创建：create table [表名]（默认内部表）
2. 外部表的创建：create external table [表名] location 'hdfs_path'（hdfs_path 必须是文件夹，否则会报错）

面试：内部表和外部表的区别？

1. 是否有external 关键字修饰
2. 针对内部表、删除内部表会将源数据和数据一起删除；删除外部表的数据，仅仅删除表的元数据，数据不会删除
3. 在导入数据到外部表，数据并没有移动到自己的数据仓库目录下，即外部表的数据并非由它自己来管理，而内部表不同。

删除内部表，再创建相同的内部表，会显示数据信息
删除外部表，再创建相同的外部表，显示不了数据信息

内部表和外部表的应用场景

1. 内部表：在做ETL逻辑处理的时候，通常使用内部表作为Hive处理的中间表，逻辑计算完之后，表和中间数据一起删除

drop table tmp_name;
create table if not exists tmp_name as
select ..业务逻辑过程...

查看表在Hdfs中的位置：show create table --; describe extended bucket_user;

2. 外部表：如担心数据怕被误删除，可以选择外部表；导入hdfs中的源数据

Hive建表方法

**注意：**建表时一般不建议使用关键字作为列名、如果非要需要使用``修饰

CREATE TABLE `udata`(`user_id` string, `item_id` string, `rating` string, `timestamp` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;

way1. 直接建表法
应用场景：
1. 用户可以自定义
2. 数据量大，建议使用orc存储；如果是维度表，可以使用text

create table movies (uid string,iids string,score string，ts string）
ROW FORMAT DELIMITED FIELDS
TERMINATED BY '\t' 
LINES TERMINATED BY '\n';

way2. 抽取（as）建表法
应用场景：
1. 只关心表的结构，而数据不复制

create table article_as as select * from article;

way3. like建表
应用场景：
1. 只关心表的结构，而数据不复制

create table article_like like article;

Hive表执行顺序

MR程序的执行顺序：
map阶段：

1. 执行from 加载，进行表的查找与加载
2. 执行where过滤，进行条件过滤与筛选
3. 执行select查询，进行输出项的筛选
4. map端文件合并，reduce阶段：map端本地溢出写文件的合并操作，每个map最终形成一个临时文件。然后按列映射到对应的reduce阶段。

Hive表执行顺序：
Reduce阶段：
1 . group by，对Map端发送过来的数据进行分组并计算
5. having，最后过滤列用于输出结果
6. order by：排序后进行结构输出到HDFS文件

Hive分区

分区表
分区表： 业界常使用dt、d作为分区字段
本质：减少数据查询范围，提高数据查询效率
常见分区表格式：XXXX-XX-XX

select *
from tmp
where to_data('data')='2023-01-01' and hour=12;--无分区
where dt='2023-01-01';--有分区

多级分区场景

  create table `udata_partition`(`user_id` string,`item_id` string,`rating` int)partitioned by (`dt` string)row format delimitedfields terminated by '\t'lines terminated by '\n';
show partitions udata_partition;--展示时间

多级分区场景：存储一些流量埋点日志信息
dt=1970-01-11（一级分区）/hour=11（二级分区）
dt=1970-01-11/hour=12
dt=1970-01-11/hour=13

分区细节

1. 一个表可以拥有一个或多个分区，每个分区以文件夹形式单独存在表文件夹目录下
2. 表和列名不区分大小写
3. 分区以字段形式在表结构中存在，通过describe table命令可查看到字段存在，但该字段仅仅时分区的表示
4. 分区由一级、二级设置，一般为一级
5. 分区分为动态分区和静态分区

分区常见操作

show partitions 表名;--显示分区--增加单个分区
alter table 表名 add partition(分区字段名称=实际的分区值)
alter tavle part1 add partition(country='UN');--增加多个分区
alter table 表名 add partition(分区字段名称=实际的分区值),partition(分区字段名称=实际的分区值);
alter table part1 add partition(country='UK'),partition(country='UN');--增加分区的同时增加数据
alter table 表名add partition(分区字段名称=实际分区的值） location 'HDFS路径';
--ps:location后面需要时存储数据的路径文件夹而非文件
alter table part1 add partition(country='UK') location '/user/hive';--删除单个分区
alter table 表名 drop partition(分区字段名=实际分区名);
slter table part1 drop partition(country='UK');--删除多分区
alter table 表名 drop partition(分区字段名=实际分区名), partition(分区字段名=实际分区名);

Hive动态分区和静态分区

静态分区
应用场景：每次load data都要操作，非常繁琐
优点：如果数据量不大，同时知道分区的数据类型可以这样操作

--将udata数据196用户插入udata_partition里面
insert overwrite table udata_partition partition(dt='2023-02-17')
select user_id, item_id, rating from udata where user_id='196';--将udata数据186用户插入到udata_partition里面
insert overwrite table udata_partition partition(dt='2023-02-18')
select user_id,item_id,rating from udata where user_id='186';

动态分区
必须操作：

1. 打开动态分区模式：set hive.exec.dynamic.partition=true
2. 设置分区模式为非严格模式：set hive.exec.dynamic.partition.mode=nonstrict
   应用场景：在插入数据时，不确定分区数量且分区数量不是特别大的时候（注意：退出终端后set失效）

删除指定分区：alter table udata_partition drop partition(dt='1970-01-11');
插入分区：insert overwrite table udata_partition partition(dt)

--具体时间形式
select from_unixtime(cast(881250949/1000 as bigint),'yyyy-MM-dd HH:mm:ss') from udata;
--具体data形式
select to_date(from_unixtime(cast(881250949/1000 as bigint),'yyyy-MM-dd HH:mm:ss')) from udata;--插入指定分区
insert overwrite table udata_partition partition(dt)
select user_id,item_id,rating,to_date(from_unixtime(cast(`timestamp`/1000 as bigint),'yyyy-MM-dd HH:mm:ss'))  from udata where user_id='305';

二者选择：工作中常用动态分区

Hive分桶

本质：是让数据查询发生在更小的范围内
Hive计算桶列的hash值，再以桶的个数取模计算，判断哪条数据应该属于哪个桶中
user_id order_id order_amt
196 1000110 10
22 1900000 30
21 1809999 40

我现在有3个桶，0、1、2 196%3=1 22%3=1 21%3=0
应用场景

1. 数据抽样
   处理大规模数据，尤其载数据挖掘阶段，可用一份数据验证一下代码可否运行成功，进行局部测试
2. map-side join
   可以获得更高查询处理效率。桶为表加上额外的结构，Hive在处理有些查询时能利用这个结构。

Hive分桶的创建

参数设置： set hive.enforce.bucketing = true
会自动根据bucket个数自动分配Reduce task个数，reduce个数和bucket个数一样
reduce 有多少个，就有多少个文件
创建：

--创建表create table bucket_user (id int) clustered by (id) into 4 buckets;--分桶字段一定是表中有的字段--分区字段一定是表中没有的字段--插入insert overwrite table bucket_user select cast(user_id as int) from udata;

查看表的分桶：hadoop fs -ls /hive/warehouse/bucket_user

数据抽样：select * from bucket_user tablesample(bucket 1 out of 16 on id) limit 10;
tablesample为抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y);
–x 表示从第几个分桶进行抽样，y每隔几个分桶取一个分桶。
–y必须时table总Bucket数的倍数或者因子。
总结
Hive分桶的概念即为MapReduce的分区概念，两者完全相同。
物理上每个同就是目录的一个文件，一个作业产生的同（输出文件）数量和reduce任务个数相同。分区代表了数据的仓库，即文件夹目录。每个文件夹下面可放不同的数据文件，通过文件夹可以查询里面存放的文件。

Hive优化

一 减少查询数据量

1. 分区表优化
2. 桶表优化
   分桶规则：对分桶字段进行哈希，哈希值除以桶的个数求余，余数决定了该条记录在哪个桶，也就是玉树相同的在一个桶中。
   优点：1. 提高join查询效率； 2. 提高抽样效率

二 压缩数据

列裁剪、数据存储

textfile:存储数据不压缩、查询效率低 ——行存储
常见维度表
orc 存储数据压缩，查询效率高 ——列存储
场景：数据量大，常见事实表

面试点： 行存储和列存储区别？ （记下来）
1. 行存储写入一次性完成，消耗时间。数据产出过程中
2. 列存储写入和完整性不如行。存储

Map的优化

–作业会通过input的目录产生一个或多个map任务。set dfs.block.size
– Map越多越好嘛？是不是保证每个map处理接近文件快的大小？
– 如何合并小文件，减少map数？
– 如何适当增加map数？
set mapred.map.tasks =10;
– Map端聚合
set hive.map.aggr=true;类似于mr中的combiner.

Reduce的优化

– hive.exec.reducers.bytes.per.reducer；reduce任务处理的数据量
– 调整reduce的个数：
* 设置reduce处理的数据量
* set mapred.reduce.tasks = 10

一个Reduce

1. 没有group by

select user_id, order_dow 
from orderswhere order_dow='0'order by user_idlimit 10;

number of mappers: 1; number of reducers: 1
2. order by(可用distribute by 和sort by)
3. 笛卡尔积

 select *from tmp_d a join (select * from tmp_d) bon a.order_number=b.order_number

分区裁剪:

– 分区裁剪中，当使用外关联时，如果将副表过滤条件写在where后面，则会先全表关联再过滤

需求：周一下单的订单数？

--way 1：先关联，再where过滤（不推荐）selectcount(*) cntfrom (select order_id,order_dow from orders limit 1000) ordjoin (select order_id from trains) traon ord.order_id=tra.order_idwhere order_dow='1'limit 10;--way 2：关联同时过滤selectcount(*) cntfrom (select order_id,order_dow from orders limit 1000) ordjoin (select order_id from trains) traon (ord.order_id=tra.order_id and ord.order_dow='1')limit 10;--way 3：先过滤，再关联selectcount(*) cntfrom (select order_id,order_dow from orders where ord.order_dow='1' limit 1000) ordjoin (select order_id from trains) traon ord.order_id=tra.order_idlimit 10;

Map join:

 select /*+ mapjoin(aisles)*/ a.aisle, b.product_id --指定脚本from aisles ajoin products bon a.aisle_id=b.aisle_idlimit 10;

Union all/distinct

--way 1:select count(*) cnt from(select distinct *from (select order_id,user_id from orders where order_dow='0'union allselect order_id,user_id from orders where order_dow='1') t) t1--way 2:
select count(*) cnt from(select order_id,user_id from orders where order_dow='0'unionselect order_id,user_id from orders where order_dow='1') t1

参数配置：set hive.groupby.skewindata=true;

 select add_to_cart_order, count(1) as cntfrom priorsgroup by add_to_cart_orderlimit 10;

Hive 常见数据倾斜：

**背景：**在map端hash数据分配到reduce端的时候，某一个key的数据量远远大于其他key，导致某一个reduce的处理时间较长
**表现：**任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成

万能方法： hive.groupby.skewindata = true
解决方案步骤：

1. 进行数据抽样, 确定哪些字段中存在大量的NULL值

select colname, count(*) cntfrom table group by colname

2. 和业务方进行沟通这些数据是否可以直接过滤
3. 采取最低成本的优化策略，参数优化
   1）set hive.map.aggr=true;
   2）set hive.groupby.skewindata=true;
4. 参数优化效果不明显，进行代码优化
   1）case when uid is null then cast(rand()*100000 as int) else uid end --随机产生0-10000的数据，随机进行填充
   2）将异常值单独拿出来处理，最后再union回去（正常的数据处理+异常数据处理，然后union）
   3）如果代码中涉及到count distinct 可以进行job的拆分。先distinct 再count

注意：
在书写代码逻辑的时候，要注意列裁剪，数据的提前过滤，以及在关联过程中避免产生笛卡尔积，保证在代码不会存在问题的情况下，再去排查数据的问题！！！

/*+ STREAMTABLE(a) */ 指定大表

select /*+ STREAMTABLE(pri) */ ord.order_id
from orders ord
inner join priors pri
on ord.order_id=pri.order_id
limit 10;

Hive join优化——表连接顺序

注意数据在关联时候过滤
Hive join优化 - 并行执行 set hive.exec.parallel=true;

数据倾斜！！！面试频率高

数据倾斜——大大表关联【面试点】

on case when (x.uid = '-' or x.uid = '0' or x.uid is null) 
then concat('-', rand())
else x.uid end= f.user_id;

数据倾斜——空间换时间
同一个reduce上进行distinct操作压力很大

面试点

面试点1：如何快速知道一个表的特性？
show create table table_name：是否为分区表？是否为维度表？数据存储的路径
desc table_name：表的结构
desc extended table_name：
快速定位表的主键是否重复？count(order_id)、count(distinct order_id)如果相等，则不重复
show partitions table_name; 分区枚举值
确定数据的形式，数据是全量分区还是增量分区

现在有一张分区表

select dt, count(*) cnt from table_name 
where dt >='2023-01-01'
group by dt
order by cnt desc;

会得到两种结果：
该表是全量分区的数据 T，我和其他表进行关联的时候，限制where dt ='T'
2023-01-01 20000
2023-01-02 19000
2023-01-01 18900

该表数据是增量分区数据，where d between 'T-6' and 'T'有大有小
2023-01-03 20000
2023-01-02 21000
2023-01-01 18900
面试点2：如何对数据进行去重？
方向一：group by
方向二：distinct
方向三：开窗函数 row_number() over(partition by col1 order by col2)

面试点3：数据RD，如何体现本身的差异（提高工作效率？）

1. 业务理解
2. 沟通协作
3. 经验和制定复杂的技术方案能力

面试点4：数据RD最核心的三个能力？

1. 业务
2. 技术
3. 细心

SQL参数分析

df -h：查看磁盘使用情况
du -sh *：查看当前磁盘使用情况

select distinct order_dow from orders

set hive.exec.reducers.bytes.per.reducer=<number> 每个reduce处理的byte数量
set hive.exec.reducers.bytes.per.reducer=20000;

select user_id, count(1) as ord_cnt
from orders
group by user_id
limit 10;

set hive.exec.reducers.max=<number> 设置reduce最大的值
set mapreduce.job.reduces=<number> 指定reduce的个数
number of mappers: 1; number of reducers: 5

number of mappers: 1; number of reducers: 15

总结：mapreduce.job.reduces优先级最高