Hive
数据处理方法论
数据敏感度?数据思维?
step1 了解产品/运营的需求
- 明确需求的背景
- 解决什么问题
- 是否产生可以量化的价值
- 业务方使用该需求的数据后续会应用到哪些具体的应用
- 特别是涉及到金额类数据,一定要准确(decimal)
- 涉及到哪些指标(可以量化.eg.订单总金额)或者维度(id、性别维度等)、具体的口径信息
数据同学是数据的第一负责人
step2 数据收集(数据准备)
步骤
- 处理需求,明确需要哪些数据
- 获取数据(接口调取、消息队列、爬虫、文件、数据库、数据仓库等等)
注意事项
检查数据是否都有存储或者数据都有落库操作!!!
在工作中要给自己留buffer
step3 数据处理(数据调研)
清洗数据(ETL)
- 缺失值
- 异常值
- 重复值
- 规范化
- 数据类型变化
- 合并数据集等等
业务场景:统计每个用户同时加入购物车并下单的订单金额
数据库中有两个表:
- 购物车表:tableA(uid string, order_amt decimal(10,6))
- 下单表: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
要做好充足的数据调研
注意:
- 一定要细扣,细扣到代码都可以写出来为止
- 邮件周知给相关方
- 工作中做事情要讲究闭环
step4 数据分析
如何确保数据的准确性?!!
- 抽样验证(抽取样例数据和系统对比验证,无法保证100%)
- 汇总验证(选择粗粒度【年、月维度】的维度进行指标验证)
- 对比验证(和历史老模型进行对比验证)
数据决策和业务优化
Hive背景
Hive简介
- Hive是Hadoop大数据仓库Hive。在数据仓库中,SQL是最常用的分析工具。
- Hive本质是一个SQL解析引擎,将SQL语句转译成MR Job,达到快速开发的目的。
- hive的表为纯逻辑表,只是表的定义等,即表的元数据。本质为Hadoop的目录/文件,达到了元数据与数据存储分离的目的。
- hive可之间将结构化的数据文件映射为一张数据库表。
- hive元数据存在MySQL中,为什么没有存放在derby?
* 只支持单线程操作,不支持并发,导致整个的执行效率偏低 - hive的内容是读多写少,不支持对数据的改写和删除(本身性能)
引入原因
- 对存在HDFS上的文件或HBase中的表进行查询时,要手写一堆MapReduce代码
- 对于统计任务,只能由懂MapReduce的程序员才能搞定
- 耗时耗力,更多精力没有有效的释放出来
- Hive基于一个统一的查询分析曾,通过SQL语句的方式对HDFS上的数据进行查询、统计和分析
安装hive注意事项
- 拷贝链接驱动jar包:mysql-connector-java-5.1.49-bin.jar
- 配置hive-site.xml
- 在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的数据类型
注意:要根据数据本身的特点,选择合适的数据类型进行存储
数据类型
- 比如枚举值 0、1建议采用tinyint
- 如果涉及到订单号,uid建议采用bigint、integer
- 涉及到小数,可以使用double、float。准确而言,double
- 涉及到和金钱打交道,建议使用decimal(10,6)!!!
- 时间类型:data、timestamp
- 字符串类型:string
- 复杂类型:maps、structs 常应用于流量埋点日志中,便于解析使用
- 其它类型:常见于0、1,替代True、False
Hive架构
Hive数据管理
Hive中有4种数据模型:内部表、外部表、分区表、分桶
如何区分分区表?
- desc udata_partition;
- show partitions udata_partition;
- show create table udata_partition;
- hadoop fs -ls /usr/hive/warehouse/badou.db/udata_partition
Hive的内部表和外部表
内部表与外部表的创建
- 内部表的创建:
create table [表名]
(默认内部表) - 外部表的创建:
create external table [表名] location 'hdfs_path'
(hdfs_path 必须是文件夹,否则会报错)
面试:内部表和外部表的区别?
- 是否有external 关键字修饰
- 针对内部表、删除内部表会将源数据和数据一起删除;删除外部表的数据,仅仅删除表的元数据,数据不会删除
- 在导入数据到外部表,数据并没有移动到自己的数据仓库目录下,即外部表的数据并非由它自己来管理,而内部表不同。
删除内部表,再创建相同的内部表,会显示数据信息
删除外部表,再创建相同的外部表,显示不了数据信息
内部表和外部表的应用场景
- 内部表:在做ETL逻辑处理的时候,通常使用内部表作为Hive处理的中间表,逻辑计算完之后,表和中间数据一起删除
drop table tmp_name;
create table if not exists tmp_name as
select ..业务逻辑过程...
查看表在Hdfs中的位置:show create table --; describe extended bucket_user;
- 外部表:如担心数据怕被误删除,可以选择外部表;导入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阶段:
- 执行from 加载,进行表的查找与加载
- 执行where过滤,进行条件过滤与筛选
- 执行select查询,进行输出项的筛选
- 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 delimited
fields 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
分区细节
- 一个表可以拥有一个或多个分区,每个分区以文件夹形式单独存在表文件夹目录下
- 表和列名不区分大小写
- 分区以字段形式在表结构中存在,通过describe table命令可查看到字段存在,但该字段仅仅时分区的表示
- 分区由一级、二级设置,一般为一级
- 分区分为动态分区和静态分区
分区常见操作
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';
动态分区
必须操作:
- 打开动态分区模式:
set hive.exec.dynamic.partition=true
- 设置分区模式为非严格模式:
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
应用场景
- 数据抽样
处理大规模数据,尤其载数据挖掘阶段,可用一份数据验证一下代码可否运行成功,进行局部测试 - 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. 提高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
- 没有group by
select user_id, order_dow
from orders
where order_dow='0'
order by user_id
limit 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) b
on a.order_number=b.order_number
分区裁剪:
– 分区裁剪中,当使用外关联时,如果将副表过滤条件写在where后面,则会先全表关联再过滤
需求:周一下单的订单数?
--way 1:先关联,再where过滤(不推荐)
select
count(*) cnt
from (select order_id,order_dow from orders limit 1000) ord
join (select order_id from trains) tra
on ord.order_id=tra.order_id
where order_dow='1'
limit 10;
--way 2:关联同时过滤
select
count(*) cnt
from (select order_id,order_dow from orders limit 1000) ord
join (select order_id from trains) tra
on (ord.order_id=tra.order_id and ord.order_dow='1')
limit 10;
--way 3:先过滤,再关联
select
count(*) cnt
from (select order_id,order_dow from orders where ord.order_dow='1' limit 1000) ord
join (select order_id from trains) tra
on ord.order_id=tra.order_id
limit 10;
Map join:
select /*+ mapjoin(aisles)*/ a.aisle, b.product_id --指定脚本
from aisles a
join products b
on a.aisle_id=b.aisle_id
limit 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 all
select 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'
union
select order_id,user_id from orders where order_dow='1'
) t1
参数配置:set hive.groupby.skewindata=true;
select
add_to_cart_order, count(1) as cnt
from priors
group by add_to_cart_order
limit 10;
Hive 常见数据倾斜:
**背景:**在map端hash数据分配到reduce端的时候,某一个key的数据量远远大于其他key,导致某一个reduce的处理时间较长
**表现:**任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成
万能方法: hive.groupby.skewindata = true
解决方案步骤:
- 进行数据抽样, 确定哪些字段中存在大量的NULL值
select colname, count(*) cnt
from table
group by colname
- 和业务方进行沟通这些数据是否可以直接过滤
- 采取最低成本的优化策略,参数优化
1)set hive.map.aggr=true;
2)set hive.groupby.skewindata=true;
- 参数优化效果不明显,进行代码优化
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,如何体现本身的差异(提高工作效率?)
- 业务理解
- 沟通协作
- 经验和制定复杂的技术方案能力
面试点4:数据RD最核心的三个能力?
- 业务
- 技术
- 细心
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优先级最高