0%

HiveQL简单介绍

Hive 提供了类SQL的语法(HQL),封装了底层的 MapReduce 过程。

目录 概述
操作数据库 创建、修改、删除数据库,查看数据库描述信息
操作数据表 数据类型、复杂数据类型用例、数据类型转换,创建、修改、删除表,查看表的信息
插入数据 INSERT 关键字的使用
加载数据 LOAD 关键字的使用
多表插入与动态分区插入 扫描一遍源表就可以生成多个不相交的输出;在插入数据到分区表时,根据源表的列值自动创建分区
导入导出表数据 EXPORTIMPORT 关键字的使用
保存查询结果 保存查询结果到本地、HDFS 或 Hive 表
清空表数据 TRUNCATE 关键字的使用
查询数据 SELECT 关键字的使用
常用聚合函数 COUNTMAXMINSUMAVG 聚合函数的使用
分组 GROUP BYHAVING 关键字的使用
排序 ORDER BYSORT BYDISTRIBUTE BYCLUSTER BY 关键字的使用
连接 内连接、外连接、多表连接、分桶表连接
抽样 块抽样、分桶抽样、随机抽样
其他内置函数 日期函数、时间戳函数、字符函数、条件函数、窗口函数、排名函数、行转列、列转行
案例 复制分区表、统计分析与排名、ETL

本文主要介绍 HiveQL,可以在 Hive CLI 或 Beeline 中执行这些语句。

hive/
1
2
3
4
5
6
# 启动Metastore Server服务和HiveServer2服务
$ nohup bin/hive --service metastore 2>&1 &
$ nohup bin/hive --service hiveserver2 2>&1 &

# 启动Beeline并连接HiveServer2服务
$ bin/beeline -n root -p root -u jdbc:hive2://hadoopserver:10000

数据定义语言DDL

用来定义数据库对象,比如数据库,表,列等。

操作数据库

创建数据库

创建 Hive 数据库,可以指定创建位置:

1
2
3
4
5
-- 创建数据库
create database if not exists myhive;

-- 创建数据库并指定在HDFS上的存储位置
create database myhive2 location '/myhive2';

Hive 数据库默认存放位置的修改可以参考 数据库默认存放位置

查看数据库信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查看有哪些数据库
hive> show databases;
default
myhive

-- 查看数据库基本描述信息
hive> desc database myhive;
+----------+----------+--------------------------------------------------+-------------+-------------+-------------+--+
| db_name | comment | location | owner_name | owner_type | parameters |
+----------+----------+--------------------------------------------------+-------------+-------------+-------------+--+
| myhive2 | | hdfs://node01:8020/user/hive/warehouse/myhive.db | root | USER | |
+----------+----------+--------------------------------------------------+-------------+-------------+-------------+--+

-- 查看数据库扩展描述信息
hive> desc database extended myhive;
+----------+----------+--------------------------------------------------+-------------+-------------+-----------------------+--+
| db_name | comment | location | owner_name | owner_type | parameters |
+----------+----------+--------------------------------------------------+-------------+-------------+-----------------------+--+
| myhive2 | | hdfs://node01:8020/user/hive/warehouse/myhive.db | root | USER | {createtime=20190611} |
+----------+----------+--------------------------------------------------+-------------+-------------+-----------------------+--+

修改数据库

修改数据库的部分属性:

1
alter database myhive set dbproperties('createtime'='20190611');

注:数据库的大部分元数据信息,包括数据库的名称以及数据库所在的位置,是不可更改的。

删除数据库

1
2
3
4
5
-- 删除数据库,数据库中不能有数据表,否则会报错
drop database myhive2;

-- 删除数据库(级联),连同数据表一起删除
drop database myhive cascade;

操作数据表

数据类型

Hive 支持的数据类型如下:

分类类型描述示例
数值类型TINYINT1字节有符号整数,-128~1271Y
SMALLINT2字节有符号整数,-32768~327671S
INT/INTEGER4字节有符号整数1
BIGINT8字节有符号整数1L
FLOAT4字节单精度浮点数1.0
DOUBLE8字节双精度浮点数1.0
DEICIMAL表示任意精度(精度为38位)的有符号小数1.0
字符串类型STRING无上限可变长度字符串"abc"
VARCHAR可变长度字符串"abc"
CHARchar(length)。 固定长度字符串,需要指定长度"abc"
日期/时间类型TIMESTAMP精度到纳秒的时间戳1577808000, 2020-01-02 03:04:05.123456789
DATE日期,以 YYYY-­MM-­DD 的形式描述特定的年/月/日'2013-01-01'
INTERVAL时间频率间隔INTERVAL '1' DAY
其他类型BOOLEANtrue/falsetrue, false
BINARY字节数组可以包含任意字节,并且Hive不会将这些字节解析为数字或字符串。
复杂数据类型ARRAYARRAY<data_type>。有序的同类型的集合array(1,2)
MAPMAP<primitive_type, data_type>。一组无序的键-值对。键必须是原始类型,值可以是任意类型。同一个映射的键的类型必须相同,值的类型也必须相同map(‘a’,1,‘b’,2)
STRUCTSTRUCT<col_name : data_type [COMMENT col_comment], ...>。一组字段集合,字段的类型可以不同struct(‘a’,1,1.0), named_stract(‘col1’,‘1’,‘col2’,1,‘clo3’,1.0)
UNIONUNIONTYPE<data_type, data_type, ...>。值的数据类型可以使多个被定义的数据类型中的任意一个,这个值通过一个整数(零索引)来标记其为联合类型中的哪个数据类型created_union(1,‘a’,63)

说明:

  • Hive 的 String 类型相当于 MySQL 数据库的 varchar 类型,是一个可变的字符串,不过它不能声明最多存储多少个字符,理论上最大容量为 2 GB。
  • Hive 的 ArrayMap 与 Java 中的 ArrayMap 类似,而 Struct 与 C 语言中的 Struct 类似。
  • Hive 对复杂数据类型的支持仍不完整。在JOINWHEREGROUP BY子句中引用复杂数据类型字段的查询将失败,并且 Hive 没有定义语法来提取复杂数据类型的标记或值字段。
  • 如有复杂数据类型的需求可以将其转为 json 格式字符串进行存储,Hive 提供了 get_json_objectjson_tuple 函数用于解析 json 字符串。

复杂数据类型用例

需求

某表需要存储的数据结构如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
{
"name": "sannaha",
"friends": [
"Asuka",
"Ayanami"
],
"account": {
"steam": 76561198213678672,
"epic": 65782119236786761
},
"address": {
"city": "Beijing",
"country": "China"
}
}

{"duration" : 288, "dest_phone_number" : "17701282133", "cellid" : 111, "flag" : "called", "lac" : 6148}

由于 Hive 需要按行读取数据,基于上述结构,对数据进行处理,得到便于 Hive 处理的结构,并复制得到两条数据:

1
2
sannaha,Asuka_Ayanami,steam:76561198213678672_epic:65782119236786761,Beijing_China
sannaha2,Asuka_Ayanami,steam:76561198213678672_epic:65782119236786761,Beijing_China

其中保存 friends 使用 Array,account 使用 Map,address 使用 Struct。这里使用 _ 作为分隔符将 Map、Struct 和 Array 中的多个元素隔开。

1
2
sannaha,Asuka_Ayanami,steam:76561198213678672_epic:65782119236786761,Beijing_China
sannaha2,Asuka_Ayanami,steam:76561198213678672_epic:65782119236786761,Beijing_China

创建用户表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建表
create table myuser2(
name string,
friends array<string>,
account map<string,int>,
address struct<street:string, city:string>
)
row format delimited --行格式限定
fields terminated by ',' --列分隔符
collection items terminated by '_' --Map、Struct和Array中的元素分隔符
map keys terminated by ':' --Map中key与value的分隔符
lines terminated by '\n'; --行分隔符,可省略

-- 加载表数据
load data local inpath '/data/myuser.txt' into table myuser;

查询数据

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
-- 全查询
-- 查询表数据。发现由于account中的数值超出int范围,显示为null
select * from myuser;
myuser.name myuser.friends myuser.account myuser.address
sannaha ["Asuka","Ayanami"] {"steam":null,"epic":null} {"street":"Beijing","city":"China"}
sannaha2 ["Asuka","Ayanami"] {"steam":null,"epic":null} {"street":"Beijing","city":"China"}

--修改表,将account类型改为map<string,string>
alter table myuser change column account account map<string,string>;

-- 再次查询表数据,显示正常
select * from myuser;
myuser.name myuser.friends myuser.account myuser.address
sannaha ["Asuka","Ayanami"] {"steam":"76561198213678672","epic":"65782119236786761"} {"street":"Beijing","city":"China"}
sannaha2 ["Asuka","Ayanami"] {"steam":"76561198213678672","epic":"65782119236786761"} {"street":"Beijing","city":"China"}

-- 查询复杂数据类型内的数据
-- 查询Array类型字段内的数据,中括号中填写下标
select friends[0] as friends from myuser;
friends
Asuka
Asuka

-- 查询Map类型的字段内的数据,中括号中填写key
select account['epic'] as account from myuser;
account
65782119236786761
65782119236786761

-- 查询Struct类型的字段内的数据,点后面跟属性名
select address.street from myuser;
street
Beijing
Beijing

注:CDH 6.1.1的 Hive 的 hive.fetch.task.conversion 默认为 minimal,会对查询进行优化,某些情况的查询可以不触发 MapReduce 计算。对于 Map、Struct 和 Array 这种复杂数据类型内数据的查询, minimal 级别会触发 MapReduce 计算,如果更改级别为 more,则不会触发 MapReduce 计算。

数据类型转换

Hive 的基本数据类型是可以进行隐式转换的,类似于 Java 的类型转换,例如:某表达式使用 INT 类型,TINYINT 会自动转换为 INT 类型;但是 Hive 不能进行反向转化,例如:某表达式使用 TINYINT 类型,INT 不会自动转换为 TINYINT 类型,它会返回错误,除非使用 CAST。

  1. 隐式转换规则
  • 任何整数类型都可以隐式地转换为一个范围更广的类型,如 TINYINT 可以转换成 INT,INT 可以转换成 BIGINT。
  • 所有整数类型、浮点类型和字符串类型都可以隐式地转换成 DOUBLE。
  • TINYINT、SMALLINT、INT 都可以转换成 FLOAT。
  • BOOLEAN 类型不可以转换成任何其他类型。
  1. 强制转换
  • 可以使用 CAST 显式地进行数据类型转换。如 CAST('1' AS INT) 可以把字符串 1 转换成整数 1;如果强制类型转换失败,如 CAST('X' AS INT),表达式返回 NULL

创建表

语法格式

1
2
3
4
5
6
7
8
9
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

说明:

  1. CREATE TABLE:创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常。可以用 IF NOT EXISTS 选项来忽略这个异常。
  2. EXTERNAL:创建一个外部表,需要在建表的同时指定一个指向数据实际存放位置的路径。
  3. LIKE:复制现有的表结构,但是不复制表数据
  4. PARTITIONED BY/CLUSTERED BY:Hive 引入 Partition(分区)和 Bucket(桶)的概念,可以在创建表时进行设置,这样做可以让查询发生在小范围的数据上,提高效率。
  5. ROW FORMAT <row_format>|ROW FORMAT SERDE <serde_name>:创建表时可以自定义 SerDe(Serializer/Deserializer)或者使用默认的 SerDe(ROW FORMAT DELIMITED)。
  6. STORED AS <file_format>:如果文件数据是纯文本,可以使用 STORED AS TEXTFILE;如果数据需要压缩,使用STORED AS SEQUENCEFILE

内部表与外部表

  • 如果数据只由 Hive 使用,可以创建内部表(managed table,又称托管表),数据由 Hive 管理。删除内部表时,元数据和表数据会被一起删除
  • 如果数据是公共的,需要创建外部表(external table),数据由 HDFS 管理,一般是在数据的存放位置处创建外部表(使用 location 指定该目录)。删除外部表时,只删除元数据,不删除表数据
  • 创建表时(无论内部表还是外部表),如果不指定 location,那么会在 Hive 的仓库目录(/user/hive/warehouse/databasename.db)下生成一个与表名相同的目录,表中的数据会被存放在该目录下。
  • 加载数据(load data)时,如果加载的是本地文件系统中的文件(load data local inpath),则该文件会从本地文件系统复制到 HDFS 中的表目录中;如果是加载的是 HDFS 中的文件(load data inpath),则该文件会被移动到 HDFS 中的表目录中。
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
-- 选择使用的数据库
use myhive;

-- 创建内部表
create table if not exists student(id string, name string, score double);

-- 创建内部表,指定序列化方式、字段分隔符以及存储格式
create table if not exists student2(id string, name string, score double)
row format delimited
fields terminated by '\t'
stored as textfile;

-- 从本地文件系统加载数据到表中
load data local inpath '/data/stu.csv' into table student2;
-- 从HDFS加载并覆盖已有数据到表中
load data inpath '/hdfsdata/stu.csv' overwrite into table student2;

-- 根据查询结果创建内部表,包括表数据
create table student3 as select * from student2;
-- 根据已有的表结构创建内部表,不包括表数据
create table student4 like student2;

-- 创建外部表,指定序列化方式、字段分隔符以及存放位置
create external table stu(id string, name string, score double)
row format delimited
fields terminated by '\t'
location '/hivetable/stu/';

分区表

Hive 把表组织成分区,可以将数据根据日期、时间等进行粗粒度的划分,这样在查询的时候可以直接查询某个分区,提高查询速度。分区在 HDFS中 表现为目录

创建分区表(PARTITIONED BY)

1
2
3
4
5
6
7
8
9
10
11
-- 创建一个分区表
create table result(sid string, cid string, score double)
partitioned by (month string)
row format delimited
fields terminated by '\t';

-- 创建一个多分区表
create table result2(sid string, cid string, score double)
partitioned by (year string, month string, day string)
row format delimited
fields terminated by '\t';

加载数据

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
-- 加载数据到分区表,需要显式指定分区值
load data local inpath '/data/result.csv'
into table result
partition (month='202001');

load data local inpath '/data/result.csv'
into table result
partition (month='202005');

-- 查询分区表数据
-- 可以查看到分区字段存在,但是该字段并不存放实际的数据,仅仅是分区的表示(伪列)
select * from result;
result.sid result.cid result.score result.month
141360130 8848 89.5 202001
141360131 8848 99.0 202001
141360132 8848 86.5 202001
141360133 8848 87.5 202001
141360134 8848 84.5 202001
141360135 8848 72.5 202001
141360136 8848 79.5 202001
141360137 8848 69.0 202001
141360138 8848 59.5 202001
141360139 8848 88.5 202001
141360130 4576 79.5 202001
141360131 4576 59.0 202001
141360132 4576 86.0 202001
141360133 4576 85.5 202001
141360134 4576 94.5 202001
141360135 4576 72.5 202001
141360136 4576 69.0 202001
141360137 4576 89.0 202001
141360138 4576 69.5 202001
141360139 4576 68.0 202001
141360130 8848 89.5 202005
141360131 8848 99.0 202005
141360132 8848 86.5 202005
141360133 8848 87.5 202005
141360134 8848 84.5 202005
141360135 8848 72.5 202005
141360136 8848 79.5 202005
141360137 8848 69.0 202005
141360138 8848 59.5 202005
141360139 8848 88.5 202005
141360130 4576 79.5 202005
141360131 4576 59.0 202005
141360132 4576 86.0 202005
141360133 4576 85.5 202005
141360134 4576 94.5 202005
141360135 4576 72.5 202005
141360136 4576 69.0 202005
141360137 4576 89.0 202005
141360138 4576 69.5 202005
141360139 4576 68.0 202005

-- 加载数据到多分区表
load data local inpath '/data/result.csv'
into table result2
partition(year='2020',month='01',day='11');

load data local inpath '/data/result.csv'
into table result2
partition(year='2020',month='05',day='01');

-- 查询多分区表数据
> select * from result2;
result2.sid result2.cid result2.score result2.year result2.month result2.day
141360130 8848 89.5 2020 01 11
141360131 8848 99.0 2020 01 11
141360132 8848 86.5 2020 01 11
141360133 8848 87.5 2020 01 11
141360134 8848 84.5 2020 01 11
141360135 8848 72.5 2020 01 11
141360136 8848 79.5 2020 01 11
141360137 8848 69.0 2020 01 11
141360138 8848 59.5 2020 01 11
141360139 8848 88.5 2020 01 11
141360130 4576 79.5 2020 01 11
141360131 4576 59.0 2020 01 11
141360132 4576 86.0 2020 01 11
141360133 4576 85.5 2020 01 11
141360134 4576 94.5 2020 01 11
141360135 4576 72.5 2020 01 11
141360136 4576 69.0 2020 01 11
141360137 4576 89.0 2020 01 11
141360138 4576 69.5 2020 01 11
141360139 4576 68.0 2020 01 11
141360130 8848 89.5 2020 05 01
141360131 8848 99.0 2020 05 01
141360132 8848 86.5 2020 05 01
141360133 8848 87.5 2020 05 01
141360134 8848 84.5 2020 05 01
141360135 8848 72.5 2020 05 01
141360136 8848 79.5 2020 05 01
141360137 8848 69.0 2020 05 01
141360138 8848 59.5 2020 05 01
141360139 8848 88.5 2020 05 01
141360130 4576 79.5 2020 05 01
141360131 4576 59.0 2020 05 01
141360132 4576 86.0 2020 05 01
141360133 4576 85.5 2020 05 01
141360134 4576 94.5 2020 05 01
141360135 4576 72.5 2020 05 01
141360136 4576 69.0 2020 05 01
141360137 4576 89.0 2020 05 01
141360138 4576 69.5 2020 05 01
141360139 4576 68.0 2020 05 01

-- 分区表联合查询,使用 union all
select result.sid,result.cid,result.score,result.month as fenqu from result
where month='202005'
union all
select result2.sid,result2.cid,result2.score,result2.day as fenqu from result2
where year='2020' and month='05' and day='01';
_u1.sid _u1.cid _u1.score _u1.fenqu
141360130 8848 89.5 202005
141360131 8848 99.0 202005
141360132 8848 86.5 202005
141360133 8848 87.5 202005
141360134 8848 84.5 202005
141360135 8848 72.5 202005
141360136 8848 79.5 202005
141360137 8848 69.0 202005
141360138 8848 59.5 202005
141360139 8848 88.5 202005
141360130 4576 79.5 202005
141360131 4576 59.0 202005
141360132 4576 86.0 202005
141360133 4576 85.5 202005
141360134 4576 94.5 202005
141360135 4576 72.5 202005
141360136 4576 69.0 202005
141360137 4576 89.0 202005
141360138 4576 69.5 202005
141360139 4576 68.0 202005
141360130 8848 89.5 01
141360131 8848 99.0 01
141360132 8848 86.5 01
141360133 8848 87.5 01
141360134 8848 84.5 01
141360135 8848 72.5 01
141360136 8848 79.5 01
141360137 8848 69.0 01
141360138 8848 59.5 01
141360139 8848 88.5 01
141360130 4576 79.5 01
141360131 4576 59.0 01
141360132 4576 86.0 01
141360133 4576 85.5 01
141360134 4576 94.5 01
141360135 4576 72.5 01
141360136 4576 69.0 01
141360137 4576 89.0 01
141360138 4576 69.5 01
141360139 4576 68.0 01

查看分区

1
2
3
4
show partitions result;
partition
month=202001
month=202005

查看目录结构,验证分区在 HDFS中 表现为目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 单分区表表现为单级目录
$ hdfs dfs -ls -R /user/hive/warehouse/myhive.db/result
drwxrwxrwt - root hive 0 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result/month=202001
-rwxrwxrwt 3 root hive 402 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result/month=202001/result.csv
drwxrwxrwt - root hive 0 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result/month=202005
-rwxrwxrwt 3 root hive 402 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result/month=202005/result.csv

# 多分区表表现为多级目录
$ hdfs dfs -ls -R /user/hive/warehouse/myhive.db/result2
drwxrwxrwt - root hive 0 2020-06-16 15:27 /user/hive/warehouse/myhive.db/result2/year=2020
drwxrwxrwt - root hive 0 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result2/year=2020/month=01
drwxrwxrwt - root hive 0 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result2/year=2020/month=01/day=11
-rwxrwxrwt 3 root hive 402 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result2/year=2020/month=01/day=11/result.csv
drwxrwxrwt - root hive 0 2020-06-16 15:27 /user/hive/warehouse/myhive.db/result2/year=2020/month=05
drwxrwxrwt - root hive 0 2020-06-16 15:27 /user/hive/warehouse/myhive.db/result2/year=2020/month=05/day=01
-rwxrwxrwt 3 root hive 402 2020-06-16 15:27 /user/hive/warehouse/myhive.db/result2/year=2020/month=05/day=01/result.csv

添加分区

1
2
3
4
5
6
7
8
9
10
11
-- 添加一个分区
alter table result
add partition(month='202101');

-- 添加一个多分区
alter table result2
add partition(year='2021',month='01',day='11');

-- 一次添加多个分区
alter table result
add partition(month='202105') partition(month='202201');

删除分区

1
alter table result drop partition(month='202201');

外部分区表

场景:现有 HDFS 集群,会在对应的日期文件夹下生成文件 result.csv,如 /hivetable/score/month=202005/result.csv。文件为公共的,不能移动。

需求:创建对应的 Hive 表,将 result.csv 中的数据加载到表中;删除表后,数据不能删除。

数据准备:

1
2
$ hdfs dfs -mkdir -p /hivetable/result/month=202005
$ hdfs dfs -put result.csv /hivetable/result/month=202005/

实现:

  1. 创建外部分区表,指定数据文件存放目录:
1
2
3
4
5
6
7
8
9
create external table result3(sid string, cid string, score double)
partitioned by (month string)
row format delimited
fields terminated by '\t'
location '/hivetable/result';

-- 创建的外部分区表是无法查询到数据的
select * from result3;
result3.sid result3.cid result3.score result3.month
  1. 有两种方式可以让外部分区表能够查询到数据:
  • 修复表,建立表与数据文件之间的关系映射:
1
msck repair table result3;
  • 修改表,手动添加分区:
1
2
alter table result3 
add partition(month='202005');

查看元数据

创建外部分区表指定 location,或是以 dfs -put 的方式“加载”数据进分区表目录,数据都是无法被查询到的,原因就是没有生成 Hive 的元数据,而它存储在 MySQL 中:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> use metastore;

-- 查看分区元数据
mysql> select * from PARTITIONS;
+---------+-------------+------------------+---------------------------+-------+--------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME | SD_ID | TBL_ID |
+---------+-------------+------------------+---------------------------+-------+--------+
| 5179 | 1592292400 | 0 | month=202001 | 7821 | 2661 |
| 5180 | 1592292404 | 0 | month=202005 | 7822 | 2661 |
| 5181 | 1592292419 | 0 | year=2020/month=01/day=11 | 7823 | 2662 |
| 5182 | 1592292422 | 0 | year=2020/month=05/day=01 | 7824 | 2662 |
| 5185 | 1592292738 | 0 | month=202101 | 7828 | 2661 |
| 5186 | 1592292741 | 0 | year=2021/month=01/day=11 | 7829 | 2662 |
| 5187 | 1592292749 | 0 | month=202105 | 7830 | 2661 |
| 5191 | 1592292941 | 0 | month=202005 | 7836 | 2665 |
+---------+-------------+------------------+---------------------------+-------+--------+

分桶表

表或分区可以进一步分为桶(bucket),将数据根据列进行细粒度的划分,桶在 HDFS 中表现为文件

分桶的好处:

  1. 获得更高效的查询效率。分桶为数据提供额外的结构,两个在相同列上划分了桶的表,可以使用 Map 端 join 高效地实现连接操作。详见 *分桶表的连接 *

  2. 使取样(sample)更高效。详见 *分桶抽样 *

开启Hive的强制分桶(推荐)
允许根据表中指定的分桶个数选择正确的 Ruduce 的个数:

1
set hive.enforce.bucketing=true;

设置Reduce的个数
如果没有开启强制分桶,可以手动指定 Reduce 数量。默认情况下,该值为 -1,Hive 会自动计算出应该启动多少个 Reduce,也可以在本次会话中根据桶数指定 Reduce 个数:

1
set mapreduce.job.reduces=2;

创建分桶表(CLUSTERED BY)
创建一个根据 sid 字段进行分桶的表,桶的个数为 3:

1
2
3
4
create table result4(sid string, cid string, score double)
clustered by(sid) into 3 buckets
row format delimited
fields terminated by '\t';

加载数据
使用 load data 插入数据并不会进行 MapReduce,只生成了一个数据文件,无法得到分桶表。因此分桶表只能通过 insert 来插入数据:

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建内部表作为中间表用来加载数据
create table result4_common (sid string, cid string,score double)
row format delimited
fields terminated by '\t';

-- 加载数据到内部表
load data local inpath '/data/result.csv'
into table result4_common;

-- 插入数据到分桶表
insert into table result4
select sid,cid,score from result4_common cluster by(sid);

查看分桶表 result4 的目录结构,可以看到表数据以三个文件的形式进行保存:

1
2
3
4
5
$ hdfs dfs -ls -R /user/hive/warehouse/myhive.db/result4/
hdfs dfs -ls -R /user/hive/warehouse/myhive.db/result4/
-rwxrwxrwt 3 root hive 160 2020-06-16 15:41 /user/hive/warehouse/myhive.db/result4/000000_0
-rwxrwxrwt 3 root hive 120 2020-06-16 15:41 /user/hive/warehouse/myhive.db/result4/000001_0
-rwxrwxrwt 3 root hive 120 2020-06-16 15:41 /user/hive/warehouse/myhive.db/result4/000002_0

查看文件内容,发现数据被分为了两份,并且 sid 相同的数据被划分进同一个桶:

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
$ hdfs dfs -cat /user/hive/warehouse/myhive.db/result4/000000_0
141360130 4576 79.5
141360139 4576 68.0
141360136 8848 79.5
141360136 4576 69.0
141360139 8848 88.5
141360133 8848 87.5
141360133 4576 85.5
141360130 8848 89.5

$ hdfs dfs -cat /user/hive/warehouse/myhive.db/result4/000001_0
141360131 4576 59.0
141360134 4576 94.5
141360131 8848 99.0
141360134 8848 84.5
141360137 4576 89.0
141360137 8848 69.0

$ hdfs dfs -cat /user/hive/warehouse/myhive.db/result4/000002_0
141360135 4576 72.5
141360135 8848 72.5
141360138 8848 59.5
141360138 4576 69.5
141360132 8848 86.5
141360132 4576 86.0

数据划分的依据,就是根据分桶时指定的字段 sid 进行 Hash 散列,对桶的个数进行取模。

查看表信息

查看表的结构,包括字段、分区、以及是内部表还是外部表:

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
-- 查看当前库有哪些表,支持通配符
hive> show tables like 'stu*';
student

-- 查看指定库有哪些表
hive> show tables in myhive;
student
result
result2


-- 查看表的描述信息(使用Beeline查看更美观)
hive> desc student;
+-----------+------------+----------+
| col_name | data_type | comment |
+-----------+------------+----------+
| id | string | |
| name | string | |
| score | double | |
+-----------+------------+----------+

-- 查看表的描述信息(分区表)
hive> desc result2;
+--------------------------+-----------------------+-----------------------+
| col_name | data_type | comment |
+--------------------------+-----------------------+-----------------------+
| sid | string | |
| cid | string | |
| score | double | |
| year | string | |
| month | string | |
| day | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| | NULL | NULL |
| year | string | |
| month | string | |
| day | string | |
+--------------------------+-----------------------+-----------------------+

-- 查看外部表的结构化数据,可以通过Table Type区分内部表和外部表
hive> desc formatted stu;
+-------------------------------+----------------------------------------------------+-----------------------+
| col_name | data_type | comment |
+-------------------------------+----------------------------------------------------+-----------------------+
| # col_name | data_type | comment |
| | NULL | NULL |
| id | string | |
| name | string | |
| score | double | |
| | NULL | NULL |
| # Detailed Table Information | NULL | NULL |
| Database: | myhive | NULL |
| OwnerType: | USER | NULL |
| Owner: | root | NULL |
| CreateTime: | Tue Jun 16 15:13:08 CST 2020 | NULL |
| LastAccessTime: | UNKNOWN | NULL |
| Retention: | 0 | NULL |
| Location: | hdfs://devcdh1.cdh.com:8020/hivetable/stu | NULL |
| Table Type: | EXTERNAL_TABLE | NULL |
| Table Parameters: | NULL | NULL |
| | EXTERNAL | TRUE |
| | numFiles | 1 |
| | totalSize | 200 |
| | transient_lastDdlTime | 1592291588 |
| | NULL | NULL |
| # Storage Information | NULL | NULL |
| SerDe Library: | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | NULL |
| InputFormat: | org.apache.hadoop.mapred.TextInputFormat | NULL |
| OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | NULL |
| Compressed: | No | NULL |
| Num Buckets: | -1 | NULL |
| Bucket Columns: | [] | NULL |
| Sort Columns: | [] | NULL |
| Storage Desc Params: | NULL | NULL |
| | field.delim | \t |
| | serialization.format | \t |
+-------------------------------+----------------------------------------------------+-----------------------+

修改表

修改表名

1
2
-- 将student表的表名修改为student2
alter table student rename to student2;

增加/修改列信息

1
2
3
4
5
6
-- 添加列
alter table student2 add columns (mycol1 string, mycol2 string);

-- 修改列名及数据类型
alter table student2 change column mycol2 mycol_changed int;
alter table myuser change column account account map<string,string>;

删除表

删除表中所有数据,保留表的定义。如果是外部表,可以进入 Hive CLI,使用 dfs -rmr 删除外部表目录。

1
truncate table student2;

删除内部表的元数据和表数据。如果是外部表,只删除元数据,不删除表数据。

1
drop table student5;

数据操作语言DML

用来对数据库中表的记录进行更新。

插入数据

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
create table result5 like result;

-- 直接向分区表中插入数据(不推荐)
insert into table result5
partition(month ='202001')
values ('141360130','1551',100);

-- 通过查询方式插入数据到Hive表,Hive表需存在
insert into table result5 partition(month = '202005')
select sid, cid, score from result;

-- 通过查询方式插入数据到Hive表,会覆盖原来的数据
insert overwrite table result5 partition(month = '202005')
select sid, cid, score from result;

-- 通过查询方式插入数据,将数据保存到本地
insert overwrite local directory '/loca/path/'
row format delimited fields
terminated by '\t'
select * from stu;

-- 通过查询方插入数据,将数据保存到HDFS
insert overwrite directory '/hdfs/path/'
row format delimited fields
terminated by '\t'
select * from stu;

为什么要避免直接向 Hive 表中插入数据?

Hive 数据仓库主要是用来将 HDFS 中文件的数据映射成表数据,尽量避免使用 insert 逐条插入数据,因为:

  1. 每次插入会执行一次 MR 任务,插入速度慢。
  2. 每次插入会生成一个小文件来记录本次插入的数据,大量小文件的元数据信息会占用 NameNode 的内存空间。

验证loadstu.csv 文件进 stu 表后,再 insert 两条数据,产生两个小文件。

insert数据

加载数据

语法格式:

1
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]

说明:

  1. filepath 可以是:
  • 相对路径,如 project/file1。
  • 绝对路径,如 /data/project/file1。
  • 完整的 URL,如 hdfs://devcdh1.cdh.com:9000/data/project/file1
  1. 目标可以是一个表或是一个分区。如果目标表是分区表,必须指定是要加载到哪个分区。
  2. filepath 可以是一个文件,也可以是一个目录(会将目录下的所有文件都加载)。
  3. 如果命令中带 LOCAL,表示:
  • LOAD 命令从本地文件系统中加载数据,可以是相对路径,也可以是绝对路径。对于本地文件系统,也可以使用完整的 URL,如 file:///data/project/file1
  • LOAD 命令会根据指定的本地文件系统中的 filepath 复制文件到目标文件系统,然后再移到对应的表。
  1. 如果命令中没有 LOCAL,表示从 HDFS 加载文件,会移动文件到指定目录(而非复制)filepath 可以使用完整的 URL 方式,或者使用 fs.default.name 定义的值。
  2. 命令带 OVERWRITE 时加载数据之前会先清空目标表或分区中的内容,否则就是追加的方式。
1
2
3
4
-- 通过加载数据的方式插入数据
load data local inpath '/data/result.csv'
into table result5
partition(month='202001');

多表插入

多表插入,只需要扫描一遍源表就可以生成多个输出,将一张表的数据插入到多张表:

1
2
3
4
5
-- from source_table 指定源表
from source_table
-- 只能跟 insert 语句,指定数据插入到哪张表,select 后面不再需要 from 子句
insert <into/overwrite> table table1 select field1,field2... [where ...]
insert <into/overwrite> table table2 select field2,field3... [where ...]

这样可以减少表的扫描,减少 STAGE 数量,达到优化的目的。可以与动态分区插入共同使用。

动态分区插入

在插入数据到分区表时,只需在分区子句中指定分区列名列表,而列值是可选的。如果给出分区列值,我们将其称为静态分区;如果不指定分区列值,而是根据源表的列值自动生成,我们称为动态分区

每个动态分区列都有来自 SELECT 语句的相应的投影列,分区列表中动态分区列应位于静态分区列之后,并对应 PARTITION 子句中的顺序。

在Hive3.0.0(hive-19083)中,不需要为动态分区指定分区列。如果未指定分区规范,Hive将自动生成该分区规范。在Hive 0.9.0之前默认禁用动态分区插入,在Hive 0.9.0及更高版本中默认启用动态分区插入。下面是支持动态分区插入的相关配置属性:

配置属性 默认值 注释
hive.exec.dynamic.partition true 需要设置为true来启用动态分区插入
hive.exec.dynamic.partition.mode strict 在strict模式下,用户必须指定至少一个静态分区的情况下,防止不小心将覆盖所有分区,在nonstrict模式下,允许所有分区是动态的。
hive.exec.max.dynamic.partitions.pernode 100 允许在每个MAPPER/REDUCER节点创建动态分区的最大数目
hive.exec.max.dynamic.partitions 1000 允许创建动态分区的最大数目
hive.exec.max.created.files 100000 在MapReduce作业中所有MAPPER/REDUCER创建HDFS文件的最大数量
hive.error.on.empty.partition false 当动态分区插入产生空结果时,是否抛出一个异常
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
50
51
52
53
54
55
-- 创建内部表 result_source 作为源表
create table result_source(sid string, cid string, score double)
row format delimited
fields terminated by '\t'
location '/data/result';

-- 查询 result_source 表数据
> select * from result_source;
result_source.sid result_source.cid result_source.score
141360130 8848 89.5
141360131 8848 99.0
141360132 8848 86.5
141360133 8848 87.5
141360134 8848 84.5
141360135 8848 72.5
141360136 8848 79.5
141360137 8848 69.0
141360138 8848 59.5
141360139 8848 88.5
141360130 4576 79.5
141360131 4576 59.0
141360132 4576 86.0
141360133 4576 85.5
141360134 4576 94.5
141360135 4576 72.5
141360136 4576 69.0
141360137 4576 89.0
141360138 4576 69.5
141360139 4576 68.0

-- 创建分区表 result_partition,有 month 和 cid 两个分区
create table result_partition(sid string,score double)
partitioned by (month string, cid string)
row format delimited
fields terminated by '\t';

-- 动态分区插入
-- c_id 分区列会根据源表中 c_id 的列值动态创建,动态分区要放在静态分区字段之后
insert overwrite table result_partition partition(month = '202005',cid)
select sid, score, cid where score > 85;


-- 查询 result_partition 表数据
-- 可以看到 month 分区下动态创建了两个分区:cid=4576 和 cid=8848
> select * from result_partition;
result_partition.sid result_partition.score result_partition.month result_partition.cid
141360132 86.0 202005 4576
141360133 85.5 202005 4576
141360134 94.5 202005 4576
141360137 89.0 202005 4576
141360130 89.5 202005 8848
141360131 99.0 202005 8848
141360132 86.5 202005 8848
141360133 87.5 202005 8848
141360139 88.5 202005 8848

注意:在进行动态分区插入时,分区列需要放在 select 字段列表的末尾,如果将 score 放在了最后,那么会错误地根据 score 的值创建 cid 分区。验证如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 清空表数据
truncate table result_partition;
-- 重新插入数据
from result_source
insert overwrite table result_partition partition(month='202005',cid)
select sid, cid, score where score > 85;
-- 分区变为了 score 的值,cid 的值则存储在 score 列
> select * from result_partition;
result_partition.sid result_partition.score result_partition.month result_partition.cid
141360133 4576.0 202005 85.5
141360132 4576.0 202005 86.0
141360132 8848.0 202005 86.5
141360133 8848.0 202005 87.5
141360139 8848.0 202005 88.5
141360137 4576.0 202005 89.0
141360130 8848.0 202005 89.5
141360134 4576.0 202005 94.5
141360131 8848.0 202005 99.0

扩展阅读: Hive 官方手册翻译 – Hive DML(数据操纵语言)

导入导出表数据

使用 export 导出表数据到 HDFS:

1
2
-- 导出表(到HDFS)
export table result to '/hdfsdata/result';

查看导出文件:

1
2
3
4
5
6
7
# 查看导出文件
$ hdfs dfs -ls -R /hdfsdata/result/
-rwxr-xr-x 3 root supergroup 6312 2020-06-16 16:24 /hdfsdata/result/_metadata
drwxr-xr-x - root supergroup 0 2020-06-16 16:24 /hdfsdata/result/month=202001
-rwxr-xr-x 3 root supergroup 402 2020-06-16 16:24 /hdfsdata/result/month=202001/result.csv
drwxr-xr-x - root supergroup 0 2020-06-16 16:24 /hdfsdata/result/month=202005
-rwxr-xr-x 3 root supergroup 402 2020-06-16 16:24 /hdfsdata/result/month=202005/result.csv

使用 import 将 HDFS 文件导入为表数据:

1
2
3
4
-- 创建表
create table result2 like result;
-- 导入表数据(从HDFS)
import table result2 from '/hdfsdata/result';

保存查询结果

保存查询结果到本地

方法一:调用 hive 的标准输出,将查询结果写到指定的文件中:

1
2
3
4
# hive -e HQL语句 > file
$ hive -e "select * from dbname.tablename" > /local/path/filename.txt
# hive -f HQL脚本文件 > file
$ hive -f select.sql > /local/path/filename.txt

方法二:使用 INSERT OVERWRITE LOCAL DIRECTORY 保存结果到本地:

1
2
3
4
5
6
7
8
9
10
11
-- 将查询的结果格式化导出到本地
insert overwrite local directory '/data/exporthive/myuser'
row format delimited
fields terminated by '\t'
collection items terminated by '#'
select * from myuser;

-- 查看导出文件内容
$ cat /data/exporthive/myuser/000000_0
sannaha Asuka#Ayanami steam76561198213678672#epic65782119236786761 Beijing#China
sannaha2 Asuka#Ayanami steam76561198213678672#epic65782119236786761 Beijing#China

保存查询结果到HDFS

使用 INSERT OVERWRITE DIRECTORY 保存结果到 HDFS:

1
2
3
4
5
6
-- 将查询的结果格式化导出到HDFS
insert overwrite directory '/hdfsdata/exporthive'
row format delimited
fields terminated by '\t'
collection items terminated by '#'
select * from myuser;

保存查询结果到Hive表

如果表已建好,使用 INSERT OVERWRITE TABLE 将结果写入 Hive 表:

1
2
3
4
5
6
7
8
9
10
11
12
-- 通过查询方式插入数据到Hive表,会覆盖原来的数据
insert overwrite table result5 partition(month = '202005')
select sid, cid, score from result;

-- 通过查询方式插入数据到Hive表
insert into table result5 partition(month = '202005')
select sid, cid, score from result;

-- 根据查询结果创建内部表,包括表数据
create table student3 as select * from student2;
-- 根据已有的表结构创建内部表,不包括表数据
create table student4 like student2;

清空表数据

只能用于清空内部表,清空外部表会报错:

1
truncate table student2;

数据查询语言DQL

用来查询数据库中表的记录。

查询数据

语法结构:

1
2
3
4
5
6
7
8
9
10
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]

注意:

  1. GROUP BY 分组统计,通常与 count(*) 等聚合函数搭配使用,按照一个或者多个字段对结果进行分组,然后对每个组执行聚合操作。SELECT 语句只能查询 GROUP BY 子句中包含的字段(或包含其他字段的聚合函数)。
  2. ORDER BY 会做全局排序,因此只有一个 Reducer。当数据的规模较大时,需要较长的计算时间。
  3. SORT BY 只保证每个 Reducer 内有序。如果用 SORT BY 进行排序,并且设置mapred.reduce.tasks>1,则 SORT BY 不保证全局有序
  4. DISTRIBUTE BY 根据指定的字段将数据分发到不同的 Reducer,指定字段的值相同的行会被分发到同一个 Reducer。
  5. CLUSTER BY 用作 DISTRIBUTE BY+SORT BY 的替代写法。除了具有 DISTRIBUTE BY 的分发功能外,还会对该字段进行排序。

全表查询

Hive 对查询进行过优化,像这种不涉及计算的查询可以不触发 MapReduce(hive.fetch.task.conversion 设置为 minimal 也不触发):

1
2
3
4
5
6
7
8
9
10
11
12
13
> select * from stu;
OK
stu.id stu.name stu.score
141360131 Jerry 90.5
141360132 Asuka 99.0
141360133 Wade 100.0
141360134 Tabitha 100.0
141360135 Madeline 70.5
141360136 Albert 91.0
141360137 Vinny 67.5
141360138 Lucinda 79.0
141360139 Gillian 78.0
Time taken: 0.179 seconds, Fetched: 9 row(s)

查询指定列

可以用 AS 指定列别名:

1
select id as stuid ,name as stuname from stu;

不触发 MR

使用查询条件筛选

1
select * from stu where id <3;

minimal 触发 MR,more 不触发

限制返回行数

1
select * from stu limit 2;

不触发 MR

模糊查询

  • LIKE 运算选择类似的值。% 代表零个或多个字符,_ 代表一个字符。
  • RLIKE 是 Hive 提供的对 LIKE 的扩展,通过 Java 的正则表达式来指定匹配条件。
1
2
3
4
5
6
7
8
-- 查找成绩以8开头的所有数据
select * from result where score like '8%';

-- 查找成绩第二位为9的所有数据
select * from result where score like '_9%';

-- 查找成绩中含2的所有数据。正则表达式会对字符串进行匹配,需将score字段由double转为string,否则会报错
select * from result where cast(score as string) rlike '[5]';

minimal 触发 MR,more 不触发

关系运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查询id等于1的所有的数据
> select * from stu where id = '141360131';
stu.id stu.name stu.score
141360131 Jerry 90.5

-- 查询id数在1到3的所有数据
> select * from stu where id between '141360131' and '141360133';
stu.id stu.name stu.score
141360131 Jerry 90.5
141360132 Asuka 99.0
141360133 Wade 100.0

-- 查询名字为空的所有数据
> select * from stu where name is null;

-- 查询id是1和3的数据
> select * from stu where id in('141360131', '141360133');
stu.id stu.name stu.score
141360131 Jerry 90.5
141360133 Wade 100.0

minimal 触发 MR,more 不触发

下表详细介绍了关系运算符,这些运算符同样可以用于 JOIN ONHAVING 语句中。

运算符 支持的数据类型 描述
A=B, A==B 基本数据类型 如果A等于B则返回TRUE,反之返回FALSE
A<=>B 基本数据类型 如果A和B都为NULL,则返回TRUE;如果任意一个为NULL则结果为NULL;其他情况的和等号(=)运算符的结果一致
A<>B, A!=B 基本数据类型 A或者B为NULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE
A<B 基本数据类型 A或者B为NULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE
A<=B 基本数据类型 A或者B为NULL,则返回NULL;如果A小于等于B,则返回TRUE,反之返回FALSE
A>B 基本数据类型 A或者B为NULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE
A>=B 基本数据类型 A或者B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE
A [NOT] BETWEEN B AND C 基本数据类型 如果A,B或者C任一为NULL,则结果为NULL。如果A的值大于等于B而且小于或等于C,则结果为TRUE,反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL 所有数据类型 如果A等于NULL,则返回TRUE,反之返回FALSE
A IS NOT NULL 所有数据类型 如果A不等于NULL,则返回TRUE,反之返回FALSE
IN(数值1, 数值2) 所有数据类型 使用 IN运算显示列表中的值
A [NOT] LIKE B STRING 类型 B是一个SQL下的简单正则表达式,如果A与其匹配的话,则返回TRUE;反之返回FALSE。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母’x’结尾,而‘%x%’表示A包含有字母’x’,可以位于开头,结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B STRING 类型 B是一个正则表达式,如果A与其匹配,则返回TRUE;反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。

子查询部分

在一个复杂 SQL 中,对于会被多次使用的中间结果我们可以创建临时表,或是使用子查询部分。Hive 中可以使用 WITH AS 建立子查询部分,会将这个片段产生的结果集保存在内存中,供整个 SQL 使用。既可以优化执行速度,又能提高 SQL 可读性:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH sub_query_tmp1 AS 
(
SELECT field1
,field2
,...
FROM source_table
), sub_query_tmp2 AS
(
SELECT field1
,field2
,...
FROM sub_query_tmp1
)
-- SELECT * FROM sub_query_tmp2;
INSERT OVERWRITE TABLE target_table
SELECT * FROM sub_query_tmp2;

注意:

  1. WITH 只需写一次,多个子查询之间用逗号分隔;
  2. 子查询中不能使用 WITH AS 嵌套;
  3. 下面的子查询可以使用上面的子查询,比如 sub_query_tmp2 中就使用了 sub_query_tmp1
  4. 子查询部分后面不能加分号,需要跟上主体的查询语句或者插入语句。

常用聚合函数

聚合函数都会触发 MR:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 求总行数(count)
select count(1) from stu;

-- 求最大值(max)
select max(id) from stu;

-- 求最小值(min)
select min(id) from stu;

-- 求总和(sum)
select sum(s_score) from stu;

-- 求平均值(avg)
select avg(id) from stu;

分组

GROUP BY 语句通常会和聚合函数一起使用,按照一个或者多个字段对结果进行分组,然后对每个组执行聚合操作。SELECT 语句只能查询 GROUP BY 子句中包含的字段(或包含其他字段的聚合函数)。

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
-- 查询各个学生的平均分数
> select sid, avg(score) from result group by sid;
sid _c1
141360130 84.5
141360131 79.0
141360132 86.25
141360133 86.5
141360134 89.5
141360135 72.5
141360136 74.25
141360137 79.0
141360138 64.5
141360139 78.25

-- 查询各个学生的最高成绩
> select sid , max(score) from result group by sid;
sid _c1
141360130 89.5
141360131 99.0
141360132 86.5
141360133 87.5
141360134 94.5
141360135 72.5
141360136 79.5
141360137 89.0
141360138 69.5
141360139 88.5

HAVING 语句用于根据条件筛选结果。HAVINGWHERE 的不同点:

  • WHERE 的作用顺序早于 HAVING
  • WHERE 针对表中的字段发挥作用,剔除数据;HAVING 针对查询结果中的字段发挥作用,筛选数据。
  • WHERE 后面不能使用分组函数,HAVING 后面可以使用分组函数。
  • HAVING 只能用于 GROUP BY 分组统计语句。
1
2
3
4
5
6
-- 查询平均成绩大于85的
> select sid ,avg(score) avgscore from result group by sid having avgscore > 85;
sid avgscore
141360132 86.25
141360133 86.5
141360134 89.5

排序

ORDER BY

全局排序,只有一个 ReducerORDER BY 子句在SELECT语句的末尾,默认为ASC升序,DESC为降序。

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
50
51
52
53
54
55
56
-- 查询学生的成绩,并按照分数降序排列
> SELECT s.id,s.name,r.cid,r.score FROM stu s
LEFT JOIN result r ON s.id = r.sid
WHERE r.month = '202001'
ORDER BY r.score DESC;
141360131 Jerry 8848 99.0
141360134 Tabitha 4576 94.5
141360137 Vinny 4576 89.0
141360139 Gillian 8848 88.5
141360133 Wade 8848 87.5
141360132 Asuka 8848 86.5
141360132 Asuka 4576 86.0
141360133 Wade 4576 85.5
141360134 Tabitha 8848 84.5
141360136 Albert 8848 79.5
141360135 Madeline 8848 72.5
141360135 Madeline 4576 72.5
141360138 Lucinda 4576 69.5
141360136 Albert 4576 69.0
141360137 Vinny 8848 69.0
141360139 Gillian 4576 68.0
141360138 Lucinda 8848 59.5
141360131 Jerry 4576 59.0

-- 分组排序
> SELECT sid ,avg(score) avg FROM result
WHERE month = '202001'
GROUP BY sid
ORDER BY avg;
sid avg
141360138 64.5
141360135 72.5
141360136 74.25
141360139 78.25
141360137 79.0
141360131 79.0
141360130 84.5
141360132 86.25
141360133 86.5
141360134 89.5

-- 多个列排序
SELECT sid, avg(score) avg FROM result
GROUP BY sid
ORDER BY avg DESC,sid DESC;
sid avg
141360134 89.5
141360133 86.5
141360132 86.25
141360130 84.5
141360137 79.0
141360131 79.0
141360139 78.25
141360136 74.25
141360135 72.5
141360138 64.5

SORT BY

每个 Reducer 内部进行排序,对全局结果集来说不保证有序

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
-- 查看reduce个数
set mapreduce.job.reduces;

-- 设置reduce个数
set mapreduce.job.reduces=3;

-- 查询成绩按照成绩降序排列
select * from result sort by score desc;

-- 将查询结果导出到文件中(按照学号降序排列)
insert overwrite local directory '/data/sort/ResultSort'
row format delimited
fields terminated by '\t'
select sid, cid, score from result
where month='202001'
sort by sid desc;

$ cat /data/sort/ResultSort/000000_0
141360139 8848 88.5
141360138 4576 69.5
141360138 8848 59.5
141360137 4576 89.0
141360137 8848 69.0
141360136 8848 79.5
141360135 8848 72.5
141360134 8848 84.5
141360134 4576 94.5

$ cat /data/sort/ResultSort/000001_0
141360139 4576 68.0
141360136 4576 69.0
141360133 4576 85.5
141360133 8848 87.5
141360132 8848 86.5
141360131 4576 59.0
141360131 8848 99.0
141360130 4576 79.5

$ cat /data/sort/ResultSort/000002_0
141360135 4576 72.5
141360132 4576 86.0
141360130 8848 89.5

DISTRIBUTE BY

类似 MapReduce 中 Partition,根据指定的字段将数据分发到不同的 Reducer,指定字段的值相同的行会被分发到同一个 Reducer。DISTRIBUTE BY 不会进行排序,但可以结合SORT BY语句实现 Reducer 内部排序。Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前,并且分发字段和排序字段可以不同

注意:DISTRIBUTE BY 同样需要设置多个 Reduce 进行处理,否则无法看到效果。

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
-- 将查询结果导出到文件中(按照学号分发,按照成绩降序排列)
insert overwrite local directory '/data/sort/ResultSort2'
row format delimited
fields terminated by '\t'
select sid, cid, score from result
where month='202001'
distribute by sid
sort by score desc;

$ cat /data/sort/ResultSort2/000000_0
141360130 8848 89.5
141360139 8848 88.5
141360133 8848 87.5
141360133 4576 85.5
141360136 8848 79.5
141360130 4576 79.5
141360136 4576 69.0
141360139 4576 68.0

$ cat /data/sort/ResultSort2/000001_0
141360131 8848 99.0
141360134 4576 94.5
141360137 4576 89.0
141360134 8848 84.5
141360137 8848 69.0
141360131 4576 59.0


$ cat /data/sort/ResultSort2/000002_0
141360132 8848 86.5
141360132 4576 86.0
141360135 8848 72.5
141360135 4576 72.5
141360138 4576 69.5
141360138 8848 59.5

CLUSTER BY

DISTRIBUTE BYSORT BY字段相同时,可以使用CLUSTER BY替代。排序结果为正序。

1
2
3
-- 以下两种写法等价
select * from result cluster by sid;
select * from result distribute by sid sort by sid;

连接

等值JOIN

Hive 支持通常的 SQL JOIN 语句,但是只支持等值连接,不支持非等值连接

1
2
3
4
-- 查询分数对应的姓名
SELECT s.s_id,s.s_score,stu.s_name,stu.s_birth FROM score s
JOIN student stu
ON s.s_id = stu.s_id;

内连接

进行内连接(INNER JOIN)的两个表,表中符合连接条件的数据才会被保留下来:

1
2
3
select * from teacher t 
inner join course c
on t.t_id = c.t_id;

左、右外连接

  • 左外连接(LEFT [OUTER] JOIN):以左侧为基底,返回左表中所有行,如果右侧没有匹配的行,其提供的字段返回空值。
  • 右外连接(RIGHT [OUTER] JOIN):以右侧为基底,返回右表中所有行,如果左侧没有匹配的行,其提供的字段返回空值。
1
2
3
4
5
6
7
8
-- 查询老师对应的课程
select * from teacher t
left join course c
on t.t_id = c.t_id;

select * from teacher t
right join course c
on t.t_id = c.t_id;

满外连接

满外连接(FULL [OUTER] JOIN)返回所有表中符合 WHERE 子句的所有记录。如果任一表的指定字段没有符合条件的值的话,就使用NULL值替代。

1
SELECT * FROM teacher t FULL JOIN course c ON t.t_id = c.t_id ;

多表连接

多表连接查询。连接 n 个表,至少需要 n-1 个连接条件。

1
2
3
4
5
6
7
8
查询老师对应的课程,以及对应的分数,对应的学生
select * from teacher t
left join course c
on t.t_id = c.t_id
left join score s
on s.c_id = c.c_id
left join student stu
on s.s_id = stu.s_id;

大多数情况下,Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首先启动一个MapReduce Job 对techer表和course表进行连接操作,然后会再启动一个 MapReduce Job 将第一个 MapReduce Job 的输出和表score进行连接操作。

分桶表的连接

分桶为数据提供额外的结构,两个在相同列上划分了桶的表,可以使用 Map 端 join 高效地实现连接操作。原因是对相同列进行的 Hash 散列再根据桶数取模,只要两个表的桶数为整数倍,那么很容易根据一条数据在左表的桶号推断出在右表的哪个桶中。因此,Mapper 只需要获取那个桶即可进行连接。

数据抽样

在大规模数据量的数据分析及建模任务中,针对全量数据进行挖掘分析往往会十分耗时和占用集群资源,因此一般情况下只需要抽取一小部分数据进行分析及建模操作。

块抽样

使用 Hive 提供的 TABLESAMPLE() 函数来进行块抽样(Block Sampling):

  • TABLESAMPLE (n PERCENT):根据原始数据大小的百分比来抽样。如果以块抽样(百分比)的方式创建抽样表,会在 HDFS 块级别执行此操作,以便采样粒度为块大小。例如,如果块大小为 256 MB,即使 n% 的输入大小仅为 100 MB,创建的抽样表中也会获得 256 MB的数据。
  • TABLESAMPLE (nM):根据样本大小来抽样,样本的大小为 n MB。
  • TABLESAMPLE (n ROWS):根据行数来抽样。这里行数,指的是在每个 InputSplit 中取样的行数,也就是每个 Map 中都取样 n 行。

实例

查询数据:

1
2
3
4
5
6
7
8
9
10
11
> select * from stu;
stu.id stu.name stu.score
1 Sannaha 100.0
2 Jerry 90.5
3 Asuka 99.0
4 Wade 100.0
5 Tabitha 100.0
6 Madeline 70.5
7 Albert 91.0
8 Vinny 67.5
9 Lucinda 79.0

块抽样:

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- 块抽样(百分比),抽样结果显示10%的数据,而非最少包含1个 HDFS 块的数据
> select * from stu tablesample(10 percent);
stu.id stu.name stu.score
1 Sannaha 100.0

> select * from stu tablesample(20 percent);
stu.id stu.name stu.score
1 Sannaha 100.0
2 Jerry 90.5

> select * from stu tablesample(50 percent);
stu.id stu.name stu.score
1 Sannaha 100.0
2 Jerry 90.5
3 Asuka 99.0
4 Wade 100.0
5 Tabitha 100.0

-- 块抽样(样本大小)
> select * from stu tablesample(1M);
stu.id stu.name stu.score
1 Sannaha 100.0
2 Jerry 90.5
3 Asuka 99.0
4 Wade 100.0
5 Tabitha 100.0
6 Madeline 70.5
7 Albert 91.0
8 Vinny 67.5
9 Lucinda 79.0

-- 块抽样(行数)
> select * from stu tablesample(1 rows);
stu.id stu.name stu.score
1 Sannaha 100.0

> select * from stu tablesample(5 rows);
stu.id stu.name stu.score
1 Sannaha 100.0
2 Jerry 90.5
3 Asuka 99.0
4 Wade 100.0
5 Tabitha 100.0

-- 根据块抽样(百分比)创建抽样表stu_sample1
create table stu_sample1 as select * from stu tablesample(10 percent);
-- 查询抽样表stu_sample1,抽样结果最少包含1个HDFS块的数据,与不创建抽样表的块取样结果不同
> select * from stu_sample1;
stu_sample1.id stu_sample1.name stu_sample1.score
1 Sannaha 100.0
2 Jerry 90.5
3 Asuka 99.0
4 Wade 100.0
5 Tabitha 100.0
6 Madeline 70.5
7 Albert 91.0
8 Vinny 67.5
9 Lucinda 79.0

-- 根据块抽样(样本大小)创建抽样表stu_sample2
create table stu_sample2 as select * from stu tablesample(1M);

-- 根据块抽样(样本大小)创建抽样表stu_sample3
create table stu_sample3 as select * from stu tablesample(5 rows);
-- 查询抽样表stu_sample1,与不创建抽样表的块取样结果相同
> select * from stu_sample3;
stu_sample3.id stu_sample3.name stu_sample3.score
1 Sannaha 100.0
2 Jerry 90.5
3 Asuka 99.0
4 Wade 100.0
5 Tabitha 100.0

分桶抽样

通常,TABLESAMPLE 将扫描整个表并获取样本,这样并不高效。因此,可以使用 CLUSTERED BY 创建分桶表,如果 TABLESAMPLE 中指定的列与分桶表指定的列匹配,则 TABLESAMPLE 仅扫描表所需的哈希分区。

注:并非只有分桶表可以进行分桶抽样

语法:

1
TABLESAMPLE(BUCKET x OUT OF y [ON colname])

说明:

  • x:抽样的桶编号。桶的编号从 1 开始。
  • y:可以理解为分桶抽样时“临时桶”的数量。如果是分桶表,建议是分桶表桶数的倍数或因数。
  • colname:抽样的列,可以是分区列以外的任意列。

现有分桶表 source 有 32 个根据 id 字段创建的桶(CLUSTERED BY id INTO 32 BUCKETS):

1
2
3
4
5
6
7
-- 抽样数据选择3号和19号桶
-- 因为32个桶形成16个clusters,每个cluster将由2个桶组成
TABLESAMPLE(BUCKET 3 OUT OF 16 ON id)

-- 抽样数据选择3号桶的一半
-- 因为32个桶形成64个clusters,每个cluster将由0.5个桶组成
TABLESAMPLE(BUCKET 3 OUT OF 64 ON id)

实例

创建分桶表 stu2,根据 id 字段分为 3 桶,插入 stu 表中数据:

1
2
3
4
5
6
create table stu2(id string,name string,score double) 
clustered by(id) into 3 buckets
row format delimited
fields terminated by '\t';

insert into table stu2 select * from stu cluster by (id);

查询数据:

1
2
3
4
5
6
7
8
9
10
11
12
> select * from stu2;
OK
stu2.id stu2.name stu2.score
9 Lucinda 79.0
3 Asuka 99.0
6 Madeline 70.5
7 Albert 91.0
1 Sannaha 100.0
4 Wade 100.0
5 Tabitha 100.0
8 Vinny 67.5
2 Jerry 90.5

桶文件内容:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ hdfs dfs -cat /user/hive/warehouse/myhive.db/stu2/000000_0
9 Lucinda 79.0
3 Asuka 99.0
6 Madeline 70.5

$ hdfs dfs -cat /user/hive/warehouse/myhive.db/stu2/000001_0
7 Albert 91.0
1 Sannaha 100.0
4 Wade 100.0

$ hdfs dfs -cat /user/hive/warehouse/myhive.db/stu2/000002_0
5 Tabitha 100.0
8 Vinny 67.5
2 Jerry 90.5

分桶抽样:

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
-- 分桶抽样,对比非分桶表stu和分桶表stu2的抽样结果,结果一致
> select * from stu tablesample(bucket 1 out of 3 on id);
stu.id stu.name stu.score
3 Asuka 99.0
6 Madeline 70.5
9 Lucinda 79.0

-- 抽样时的桶数等于分桶表的桶数时,对比桶文件内容和分桶表stu2的抽样结果,结果一致
> select * from stu2 tablesample(bucket 1 out of 3 on id);
stu2.id stu2.name stu2.score
9 Lucinda 79.0
3 Asuka 99.0
6 Madeline 70.5

> select * from stu2 tablesample(bucket 2 out of 3 on id);
stu2.id stu2.name stu2.score
7 Albert 91.0
1 Sannaha 100.0
4 Wade 100.0

> select * from stu2 tablesample(bucket 3 out of 3 on id);
stu2.id stu2.name stu2.score
5 Tabitha 100.0
8 Vinny 67.5
2 Jerry 90.5

-- 抽样时的桶数为分桶表桶数的倍数/因数
> select * from stu2 tablesample(bucket 1 out of 6 on id);
stu2.id stu2.name stu2.score
6 Madeline 70.5

-- 抽样时的桶数不为分桶表桶数的倍数/因数
> select * from stu2 tablesample(bucket 1 out of 4 on id);
stu2.id stu2.name stu2.score
4 Wade 100.0
8 Vinny 67.5

-- 将分桶抽样结果创建为抽样结果表stu2_sample
> create table stu2_sample as select id,name,score from stu2 tablesample(bucket 1 out of 3 on id);
-- 查询抽样结果表stu2_sample,与分桶抽样查询结果一致
> select * from stu2_sample;
stu2_sample.id stu2_sample.name stu2_sample.score
9 Lucinda 79.0
3 Asuka 99.0
6 Madeline 70.5

随机抽样

其他内置函数

日期函数

  1. 格式化日期:date_format
1
2
3
4
5
6
-- 按指定格式返回时间date
date_format(date/timestamp/string ts, string fmt)

-- 示例
select date_format('2020-05-01', 'yyyy/MM/dd HH:mm:ss');
2020/05/01 00:00:00
  1. 日期增加天数:date_add
1
2
3
4
5
6
7
8
-- 从开始时间startdate加上days
date_add(string startdate, int days)

-- 示例
select date_add('2020-05-01', 5);
2020-05-06
select date_add('2020-05-01', -5);
2020-04-26
  1. 日期减少天数:date_sub
1
2
3
4
5
6
7
8
-- 从开始时间startdate减去days
date_sub(string startdate, int days)

-- 示例
select date_sub('2020-05-01', 5);
2020-04-26
select date_sub('2020-05-01 12:00:00', 5);
2020-04-26
  1. 日期差异:datediff
1
2
3
4
5
6
-- 计算开始时间startdate到结束时间enddate相差的天数
datediff(string enddate, string startdate)

-- 示例
select datediff('2020-05-01', '2020-01-01');
121

注:日期函数要求日期分隔符为 -,如果数据格式不符合要求,可以使用时间戳函数两次转换、字符的截取和拼接、正则替换等方式进行转换,或是在 UDF 中实现。

时间戳函数

  1. 获取当前时间戳
1
2
3
4
5
6
-- 获取当前时间戳
unix_timestamp()

-- 示例
select unix_timestamp();
1626318568
  1. 日期转时间戳
1
2
3
4
5
6
7
8
9
10
-- 将日期字符串转为时间戳,默认日期格式为yyyy-MM-dd HH:mm:ss,格式不匹配返回NULL
unix_timestamp(string date[, string pattern])

-- 示例
select UNIX_TIMESTAMP('2021-05-01 23:33:33');
1619912013
select UNIX_TIMESTAMP('2021-05-01');
NULL
select UNIX_TIMESTAMP('20210501', 'yyyyMMdd');
1619827200
  1. 时间戳转日期
1
2
3
4
5
6
7
8
-- 将时间戳转为指定日期格式
from_unixtime(bigint unixtime[, string format])

-- 示例
select FROM_UNIXTIME(1619912013);
2021-05-01 23:33:33
select FROM_UNIXTIME(1619912013, 'yyyy-MM-dd');
2021-05-01
  1. 将日期数据转为符合日期函数要求的 yyyy-MM-dd
1
2
select FROM_UNIXTIME(UNIX_TIMESTAMP('20210501', 'yyyyMMdd'), 'yyyy-MM-dd');
2021-05-01

字符函数

  1. 字符串拼接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 对二进制字节码或字符串按次序进行拼接
concat(string|binary A, string|binary B...)
-- 使用指定的分隔符对字符串进行拼接
concat_ws(string SEP, string A, string B...)
-- 使用指定的分隔符拼接Array中的元素
concat_ws(string SEP, array<string>)

-- 示例
select concat('sannaha','-','blog',':','sannaha.moe');
sannaha-blog:sannaha.moe
select concat_ws('-','sannaha','blog','sannaha.moe');
sannaha-blog-sannaha.moe
select concat_ws('-',array('sannaha','blog','sannaha.moe'));
sannaha-blog-sannaha.moe
  1. 字符串截取
1
2
3
4
5
6
7
8
9
10
-- 对于二进制/字符串A,从start位置开始截取长度为length的字符串并返回
substr(string|binary A, int start, int len)
substring(string|binary A, int start, int len)

-- 示例
select substr('www.sannaha.moe',5,7);
sannaha
-- 将日期数据拼接符合日期函数要求的yyyy-MM-dd
select concat_ws('-',substr('20171205',1,4),substr('20171205',5,2),substr('20171205',7,2));
2017-12-05
  1. 字符串分割
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 按照正则表达式pat来分割字符串str,分割后返回字符串数组
split(string str, string pat)
-- 将字符串str按照指定分隔符转换成Map
-- 第一个参数是需要转换字符串
-- 第二个参数是键值对之间的分隔符,默认为逗号
-- 第三个参数是键值之间的分隔符,默认为冒号
str_to_map(string str[, delimiter1, delimiter2])

-- 示例
select split("sannaha-blog-sannaha.moe",'-');
["sannaha","blog","sannaha.moe"]
select str_to_map("blog=sannaha.moe,homepage=thinklong.me",',','=');
{"homepage":"thinklong.me","blog":"sannaha.moe"}
select str_to_map("blog:sannaha.moe,homepage:thinklong.me");
{"homepage":"thinklong.me","blog":"sannaha.moe"}
  1. 正则提取和替换
1
2
3
4
5
6
7
8
9
10
11
12
-- 提取字符串str中符合正则表达式pattern的第index个部分的子字符串
regexp_extract(string str, string pattern, int index)
-- 按照Java正则表达式PATTERN将字符串str中符合条件的部分替换为REPLACEMENT指定的字符串
-- 如里REPLACEMENT为空串,抽符合正则的部分将被去掉
regexp_replace(string str, string PATTERN, string REPLACEMENT)

-- 提取域名,也可以使用专门处理URL的parse_url函数
select regexp_extract("https://www.sannaha.moe/Hive-Part2/", "(http(s)?:\/\/)?(www\.)?([a-zA-Z0-9]{0,62}\.[a-zA-Z0-9]{0,62})",4);
sannaha.moe
-- 正则替换日期数据为符合日期函数要求的yyyy-MM-dd
select regexp_replace('2020/05/01,2020 05 01', '\\s|/', '-');
2020-05-01,2020-05-01

正则-匹配域名

可视化观察正则表达式:*Regulex *

  1. 提取 URL 信息:
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
-- 从URL中提取指定部分的内容
-- 参数url是URL字符串
-- URL = PROTOCOL:[//authority]path[?QUERY][#REF]
-- authority = [[email protected]]host[:port]
-- 参数partToExtract是要抽取的部分,包括 HOST, AUTHORITY, PATH, QUERY, REF, PROTOCOL, FILE, USERINFO
-- partToExtract值为QUERY时可以指定第三个参数keyToExtract
parse_url(string url, string partToExtract [, string keyToExtract])

-- 提取主机名(HOST)
select parse_url("https://[email protected]:8080/Hive-Part2/", "HOST");
sannaha.moe
-- 提取权限(AUTHORITY),authority = [[email protected]]host[:port]
select parse_url("https://[email protected]:8080/Hive-Part2/", "AUTHORITY");
root@sannaha.moe:8080
-- 提取用户信息(USERINFO),如果没有则为NULL
select parse_url("https://[email protected]:8080/Hive-Part2/", "USERINFO");
root
select parse_url("https://sannaha.moe/Hive-Part2/login?u=root#字符函数", "PATH");
/Hive-Part2/login
select parse_url("https://sannaha.moe/Hive-Part2/login?u=root&p=123#字符函数", "QUERY");
u=root&p=123
select parse_url("https://sannaha.moe/Hive-Part2/login?u=root&p=123#字符函数", "QUERY", "u");
root
select parse_url("https://sannaha.moe/Hive-Part2/login?u=root#字符函数", "REF");
字符函数
select parse_url("https://sannaha.moe/Hive-Part2/login?u=root#字符函数", "PROTOCOL");
https
select parse_url("https://sannaha.moe/Hive-Part2/正则-匹配域名.png", "FILE");
/Hive-Part2/正则-匹配域名.png
  1. 去除空格
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 将字符串A前后出现的空格去掉
trim(string A)
-- 去掉字符串A前面的空格
ltrim(string A)
-- 去掉字符串后面出现的空格
rtrim(string A)

-- 示例
select trim(' sannaha ');
sannaha
select ltrim(' sannaha ');
sannaha
select rtrim(' sannaha ');
sannaha
  1. 字符串长度
1
2
3
4
5
6
-- 返回字符串A的长度
length(string A)

-- 示例
select length('sannaha');
7
  1. 千分位表示
1
2
3
4
5
6
-- 将数值x转换成用千分位表示的字符串,四舍五入保留d位小数,如果d为0将不保留小数
format_number(number x, int d)

-- 示例
select format_number(1919810.114514, 5);
1,919,810.11451

解析json

Hive 提供了 get_json_objectjson_tuple 两个内置函数来解析 json 字符串。

解析一个字段

get_json_object 每次可以解析一个字段:

1
2
3
4
5
6
7
8
9
-- 解析 json_str,返回 field_str
-- 如果解析无效,返回 NULL
get_json_object(string json_str,string $.key)

-- 示例
hive> select get_json_object('{"movie":"流浪地球","rate":"5"}','$.movie') AS movie,
get_json_object('{"movie":"流浪地球","rate":"5"}','$.rate') AS rate;
movie rate
流浪地球 5

解析多个字段

json_tuple 是个 UDTF 函数,可以一次解析多个 json 字段,经常与 lateral view 一起使用:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 解析 json_str
json_tuple(string json_str,string key1,string key2,...)

-- 示例一,直接使用函数
hive> select json_tuple('{"movie":"流浪地球","rate":"5"}','movie','rate') as movie,rate;
c0 c1
流浪地球 5
-- 注意:直接使用函数,SELECT 子句中无法添加其他字段
hive> select 'sannaha' AS u_name,json_tuple('{"movie":"流浪地球","rate":"5"}','movie','rate');
FAILED: SemanticException [Error 10081]: UDTF’s are not supported outside the SELECT clause, nor nested in expressions

-- 示例二,与 lateral view 一起使用(建表过程略)
hive> SELECT t1.u_name, t2.movie, t2.rate
FROM douban t1 lateral view json_tuple
(t1.json_str, 'movie', 'rate') t2 AS movie, rate;
t1.u_name t2.movie t2.rate
sannaha 流浪地球 5

解析数组

对于 json 数组,可以先用 explode 函数将 json 数组里的元素按行的形式输出,再用 get_json_objectjson_tuple 函数解析里面的字段。

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
-- 步骤一:将一个 json 数组转为多行 json 字符串
-- 先用正则替换处理 json 数组
-- 然后用 split 将其转为字符串数组
-- 最后用 explode 函数将字符串数组转为多行 json 字符串
hive> SELECT explode(
split(
regexp_replace(
regexp_replace(
'[{"website":"sannaha.moe","name":"博客"},{"website":"thinklong.me","name":"主页"}]','\\[|\\]',''
), --将 json 数组两边的中括号去掉
'\\}\\,\\{','\\}\\;\\{'
), --将 json 数组元素之间的逗号替换为分号
'\\;') --以分号为分隔符切分为数组
); --列转行
col
{"website":"sannaha.moe","name":"博客"}
{"website":"thinklong.me","name":"主页"}

-- 步骤二:在上面的基础上用 json_tuple 解析 json 字符串
SELECT json_tuple(json_str,'website','name') FROM (
SELECT explode(
split(
regexp_replace(
regexp_replace(
'[{"website":"sannaha.moe","name":"博客"},{"website":"thinklong.me","name":"主页"}]','\\[|\\]',''
), --将 Json 数组两边的中括号去掉
'\\}\\,\\{','\\}\\;\\{'
), --将 Json 数组元素之间的逗号换成分号
'\\;') --以分号作为分隔符
) as json_str
) t;
c0 c1
sannaha.moe 博客
thinklong.me 主页

条件函数

  1. 空字段赋值
1
2
3
4
5
6
7
8
-- 如果value值为NULL就返回default_value,否则返回value
nvl(T value, T default_value)

-- 示例
select nvl(null,'sannaha');
sannaha
select nvl('hello','sannaha');
hello
  1. 返回第一个非空数据
1
2
3
4
5
6
-- 返回第一个非null的值,如果全部都为NULL就返回NULL
COALESCE(T v1, T v2, ...)

-- 示例
select coalesce(null,null,'sannaha','blog');
sannaha
  1. 是否为 null
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 如果a为null就返回true,否则返回false
isnull(a)
-- 如果a为非null就返回true,否则返回false
isnotnull(a)

-- 示例
select isnull(null);
true
select isnull('');
false
select isnotnull(null);
false
select isnotnull('');
true
  1. if 判断
1
2
3
4
5
-- 如果testCondition为true就返回valueTrue,否则返回valueFalseOrNull
if(boolean testCondition, T valueTrue, T valueFalseOrNull)

select if('sannaha'='female','做VTB','看VTB');
看VTB
  1. case when then 判断
1
2
3
4
5
6
7
8
9
10
-- 如果a=b就返回c,a=d就返回e,否则返回f  如CASE 4 WHEN 5  THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
-- 如果a=ture就返回b,c= ture就返回d,否则返回e 如:CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5;CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0
CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

-- 示例
select case 7 when 4 then 4 when 7 then 7 when 5 then 5 else 1 end;
7
select case when 'sannaha'='female' then '做VTB' when 1+1=0 then 'WTF' else '看VTB' end;
看VTB

实例

在需要进行条件选择的场景下,可以使用 CASE WHENIF 进行选择。

统计员工表中不同 level 下男性和女性各自的人数。表数据如下:

emp.csv
1
2
3
4
5
6
7
8
风清扬	P14	male
逍遥子 P14 male
三丰 P10 male
无忌 P10 male
赵敏 P10 female
苗人凤 M10 male
语嫣 M10 female
苏荃 M10 female

创建员工表并导入数据:

1
2
3
4
5
create table emp(name string, level string, sex string)
row format delimited
fields terminated by '\t';

load data local inpath '/data/emp.csv' into table emp;

两种方式实现查询:

1
2
3
4
5
6
7
8
9
10
11
12
13
-- CASE WHEN 
select level,
sum(case sex when 'male' then 1 else 0 end) male_count,
sum(case sex when 'female' then 1 else 0 end) female_count
from emp
group by level;

-- IF
select level,
sum(if(sex='male',1,0)) male_count,
sum(if(sex='female',1,0)) female_count
from emp
group by level;

窗口函数

相关函数:

  • OVER():指定分析函数工作的数据窗口大小,该窗口大小会随着行的变化而变化。
  • CURRENT ROW:当前行
  • n PRECEDING:往前 n 行数据
  • n FOLLOWING:往后 n 行数据
  • UNBOUNDED PRECEDING:从起始行开始
  • UNBOUNDED FOLLOWING:到结尾行结束
  • LAG(col,n):往前第 n 行数据
  • LEAD(col,n):往后第 n 行数据
  • NTILE(n):用于将有序分区中的数据切分成 n 片,返回当前记录所在的切片值,切片值从 1 开始。

窗口函数实例

需求:

  1. 查询在 2020 年 6 月份进行过消费的用户以及消费用户的数量。
  2. 查询用户的购买明细,并统计所有用户月消费总额。
  3. 在 2 的基础上,将消费额按照日期进行累加。
  4. 查询用户上次的购买时间
  5. 查询前 20% 时间的订单信息

准备数据:

steammarket.csv
1
2
3
4
5
6
7
8
9
10
11
12
13
14
ZywOo	P90 | 精英之作	2017-07-18	1.21
SANNAHA M4A1 消音型 | 多变迷彩 2017-07-18 4.60
ZywOo Tec-9 | 竹林 2017-07-19 1.57
SANNAHA M4A4 | 杀意大名 2017-07-24 16.01
SANNAHA AK-47 | 精英之作 2017-09-14 8.56
ZywOo M4A1 消音型 | 毁灭者 2000 2017-09-14 85.23
SANNAHA 格洛克 18 型 | 荒野反叛 2017-09-17 251.00
SANNAHA AUG | 席德.米德 2020-06-18 23.47
ZywOo AWP | 蠕虫之神 2020-06-18 10.69
SANNAHA AWP | 猫猫狗狗 2020-06-20 19.02
SANNAHA 沙漠之鹰 | 科林斯遗产 2020-06-20 10.26
s1mple AK-47 | 深海复仇 2020-06-22 143.08
s1mple M4A4 | X 光 2020-06-23 80.01
s1mple AWP | 暴怒野兽 2020-06-23 301.27

创建表并加载数据:

1
2
3
4
5
6
7
8
9
create table steammarket(
id string,
item string,
listeddate string,
price double)
row format delimited
fields terminated by '\t';

load data local inpath '/data/steammarket.csv' into table steammarket;

实现需求:

  1. 查询在2020年6月份进行过消费的用户以及消费用户的数量。
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
-- over()函数会对*每一条数据*开一个窗口
-- 窗口大小由over()函数中的参数决定,如果不传参,表示数据集中的所有数据
> select id,count(*) over() from steammarket
where substring(listeddate,1,7)='2020-06'
group by id;
id count_window_0
s1mple 3
ZywOo 3
SANNAHA 3

-- 注意与不加over()的区别
> select id,count(*) from steammarket
where substring(listeddate,1,7)='2020-06'
group by id;
id _c1
SANNAHA 3
ZywOo 1
s1mple 3

-- 注意与不加group by的区别
> select id,count(*) over() from steammarket
where substring(listeddate,1,7)='2020-06';
id count_window_0
s1mple 7
s1mple 7
s1mple 7
SANNAHA 7
SANNAHA 7
ZywOo 7
SANNAHA 7
  1. 查询用户的购买明细,并统计所有用户月消费总额。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
> select id,
item,
listeddate,
price,
sum(price) over(partition by month(listeddate))
from steammarket;
id item listeddate price sum_window_0
s1mple AWP | 暴怒野兽 2020-06-23 301.27 587.8000000000001
s1mple M4A4 | X 光 2020-06-23 80.01 587.8000000000001
s1mple AK-47 | 深海复仇 2020-06-22 143.08 587.8000000000001
SANNAHA 沙漠之鹰 | 科林斯遗产 2020-06-20 10.26 587.8000000000001
SANNAHA AWP | 猫猫狗狗 2020-06-20 19.02 587.8000000000001
ZywOo AWP | 蠕虫之神 2020-06-18 10.69 587.8000000000001
SANNAHA AUG | 席德.米德 2020-06-18 23.47 587.8000000000001
ZywOo P90 | 精英之作 2017-07-18 1.21 23.39
SANNAHA M4A4 | 杀意大名 2017-07-24 16.01 23.39
ZywOo Tec-9 | 竹林 2017-07-19 1.57 23.39
SANNAHA M4A1 消音型 | 多变迷彩 2017-07-18 4.6 23.39
ZywOo M4A1 消音型 | 毁灭者 2000 2017-09-14 85.23 344.79
SANNAHA AK-47 | 精英之作 2017-09-14 8.56 344.79
SANNAHA 格洛克 18| 荒野反叛 2017-09-17 251.0 344.79
  1. 在 2 的基础上,将消费额按照日期进行累加。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select id,item,listeddate,price,
-- 以所有数据作为窗口
sum(price) over() as sum1,
-- 以分组内的所有数据作为窗口(或用partition by)
sum(price) over(distribute by id) as sum2,
-- 以分组内的数据作为窗口,窗口大小依次增加(或用partition by + order by)
sum(price) over(distribute by id sort by listeddate) as sum3,
-- 以分组内的数据作为窗口,窗口范围为从起始行到当前行
sum(price) over(distribute by id sort by listeddate rows between UNBOUNDED PRECEDING and current row) as sum4,
-- 以分组内的数据作为窗口,窗口范围为当前行与前一行
sum(price) over(distribute by id sort by listeddate rows between 1 PRECEDING and current row) as sum5,
-- 以分组内的数据作为窗口,窗口范围为前一行到后一行
sum(price) over(distribute by id sort by listeddate rows between 1 PRECEDING and 1 FOLLOWING) as sum6,
-- 以分组内的数据作为窗口,窗口范围为当前行到结尾行
sum(price) over(distribute by id sort by listeddate rows between current row and UNBOUNDED FOLLOWING) as sum7
from steammarket;

窗口函数查询结果可视化

  1. 查询用户的上次消费时间。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select id,item,listeddate,price,
-- 往前一行数据的消费时间,如果不存在则赋默认值
lag(listeddate,1,'1900-01-01') over(partition by id order by listeddate) as lastdate1,
-- 往前第二行数据的消费时间
lag(listeddate,2) over(partition by id order by listeddate) as lastdate2
from steammarket;
id item listeddate price lastdate1 lastdate2
SANNAHA M4A1 消音型 | 多变迷彩 2017-07-18 4.6 1900-01-01 NULL
SANNAHA M4A4 | 杀意大名 2017-07-24 16.01 2017-07-18 NULL
SANNAHA AK-47 | 精英之作 2017-09-14 8.56 2017-07-24 2017-07-18
SANNAHA 格洛克 18| 荒野反叛 2017-09-17 251.0 2017-09-14 2017-07-24
SANNAHA AUG | 席德.米德 2020-06-18 23.47 2017-09-17 2017-09-14
SANNAHA 沙漠之鹰 | 科林斯遗产 2020-06-20 10.26 2020-06-18 2017-09-17
SANNAHA AWP | 猫猫狗狗 2020-06-20 19.02 2020-06-20 2020-06-18
ZywOo P90 | 精英之作 2017-07-18 1.21 1900-01-01 NULL
ZywOo Tec-9 | 竹林 2017-07-19 1.57 2017-07-18 NULL
ZywOo M4A1 消音型 | 毁灭者 2000 2017-09-14 85.23 2017-07-19 2017-07-18
ZywOo AWP | 蠕虫之神 2020-06-18 10.69 2017-09-14 2017-07-19
s1mple AK-47 | 深海复仇 2020-06-22 143.08 1900-01-01 NULL
s1mple M4A4 | X 光 2020-06-23 80.01 2020-06-22 NULL
s1mple AWP | 暴怒野兽 2020-06-23 301.27 2020-06-23 2020-06-22
  1. 查询时间在前 20% 的订单信息。
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
-- 数据根据时间排序并分5片,取第1片即前20%的订单信息
> select id,item,listeddate,price from (
select id,item,listeddate,price,
ntile(5) over(order by listeddate) ntile_5
from steammarket) t
where ntile_5=1;
id item listeddate price
ZywOo P90 | 精英之作 2017-07-18 1.21
SANNAHA M4A1 消音型 | 多变迷彩 2017-07-18 4.6
ZywOo Tec-9 | 竹林 2017-07-19 1.57

-- 中间嵌套的SQL查询结果
> select id,item,listeddate,price,
ntile(5) over(order by listeddate) ntile_5
from steammarket;
id item listeddate price ntile_5
ZywOo P90 | 精英之作 2017-07-18 1.21 1
SANNAHA M4A1 消音型 | 多变迷彩 2017-07-18 4.6 1
ZywOo Tec-9 | 竹林 2017-07-19 1.57 1
SANNAHA M4A4 | 杀意大名 2017-07-24 16.01 2
SANNAHA AK-47 | 精英之作 2017-09-14 8.56 2
ZywOo M4A1 消音型 | 毁灭者 2000 2017-09-14 85.23 2
SANNAHA 格洛克 18| 荒野反叛 2017-09-17 251.0 3
SANNAHA AUG | 席德.米德 2020-06-18 23.47 3
ZywOo AWP | 蠕虫之神 2020-06-18 10.69 3
SANNAHA AWP | 猫猫狗狗 2020-06-20 19.02 4
SANNAHA 沙漠之鹰 | 科林斯遗产 2020-06-20 10.26 4
s1mple AK-47 | 深海复仇 2020-06-22 143.08 4
s1mple M4A4 | X 光 2020-06-23 80.01 5
s1mple AWP | 暴怒野兽 2020-06-23 301.27 5

排名函数

排名函数也属于窗口函数。

相关函数:

  • RANK():返回分组中的排名,数据相同时排名会重复,会留下空位。
  • DENSE_RANK():返回分组中的排名,数据相同时排名会重复,不会留下空位。
  • ROW_NUMBER():返回分组中的排名,数据相同时会按照数据集中的顺序给予不同的排名。

排名函数实例

需求:现有游戏评分数据,计算不同游戏在各家媒体中的排名。

准备数据:

gamescore.csv
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
THE LAST OF US	IGN	100
THE LAST OF US Gamespot 80
THE LAST OF US Metacritic 95
THE LAST OF US UserScore 91
THE LAST OF US PART II IGN 100
THE LAST OF US PART II IGN Japan 70
THE LAST OF US PART II Gamespot 80
THE LAST OF US PART II Metacritic 95
THE LAST OF US PART II UserScore 91
PERSONA 5 ROYAL IGN 100
PERSONA 5 ROYAL IGN Japan 89
PERSONA 5 ROYAL Gamespot 100
PERSONA 5 ROYAL Metacritic 95
PERSONA 5 ROYAL UserScore 81
FINAL FANTASY VII REMAKE IGN 80
FINAL FANTASY VII REMAKE IGN Japan 80
FINAL FANTASY VII REMAKE Gamespot 100
FINAL FANTASY VII REMAKE Metacritic 87
FINAL FANTASY VII REMAKE UserScore 80
ORI AND THE WILL OF THE WISPS IGN 90
ORI AND THE WILL OF THE WISPS Metacritic 88
ORI AND THE WILL OF THE WISPS UserScore 85

创建表并加载数据:

1
2
3
4
5
6
7
create table gamescore(name string,
media string,
score double)
row format delimited
fields terminated by '\t';

load data local inpath '/data/gamescore.csv' into table gamescore;

按需求查询数据:

1
2
3
4
5
select name,media,score,
rank() over(partition by media order by score desc) rp,
dense_rank() over(partition by media order by score desc) drp,
row_number() over(partition by media order by score desc) rmp
from gamescore;

排名函数查询结果可视化

行转列

行转列,就是将某对象在同一列下的多行内容作为多个字段追加到一行中。如下图所示:

行转列

Hive 中的行列转换与 RDBMS 中的稍有区别,行转列是将多行内容拼接起来放在一列。

相关函数:

  • CONCAT(str1, str2…):将多个字符串拼接起来,返回一个字符串。
  • CONCAT_WS(separator, str1, str2…):使用连接符 separator 将多个字符串拼接起来,返回一个字符串。该函数会跳过分隔符参数之后的任何 NULL 和空字符串。如果分隔符为 NULL,返回值也为 NULL。
  • COLLECT_SET(col):将 col 字段的值去重汇总,返回 Array 类型数据。该函数为聚合函数,只接受基本数据类型。

函数用法示例:

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
-- CONCAT
> select concat(name,'-',level) name_level from emp;
name_level
风清扬-P14
逍遥子-P14
三丰-P10
无忌-P10
赵敏-P10
苗人凤-M10
语嫣-M10
苏荃-M10

-- CONCAT_WS
> select concat_ws(',',name,level) from emp;
name_level
风清扬,P14
逍遥子,P14
三丰,P10
无忌,P10
赵敏,P10
苗人凤,M10
语嫣,M10
苏荃,M10

-- COLLECT_SET
> select collect_set(level) levels from emp;
levels
["P14","P10","M10"]

行转列示例

需求:将 emp 员工表中 levelsex 一样的员工归类到一起。

实现:

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
-- 实现。其中COLLECT_SET为聚合函数,多进一出,需要使用GROUP BY进行分组
> select level_sex,
concat_ws(',',collect_set(name)) names
from (
select
concat(level,',',sex) level_sex,
name
from emp
) t1
group by level_sex;
level_sex names
M10,female 语嫣,苏荃
M10,male 苗人凤
P10,female 赵敏
P10,male 三丰,无忌
P14,male 风清扬,逍遥子

-- 中间嵌套的SQL查询结果如下
> select
concat(level,',',sex) level_sex,
name
from emp;
level_sex name
P14,male 风清扬
P14,male 逍遥子
P10,male 三丰
P10,male 无忌
P10,female 赵敏
M10,male 苗人凤
M10,female 语嫣
M10,female 苏荃

列转行

列转行与行转列相反,将一列内容拆分为多行。

相关函数:

  • EXPLODE(col):将 Hive 中一列复杂的 Array 或 Map 拆成多行。
  • LATERAL VIEW:和 SPLIT、EXPLODE 等 UDTF 一起使用,将一列数据拆成多行,并对拆分后的数据进行聚合。
  • 用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias

函数用法示例:

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
-- 准备数据
$ vi gameinfo.csv
Sekiro: Shadows Die Twice 动作,潜行,类魂系列,冒险
Beat Saber 虚拟现实,节奏,音乐,独立
Half-Life: Alyx 虚拟现实,第一人称射击,恐怖

-- 创建游戏信息表
create table gameinfo(game string,
category array<string>)
row format delimited
fields terminated by '\t'
collection items terminated by ',';

-- 加载数据
load data local inpath '/data/gameinfo.csv' into table gameinfo;

-- EXPLODE
> select explode(category) categories from gameinfo;
categories
动作
潜行
类魂系列
冒险
虚拟现实
节奏
音乐
独立
虚拟现实
第一人称射击
恐怖

列转行示例

需求:将游戏对应的分类拆分为多行。

实现:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
> select game,categories from 
gameinfo lateral view explode(category) gameinfo_temp as categories;
game categories
Sekiro: Shadows Die Twice 动作
Sekiro: Shadows Die Twice 潜行
Sekiro: Shadows Die Twice 类魂系列
Sekiro: Shadows Die Twice 冒险
Beat Saber 虚拟现实
Beat Saber 节奏
Beat Saber 音乐
Beat Saber 独立
Half-Life: Alyx 虚拟现实
Half-Life: Alyx 第一人称射击
Half-Life: Alyx 恐怖

案例

复制分区表

  1. 创建新表,与原表表结构相同:
1
create table test.tablename like sannaha.tablename;
  1. 通过 HDFS 复制原表数据:
1
2
# 这里选择复制原表的一个分区作为演示
$ hdfs dfs -cp /user/hive/warehouse/sannaha.db/tablename/dt=20210628 /user/hive/warehouse/test.db/tablename
  1. 修复表结构:
1
msck repair table test.tablename;

蚂蚁森林

表数据

1
2
3
4
5
6
7
8
9
以下表记录了用户每天的蚂蚁森林低碳生活的记录流水。
table_name:user_low_carbon
user_id data_dt  low_carbon
用户     日期      减少碳排放(g)

蚂蚁森林植物换购表,用于记录申领环保植物所需要减少的碳排放量
table_name:  plant_carbon
plant_id plant_name low_carbon
植物编号 植物名 换购植物所需要的碳

低碳生活流水:

user_low_carbon.txt
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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
u_001	2017/1/1	10
u_001 2017/1/2 150
u_001 2017/1/2 110
u_001 2017/1/2 10
u_001 2017/1/4 50
u_001 2017/1/4 10
u_001 2017/1/6 45
u_001 2017/1/6 90
u_002 2017/1/1 10
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
u_002 2017/1/6 68
u_003 2017/1/1 20
u_003 2017/1/2 10
u_003 2017/1/2 150
u_003 2017/1/3 160
u_003 2017/1/4 20
u_003 2017/1/5 120
u_003 2017/1/6 20
u_003 2017/1/7 10
u_003 2017/1/7 110
u_004 2017/1/1 110
u_004 2017/1/2 20
u_004 2017/1/2 50
u_004 2017/1/3 120
u_004 2017/1/4 30
u_004 2017/1/5 60
u_004 2017/1/6 120
u_004 2017/1/7 10
u_004 2017/1/7 120
u_005 2017/1/1 80
u_005 2017/1/2 50
u_005 2017/1/2 80
u_005 2017/1/3 180
u_005 2017/1/4 180
u_005 2017/1/4 10
u_005 2017/1/5 80
u_005 2017/1/6 280
u_005 2017/1/7 80
u_005 2017/1/7 80
u_006 2017/1/1 40
u_006 2017/1/2 40
u_006 2017/1/2 140
u_006 2017/1/3 210
u_006 2017/1/3 10
u_006 2017/1/4 40
u_006 2017/1/5 40
u_006 2017/1/6 20
u_006 2017/1/7 50
u_006 2017/1/7 240
u_007 2017/1/1 130
u_007 2017/1/2 30
u_007 2017/1/2 330
u_007 2017/1/3 30
u_007 2017/1/4 530
u_007 2017/1/5 30
u_007 2017/1/6 230
u_007 2017/1/7 130
u_007 2017/1/7 30
u_008 2017/1/1 160
u_008 2017/1/2 60
u_008 2017/1/2 60
u_008 2017/1/3 60
u_008 2017/1/4 260
u_008 2017/1/5 360
u_008 2017/1/6 160
u_008 2017/1/7 60
u_008 2017/1/7 60
u_009 2017/1/1 70
u_009 2017/1/2 70
u_009 2017/1/2 70
u_009 2017/1/3 170
u_009 2017/1/4 270
u_009 2017/1/5 70
u_009 2017/1/6 70
u_009 2017/1/7 70
u_009 2017/1/7 70
u_010 2017/1/1 90
u_010 2017/1/2 90
u_010 2017/1/2 90
u_010 2017/1/3 90
u_010 2017/1/4 90
u_010 2017/1/4 80
u_010 2017/1/5 90
u_010 2017/1/5 90
u_010 2017/1/6 190
u_010 2017/1/7 90
u_010 2017/1/7 90
u_011 2017/1/1 110
u_011 2017/1/2 100
u_011 2017/1/2 100
u_011 2017/1/3 120
u_011 2017/1/4 100
u_011 2017/1/5 100
u_011 2017/1/6 100
u_011 2017/1/7 130
u_011 2017/1/7 100
u_012 2017/1/1 10
u_012 2017/1/2 120
u_012 2017/1/2 10
u_012 2017/1/3 10
u_012 2017/1/4 50
u_012 2017/1/5 10
u_012 2017/1/6 20
u_012 2017/1/7 10
u_012 2017/1/7 10
u_013 2017/1/1 50
u_013 2017/1/2 150
u_013 2017/1/2 50
u_013 2017/1/3 150
u_013 2017/1/4 550
u_013 2017/1/5 350
u_013 2017/1/6 50
u_013 2017/1/7 20
u_013 2017/1/7 60
u_014 2017/1/1 220
u_014 2017/1/2 120
u_014 2017/1/2 20
u_014 2017/1/3 20
u_014 2017/1/4 20
u_014 2017/1/5 250
u_014 2017/1/6 120
u_014 2017/1/7 270
u_014 2017/1/7 20
u_015 2017/1/1 10
u_015 2017/1/2 20
u_015 2017/1/2 10
u_015 2017/1/3 10
u_015 2017/1/4 20
u_015 2017/1/5 70
u_015 2017/1/6 10
u_015 2017/1/7 80
u_015 2017/1/7 60

蚂蚁森林植物换购表:

plant_carbon.txt
1
2
3
4
p001	梭梭树	17
p002 沙柳 19
p003 樟子树 146
p004 胡杨 215

蚂蚁森林植物申领统计

2017 年 1 月 1 日开始记录低碳生活流水(user_low_carbon),2017 年 10 月 1 日之前满足申领条件的用户都申领了一棵 p004-胡杨,剩余的能量全部用来领取 p002-沙柳 。统计在 10 月 1 日累计申领 p002-沙柳 数量前 10 的用户,以及比后一名多领了几棵沙柳。得到的统计结果格式如下:

user_id plant_count less_count
u_007 66 3
u_013 63 10
u_008 53 7

实现:

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
> select user_id,
plant_count,
plant_count - lead(plant_count, 1, 0) over(
order by plant_count desc
) less_count
from --统计各个用户领取沙柳数量
(
select t1.user_id,
if(
t1.carbon_sum >= t2.p004_carbon,
floor((t1.carbon_sum - t2.p004_carbon) / t3.p002_carbon),
floor(t1.carbon_sum / t3.p002_carbon)
) plant_count
from -- 统计各个用户在指定时间段内低碳数据之和
(
select user_id,
sum(low_carbon) carbon_sum
from user_low_carbon
where date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM') < '2017-10'
group by user_id
) t1,
-- 查询胡杨所需低碳数据
(
select low_carbon p004_carbon
from plant_carbon
where plant_id = 'p004'
) t2,
-- 查询沙柳所需低碳数据
(
select low_carbon p002_carbon
from plant_carbon
where plant_id = 'p002'
) t3
) t4
limit 10;

user_id plant_count less_count
u_007 66 3
u_013 63 10
u_008 53 7
u_005 46 1
u_010 45 1
u_014 44 5
u_011 39 2
u_009 37 5
u_006 32 9
u_002 23 1

蚂蚁森林低碳用户排名分析

查询 user_low_carbon 表中符合要求的每日流水记录,要求如下:

  1. 用户在 2017 年,连续三天(或以上),每天减少碳排放(low_carbon)之和都大于 100。
  2. 查询返回满足该条件的当天的所有流水记录。

举例说明,用户 u_002 的流水记录如下:

1
2
3
4
5
6
7
8
u_002	2017/1/1	10
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
u_002 2017/1/6 68

2017/1/2 ~ 2017/1/5 连续四天,每天减少的碳排放量之和都大于 100,因此查询结果应返回这四天的流水记录。

这个需求的重点是如何判断“连续三天(或以上)”,在这里提供了多种解法。

解法一

列举出每一种符合“前后三天”条件的情况:

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
-- 连接user_low_carbon表,获取每一条流水记录
select t.user_id,
t.data_dt,
t.low_carbon
from -- 得到符合“连续三天”条件的用户id和日期
(
select user_id,
data_dt
from -- 计算前后两次符合减少碳排放量条件的日期与当天日期的差值
(
select user_id,
data_dt,
datediff(data_dt, lag2) lag2_diff,
datediff(data_dt, lag1) lag1_diff,
datediff(data_dt, lead2) lead2_diff,
datediff(data_dt, lead1) lead1_diff
from --记录下前后两次符合减少碳排放量条件的日期
(
select user_id,
data_dt,
lag(data_dt, 2, '1970-01-01') over(
partition by user_id
order by data_dt
) lag2,
lag(data_dt, 1, '1970-01-01') over(
partition by user_id
order by data_dt
) lag1,
lead(data_dt, 2, '9999-12-31') over(
partition by user_id
order by data_dt
) lead2,
lead(data_dt, 1, '9999-12-31') over(
partition by user_id
order by data_dt
) lead1
from -- 查询2017年单日减少碳排放量之和大于100的用户id及日期
(
select user_id,
date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') data_dt
from user_low_carbon
where substring(data_dt, 1, 4) = '2017'
group by user_id,
data_dt
having sum(low_carbon) > 100
) t1
) t2
) t3
where -- 符合“连续三天”条件的三种情况
(
--上上次符合条件的日期距离今天为2,且上次符合条件的日期距离今天为1
lag2_diff = 2
and lag1_diff = 1
)
or (
--上次符合条件的日期距离今天为2,且下次符合条件的日期距离今天为-1
lag1_diff = 1
and lead1_diff = -1
)
or (
--下次符合条件的日期距离今天为-1,且下下次符合条件的日期距离今天为-2
lead1_diff = -1
and lead2_diff = -2
)
) t4
left join user_low_carbon t on t4.user_id = t.user_id
and date_format(t4.data_dt,'yyyy/M/d') = t.data_dt;

t.user_id t.data_dt t.low_carbon
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
u_005 2017/1/2 50
u_005 2017/1/2 80
u_005 2017/1/3 180
u_005 2017/1/4 180
u_005 2017/1/4 10
u_008 2017/1/4 260
u_008 2017/1/5 360
u_008 2017/1/6 160
u_008 2017/1/7 60
u_008 2017/1/7 60
u_009 2017/1/2 70
u_009 2017/1/2 70
u_009 2017/1/3 170
u_009 2017/1/4 270
u_010 2017/1/4 90
u_010 2017/1/4 80
u_010 2017/1/5 90
u_010 2017/1/5 90
u_010 2017/1/6 190
u_010 2017/1/7 90
u_010 2017/1/7 90
u_011 2017/1/1 110
u_011 2017/1/2 100
u_011 2017/1/2 100
u_011 2017/1/3 120
u_013 2017/1/2 150
u_013 2017/1/2 50
u_013 2017/1/3 150
u_013 2017/1/4 550
u_013 2017/1/5 350
u_014 2017/1/5 250
u_014 2017/1/6 120
u_014 2017/1/7 270
u_014 2017/1/7 20

解法二

若日期与等差数列的差值相等,则为“连续的天”。这种方式可以随意指定“前后n天”,而不必列举出每一种情况:

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
select t.user_id,
t.data_dt,
t.low_carbon
from -- 统计连续的天数
(
select user_id,
data_dt,
count(*) over(PARTITION by user_id, data_sub_rk) as date_count
from -- 将日期减去当前的排名,差值相等则说明这几天连续
(
select user_id,
data_dt,
date_sub(data_dt, rk) data_sub_rk
from -- 按照日期进行排序,并给每条数据一个不同的排名
(
select user_id,
data_dt,
rank() over(
partition by user_id
order by data_dt
) rk
from -- 查询2017年单日减少碳排放量之和大于100的用户id及日期
(
select user_id,
date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') data_dt
from user_low_carbon
where substring(data_dt, 1, 4) = '2017'
group by user_id,
data_dt
having sum(low_carbon) >= 100
) t1
) t2
) t3
) t4
left join user_low_carbon t on t4.user_id = t.user_id
and date_format(t4.data_dt,'yyyy/M/d') = t.data_dt
where date_count >= 3;

t.user_id t.data_dt t.low_carbon
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
u_005 2017/1/3 180
u_005 2017/1/2 50
u_005 2017/1/2 80
u_005 2017/1/4 180
u_005 2017/1/4 10
u_008 2017/1/4 260
u_008 2017/1/6 160
u_008 2017/1/5 360
u_008 2017/1/7 60
u_008 2017/1/7 60
u_009 2017/1/4 270
u_009 2017/1/2 70
u_009 2017/1/2 70
u_009 2017/1/3 170
u_010 2017/1/4 90
u_010 2017/1/4 80
u_010 2017/1/5 90
u_010 2017/1/5 90
u_010 2017/1/6 190
u_010 2017/1/7 90
u_010 2017/1/7 90
u_011 2017/1/2 100
u_011 2017/1/2 100
u_011 2017/1/1 110
u_011 2017/1/3 120
u_013 2017/1/2 150
u_013 2017/1/2 50
u_013 2017/1/3 150
u_013 2017/1/4 550
u_013 2017/1/5 350
u_014 2017/1/5 250
u_014 2017/1/6 120
u_014 2017/1/7 270
u_014 2017/1/7 20

快视频

需求

统计快视频的常规指标,各种TopN指标:

  • 统计视频观看次数 Top10
  • 统计视频数量 Top10 的类别
  • 统计观看次数 Top20 的视频所属类别及对应视频数量
  • 统计观看次数 Top50 的视频所关联视频的类别排名
  • 统计每个视频类别中的观看次数 Top10
  • 统计每个视频类别中的热度 Top10
  • 统计视频上传数量 Top10 的用户及上传的视频
  • 统计每个视频类别中视频观看数 Top10

原始数据

1.数据结构

字段 说明 详细描述
videoid 视频唯一id 11位字符串
uploader 视频上传者 上传者用户名
age 视频年龄 视频在平台上的整数天
category 视频类别 上传视频指定的视频分类
length 视频长度 整型数字
views 观看次数 视频被浏览的次数
rate 视频评分 满分5分
rating 视频热度 整型数字
conment 评论数 整型数字
relatedId 相关视频id 相关视频的id,最多20个
  1. 数据示例
1
2
3
7D0Mf4Kn4Xk	periurban	583	Music	201	6508	4.19	687	312	e2k0h6tPvGc	yuO6yjlvXe8	VqpnWBo-R4E	bdDskrr8jRY	y3IDp2n7B48	JngPWhfCb2M	KQaUvH5oiO4	NSzrwv5MCwc	NHB0a0xtLgU	DlRodd4s86s	EzKwOYLh-S0	eUIfRyrqwp8	AK8Wtfwe-1k	Eq4hGkIqBGw	N1lkLaLJHlc	-uIffs-DHkM	zpTorUhCd8Y	AvSK0qPw7EU	WX5KLMqY4bM	VKFqqoeMdjw
MEvoy_owET8 smpfilms 736 Travel & Places 921 109673 4.25 1181 774 YtX2nwowMtU A5dp02FXDmM bGoUu4gAHaI faDB-ToajhM srcg9xLjtuE 9aE4eMVeUEw G5fZky7Nm1k UEXvMJo3ZAY sCTbH-VP7mA WiriPTfpIP8 W-s_e61hkys cQWtiU6d99w 93LHxjgQ4LE JEiATJFBWO0 JzjnhpqWIPs G_qfXiOkYPU Gd6M-B3FOaQ Y5pMgbhyb18 7jdAdCmMRkg yxbLFd6Y38E
SDNkMu8ZT68 w00dy911 630 People & Blogs 186 10181 3.49 494 257 rjnbgpPJUks

ETL

通过观察原始数据形式,可以发现,视频可以有多个所属分类,每个所属分类用&符号分割,且分割的两边有空格字符,同时相关视频也是可以有多个元素,多个相关视频又用 \t 进行分割。为了分析数据时方便对存在多个子元素的数据进行操作,我们首先进行数据重组清洗操作。即:将所有的类别用“&”分割,同时去掉两边空格,多个相关视频id也使用“&”进行分割。

pom

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
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>moe.sannaha</groupId>
<artifactId>QuickVideo</artifactId>
<version>1.0-SNAPSHOT</version>

<dependencies>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>3.0.0</version>
</dependency>

<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>3.0.0</version>
</dependency>

<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-hdfs</artifactId>
<version>3.0.0</version>
</dependency>
</dependencies>

</project>

Utils

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
package moe.sannaha.utils;

public class ETLUtil {
/**
* 1. 过滤掉字段数量小于9个的字符串
* 2. 去掉类别字段中的空格
* 3. 修改相关视频ID字段的分隔符,由`\t`替换为`&`
* @param originalStr 原始字符串
* @return 过滤后的字符串
*/
public static String etlStr(String originalStr) {
StringBuilder sb = new StringBuilder();

// 1. 切割
String[] fields = originalStr.split("\t");

// 2. 根据字段数量进行过滤
if (fields.length < 9) {
return null;
}

// 3. 去掉类别字段中的空格
fields[3] = fields[3].replaceAll(" ", "");

// 4. 修改相关视频ID字段的分隔符
for (int i = 0; i < fields.length; i++) {
// 对非相关视频ID字段进行处理
if (i < 9) {
if (i == fields.length - 1) {
sb.append(fields[i]);
} else {
sb.append(fields[i]).append("\t");
}
} else {
if (i == fields.length - 1) {
sb.append(fields[i]);
} else {
sb.append(fields[i]).append("&");
}
}
}

// 5. 返回结果
return sb.toString();
}
}

Mapper

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
package moe.sannaha.etl;

import moe.sannaha.utils.ETLUtil;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Mapper;

import java.io.IOException;

public class ETLMapper extends Mapper<LongWritable, Text, NullWritable, Text> {
Text value = new Text();

@Override
protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
// 1. 获取数据
String originalStr = value.toString();

// 2. 过滤数据
String etlStr = ETLUtil.etlStr(originalStr);

// 3. 写出
if (etlStr == null) {
return;
} else {
value.set(etlStr);
}
context.write(NullWritable.get(),value);
}
}

Main

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
package moe.sannaha.etl;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.conf.Configured;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.lib.input.TextInputFormat;
import org.apache.hadoop.mapreduce.lib.output.TextOutputFormat;
import org.apache.hadoop.util.Tool;
import org.apache.hadoop.util.ToolRunner;

public class ETLMain extends Configured implements Tool {
public int run(String[] strings) throws Exception {
Job job = Job.getInstance(super.getConf(), "QuickVideoETL");

job.setJarByClass(ETLMain.class);

job.setInputFormatClass(TextInputFormat.class);
TextInputFormat.addInputPath(job, new Path(strings[0]));

job.setMapperClass(ETLMapper.class);
job.setMapOutputKeyClass(NullWritable.class);
job.setNumReduceTasks(0);

job.setOutputFormatClass(TextOutputFormat.class);
TextOutputFormat.setOutputPath(job,new Path(strings[1]));

boolean b = job.waitForCompletion(true);
return b ? 0 : 1;
}

public static void main(String[] args) throws Exception {
Configuration configuration = new Configuration();
int run = ToolRunner.run(configuration, new ETLMain(), args);
System.exit(run);
}
}

执行

视频数据存储在 HDFS 中的 /hdfsdata/quickvideo/video/ 下,执行 ETL:

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
$ yarn jar QuickVideo-1.0-SNAPSHOT.jar moe.sannaha.etl.ETLMain /hdfsdata/quickvideo/video /hdfsdata/quickvideo/videoetl
...
20/07/01 23:27:27 INFO mapreduce.Job: Job job_1593614454710_0003 completed successfully
20/07/01 23:27:27 INFO mapreduce.Job: Counters: 33
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=1087130
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=213621137
HDFS: Number of bytes written=212238254
HDFS: Number of read operations=35
HDFS: Number of large read operations=0
HDFS: Number of write operations=10
HDFS: Number of bytes read erasure-coded=0
Job Counters
Launched map tasks=5
Data-local map tasks=5
Total time spent by all maps in occupied slots (ms)=22147
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=22147
Total vcore-milliseconds taken by all map tasks=22147
Total megabyte-milliseconds taken by all map tasks=22678528
Map-Reduce Framework
Map input records=749361
Map output records=743569
Input split bytes=620
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=1853
CPU time spent (ms)=14720
Physical memory (bytes) snapshot=1471266816
Virtual memory (bytes) snapshot=12901552128
Total committed heap usage (bytes)=1236795392
Peak Map Physical memory (bytes)=402010112
Peak Map Virtual memory (bytes)=2586484736
File Input Format Counters
Bytes Read=213620517
File Output Format Counters
Bytes Written=212238254

建表

创建视频表 quickvideo_video_orc,用户表 quickvideo_user_orc,使用 ORC 格式存储;以及用于加载数据的 quickvideo_video_oriquickvideo_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
50
51
-- 视频表
create table quickvideo_video_orc(
videoId string,
uploader string,
age int,
category array<string>,
length int,
views int,
rate float,
rating int,
comment int,
relatedId array<string>)
clustered by (uploader) into 6 buckets
row format delimited fields terminated by "\t"
collection items terminated by "&"
stored as orc;

-- 用户表
create table quickvideo_user_orc(
uploader string,
videos int,
friend int)
row format delimited
fields terminated by "\t"
stored as orc;

-- 视频表(加载数据用)
create table quickvideo_video_ori(
videoId string,
uploader string,
age int,
category array<string>,
length int,
views int,
rate float,
rating int,
comment int,
relatedId array<string>)
row format delimited
fields terminated by "\t"
collection items terminated by "&"
stored as textfile;

-- 用户表(加载数据用)
create table quickvideo_user_ori(
uploader string,
videos int,
friend int)
row format delimited
fields terminated by "\t"
stored as textfile;

加载数据:

1
load data inpath "/hivetable/quickvideo/video" into table quickvideo_video_ori;

插入数据到 ORC 表:

1
2
insert into table quickvideo_video_orc select * from quickvideo_video_ori;
insert into table quickvideo_user_orc select * from quickvideo_user_ori;

对比 TEXTFILE 和 ORC 两种存储格式占用空间的大小,可以发现 ORC 格式的数据压缩率还是很高的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ hdfs dfs -du -h /user/hive/warehouse/myhive.db/quickvideo_video_ori
138.7 M 416.0 M /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00000
56.4 M 169.2 M /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00001
6.4 M 19.3 M /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00002
883.7 K 2.6 M /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00003
54.5 K 163.4 K /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00004

$ hdfs dfs -du -h /user/hive/warehouse/myhive.db/quickvideo_video_orc
17.4 M 52.3 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000000_0
17.6 M 52.9 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000001_0
17.3 M 51.9 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000002_0
17.2 M 51.7 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000003_0
17.3 M 51.9 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000004_0
17.2 M 51.6 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000005_0

业务分析

统计观看次数Top10

使用 order by 按照 views 字段全局排序,只显示前 10 条:

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
> select
videoId,
uploader,
age,
category,
length,
views,
rate,
rating,
comment
from quickvideo_video_orc
order by views desc
limit 10;

+--------------+------------------+------+---------------------+---------+-----------+-------+----------+-----------+
| videoid | uploader | age | category | length | views | rate | rating | comment |
+--------------+------------------+------+---------------------+---------+-----------+-------+----------+-----------+
| dMH0bHeiRNg | judsonlaipply | 415 | ["Comedy"] | 360 | 42513417 | 4.68 | 87520 | 22718 |
| 0XxI-hvPRRA | smosh | 286 | ["Comedy"] | 194 | 20282464 | 4.49 | 80710 | 35408 |
| 1dmVU08zVpA | NBC | 670 | ["Entertainment"] | 165 | 16087899 | 4.79 | 30085 | 5945 |
| RB-wUgnyGv0 | ChrisInScotland | 506 | ["Entertainment"] | 159 | 15712924 | 4.78 | 8222 | 1996 |
| QjA5faZF1A8 | guitar90 | 308 | ["Music"] | 320 | 15256922 | 4.84 | 120506 | 38393 |
| -_CSo1gOd48 | tasha | 190 | ["People","Blogs"] | 205 | 13199833 | 3.71 | 38045 | 9904 |
| 49IDp76kjPw | TexMachina | 381 | ["Comedy"] | 59 | 11970018 | 4.55 | 22579 | 5280 |
| tYnn51C3X_w | CowSayingMoo | 516 | ["Music"] | 231 | 11823701 | 4.67 | 29479 | 10367 |
| pv5zWaTEVkI | OkGo | 531 | ["Music"] | 184 | 11672017 | 4.83 | 42386 | 10082 |
| D2kJZOfq7zk | mrWoot | 199 | ["People","Blogs"] | 185 | 11184051 | 4.82 | 42162 | 10819 |
+--------------+------------------+------+---------------------+---------+-----------+-------+----------+-----------+

统计视频数量Top10的类别

  1. 统计每个类别有多少个视频,显示出包含视频最多的前10个类别。
  2. 要按照类别 group by 聚合,然后 count 组内的 videoid 个数。
  3. 一个视频对应一个或多个类别。所以如果要 group by 类别,需要先对类别进行 explode,然后再进行 count 即可。
  4. 按照视频数量排序,显示前 10 条。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  -- 排序,显示前10条
> select categories, videonum from
-- 根据分类分组并求和
(select categories, count(*) videonum from
-- 列转行
(select videoid, categories from
quickvideo_video_orc lateral view explode(category) quickvideo_video_orc_temp as categories) t1
group by categories) t2
order by videonum desc
limit 10;
+----------------+-----------+
| categories | videonum |
+----------------+-----------+
| Music | 179049 |
| Entertainment | 127674 |
| Comedy | 87818 |
| Film | 73293 |
| Animation | 73293 |
| Sports | 67329 |
| Games | 59817 |
| Gadgets | 59817 |
| People | 48890 |
| Blogs | 48890 |
+----------------+-----------+

统计观看次数Top20的视频所属类别及对应视频数量

  1. 查询观看次数 Top20。
  2. 对这 20 条数据中的类别进行 explode
  3. 根据分类进行分组计数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  -- 根据分类进行分组计数
> select categories, count(*) videonum from
-- 对类别进行explode
(select views, categories from
-- 查询观看次数Top20
(select views, category from quickvideo_video_orc
order by views desc
limit 20) t1 lateral view explode(category) quickvideo_video_orc_temp as categories) t2
group by categories;
+----------------+-----------+
| categories | videonum |
+----------------+-----------+
| Blogs | 2 |
| Comedy | 6 |
| Entertainment | 6 |
| Music | 5 |
| People | 2 |
| UNA | 1 |
+----------------+-----------+

统计观看次数Top50的视频所关联视频的类别排名

  1. 查询观看次数 Top50。
  2. 对这 50 条数据中的相关视频id进行 explode
  3. 关联视频表,获得分类。
  4. 对分类进行 explode
  5. 根据分类进行分组计数。
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
  -- 分组求和
> select categories, count(*) categorynum from
(select categories from (select t3.videoid, t3.category from
(select relatedIds from
(select relatedId, views from quickvideo_video_orc
order by views desc
limit 50) t1 lateral view explode(relatedId) quickvideo_video_orc_relatedId as relatedIds
where relatedIds is not null) t2
left join quickvideo_video_orc t3
on t2.relatedIds = t3.videoid) t4 lateral view explode(category) quickvideo_video_orc_category as categories) t5 group by categories
order by categorynum desc;
+----------------+--------------+
| categories | categorynum |
+----------------+--------------+
| Comedy | 237 |
| Entertainment | 216 |
| Music | 195 |
| Blogs | 51 |
| People | 51 |
| Film | 47 |
| Animation | 47 |
| News | 24 |
| Politics | 24 |
| Games | 22 |
| Gadgets | 22 |
| Sports | 19 |
| Howto | 14 |
| DIY | 14 |
| UNA | 13 |
| Places | 12 |
| Travel | 12 |
| Animals | 11 |
| Pets | 11 |
| Autos | 4 |
| Vehicles | 4 |
+----------------+--------------+

统计每个视频类别中的观看次数Top10

  1. 创建新表,在视频表的基础上存放对分类进行 explode 后的数据。
  2. 插入数据到新表。
  3. 统计每个视频类别中的观看次数 Top10。
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
50
51
-- 创建新表
> create table quickvideo_video_category(
videoId string,
uploader string,
age int,
categoryId string,
length int,
views int,
rate float,
rating int,
comment int,
relatedId array<string>)
row format delimited fields terminated by "\t"
collection items terminated by "&"
stored as orc;

-- 插入数据
> insert into table quickvideo_video_category
select videoId,uploader,age,categoryId,length,views,rate,rating,comment,relatedId
from quickvideo_video_orc lateral view explode(category) quickvideo_video_orc_category as categoryId;

-- 统计每个类别观看次数TOP10
> select categoryId, views, videoid from
(select categoryId, views, videoid,
dense_rank() over(partition by categoryId order by views desc) drp
from quickvideo_video_category) t1
where t1.drp <= 10;
+----------------+-----------+--------------+
| categoryid | views | videoid |
+----------------+-----------+--------------+
| Animation | 5840839 | sdUUx5FdySs |
| Animation | 5147533 | 6B26asyGKDo |
| Animation | 3772116 | H20dhY01Xjk |
| Animation | 3356163 | 55YYaJIrmzo |
| Animation | 3230774 | JzqumbhfxRo |
| Animation | 3114215 | eAhfZUZiwSE |
| Animation | 2866490 | h7svw0m-wO0 |
| Animation | 2830024 | tAq3hWBlalU |
| Animation | 2569611 | AJzU3NjDikY |
| Animation | 2337238 | ElrldD02if0 |
| Autos | 2803140 | RjrEQaG5jPM |
| Autos | 2773979 | cv157ZIInUk |
| Autos | 1832224 | Gyg9U1YaVk8 |
| Autos | 1412497 | 6GNB7xT3rNE |
| Autos | 1347317 | tth9krDtxII |
| Autos | 1262173 | 46LQd9dXFRU |
| Autos | 1013697 | pdiuDXwgrjQ |
| Autos | 956665 | kY_cDpENQLE |
| Autos | 942604 | YtxfbxGz1u4 |
| Autos | 847442 | aCamHfJwSGU |
...

统计视频数量Top10的用户及他们上传的观看次数Top10的视频

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
  -- 过滤获得视频数量Top10的用户的观看次数Top10的视频,并对结果根据视频数量和观看次数进行倒序排序
> select uploader, uploadnum, videoId, views from
-- 对视频数量Top10的用户的视频的观看次数进行排名
(select uploader, uploadnum, videoId, views,
dense_rank() over(partition by uploader order by views desc) drp from
-- 关联视频表获得视频ID和观看次数
(select t2.uploader, t2.uploadnum, t3.videoId, t3.views from
-- 查询视频数量Top10的用户
(select uploader, uploadnum from
-- 统计各个用户的视频数量
(select uploader, count(*) uploadnum
from quickvideo_video_orc
group by uploader) t1
order by uploadnum desc limit 10) t2
left join quickvideo_video_orc t3
on t2.uploader = t3.uploader) t4) t5
where t5.drp <= 10
order by uploadnum desc, uploader, views desc;
+-----------------+------------+--------------+----------+
| uploader | uploadnum | videoid | views |
+-----------------+------------+--------------+----------+
| GRANHERMANO8 | 421 | PpH8LTg4ifo | 51872 |
| GRANHERMANO8 | 421 | 8vi6RNoOCL4 | 51829 |
| GRANHERMANO8 | 421 | E0vb21MPEa4 | 36132 |
| GRANHERMANO8 | 421 | pqAuixCAT7U | 30028 |
| GRANHERMANO8 | 421 | gnjXMxB2jnM | 18115 |
| GRANHERMANO8 | 421 | 4_NUEKfy3kw | 14387 |
| GRANHERMANO8 | 421 | ejoUmamV3N8 | 14364 |
| GRANHERMANO8 | 421 | 3CKkvlQZrwU | 13030 |
| GRANHERMANO8 | 421 | igCG6a3vKy0 | 11460 |
| GRANHERMANO8 | 421 | BRTNo_DCGt8 | 9857 |
| CBS | 249 | 5bry10DU8bo | 4085011 |
| CBS | 249 | YgW7or1TuFk | 2899397 |
| CBS | 249 | e0yX5Ns6gh4 | 2802303 |
| CBS | 249 | NvQScRuZj9s | 2318862 |
| CBS | 249 | pX5gRaxcviE | 1957631 |
| CBS | 249 | 3SCJLlSf21Y | 1779922 |
| CBS | 249 | CuYD2cwMbpw | 1649511 |
| CBS | 249 | DUaJLJlTZLk | 1333416 |
| CBS | 249 | Vz8AuKmItwM | 1321534 |
| CBS | 249 | c5P6MLiKEJI | 1247356 |
...

参考资料

Hive Data Types
Hive Operators and User-Defined Functions (UDFs)
Order, Sort, Cluster, and Distribute By
HIVE Row Formats和SerDe
LanguageManual DDL BucketedTables
Hive之分区(Partitions)和桶(Buckets)
LanguageManual Sampling
Hive2.0函数大全(中文版)
Hive 官方手册翻译 – Hive DML(数据操纵语言)