1. 简介

Hive是一款基于Hadoop的数据仓库工具，设计初衷就是使不擅长编程的数据分析人员也能参与到大数据中来，常用功能是数据提取转化加载（ETL）和数据分析。其使用存储在HDFS中的数据，可以将结构化的数据文件映射为一张数据库表，并将SQL语句转换为MapReduce任务（目前不止MapReduce，还包括Spark和TEZ）执行查询。

2. 架构

Hive架构如图11所示：由客户端提供查询语句，提交给Hive，Hive再交给Driver处理（Compiler先编译，编译时要从元数据库中获取元数据信息，生成逻辑计划；生成物理计划；由优化器进行优化；执行器提交任务，同时还需要提取元数据信息关联具体的数据，这些元数据信息送到HDFS的数据节点），并将结果返回或写入HDFS。

image.png

3. 特点

3.1 可扩展性

Hive 可以自由的扩展集群的规模，Hive 本身只是将 HSQL 解析为 MapReduce 任务，数据存储在 HDFS，所以其扩展性取决与 Hadoop 的扩展性 。

3.2 支持自定义函数

Hive 支持自定义函数，用户可以根据自己的需求来实现自己的函数。

3.3 高容错性

可以保障即使有节点出现问题，SQL 语句仍可完成执行。由于 Hive 本身只是将 HSQL 解析为 MapReduce 任务，其容错性取决于底层的文件系统HDFS，和计算引擎 MapReduce，而HDFS 和 MapReduce 定位是运行在通用硬件上，因此容错性可以保障。

4. 语法

4.1 DDL

4.1.1 数据库操作

4.1.1.1 创建

create database if not exists hive;
# 同 mysql

4.1.1.2 查询数据库列表

show databases;
# 同 mysql

4.1.1.3 删除数据库

drop database if exists hive;
# 同 mysql
drop database if exists hive cascade;
# 删除带表的数据库

4.1.1.4 描述数据库

desc database hive;
# 同 mysql

4.1.1.5 查看当前使用的数据库

select current_database();

4.1.1.6 切换数据库

use hive;
# 同 mysql

4.1.2 表操作

4.1.2.1 创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

　　[(col_name data_type [COMMENT col_comment], ...)]

　　[COMMENT table_comment]

　　[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

　　[CLUSTERED BY (col_name, col_name, ...)

　　　　[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

　　[ROW FORMAT row_format]

　　[STORED AS file_format]

　　[LOCATION hdfs_path]

• EXTERNAL:指定创建外部表。
• COMMENT：可以为表和字段添加描述。
• PARTITIONED BY：指定分区字段（支持多属性分区）。
• CLUSTERED BY: 指定分桶字段，分桶和分区的区别：分桶字段在文件中中，分区字段不在文件中。
• SORTED BY：指定桶内排序字段。
• ROW FORMAT：指定分割符。
• STORED AS ：文件类型。
• LOCATION：表路径。

4.1.2.1.1 创建内部表

• 创建表

create table student(id int, name string, sex string, age int,department string) row format delimited fields terminated by ",";
* 导入数据

load data INPath '/Users/dreamaker/Downloads/apache-hive-2.3.4-bin/data/tableA' into table tableA;

• 查询数据

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a left join tableB b on a.dept=b.id and a.grade>80;

4.1.2.1.2 创建外部表

在创建内部表语法基础上，需使用 EXTERNAL 关键字，并指定 location 。

4.1.2.1.3 创建分区表

• 在内部表（或外部表）的基础上，指定 PARTITIONED BY 属性
• 使用 alter 命令添加分区。

alter table tablename add partition(key=value);

• 在 load data 目录的基础上，添加 partition(key=value)命令。

load data local inpath "/home/hadoop/student.txt" into table student_ptn partition(city="beijing");

• 动态分区
  创建分区表

create table student_ptn_age(id int,name string,sex string,department string) partitioned by (age int);

在插入数据时指定按照查询结果中的某个属性分区

insert overwrite table student_ptn_age partition(age) select id,name,sex,department，age from student_ptn;

4.1.2.1.4 创建分桶表

在普通表的基础上，使用CLUSTERED BY (col_name, col_name, ...)[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS命令。

4.1.2.1.5 使用 CTAS 创建表

create table student_ctas as select * from student where id < 95012;

4.1.2.1.6 复制表结构

create table stu like student;

4.1.2.2 查看表

• 查看表基本信息

desc tablename;

• 查看表详细信息

desc extended tablename;

• 格式化表详细信息

desc formatted tablename;

• 查看表分区信息

show partitions tablename;

• 查看表的详细建表语句

show create table tablename;

4.1.2.3 列举表

• 列举当前数据库中的所有表

show tables;

• 列举指定数据库中的所有表

show tables in hive;

• 模式匹配当前数据库中的所有表

show tables like 'stu*';

4.1.2.4 修改表

4.1.2.4.1 修改表名

alter table oldname rename to newname;

4.1.2.4.2 添加列

alter table tablename add colums (filed,datatype);

4.1.2.4.3 修改一列的定义

alter table tablename change old_columname  new_columname datatype;

4.1.2.4.4 替换所有字段

alter table tablename replaces columns (columname,datatype...);

4.1.2.4.5 添加分区

前文创建分区表中已演示，支持批量添加分区。

4.1.2.4.6 修改分区的存储目录

alter table student_ptn partition (city='beijing') set location '/student_ptn_beijing';

4.1.2.4.7 删除分区

alter table student_ptn drop partition (city='beijing');

4.1.2.5 删除表

drop table tablename;

4.1.2.6 清空表

truncate table tablename;

4.1.3 视图操作

4.1.3.1 创建

create view view_cdt as select * from cdt;

4.1.3.2 查看视图列表

show views

4.1.3.3 描述视图

desc view_cdt;

4.1.3.4 查询视图

select * from view_cdt;

4.1.3.5 删除视图

drop view if exists view_cdt;

4.2 数据类型和存储格式

4.2.1 基本数据类型

布尔、整形、浮点型、字符型、字符串、字节数组、时间戳、日期

4.2.2 复合类型

create table cdt(
id int, 
name string, 
work_location array<string>, 
piaofang map<string,bigint>, 
address struct<location:string,zipcode:int,phone:string,value:int>) 
row format delimited 
fields terminated by "\t" 
collection items terminated by "," 
map keys terminated by ":" 
lines terminated by "\n";

4.2.3 存储格式

4.3 函数

4.3.1 内置函数

4.3.1.1 数学函数

取整、随机数、数学运算

4.2.1.2 集合函数

size

4.2.1.3 类型转换

4.2.1.4 日期函数

4.2.1.5 条件函数

select case field1 when value1 then field2 else 0 as field3
case field1 when value2 then field2 else 0 as field4
# case 数等于 field1 不同的 value 数。  

4.2.1.6 字符函数

4.2.1.7 聚合函数

4.2.1.8 表生成函数

4.2.1.9 窗口函数

• partition by:指定分组属性。
• order by:指定排序属性。
• 聚合函数：max()、min()、sum、avg()、rank()、ntile(n)、row_number()、dense_rank()

select max() over (partition by field1 [order by field2] [rows between unbounded/n preceding and current row/unbounded/n following])

• preceding: 窗口左边界，可指定为 current row。
• following: 窗口右边界，可指定为 current row。

4.2.2 查看内置函数列表

show functions

4.2.1.2 描述函数

desc function [extended]substr;

4.2.2 自定义函数 UDF

4.2.2 自定义函数

4.3 DQL

4.1 Join

4.4 实战

4.4.1

• 查询每个用户每月的 ucount 之和。

insert into table tmp_access select uname,umonth,sum(ucount) from t_access t group by t.uname,t.umonth;

• 查询每个用户每个月的最高 umount，和 总 umount。

select name,mon,num,max(num) over (partition by name order by mon)  as max, sum(num) over (partition by name order by mon) as sum from tmp_access;

4.4.2 查询数学成绩高于其他成绩的学生

create view score_rank as select sid,course,rank() over (partition by sid order by score desc) as rn1 from course;

select * from score_rank where rn1=1 and course='shuxue';

4.4.3 查询每年的最高温度和具体哪一天

create view weather_rank as select substr(data,1,8) `date`,rank() over (partition by substr(data,1,4) order by substr(data,9,2) desc) as rn1 from weather; 

select * from weather_rank where rn1=1;

4.4.4 查询学生是否选择了某门课程

select id,case course when 'a' then 1 else 0 as a,
when 'b' then 1 else 0 as b,
when 'c' then 1 else 0 as c,
when 'd' then 1 else 0 as d,
when 'e' then 1 else 0 as e,
when 'f' then 1 else 0 as f;

select id,max(a),max(b),max(c),max(d),max(e),max(f) group by id;

4.4.5 求月销售额和累计到当月的总销售额

窗口函数

5. 数据仓库理论

image.png

参考资料

• Hive学习之路 （一）Hive初识
• Hive学习之路 （七）Hive的DDL操作
• Hive表生成函数explode讲解
• 数据仓库之分层模型
• 数据仓库ODS、DW和DM概念区分