hive03

0x0 hive命令

启动

hive --service metastore #启动元数据库
hive --service hiveserver2 #启动hiveserver2
hiveservices.sh start #通过脚本启动
bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu

hive客户端

bin/hive #启动hive客户端
#可以配置打印表头 见附录4
bin/hive -help
bin/hive -e "select id from student;" # -e 不进入客户端执行sql
bin/hive -f # -f 执行文件内sql 
	touch hivef.sql
		select *from student;
bin/hive -f /opt/module/hive/data/hivef.sql #执行文件
bin/hive -f /opt/module/hive/datas/hivef.sql  > /opt/module/datas/hive_result.txt #结果写入文件
hive> dfs -ls /; #查看dfs文件
bin/hive -hiveconf mapred.reduce.tasks=10; #启动带配置

hive配置

常见的配置设置—不赋值的话可以直接看当前值

set mapreduce.job.queuename;---得到default
alter table person set tblproperties("EXTERNAL"="TRUE")--修改内外部表
set mapreduce.job.reduces=3;--设置分区数用于cluster by---不用时设成-1
set hive.exec.mode.local.auto=true;--本地模式
set mapreduce.job.queuename=hive;--虽然名字是mapreduce，但是实际是你启动客户端时显示的计算引擎，非常好用。
set hive.exec.dynamic.partition.mode=nonstrict;--动态分区
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;--项目里。

0x1 hive高级函数

空字段解决nvl/coal

-- nvl(a,b) 如果a字段为空,则补b
select comm, nvl(comm,-1) as comma from emp;

-- coalesce() 返回第一个不为null的值,用途:多表join取值
select coalesce(comm,empno) as comma from emp;

判断if/case

-- if(条件,值1,值2) 满足条件取值1,不满足条件取值2

-- case [条件] when [触发条件] else [不触发条件] end
case sex when '女' then 1 else 0 end
-- 将每个部门搞出来
select
    ename,
    concat(
        "部门:",
        case deptno
           when 10 then "财务"
           when 20 then "销售"
           when 30 then "研发"
           else "供应链"
           end
    )
 from emp;
-- 求每个部门男女各有多少人
select 
	dept_Id,
	sum(case sex when '男' then 1 else 0 end) as man,
	sum(case sex when '女' then 1 else 0 end) as women
from emp_sex 
group by dept_id ;

列转行炸裂函数

[重点]

==[explode()]=====================
-- 将数组炸开
-- 将其中一行变为set,如果要添加其他字段,需要分组
create table testExp as select collect_set(name) as nameset from person;
-- 将array炸开
select explode(nameset) from testExp;

-- 将文字炸开 注意split(col,',')函数,第一个传入列,第二个传入分隔符
create table testExp as select concat_ws(',',collect_set(name)) as nameset from person; -- jueqian,yangyang,songsong
select * from testExp;
select explode(split(nameset,',')) from testExp;

-- 炸开 可以连用 执行过程从左到右,这种lateral view类似于 join 又类似于笛卡尔积
select movie,category_info,cexie
from movie_info
lateral view explode(split(category,','))tmp as category_info
lateral view explode(split('a,b',','))tmp as cexie;

-- later view的本质类似于join
-- 相当于每个元素与一个炸开的数列进行笛卡尔积
-- 这个展示的其中一个元素的模拟
select movie,tmp.category from movie_info
    join (
    select explode(split(category,',')) as category from movie_info where movie='《疑犯追踪》'
    ) tmp
where movie='《疑犯追踪》';

select movie,category_info
from movie_info
lateral view explode(split(category,','))tmp as category_info;

窗口函数[重]

by整理

-- 建表: 
partitioned by :  分区表 
clustered by   :  分桶表
-- 查询: 
order by :  全局排序  
distribute by : 分区 
sort  by :  分区内排序
cluster by: 分区排序
-- 窗口函数:
partition by : 分区
order by :  排序 
partition by .... order by ... : 分区排序.   
distribute by : 分区
sort by ：排序
distribute by .... sort by ... : 分区排序
--和partition by .... order by效果一模一样。但一般用后者便于区分。

窗口函数内部限定语句

-- 窗口内限定语句
-- 查询前20%时间的订单信息
select name,orderdate,cost,
       collect_list(name) over(partition by name order by orderdate rows between 1 preceding and current row)
from  business;
-- order by 正常是根据过来开始计算的,通过使用行限定作用作用域
-- current row：当前行
-- n preceding：往前n行数据
-- n following：往后n行数据
-- unbounded：起点，
-- unbounded preceding 表示从前面的起点，
-- unbounded following 表示到后面的终点
-- 当前行到上一行 [1,c] 共两行
collect_list(name) over(partition by name order by orderdate rows between 1 preceding and current row);
-- 从前面3行到当前行 [3,2,1,c] 共四行
collect_list(name) over(partition by name order by orderdate rows between 1 preceding and current row);
-- 前面-当前
collect_list(name) over(partition by name order by orderdate rows between unbounded preceding and current row);
-- 当前-以后
collect_list(name) over(partition by name order by orderdate rows between current row and unbounded following);

窗口函数

-- 窗口函数：
sum()
count()
avg()
max()
min()
concat()
concat_ws()
collect_set()
collect_list()
-- 往前第n行数据，是null就取默认值
-- (为null的字段和取不到的数会不会混在一起呢)
lag(col,n,default_val)
-- 往后第n行数据。。。
lead(col,n, default_val)
-- 分区排序后当前窗口第一行数据
first_value(col)
-- 分区排序当前窗口最后一行数据
last_value(col)
select name,orderdate,cost,
       first_value(name) over(partition by name order by orderdate)
from  business;
		
-- 这三个函数无参，
-- 而且必须要order by而且一般从大到小desc。
-- 如按照每门学科成绩排名 
rank() over(partition by subject order by score desc) rk

row_number()
-- 给每条数据编号
-- 无重复编号
-- 这三个函数无参

rank()
-- 考虑并列，总数不会变
-- 有重复编号，但总数不变

dense_rank()
-- 考虑并列，总数会减少
-- 有重复编号，总数变少

NTILE(n)
-- 各个区内编号(1->n)，(区内总数大于n则会有均匀重复编号，小于n就从1到区内总数)编号从1开始，对于每一行，NTILE返回此行编号。注意：n必须为int类型。这4个窗口函数数据集一般要整个分区。

==[分组函数注意]==============
select name,orderdate,cost,
       sum(cost) over(rows between unbounded preceding and current row )
from  business;
-- 开窗要指定范围
-- order by默认开窗大小以前到当前 相当于spark的window 增量

常用函数

-- unix_timestamp -- 日期转变为时间戳
select unix_timestamp(orderdate,'yyyy-MM-dd') from business; -- 将日期转换为时间戳

-- from_unixtime -- 将时间戳变为时间
select from_unixtime(unix_timestamp(orderdate,'yyyy-MM-dd'),'yyyy-MM') from business; 

-- current_date() -- 返回当前日期 2021-02-02
select current_date() from business;

-- current_timestamp -- 返回当前时间戳
select `current_timestamp`(); --2021-02-02 10:18:09.087000000

-- date_format -- 时间格式化
-- date_format(`current_timestamp`(),pattern)
select date_format(current_timestamp(),'yyyy-MM-ss') -- 2021-02-01

--to_date：抽取日期部分
select to_date(date_format(current_timestamp(),'yyyy-MM-dd hh:mm:ss'));

year：获取年   --输入：标准、半标准   返回：年
month：获取月  --输入：标准、半标准   返回：年
day：获取日    --输入：标准、半标准   返回：年
hour：获取时   --输入：标准、半标准   返回：年
minute：获取分 --输入：标准、半标准   返回：年
second：获取秒 --输入：标准、半标准   返回：年
weekofyear：当前时间是一年中的第几周 --输入：标准、半标准   返回：int --不准确需要搭配  year使用
dayofmonth：当前时间是一个月中的第几天--输入：标准、半标准   返回：int --不准确需要搭配  year、month来确定
-- months_between： 两个日期间的月份个数 --输入：标准、半标准，标准、半标准   返回double
-- add_months：日期加减月 --输入：标准、半标准，int   返回：半标准
datediff：两个日期相差的天数 --输入：标准、半标准   返回：int
date_add：日期加天数 --输入：标准、半标准，int   返回：半标准  --- 输入负数也可以
date_sub：日期减天数 --输入：标准、半标准，int   返回：半标准
next_day('2020-06-14','MO') 当前天的下一个周几
--输入：标准、半标准，星期一~日（无视大小写）  返回：半标准 --周一取下一个周一就是7天以后了（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
--取当前周的周一 [重]
date_add(next_day('2020-06-14','MO'),-7)
last_day('2020-06-14') 求当月最后一天日期

==[常用取整函数]==================================
int 正数靠左，负数靠右
round： 不传小数位数表示四舍五入到整数 -1.5->-2   round(值，小数位数)来四舍五入    
ceil：  向上取整  -1,7->-1
floor： 向下取整  -1.1->-2

==[常用字符串操作函数]==================
upper : 转大写
lower : 转小写
length : 长度
substring : substring( '1970-01-01 21:00:00',1,7) 
-- 表示从1到7的字符串  1970-01
replace : 字符替换 
-- [关于时间格式的改变]--
month(replace(month_visit,'/','-'))  
2017/1/22->2017-1-22  这好像也算半标准格式
trim : 前后去空格
lpad : 向左补齐，到指定长度   lpad('abc',11,'def'); defdefdeabc  
rpad : 向右补齐，到指定长度  rpad('abc',11,'def'); abcdefdefde
-- [关于电话号码的脱敏]--
13855364502     
脱敏：rpad(substring(str,1,3),11,'*')  
-- 11值指的是总得时间数
-- 13855364502 或者用concat 
-- 138****4502

=============================================
regexp_replace : SELECT regexp_replace('100-200', '(\\d+)', 'num') ；
-- 使用正则表达式匹配目标字符串，匹配成功后替换！
select str rlike '^AB$'  返回true/false

集合操作

建表定义、切割，load，访问，插入
-- 1.定义：
friends array<string>,
childs  map<string,int>,
address struct<street:string,city:string,mph:int>
-- 2.访问：
select friends[1],childs['xiao song'],address.city --这么看来只有map取值不具有通用性
-- 3.插入或转化：
array(值1,值2,值3...)
named_struct(col1_name,值1,col2_name,值2....)
str_to_map(字符串参数, 分隔符1, 分隔符2)，--分隔1，map之间，分隔2，键值对
array('basketball','read'),str_to_map('xiaoming_1,xiaohong_2',',','_'),named_struct('street','ss','city','bb','mph',90);

--[集合函数]--------------------------
-- size(字段) : 集合中元素的个数
select name,
       size(friends) num_friends,
       size(person.child)
from  person;
-- struct体(理解为对象,不能够size)

-- map_keys(字段) : 返回map中的key            返回一个数组 ：
select name,
       map_keys(child) mapp
from person;
map_keys(str_to_map('zys:18',',',':'))    ["zys"]

map_values（字段）: 返回map中的value       返回一个数组 ： map_values(str_to_map('zys:18',',',':'))  ["18"]

-- str_to_map 将字符串转为map
select str_to_map('adb_1,bvc_2',',','_') --{"adb":"1","bvc":"2"}

-- array_contains(字段，元素) : 判断array中是否包含某个元素
select array_contains(array('a','b','c'),'a'); -- true

-- sort_array : 将array中的元素排序                           
select sort_array(array('e','b','c')); -- ["b","c","e"]

-- 创建高级结构体
select array('a','b','c');
select collect_list(col);
-- str_to_map
select str_to_map('adb_1,bvc_2',',','_') --{"adb":"1","bvc":"2"}
-- named_struct 创建表时指定好

json

json操作--见json typora文档

get_json_object(col,'$....')
对象属性   			$.属性名4
对象属性的数组元素  		$.属性名4[0]
对象属性的对象数组的属性  $.属性名4[0].属性名411
{
	"属性名1":值1,
	"属性名2":{"属性名21":值21，"属性名22":值22},
	"属性名3":["属性名31":值31,"属性名32":值32],
	"属性名4":[{"属性名411":值411，"属性名412":值412},{"属性名421":值421，"属性名422":值422}]
}
想要炸裂json对象数组
首先json数组不是hive里面的array、map。
要自定义udtf才能炸裂

正则表达式

正则操作 --见scala
regexp_replace： SELECT regexp_replace('100-200', '(\\d+)', 'num') 
使用正则表达式匹配目标字符串，匹配成功后替换！
select str rlike '^AB$'  返回true/false

==[示例]===
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
==========
-- ^表示以谁开头
select collect_list(ename) from emp where ename rlike '^A'; -- ["ALLEN","ADAMS"]

-- $表示以谁结束 注意A要放在前面
select collect_list(ename) from emp where ename rlike 'S$'; 
--["JONES","ADAMS","JAMES"]

-- .表示任意字符 出/r/r的任意单个字符
select collect_list(ename) from emp where ename rlike 'ALL.N'; --["ALLEN"]

-- *表示任意个数
-- A+表示字符串中含有A,重复一次或者多次
select collect_list(ename) from emp where ename rlike 'A+';
-- ["ALLEN","WARD","MARTIN","BLAKE","CLARK","ADAMS","JAMES"]

-- [abc] 字符集匹配,匹配包含其中任意一个字符就可以
select collect_list(ename) from emp where ename rlike '[AL]';
-- ["ALLEN","WARD","MARTIN","BLAKE","CLARK","ADAMS","JAMES","MILLER"]

-- ^$空值

-- .*搭配任意 

-- \\A 反向字符集 匹配输入字符串开始的位置(无多行支持)

-- \\d 配置数字 类似[0-9]

-- \\D 匹配任意非数字的字符
select regexp_replace(orderdate,'\\d+','num') from business;

自定义函数[空着]

-- 继承Hive提供的类
 		org.apache.hadoop.hive.ql.udf.generic.GenericUDF  
		org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
-- 实现类中的抽象方法
-- 在hive的命令行窗口创建函数
-- 添加jar
add jar linux_jar_path
-- function
create [temporary] function [dbname.]function_name AS class_name;
-- 在hive的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;

用的不多,看文档吧

0x2 hive优化

fetch抓取

set hive.fetch.task.conversion=more;

mapjoin

set hive.mapjoin.smalltable.filesize=25000000;

groupby的负载均衡

set hive.groupby.skewindata = true
-- 两次join