本文主要是介绍认识Hive数据库，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

简介

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

其本质是将SQL转换为MapReduce/Spark的任务进行运算，底层由HDFS来提供数据的存储，说白了hive可以理解为一个将SQL转换为MapReduce/Spark的任务的工具，甚至更进一步可以说hive就是一个MapReduce/Spark Sql的客户端为什么要使用hive ?

主要的原因有以下几点:

• 学习MapReduce的成本比较高, 项目周期要求太短, MapReduce如果要实现复杂的查询逻辑开发的难度是比较大的。
• 而如果使用hive, hive采用操作接口类似SQL语法, 提高快速开发的能力. 避免去书写MapReduce,减少学习成本, 而且提供了功能的扩展

hive的特点:

1. 可扩展 :Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。
2. 延展性 : Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
3. 容错 : 良好的容错性，节点出现问题SQL仍可完成执行。

 

Hive 数据类型

Hive的基本数据类型有：TINYINT，SAMLLINT，INT，BIGINT，BOOLEAN，FLOAT，DOUBLE，

STRING，TIMESTAMP(V0.8.0+)和BINARY(V0.8.0+)。

Hive的集合类型有：STRUCT，MAP和ARRAY。

Hive主要有四种数据模型(即表)：内部表、外部表、分区表和桶表。

表的元数据保存传统的数据库的表中，当前hive只支持Derby和MySQL数据库。

2.1 Hive 内部表

Hive中的内部表和传统数据库中的表在概念上是类似的，Hive的每个表都有自己的存储目录，除了外部表外，所有的表数据都存放在配置在hive-site.xml文

件的${hive.metastore.warehouse.dir}/table_name目录下。

创建内部表：

CREATE TABLE IF NOT EXISTS students(user_no INT,name STRING,sex STRING,

grade STRING COMMOT '班级'）COMMONT '学生表'

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

STORE AS TEXTFILE;

2.2 Hive 外部表

被external修饰的为外部表（externaltable），外部表指向已经存在在Hadoop

HDFS上的数据，除了在删除外部表时只删除元数据而不会删除表数据外，其他和内部表很像。创建外部表：

CREATE EXTERNAL TABLE IF NOT EXISTS students(user_no INT,name STRING,sex STRING,

class STRING COMMOT '班级'）COMMONT '学生表'

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

STORE AS SEQUENCEFILE

LOCATION '/usr/test/data/students.txt';

2.3 Hive 分区表

分区表的每一个分区都对应数据库中相应分区列的一个索引，但是其组织方式和传统的关系型数据库不同。在Hive中，分区表的每一个分区都对应表下的一个目录，所有的分区的数据都存储在对应的目录中。

比如说，分区表partitinTable有包含nation(国家)、ds(日期)和city(城市)3 个分区，其中nation = china，ds = 20130506，city = Shanghai则对应HDFS

上的目录为：

/datawarehouse/partitinTable/nation=china/city=Shanghai/ds=20130506/。分区中定义的变量名不能和表中的列相同。创建分区表：

CREATE TABLE IF NOT EXISTS students(user_no INT,name STRING,sex STRING,

class STRING COMMOT '班级'）COMMONT '学生表'

PARTITIONED BY (ds STRING,country STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

STORE AS SEQUENCEFILE;

2.4 Hive 分桶表

桶表就是对指定列进行哈希(hash)计算，然后会根据hash值进行切分数据，将具有不同hash值的数据写到每个桶对应的文件中。

将数据按照指定的字段进行分成多个桶中去，说白了就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去。

创建分桶表：

CREATE TABLE IF NOT EXISTS students(user_no INT,name STRING,sex STRING,

class STRING COMMOT '班级',score SMALLINT COMMOT '总分'）COMMONT '学生表'

PARTITIONED BY (ds STRING,country STRING)

CLUSTERED BY(user_no) SORTED BY(score) INTO 32 BUCKETS

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

STORE AS SEQUENCEFILE;

2.5 Hive 视图

在 Hive 中，视图是逻辑数据结构，可以通过隐藏复杂数据操作（Joins, 子查询, 过滤,数据扁平化）来于简化查询操作。

与关系数据库不同的是，Hive视图并不存储数据或者实例化。一旦创建 HIve 视图，它的 schema 也会立刻确定下来。对底层表后续的更改(如 增加新列)并不会影响视图的 schema。如果底层表被删除或者改变，之后对视图的查询将会 failed。基于以上 Hive view 的特性，我们在ETL和数据仓库中对于经常变化的表应慎重使用视图。创建视图：

CREATE VIEW employee_skills

AS

SELECT name, skills_score['DB'] AS DB,

skills_score['Perl'] AS Perl,

skills_score['Python'] AS Python,

skills_score['Sales'] as Sales,

skills_score['HR'] as HR

FROM employee;

创建视图的时候是不会触发 MapReduce 的 Job，因为只存在元数据的改变。但是，当对视图进行查询的时候依然会触发一个 MapReduce Job 进程：SHOW

CREATE TABLE 或者 DESC FORMATTED TABLE 语句来显示通过      CREATE

VIEW   语句创建的视图。以下是对Hive 视图的 DDL操作：更改视图的属性：

ALTER VIEW employee_skills

SET TBLPROPERTIES ('comment' = 'This is a view');

重新定义视图：

ALTER VIEW employee_skills AS

SELECT * from employee ;

删除视图：

DROP VIEW employee_skills;

 

 

• 创建数据库:

create database if not exists myhive;

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

的 :hive.metastore.warehouse.dir

创建数据库并指定hdfs存储位置 :

create database myhive2 location '/myhive2';

• 修改数据库:

alter database myhive2 set dbproperties('createtime'='20210329');

说明：可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

• 查看数据库详细信息

查看数据库基本信息

hive (myhive)> desc database myhive2;

查看数据库更多详细信息

hive (myhive)> desc database extended myhive2;

• 删除数据库

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

drop database myhive2;

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

drop database

myhive

cascade;

对数据表的操作

对管理表(内部表)的操作:

• 建内部表:

hive (myhive)> use myhive; -- 使用myhive数据库

hive (myhive)> create table stu(id int,name string);

hive (myhive)> insert into stu values (1,"zhangsan");

hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi"); -- 一次插入多条数据

hive (myhive)> select * from stu;

• hive建表时候的字段类型:

	分类		类型		描述		字面量示例
	原始类型		BOOLEAN		true/false		TRUE
		TINYINT 1字节的有符号整数 -128~127 1Y
		SMALLINT 2个字节的有符号整数， -32768~3276 7 1S
		INT BIGINT FLOAT		4个字节的带符号整数 8字节带符号整数 4字节单精度浮点数1.0		1 1L
分类		类型 DOUBLE DEICIMAL STRING VARCHAR CHAR BINARY TIMESTAM P DATE INTERVAL		描述 8字节双精度浮点数 任意精度的带符号小数 字符串，变长 变长字符串 固定长度字符串 字节数组 时间戳，毫秒值精度 日期 时间频率间隔		字面量示例 1.0 1.0 “a”,’b’ “a”,’b’ “a”,’b’ 无法表示 122327493795 ‘2016-03-29’
	复杂类型		ARRAY		有序的的同类型的集合		array(1,2)
		MAP key-value,k ey必须为原始类型， 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)

对decimal类型简单解释下：

用法：decimal(11,2) 代表最多有11位数字，其中后2位是小数，整数部分是9 位；如果整数部分超过9位，则这个字段就会变成null；如果小数部分不足2位，则后面用0补齐两位，如果小数部分超过两位，则超出部分四舍五入

也可直接写 decimal，后面不指定位数，默认是 decimal(10,0) 整数10位，没有小数

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

create table if not exists stu2(id int ,name string) row format delimited fields t

erminated by '\t' stored as textfile location '/user/stu2';

row format delimited fields terminated by '\t' 指定字段分隔符，默认分隔符为 '\001' stored as 指定存储格式 location 指定存储位置

• 根据查询结果创建表

create table stu3 as select * from stu2;

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

create table stu4 like stu2;

• 查询表的结构

只查询表内字段及属性

desc stu2;

详细查询

desc formatted stu2;

• 查询创建表的语句

show create table stu2;

对外部表操作

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

 构建外部表

create external table student (s_id string,s_name string) row format delimited fiel

ds 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 tabl

e student;

 从hdfs文件系统向表中加载数据

load data inpath '/hivedatas/techer.csv' into table techer;

加载数据到指定分区

load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201

210);

	注意： 1.使用 loaddatalocal 表示从本地文件系统加载，文件会拷贝到hdfs 上 2.使用 load data 表示从hdfs文件系统加载，文件会直接移动到hive 相关目录下，注意不是拷贝过去，因为hive认为hdfs文件已经有3副本了，没必要再次拷贝了 3.如果表是分区表，load 时不指定分区会报错 4.如果加载相同文件名的文件，会被自动重命名

对分区表的操作

 创建分区表的语法

create table score(s_id string, s_score int) partitioned by (month string);

 创建一个表带多个分区

create table score2 (s_id string, s_score int) partitioned by (year string,month st

ring,day string);

注意： hive表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时， hive会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错

当表是分区表时，比如 partitioned by (day string)， 则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123 这种格式，然后使用：msck repair table score; 修复表结构，成功之后即可看到数据已经全部加载到表当中去了

 加载数据到一个分区的表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score parti

tion (month='201806');

 加载数据到一个多分区的表中去

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 part

ition(year='2018',month='06',day='01');

• 查看分区

show partitions score;

• 添加一个分区

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

 同时添加多个分区

alter table score add partition(month='201804') partition(month = '201803');

注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

 删除分区

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

对分桶表操作

将数据按照指定的字段进行分成多个桶中去，就是按照分桶字段进行哈希划分到多个文件当中去分区就是分文件夹，分桶就是分文件

分桶优点：

1. 提高join查询效率
2. 提高抽样效率

• 开启hive的捅表功能

set hive.enforce.bucketing=true;

• 设置reduce的个数

set mapreduce.job.reduces=3;

 创建桶表

create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

桶表的数据加载：由于桶表的数据加载通过hdfsdfs-put文件或者通过loaddata 均不可以，只能通过insert overwrite 进行加载所以把文件加载到桶表中，需要先创建普通表，并通过insertoverwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

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

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

指定桶字段

修改表和删除表

 修改表名称

alter table old_table_name rename to new_table_name;

 增加/修改列信息

查询表结构

desc score5;

添加列

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

更新列

alter table score5 change column mysco mysconew int;

 删除表操作

drop table score5;

 清空表操作

truncate table score6;

说明：只能清空管理表，也就是内部表；清空外部表，会产生错误

注意：truncate 和 drop：

如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表结构恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复 truncate清空的表

所以 truncate 一定慎用，一旦清空将无力回天

向hive表中加载数据

 直接向分区表中插入数据

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

 通过load方式加载数据

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

score partition(month='201806');

 通过查询方式加载数据

insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score

from score1;

 查询语句中创建表并加载数据

create table score2 as select * from score1;

 在创建表是通过location指定加载数据的路径

create external table score6 (s_id string,c_id string,s_score int) row format delim

ited fields terminated by ',' location '/myscore';

 export导出与import 导入 hive表数据（内部表操作）

create table techer2 like techer; --依据已有表结构创建表

export table techer to

'/export/techer';

import table techer2 from '/export/techer';

hive表中数据导出

 insert导出

将查询的结果导出到本地

insert overwrite local directory '/export/servers/exporthive' select * from score;

将查询的结果格式化导出到本地

insert overwrite local directory '/export/servers/exporthive' row format delimited

fields terminated by '\t' collection items terminated by '#' select * from student;

将查询的结果导出到HDFS上(没有local)

insert overwrite directory '/export/servers/exporthive' row format delimited fields

terminated by '\t' collection items terminated by '#' select * from score;

 Hadoop命令导出到本地

dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;

 hive shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）

hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt

hive -f export.sh > /export/servers/exporthive/score.txt

 export导出到HDFS上

export table score to '/export/exporthive/score';

hive的DQL查询语法

单表查询

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、  sortby不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer 的输出有序，不保证全局有序。

3、  distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

4、  Clusterby(字段) 除了具有Distributeby的功能外，还会对该字段进行排序。因此，如果分桶和sort字段是同一个时，此时，clusterby=distributeby+sort by

• WHERE语句

select * from score where s_score < 60;

注意：

小于某个值是不包含null的，如上查询结果是把 s_score 为 null 的行剔除的

• GROUP BY 分组

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

分组后对数据进行筛选，使用having

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

注意：

如果使用 group by 分组，则 select 后面只能写分组的字段或者聚合函数 where和having区别：

1 having是在 group by 分完组之后再对数据进行筛选，所以having 要筛选的字段只能是分组字段或者聚合函数

2where 是从数据表中的字段直接进行的筛选的，所以不能跟在gruopby后面，也不能使用聚合函数

 join 连接

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

select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略

LEFT OUTER JOIN 左外连接：左边所有数据会被返回，右边符合条件的被返回

select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略

RIGHT OUTER JOIN 右外连接：右边所有数据会被返回，左边符合条件的被返回、

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

FULL OUTER JOIN 满外(全外)连接: 将会返回所有表中符合条件的所有记录。如果任一表的指定字段没

有符合条件的值的话，那么就使用NULL值替代。

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

注：1.hive2版本已经支持不等值连接，就是 joinon条件后面可以使用大于小于符号了;并且也支持 join on 条件后跟or (早前版本 on 后只支持 = 和 and，不支持 > < 和 or)

2.如hive执行引擎使用MapReduce，一个join就会启动一个job，一条sql语句中如有多个join，则会启动多个job 注意：表之间用逗号(,)连接和 inner join 是一样的

select * from table_a,table_b where table_a.id=table_b.id;

它们的执行效率没有区别，只是书写方式不同，用逗号是sql89标准，join 是sql

92标准。用逗号连接后面过滤条件用 where ，用 join 连接后面过滤条件是 on。

• order by 排序

全局排序，只会有一个reduce

ASC（ascend）: 升序（默认） DESC（descend）: 降序

SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_sco

re DESC;

注意：order by 是全局排序，所以最后只有一个reduce，也就是在一个节点执行，如果数据量太大，就会耗费较长时间

• sort by 局部排序

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

设置reduce个数

set mapreduce.job.reduces=3;

查看设置reduce个数

set mapreduce.job.reduces;

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

select * from score sort by s_score;

 

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

insert overwrite local directory '/export/servers/hivedatas/sort' select * from sco

re sort by s_score;

• distribute by 分区排序

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

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

set mapreduce.job.reduces=7;

通过distribute by

进行数据的分区

select * from score distribute by s_id sort by s_score;

注意：Hive要求 distribute by 语句要写在 sort by 语句之前

• 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;

Hive函数

聚合函数

hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函数

注意：聚合操作时要注意null值 count(*) 包含null值，统计所有行数 count(id) 不包含null值 min 求最小值是不包含null，除非所有值都是null avg 求平均值也是不包含null

 非空集合总体变量函数: var_pop

语法: var_pop(col)

返回值: double

说明: 统计结果集中col非空集合的总体变量（忽略null）

 非空集合样本变量函数: var_samp

语法: var_samp (col)

返回值: double

说明: 统计结果集中col非空集合的样本变量（忽略null）

 总体标准偏离函数: stddev_pop

语法: stddev_pop(col)

返回值: double

说明: 该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同

 中位数函数: percentile

语法: percentile(BIGINT col, p)

返回值: double

说明: 求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点

数类型

关系运算

支持：等值(=)、不等值(!= 或 <>)、小于(<)、小于等于(<=)、大于(>)、大于等于(>=)

空值判断(is null)、非空判断(is not null)

 LIKE比较: LIKE

语法: A LIKE B

操作类型: strings

描述: 如果字符串A或者字符串B为NULL

，

则返回NULL

；

如果字符串A符合表达式B 的正则语法

，

则为

TRUE；否则为FALSE。B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。

 JAVA的LIKE操作: RLIKE

语法: A RLIKE B

操作类型: strings

描述: 如果字符串

A

或者字符串

B

为NULL，则返回NULL；如果字符串

A

符合JAVA正则表达式

B

的正则

语法，则为TRUE；否则为FALSE。

 REGEXP操作: REGEXP

语法: A REGEXP B

操作类型: strings

描述: 功能与RLIKE相同

示例：select 1 from tableName where 'footbar' REGEXP '^f.*r$';

结果：1

数学运算

支持所有数值类型：加(+)、减(-)、乘(*)、除(/)、取余(%)、位与(&)、位或(|)、位异或(^)、位取反

(~)

逻辑运算

支持：逻辑与(and)、逻辑或(or)、逻辑非(not)

数值运算

 取整函数: round

语法: round(double a)

返回值: BIGINT

说明: 返回double类型的整数值部分（遵循四舍五入）

示例：select round(3.1415926) from tableName;

结果：3

 指定精度取整函数: round

语法: round(double a, int d)

返回值: DOUBLE

说明: 返回指定精度

d

的double类型

hive> select round(3.1415926,4) from tableName;

3.1416

 向下取整函数: floor

语法: floor(double a)

返回值: BIGINT

说明: 返回等于或者小于该double变量的最大的整数

hive> select floor(3.641) from tableName;

3

 向上取整函数: ceil

语法: ceil(double a)

返回值: BIGINT

说明: 返回等于或者大于该double变量的最小的整数

hive> select ceil(3.1415926) from tableName;

4

 取随机数函数: rand

语法: rand(),rand(int seed)

返回值: double

说明: 返回一个

0

到

1

范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列

hive> select rand() from tableName; -- 每次执行此语句得到的结果都不同

0.5577432776034763

hive> select rand(100) ; -- 只要指定种子，每次执行此语句得到的结果一样的

0.7220096548596434

 自然指数函数: exp

语法: exp(double a)

返回值: double

说明: 返回自然对数

e

的

a

次方

hive> select exp(2) ;

7.38905609893065

 以10为底对数函数: log10

语法: log10(double a)

返回值: double

说明: 返回以10为底的

a

的对数

hive> select log10(100) ;

2.0

此外还有：以2为底对数函数: log2()、对数函数: log()

 幂运算函数: pow

语法: pow(double a, double p)

返回值: double

说明: 返回

a

的

p

次幂

hive> select pow(2,4) ;

16.0

这篇关于认识Hive数据库的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！