Hive第二篇HQL

3. Hive 的基本操作

3.1 数据库操作

####3.1.1 创建数据库

create database if not exists myhive;
use  myhive;

说明：hive的表存放位置模式是由hive-site.xml当中的一个属性指定的

<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>

####3.1.2 创建数据库并指定位置

create database myhive2 location '/myhive2';

####3.1.3 设置数据库键值对信息

数据库可以有一些描述性的键值对信息，在创建时添加：

create database foo with dbproperties ('owner'='emprock', 'date'='20190120');

查看数据库的键值对信息：

describe database extended foo;

修改数据库的键值对信息：

alter database foo set dbproperties ('owner'='itheima');

####3.1.4 查看数据库更多详细信息

desc database extended  myhive2;

####3.1.5 删除数据库

删除一个空数据库，如果数据库下面有数据表，那么就会报错

drop  database  myhive2;

强制删除数据库，包含数据库下面的表一起删除

drop  database  myhive  cascade;   

3.2 数据库表操作

####3.2.1 创建表的语法:

create [external] table [if not exists] table_name (
col_name data_type [comment '字段描述信息']
col_name data_type [comment '字段描述信息'])
[comment '表的描述信息']
[partitioned by (col_name data_type,...)]
[clustered by (col_name,col_name,...)]
[sorted by (col_name [asc|desc],...) into num_buckets buckets]
[row format row_format]
[storted as ....]
[location '指定表的路径']

说明：

1. create table

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

2. external

可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

3. comment

表示注释,默认不能使用中文

4. partitioned by

表示使用表分区,一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下 .

5. clustered by
   对于每一个表分文件， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。

6. sorted by

   指定排序字段和排序规则

7. row format

​ 指定表文件字段分隔符

8. storted as指定表文件的存储格式, 常用格式:SEQUENCEFILE, TEXTFILE, RCFILE,如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 storted as SEQUENCEFILE。

9. location

指定表文件的存储路径

####3.2.2 内部表的操作

创建表时,如果没有使用external关键字,则该表是内部表（managed table）

Hive建表字段类型

分类	类型	描述	字面量示例
原始类型	BOOLEAN	true/false	TRUE
	TINYINT	1字节的有符号整数, -128~127	1Y
	SMALLINT	2个字节的有符号整数，-32768~32767	1S
	INT	4个字节的带符号整数	1
	BIGINT	8字节带符号整数	1L
	FLOAT	4字节单精度浮点数	1.0
	DOUBLE	8字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，变长	“a”,’b’
	VARCHAR	变长字符串	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组	无法表示
	TIMESTAMP	时间戳，毫秒值精度	122327493795
	DATE	日期	‘2016-03-29’
	INTERVAL	时间频率间隔
复杂类型	ARRAY	有序的的同类型的集合	array(1,2)
	MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
	STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
	UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

建表入门:

use myhive;
create table stu(id int,name string);
insert into stu values (1,"zhangsan");  #插入数据
select * from stu;

创建表并指定字段之间的分隔符

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t';

创建表并指定表文件的存放路径

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' location '/user/stu2';

根据查询结果创建表

create table stu3 as select * from stu2; # 通过复制表结构和表内容创建新表

根据已经存在的表结构创建表

create table stu4 like stu;

查询表的详细信息

desc formatted  stu2;

. 删除表

drop table stu4;

####3.2.3 外部表的操作

外部表说明

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉.

内部表和外部表的使用场景

每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

操作案例

分别创建老师与学生表外部表，并向表中加载数据

创建老师表

create external table teacher (t_id string,t_name string) row format delimited fields terminated by '\t';

创建学生表

create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by '\t';

加载数据

load data local inpath '/export/servers/hivedatas/student.csv' into table student;

加载数据并覆盖已有数据

load data local inpath '/export/servers/hivedatas/student.csv' overwrite  into table student;

从hdfs文件系统向表中加载数据（需要提前将数据上传到hdfs文件系统）

cd /export/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put techer.csv /hivedatas/
load data inpath '/hivedatas/techer.csv' into table teacher;

####3.2.4 分区表的操作

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每月，或者天进行切分成一个个的小的文件,存放在不同的文件夹中.

创建分区表语法

create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

创建一个表带多个分区

create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';

加载数据到分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

加载数据到多分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

多分区表联合查询(使用 union all)

select * from score where month = '201806' union all select * from score where month = '201806';

查看分区

show  partitions  score;

添加一个分区

alter table score add partition(month='201805');

删除分区

alter table score drop partition(month = '201806');

####3.2.5 分区表综合练习

需求描述：

现在有一个文件score.csv文件，存放在集群的这个目录下/scoredatas/month=201806，这个文件每天都会生成，存放到对应的日期文件夹下面去，文件别人也需要公用，不能移动。需求，创建hive对应的表，并将数据加载到表中，进行数据统计分析，且删除表之后，数据不能删除

数据准备：

hdfs dfs -mkdir -p /scoredatas/month=201806
hdfs dfs -put score.csv /scoredatas/month=201806/

创建外部分区表，并指定文件数据存放目录

create external table score4(s_id string, c_id string,s_score int) partitioned by (month string) row format delimited fields terminated by '\t' location '/scoredatas';

进行表的修复(建立表与数据文件之间的一个关系映射)

msck  repair   table  score4;

####3.2.6 分桶表操作

分桶，就是将数据按照指定的字段进行划分到多个文件当中去,分桶就是MapReduce中的分区.

开启 Hive 的分桶功能

set hive.enforce.bucketing=true;

设置 Reduce 个数

set mapreduce.job.reduces=3;

创建分桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

桶表的数据加载，由于通标的数据加载通过hdfs dfs -put文件或者通过load data均不好使，只能通过insert overwrite

创建普通表，并通过insert overwriter的方式将普通表的数据通过查询的方式加载到桶表当中去

创建普通表

create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

普通表中加载数据

load data local inpath '/export/servers/hivedatas/course.csv' into table course_common;

通过insert overwrite给桶表中加载数据

insert overwrite table course select * from course_common cluster by(c_id);

###3.3 修改表结构

重命名:

alter  table  old_table_name  rename  to  new_table_name;

把表score4修改成score5

alter table score4 rename to score5;

增加/修改列信息:

• 查询表结构

desc score5;

• 添加列

alter table score5 add columns (mycol string, mysco int);

• 更新列

alter table score5 change column mysco mysconew int; 

• 删除表

drop table score5;

hive表中加载数据

直接向分区表中插入数据

create table score3 like score;

insert into table score3 partition(month ='201807') values ('001','002','100');

通过查询插入数据

通过load方式加载数据

load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

通过查询方式加载数据

create table score4 like score;
insert overwrite table score4 partition(month = '201806') select s_id,c_id,s_score from score;

4. Hive 查询语法

4.1. SELECT

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

1. order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2. sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3. distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
4. cluster by(字段) 除了具有distribute by的功能外，还会对该字段进行排序.

因此，如果distribute 和sort字段是同一个时，此时，cluster by = distribute by + sort by

4.2. 查询语法

全表查询

select * from score;

选择特定列

select s_id ,c_id from score;

列别名

1）重命名一个列。
2）便于计算。
3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

select s_id as myid ,c_id from score;

4.3. 常用函数

• 求总行数（count）

select count(1) from score;

• 求分数的最大值（max）

select max(s_score) from score;

• 求分数的最小值（min）

select min(s_score) from score;

• 求分数的总和（sum）

select sum(s_score) from score;

• 求分数的平均值（avg）

select avg(s_score) from score;

4.4. LIMIT语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from score limit 3;

4.5. WHERE语句

1. 使用WHERE 子句，将不满足条件的行过滤掉。
2. WHERE 子句紧随 FROM 子句。
3. 案例实操

查询出分数大于60的数据

select * from score where s_score > 60;

比较运算符

操作符	支持的数据类型	描述
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相匹配，而不是只需与其字符串匹配。

• 查询分数等于80的所有的数据

select * from score where s_score = 80;

• 查询分数在80到100的所有数据

select * from score where s_score between 80 and 100;

• 查询成绩为空的所有数据

select * from score where s_score is null;

• 查询成绩是80和90的数据

select * from score where s_score in(80,90);

4.6. LIKE 和 RLIKE

1. 使用LIKE运算选择类似的值
2. 选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。

1. RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

2. 案例实操

   1. 查找以8开头的所有成绩

   	select * from score where s_score like '8%';
   

   1. 查找第二个数值为9的所有成绩数据

   select * from score where s_score like '_9%';
   

   1. 查找s_id中含1的数据

   select * from score where s_id rlike '[1]';  #  like '%1%'
   

4.7. 逻辑运算符

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

• 查询成绩大于80，并且s_id是01的数据

select * from score where s_score >80 and s_id = '01';

• 查询成绩大于80，或者s_id 是01的数

select * from score where s_score > 80 or s_id = '01';

• 查询s_id 不是 01和02的学生

select * from score where s_id not in ('01','02');

4.8. 分组

GROUP BY 语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
案例实操：

• 计算每个学生的平均分数

select s_id ,avg(s_score) from score group by s_id;

• 计算每个学生最高成绩

select s_id ,max(s_score) from score group by s_id;

HAVING 语句

1. having与where不同点

   1. where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
   2. where后面不能写分组函数，而having后面可以使用分组函数。
   3. having只用于group by分组统计语句。

2. 案例实操：

   • 求每个学生的平均分数

   select s_id ,avg(s_score) from score group by s_id;
   

   • 求每个学生平均分数大于85的人

   select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
   

4.9. JOIN 语句

4.9.1. 等值 JOIN

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

案例操作: 查询分数对应的姓名

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;

4.9.2. 表的别名

• 好处

  • 使用别名可以简化查询。
  • 使用表名前缀可以提高执行效率。

• 案例实操

  • 合并老师与课程表

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

4.9.3. 内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select * from techer t inner join course c on t.t_id = c.t_id;

4.9.4. 左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
查询老师对应的课程

select * from techer t left join course c on t.t_id = c.t_id;

4.9.5. 右外连接

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

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

4.9.6. 多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生

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;进行连接操作。

4.10. 排序

4.10.1. 全局排序

Order By：全局排序，一个reduce

1. 使用 ORDER BY 子句排序
   ASC（ascend）: 升序（默认）
   DESC（descend）: 降序

2. ORDER BY 子句在SELECT语句的结尾。

3. 案例实操

   1. 查询学生的成绩，并按照分数降序排列

   SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;
   

   1. 查询学生的成绩，并按照分数升序排列

   SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score asc;
   

4.10.2. 按照别名排序

按照分数的平均值排序

select s_id ,avg(s_score) avg from score group by s_id order by avg;

4.10.3. 多个列排序

按照学生id和平均成绩进行排序

select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;

4.10.4. 每个MapReduce内部排序（Sort By）局部排序

Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。

1. 设置reduce个数

set mapreduce.job.reduces=3;

1. 查看设置reduce个数

set mapreduce.job.reduces;

1. 查询成绩按照成绩降序排列

select * from score sort by s_score;

1. 将查询结果导入到文件中（按照成绩降序排列）

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

4.10.5. 分区排序（DISTRIBUTE BY）

Distribute By：类似MR中partition，进行分区，结合sort by使用。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实操：先按照学生id进行分区，再按照学生成绩进行排序。

1. 设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去

set mapreduce.job.reduces=7;

1. 通过distribute by 进行数据的分区

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score distribute by s_id sort by s_score;

4.10.6. CLUSTER BY

当distribute by和sort by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。

以下两种写法等价

select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

5.Hive Shell参数

5.1 Hive命令行

语法结构

bin/hive [-hiveconf x=y]* [<-i filename>]* [<-f filename>|<-e query-string>] [-S]

说明：

1、 -i 从文件初始化HQL。

2、 -e从命令行执行指定的HQL

3、 -f 执行HQL脚本

4、 -v 输出执行的HQL语句到控制台

5、 -p connect to Hive Server on port number

6、 -hiveconf x=y Use this to set hive/hadoop configuration variables. 设置hive运行时候的参数配置

5.2 Hive参数配置方式

开发Hive应用时，不可避免地需要设定Hive的参数。设定Hive的参数可以调优HQL代码的执行效率，或帮助定位问题。

对于一般参数，有以下三种设定方式：

• 配置文件
• 命令行参数
• 参数声明

配置文件：Hive的配置文件包括

• 用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml

• 默认配置文件： $HIVE_CONF_DIR/hive-default.xml

  用户自定义配置会覆盖默认配置。

另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。

配置文件的设定对本机启动的所有Hive进程都有效。

命令行参数：启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf param=value来设定参数，例如：

bin/hive -hiveconf hive.root.logger=INFO,console

这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。

参数声明：可以在HQL中使用SET关键字设定参数，例如：

set mapred.reduce.tasks=100;

这一设定的作用域也是session级的。

上述三种设定方式的优先级依次递增。即参数声明覆盖命令行参数，命令行参数覆盖配置文件设定。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在Session建立以前已经完成了。

参数声明 > 命令行参数 > 配置文件参数（hive）