Hive再次学习<二>

其他更多java基础文章：
java基础学习(目录)

---

学习资料：
尚硅谷2021版Hive教程（基于hive3.1.2）
一文学完所有的Hive Sql（两万字最全详解）

重要函数

NVL空字段赋值

1. 函数说明 NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。

2. 查询：如果员工的 comm 为 NULL，则用-1 代替

hive (default)> select comm,nvl(comm, -1) from emp; OK comm    _c1 
NULL    -1.0 
300.0   300.0 

CASE WHEN THEN ELSE END

写法一

语法: case when a then b [when c then d]* [else e] end
返回值: T
说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
mary

写法二

语法: case a when b then c [when d then e]* [else f] end
返回值: T
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
mary

行转列

相关函数说明

• CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
• CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
  注意: CONCAT_WS must be "string or array<string>
• COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段。

3. 需求
   把星座和血型一样的人归类到一起。结果如下：

射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋|苍老师 

4. 查询sql

SELECT t1.c_b, 
CONCAT_WS("|",collect_set(t1.name)) 
FROM ( 
SELECT NAME, 
CONCAT_WS(',',constellation,blood_type) c_b FROM person_info 
)t1 
GROUP BY t1.c_b 

列转行

函数说明

EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

3）需求
将电影分类中的数组数据展开。结果如下：

《疑犯追踪》      悬疑 
《疑犯追踪》      动作 
《疑犯追踪》      科幻 
《疑犯追踪》      剧情 
《Lie to me》   悬疑 
《Lie to me》   警匪 
《Lie to me》   动作 
《Lie to me》   心理 
《Lie to me》   剧情《战狼2》        战争 
《战狼2》        动作 
《战狼2》        灾难 

4） 按需求查询数据

SELECT movie, category_name 
FROM movie_info lateral VIEW explode(split(category,",")) movie_info_tmp  AS category_name; 

窗口函数（开窗函数）

相关函数说明

• OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
• CURRENT ROW：当前行 n PRECEDING：往前 n 行数据 n FOLLOWING：往后 n 行数据
• UNBOUNDED：起点，
  • UNBOUNDED PRECEDING 表示从前面的起点，
  • UNBOUNDED FOLLOWING 表示到后面的终点
• LAG(col,n,default_val)：用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
• LEAD(col,n, default_val)：用于统计窗口内往后第n行值第一个参数为列名，第二个参数为往后第n行（可选，默认为1），第三个参数为默认值（当往后第n行为NULL时候，取默认值，如不指定，则为NULL）
• FIRST_VALUE(col): 取分组内排序后，截止到当前行，第一个值
• NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

窗口函数最重要的关键字是 partition by 和 order by。具体语法如下：over (partition by xxx order by xxx rows between xxxxxxx )

• 如果不指定rows between,默认为从起点到当前行;
• 如果不指定order by，则将分组内所有值累加;

数据准备：

name，orderdate，cost 
jack,2017-01-01,10 tony,2017-01-02,15 jack,2017-02-03,23 tony,2017-01-04,29 
jack,2017-01-05,46 jack,2017-04-06,42 tony,2017-01-07,50 jack,2017-01-08,55 
mart,2017-04-08,62 mart,2017-04-09,68 neil,2017-05-10,12 mart,2017-04-11,75 
neil,2017-06-12,80 mart,2017-04-13,94

窗口函数示例1

1. 查询在 2017 年 4 月份购买过的顾客及总人数

select name,count(*) over ()  from business where substring(orderdate,1,7) = '2017-04' group by name; 

2. 查询顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business; 

3. 将每个顾客的 cost 按照日期进行累加

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; 

rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

4. 查看顾客上次的购买时间

select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, 
lag(orderdate,2) over (partition by name order by orderdate) as time2  from business; 

5. 查询前 20%时间的订单信息

select * from 
( select name,orderdate,cost, 
   ntile(5) over(order by orderdate) sorted   from business 
) t where sorted = 1; 

窗口函数示例2

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1 
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from test_t1;


pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv3: 分组内(cookie1)所有的pv累加
pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号，13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号
pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号，14号=14号+15号+16号

NTILE

Hive Ntile分析函数学习

有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。
Ntile可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

//查询前 20%时间的订单信息
select * from 
( select name,orderdate,cost, 
   ntile(5) over(order by orderdate) sorted   from business 
) t where sorted = 1; 

row_number、rank、dense_rank

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

3. 按需求查询数据

select name, subject, score, 
rank() over(partition by subject order by score desc) rp, 
dense_rank() over(partition by subject order by score desc) drp, 
row_number() over(partition by subject order by score desc) rmp 
from score; 

 name    subject score   rp      drp     rmp 
 孙悟空  数学    95      1       1       1 
 宋宋    数学    86      2       2       2 
 婷婷    数学    85      3       3       3 
 大海    数学    56      4       4       4 
 宋宋    英语    84      1       1       1 
 大海    英语    84      1       1       2 
 婷婷    英语    78      3       2       3 
 孙悟空  英语    68      4       3       4 
 大海    语文    94      1       1       1 
 孙悟空  语文    87      2       2       2 
 婷婷    语文    65      3       3       3 
 宋宋    语文    64      4       4       4 

lag,lead,first_value,last_value

lxw1234.com/archives/20…

• LAG 用于统计窗口内往上第n行值，第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
FROM lxw1234;
 
 
cookieid createtime             url    rn       last_1_time             last_2_time
-------------------------------------------------------------------------------------------
cookie1 2015-04-10 10:00:00     url1    1       1970-01-01 00:00:00     NULL
cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:00:00     NULL
cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:00:02     2015-04-10 10:00:00
cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:03:04     2015-04-10 10:00:02
cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:10:00     2015-04-10 10:03:04
cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 10:50:01     2015-04-10 10:10:00
cookie1 2015-04-10 11:00:00     url7    7       2015-04-10 10:50:05     2015-04-10 10:50:01
cookie2 2015-04-10 10:00:00     url11   1       1970-01-01 00:00:00     NULL
cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:00:00     NULL
cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:00:02     2015-04-10 10:00:00
cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:03:04     2015-04-10 10:00:02
cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:10:00     2015-04-10 10:03:04
cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 10:50:01     2015-04-10 10:10:00
cookie2 2015-04-10 11:00:00     url77   7       2015-04-10 10:50:05     2015-04-10 10:50:01

• LEAD 同上，只是向下取值
• FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
• LAST_VALUE 取分组内排序后，截止到当前行，最后一个值（通常就是当前行本身）

如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果

如果想要取分组内排序后最后一个值，则需要变通一下：

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 
FROM lxw1234 
ORDER BY cookieid,createtime;
 
cookieid  createtime            url     rn     last1    last2
-------------------------------------------------------------
cookie1 2015-04-10 10:00:00     url1    1       url1    url7
cookie1 2015-04-10 10:00:02     url2    2       url2    url7
cookie1 2015-04-10 10:03:04     1url3   3       1url3   url7
cookie1 2015-04-10 10:10:00     url4    4       url4    url7
cookie1 2015-04-10 10:50:01     url5    5       url5    url7
cookie1 2015-04-10 10:50:05     url6    6       url6    url7
cookie1 2015-04-10 11:00:00     url7    7       url7    url7
cookie2 2015-04-10 10:00:00     url11   1       url11   url77
cookie2 2015-04-10 10:00:02     url22   2       url22   url77
cookie2 2015-04-10 10:03:04     1url33  3       1url33  url77
cookie2 2015-04-10 10:10:00     url44   4       url44   url77
cookie2 2015-04-10 10:50:01     url55   5       url55   url77
cookie2 2015-04-10 10:50:05     url66   6       url66   url77
cookie2 2015-04-10 11:00:00     url77   7       url77   url77

压缩和存储

MR 支持的压缩编码

压缩格式	算法	文件扩展名	是否可切分
DEFLATE	DEFLATE	.deflate	否
Gzip	DEFLATE	.gz	否
bzip2	bzip2	.bz2	是
LZO	LZO	.lzo	是
Snappy	Snappy	.snappy	否

为了支持多种压缩/解压缩算法，Hadoop 引入了编码/解码器，如下表所示：

压缩格式	对应的编码/解码器
DEFLATE	org.apache.hadoop.io.compress.DefaultCodec
gzip	org.apache.hadoop.io.compress.GzipCodec
bzip2	org.apache.hadoop.io.compress.BZip2Codec
LZO	com.hadoop.compression.lzo.LzopCodec

压缩参数配置

要在 Hadoop 中启用压缩，可以配置如下参数（mapred-site.xml 文件中）：

参数	默认值	阶段	建议
io.compression.codecs（在 core-site.xml 中配置）	org.apache.hadoop.io.compress.DefaultCodec, org.apache.hadoop.io.compress.GzipCodec, org.apache.hadoop.io.compress.BZip2Codec, org.apache.hadoop.io.compress.Lz4Codec	输入压缩	Hadoop 使用文件扩展名判断是否支持某种编解码器
mapreduce.map.output.compress	false	mapper 输出	这个参数设为 true 启用压缩
mapreduce.map.output.compress.codec	org.apache.hadoop.io.compress.DefaultCodec	mapper 输出	使用 LZO、LZ4 或snappy 编解码器在此阶段压缩数据
mapreduce.output.fileoutputformat.compress	false	reducer 输出	这个参数设为 true 启用压缩
mapreduce.output.fileoutputformat.compress.codec	org.apache.hadoop.io.compress. DefaultCodec	reducer 输出	使用标准工具或者编解码器，如 gzip 和 bzip2
mapreduce.output.fileoutputformat.compress.type	RECORD	reducer 输出	SequenceFile 输出使用的压缩类型：NONE和 BLOCK

开启 Map 输出阶段压缩（MR 引擎）

开启 map 输出阶段压缩可以减少 job 中 map 和 Reduce task 间数据传输量。具体配置如下：

1. 开启 hive 中间传输数据压缩功能

hive (default)>set hive.exec.compress.intermediate=true; 

2. 开启 mapreduce 中 map 输出压缩功能

hive (default)>set mapreduce.map.output.compress=true; 

3. 设置 mapreduce 中 map 输出数据的压缩方式

hive (default)>set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec; 

开启 Reduce 输出阶段压缩

当 Hive 将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为 true，来开启输出结果压缩功能。

1. 开启 hive 最终输出数据压缩功能

hive (default)>set hive.exec.compress.output=true; 

2. 开启 mapreduce 最终输出数据压缩

hive (default)>set mapreduce.output.fileoutputformat.compress=true; 

3. 设置 mapreduce 最终数据输出压缩方式

hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec; 

4. 设置 mapreduce 最终数据输出压缩为块压缩

hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK; 

5. 测试一下输出结果是否是压缩文件

hive (default)> insert overwrite local directory 
'/opt/module/data/distribute-result' select * from emp distribute by deptno sort by empno desc; 

存储方式和压缩总结

在实际的项目开发当中，hive 表的数据存储格式一般选择：orc 或 parquet。
压缩方式一般选择 snappy，lzo。