Hive常用操作持续更新！！！

– 创建库

create database my_test;
1

– 创建表
– 内部表

create table my_test.test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
-- 分区
partitioned by (dt string)
-- 定义字段之间的分隔符
row format delimited fields terminated by ','
-- 定义array、map数组实体和数据分隔符
collection items terminated by '_'
-- MAP 中的 key 与 value 的分隔符
map keys terminated by ':'
-- 定义每条数据的分隔符
lines terminated by '\n';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

–外部表

create external table my_test.external_test(
name string,
age string
)
partitioned by (dt string)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/external_test';
1
2
3
4
5
6
7

– 修改表名

ALTER TABLE table_name RENAME TO new_table_name;

-- 插入数据
insert into/overwrite table external_test partition (dt = '20220821') values ('张三','11'),('李四','12');

-- 上传数据
-- hive上传数据，本地文件上传到表中
load data local inpath '/opt/module/hive_datas/test.txt' into table test;
-- hive上传数据，hdfs文件上传到表中
load data inpath '/opt/module/hive_datas/test.txt' into table test;
-- 分区表上传数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into tabledept_partition2 partition(day='20200401',hour='15');
1
2
3
4
5
6
7
8
9
10
11
12

– 导出表中数据到本地
insert overwrite local directory ‘/opt/module/hive_xz/external_test’ select * from external_test;

– 动态分区
– 开启动态分区功能（默认 true，开启）

--设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使动态分区。）
set hive.exec.dynamic.partition.mode=nonstrict;
/**
不设置非严格模式直接使用动态分区会报错，报错信息如下

FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

**/
insert into table dept_partition_1
partition (day)
select * from dept_partition;
1
2
3
4
5
6
7
8
9
10
11

– 分桶表；分区针对的是数据的存储路径；分桶针对的是数据文件。
– 创建分桶表：注意！！！分区表分区字段是一个新字段，分桶表是已经定义的字段
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by ‘\t’;

– 函数
– NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则NVL函数返回default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。
select nvl(null, -1)

– case when then else end as

– 列转行
/**
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重
汇总，产生 Array 类型字段。
collect_list(col2) ：转换为数组，字段的值不进行去重
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字
符串;
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参
数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将
为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接
的字符串之间;
**/
将下图中数据转换为第二张图中形式

select concat_blood_type, concat_ws('|',collect_set(name)) from 
(select concat_ws(',',constellation,blood_type) concat_blood_type, name from person_info) a1
group by concat_blood_type
1
2
3

select * from poptbl2;
/**
pref_name       sex     population
德岛    1      60
德岛    2      40

**/

select
pref_name, 
max(case when sex = 1 then population else null end) nam,
max(case when sex = 2 then population else null end) woman 
from poptbl2
group by pref_name;

/**
pref_name       nam     woman
德岛    60      40

**/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

SELECT
movie,
category_name
FROM
movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;
1
2
3
4
5
6
7

– 开窗函数的使用
/**
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val)：往前第 n 行数据
LEAD(col,n, default_val)：往后第 n 行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对
于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。
**/

-- （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 name,month(orderdate)) from business;

--（3）上述的场景, 将每个顾客的 cost 按照日期进行累加
select name,orderdate,cost,sum(cost)over(partition by name order by orderdate) from business;

--（4）查询每个顾客上次的购买时间
select name,orderdate,cost,LAG(orderdate,1,orderdate)over(partition by name order by orderdate) from business;

--（5）查询前 20%时间的订单信息;解析：将数据分为五组数据取出第一组为前20%的数据
select * from
(select name,orderdate,cost,NTILE(5)over(order by orderdate) nt_ov from business)a where nt_ov = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

– Rank
/**
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
**/
– ）需求:计算每门学科成绩排名。

 select *,
 rank()over(partition by subject order by score)rk, 
 dense_rank()over(partition by subject order by score) dr, 
 row_number()over(partition by subject order by score) rn from score;
 
/**
结果：
score.name      score.subject   score.score     rk      dr      rn
大海    数学    56      1       1       1
婷婷    数学    85      2       2       2
宋宋    数学    86      3       3       3
孙悟空  数学    95      4       4       4
孙悟空  英语    68      1       1       1
婷婷    英语    78      2       2       2
宋宋    英语    84      3       3       3
大海    英语    84      3       3       4
宋宋    语文    64      1       1       1
婷婷    语文    65      2       2       2
孙悟空  语文    87      3       3       3
大海    语文    94      4       4       4
 **/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

常用日期函数
unix_timestamp:返回当前或指定时间的时间戳
select unix_timestamp();
输出：1661148336
select unix_timestamp(“2022-08-22”,‘yyyy-MM-dd’);
输出：1661126400
from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);
输出：2020-10-28 00:00:00

current_date：当前日期
select current_date;
输出：2022-08-21

current_timestamp：当前的日期加时间
select current_timestamp;
输出：2022-08-21 23:04:55.736

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:多维分析

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

（1）开启 hive 中间传输数据压缩功能
set hive.exec.compress.intermediate=true;
（2）开启 mapreduce 中 map 输出压缩功能
set mapreduce.map.output.compress=true;
（3）设置 mapreduce 中 map 输出数据的压缩方式
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
（4）执行查询语句
select count(ename) name from emp;

–开启 Reduce 输出阶段压缩
（1）开启 hive 最终输出数据压缩功能
set hive.exec.compress.output=true;
（2）开启 mapreduce 最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
（3）设置 mapreduce 最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec =
org.apache.hadoop.io.compress.SnappyCodec;
（4）设置 mapreduce 最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
（5）测试一下输出结果是否是压缩文件
insert overwrite local directory’/opt/module/data/distribute-result’ select * from emp distribute by deptno sort by empno desc;

列式存储和行式存储
1）行存储的特点
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列
的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度
更快。
2）列存储的特点
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的
数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算
法。
TEXTFILE 和 SEQUENCEFILE 的存储格式都是基于行存储的；
ORC 和 PARQUET 是基于列式存储的。

TextFile 格式
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合 Gzip、Bzip2 使用，
但使用 Gzip 这种方式，hive 不会对数据进行切分，从而无法对数据进行并行操作。

Orc 格式
Orc (Optimized Row Columnar)是 Hive 0.11 版里引入的新的存储格式。
如下图所示可以看到每个 Orc 文件由 1 个或多个 stripe 组成，每个 stripe 一般为 HDFS
的块大小，每一个 stripe 包含多条记录，这些记录按照列进行独立存储，对应到 Parquet
中的 row group 的概念。每个 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，即从后
往前读。

Parquet 格式
Parquet 文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的
数据和元数据，因此 Parquet 格式文件是自解析的。
（1）行组(Row Group)：每一个行组包含一定的行数，在一个 HDFS 文件中至少存储一
个行组，类似于 orc 的 stripe 的概念。
（2）列块(Column Chunk)：在一个行组中每一列保存在一个列块中，行组中的所有列连
续的存储在这个行组文件中。一个列块中的值都是相同类型的，不同的列块可能使用不同的
算法进行压缩。
（3）页(Page)：每一个列块划分为多个页，一个页是最小的编码的单位，在同一个列块
的不同页可能使用不同的编码方式。
通常情况下，在存储 Parquet 数据的时候会按照 Block 大小设置行组的大小，由于一般
情况下每一个 Mapper 任务处理数据的最小单位是一个 Block，这样可以把每一个行组由一
个 Mapper 任务处理，增大任务执行并行度。Parquet 文件的格式。
文件存储格式 注意：hdfs中存储文档大小比较 ORC < Parquet < textFile

– TextFile

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;
1
2
3
4
5
6
7
8
9
10
11

– orc存储建表语句； 注意！！ORC存储格式的表导入数据使用insert into即可

create table log_orc(
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 orc
tblproperties("orc.compress"="NONE");

insert into table log_orc select * from log_text;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

–创建表，存储数据格式为 parquet

create table log_parquet(
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 parquet;

insert into table log_parquet select * from log_text;
1
2
3
4
5
6
7
8
9
10
11
12
13

– 调优
1、小表大表 Join（MapJOIN）
将 key 相对分散，并且数据量小的表放在 join 的左边，可以使用 map join 让小的维度表
先进内存。在 map 端完成 join。
实际测试发现：新版的 hive 已经对小表 JOIN 大表和大表 JOIN 小表进行了优化。小表放
在左边和右边已经没有区别。

开启 MapJoin 参数设置
（1）设置自动选择 Mapjoin
set hive.auto.convert.join = true; 默认为 true
（2）大表小表的阈值设置（默认 25M 以下认为是小表）：
set hive.mapjoin.smalltable.filesize = 25000000;

2、 大表 Join 大表
2.1、空 KEY 过滤
有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同
的 reducer 上，从而导致内存不够。此时我们应该仔细分析这些异常的 key，很多情况下，
这些 key 对应的数据是异常数据，我们需要在 SQL 语句中进行过滤。例如 key 对应的字段为
空，

2.2、空 key 转换
有时虽然某个 key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在
join 的结果中，此时我们可以表 a 中 key 为空的字段赋一个随机的值，使得数据随机均匀地
分不到不同的 reducer 上。

3、Count(Distinct) 去重统计
数据量小的时候无所谓，数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个
Reduce Task 来完成，这一个 Reduce 需要处理的数据量太大，就会导致整个 Job 很难完成，
一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换,但是需要注意 group by 造成
的数据倾斜问题

select count(distinct id) from bigtable;
1

当数据量很大时可以开启两个reducer去运行

select count(id) from (select id from bigtable group by id) a;
1

4、行列过滤
列处理：在 SELECT 中，只拿需要的列，如果有分区，尽量使用分区过滤，少用 SELECT
*。
行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在 Where 后面，
那么就会先全表关联，之后再过滤

select o.id from bigtable b
join bigtable o on o.id = b.id
where o.id <= 10;
1
2
3

优化：

 select b.id from bigtable b
join (select id from bigtable where id <= 10) o on b.id = o.id;
1
2

练习题：
表结构如下

建表语句如下

create table gulivideo_ori(
 videoId string, 
 uploader string, 
 age int, 
 category array<string>, 
 length int, 
 views int, 
 rate float, 
 ratings int, 
 comments int,
 relatedId array<string>)
row format delimited fields terminated by "\t"
collection items terminated by "&"
stored as textfile;

create table gulivideo_user_ori(
 uploader string,
 videos int,
 friends int)
row format delimited 
fields terminated by "\t" 
stored as textfile;

create table gulivideo_orc(
 videoId string, 
 uploader string, 
 age int, 
 category array<string>, 
 length int, 
 views int, 
 rate float, 
 ratings int, 
 comments int,
 relatedId array<string>)
stored as orc
tblproperties("orc.compress"="SNAPPY");

create table gulivideo_user_orc(
 uploader string,
 videos int,
 friends int)
row format delimited 
fields terminated by "\t" 
stored as orc
tblproperties("orc.compress"="SNAPPY");


load data local inpath "/opt/module/hive_datas/2.txt" into table gulivideo_ori;
load data local inpath "/opt/module/hive_datas/user.txt" into table gulivideo_user_ori;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

– 统计视频观看数 Top10
– 统计视频类别热度 Top10
– 统计出视频观看数最高的 20 个视频的所属类别以及类别包含 Top20 视频的个数
– 统计视频观看数 Top50 所关联视频的所属类别排序
– 统计每个类别中的视频热度 Top10,以 Music 为例
– 统计每个类别视频观看数 Top10
– 统计上传视频最多的用户 Top10 以及他们上传的视频观看次数在前 20 的视频

查询test表中one，two，three存在2的数据

select * from test where 2 in (one,two,three);
1

查询test表中one，two，three，four全部为null的数据
COALESCE()函数：返回列表中第一个非null表达式的值。如果所有表达式求值为null，则返回null

select * from test where COALESCE(one,two,three,four) is null;
1