地表最强系列之带你学Hive

什么是Hive?

hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。Hive的优点是学习成本低，可以通过类似SQL语句实现快速MapReduce统计，使MapReduce变得更加简单，而不必开发专门的MapReduce应用程序。hive是十分适合数据仓库的统计分析和Windows注册表文件。

Hive的优势和特点

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

Hive的发展里程碑和主流版本的社区

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 （1.2.1 本系列课实验重点版本 )
16年2月 – Hive 2.0.0 (添加 HPLSQL, LLAP)
16年6月 – Hive 2.1.0 （2.1.0 本系列课实验补充版本 )

Hive体系架构

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

默认Derby, 轻量级内嵌SQL数据库,Derby非常适合测试和演示,存储在.metastore_db目录中
实际生产一般存储在MySQL中,修改配置文件hive-site.xml
HCatalog
将Hive元数据共享给其他应用程序

Hive操作-命令窗口模式

有两种客户端工具：Beeline和Hive命令行（CLI）
有两种模式：命令行模式和交互模式
命令行模式

Hive操作-客户端交互模式

• 检查Hive服务是否已经正常启动
• 使用Hive交互方式（输入hive即可）
• 使用beeline
  需启动hiveserver2服务
  nohup hive --service metastore &（非必须）
  nohup hive --service hiveserver2 &
  输入beeline进入beeline交互模式
  !connect jdbc:hive2://hadoop101:10000

Hive数据类型 - 基本数据类型

ARRAY：存储的数据为相同类型
MAP：具有相同类型的键值对
STRUCT：封装了一组字段

Hive数据结构

Hive对数据库的操作

• 创建表–create database if not exists myhive;
• 查看表–show databases;
• 修改表–alter database test set owner user root;
• 删除表–drop database if exists myhivebook cascade;

Hive对数据表的操作

数据表分为内部表和外部表

• 内部表（管理表）
  HDFS中为所属数据库目录下的子文件夹
  数据完全由Hive管理，删除表(元数据)会删除数据
• 外部表（External Tables）
  数据保存在指定位置的HDFS路径中
  Hive不完全管理数据，删除表(元数据)不会删除数据
• 创建内部表的语句

create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/home/hadoop/hive/warehouse/student';
-- 查询表的类型
desc formatted student;

• 创建外部表的语句

CREATE EXTERNAL TABLE IF NOT EXISTS employee_external (
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>>
)
COMMENT 'This is an external table'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE
LOCATION '/home/hadoop/hive/warehouse/employee'; 

Hive的分隔符

• 默认分隔符
  字段：^A(\001)
  集合：^B(\002)
  映射：^C(\003)
• 自定义分隔符
  – 指定列分隔符语法
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘|’

Hive建表高阶语句

• CTAS – as select方式建表

CREATE TABLE ctas_employee as SELECT * FROM employee;

PS:CTAS不能创建partition, external, bucket table

• CTE (CTAS with Common Table Expression)

CREATE TABLE cte_employee 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;

• like

 CREATE TABLE employee_like LIKE employee;(只会有结构,没有数据)

创建临时表

• 临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法
  表只对当前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..

Hive对表的操作

• 删除表
  DROP TABLE IF EXISTS employee [With PERGE];
  TRUNCATE TABLE employee; – 清空表数据
• 修改表
  ALTER TABLE employee RENAME TO new_employee;
  ALTER TABLE c_employee SET TBLPROPERTIES (‘comment’=‘New name, comments’);
  ALTER TABLE employee_internal SET SERDEPROPERTIES (‘field.delim’ = ‘$’);
  ALTER TABLE c_employee SET FILEFORMAT RCFILE; – 修正表文件格式
  – 修改表的列操作
  ALTER TABLE employee_internal CHANGE old_name new_name STRING; – 修改列名
  ALTER TABLE c_employee ADD COLUMNS (work string); – 添加列
  ALTER TABLE c_employee REPLACE COLUMNS (name string); – 替换列

加载数据

• LOAD用于在Hive中移动数据
  LOAD DATA LOCAL INPATH ‘/home/dayongd/Downloads/employee.txt’
  OVERWRITE INTO TABLE employee;
  – 加LOCAL关键字，表示原始文件位于Linux本地，执行后为拷贝数据
  LOAD DATA LOCAL INPATH ‘/home/dayongd/Downloads/employee.txt’
  OVERWRITE INTO TABLE employee_partitioned PARTITION (year=2014, month=12);
  – 没有LOCAL关键字，表示文件位于HDFS文件系统中,执行后为直接移动数据
  LOAD DATA INPATH ‘/tmp/employee.txt’
  OVERWRITE INTO TABLE employee_partitioned PARTITION (year=2017, month=12);
• LOCAL：指定文件位于本地文件系统，执行后为拷贝数据
• OVERWRITE：表示覆盖表中现有数据

Hive分区（Partition）

• 分区主要用于提高性能
  分区列的值将表划分为一个个的文件夹
  查询时语法使用"分区"列和常规列类似
  查询时Hive会只从指定分区查询数据，提高查询效率
• 分区分为静态分区和动态分区

静态分区

• 创建分区表

create table dept_partition(
deptno int, 
dname string,
loc string )
partitioned by (month string)
row format delimited fields terminated by '\t';

• 静态分区操作

-- 添加分区
alter table dept_partition add partition(month='201906') ;
alter table dept_partition add partition(month='201905') partition(month='201904');
-- 删除分区
alter table dept_partition drop partition (month='201904');
alter table dept_partition drop partition (month='201905'), partition (month='201906');

• 分区表操作

-- 查看分区表有多少分区
show partitions dept_partition;
-- 加载数据到分区表中
load data local inpath '/opt/datas/dept.txt' into table dept_partition partition(month='201909');

• 创建多级分区

-- 创建二级分区表
create table dept_partition2(
deptno int, dname string, loc string)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';
-- 加载数据到二级分区表中
load data local inpath '/opt/datas/dept.txt' into table dept_partition2 partition(month='201909', day='13');

动态分区

• 使用动态分区需设定属性

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

• 动态分区建表语句和静态分区相同
• 动态分区插入数据

insert into dynamic_people
partition(year,month)
select id,name,age,start_date,year(start_date),month(start_date)
from people;
-- 查询数据
select * from dynamic_people where year = 2018;	

视图

视图:只存储后面的语句结果,不接受参数,不可用插入等操作

--创建视图
create view view_mu
as select * from userinfos where username like '%k%%';