Apache Hive基础以及Hive基础命令

一、什么是 Hive

hive是基于Hadoop构建的一套数据仓库分析系统，是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制；hive数据仓库工具能将结构化的数据文件映射为一张数据库表，提供类sql的查询语言HQL(Hive Query Language)能将 SQL 语句转变成MapReduce任务来执行。
hive官网：hive.apache.org

Hive发展历史及版本：

• 07年8月 – 始于Facebook
• 13年5月 – 0.11 Stinger Phase 1 ORC HiveServer2
• 13年10月 – 0.12.0 Stinger Phase 2 - ORC improvement
• 14年4月 – Hive 0.13.0 as Stinger Phase 3
• 14年11月 – Hive 0.14.0
• 15年2月 – Hive 1.0.0
• 15年5月 – Hive 1.2.0
• 16年2月 – Hive 2.0.0 (添加 HPLSQL, LLAP)
• 16年6月 – Hive 2.1.0

Hive的优势和特点：

• 提供了一个简单的优化模型
• HQL类SQL语法，简化MR开发
• 支持在不同的计算框架上运行
• 支持在HDFS和HBase上临时查询数据
• 支持用户自定义函数、格式
• 成熟的JDBC和ODBC驱动程序，用于ETL和BI
• 稳定可靠（真实生产环境）的批处理
• 有庞大活跃的社区

Hive体系架构

二、Hive数据类型

原始数据类型

类型	示例	类型	示例
tinyint	10Y	smallint	10S
int	10	bigint	100L
float	1.342	double	1.234
decimal	3.14	binary	1010
boolean	true	string	‘Book’ or “Book”
char	‘YES’ or “YES”	varchar	‘Book’ or “Book”
date	‘2013-01-31’	timestamp	‘2013-01-31 00:13:00.345’

复杂数据类型

类型	数据格式	定义	示例
array	[‘Apple’,‘Orange’,‘Mongo’]	array	a[0] = ‘Apple’
map	{‘A’:‘Apple’,‘O’:‘Orange’}	map<string,string>	b[‘A’] = ‘Apple’
struct	{‘Apple’, 2}	struct<fruit:string,weight:int>	c.weight = 2

类型	说明
array	存储的数据为相同类型
map	具有相同类型的键值对
struct	封装了一组字段

Hive元数据结构

数据结构	描述	逻辑关系	物理存储（HDFS)
Database	数据库	表的集合	文件夹
Table	表	行数据的集合	文件夹
Partition	分区	用于分割数据	文件夹
Buckets	分桶	用于分布数据	文件
Row	行	行记录	文件中的行
Columns	列	列记录	每行中指定的位置
Views	视图	逻辑概念，可跨越多张表	不存储数据
Index	索引	记录统计数据信息	文件夹

Hive元数据管理

• 记录数据仓库中模型的定义、各层级间的映射关系
• 存储在关系数据库中

三、Hive基础命令

1、连接方式

hive
hive -h hostname -p port

2、库级操作（同mysql）

（1）新建数据库

create database DataBaseNmae;

（2）删除数据库

drop database DataBaseNmae;

3、数据表分类

（1）内部表（管理表）

• HDFS中为所属数据库目录下的子文件夹
• 数据完全由Hive管理，删除表(元数据)会删除数据

（2）外部表（External Tables）

• 数据保存在指定位置的HDFS路径中
• Hive不完全管理数据，删除表(元数据)不会删除数据

create external table tmp_table_name (c1 string);

4、hive 建表语句

（1）hive 建表语句

create table c(
id int,
name string,
info struct<gender:string,age:int>,
hunji array<string>,
zongmen map<string,int>)
comment 'This is an external table'				#表注释，可选
row format delimited
fields terminated by '|'						#字段分隔
collection items terminated by ','				#分隔集合和映射
map keys terminated by ':'						#属性值分隔
lines terminated by '\n'						#行分隔
stored as textfile								#文件存储格式()
location '/user/root/employee';					#文件存储路径

（2）CTAS – as select方式建表

• 完全复制表结构及其中数据
• 该方法不能创建partition, external, bucket table;

create table newTableName as select * from ordTableName;

（3）Like方式建表

• 复制表结构不复制表中数据

create table newTableName like ordTableName;

（4）CTE建表（子查询）

create table tableName as
with
r1 as  (select name from r2 where name = 'Michael'),
r2 as (select name from employee where sex_age.sex= 'Male'),
r3 as (select name from employee where sex_age.sex= 'Female')
select * from r1 union all select * from r3;

（5）临时表
临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法；

• 表只对当前 session 有效，session 退出后自动删除
• 表空间位于/tmp/hive-<user_name>(安全考虑)
• 如果创建的临时表表名已存在，实际用的是临时表

create temporary table tmp_table_name1 (c1 string);
create temporary table tmp_table_name2 as..
create temporary table tmp_table_name3 like..

5、插入数据、读取文件

（1）语句插入数据（速度奇慢无比）

insert into student values(1,'张三','男',25);

（2）读取文件插入数据

• 数据库以及表在hdfs文件系统中以文件夹的方式存储；
• 将文件上传到hdfs上相应的表的文件夹中即可(注意文件格式与表结构对应)

6、删除表

（1）删除表，[with perge]可选，选上直接删除表，否则会放到 .Trash目录

drop table if exists tableName [with perge];

（2）清空表数据

truncate table tableName;

7、修改表

（1）修改表名

alter table oldTableName rename to newTableName;

（2）修正表文件格式

alter table tableName set fileformat rcfile;	#rcfile文件格式

（3）修改分隔符

alter table tableName set serdeproperties ('field.delim' = '$');

（4）修改表列名

alter table tableName change old_name new_name string; 

（5）添加列

alter table tableName add columns (LieName string);

（6）替换列

alter table tableName replace columns (name string); 

四、Hive分区

• 分区主要用于提高性能
• 分区列的值将表划分为segments(文件夹)
• 查询时使用"分区"列和常规列类似
• 查询时Hive自动过滤掉不用于提高性能的分区
• 分为静态分区和动态分区

定义分区

一个分区相当于一个列；
注意分区语句位置，必须在表元素列表后，分隔符语句前；

create table tableName(
id int,
name string,
info struct<gender:string,age:int>,
hunji array<string>,
zongmen map<string,int>)
partitioned by (name1 string,name2 int)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

静态分区操作

1、添加分区

alter table tableName add partition (name1='xxx',name2=xx);

2、删除分区

alter table tableName drop partition (name1='xxx',name2=xx);

动态分区

1、使用动态分区需设定属性

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.modenonstrict;

2、动态分区设置方法

insert into table employee_partitioned partition(year, month)
select name,array('Toronto'),
named_struct("sex","male","age",30),
map("python",90),
map("r&d", array('developer')),
year(start_date),month(start_date)
from employee_hr;

五、Hive 分桶(Buckets)

1、分桶对应于HDFS中的文件

• 更高的查询处理效率
• 使抽样（sampling）更高效
• 根据“桶列”的哈希函数将数据进行分桶

2、分桶只有动态分桶

set hive.enforce.bucketing = true;

3、定义分桶

• 分桶的列是表中已有的列
• 分桶数最好是2的n次方

clustered by (LieName) into 2 buckets;

4、分桶抽样（Sampling）

（1）随机抽样基于整行数据

select * from tableName tablesample(bucket 3 out of 32 on rand()) s;

（2）随机抽样基于指定列（使用分桶列更高效）

select * from tableName tablesample(bucket 3 out of 32 on LieName) s;

（3）随机抽样基于block size

select * from tableName tablesample(10 percent) s;			#10%
select * from tableName tablesample(1M) s;					#1M
select * from tableName tablesample(10 rows) s;				#10行

六、视图

1、视图概述（同mysql）

• 通过隐藏子查询、连接和函数来简化查询的逻辑结构
• 虚拟表，从真实表中选取数据
• 只保存定义，不存储数据
• 如果删除或更改基础表，则查询视图将失败
• 视图是只读的，不能插入或装载数据

2、视图操作命令

（1）创建视图

create view v_name as select...;

（2）查找视图

show views;			#hive 2.2.0之后引入
show create table v_name;		#查看视图定义

（3）删除视图

drop v_name;

（4）更改视图

alter view v_name as select...;

3、侧视图

• 常与表生成函数结合使用，将函数的输入和输出连接
• outer关键字：即使output为空也会生成结果

select name,work_place,loc from employee lateral view outer explode(split(null,',')) a as loc;

• 支持多层级

select name,wps,skill,score from employee 
lateral view explode(work_place) work_place_single as wps
lateral view explode(skills_score) sks as skill,score;

• 通常用于规范化行或解析JSON