Hive（完整版）

Hive

1.基本概念

Hive本质上是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并 提供类 SQL 查询功能。通俗一点就是Hive相当于一个hadoop的客户端，利用hdfs存储数据，利用mapreduce计算框架来进行计算任务，好处就是可以将人从繁琐的mapreduce程序中解放出来，通过编写简单的HQL语句从而实现对复杂逻辑的运算。

2.优缺点

优点

• 采用类sql的语法，开发简单
• 对数据量大，实时性要求不高的场景，发挥作用尤为明显
• hive支持用户自定义函数

缺点

• hive不擅长处理实时性要求比较高的数据
• hive自动生成Mapreduce任务，通常情况下不够智能化
• hive的任务执行粒度比较粗，由于封装性太好，导致调优比较困难

3.hive架构原理

1.客户端可以采用jdbc的方式访问hive

2.客户端将编写好的HQL语句提交，经过SQL解析器，编译器，优化器，执行器执行任务。hive的存算都依赖于hadoop框架，所依赖的真实数据存放在hdfs中，解析好的mapreduce程序，提交给yarn。

3.另外hive框架自带一个名为debay的数据库，其作用是用来记录hdfs上数据的存放位置，也就是说，在客户端提交任务之后，hive优先会去数据库中查询所需要数据在hdfs上面的路径信息，然后在拿着路径信息再去hdfs寻找数据。但是debay有一个缺点就是只支持单用户访问，通常情况下，会将debay数据库换成mysql数据库。

4.hive的安装

1.hive在安装之前需要安装一下mysql数据库，下面编写一个mysql自动安装脚本方便安装mysql

read -p "请输入mysql8的zx压缩包文件所在路径(eg:/opt/mysql8.xxx.xz):" FILE_PATH
read -p "请输入想要安装的目录(eg:opt/mysql):" DEST_PATH

rpm -e --nodeps $(rpm -qa | grep mariadb)

echo -e "\033[40;32m   (1/13)正在解压,请耐心等待解压过程约1-3分钟... \033[0m"
tar Jxf $FILE_PATH -C .
echo -e "\033[40;32m   解压完成 \033[0m"

echo -e "\033[40;32m   (2/13)移动加压后的文件到$DEST_PATH \033[0m"
mv mysql-8*x86_64 $DEST_PATH

echo -e "\033[40;32m   (3/13)添加环境变量$DEST_PATH \033[0m"
echo "export MYSQL_HOME=$DEST_PATH" >>/etc/profile
echo 'export PATH=.:$MYSQL_HOME/bin:$PATH' >>/etc/profile
source /etc/profile

echo -e "\033[40;32m   (4/13)创建data目录 \033[0m"
mkdir $DEST_PATH/data

echo -e "\033[40;32m   (5/13)创建my.cnf配置文件 \033[0m"
rm -rf /etc/my.cnf
echo "
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
user=mysql
socket=/tmp/mysql.sock
basedir=$DEST_PATH
datadir=$DEST_PATH/data
log-error=$DEST_PATH/error.log
pid-file = $DEST_PATH/mysql.pid
transaction_isolation = READ-COMMITTED
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1
" > /etc/my.cnf
echo 'sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"' >> /etc/my.cnf

echo -e "\033[40;32m   (6/13)创建mysql组 \033[0m"
groupadd mysql

echo -e "\033[40;32m   (7/13)创建mysql用户并加入mysql组 \033[0m"
useradd -g mysql mysql

echo -e "\033[40;32m   (8/13)修改安装目录权限和所有者 \033[0m"
chown -R mysql:mysql $DEST_PATH
chmod -R 755 $DEST_PATH

echo -e "\033[40;32m   (9/13)初始化mysql \033[0m"
$DEST_PATH/bin/mysqld --initialize --user=mysql

echo -e "\033[40;32m   (10/13)尝试启动mysql \033[0m"
$DEST_PATH/support-files/mysql.server start

echo -e "\033[40;32m   (11/13)将mysqld添加为服务并设置开机自启动 \033[0m"
cp $DEST_PATH/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --level 345 mysqld on

echo -e "\033[40;32m   (12/13)重启mysql \033[0m"
service mysqld restart

echo -e "\033[40;32m   (13/13)读取临时密码 \033[0m"
TEMP_PW=$(cat $DEST_PATH/error.log | grep 'password' | awk -F' ' '{print $NF}')

echo -e "

\033[40;32m  mysql的初始临时密码为:$TEMP_PW \033[0m

\033[40;32m  使用初始密码登录mysql后,您可以使用如下SQL修改初始密码: \033[0m
\033[40;33m  ALTER user 'root'@'localhost' IDENTIFIED BY '123456'; \033[0m

\033[40;32m  使用如下SQL添加可远程访问的root用户: \033[0m
\033[40;33m  CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password  BY '123456'; \033[0m
\033[40;33m  GRANT ALL ON *.* TO 'root'@'%'; \033[0m
\033[40;33m  FLUSH PRIVILEGES; \033[0m

\033[40;32m  3秒后将使用初始密码登录mysql,感谢您的使用 \033[0m
"

sleep 3

mysql -uroot -p$TEMP_PW
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

2.待mysql安装完毕之后，测试mysql的连通性

mysql -uroot -p

//换一台机器（测试远程连接）
mysql -h安装mysql机器的ip -uroot -p 
1
2
3
4

3.上传解压hive的安装包，进入conf目录，打开hive-site.xml 文件

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
 <!-- jdbc 连接的 URL -->
 <property>
 <name>javax.jdo.option.ConnectionURL</name>
 <value>jdbc:mysql://host01:3306/metastore?useSSL=false</value>
</property>
 <!-- jdbc 连接的 Driver-->
 <property>
 <name>javax.jdo.option.ConnectionDriverName</name>
 <value>com.mysql.cj.jdbc.Driver</value>
</property>
<!-- jdbc 连接的 username-->
 <property>
 <name>javax.jdo.option.ConnectionUserName</name>
 <value>root</value>
 </property>
 <!-- jdbc 连接的 password -->
 <property>
 <name>javax.jdo.option.ConnectionPassword</name>
 <value>123456</value>
</property>
 <!-- Hive 元数据存储版本的验证 -->
 <property>
 <name>hive.metastore.schema.verification</name>
 <value>false</value>
</property>
 <!--元数据存储授权-->
 <property>
 <name>hive.metastore.event.db.notification.api.auth</name>
 <value>false</value>
 </property>
 <!-- Hive 默认在 HDFS 的工作目录 -->
 <property>
 <name>hive.metastore.warehouse.dir</name>
 <value>/user/hive/warehouse</value>
 </property>
</configuration>
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

4.拷贝mysql 的连接驱动到hive的lib文件夹下

5.打开mysql创建一个名为metastore的数据库（与hive配置文件URL地址后面的数据库名保持一致即可）

6.初始化hive元数据库

schematool -initSchema -dbType mysql -verbose
1

7.配置环境变量，并刷新配置文件

8.直接在命令行输入hive启动hive客户端

9.在启动过程中会出现hive的日志jar包与hadoop的日志jar包冲突的问题，解决办法：将版本较低的jar包格式类型，修改.jar为.bak即可

10.hive的日志文件默认保存在/tmp/xu/hive.log（当前用户名下），过一段时间就会删除，因此修改日志的存放位置，打开/hive/conf/hive-log4j2.properties.template 文件名称为 hive-log4j2.properties，将日志更换到hive包下的logs文件下的hive.log中，重新启动hive客户端。

hive.log.dir=/opt/module/hive-3.1.2/logs
1

11.元数据信息说明：hive下的所有数据库信息都保存在mysql数据库中metastore下的dbs中，表信息保存在tbls中，数据库位置在hdfs中的/user/hive/warehouse下

5.hive的基本操作

1.hive -e不进入hive的客户端，执行sql语句

hive -e "select * from test;"
1

2.hive -f不进入hive的客户端，执行sql脚本

//客户端执行执行sql脚本
hive -f ./hivef.sql

//客户端执行slq脚本并将执行结果写入另一个文件中
hive -f ./hivef.sql > ./result.txt
1
2
3
4
5

3.查看hdfs中的文件，因为hive的底层存储依赖于hdfs，因此在hive客户端中可以直接访问hdfs

dfs -ls /
1

4.关闭客户端

exit;
quit;
ctrl + c

1
2
3
4

6.hive常见参数的配置

1.在进入客户端之后，来回进行数据库切换操作，过一会就不知道在那个数据库中，在hive-site.xml配置如下信息

 <!-- 配置表的列头信息显示 -->
<property>
     <name>hive.cli.print.header</name>
     <value>true</value>
 </property>
 <!-- 配置当前数据库显示 -->
 <property>
     <name>hive.cli.print.current.db</name>
     <value>true</value>
 </property>

1
2
3
4
5
6
7
8
9
10
11

2.查看当前所有配置信息

set;

1
2

3.修改配置信息的几种方式

1. 配置文件，在配置文件中使用Key，Value的形式对相应属性进行配置

2. 命令行参数（仅对本次hive客户端启动有效）

   hive -f ./hivef.sql -hiveconf mapred.reduce.tasks=10;
   
   1
   2

3. 使用hive客户端的set对相应参数进行配置（仅对本次hive客户端启动有效）

   set mapred.reduce.tasks=100;
   
   1
   2

7.数据库表相关

1.创建一个数据库并指定其在hdfs上的位置，默认位置在/user/hive/warehouse/*.db。

create database IF NOT EXISTS db_hive location '/db_hive2.db';

1
2

2.显示数据库信息

//显示数据库信息
desc database db_hive;
//显示数据库详细信息
desc database extended db_hive;

1
2
3
4
5

3.删除数据库

//若数据库为空
drop database db_hive;
//如数据库不为空，仍要删除
drop database db_hive cascade;

1
2
3
4
5

4.创建表

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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

1
2
3
4
5
6
7
8
9
10
11
12

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常； 用户可以用 IF NOT EXISTS 选项来忽略这个异常。

（2）EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实 际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外 部表只删除元数据，不删除数据。

（3）COMMENT：为表和列添加注释。

（4）PARTITIONED BY 创建分区表

（5）CLUSTERED BY 创建分桶表

（6）SORTED BY 不常用，对桶中的一个或多个列另外排序

（7）ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] |

SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需 要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表 的具体的列的数据。 SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。

（8）STORED AS 指定存储文件类型 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列 式存储格式文件） 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

（9）LOCATION ：指定表在 HDFS 上的存储位置。

（10）AS：后跟查询语句，根据查询结果创建表。

（11）LIKE 允许用户复制现有的表结构，但是不复制数据。

5.外部表：在创建表的时候添加 关键字 external

create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

1
2
3
4
5
6
7

好处是，外部表删除时，只会删除mysql数据库里面的元数据，而不会真正删除hdfs里面的数据文件，相对来说操作比较安全，通常共享数据就创建外部表。

外部表和管理表的相互转化

set tblproperties('EXTERNAL'='TRUE'); 	//外部表
set tblproperties('EXTERNAL'='FALSE'); 	//内部表

1
2
3

6.创建一个数据库并指定其在HDFS上面的位置

hive (default)> create database db_hive2 location '/db_hive2.db';

1
2

7.查看数据库详情

//查看数据库信息
desc database test;

//查看数据库详细信息
desc database extended test;

1
2
3
4
5
6

8.删除数据库

//删除数据库
drop database if exists test;

//如果数据库中有已经存在表,那么用以上方法是无法删除的,需要加上强制符cascade
drop database if exists test cascade;

1
2
3
4
5
6

9.重命名表

//RENAME TO必须为大写
ALTER TABLE table_name RENAME TO new_table_name

1
2
3

8.数据导入

1.向表中加载数据（load）

load data [local] inpath '数据的 path' [overwrite] 
into table student [partition (partcol1=val1,…)];

（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表;不加local则表示从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据,否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区

1
2
3
4
5
6
7
8
9
10
11

2.以查询的方式向表中插入数据

//以查询的方式向表中插入数据
insert overwrite table sut4 select * from stu3;

//直接向表中插入数据
insert into table stu5 values(1,'wangwu'),(2,'zhaoliu');

注意：insert into 与insert overwrite 的区别是overwrite会覆盖掉原来的数据

1
2
3
4
5
6
7
8

3.向表中加载数据

//加local关键字表示从本地加载数据到hive表中
load data local inpath '/opt/module/hive/datas/student.txt' into table default.student;

//不加local关键字表示的是从hdfs集群加载文件到hive表中
load data inpath '/opt/module/hive/datas/student.txt' into table default.student;

1
2
3
4
5
6

9.函数

1.常用函数

1.求总行数count()
select count(*) cnt from emp;

2.求最大值max()
select max(sal) max_sal from emp;

3.求最小值min()
select min(sal) min_sal from emp;

4.求总和sum()
select sum(sal) sum_sal from emp;

5.求平均值avg()
select avg(sal) avg_sal from emp;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.Limit语句

LIMIT 子句用于限制返回的行数。
select * from emp limit 5;

1
2
3

3.where子句

1.where用来过滤查询中不需要的结果
2.where子句紧跟from
3.where子句不能使用字段别名
 select * from emp where sal >1000;

1
2
3
4
5

4.比较运算符（Between/In/ Is Null）

1.查询出薪水等于 5000 的所有员工
select * from emp where sal =5000;

2.查询工资在 500 到 1000 的员工信息
select * from emp where sal between 500 and 1000;

3.查询 comm 为空的所有员工信息
select * from emp where comm is null;

4.查询工资是 1500 或 5000 的员工信息
select * from emp where sal IN (1500, 5000);

1
2
3
4
5
6
7
8
9
10
11
12

5.Like关键字

1.查找名字以 A 开头的员工信息
select * from emp where ename LIKE 'A%';

2.查找名字中第二个字母为 A 的员工信息
select * from emp where ename LIKE '_A%';

3.查找名字中带有 A 的员工信息
select * from emp where ename RLIKE '[A]';

1
2
3
4
5
6
7
8
9

6.逻辑运算符（And/Or/Not）

1.查询薪水大于 1000，部门是 30
select * from emp where sal>1000 and deptno=30;

2.查询薪水大于 1000，或者部门是 30
select * from emp where sal>1000 or deptno=30;

3.查询除了 20 部门和 30 部门以外的员工信息
select * from emp where deptno not IN(30, 20);

1
2
3
4
5
6
7
8
9

7.Group By语句

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
1.计算 emp 表每个部门的平均工资
 select t.deptno, avg(t.sal) avg_sal 
 from emp t 
 group by t.deptno;
 
2.计算 emp 每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal 
from emp t 
group by
t.deptno, t.job;

1
2
3
4
5
6
7
8
9
10
11
12

8.Having

having与where不同点
（1）where 后面不能写分组函数，而 having 后面可以使用分组函数。
（2）having 只用于 group by 分组统计语句。
1.求每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;
 
2.求每个部门的平均薪水大于 2000 的部门
select deptno, avg(sal) avg_sal 
from emp 
group by deptno 
having avg_sal > 2000;

1
2
3
4
5
6
7
8
9
10
11
12

9.join

1.内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select e.empno, e.ename, d.deptno 
from emp e 
join dept d 
on 
e.deptno = d.deptno;

2.左外连接
左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno 
from emp e 
left join dept d 
on 
e.deptno = d.deptno;

3.右外连接
右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno 
from emp e right join 
dept d 
on 
e.deptno = d.deptno;

4.满外连接
满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。
select e.empno, e.ename, d.deptno 
from emp e 
full join dept d 
on 
e.deptno = d.deptno;

5.多表连接
SELECT e.ename, d.dname, l.loc_name
FROM emp e 
JOIN dept d
ON d.deptno = e.deptno 
JOIN location l
ON d.loc = l.loc;

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

10.排序

1.全局排序（Order by）

Order By：全局排序，只有一个 Reducer（在数据量非常庞大的情况下，Order By一般是禁用的）
按照部门和工资升序排序
select ename, deptno, sal from emp order by deptno, sal;

1
2
3
4

2.每个 Reduce 内部排序（Sort By）

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。
1.设置 reduce 个数
set mapreduce.job.reduces=3;

2.查看设置 reduce 个数
set mapreduce.job.reduces;

3.根据部门编号降序查看员工信息
select * from emp sort by deptno desc;

1
2
3
4
5
6
7
8
9
10
11

3.分区（Distribute By）

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为
了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition
（自定义分区），进行分区，结合 sort by 使用。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。
1.先按照部门编号分区，再按照员工编号降序排序。
set mapreduce.job.reduces=3;
insert overwrite local directory 
'/opt/module/data/distribute-result' 
select * from emp distribute by 
deptno sort by empno desc;

1
2
3
4
5
6
7
8
9
10
11

4.Cluster By

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 代替前两者。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或 DESC。
以下两种写法等价
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

1
2
3
4
5
6

11.分区表

1.创建分区表

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

1
2
3
4
5
6

2.数据的导入

load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition 
partition(day='20200401');
load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition 
partition(day='20200402');
load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition 
partition(day='20200403');

1
2
3
4
5
6
7

3.查询

#单分区查询
select * from dept_partition where day='20200401';

#多分区联合查询
select * from dept_partition where day='20200401'
 union
 select * from dept_partition where day='20200402'
 union
 select * from dept_partition where day='20200403';

select * from dept_partition where day='20200401' or day='20200402' or day='20200403';

1
2
3
4
5
6
7
8
9
10
11
12

4.增加分区

增加单个分区
alter table dept_partition add partition(day='20200404');

1
2
3

5.删除分区

删除单个分区
alter table dept_partition drop partition (day='20200406');

1
2
3

6.查看当前表有哪些分区

show partitions dept_partition;

1
2

12.二级分区

1.创建二级分区表

create table dept_partition2(
 deptno int, dname string, loc string
 )
 partitioned by (day string, hour string)
 row format delimited fields terminated by '\t';

1
2
3
4
5
6

2.加载数据到二级分区表中

load data local inpath '/opt/module/hive/datas/dept_20200401.log' 
into tabledept_partition2 partition(day='20200401', hour='12');

1
2
3

3.查询分区数据

select * from dept_partition2 where day='20200401' and hour='12';

1
2

13.抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结 果。Hive 可以通过对表进行抽样来满足这个需求。

select * from stu_buck tablesample(bucket 1 out of 4 on id);

1
2

14.Hive的内置函数

1.查看所有的内置函数

show functions;

1
2

2.查看函数的具体用法（部分函数可能没有）

desc function extended exp;

1
2

3.常用函数

函数：nvl
说明：给值为NULL的数据赋值，它的格式是NVL(value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value 的值，否则返回value的值，如果两个参数
都为NULL，则返回NULL。
样例：如果员工的 comm 为 NULL，则用-1 代替
select comm,nvl(comm, -1) from emp;

函数：CASE(字段1)WHEN(条件1)THEN(满足条件1的结果)ELSE(不满足条件1的结果)END
说明：如果字段1满足条件1则走满足条件1的结果，如果不满足条件1则走不满足的条件
样例：根据部门统计每个部门男女生各有多少人
select
 dept_id,
 sum(case sex when '男' then 1 else 0 end) male_count,
 sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;

函数:concat()
说明:输入任意个字符串，将这些字符串拼接起来
样例：返回下面两个字符串拼接后的结果
select concat('a','+','b','=','ab')

函数:concat_WS()
说明:第一个参数输入连接符，后面输入多个字符串或字符串数组
样例：将下面几个字符串用‘+’连接
select concat_ws('+','a','b','c')

函数:over()
说明:开窗函数
样例：查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;
样例：查询顾客上次购买的时间
select name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, 
lag(orderdate,2) over (partition by name order by orderdate) as time2 
from business;
样例：查询前 20%时间的订单信息
select * from (
 select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
 from business
) t
where sorted = 1;

函数:rank()
说明:排名
样例：按如下要求进行排名
select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

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

15.自定义函数

1.UDF（一进一出）

2.UDAF(多进一出)

3.UDTF（一进多出）

在编写自定义函数的时候，三者主要的区别就是要导入的基础类不一致

步骤一：引入依赖

<dependencies>
	<dependency>
		<groupId>org.apache.hive</groupId>
		<artifactId>hive-exec</artifactId>
		<version>3.1.2</version>
	</dependency>
</dependencies>

1
2
3
4
5
6
7
8

步骤二：继承对应的基础类

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import 
org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectIn
spectorFactory;
/**
* 自定义 UDF 函数，需要继承 GenericUDF 类
* 需求: 计算指定字符串的长度
*/
public class MyStringLength extends GenericUDF {
 /**
 *
 * @param arguments 输入参数类型的鉴别器对象
 * @return 返回值类型的鉴别器对象
 * @throws UDFArgumentException
 */
 @Override
 public ObjectInspector initialize(ObjectInspector[] arguments) throws 
UDFArgumentException {
 // 判断输入参数的个数
 if(arguments.length !=1){
 throw new UDFArgumentLengthException("Input Args Length 
Error!!!");
 }
 // 判断输入参数的类型
 
if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)
){
 throw new UDFArgumentTypeException(0,"Input Args Type 
Error!!!");
 }
 //函数本身返回值为 int，需要返回 int 类型的鉴别器对象
 return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
 }
 /**
 * 函数的逻辑处理
 * @param arguments 输入的参数
 * @return 返回值
 * @throws HiveException
 */
 @Override
 public Object evaluate(DeferredObject[] arguments) throws 
HiveException {
 if(arguments[0].get() == null){
 return 0;
 }
 return arguments[0].get().toString().length();
 }
 @Override
 public String getDisplayString(String[] children) {
 return "";
 }
}

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

步骤三：打成jar包

步骤四：将打好的jar包上传到hive的classpath中

add jar /opt/module/data/myudf.jar;

1
2

步骤五：创建临时函数与开发好的 java class 关联

create temporary function my_len as "com.atguigu.hive.MyStringLength";

1
2

步骤六：在hive中使用创建好的函数

 select ename,my_len(ename) ename_len from emp;

1
2

16.hive调优

1.压缩

Hive支持的几种压缩格式

1.1开启map阶段的压缩

优势：开启 map 输出阶段压缩可以减少 job 中 map 和 Reduce task 间数据传输量。

步骤一：

开启 hive 中间传输数据压缩功能
set hive.exec.compress.intermediate=true;

1
2
3

步骤二：

开启 mapreduce 中 map 输出压缩功能
set mapreduce.map.output.compress=true;

1
2
3

步骤三：

设置 mapreduce 中 map 输出数据的压缩方式
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

1
2
3

步骤四：

执行查询语句
select xxx from xxx;

1
2
3

1.2开启reduce阶段的压缩

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

步骤一：

开启 hive 最终输出数据压缩功能
set hive.exec.compress.output=true;

1
2
3

步骤二：

开启 mapreduce 最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;

1
2
3

步骤三：

设置 mapreduce 最终数据输出压缩方式
 set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

1
2
3

步骤四：

设置 mapreduce 最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type=BLOCK;

1
2
3

步骤五：

测试一下输出结果是否是压缩文件
insert overwrite local directory '/opt/module/data/distribute-result' select * from emp distribute by 
deptno sort by empno desc;

1
2
3
4

2.文件格式

主流的三种文件格式：Textfile、ORC、Parquet

压缩文件的压缩比测试：

步骤一：

--创建一个自带存储格式的物理表
create table log_text(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as textfile;（切换三种文件格式textfile、ORC、Parquet）

1
2
3
4
5
6
7
8
9
10
11
12
13

步骤二：

--向表中加载数据
load data local inpath '/opt/module/hive/datas/log.data' into table log_text ;

1
2
3

步骤三：

 --查看表中文件的大小与源文件的大小进行比较
 dfs -du -h /user/hive/warehouse/log_text;

1
2
3

比较三者的压缩性能

ORC > Parquet > Textfile

切三者经过压缩后的查询效率接近

3.存储与压缩进行结合

1.创建一个 ZLIB 压缩的 ORC 存储方式
建表语句
create table log_orc_zlib(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="ZLIB");
插入数据
insert into log_orc_zlib select * from log_text;
查看插入后的数据
dfs -du -h /user/hive/warehouse/log_orc_zlib/ ;

2.创建一个 SNAPPY 压缩的 ORC 存储方式
建表语句
create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="SNAPPY");

插入数据
insert into log_orc_snappy select * from log_text;
查看插入后的数据
dfs -du -h /user/hive/warehouse/log_orc_snappy/;

3.创建一个 SNAPPY 压缩的 parquet 存储方式
建表语句
create table log_parquet_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as parquet
tblproperties("parquet.compression"="SNAPPY");
插入数据
insert into log_parquet_snappy select * from log_text;
查看插入的数据
dfs -du -h /user/hive/warehouse/log_parquet_snappy/;

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

存储方式和压缩总结

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

4.执行计划

在执行语句之前可以加上explain查看当前语句的执行计划，可以查看执行计划，修改程序的执行流程，达到调优的目的

#不走MR程序
hive (test)> explain select * from bes;
OK
Explain
STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: bes
          Statistics: Num rows: 1 Data size: 2660 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: name (type: string), orderdate (type: string), cost (type: int)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 1 Data size: 2660 Basic stats: COMPLETE Column stats: NONE
            ListSink

Time taken: 1.446 seconds, Fetched: 17 row(s)

#走MR程序的
hive (test)> explain select count(*) from bes;
OK
Explain
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: bes
            Statistics: Num rows: 1 Data size: 2660 Basic stats: COMPLETE Column stats: COMPLETE
            Select Operator
              Statistics: Num rows: 1 Data size: 2660 Basic stats: COMPLETE Column stats: COMPLETE
              Group By Operator
                aggregations: count()
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
                Reduce Output Operator
                  sort order:
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
                  value expressions: _col0 (type: bigint)
      Execution mode: vectorized
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          mode: mergepartial
          outputColumnNames: _col0
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
          File Output Operator
            compressed: false
            Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.637 seconds, Fetched: 43 row(s)

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

5.Fetch抓取

Hive的某些情况的查询可以不必使用 MapReduce 计算。例如：SELECT * FROM emp;在这种情况下，Hive 可以简单地读取 emp对应的存储目录下的文件， 然后输出查询结果到控制台。在 hive-default.xml.template 文件中 hive.fetch.task.conversion 默认是 more，老版本 hive 默认是 minimal，该属性修改为 more 以后，在全局查找、字段查找、limit 查找等都不走 mapreduce。

• 把 hive.fetch.task.conversion 设置成 none，然后执行查询语句，都会执行 mapreduce 程序。

set hive.fetch.task.conversion=none;
select * from emp;
select ename from emp;
select ename from emp limit 3;
1
2
3
4

• 把 hive.fetch.task.conversion 设置成 more，然后执行查询语句，如下查询方式都不 会执行 mapreduce 程序。

set hive.fetch.task.conversion=more;
select * from emp;
select ename from emp;
select ename from emp limit 3;
1
2
3
4