hive是基于Hadoop的一個數(shù)據(jù)倉庫工具物独，提供了豐富的SQL查詢方式來分析存儲在Hadoop分布式文件系統(tǒng)中的數(shù)據(jù)复局。
一、HIVE基礎(chǔ)概念與操作
1、Hive 內(nèi)部表和外部表關(guān)系
學(xué)習(xí)create table時經(jīng)常會遇到“內(nèi)部表”便锨、“外部表”的概念，因此需要梳理一下這兩類表的關(guān)系
| 差異 | 內(nèi)部表 | 外部表 |
|---|---|---|
| 創(chuàng)建 | 默認(rèn)創(chuàng)建為內(nèi)部表 | 需要加關(guān)鍵字external創(chuàng)建 |
| sql create | create table | create external table....location '/path' |
| 對數(shù)據(jù)的處理 | 將數(shù)據(jù)移動到數(shù)據(jù)倉庫指向的路徑 | 僅記錄數(shù)據(jù)所在的路徑我碟，不對數(shù)據(jù)的位置做任何改變 |
| 刪除 | 內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會被一起刪除 | 外部表只刪除元數(shù)據(jù)放案，不刪除數(shù)據(jù) |
【場景使用】[1]
外部表：比如某個公司的原始日志數(shù)據(jù)存放在一個目錄中，多個部門對這些原始數(shù)據(jù)進(jìn)行分析矫俺，那么創(chuàng)建外部表是明智選擇吱殉，這樣原始數(shù)據(jù)不會被刪除。
內(nèi)部表：對原始數(shù)據(jù)或比較重要的中間數(shù)據(jù)進(jìn)行建表存儲
2厘托、分區(qū)是什么友雳？
hive為了避免全表查詢，從而引進(jìn)分區(qū)铅匹，將數(shù)據(jù)按目錄進(jìn)行劃分押赊，減少不必要的查詢，從而提高效率伊群。
（1）分區(qū)表創(chuàng)建
hive的分區(qū)字段采用表外字段，在建表時partitioned by設(shè)定分區(qū)字段
CREATE table t_part(
id int
, name string
)
partitioned by (country string)
ROW format delimited
fields terminated by ','
（2）插入數(shù)據(jù)
插入分區(qū)及數(shù)據(jù)內(nèi)容
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查看分區(qū)情況
（3）數(shù)據(jù)檢索
分區(qū)表的意義在于優(yōu)化查詢策精。查詢時盡量利用分區(qū)字段舰始，如果不使用分區(qū)字段，就會全表掃描咽袜。
SELECT * FROM t_part WHERE country='China'
3丸卷、建表
（1）人工建表
create table的時候需要設(shè)置字段、字段類型询刹，還有分隔符的設(shè)置
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 數(shù)據(jù)插入
insert有兩種方式，一種是人為插入數(shù)據(jù)凹联，一種為select檢索后插入數(shù)據(jù)（ps還有導(dǎo)入文本插入沐兰，暫放）
| 插入方式 | 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直接追加到表中數(shù)據(jù)的尾部蔽挠，而insert overwrite會重寫數(shù)據(jù)住闯，既先進(jìn)行刪除，再寫入澳淑，采用哪種方式根據(jù)項目要求選擇比原。
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)
注意：轉(zhuǎn)換格式時蚌铜，String類型到int類型是數(shù)據(jù)類型高階到低階轉(zhuǎn)化锨侯，這一般是被禁止的。就像可以由int轉(zhuǎn)double是無損的厘线，但是double轉(zhuǎn)int會損失精度所以也不能轉(zhuǎn)化识腿。幾乎所有的數(shù)據(jù)類型都能轉(zhuǎn)化成string
6、內(nèi)置函數(shù)
HIVE提供了很多內(nèi)置函數(shù)用來支持基礎(chǔ)數(shù)據(jù)操作造壮，詳細(xì)可參考Hive2.0函數(shù)大全(中文版)渡讼。
二、案例實(shí)踐
數(shù)據(jù)對象為student和black_name
1耳璧、【案例一】修改錯誤問題
【需求】插入balck_name信息時成箫，小明重復(fù)插入了旨枯，需要對balck_name表做去重處理蹬昌；表student id=8的name應(yīng)該為“小曹”，需要修正過來
【技術(shù)點(diǎn)】row_number攀隔、update皂贩、overwrite、if
【實(shí)現(xiàn)步驟】
（1）black_name去重
使用row_number可以記錄目標(biāo)對象出現(xiàn)的次數(shù)昆汹，SELECT name, Row_Number() OVER (partition by name ORDER BY name desc) rank FROM student明刷，所以選擇第一次出現(xiàn)的值即可（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作為一種分布式環(huán)境下以HDFS為支撐的數(shù)據(jù)倉庫，它同樣更多的要求數(shù)據(jù)是不可變的满粗，基本不用hive做數(shù)據(jù)更新
可以通過update來修改數(shù)據(jù)辈末。如果一個表要實(shí)現(xiàn)update和delete功能，該表就必須支持ACID（語法是update 表名 set 字段名=“”where 條件）
有點(diǎn)麻煩映皆，換個方法試試看
INSERT overwrite table student
SELECT id
, if(id = 8, '小曹', name)
, age, class
FROM student
成功挤聘！
2、【案例二】去重用逗號分隔
【需求】根據(jù)name做group by捅彻，每個人的課程去重用逗號分隔组去，為string格式
【技術(shù)點(diǎn)】collect_set、concat_ws
【實(shí)現(xiàn)步驟】
（1）collect_set 去重取值
Hive中支持將某列轉(zhuǎn)為一個數(shù)組返回的函數(shù)有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對字符串按次序進(jìn)行拼接广料，concat_ws指定分隔符，進(jìn)行拼接
SELECT name,concat_ws(',',collect_set(class))
FROM student
GROUP BY name
3幼驶、【案例三】生成md5編號
【需求】根據(jù)name艾杏、class、age為每一行生成一個md5編號
【技術(shù)點(diǎn)】md5盅藻、concat购桑、cast
【實(shí)現(xiàn)步驟】
Hive提供md5(string/binary)返回md5碼畅铭；concat支持把不同類型的字段組裝；cast支持字段類型的轉(zhuǎn)換
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"
【技術(shù)點(diǎn)】current_timestamp、unix_timestamp阳惹、from_unixtime谍失、date_sub
【實(shí)現(xiàn)步驟】
Hive使用current_timestamp()獲得當(dāng)前時間，格式為“yyyy-MM-dd hh:mm:ss"莹汤；
格式轉(zhuǎn)換方面快鱼，unix_timestamp用于轉(zhuǎn)換time string為時間戳格式，from_unixtime支持將時間戳轉(zhuǎn)換為time string
日期差值方面纲岭，datediff(string enddate,string startdate)返回結(jié)束日期減去開始日期的天數(shù)抹竹；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止潮、【案例五】數(shù)量大于某個值
【需求】輸出name出現(xiàn)行數(shù)大于1的完整信息
【技術(shù)點(diǎn)】a.*
【實(shí)現(xiàn)步驟】
因?yàn)樾枰付ㄒ敵鐾暾畔ⅲ?code>SELECT * FROM student group by name是不合理窃判、無法運(yùn)行、無法輸出完整信息的沽翔。
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信息
【技術(shù)點(diǎn)】left join窿凤、full outer join仅偎、right join、inner join
比較inner join雳殊、full join橘沥、left join、right join的效果
【實(shí)現(xiàn)步驟】
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座咆，該字段內(nèi)容為：[{'name':name,'class':['奧數(shù)']}]。要求最外面為[]仓洼，中間為{}介陶，class字段對應(yīng)為[]
【技術(shù)點(diǎn)】str_to_map，named_struct
【實(shí)現(xiàn)步驟】
str_to_map支持將文本轉(zhuǎn)換為{}形式色建，但顯然是無法滿足當(dāng)前需求的
str_to_map('aaa:11&bbb:22', '&', ':')
發(fā)現(xiàn)函數(shù)named_struct可以實(shí)現(xiàn)該功能
named_struct('type', name, 'info', collect_set(class))
三哺呜、備注
- SQL代碼格式化：https://tool.oschina.net/codeformat/sql
- 以上截圖基于DBeaver界面
參考資料
[1] Hive外部表和內(nèi)部表的使用場景：https://blog.csdn.net/qq_34341930/article/details/89032791
[2] 《HIVE編程指南》
