目录
一、Hive 概览
1. Hive是什么
2. Hive应用场景
3. Hive 不适用场景
二、Hive架构
三、Hive 列值关键特性
1. hive数据类型
2. 类型转换
3. 日期处理函数
4. hive NULL值处理
四、Hive 建表
1. 建表语句
2. hive 建表的3种方式
3. Hive 内部表和外部表
4. Hive 分区表
五、Hive高级查询
1. Hive UDF函数
2. Hive 行转列和列转行函数
3. Hive 开窗函数
一、Hive 概览
1. Hive是什么
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供近乎标准SQL查询的功能。
2. Hive应用场景
Hadoop组件的hdfs文件系统解决了海量数据存储的问题，通过水平扩展DataNode节点，解决了单台机器存储能力有限的问题。Hadoop的mapreduce框架解决了海量数据计算的问题，使用并行计算的思想，将1个大的计算任务分割成n个小的任务同时计算，再将结果汇总。但是对于结构化的hdfs文件，比如一个csv格式的文本，想对其中的某些列做count，group by等类似sql的聚合操作，只能自己写代码实现，缺乏灵活性，并且对不会程序开发的人来说也是一个挑战。
Hive 的出现便是为了解决这一问题的，hive 给使用功能提供了类sql的查询功能，使得大家可以像使用普通数据库一样查询hive数据库，而不用关心底层功能实现。
在传统关系性数据库，例如mysql，单表支持的数据规模最大在亿级别，再大的规模需要考虑分库分表。而Hive支持的数据规模在千亿、万亿级别，专门解决海量数据查询的问题。在实际应用环境，业务数据都是存在mysql中，通过定期（比如按天）导数的方式，将数据导入hive中，构建大宽表，然后查询分析hive数据库。
3. Hive 不适用场景
Hive是面向OLAP领域的，不是面向OLTP的。Hive查询目标是对整张表数据的宏观分析，而不是通过指定id或者其他条件去查询其中的某几条数据。所以Hive没有向Mysql那样设计全局索引，也没有很好的支持事务以及对数据的严格性检查。
二、Hive架构
Hive不是一款传统意义上真正的数据库，因为它本身的数据文件是存放在HDFS上面的，而不是自己负责存储。用户通过hive创建的数据库、表元信息信息交由第三方数据库，例如Mysql，Postgresql来存储的。
Hive中两个重要的组件：Hive Metastore 和 HiveServer2。Metastore负责库、表元信息的存放和读取。HiveServer2负责接受用户发起的连接请求，解析用户提交的sql语句，翻译成MapReduce任务提交给YARN，并将任务执行结果返回给用户。整体架构如下图所示：
三、Hive 列值关键特性
1. hive数据类型
Hive支持原始数据类型和复杂类型，原始类型包括数值型（整型，浮点数，定点数），Boolean，字符串，时间戳。复杂类型包括数组，map，struct。下面是Hive数据类型的一个总结：
分类 类型 描述 字面量示例
原始类型 BOOLEAN true/false TRUE
TINYINT 1字节的有符号整数 -128~127 1Y
SMALLINT 2个字节的有符号整数，-32768~32767 1S
INT 4个字节的带符号整数 1
BIGINT 8字节带符号整数 1L
FLOAT 4字节单精度浮点数 1.0F DOUBLE 8字节双精度浮点数 1.0
DEICIMAL 任意精度的带符号小数 1.0
STRING 字符串，变长 “a”,’b’
VARCHAR 变长字符串 “a”,’b’
CHAR 固定长度字符串 “a”,’b’
BINARY 字节数组
TIMESTAMP 时间戳，纳秒精度 122327493795
DATE 日期 ‘2016-03-29’ 复杂类型 ARRAY 有序的的同类型的集合 array(1,2)
MAP key-value,key必须为原始类型 ，value可以任意类型 map(‘a’,1,’b’,2)
STRUCT 字段集合,类型可以不同 struct(‘1’,1,1.0)
UNION 在有限取值范围内的一个值 create_union(1,’a’,63)2. 类型转换
2.1 隐式转换
同Java语言一样，Hive也包括隐式转换（implicit conversions）和显式转换（explicitly conversions）。Hive在需要的时候将会对numeric类型的数据进行隐式转换。任何整数类型都可以隐式地转换成一个范围更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT都可以隐式地转换成DOUBLE，并且Hive中的STRING类型也可以转换为DOUBLE类型，只要实际值为double类型。
2.2 CAST 强转
可以使用CAST关键字来显式的将一个类型的数据转换成另一个数据类型， CAST的语法为cast(value AS TYPE)。举个例子：假如我们一个员工表employees，其中有name、salary等字段；salary是字符串类型的。有如下的查询：
SELECT name, salary FROM employees WHERE cast(salary AS FLOAT) < 100000.0;
这样salary将会显示的转换成float。如果salary是不能转换成float，这时候cast将会返回NULL！
3. 日期处理函数
3.1 hive中有两个非常有用的日期转换函数：
- unix_timestamp(string date [, string format])：该函数接收日期格式的字符串，返回一个时间戳的bigint类型
- from_unixtime(bigint unixtime[, stringformat])：该函数接收一个时间戳参数，并返回格式化后的字符串
select from_unixtime(1423306743,'yyyyMMdd')
from dw.ceshi_data;
结果如下：
20150207
select unix_timestamp('2018-09-05 12:01:03')
from dw.ceshi_data;
结果如下：
15361200633.2 日期时间转日期函数: to_date语法: to_date(string timestamp)，返回值: string，说明: 返回日期时间字段中的日期部分。
select to_date('2021-01-08 10:03:01') from dual;
2021-01-083.3 year，month，day，hour，minute，second函数都是传入一个标准格式的时间字符串，分别返回日期中的年，月，天，小时，分钟，秒。
select year('2015-12-08 10:00:00') from test;
2015
select month('2015-12-08 10:00:00') from test;
123.4 current_date 函数返回当前日期：'yyyy-MM-dd'，current_timestamp函数返回当前时间：'yyyy-MM-dd HH:mm:ss'。date_add(string startdate, int days) 函数返回从指定日期加上指定天数后的日期， date_sub (string startdate, int days) 函数返回从指定减去指定天数后的日期，这几个函数配合使用在日常的查询分析中非常有用，比如查询用户表最近30天新注册用户：
select * from `user` where createTime > date_sub(current_date, 30);3.5 需要注意的是，to_date，year，month，day这些函数能够识别的日期格式是标准的日期格式：
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd
如果表中日期类型不是标准时间格式，需要先用hive字符串函数替换为正确的日期类型。
4. hive NULL值处理
hive中null实际在HDFS中默认存储为’\N’，通过查询显示的是’NULL’。如果要查询值为NULL的字符，可通过语句：aaa is null 或者 aaa =’\N’ 实现。 如果要修改NULL值默认的存储格式，可以使用下面的语句更改：
alter table name SET SERDEPROPERTIES('serialization.null.format' = 'NULL');
四、Hive 建表
1. 建表语句
Hive 建表语法跟Mysql非常类似，例如一个简单的建表语句内容如下：
create table testuser(id int, username string);但是Hive有一些额外的关键字，向CLUSTERED BY，ROW FORMAT等，用于支持表分区，分通，行格式等特性。完整的建表语法内容如下：
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]2. hive 建表的3种方式
2.1 直接建表法
直接使用create命令创建表：
CREATE TABLE `user`(
`id` int,
`username` string,
`tel` string)
partitioned by(age int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','2.2 查询建表法
create table ...as select..(CTAS)，通过查询已有的表来创建一张新表，这样可以根据已有的表来创建子表：
create table user1 as
select * from `user` where age > 20;2.3 LIKE建表法
使用like命令可以用来复制表的结构，而不填充数据：
CREATE TABLE user2 LIKE `user`;3. Hive 内部表和外部表
Hive内部表是先使用create命令创建一张表，然后使用load或者insert语句向表中插入数据。而外部表是数据源已经在hdfs上创建好了，现在需要告诉hive这份结构化数据的列类型信息，使得hive可以按照指定列类型去解析数据。
hive默认创建内部表，使用create external table xxx来创建外部表，并且需要与location关键字一起使用，指定已存在数据在hdfs上的存储位置。
4. Hive 分区表
Hive 分区表是为了解决表的数据量过大，集中存放时引起的查询效率低下的问题。将数据按照分区字段拆分存储的表，在hdfs中以文件夹的形式分别存放不同分区的数据，可以避免全表查询，提高查询效率。Hive 支持两种分区类型：静态分区和动态分区
4.1 静态分区：Hive 使用create table xxx partitioned by (colName, colType)来创建分区表，在创建分区表后，使用load 或者 insert语句插入数据时，必须指定分区列的值：
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row …];
load data local inpath '/tmp/mydata.txt' into table employees partition (col1 =china,col2=SiChuan)Hive 在hdfs上插入数据时，会以指定的分区列的值在hdfs上创建文件夹（如果不存在），然后将该条记录插入到该文件夹下的文件中。所以在设计hive分区表时，必须要考虑hive分区列可能的取值总量，防止分区数过多，在hdfs上创建了太多的文件夹。
4.2 动态分区：hive 静态分区的缺点在于每次插入数据时必须手动指定分区列的值，这是比较麻烦的，可以使用hive的动态分区特性让其自动从插入的数据中获取分区列的值。
动态分区默认是没有开启。开启后默认是以严格模式执行的，在这种模式下需要至少一个分区字段是静态的。这有助于阻止因设计错误导致导致查询差生大量的分区。列如：用户可能错误使用时间戳作为分区表字段。然后导致每秒都对应一个分区！
关闭严格分区模式，动态分区模式时是严格模式，也就是至少有一个静态分区。
set hive.exec.dynamic.partition.mode=nonstrict //分区模式，默认nostrict
set hive.exec.dynamic.partition=true //开启动态分区,默认true
set hive.exec.max.dynamic.partitions=1000 //最大动态分区数,默认1000五、Hive高级查询
1. Hive UDF函数
UDF（User-Defined Functions）即是用户定义的hive函数。distinct，count，sum这些hive自带的函数，当这些函数不能满足业务需求时，便需要使用自定义函数功能。Hive UDF函数可以分为以下几类：
- UDF：one to one，输入一个列值，输出另一个值。是row级别操作，如：upper、substr函数
- UDAF：many to one，输入一个列的多个值，输出另一个值，row mapping，如sum/min。
- UDTF：one to many ，输入一个列值，输出多个值。如alteral view与explode
要实现并使用一个UDF(one to one)类型的函数，需要以下步骤：
- 继承 UDF 类。
- 重写 evaluate 方法。
- 将该 java 文件打包成 jar。
- 在 beeline（hive 的一种终端）中输入如下命令：
jdbc:hive2://localhost:10000> add jar /mypath/MyUDF.jar; # 将jar包添加到hive classpath
jdbc:hive2://localhost:10000> create temporary function myUdf as 'com.wangzhe.MyUDF'; # 注册临时函数
jdbc:hive2://localhost:10000> select myUdf("xxx"); # 使用函数2. Hive 行转列和列转行函数
2.1 列转行
列转行的需求是将如下的数据格式：
K1 | v1 |
k1 | v2 |
k2 | v3 |
k2 | v4 |
k2 | v5 |
转换为这样的数据格式：
k1 | v1, v2 |
k2 | v3, v4, v5 |
使用hive列转行功能需要使用GROUP BY + CONCAT_WS + COLLECT_LIST/COLLECT_SET组合。举例说明，假设存在下面的表：
create table students_info
(`SNO` string comment '学生编号',
`name` string comment '姓名',
`DEPART` string comment '选修课程'
)需要按照学生姓名分组显示所有的选修课程，可以使用下面的sql语句：
select sno, name
, concat_ws(',', collect_set(DEPART)) as DEPART
from students_info
group by name2.2 行转列
在说明行转列的功能前，先复习一下数据库的第一，第二，第三范式：
- 第一范式：属性的原子性。数据库中的每一列都是不可分割的基本数据项，同一列中不能有多个值
- 第二范式：属性完全依赖于主键。即非主属性不能依赖于主键的部分属性，必须依赖于主键的所有属性。
- 第三范式：要求一个数据库表中不包含已在其他表中已包含的非主关键字信息
Hive 中出现的array，map这样的数据类型，其实是违反了第一范式标准的，但是方便了数据查询分析。使用行转列功能，需要lateral view 和 explode函数配合使用。假设上面的student_info里面的DEPART(选修课程）字段存的值是以逗号分割的课程名称列表，现在要求查询把DEPART列的数据一行分割成多行，每行显示单个课程名称，可以使用如下的SQL语句：
select SNO, name, single_DEPART
from students_info si
lateral view explode(split(si.DEPART,',')) b AS single_DEPART3. Hive 开窗函数
使用传统的group by语句查询表时，其分组列可以在结果集中直接展示，其他列只能显示其聚合结果，比如count(col1)，avg(col2) 等。即每1个分组只能显示1行记录，有多少个分组，便有多少行记录。
Hive 开窗函数也是按照指定列分割成窗口(window，和分组概念一致)，不同之处在于使用窗口函数除了可以显示基于窗口的聚合结果，还可以显示窗口内每一条数据详细信息。使用hive开窗函数的语法格式如下（[ ]表示可选项）：
select col1, col2, 开窗函数(colx) over([partion by colx] [sort by colxx]) from xxx;over()函数确定分组条件，如果不传入参数，则表示以符合条件的所有行作为1个窗口。partion by 表示基于指定列分组，可以是多个列。sort by表示基于指定类排序。开窗函数传入的列，partion by指定的列，sort by指定的列，这三者可以是完全不同的列。
开窗函数一般分为两类,聚合开窗函数和排序开窗函数。
3.1 聚合开窗函数
1) 传统聚合函数：sum, count, max, min, avg
2) first_value：返回分区中的第一个值。last_value：返回分区中的最后一个的值。
3) lag开窗函数
lag(col,n,default) 用于统计窗口内往上第n个值。
col:列名
n:往上第n行，可以不传，默认值为1
default:往上第n行为NULL时候，取默认值,不指定则取NULL
4) lead开窗函数
lead(col,n,default) 用于统计窗口内往下第n个值。
col:列名
n:往下第n行，可以不传，默认值为1
default:往下第n行为NULL时候，取默认值,不指定则取NULL
lead函数和lag函数在基于用户点击日志记录计算pv模型时特别有用
3.2 排序开窗函数
1) rank()函数
此排序方法进行排序时，相同的排序是一样的，而且下一个不同值是跳着排序的。
2) row_number()函数
此方法不管排名是否有相同的，都按照顺序1，2，3…..n
3) dense_rank()函数
此方法对于排名相同的名次一样，且后面名次不跳跃
