Hive高级操作-摩杜云开发者社区

hive的数据类型

1.原子类型

整型：tinyint smallint int bigint

浮点型：float double

布尔：boolean

字符串：string

时间戳类型：timestamp

2.复杂数据类型

2.1array数组

类似于java中的array,单值存储多个元素的,每一个元素一个值

id  name    score 
1   zs  300,295,310,410
2   ls  400,402,426
3   ww  426,500,495

建表
id int   name string    score array
指定数组元素之间的分隔符  collection items terminated by 
create table if not exists test_array(id int,name string,score array<int>) row format delimited fields terminated by "\t" collection items terminated by ",";

加载数据
load data local inpath "/home/hadoop/hive_data/test_array" into table test_array;

查询
test_array.id   test_array.name test_array.score
1       zs      [300,295,310,410]
2       ls      [400,402,426]
3       ww      [426,500,495]

查询某一个元素
select id,name,score[0] from test_array; 
select id,name,score[3] from test_array;

2.2map集合

类似于java中的hashmap是双值的，每一个元素都是k-v

数据：
name    score
huangbo yuwen:80,shuxue:89,yingyu:95
xuzheng yuwen:70,shuxue:65,yingyu:81
wangbaoqiang yuwen:75,shuxue:100,yingyu:75

建表
name-string    score-map<string,int>
指定集合元素分割符  collection items terminated by "," 
指定 k-v之间的分割符 map keys terminated by ":"
create table if not exists test_map(name string,score map<string,int>) row format delimited fields terminated by " " collection items terminated by "," map keys terminated by ":";

加载数据 
load data local inpath "/home/hadoop/hive_data/test_map" into table test_map;

查询
select * from test_map;
huangbo {"yuwen":80,"shuxue":89,"yingyu":95}
xuzheng {"yuwen":70,"shuxue":65,"yingyu":81}
wangbaoqiang    {"yuwen":75,"shuxue":100,"yingyu":75}

查询语文成绩
通过key查询value的值 
select name,score["yuwen"] from test_map;

2.3struct结构体

类似于java中自定义类,对象类型的。每一个对象属性是完全一致的是规整的格式每一行的每一小列标表示的含义一致的。

数据：
id  info 
1 zs,12,f
2 ls,15,m
3 ww,12,f

struct 声明
    struct<name:string,age:int,sex:string>
每一行  相当于创建了一个  struct声明的对象

建表 
每一个属性之间的分隔符 collection items terminated by 
create table if not exists test_struct(id int,info struct<name:string,age:int,sex:string>) row format delimited fields terminated by " " collection items terminated by ",";

数据加载  
load data local inpath "/home/hadoop/hive_data/test_struct" into table test_struct;

查询 
select * from test_struct;

查询每一个人的性别    访问对象的属性 
select id,info.sex from test_struct;


综合练习：
id  addresses   info    score
1 bj,sh,sz zs,19,f a:32,b:56,c:78
2 bj,wh ls,20,m a:78,b:67,c:90

create table if not exists person(id int,adeesses array<string>,info struct<name:string,age:int,sex:string>,score map<string,int>) row format delimited fields terminated by " " collection items terminated by ',' map keys terminated by ':';

指定分隔符的原则   从外向内   字段之间---集合元素之间---k-v之间

hive视图

1.创建视图

create view viewname as select ....
create view age19 as select * from stu_managed01 where age>19;

2.查询视图列表

show tables;   显示视图和表
show views;  显示视图

3.查询视图的描述信息

desc viewname;
desc formatted viewname;

4.删除视图

drop view viewname;

注意:

hive中的视图存储在元数据中,mysql中只存储视图代表的sql查询语句
hive视图在查询视图的时候才会执行
hive中的视图只有逻辑视图,没有物化视图
hive中的视图不支持insert、update、delete只支持查询
hive中的视图是为了提升代码可读性

hive的函数

1.内置函数

1.1查看所有的函数列表

show functions;

1.2查看函数的使用

desc function funname;
desc function max;

1.3查看函数的详细使用

desc function extended funname;
desc function extended max;

2.常用的函数

2.1数值操作

聚合函数   max min  avg   sum count 
取整函数
    ceil  向上取整
        ceil(x)     参数 浮点数  返回的大于等于参数的第一个整数
        select ceil(avg(age)) from stu_managed01;
    floor 向下取整 
        用法  ceil 返回小于参数的最大的整数
        select floor(avg(age)) from stu_managed01;
    round 四舍五入
        round(x[, d])
        参数1  需要四舍五入的数据
        参数2   保留的小数位数  不给默认0 
        select round(3.576,2);
        
随机数的函数  rand()
    rand([seed])  参数  随机数种子  一般不用
    select rand()

2.2字符串操作

字符串切分 
    split(str, regex) 参数1 需要切分的字符串 参数2 切分分隔符
    select split("1,2,3,4",",");
    返回值   array  
字符串大小写
    大       小
    ucase |lcase 
    upper|lower
    参数  需要转化的字符串
    
字符串截取  hive中字符串的下标从1开始
    substr
    substring 
    substr(需要截取的字符串, 从哪一个位置开始截取[, 截取的字符串长度])
    
    select substr("hello aaaa",3,3);
    
    
字符串查询
    instr(原始字符串, 需要查找的子字符串)  返回的子字符串出现在原始字符串的第一个位置  没有找到返回0
    
    select instr("hello","lll");
    
    
字符串拼接
    concat(str1, str2, ... strN)
    将所有的参数拼接在一起
    SELECT concat('abc', 'def');   没有分割符
    concat_ws(分割符，字符串，字符串) 
    select concat_ws("-","a","b","c");
    a-b-c 
    
字符串去前后空格
    trim() 去前后空格
    ltrim()  去左侧空格
    rtrim() 去右侧空格
    
    传一个参数  需要处理的字符串
    select trim("  hello  hello  ");

2.3日期转换

获取当前系统时间戳 时间  current_timestamp
    select current_timestamp();
    
    
    
    时间戳---日期
    from_unnixtime 
    from_unixtime(时间戳, 日期格式)
    select from_unixtime(15262733537,"yyyy-MM-dd hh:mm")
    
    日期----时间戳
    unix_timestamp 
    unix_timestamp(日期字符串,日期格式)
    select unix_timestamp("2019/10/15 16:00:00","yyyy/MM/dd hh:mm:ss");
    1571126400
    
    year
    month
    day 
    hour 
    minute 
    second 
    day(日期)
    select month("2019-10-12");
    ...
    ...

2.4类型转换函数

cast(需要转换的字段 as 需要转换的类型)
select cast(trim("1 ") as int);

2.5json解析函数

json_tuple  
get_json_object(json_txt, path)
参数1  json字符串
    json串  map集合+数组  {}+[]
    {data:[],name:zs}
    [name,{age:23}]
    {  
        xAxis: {
            type: 'category',
            boundaryGap: false,
            data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
        },
        yAxis: {
            type: 'value'
        },
        series: [{
            data: [820, 932, 901, 934, 1290, 1330, 1320],
            type: 'line',
            areaStyle: {}
        }]
    }
    
    map中 包含数组  数组包含 map 
参数2: path 路径  需要解析的标签值 
    $   : Root object  json串的根目录  最外层目录
    .   : map集合的子标签key值  返回对应的value值
    []  : 获取数组的下标元素
    
    
    
    xAxis $.xAxis
    'Tue'  $.xAxis.data[1]

select get_json_object('{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}',"$.movie");   
    
{"name":小明","age":14,"gender":true}
    
{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}


select get_json_object('[{"beCommentWeiboId":"","beForwardWeiboId":"","catchTime":"1387157643","commentCount":"682","content":"喂！2014。。。2014！喂。。。","createTime":"1387086483","info1":"","info2":"","info3":"","mlevel":"","musicurl":[],"pic_list":["http://ww1.sinaimg.cn/square/47119b17jw1ebkc9b07x9j218g0xcair.jpg","http://ww4.sinaimg.cn/square/47119b17jw1ebkc9ebakij218g0xc113.jpg","http://ww2.sinaimg.cn/square/47119b17jw1ebkc9hml7dj218g0xcgt6.jpg","http://ww3.sinaimg.cn/square/47119b17jw1ebkc9kyakyj218g0xcqb3.jpg"],"praiseCount":"1122","reportCount":"671","source":"iPhone客户端","userId":"1192336151","videourl":[],"weiboId":"3655768039404271","weiboUrl":"http://weibo.com/1192336151/AnoMrDstN"}]','$[0].beCommentWeiboId')

3.炸裂函数

explode（array|map）,将集合中多个元素进行炸裂开,每一个元素占用一行,有几个元素对应几行,参数array|map

array[1,2,3,4]  explode 
1
2
3
4

map{name:zs,age:14}   explode 
name    zs 
age     14

select explode(score) from test_array;
select explode(score) from test_map;


表函数对应普通字段
select name,explode(score) from test_map;
报错   
    name  原始的一行 一个  
    explode  原始的一行   多行
    对应不匹配   不支持普通字段和explode一起使用
    
    
解决方案
select 
name,sc.course course,sc.scr score 
from test_map lateral view explode(score) sc as course,scr;

注意：
    这里的sc exlode的是视图的表名
    as后面给的字段的别名
    
huangbo {"yuwen":80,
        "shuxue":89,
        "yingyu":95}

4.集合函数

array（可变参数）构建数组的函数 map(可变参数) 构建map集合的函数奇数位置 key 偶数位置 value

select array(1,2,3,4);
select explode(array(1,2,3,4));

select map(1,2,3,4);

判断数组中是否包含某个元素
array_contains(数组，需要查询的元素)   包含  true 不包含  false 
select array_contains(array(1,2,3,4),2);

5.收集函数

聚合函数   将多行的某一个字段收集为个数组
collect_set   去重
collect_list   不去重

select 
dept,collect_set(age) 
from stu_managed01 
group by dept;

6.自定义函数

UDF USER-DEFINE-FUNCTION 进一条出一条字符串操作 split substr 日期 UDAF ser-define-aggregation-function 聚合函数进多条出一条
max min avg sum count UDTF ser-define-table-function 表函数进一条出多条 explode

内置函数271个
内置函数  无法满足 需求的时候 
自定义函数   
 UDF定义
步骤：
1)定义一个类  继承UDF 
2)实现一个或多个  evaluate 的方法
    参数  ：函数调用的时候的传参
	 * 返回值   函数调用之后的结果  不为void
3)将自定义的UDF打jar包
4）将jar包传到 hive所在的节点本地 
5）将jar包 传到hive的 classpath下
在hive中执行
add jar jar包路径
add jar /home/hadoop/hive_data/myudf.jar;

list jar;
list jars;
6）创建一个临时函数  关联  UDF 
create temporary function funname as "类的全限定名"
create temporary function myfun as "com.aura.cn.udf.MyUDF";
使用  通过函数名使用
select myfun(3);
这里调用myfun的时候  底层调用 com.aura.cn.udf.MyUDF.evaluate的方法的


临时函数  只对当前客户端生效  当前客户端退出  临时函数注销
要想重新生效  重复  5/6步操作

7.多字节分隔符的解析

字段之间的分隔符超过一个字节

数据 
1||zs||23
2||ls||12

建表  
create table if not exists test02(id int,name string,age int) row format delimited fields terminated by "\\|\\|";


load data local inpath "/home/hadoop/hive_data/data01" into table test02;

解析有问题  
hive数据解析类：
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe       

LazySimpleSerDe 这个类  只能解析单字节分隔符  不能解析多字节分隔符


解决方案：
1）将多字节分隔符   替换为 单字节分隔符
|| ---》  | 
这个替换过程中 一定足够了解数据
1||zgjz||aiguo|zainan

2)修改源码
不通用  

3）换解析类  
解析类   正则表达式 'org.apache.hadoop.hive.serde2.RegexSerDe'
指定   输入的正则表达式 input   输出的结果 output
('input.regex'='(.*)\\|\\|(.*)','output.format.string'='%1$s %2$s')

建表的时候   换掉解析类
建表语句：
create table if not exists test03(id int,name string,age int)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties('input.regex'='(.*)\\|\\|(.*)\\|\\|(.*)','output.format.string'='%1$s %2$s %3$s') 
stored as textfile;

input.regex 指定输入的正则表达式
    1||zs||23
    (.*)\\|\\|(.*)\\|\\|(.*)
    (.*)::(.*)::(.*)::(.*)::(.*)
    组编号  1开始
    最左侧的（ 算是第一组
output.format.string  指定输出的结果
    第一组   第二组   第三组  


load data local inpath "/home/hadoop/hive_data/data01" into table test03;

8.窗口函数

8.1开窗函数

对原来的表数据进行独立的拉去窗口中，对每一个窗口进行分析

需求：
stu_managed01   每一个部门中 年龄最大那个人
select 
dept,name,max(age) max_age 
from stu_managed01 
group by dept;

每一个组中的最值   使用  局限性的


stu_managed01   每一个部门中 年龄最大的前两个
分组   dept   

select 
dept, 
from stu_managed01 group by dept order by age desc limit 2;

8.2分析函数

1）突破group by 查询的极限性 2）分组 topN

over() 子句    开窗函数   开窗--将原始数据  分成不同的窗口
over（开窗依据 字段 排序规则）
    开窗依据  
        默认将相同的字段  放在一个窗口中
        distribute by |partition by 
    排序规则
        针对于每一个窗口的
        sort by | order by 
    over(distribute by + sort by )
    over(partition by + order by )
    
    over指定完成之后  我们原始表的数据  就会先按照开窗依据开窗  在在每一个窗口内 进行排序
over子句  不可以独立使用
select over(distribute by dept sort by age) from stu_managed01;  错误

使用  
1）聚合函数一起使用 
聚合函数 + over（distribute by + sort by | partition by +order by）

观察  over的效果
开窗依据  dept 
排序  age 
求  max 

select 
sid,name,sex,age,dept,
sum(age) over(distribute by dept sort by age) 
from stu_managed01;
求  每一个窗口的  最大值

95008   李娜    女      18      CS      18
95008   李娜    女      18      CS      18
95014   王小丽  女      19      CS      19
95010   孔小涛  男      19      CS      19
95010   孔小涛  男      19      CS      19
95014   王小丽  女      19      CS      19
95012   孙花    女      20      CS      20
95001   李勇    男      20      CS      20
95001   李勇    男      20      CS      20
95012   孙花    女      20      CS      20
95013   冯伟    男      21      CS      21
95013   冯伟    男      21      CS      21
95006   孙庆    男      23      CS      23
95006   孙庆    男      23      CS      23

聚合函数  + over使用的时候
    1.over 子句中 指定  distribute by + sort by 的时候 
    求 每一个窗口内的  累计当前数据位止的 聚合函数的值
    执行顺序  
    1)distribute by  
    2) sort by 一个年龄一个年龄出的  每次出来一个年龄之后  都会执行一次聚合函数
    
    2.指定  distribute by 
    求的是每一个窗口的  聚合函数结果
2）row_number | rank | dense_rank + over（）
over子句   指定开窗依据   前面的函数功能点
row_number   针对每一个窗口  显示行号的 每一个窗口从1开始  每一个窗口中  顺序递增的

开窗  dept 
排序  age 
select 
sid,name,age,sex,dept,
row_number() over(partition by dept order by age) r_no 
from stu_managed01;

95008   李娜    18      女      CS      1
95008   李娜    18      女      CS      2
95014   王小丽  19      女      CS      3
95010   孔小涛  19      男      CS      4
95010   孔小涛  19      男      CS      5
95014   王小丽  19      女      CS      6
95012   孙花    20      女      CS      7
95001   李勇    20      男      CS      8
95001   李勇    20      男      CS      9
95017   王风娟  18      女      IS      1
95017   王风娟  18      女      IS      2
95019   邢小丽  19      女      IS      3
95004   张立    19      男      IS      4
95018   王一    19      女      IS      5


每一个部门中 年龄最大那个人
开窗  distribute by dept sort by age desc 
功能  row_number 

select 
sid,name,sex,age,dept,
row_number() over(distribute by dept sort by age desc) no 
from stu_managed01;

每个窗口中 行号 =1 
select 
* 
from 
(
select 
sid,name,sex,age,dept,
row_number() over(distribute by dept sort by age desc) no 
from stu_managed01
) a where a.no=1;


每一个部门中年龄最小的前两个人
select
* 
from
(
select 
sid,name,age,sex,dept,row_number() over(distribute by dept sort by age) rownum
from stu_managed01
) a where rownum<=2;


rank()  | dense_rank()
select 
sid,name,age,sex,dept,rank() over(distribute by dept sort by age) rk 
from stu_managed01;
rank 排名
95008   李娜    18      女      CS      1
95008   李娜    18      女      CS      1
95014   王小丽  19      女      CS      3
95010   孔小涛  19      男      CS      3
95010   孔小涛  19      男      CS      3
95014   王小丽  19      女      CS      3
95012   孙花    20      女      CS      7
95001   李勇    20      男      CS      7
95001   李勇    20      男      CS      7
95012   孙花    20      女      CS      7

select 
sid,name,age,sex,dept,dense_rank() over(distribute by dept sort by age) rk 
from stu_managed01;
dense_rank 
95008   李娜    18      女      CS      1
95008   李娜    18      女      CS      1
95014   王小丽  19      女      CS      2
95010   孔小涛  19      男      CS      2
95010   孔小涛  19      男      CS      2
95014   王小丽  19      女      CS      2
95012   孙花    20      女      CS      3
95001   李勇    20      男      CS      3
95001   李勇    20      男      CS      3
95012   孙花    20      女      CS      3



3)lag|lead | first_value|last_value + over