hive date_format函数 yyyyMMdd hive date_format函数秒

Hive_Function_调优

1.Function分为两种：

1.内置function

2.udf函数

一，内置函数：

2.内置函数：

upper lower

查看内置函数的命令：

show functions like upper;

desc function extended upper; 函数的详细使用

3.时间函数

1.current_date  => 打印当前时间  2022-12-28  yyyy-MM-dd 
2.current_timestamp => 打印当前时间戳 2022-12-28 09:57:02.319
3.unix_timestamp ** => 日期转成 秒值【1970】 
	1.打印当前时间的时间戳 1672221664 秒值 long
	2.传入一个dt =》 秒值
		eg： 
			unix_timestamp('2022-12-28 00:00:00');
			unix_timestamp('2022-12-28','yyyy-MM-dd');
4.from_unixtime **    => 秒值  =》 日期格式 
5.to_date  => 年月日 
6.year => 年
	year、quarter、month day hour minute

	季度：q1 q2 q3 q4 
7.months_between 、add_months、datediff ** 
8.date_add、 date_sub、last_day =》
9.date_format  日期格式  ***

4.算术相关函数

1.round 四舍五入

2.ceil 向上取整

3.floor 向下取整

4.rand（）

5.字符串相关函数

upper
lower 
length
trim
lpad 左边补全 
rpad 右边补齐
replace/ regexp_replace
substr 字符串截取 
concat 字符串拼接 
concat_ws 字符串拼接  
split 字符串分割

6.json数据处理

1.get_json_object  ok 
	2.json_tuple  首选
	3.udf函数

create table move_json(
json string
);

load data local inpath “/home/hadoop/tmp/json/move_json” into table move_json;

{“movie”:“1836”,“rate”:“5”,“time”:“978300172”,“userid”:“1”}
电影编号 评分 打分时间 打分用户id

往往需要json数据解析出来：
1.普通的json
2.嵌套json：
1.数组 ：
多个元素+struct
2.struct：
kv
3.不规则json : udf

json_tuple

select
json_tuple(json,‘movie’,‘rate’,‘time’,‘userid’) as (movie,rate,time,userid)
from move_json;

select
get_json_object(json,‘$.movie’) as movie
from move_json limit 10;

{“store”:{“fruit”:[{“weight”:8,“type”:“apple”},{“weight”:9,“type”:“pear”}],“bicycle”:{“price”:19.95,“color”:“red”}},“email”:“amy@only_for_json_udf_test.net”,“owner”:“amy”}

create table json02(
json string
);

select
get_json_object(json,‘$.store.bicycle.price’) as movie
from json02 limit 10;

2.parse_url_tuple  => 解析url会用到 
	https://cwiki.apache.org/course?a=b&c=d 
	协议 domain/hostname  path 参数 
	select parse_url_tuple('https://cwiki.apache.org/course?a=b&c=d',"HOST","PATH","QUERY")

7.判断类函数

1.case when  
		when 
		else 
end  

2.if()

1,zihang,QT,男
2,子浩,QT,男
3,海哥,QT,男
4,祖安,QA,男
5,db,HR,男
6,张三,HR,男
7,梦珂,HR,女
8,奕乐,QA,女

create table emp_info(
id string,
name string,
dept string,
gender string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY “,”;
load data local inpath “/home/hadoop/tmp/json/emp_info.txt” into table emp_info;

需求：
每个部门 男女生各有多少人？

select
dept,
sum(case when gender=‘男’ then 1 else 0 end ) as male_cnt,
sum(case when gender=‘女’ then 1 else 0 end ) as female_cnt
from emp_info
group by dept ;

select
dept,
sum(if(gender=‘男’,1,0) ) as male_cnt,
sum(if(gender=‘女’,1,0) ) as female_cnt
from emp_info
group by dept ;

二，udf函数

hive、sparksql。。。

内置函数：不能够解决某些业务场景

udf 一进一出 upper lower ：
1.extends UDF
2.实现 evaluate

udaf 多进一出 sum max
udtf 一进多出 explode

udf 一进一出 upper lower ：
1.extends UDF
2.实现 evaluate

3.使用： 
	1.打包上传服务器 

	1.临时函数 【推荐】
		1.add jar /home/hadoop/lib/dl2262-hive-udf-1.0.jar;
		2.create temporary function hello_udf as 'com.dl2262.UDFHello'; 

		注意： 
			仅仅是当前session有效

		另外一种使用方式；
			1.把自己开发jar =》 hive目录下 auxlib下面 
				add jar  =》 可以省略 
					HIVE_AUX_JARS_PATH

	2.永久函数【不建议使用】
		CREATE FUNCTION [db_name.]function_name AS class_name

[USING JAR|FILE|ARCHIVE ‘file_uri’ [, JAR|FILE|ARCHIVE ‘file_uri’] ];

create function hello_udf02 as ‘com.dl2262.UDFHello’ USING JAR ‘hdfs://bigdata32:9000/udf-lib/dl2262-hive-udf-1.0.jar’;

补充：

1.hive -e "sql"
 hive  -f xx.sql  [推荐]

2.hive -i xx.sql  [hive session 初始化用的] 【推荐】
	hive -i -f

三，调优

场景：如下问题：

谈谈你再工作中针对SQL(hive、sparksql…)的优化 ，做的好的3个点？

1.结合场景 + 数据量： 
	调优前  vs 调优后 的对比情况 

2.常见的调优点： groupby  join  count(distinct ) =》 shuffle =》 数据倾斜 
	shuffle：根据 map输出的key进行数据分发 各自的reducer上去的 
	数据倾斜 =》 倾斜在key上 
3.没有一劳永逸的调优

1.fetch抓取策略

符合fetch抓取策略的 不走mr作业

2.本地化执行

hive.exec.mode.local.auto  =》 开关

hive.exec.mode.local.auto.inputbytes.max =》 按照加载的数据量
hive.exec.mode.local.auto.tasks.max =》按照task
hive.exec.mode.local.auto.input.files.max =》按照文件加载的个数

3.模式限制
hive.mapred.mode

4.推测式执行
1.假设 node1 node2 node3
机器上运行我们的task

2.假设node3机器负载比较高 
	场景1： 
		某个时刻、某个节点 负载比较高： cpu使用率比较高、mem被别的作业占用比较多 
	
	思考： 
		task1 和task2 很快运行完成了 ，但由于 node3负载高 ，task3运行比较慢 

		=》 “木桶效应/短板效应 ”
			“桶中能装多少睡是由最短的这块板来决定的 ”
		
		大数据里：一个job跑完需要耗费多长时间 由最慢的一个task来决定的 

	hadoop{mR}
	hive 
	spark.... 
	内部都有一个机制：推测式执行  去解决这个问题

推测式：
1.node3节点上task 跑了 “一定时间”
会在另外一个节点上页启动一个相同的task
【node5】

这个task3 同时运行在node3节点 node5上 ，谁先跑完 就采用谁的结构 同时kill掉另外一个task

mapreduce：
mapreduce.map.speculative
mapreduce.reduce.speculative

hive：
hive.mapred.reduce.tasks.speculative.execution

新的场景2： 
	map端   
		
	reduce端 ： reduce task key不够分散【skew】=》数据倾斜 如何解决？

5.裁剪 【了解】
1.hive.optimize.cp =》 是否开启列裁剪

eg： table：emp   ,columns: a,b,c,d,e 
	数据存储格式：行式存储

	select a,b,c from emp ;
	查询只需要3个字段  
		1. 行式存储  文件无法进行列裁剪  一定是加载全部表中字段 
		2. 列式存储 ： orc、parquet  列式存储的文件 可以进行列裁剪的 
			节省 网络io和磁盘io

2.hive.optimize.ppd 谓词下压 =》 节省 网络io和磁盘io 【减少首次加载的数据量】
谓词： 条件相关的东西 where on
下压：取数据从数据源头取数

select a,b,c from emp where id=10;
table : 列式存储：   where id=10

select
count(1),
deptno
from emp
where deptno=20
group by deptno;

select
from
(
select
count(1),
deptno
from emp
group by deptno;
)
where deptno =20;

6.设置 map task数量 & reduce task 数量

1.设置 map task 数量
map task 个数 取决于 切片的个数
切片的个数： 文件的个数、文件大小 、blocksize 、是否可以被切分
FileInputFormat：
getSplits：
computeSplitSize：

mapreduce.input.fileinputformat.split.minsize
	mapreduce.input.fileinputformat.split.maxsize

1.一个目录，就一个文件 
	a文件：800M  =》 切分成几个切片？ 7个map task 处理 
		6.25 = 6 
		32  128*10%  

2.一个目录。多个文件 
	a:20m  1map 
	b:20m  1map 
	c:140m   2map task 
	=>  4 map task处理 

Q1: map task数量越多越好吗 ？ 
	1.越多可能有什么问题？ 
		1.mapreduce作业：进程级别 task =》 jvm

2.设置reduce task个数
reduce 个数 决定了 最终文件的个数

set hive.exec.reducers.bytes.per.reducer=
set hive.exec.reducers.max=
set mapreduce.job.reduces=

hive reduce task个数 如何计算的？
1.用户指定 ：
set mapreduce.job.reduces =》 reduce task个数
2.用户设置参数 hive引擎自己去算
set hive.exec.reducers.bytes.per.reducer =》 每个reduce 处理的数据量
set hive.exec.reducers.max =》 reduce task最多有多少个

set mapreduce.job.reduces=3;
create table res as select deptno,count(1) from emp_tmp group by deptno;

reduce数据设置？
按照你的作业复杂度 最终输出文件大小来确定 => 小文件问题

hive 小文件合并问题？
Q1: 为什么说hdfs 不适合处理/存储小文件?
1.存储角度：nn压力
2.处理角度：一个小文件对应一个task 处理

Q2： 如何合并小文件 ？ 
		1. hdfs api 来完成 =》  行式存储  
			github.com 
		2.hive 里面支持 DDL 
			Alter Table/Partition Concatenate =》 合并小文件命令 
				前提：table 存储 orc存储 =》 列式存储

create table stu_info(id int,name string,age int);

insert into table stu_info values(1,‘zhangsan1’,30),(2,‘lisi2’,31);
insert into table stu_info values(2,‘zhangsan2’,30),(3,‘lisi3’,31);
insert into table stu_info values(3,‘zhangsan3’,30),(4,‘lisi4’,31);
insert into table stu_info values(4,‘zhangsan4’,30),(5,‘lisi5’,31);

create table stu_info_orc(id int,name string,age int) stored as orc;
insert into table stu_info_orc values(1,‘zhangsan1’,30),(2,‘lisi2’,31);
insert into table stu_info_orc values(2,‘zhangsan2’,30),(3,‘lisi3’,31);
insert into table stu_info_orc values(3,‘zhangsan3’,30),(4,‘lisi4’,31);
insert into table stu_info_orc values(4,‘zhangsan4’,30),(5,‘lisi5’,31);
alter table stu_info_orc concatenate;

7.并行执行
mr： 可以以 chain方式去运行 job
mr1=》mr2 => mr3 [explain]

hive : sql =>mr  
	sql的复杂度 不一样 =》 翻译成 mr的个数 以及依赖 都不一样的

EXPLAIN： 查看sql 执行计划
1.The Abstract Syntax Tree for the query 【sql的语法树 不显示了】
2.The dependencies between the different stages of the plan 【一个job 分为多个stage】
3.The description of each of the stages 【每个stage详细信息】

join：

=》mr

explain
select
a.,
b.
from user_click as a left join product_info as b
on a.product_id=b.product_id;

hive : sql =>mr
sql的复杂度 不一样 =》 翻译成 mr的个数 以及依赖 都不一样的

即： 
		一个sql 可以转换成多个mr job 
			1.可能是串行执行 
			2.也可能是并行执行

mr1=》mr2 => mr3

mr1 =>  
		mr3
mr2 => 

假如sql =》 4个job  
	1 2 3 job是没有任何依赖关系的  
		前提：资源够 并行跑 一般性能会好一些的

1.并行执行的参数
hive.exec.parallel =》 false 开关
hive.exec.parallel.thread.number =》 运行并行跑的job数

eg：
emp e join dept d on
加载数据： e d 可以并行跑的
join

8.数据倾斜

1.什么是数据倾斜？
wc：
map输出：(word,1)
shuffle:
按照分区规则 对map端输出的数据进行重新分发
reduce：输入
(word,<1,1,1,1>)

RT2和RT3 处理的数据的时间会很短 
	RT1 待处理的数据量过多【数据倾斜】，可能会导致什么问题？ 
		1.处理速度慢 能跑完 
		2.由于数据倾斜  跑不完 【99%】

	数据倾斜？ 
		就是由于某个或者某几个 key对应的数据量过大 从而导致对应的task处理非常慢 或者运行报错

导致的原因？
1.只有shuffle才有可能导致数据倾斜

join、groupby 、count(distinct)

解决：
1.group by

map端输出的某些key 数据量过大  =》 skew 【数据倾斜 】

eg： 
	select deptno from emp group by  deptno;

解决思想： 
	"先打散，再聚合" 

	hive: 
		1.参数来解决  
			hive.groupby.skewindata  => sql处于数据倾斜是 会优化 groupby  
			hive.map.aggr  => map端开启一个聚合 

		2.不通过参数，用户自己定义的 udf函数  
			1.一个mr作业： 
				两个mr作业 解决数据倾斜的问题

			udf： 
				add_suffix
				pre_suffix

select
pre_suffix(n_deptno) as pre_deptno,
sum(cnt) as cnt_sum
from
(
select
add_suffix(deptno) as n_deptno,
count(1) as cnt
from emp
group by add_suffix(deptno)
) as as
group by pre_suffix(n_deptno);

2.count(distinct)

select count(distinct deptno) from emp_tmp ;  
	distinct => job只有一个 task 只有一个reduce task来完成 =》 必然导致数据倾斜问题

设置reduce 个数 是没有用的 

去重： distinct  性能不行 

解决： 
	group by

select
deptno
from bigdata_hive.emp_tmp
group by deptno;

select
count(1) as cnt
from
(
select
deptno
from emp_tmp
group by deptno
) as a ;

3.join
1.shuffle join/ mapreduce join 普通join
2.map join =》 不会导致数据倾斜

1.普通join 导致数据倾斜 如何解决？ 工作遇到了 可能low怎么解决

2. 直接抽取 skew数据
   sql：
   2个sql ：
   1sql =》 skew key join
   2sql =》 no skew key join

a 表
b 表

得有一个表是小表

1.map join：
1.参数设置
set hive.auto.convert.join=true;
set hive.auto.convert.join=false;
select
a.,
b.
from user_click as a left join product_info as b
on a.product_id=b.product_id
limit 2;
2.hints写法： sparksql 也有这种写法 好用的

set hive.auto.convert.join=true;
select /+ MAPJOIN(product_info) /
a.,
b.
from user_click as a left join product_info as b
on a.product_id=b.product_id
limit 2;