Hive函数与文件格式
一、Hive函数
1、查看所有函数
hive (default)> show functions;
2、查看函数语法
hive (default)> desc function 函数名;
查看函数详情，会返回一个例子供参考
hive (default)> desc function extended 函数名;
3、NVL函数：空字段赋值
语法：NVL( value，default_value)
如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。
4、if函数
语法：if(expr1,expr2,expr3)
expr1为true便返回expr2，否则返回expr3
5、case函数
select *,
case did
when 10 then '销售部'
when 20 then '财务部'
else '其他 '
end as dname
from tb_emp;
case与条件表达式
select *,
case
when sal>=800 and sal <= 1200 then '小康'
when sal=3000 then '有钱'
else '其他 '
end as dname
from tb_emp;
6、 substr 、substring截取字符串
两个函数用法一样
substring(str, 起始位置, 截取长度)，起始从1开始，若截取长度不写，则截到末尾
hive> select substr('abcde',3) ;//意为从第三个开始截取，一直到结尾。a的下标为1。
cde
hive> select substr('abcde',3,2);//从第三个起开始截取两个步长
cd
hive> select substr('abcde',-1); //截取最后一位
e
hive>select substring('abcde',-2,2) ;//截取最后两个
de
7、 行转列
（1）concat拼接
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
select concat(1,2)
+------+
| _c0 |
+------+
| 12 |
+------+
（2）concat_ws拼接
concat_ws(separator, [string | array(string)]+)：它是一个特殊形式的CONCAT()。第一个参数指定拼接分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间。
注意: CONCAT_WS must be “string or array”
concat_ws()函数(concatenate with separator)将数组元素用指定的分隔符(本例中为逗号)连接成字符串。
select concat_ws(',','1','2');
+------+
| _c0 |
+------+
| 1,2 |
+------+
（3）collect_set(col)和collect_list(col)
collect_set和collect_list都是将某列的所有值汇总转换成Array ，不同的是collect_set会去重，collect_list不会去重
（4）行转列案例
--数据如下
name gender job
涛哥 M T
娜娜 F S
老赵 M T
小刘 F T
老娜 F S
-- 获取如下结果
S,F 娜娜|老娜
T,F 小刘
T,M 涛哥|老赵
drop table tb_teacher ;
create table tb_teacher(
name string,
gender string,
job string)
row format delimited fields terminated by "\t";
load data local inpath "/data/teacher.tsv" into table tb_teacher;
+------------------+--------------------+-----------------+
| tb_teacher.name | tb_teacher.gender | tb_teacher.job |
+------------------+--------------------+-----------------+
| 涛哥 | M | T |
| 娜娜 | F | S |
| 老赵 | M | T |
| 小刘 | F | T |
| 老娜 | F | S |
+------------------+--------------------+-----------------+
collect_list
select
name ,
concat_ws(',',job,gender) as job_gender
from
tb_teacher ;
+-------+------+
| name | job_gender |
+-------+------+
| 涛哥 | T,M |
| 娜娜 | S,F |
| 老赵 | T,M |
| 小刘 | T,F |
| 老娜 | S,F |
+-------+------+
select
job_gender ,
collect_list(name)
from
(
select
name ,
concat_ws(',',job,gender) as job_gender
from
tb_teacher
)t
group by job_gender ;
+-------------+--------------+
| job_gender | _c1 |
+-------------+--------------+
| T,F | ["小刘"] |
| S,F | ["娜娜","老娜"] |
| T,M | ["涛哥","老赵"] |
+-------------+--------------+
select
job_gender ,
concat_ws('|',collect_list(name)) as names
from
(
select
name ,
concat_ws(',',job,gender) as job_gender
from
tb_teacher
)t
group by job_gender ;
+-------------+--------+
| job_gender | names |
+-------------+--------+
| T,F | 小刘 |
| S,F | 娜娜|老娜 |
| T,M | 涛哥|老赵 |
+-------------+--------+
7、 列转行
（1）explode(col)炸裂函数
EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。Array炸裂成一列，Map炸裂成两列，key一列，value一列
（2）lateral view 侧窗口函数，维护了炸裂前后的关系
用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，lateral view 在此基础上可以对拆分后的数据进行聚合。
lateral view udtf(expression)
tableAlias as columnAlias (,columnAlias)*
tableAlias as columnAlias此处为固定写法，tableAlias 是给前面的侧写窗口起了个临时表名，columnAlias是给给炸裂后的字段起别名
（3）列转行案例
--数据如下
《八佰》 战争,动作,爱国,剧情
《当幸福来敲门》 剧情,励志,怀旧,心理,故事
《悬崖之上》 战争,爱国,抗日,谍战
--代码实现
create table tb_movie(
name string,
category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
load data local inpath '/data/movie.txt' into table tb_movie ;
select * from tb_movie ;
+----------------+-----------------------------+
| tb_movie.name | tb_movie.category |
+----------------+-----------------------------+
| 《八佰》 | ["战争","动作","爱国","剧情"] |
| 《当幸福来敲门》 | ["剧情","励志","怀旧","心理","故事"] |
| 《悬崖之上》 | ["战争","爱国","抗日","谍战"] |
+----------------+-----------------------------+
select
name,
category_name
from
tb_movie lateral view explode(category) table_tmp as category_name;
+-----------+----------------+
| name | category_name |
+-----------+----------------+
| 《八佰》 | 战争 |
| 《八佰》 | 动作 |
| 《八佰》 | 爱国 |
| 《八佰》 | 剧情 |
| 《当幸福来敲门》 | 剧情 |
| 《当幸福来敲门》 | 励志 |
| 《当幸福来敲门》 | 怀旧 |
| 《当幸福来敲门》 | 心理 |
| 《当幸福来敲门》 | 故事 |
| 《悬崖之上》 | 战争 |
| 《悬崖之上》 | 爱国 |
| 《悬崖之上》 | 抗日 |
| 《悬崖之上》 | 谍战 |
+-----------+----------------+
8、 窗口函数
窗口: 聚合函数执行的范围 , 组内执行聚合 , 窗口大小就是组
在进行分组聚合以后 ， 我们还想操作集合以前的数据，就需要使用到窗口函数
可以将窗口函数看做要查询的一个字段，只不过这个字段是经过处理计算得到的。以往的聚合函数都是多行返回一行，加上窗口函数后，行数不会再发生变化，每行都会有计算出的结果（针对每行进行计算）。
（1）over()
-
over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
-
current row：当前行 current row
-
n preceding：往前n行数据
-
n following：往后n行数据
-
unbounded preceding：起点
-
unbounded following：终点
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按 name分组，组内数据逐行累加累加
sum(cost) over(partition by name order by orderdate rows between
UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1
PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1
PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current
row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;
当over()里使用了order by 后，默认窗口大小是从起始行到当前行，所以不管前面聚合函数是啥，都是逐行累计做运算。
没有使用order by时，默认窗口大小是从起始行到结束行
（2）lag()与lead()
必须和over()一起使用
-
lag(col,n,[default_val])：往前第n行数据
-
lead(col,n,[default_val])：往后第n行数据
--查询顾客上次的购买时间
--查cdate 往前第一行的数据，若是null则用first_date代替
select
* ,
lag(cdate , 1 , 'first_date') over(partition by name order by cdate) as bay_date
from
tb_orders ;
（3）nyile
必须和over()一起使用
- nyile(n)：把有序的数据集合 平均分配 到 指定的数量（n）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1
select
* ,
ntile(5) over() as zb
from
tb_orders ;
+-----------------+------------------+------------------+-----------------+
| tb_orders.name | tb_orders.cdate | tb_orders.money | zb |
+-----------------+------------------+------------------+-----------------+
| mart | 2017-04-13 | 94.0 | 1 |
| neil | 2017-06-12 | 80.0 | 1 |
| mart | 2017-04-11 | 75.0 | 1 |
| neil | 2017-05-10 | 12.0 | 2 |
| mart | 2017-04-09 | 68.0 | 2 |
| mart | 2017-04-08 | 62.0 | 2 |
| jack | 2017-01-08 | 55.0 | 3 |
| tony | 2017-01-07 | 50.0 | 3 |
| jack | 2017-04-06 | 42.0 | 3 |
| jack | 2017-01-05 | 46.0 | 4 |
| tony | 2017-01-04 | 29.0 | 4 |
| jack | 2017-02-03 | 23.0 | 4 |
| tony | 2017-01-02 | 15.0 | 5 |
| jack | 2017-01-01 | 10.0 | 5 |
+-----------------+------------------+------------------+-----------------+
（4）count(1)与count(1) over()的区别
count(1)算的是每个分组里该字段的个数，count(1) over()算的是分组之后形成的表中这个字段的个数。
也就是说，当有group by的时候，聚合函数后面加over()，是对分组后形成的表进行聚合。
（5）first_value()与last_value()
必须和over()一起使用
first_value(col)：返回某个字段在窗口中的第一个值
last_value(col)：返回某个字段在窗口中的最后一个值
select id,first_value(id) over() from tb_user;
select id,last_value(id) over() from tb_user;
（5）练习题
--统计每个人最高的连续登录次数
a,2017-02-05,200
a,2017-02-06,300
a,2017-02-07,200
a,2017-02-08,400
a,2017-02-10,600
b,2017-02-05,200
b,2017-02-06,300
b,2017-02-08,200
b,2017-02-09,400
b,2017-02-10,600
c,2017-01-31,200
c,2017-02-01,300
c,2017-02-02,200
c,2017-02-03,400
c,2017-02-10,600
a,2017-03-01,200
a,2017-03-02,300
a,2017-03-03,200
a,2017-03-04,400
a,2017-03-05,600
--思路
先根据每个人分区,根据日期排序，打上行号
再用日期减去行号，如果得到的结果相等 则是连续增长
统计连续增长天数
取最大值
--先根据每个人分区,根据日期排序，打上行号
select * ,row_number() over(partition by name order by cdate) num from tb_shop
+---------------+----------------+----------------+------+
| tb_shop.name | tb_shop.cdate | tb_shop.money | num |
+---------------+----------------+----------------+------+
| a | 2017-02-05 | 200.0 | 1 |
| a | 2017-02-06 | 300.0 | 2 |
| a | 2017-02-07 | 200.0 | 3 |
| a | 2017-02-08 | 400.0 | 4 |
| a | 2017-02-10 | 600.0 | 5 |
| a | 2017-03-01 | 200.0 | 6 |
| a | 2017-03-02 | 300.0 | 7 |
| a | 2017-03-03 | 200.0 | 8 |
| a | 2017-03-04 | 400.0 | 9 |
| a | 2017-03-05 | 600.0 | 10 |
| b | 2017-02-05 | 200.0 | 1 |
| b | 2017-02-06 | 300.0 | 2 |
| b | 2017-02-08 | 200.0 | 3 |
| b | 2017-02-09 | 400.0 | 4 |
| b | 2017-02-10 | 600.0 | 5 |
| c | 2017-01-31 | 200.0 | 1 |
| c | 2017-02-01 | 300.0 | 2 |
| c | 2017-02-02 | 200.0 | 3 |
| c | 2017-02-03 | 400.0 | 4 |
| c | 2017-02-10 | 600.0 | 5 |
+---------------+----------------+----------------+------+
--用日期减去行号
with t1 as (select * ,row_number() over(partition by name order by cdate) num from tb_shop )
select * , date_sub(cdate, num) as date1 from t1;
+----------+-------------+-----------+---------+-------------+
| t1.name | t1.cdate | t1.money | t1.num | date1 |
+----------+-------------+-----------+---------+-------------+
| a | 2017-02-05 | 200.0 | 1 | 2017-02-04 |
| a | 2017-02-06 | 300.0 | 2 | 2017-02-04 |
| a | 2017-02-07 | 200.0 | 3 | 2017-02-04 |
| a | 2017-02-08 | 400.0 | 4 | 2017-02-04 |
| a | 2017-02-10 | 600.0 | 5 | 2017-02-05 |
| a | 2017-03-01 | 200.0 | 6 | 2017-02-23 |
| a | 2017-03-02 | 300.0 | 7 | 2017-02-23 |
| a | 2017-03-03 | 200.0 | 8 | 2017-02-23 |
| a | 2017-03-04 | 400.0 | 9 | 2017-02-23 |
| a | 2017-03-05 | 600.0 | 10 | 2017-02-23 |
| b | 2017-02-05 | 200.0 | 1 | 2017-02-04 |
| b | 2017-02-06 | 300.0 | 2 | 2017-02-04 |
| b | 2017-02-08 | 200.0 | 3 | 2017-02-05 |
| b | 2017-02-09 | 400.0 | 4 | 2017-02-05 |
| b | 2017-02-10 | 600.0 | 5 | 2017-02-05 |
| c | 2017-01-31 | 200.0 | 1 | 2017-01-30 |
| c | 2017-02-01 | 300.0 | 2 | 2017-01-30 |
| c | 2017-02-02 | 200.0 | 3 | 2017-01-30 |
| c | 2017-02-03 | 400.0 | 4 | 2017-01-30 |
| c | 2017-02-10 | 600.0 | 5 | 2017-02-05 |
+----------+-------------+-----------+---------+-------------+
--根据name与date1分组，统计连续登录次数
+-------+-----+
| name | co |
+-------+-----+
| a | 4 |
| a | 1 |
| a | 5 |
| b | 2 |
| b | 3 |
| c | 4 |
| c | 1 |
+-------+-----+
--求最大连续登录次数
with t1 as (select * ,row_number() over(partition by name order by cdate) num from tb_shop ),
t2 as (select * , date_sub(cdate, num) as date1 from t1),
t3 as (select name,count(1) co from t2 group by name , date1)
select name,max(co) from t3 group by name
+-------+------+
| name | _c1 |
+-------+------+
| a | 5 |
| b | 3 |
| c | 4 |
+-------+------+
9、Rank()、DENSE_RANK() 、ROW_NUMBER()编号函数
必须和over()一起用
-
rank() 排序相同时会重复，总数不会变
-
dense_rank() 排序相同时会重复，总数会减少
-
row_number() 根据顺序计算打编号
注意： rank() 与 dense_rank()必须在over()中加上排序，否则编号全为1， row_number() 不需要排序
--数据
name subject score
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
娜娜 语文 94
娜娜 数学 56
娜娜 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78
create table tb_score(
name string ,
subject string ,
score double
)
row format delimited fields terminated by '\t' ;
load data local inpath '/data/score.txt' into table tb_score ;
+----------------+-------------------+-----------------+
| tb_score.name | tb_score.subject | tb_score.score |
+----------------+-------------------+-----------------+
| 孙悟空 | 语文 | 87.0 |
| 孙悟空 | 数学 | 95.0 |
| 孙悟空 | 英语 | 68.0 |
| 娜娜 | 语文 | 94.0 |
| 娜娜 | 数学 | 56.0 |
| 娜娜 | 英语 | 84.0 |
| 宋宋 | 语文 | 64.0 |
| 宋宋 | 数学 | 86.0 |
| 宋宋 | 英语 | 84.0 |
| 婷婷 | 语文 | 65.0 |
| 婷婷 | 数学 | 85.0 |
| 婷婷 | 英语 | 78.0 |
+----------------+-------------------+-----------------+
select
* ,
-- 编号不考虑排序
row_number() over(partition by subject order by score desc) as rn
from
tb_score ;
select
* ,
-- 排序字段相同的行 编号一致 , 总数不变
rank() over(partition by subject order by score desc) as rn
from
tb_score ;
+----------------+-------------------+-----------------+-----+
| tb_score.name | tb_score.subject | tb_score.score | rn |
+----------------+-------------------+-----------------+-----+
| 孙悟空 | 数学 | 95.0 | 1 |
| 宋宋 | 数学 | 86.0 | 2 |
| 婷婷 | 数学 | 85.0 | 3 |
| 娜娜 | 数学 | 56.0 | 4 |
| 宋宋 | 英语 | 84.0 | 1 |
| 娜娜 | 英语 | 84.0 | 1 |
| 婷婷 | 英语 | 78.0 | 3 |
| 孙悟空 | 英语 | 68.0 | 4 |
| 娜娜 | 语文 | 94.0 | 1 |
| 孙悟空 | 语文 | 87.0 | 2 |
| 婷婷 | 语文 | 65.0 | 3 |
| 宋宋 | 语文 | 64.0 | 4 |
+----------------+-------------------+-----------------+-----+
select
* ,
-- 排序字段相同的行 编号一致 ,编号是连续的 , 总数变少
dense_rank() over(partition by subject order by score desc) as rn
from
tb_score ;
+----------------+-------------------+-----------------+-----+
| tb_score.name | tb_score.subject | tb_score.score | rn |
+----------------+-------------------+-----------------+-----+
| 孙悟空 | 数学 | 95.0 | 1 |
| 宋宋 | 数学 | 86.0 | 2 |
| 婷婷 | 数学 | 85.0 | 3 |
| 娜娜 | 数学 | 56.0 | 4 |
| 宋宋 | 英语 | 84.0 | 1 |
| 娜娜 | 英语 | 84.0 | 1 |
| 婷婷 | 英语 | 78.0 | 2 |
| 孙悟空 | 英语 | 68.0 | 3 |
| 娜娜 | 语文 | 94.0 | 1 |
| 孙悟空 | 语文 | 87.0 | 2 |
| 婷婷 | 语文 | 65.0 | 3 |
| 宋宋 | 语文 | 64.0 | 4 |
+----------------+-------------------+-----------------+-----+
10、json解析get_json_object()和json_tuple()
get_json_object()与json_tuple()都可以解析json字符串，不同的是get_json_object()只能解析一个字段，而json_tuple()可以解析多个。
- get_json_object()
get_json_object函数第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组。该方法只能接受两个参数，如果想要查看多个值，只能多写几个
select get_json_object('{"name":"linda","server":"www.baidu.com"}','$.server')
#输出结果
www.baidu.com
select get_json_object('["1","2","3"]','$.[1]');
#输出结果
2
- json_tuple()
当使用json_tuple函数时，可以显著提高效率，一次获取多个对象并且可以被组合使用
select json_tuple(’{“name”:“linda”,“server”:“www.baidu.com”}’,‘server’,‘name’)
#输出结果
linda www.baidu.com
11、日期相关函数
（1）current_date()返回当前日期
select current_date();
2021-09-18
（2）date_add()、date_sub() 日期加减
select date_add('2021-01-01',1);
2021-01-02
select date_sub('2021-01-01',1);
2020-12-31
（3）datediff() 计算两个日期差值，前面减后面
select datediff('2021-01-01','2021-01-10');
-9
（4）day()、month()、year()
select day('2021-01-02');
2
select month('2021-01-02');
1
select year('2021-01-02');
2021
12、with子查询
使用 with 表名 as 可以将一个sql语句注册为一张临时表，供之后的sql语句使用
with t1 as (select * ,row_number() over(partition by name order by cdate) num from tb_shop ),
t2 as (select * , date_sub(cdate, num) as date1 from t1),
t3 as (select name,count(1) co from t2 group by name , date1)
select name,max(co) from t3 group by name
13、其他函数
常用日期函数
unix_timestamp:返回当前或指定时间的时间戳
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');
from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);
current_date：当前日期
select current_date;
current_timestamp：当前的日期加时间
select current_timestamp;
to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');
year：获取年
select year('2020-10-28 12:12:12');
month：获取月
select month('2020-10-28 12:12:12');
day：获取日
select day('2020-10-28 12:12:12');
hour：获取时
select hour('2020-10-28 12:12:12');
minute：获取分
select minute('2020-10-28 12:12:12');
second：获取秒
select second('2020-10-28 12:12:12');
weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');
dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');
months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');
add_months：日期加减月
select add_months('2020-10-28',-3);
datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');
date_add：日期加天数
select date_add('2020-10-28',4);
date_sub：日期减天数
select date_sub('2020-10-28',-4);
last_day：日期的当月的最后一天
select last_day('2020-02-30');
date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');
常用取整函数
round： 四舍五入
select round(3.14);
select round(3.54);
ceil： 向上取整
select ceil(3.14);
select ceil(3.54);
floor： 向下取整
select floor(3.14);
select floor(3.54);
常用字符串操作函数
upper： 转大写
select upper('low');
lower： 转小写
select lower('low');
length： 长度
select length("atguigu");
trim： 前后去空格
select trim(" atguigu ");
lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');
rpad： 向右补齐，到指定长度
select rpad('atguigu',9,'g');
regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');
集合操作
size： 集合中元素的个数
select size(friends) from test3;
map_keys： 返回map中的key
select map_keys(children) from test3;
map_values: 返回map中的value
select map_values(children) from test3;
array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;
sort_array： 将array中的元素排序
select sort_array(friends) from test3;
grouping_set:多维分析
二、Hive文件格式
Hive支持的存储数的格式主要有：textfile 、sequencefile、orc、parquet。
1、列式存储和行式存储
如图所示左边为逻辑表，右边第一个为行式存储，第二个为列式存储。
（1）行存储的特点
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
（2）列存储的特点
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。
TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；
ORC和PARQUET是基于列式存储的。
2、TextFile格式
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。
3、 Orc格式
Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存储格式。
如图所示可以看到每个Orc文件由1个或多个stripe组成，每个 stripe 250MB大小，这个Stripe实际相当于RowGroup概念，不过大小由4MB->250MB，这样应该能提升顺序读的吞吐率。每个Stripe里有三部分组成，分别是Index Data，Row Data，Stripe Footer：
1）Index Data：一个轻量级的index，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在Row Data中的offset。
2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。
3）Stripe Footer：存的是各个Stream的类型，长度等信息。
每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。
4、 Parquet格式
Parquet是面向分析型业务的列式存储格式，由Twitter和Cloudera合作开发，2015年5月从Apache的孵化器里毕业成为Apache顶级项目。
Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。Parquet文件的格式如图所示。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mOq1IXIt-1631888148453)(img/wps314A.tmp.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/fe3a882a6c3db78d2a91ce613207a379.png)
上图展示了一个Parquet文件的内容，一个文件中可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验它是否是一个Parquet文件，Footer length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据，在Parquet中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前Parquet中还不支持索引页。
在hive中默认使用的数据格式是文本 , hive中处理方式以SQL的语法处理 , 结构化查询 , 使用列式存储文件格式高效。
5、 创建不同文件格式的表
（1）普通建表语句创建
create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as textfile ; --默认就是textfile格式
--stored as ORC ; --创建ORC文件格式的表
--stored as parquet ;
（2）复制表结构创建
create table tb_orc like log_text stored as orc;
6、 往非text文件格式中加载数据
（1）用insert添加
insert into table log_orc select * from log_text ;
（2）使用 as select 在建表时加载
create table log_orc stored as orc as select * from log_text ;
7、 主流文件格式对比
- 文本文件是默认格式 行式存储 不压缩 执行效率最低 18.1M
- ORC 列式存储 压缩比高 执行效率高 2.7M
- parquet 列式存储 压缩比比ORC低 执行效率和ORC相当 , 兼容性比ORC好 13.1M
执行效率ORC和parquet格式类似 , 但是ORC的压缩比更更好 ,parquet 兼容性好 。
所以优先parquet ，再 orc ，再text
三、with cube、with rollup、grouping sets多维分析
with立方体是对数据的一种预查询处理。适用于固定维度查询。
数据过多，实时查询速度太慢，又是固定维度查询，可以用with立方体提前查出所有维度查询情况保存在数据库。
- with cube
with cube 语法是将所有的维度进行group by的结果组合。
一个具有N维的数据模型，做完Cube操作，能产生2的N次方种聚合方式。
即3个字段的聚合，会有2的3次方的组合方式。 - with rollup
各维度组合，但是不能跳跃维度查询，前一维度为null后一位维度必须为null，前一维度取非null时，下一维度随意 - grouping sets
自定义维度，根据需要分组即可
多维分析只用一个MR完成各维度分组聚合，效率比较高
--原始数据
select * from tb_user;
+-------------+---------------+--------------+
| tb_user.id | tb_user.name | tb_user.age |
+-------------+---------------+--------------+
| 1 | zss | 23 |
| 1 | zss | 23 |
| 2 | lss | 33 |
| 3 | lny | 49 |
| 4 | tgg | 43 |
| 1 | zss | 23 |
| 1 | zss | 23 |
| 2 | lss | 33 |
| 3 | lny | 49 |
| 4 | tgg | 43 |
+-------------+---------------+--------------+
--使用 with cube
select id,name,age,count(1) from tb_user group by id,name,age with cube;
+-------+-------+-------+------+
| id | name | age | _c3 |
+-------+-------+-------+------+
| NULL | NULL | NULL | 10 |
| NULL | NULL | 23 | 4 |
| NULL | NULL | 33 | 2 |
| NULL | NULL | 43 | 2 |
| NULL | NULL | 49 | 2 |
| NULL | lny | NULL | 2 |
| NULL | lny | 49 | 2 |
| NULL | lss | NULL | 2 |
| NULL | lss | 33 | 2 |
| NULL | tgg | NULL | 2 |
| NULL | tgg | 43 | 2 |
| NULL | zss | NULL | 4 |
| NULL | zss | 23 | 4 |
| 1 | NULL | NULL | 4 |
| 1 | NULL | 23 | 4 |
| 1 | zss | NULL | 4 |
| 1 | zss | 23 | 4 |
| 2 | NULL | NULL | 2 |
| 2 | NULL | 33 | 2 |
| 2 | lss | NULL | 2 |
| 2 | lss | 33 | 2 |
| 3 | NULL | NULL | 2 |
| 3 | NULL | 49 | 2 |
| 3 | lny | NULL | 2 |
| 3 | lny | 49 | 2 |
| 4 | NULL | NULL | 2 |
| 4 | NULL | 43 | 2 |
| 4 | tgg | NULL | 2 |
| 4 | tgg | 43 | 2 |
+-------+-------+-------+------+
--使用 with rollup
select id,name,age,count(1) from tb_user group by id,name,age with rollup;
+-------+-------+-------+------+
| id | name | age | _c3 |
+-------+-------+-------+------+
| NULL | NULL | NULL | 10 |
| 1 | NULL | NULL | 4 |
| 1 | zss | NULL | 4 |
| 1 | zss | 23 | 4 |
| 2 | NULL | NULL | 2 |
| 2 | lss | NULL | 2 |
| 2 | lss | 33 | 2 |
| 3 | NULL | NULL | 2 |
| 3 | lny | NULL | 2 |
| 3 | lny | 49 | 2 |
| 4 | NULL | NULL | 2 |
| 4 | tgg | NULL | 2 |
| 4 | tgg | 43 | 2 |
+-------+-------+-------+------+
--使用 grouping sets
select id,name,age,count(1) from tb_user group by id,name,age grouping sets((id),(name,age));
相当于
select id,null,null,count(1) from tb_user group by id,null,null
union
select null,name,age,count(1) from tb_user group by null,name,age
+-------+-------+-------+------+
| id | name | age | _c3 |
+-------+-------+-------+------+
| NULL | lny | 49 | 2 |
| NULL | lss | 33 | 2 |
| NULL | tgg | 43 | 2 |
| NULL | zss | 23 | 4 |
| 1 | NULL | NULL | 4 |
| 2 | NULL | NULL | 2 |
| 3 | NULL | NULL | 2 |
| 4 | NULL | NULL | 2 |
+-------+-------+-------+------+
select id,name,age,count(1) from tb_user group by id,name,age grouping sets(id,());
相当于
select id,null,null,count(1) from tb_user group by id,null,null
union
select null,null,null,count(1) from tb_user
+-------+-------+-------+------+
| id | name | age | _c3 |
+-------+-------+-------+------+
| NULL | NULL | NULL | 10 |
| 1 | NULL | NULL | 4 |
| 2 | NULL | NULL | 2 |
| 3 | NULL | NULL | 2 |
| 4 | NULL | NULL | 2 |
+-------+-------+-------+------+
四、使用JDBC连接Hive
（1）添加依赖
<dependencies>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>3.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>3.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc
数据库的驱动 连接的是何种数据库
-->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>3.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-metastore</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
（2）编写代码连接Hive
//使用JDBC连接Hive
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
//注册驱动
Class.forName("org.apache.hive.jdbc.HiveDriver");
//获取连接
conn = DriverManager.getConnection("jdbc:hive2://linux1:10000/default");
//创建执行sql的对象
ps = conn.prepareStatement("select * from tb_user");
//执行sql
resultSet = ps.executeQuery();
//遍历结果
while (resultSet.next()) {
System.out.println(resultSet.getInt(1) + "," +
resultSet.getString(2) + "," +
resultSet.getInt(3));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
resultSet.close();
ps.close();
conn.close();
}
}
扫一扫
7582
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
