一、Hive简介

什么是Hive

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。 其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

Hive on Spark 和 Hive on Tez

为什么使用Hive

面临的问题：

• 人员学习成本太高
• 项目周期要求太短 我只是需要一个简单的环境 MapReduce 如何搞定 复杂查询好难
• Join如何实现

为什么要使用Hive:

• 操作接口采用类SQL语法，提供快速开发的能力
• 避免了去写MapReduce，减少开发人员的学习成本
• 扩展功能很方便

二、Hive结构

Hive 的结构如图所示

主要分为以下几个部分：

• 用户接口–包括 CLI，Client，WUI。
• 元数据存储–通常是存储在关系数据库如 mysql, derby 中。可通过hive-default.xml修改
• 解释器–编译器、优化器、执行器。
• Hadoop–用 HDFS 进行存储，利用 MapReduce 进行计算。

三、Hive支持的格式

支持的基本类型有

创建一个复合类型的表

CREATE TABLE employees (
    name STRING,
    salary FLOAT,
    subordinates ARRAY<STRING>,
    deductions MAP<STRING, FLOAT>,
    address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
) PARTITIONED BY (country STRING, state STRING)

创建数据库
CREATE DATABASE [IF NOT EXISTS] userdb
删除数据库
DROP DATABASE [IF EXISTS ]userdb

四、表的操作

创建表

• 内部表与外部表

内部表：

CREATE TABLE [IF NOT EXISTS]  worker1
(
id INT,
name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,‘ --列分隔符
LINES TERMINATED BY ‘\n‘ --行分隔符
STORED AS ORC  --存储格式，支持TextFile、Parquet、RCFile和ORC File
LOCATION ‘/user/hive/test/worker1‘--数据存放路径

外部表：

CREATE EXTERNAL TABLE [IF NOT EXISTS] worker2
(
id INT,
name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,‘ --列分隔符
LINES TERMINATED BY ‘\n‘ --行分隔符
STORED AS ORC  --存储格式，支持TextFile、Parquet、RCFile和ORC File
LOCATION ‘/user/hive/test/worker2‘--数据存放路径

内部表与外部表相互转换

alter table worker1 set TBLPROPERTIES (‘EXTERNAL‘=‘TRUE‘); --内部表转外部表 
alter table worker1 set TBLPROPERTIES (‘EXTERNAL‘=‘FALSE‘); --外部表转内部表 

动态创建表
动态创建表通常会用在创建临时表，又不想使用缓存(当然，hive不支持缓存，SparkSQL中支持)的情况
不修改字段名与类型：

CREATE TABLE worker_temp
STORED AS  orc
AS 
SELECT * FROM worker2

删除表

DROP TABLE [IF EXISTS] table_name

如果是外部表，并且有location，则需要通过hdfs删除对应的目录才能完全删除数据

清空表

TRUNCATE TABLE table_name;

修改表

• 1.修改表名
  下面是查询重命名表，把 employee 修改为 emp
  ALTER TABLE employee RENAME TO emp;
  如：

hive> ALTER TABLE worker1 RENAME TO worker;
OK
Time taken: 1.156 seconds
hive> show tables;
OK
worker
worker2

修改字段名与字段类型

下表包含employee表的字段，它显示的字段要被更改（粗体）。

下面查询重命名使用上述数据的列名和列数据类型：

修改字段名
ALTER TABLE employee CHANGE name ename String
如：

hive> ALTER TABLE worker1 CHANGE name ename String
OK
Time taken: 0.398 seconds
hive> desc worker1;
OK
id                      int
ename                   string

修改字段类型
ALTER TABLE employee CHANGE salary salary Double
如：

hive> ALTER TABLE worker1 CHANGE ename name varchar(20);
OK
Time taken: 0.472 seconds
hive> desc worker1;
OK
id                      int                                         
name                    varchar(20)             

增加列

ALTER TABLE employee ADD COLUMNS (  
dept STRING COMMENT ‘Department name‘,--COMMENT增加注释
test String
)

如

hive> ALTER TABLE worker1 ADD COLUMNS (  
    > dept STRING 
    > );
OK
Time taken: 0.372 seconds
hive> desc worker1;
OK
id                      int                                         
name                    varchar(20)                                 
dept                    string                                      
Time taken: 0.309 seconds, Fetched: 3 row(s)

修改列的顺序

这个功能通常和增加列的功能一起用。
因为Hive增加列只能在最后的一个Column后面增加，假如我想在中间插入一列怎么办呢？
可以使用

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

由于hive文件并没有修改，只是相当于修改了字段名字而已，数据并没有相应的移动。

因此，此方法适用于已建表，后续会重新刷新数据的情况；或者空表。

删除列

目前貌似移除了这个功能

ALTER TABLE worker1 DROP COLUMN test1

替换表

ALTER TABLE worker1 REPLACE COLUMNS ( 
id INT, 
name String
);

上面这条sql执行失败，提示 SerDe may be incompatible

修改字段顺序

ALTER TABLE worker1 CHANGE COLUMN c c string AFTER a;

其他操作

查看表信息

desc worker1

查看建表信息

show create table worker1

查看格式信息

desc formatted worker1

改变表文件格式

ALTER TABLE worker1 SET FILEFORMAT textFile

查看函数

show functions

五、数据操作

1.插入数据

Hive不支持单句插入的语句，必须批量，所以不要指望能用insert into workers values (1,‘jack‘) 这样的语句插入数据。hive支持的插入数据的方式有两种：

• 从文件读取数据
• 从别的表读出数据插入(insert from select)

从文件读取数据

先建立一个叫 worker.csv的文件,内容为

1,jack
2,terry
3,michael

使用LOAD DATA 导入到Hive的表中

LOAD DATA LOCAL INPATH ‘/home/root/workers.csv‘ INTO TABLE workers1;

• 不要少了 LOCAL 关键字，LOAD DATA LOCAL INPATH 跟 LOAD DATA INPATH 的区别是一个是从你本地磁盘上找源文件，一个是从hdfs上找文件
• 如果加上OVERWRITE可以再导入之前先清空表，比如 LOAD DATA LOCAL INPATH ‘/home/root/workers.csv‘ OVERWRITE INTO TABLE workers1;

从其他结果集插入

INSERT [OVERWRITE] INTO TABLE works2
select * from test

如果有分区（稍后我们会讲到）

INSERT INTO [OVERWRITE] TABLE table_name1 PARTITION(partition_name1 data, partition_name2 data, … )
SELECT select_statement FROM from_statement;

2.分区和分桶

分区表是用来加速查询的，比如你的数据非常多，但是你的应用场景是基于这些数据做日报表，那你就可以根据日进行分区，当你要做2014-05-05的报表的时候只需要加载2014-05-05这一天的数据就行了。

创建分区表

DROP TABLE IF EXISTS employee;
CREATE EXTERNAL TABLE  IF NOT EXISTS  employee
(
id INT,
name STRING,
dept  String,
year int
)
PARTITIONED BY(p_year int)--指定分区,可以包含多级分区
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,‘ --列分隔符
LINES TERMINATED BY ‘\n‘ --行分隔符
STORED AS textFile  --存储格式，支持TextFile、Parquet、RCFile和ORC File
LOCATION ‘/user/hive/test/employeedata‘--数据存放路径

查看分区

• 查看所有分区
  SHOW PARTITIONS employee;
• 查看分区的所有信息
  DESC FORMATTED table_name partition (month = ‘2015-01’ , day = ‘2015-01-25′)

插入分区数据

INSERT INTO TABLE table_name PARTITION  PARTITION(p_column = ...)
SELECT * FROM other_table_name;

INSERT OVERWRITE  TABLE table_name PARTITION  PARTITION(p_column = ...)
SELECT * FROM other_table_name;

添加分区

ALTER TABLE table_name ADD [IF NOT EXISTS ] PARTITION(p_column = ...) LOCATION "/user/root/test"

如：

ALTER TABLE employee ADD IF NOT EXISTS  PARTITION(p_year = 2012) LOCATION "/user/hive/test/employeedata/2012";
ALTER TABLE employee ADD IF NOT EXISTS  PARTITION(p_year = 2013) LOCATION "/user/hive/test/employeedata/2013";

重命名分区

ALTER TABLE table_name PARTITION (p_date="2015-12-12") RENAME TO PARTITION (p_date="2015-12-13");

删除分区

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec, PARTITION partition_spec,...;

分区使用

在hive中可以直接使用分区进行查询和计算操作，由于加入了分区，使得hive处理数据时可以读取指定分区的内容，而不用遍历全表，因此极大的加快了查询速度
如：

select * from employee where p_year = 2013;
结果：
+--------------+----------------+----------------+----------------+------------------+--+
| employee.id  | employee.name  | employee.dept  | employee.year  | employee.p_year  |
+--------------+----------------+----------------+----------------+------------------+--+
| 3            | kaleel         | SC             | 2013           | 2013             |
| 4            | Prasanth       | SC             | 2013           | 2013             |
+--------------+----------------+----------------+----------------+------------------+--+

分桶

桶表是根据某个字段的hash值，来将数据扔到不同的“桶”里面。外国人有个习惯，就是分类东西的时候摆几个桶，上面贴不同的标签，所以他们取名的时候把这种表形象的取名为桶表。桶表表专门用于采样分析

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

CREATE TABLE b_student(id INT, name STRING)
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(id) --按照哪个字段分桶
SORTED BY(name) 
INTO 4 BUCKETS --分成几个桶 
row format delimited 
fields TERMINATED BY ‘,‘;

六、复合类型

Array

创建

Array：
create table login_array(
ip string,
uid array<bigint>
)
partitioned by (dt string)
row format delemited fields terminated by ‘,‘--列分隔符
collection items terminated by ‘|‘ --数组分隔符
stored as textfile;

使用

select ip uid[0] from login_array where dt=‘20130101‘;--使用数组下标访问
select ip,size(uid) from login_array where dt=‘20130101‘;--查看数组长度
select * from login_array where array_contains(uid,‘31050007‘);--查找数组

Map

创建

Map:
create table map_test_raw(
ip string,
request Map<string,string>
)
row format delemited fields terminated by ‘,‘ --列分隔符
collection items terminated by ‘|‘ --Map集合分隔符
map keys terminated by ‘=‘ --Key与value分隔符
stored as textfile;

使用

select request[‘src‘] from map_test_raw;

七、创建视图和索引以及数据缓存

视图

根据用户的需求创建视图。可以将任何结果集数据保存为一个视图。视图在Hive的用法和SQL视图用法相同。它是一个标准的RDBMS概念。我们可以在视图上执行所有DML操作。

创建视图

CREATE VIEW worker_view AS
SELECT * FROM worker2
WHERE id>2;

删除视图

DROP VIEW worker_view

索引

索引也不过是一个表上的一个特定列的指针。创建索引意味着创建一个表上的一个特定列的指针。

创建索引

CREATE INDEX worker_index ON TABLE worker2(id)
AS ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler‘
WITH DEFERRED REBUILD

查看索引

SHOW INDEX ON worker2;

删除索引

DROP INDEX worker_index ON worker2;

缓存

除了视图以外，还可以使用数据缓存的方式来保存某一结果集，只不过视图是将结果集保存在hive中，使用的时候仍旧运行了一次查询。而缓存则是将数据保存在了内存中，通常适用于需要多次使用的数据查询；但是，cache table的语法只能在Spark SQl中使用

创建缓存表

整表缓存
sql=CACHE TABLE cache_worker AS SELECT * FROM worker2
DataFrame=hiveCtx.cacheTable("worker2")或
hiveCtx.sql("SELECT * FROM worker2").cache()[或.persist()]
缓存部分数据
sql=CACHE TABLE cache_worker AS SELECT * FROM worker2 where id>1
DataFrame=hiveCtx.sql("SELECT * FROM worker2 where id>1").cache()[或.persist()]
该条语句对应了

删除缓存表

sql=DROP TABLE cache_worker
DataFrame=hiveCtx.uncacheTable(cache_worker)或dataframe.unpersist()

参考链接Hive教程

Hive基础知识