hive是基于Hadoop的一个数据仓库工具，提供了丰富的SQL查询方式来分析存储在Hadoop分布式文件系统中的数据。
一、HIVE基础概念与操作
1、Hive 内部表和外部表关系
学习create table时经常会遇到“内部表”、“外部表”的概念，因此需要梳理一下这两类表的关系
| 差异 | 内部表 | 外部表 |
|---|---|---|
| 创建 | 默认创建为内部表 | 需要加关键字external创建 |
| sql create | create table | create external table....location '/path' |
| 对数据的处理 | 将数据移动到数据仓库指向的路径 | 仅记录数据所在的路径，不对数据的位置做任何改变 |
| 删除 | 内部表的元数据和数据会被一起删除 | 外部表只删除元数据，不删除数据 |
【场景使用】[1]
外部表：比如某个公司的原始日志数据存放在一个目录中，多个部门对这些原始数据进行分析，那么创建外部表是明智选择，这样原始数据不会被删除。
内部表：对原始数据或比较重要的中间数据进行建表存储
2、分区是什么？
hive为了避免全表查询，从而引进分区，将数据按目录进行划分，减少不必要的查询，从而提高效率。
（1）分区表创建
hive的分区字段采用表外字段，在建表时partitioned by设定分区字段
CREATE table t_part(
id int
, name string
)
partitioned by (country string)
ROW format delimited
fields terminated by ','
（2）插入数据
插入分区及数据内容
insert into t_part partition(country='China') values(7,'Mark');
insert into t_part partition(country='China') values(9,'Tony');
insert into t_part partition(country='US') values(10,'Jane');
insert into t_part partition(country='US') values(11,'Lisa');
# 批量插入模式
insert into t_part partition(country='US') values(7,'Mark'),(9,'Tony'),(10,'Jane'),(11,'Lisa')
可以用show partitions t_part查看分区情况
（3）数据检索
分区表的意义在于优化查询。查询时尽量利用分区字段，如果不使用分区字段，就会全表扫描。
SELECT * FROM t_part WHERE country='China'
3、建表
（1）人工建表
create table的时候需要设置字段、字段类型，还有分隔符的设置
CREATE TABLE IF NOT EXISTS student(
id int
, name string
,age int
,class string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
（2）基于已有表建立
CREATE TABLE IF NOT EXISTS student
AS
SELECT ...
4、INSERT 数据插入
insert有两种方式，一种是人为插入数据，一种为select检索后插入数据（ps还有导入文本插入，暂放）
| 插入方式 | SQL |
|---|---|
| 人工插入 | INSERT INTO table_name(field1,field2) VALUES(v101,v102),(v201,v202),(v301,v302),(v401,v402) |
| 检索插入 | INSERT INTO t2(field1,field2) SELECT col1,col2 FROM t1 WHERE |
在insert into 和 insert overwrite的选择上，insert into直接追加到表中数据的尾部，而insert overwrite会重写数据，既先进行删除，再写入，采用哪种方式根据项目要求选择。
5、ALTER 对表的操作
ALTER TABLE 语句用于在已有的表中对列的操作。
| 操作 | SQL |
|---|---|
| 增加列 | ALTER TABLE table_name ADD columns(column_name datatype) |
| 删除列 | ALTER TABLE table_name DROP COLUMN column_name |
| 修改列类型 | ALTER TABLE table_name CHANGE old_col_name new_col_name new_col_type |
| 表的重命名 | ALTER TABLE table_name RENAME COLUMN a TO b |
例如将表emp重命名为students，添加一个名为 "birthday" 的新列，dete格式。SQL如下：
ALTER TABLE emp RENAME TO students
ALTER table students ADD columns(birthday date)
注意：转换格式时，String类型到int类型是数据类型高阶到低阶转化，这一般是被禁止的。就像可以由int转double是无损的，但是double转int会损失精度所以也不能转化。几乎所有的数据类型都能转化成string
6、内置函数
HIVE提供了很多内置函数用来支持基础数据操作，详细可参考Hive2.0函数大全(中文版)。
二、案例实践
数据对象为student和black_name
1、【案例一】修改错误问题
【需求】插入balck_name信息时，小明重复插入了，需要对balck_name表做去重处理；表student id=8的name应该为“小曹”，需要修正过来
【技术点】row_number、update、overwrite、if
【实现步骤】
（1）black_name去重
使用row_number可以记录目标对象出现的次数，SELECT name, Row_Number() OVER (partition by name ORDER BY name desc) rank FROM student，所以选择第一次出现的值即可（rank=1）
insert overwrite table black_name
SELECT name
FROM (
SELECT name, row_number() OVER (PARTITION BY name ORDER BY name DESC) AS rn
FROM black_name
) t
WHERE t.rn = 1
（2）修改错误
HIVE作为一种分布式环境下以HDFS为支撑的数据仓库，它同样更多的要求数据是不可变的，基本不用hive做数据更新
可以通过update来修改数据。如果一个表要实现update和delete功能，该表就必须支持ACID（语法是update 表名 set 字段名=“”where 条件）
有点麻烦，换个方法试试看
INSERT overwrite table student
SELECT id
, if(id = 8, '小曹', name)
, age, class
FROM student
成功！
2、【案例二】去重用逗号分隔
【需求】根据name做group by，每个人的课程去重用逗号分隔，为string格式
【技术点】collect_set、concat_ws
【实现步骤】
（1）collect_set 去重取值
Hive中支持将某列转为一个数组返回的函数有collect_list和collect_set。不同的是collect_list不去重，而collect_set去重。
SELECT name,collect_set(class)
FROM student
GROUP BY name
（2）CONCAT的拼接
Hive中支持将list拼接为string的为concat和concat_ws，concat对字符串按次序进行拼接，concat_ws指定分隔符，进行拼接
SELECT name,concat_ws(',',collect_set(class))
FROM student
GROUP BY name
3、【案例三】生成md5编号
【需求】根据name、class、age为每一行生成一个md5编号
【技术点】md5、concat、cast
【实现步骤】
Hive提供md5(string/binary)返回md5码；concat支持把不同类型的字段组装；cast支持字段类型的转换
SELECT name,md5(CAST(concat(name, class, CAST(age AS STRING)) AS BINARY))
FROM student
# 简化版
SELECT name,md5(concat(name,class,age))
FROM student
4、【案例四】各类格式日期生成
【需求】每一行增加5个字段：
字段一为此刻的时间格式为“yyyy-MM-dd hh:mm:ss"；
字段二为此刻的时间戳格式；
字段三为“XXXX年XX月XX日”格式的此刻时间；
字段四为减去90天的时间，格式为“yyyy-MM-dd"
【技术点】current_timestamp、unix_timestamp、from_unixtime、date_sub
【实现步骤】
Hive使用current_timestamp()获得当前时间，格式为“yyyy-MM-dd hh:mm:ss"；
格式转换方面，unix_timestamp用于转换time string为时间戳格式，from_unixtime支持将时间戳转换为time string
日期差值方面，datediff(string enddate,string startdate)返回结束日期减去开始日期的天数；date_add(string startdate, int days)返回开始日期startdate增加days天后的日期；date_sub (string startdate,int days)返回开始日期startdate减去days天后的日期,返回yyyy-MM-dd日期格式
SELECT current_timestamp() as a
,unix_timestamp() as b
,from_unixtime(unix_timestamp(),'yyyy年MM月dd日') as c
,date_sub(CAST(current_timestamp() AS DATE),30) as d
FROM student
5、【案例五】数量大于某个值
【需求】输出name出现行数大于1的完整信息
【技术点】a.*
【实现步骤】
因为需要指定要输出完整信息，SELECT * FROM student group by name是不合理、无法运行、无法输出完整信息的。
SELECT a.*
FROM (
SELECT *
FROM student
) a
INNER JOIN (
SELECT name
FROM student
GROUP BY name
HAVING COUNT(name) > 1
) b
ON a.name = b.name
SELECT *
FROM student
WHERE student.name IN (
SELECT name
FROM student
GROUP BY name
HAVING COUNT(name) > 1
)
6、【案例六】多表匹配
【需求】获得不在黑名单的student信息
【技术点】left join、full outer join、right join、inner join
比较inner join、full join、left join、right join的效果
【实现步骤】
SELECT a.*
FROM student a
LEFT JOIN (
SELECT name
FROM black_name
) b
ON a.name = b.name
WHERE b.name is NULL
7、【案例七】[{[]}]格式
【需求】输出字段info，该字段内容为：[{'name':name,'class':['奥数']}]。要求最外面为[]，中间为{}，class字段对应为[]
【技术点】str_to_map，named_struct
【实现步骤】
str_to_map支持将文本转换为{}形式，但显然是无法满足当前需求的
str_to_map('aaa:11&bbb:22', '&', ':')
发现函数named_struct可以实现该功能
named_struct('type', name, 'info', collect_set(class))
三、备注
- SQL代码格式化：https://tool.oschina.net/codeformat/sql
- 以上截图基于DBeaver界面
参考资料
[1] Hive外部表和内部表的使用场景：https://blog.csdn.net/qq_34341930/article/details/89032791
[2] 《HIVE编程指南》
