HIVE最全面入门指南

一、Hive简介

Facebook为了解决海量日志数据的分析而开发了Hive，后来开源给了Apache软件基金会。

官网定义：

The Apache Hive ™ data warehouse software facilitates reading, writing, and managing large datasets residing in distributed storage using SQL.

Hive是一种用类SQL语句来协助读写、管理那些存储在分布式存储系统上大数据集的数据仓库软件。

1.Hive的几个特点

1. Hive最大的特点是通过类SQL来分析大数据，而避免了写MapReduce程序来分析数据，这样使得分析数据更容易。
2. 数据是存储在HDFS上的，Hive本身并不提供数据的存储功能
3. Hive是将数据映射成数据库和一张张的表，库和表的元数据信息一般存在关系型数据库上（比如MySQL）。
4. 数据存储方面：它能够存储很大的数据集，并且对数据完整性、格式要求并不严格。
5. 数据处理方面：因为Hive语句最终会生成MapReduce任务去计算，所以不适用于实时计算的场景，它适用于离线分析。

 

二、Hive架构

1.Hive的核心

Hive的核心是驱动引擎，驱动引擎由四部分组成：

 

1. 解释器：解释器的作用是将HiveSQL语句转换为语法树（AST）。
2. 编译器：编译器是将语法树编译为逻辑执行计划。
3. 优化器：优化器是对逻辑执行计划进行优化。
4. 执行器：执行器是调用底层的运行框架执行逻辑执行计划。

2.Hive的底层存储

Hive的数据是存储在HDFS上的。Hive中的库和表可以看作是对HDFS上数据做的一个映射。所以Hive必须是运行在一个Hadoop集群上的。

3.Hive语句的执行过程

Hive中的执行器，是将最终要执行的MapReduce程序放到YARN上以一系列Job的方式去执行。

4.Hive的元数据存储

Hive的元数据是一般是存储在MySQL这种关系型数据库上的，Hive和MySQL之间通过MetaStore服务交互。

 

元数据项	说明
Owner	库、表的所属者
LastAccessTime	最后修改时间
Table Type	表类型（内部表、外部表）
CreateTime	创建时间
Location	存储位置
	表的字段信息

 

5.Hive客户端

Hive有很多种客户端。

 

1. cli命令行客户端：采用交互窗口，用hive命令行和Hive进行通信。
2. HiveServer2客户端：用Thrift协议进行通信，Thrift是不同语言之间的转换器，是连接不同语言程序间的协议，通过JDBC或者ODBC去访问Hive。
3. HWI客户端：hive自带的一个客户端，但是比较粗糙，一般不用。
4. HUE客户端：通过Web页面来和Hive进行交互，使用的比较多。

 

三、基本数据类型

Hive支持关系型数据中大多数基本数据类型，同时Hive中也有特有的三种复杂类型。

下面的表列出了Hive中的常用基本数据类型：

 

数据类型	长度	备注
Tinyint	1字节的有符号整数	-128~127
SmallInt	1个字节的有符号整数	-32768~32767
Int	4个字节的有符号整数	-2147483648 ~ 2147483647
BigInt	8个字节的有符号整数
Boolean	布尔类型，true或者false	true、false
Float	单精度浮点数
Double	双精度浮点数
String	字符串
TimeStamp	整数	支持Unix timestamp，可以达到纳秒精度
Binary	字节数组
Date	日期	0000-01-01 ~ 9999-12-31，常用String代替
---	---	---

四、DDL语法

1.创建数据库

创建一个数据库会在HDFS上创建一个目录，Hive里数据库的概念类似于程序中的命名空间，用数据库来组织表，在大量Hive的情况下，用数据库来分开可以避免表名冲突。Hive默认的数据库是default。

 

2.创建数据库例子

hive> create database if not exists user_db;

3.查看数据库定义

 

Describe 命令来查看数据库定义，包括：数据库名称、数据库在HDFS目录、HDFS用户名称。

 

hive> describe database user_db;

OK

user_db    hdfs://bigdata-51cdh.chybinmy.com:8020/user/hive/warehouse/user_db.db     hadoop    USER

 

user_db是数据库名称。

 

hdfs://bigdata-51cdh.chybinmy.com:8020/user/hive/warehouse/user_db.db 是user_db库对应的存储数据的HDFS上的根目录。

 

4.查看数据库列表

hive> show databases;   

OK      

user_db

Default

 

5.删除数据库

删除数据库时，如果库中存在数据表，是不能删除的，要先删除所有表，再删除数据库。添加上cascade后，就可以先自动删除所有表后，再删除数据库。（友情提示：慎用啊！）删除数据库后，HDFS上数据库对应的目录就被删除掉了。

 

hive> drop database if exists testdb cascade;

 

6.切换当前数据库

hive> use user_db;

 

7.创建普通表

hive> create table if not exists userinfo  

    > (

    >   userid int,

    >   username string,

    >   cityid int,

    >   createtime date    

    > )

    > row format delimited fields terminated by '\t'

    > stored as textfile;

    OK

    Time taken: 2.133 seconds

 

以上例子是创建表的一种方式，如果表不存在，就创建表userinfo。row format delimited fields terminated by '\t' 是指定列之间的分隔符；stored as textfile是指定文件存储格式为textfile。

 

8.创建表一般有几种方式

1. create table 方式：以上例子中的方式。
2. create table as select 方式：根据查询的结果自动创建表，并将查询结果数据插入新建的表中。
3. create table like tablename1 方式：是克隆表，只复制tablename1表的结构。复制表和克隆表会在下面的Hive数据管理部分详细讲解。

 

9.创建外部表

外部表是没有被hive完全控制的表，当表删除后，数据不会被删除。

 

hive> create external table iislog_ext (

    >  ip string,

    >  logtime string    

    > )

    > ;

 

10.创建分区表

Hive查询一般是扫描整个目录，但是有时候我们关心的数据只是集中在某一部分数据上，比如我们一个Hive查询，往往是只是查询某一天的数据，这样的情况下，可以使用分区表来优化，一天是一个分区，查询时候，Hive只扫描指定天分区的数据。

普通表和分区表的区别在于：一个Hive表在HDFS上是有一个对应的目录来存储数据，普通表的数据直接存储在这个目录下，而分区表数据存储时，是再划分子目录来存储的。一个分区一个子目录。主要作用是来优化查询性能。

 

--创建经销商操作日志表

 

create table user_action_log

(

companyId INT comment   '公司ID',

userid INT comment   '销售ID',

originalstring STRING comment   'url',

host STRING comment   'host',

absolutepath STRING comment   '绝对路径',

query STRING comment   '参数串',

refurl STRING comment   '来源url',

clientip STRING comment   '客户端Ip',

cookiemd5 STRING comment   'cookiemd5',

timestamp STRING comment   '访问时间戳'

)

partitioned by (dt string)

row format delimited fields terminated by ','

stored as textfile;

 

这个例子中，这个日志表以dt字段分区，dt是个虚拟的字段，dt下并不存储数据，而是用来分区的，实际数据存储时，dt字段值相同的数据存入同一个子目录中，插入数据或者导入数据时，同一天的数据dt字段赋值一样，这样就实现了数据按dt日期分区存储。

当Hive查询数据时，如果指定了dt筛选条件，那么只需要到对应的分区下去检索数据即可，大大提高了效率。所以对于分区表查询时，尽量添加上分区字段的筛选条件。

 

11.创建桶表

桶表也是一种用于优化查询而设计的表类型。创建通表时，指定桶的个数、分桶的依据字段，hive就可以自动将数据分桶存储。查询时只需要遍历一个桶里的数据，或者遍历部分桶，这样就提高了查询效率。举例：

 

------创建订单表

create table user_leads

(

leads_id string,

user_id string,

user_id string,

user_phone string,

user_name string,

create_time string

)

clustered by (user_id) sorted by(leads_id) into 10 buckets

row format delimited fields terminated by '\t'

stored as textfile;

 

对这个例子的说明：

 

1. clustered by是指根据user_id的值进行哈希后模除分桶个数，根据得到的结果，确定这行数据分入哪个桶中，这样的分法，可以确保相同user_id的数据放入同一个桶中。而经销商的订单数据，大部分是根据user_id进行查询的。这样大部分情况下是只需要查询一个桶中的数据就可以了。
2. sorted by 是指定桶中的数据以哪个字段进行排序，排序的好处是，在join操作时能获得很高的效率。
3. into 10 buckets是指定一共分10个桶。
4. 在HDFS上存储时，一个桶存入一个文件中，这样根据user_id进行查询时，可以快速确定数据存在于哪个桶中，而只遍历一个桶可以提供查询效率。

 

12.分桶表读写过程

查看有哪些表

--查询库中表

show tables;

Show TABLES '*info';  --可以用正则表达式筛选要列出的表

 

查看表定义

查看简单定义：

 

describe userinfo;

 

查看表详细信息：

 

describe formatted userinfo;

 

执行结果如下所示：

备注	col_name	data_type	comment
列信息	# col_name	data_type	comment
	NULL	NULL
userid	int
username	string
cityid	int
createtime	date
		NULL	NULL
	# Detailed Table Information	NULL	NULL
所在库	Database:	user_db	NULL
所属HUE用户	Owner:	admin	NULL
表创建时间	CreateTime:	Tue Aug 16 06:05:14 PDT 2016	NULL
最后访问时间	LastAccessTime:	UNKNOWN	NULL
	Protect Mode:	None	NULL
	Retention:	0	NULL
表数据文件在HDFS上路径	Location:	hdfs://bigdata-51cdh.chybinmy.com:8020/user/hive/warehouse/user_db.db/userinfo	NULL
表类型(内部表或者外部表)	Table Type:	MANAGED_TABLE	NULL
表分区信息	Table Parameters:	NULL	NULL
		transient_lastDdlTime	1471352714
		NULL	NULL
	# Storage Information	NULL	NULL
序列化反序列化类	SerDe Library:	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	NULL
mapreduce中的输入格式	InputFormat:	org.apache.hadoop.mapred.TextInputFormat	NULL
mapreduce中的输出格式	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
---	---	---	---

 

13.修改表

对表的修改操作有：修改表名、添加字段、修改字段。

修改表名：

--将表名从userinfo改为user_info

alter table userinfo rename to user_info;

添加字段：

---在user_info表添加一个字段provinceid，int 类型

alter table user_info add columns (provinceid int );

修改字段：

alter table user_info replace columns (userid int,username string,cityid int,joindate date,provinceid int);

 

修改字段，只是修改了Hive表的元数据信息（元数据信息一般是存储在MySql中），并不对存在于HDFS中的表数据做修改。

并不是所有的Hive表都可以修改字段，只有使用了native SerDe (序列化反序列化类型)的表才能修改字段

删除表

--如果表存在，就删除。

drop table if exists user_info;

 

五、DML语法

1.向Hive中加载数据

1.加载到普通表

 

可以将本地文本文件内容批量加载到Hive表中，要求文本文件中的格式和Hive表的定义一致，包括：字段个数、字段顺序、列分隔符都要一致。

 

这里的user_info表的表定义是以\t作为列分隔符，所以准备好数据后，将文本文件拷贝到

 

hive客户端机器上后，执行加载命令。

load data local inpath '/home/hadoop/userinfodata.txt' overwrite into table user_info;

 

1. local关键字表示源数据文件在本地，源文件可以在HDFS上，如果在HDFS上，则去掉local，inpath后面的路径是类似”hdfs://namenode:9000/user/datapath”这样的HDFS上文件的路径。
2. overwrite关键字表示如果hive表中存在数据，就会覆盖掉原有的数据。如果省略overwrite，则默认是追加数据。

 

加载完成数据后，在HDFS上就会看到加载的数据文件。

 

2.加载到分区表

load data local inpath '/home/hadoop/actionlog.txt' overwrite into table user_action_log

PARTITION (dt='2017-05-26');

 

partition 是指定这批数据放入分区2017-05-26中。

 

3.加载到分桶表

------先创建普通临时表

create table user_leads_tmp

(

leads_id string,

user_id string,

user_id string,

user_phone string,

user_name string,

create_time string

)

row format delimited fields terminated by ','

stored as textfile;

 

------数据载入临时表

 

load data local inpath '/home/hadoop/lead.txt' overwrite into table user_leads_tmp;

 

------导入分桶表

 

set hive.enforce.bucketing = true;

insert overwrite table user_leads select * from  user_leads_tmp;

 

set hive.enforce.bucketing = true; 这个配置非常关键，为true就是设置为启用分桶。

导出数据

--导出数据，是将hive表中的数据导出到本地文件中。

 

insert overwrite local directory '/home/hadoop/user_info.bak2016-08-22 '

select * from user_info;

 

去掉local关键字，也可以导出到HDFS上。

 

2.插入数据

insert select 语句

上一节分桶表数据导入，用到从user_leads_tmp表向user_leads表中导入数据，用到了insert数据。

 

insert overwrite table user_leads select * from  user_leads_tmp;

 

这里是将查询结果导入到表中，overwrite关键字是覆盖目标表中的原来数据。如果缺省，就是追加数据。

 

如果是插入数据的表是分区表，那么就如下所示：

 

insert overwrite table user_leads PARTITION (dt='2017-05-26')

select * from  user_leads_tmp;

 

3.一次遍历多次插入

from user_action_log

insert overwrite table log1 select companyid,originalstring  where companyid='100006'

insert overwrite table log2 select companyid,originalstring  where companyid='10002'

 

每次hive查询，都会将数据集整个遍历一遍。当查询结果会插入多个表中时，可以采用以上语法，将一次遍历写入多个表，以达到提高效率的目的。

4.复制表

复制表是将源表的结构和数据复制并创建为一个新表，复制过程中，可以对数据进行筛选，列可以进行删减。

 

create table user_leads_bak

row format delimited fields terminated by '\t'

stored as textfile

as

select leads_id,user_id,'2016-08-22' as bakdate

from user_leads

where create_time<'2016-08-22';

 

上面这个例子是对user_leads表进行复制备份，复制时筛选了2016-08-22以前的数据，减少几个列，并添加了一个bakdate列。

 

5.克隆表

克隆表时会克隆源表的所有元数据信息，但是不会复制源表的数据。

 

--克隆表user_leads，创建新表user_leads_like

 

create table user_leads_like like  user_leads;

 

6.备份表

备份是将表的元数据和数据都导出到HDFS上。

 

export table user_action_log partition (dt='2016-08-19')

to '/user/hive/action_log.export'

 

这个例子是将user_action_log表中的一个分区，备份到HDFS上，to后面的路径是HDFS上的路径。

 

7.还原表

将备份在HDFS上的文件，还原到user_action_log_like表中。

 

import table user_action_log_like from '/user/hive/action_log.export';

六、HQL语法

1.Select 查询

指定列表

 

select * from user_leads;

 

select leads_id,user_id,create_time from user_leads;

select e.leads_id from user_leads e;

 

函数列

select companyid,upper(host),UUID(32) from user_action_log;

 

可以使用hive自带的函数，也可以是使用用户自定义函数。

上面这个例子upper()就是hive自带函数，UUID()就是用户自定义函数。

关于函数详细介绍，可以参考后面的章节。

 

算数运算列

select companyid,userid, (companyid + userid) as sumint from user_action_log;

 

可以进行各种算数运算，运算结果做为结果列。