學習目標

#學會Hive shell命令列與引數配置
#理解函式分類標準(UDF、UDTF、UDAF)
	根據函式輸入和輸出的行數
		一進一出
		一進多出
		多進一出
#掌握常用函式的使用
	見多識廣，多用多會，不用就忘
#掌握explode函式、側視圖使用
#掌握行列轉換、json資料處理

內容大綱

#1、hive的shell命令列和引數配置方式
#2、Hive的函式
	函式分類：內置函式、用戶定義函式
	UDF、UDTF、UDAF
#3、Hive函式高階應用（面試筆試、開發高頻區域）
	explode(UDTF)函式功能
	lateral view 側視圖
	行列轉換
	json格式資料決議

01_Apache Hive 第一代客戶端的功能

批處理：一次連接，一次互動，執行結束斷開連接
互動式處理：保持持續連接，一直互動

注意：如果說hive的shell客戶端指的是第一代客戶端bin/hive

而第二代客戶端bin/beeline屬于JDBC客戶端不是shell，

bin/hive

功能1：作為第一代客戶端連接訪問metastore服務，使用Hive，互動式方式

功能2：啟動hive服務

/export/server/apache-hive-3.1.2-bin/bin/hive --service metastore 
/export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2

功能3：批處理執行Hive SQL

#-e 執行后面的sql陳述句
/export/server/apache-hive-3.1.2-bin/bin/hive  -e 'select * from itheima.student'

#-f 執行后面的sql檔案
vim hive.sql
select * from itheima.student limit 2

/export/server/apache-hive-3.1.2-bin/bin/hive  -f hive.sql

#sql檔案不一定是.sql 要保證檔案中是正確的HQL語法，

#-f呼叫sql檔案執行的方式 是企業中hive生產環境主流的呼叫方式，

02_Apache Hive 引數配置方式與優先級范圍

有哪些引數可以配置？

https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

配置方式有哪些？注意配置方式影響范圍影響時間是怎樣？

方式1：組態檔 con/hive-site.xml

影響的是基于這個安裝包的任何使用方式，

方式2：配置引數 hiveconf

/export/server/apache-hive-3.1.2-bin/bin/hive --service metastore  

/export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2  --hiveconf hive.root.logger=DEBUG,console

#影響的是session會話級別的

方式3：set命令

session會話級別的 設定完之后將會對后面的sql執行生效，
session結束 set設定的引數將失效，

也是推薦搭建使用的設定引數方式，  誰需要 誰設定 誰生效

總結
- 從方式1到方式3 影響的范圍是越來越小的，
- 從方式1到方式3 優先級越來越高，set命令設定的會覆寫其他的，
- Hive作為的基于Hadoop的數倉，也會把Hadoop 的相關配置決議加載進來，

03_Apache Hive 內置常見運算子

官方鏈接：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
查看運算子和函式的幫助手冊

可以使用課程資料中中文版參考手冊

--顯示所有的函式和運算子
show functions;
--查看運算子或者函式的使用說明
describe function +;
--使用extended 可以查看更加詳細的使用說明
describe function extended +;

具體分類
- 關系運算子
- 算術運算子
- 邏輯運算子

--1、創建表dual
create table dual(id string);
--2、加載一個檔案dual.txt到dual表中
--dual.txt只有一行內容：內容為一個空格
load data local inpath '/root/hivedata/dual.txt' into table dual;
--3、在select查詢陳述句中使用dual表完成運算子、函式功能測驗
select 1+1 from dual;

select 1+1;

----------------Hive中關系運算子--------------------------
--is null空值判斷
select 1 from dual where 'itcast' is null;

--is not null 非空值判斷
select 1 from dual where 'itcast' is not null;

--like比較： _表示任意單個字符 %表示任意數量字符
--否定比較： NOT A like B
select 1 from dual where 'itcast' like 'it_';
select 1 from dual where 'itcast' like 'it%';
select 1 from dual where  'itcast' not like 'hadoo_';
select 1 from dual where  not 'itcast' like 'hadoo_';

--rlike：確定字串是否匹配正則運算式，是REGEXP_LIKE()的同義詞，
select 1 from dual where 'itcast' rlike '^i.*t$';
select 1 from dual where '123456' rlike '^\\d+$';  --判斷是否全為數字
select 1 from dual where '123456aa' rlike '^\\d+$';

--regexp：功能與rlike相同 用于判斷字串是否匹配正則運算式
select 1 from dual where 'itcast' regexp '^i.*t$';



-------------------Hive中算術運算子---------------------------------
--取整操作: div  給出將A除以B所得的整數部分，例如17 div 3得出5，
select 17 div 3;

--取余操作: %  也叫做取模mod  A除以B所得的余數部分
select 17 % 3;

--位與操作: &  A和B按位進行與操作的結果， 與表示兩個都為1則結果為1
select 4 & 8 from dual;  --4轉換二進制：0100 8轉換二進制：1000
select 6 & 4 from dual;  --4轉換二進制：0100 6轉換二進制：0110

--位或操作: |  A和B按位進行或操作的結果  或表示有一個為1則結果為1
select 4 | 8 from dual;
select 6 | 4 from dual;

--位異或操作: ^ A和B按位進行異或操作的結果 異或表示兩者的值不同,則結果為1
select 4 ^ 8 from dual;
select 6 ^ 4 from dual;


--3、Hive邏輯運算子
--與操作: A AND B   如果A和B均為TRUE，則為TRUE，否則為FALSE，如果A或B為NULL，則為NULL，
select 1 from dual where 3>1 and 2>1;
--或操作: A OR B   如果A或B或兩者均為TRUE，則為TRUE，否則為FALSE，
select 1 from dual where 3>1 or 2!=2;
--非操作: NOT A 、!A   如果A為FALSE，則為TRUE；如果A為NULL，則為NULL，否則為FALSE，
select 1 from dual where not 2>1;
select 1 from dual where !2=1;

--在:A IN (val1, val2, ...)  如果A等于任何值，則為TRUE，
select 1 from dual where 11  in(11,22,33);
--不在:A NOT IN (val1, val2, ...) 如果A不等于任何值，則為TRUE
select 1 from dual where 11 not in(22,33,44);

04_Apache Hive 函式及分類標準（UDF、UDAF、UDTF）

4.1 內置的函式（build in func）

所謂的內置指的是hive開發好，可以直接上手使用的；

內置函式往往根據函式的應用功能型別來分類
日期函式、數字函式、字串函式、集合函式、條件函式…

4.2 用戶定義函式（user-defined function）

用戶編程實作函式的邏輯在hive中使用，

UDF根據函式輸入行數和輸出行數進行分類

UDF 、UDAF、UDTF

#1、UDF（User-Defined-Function）普通函式 一進一出  輸入一行資料輸出一行資料

0: jdbc:hive2://node1:10000> select split("allen woon hadoop"," ");
+----------------------------+--+
|            _c0             |
+----------------------------+--+
| ["allen","woon","hadoop"]  |
+----------------------------+--+

#2、UDAF（User-Defined Aggregation Function）聚合函式，多進一出 輸入多行輸出一行

count sum max  min  avg

#3、UDTF（User-Defined Table-Generating Functions）表生成函式 一進多出 輸入一行輸出多行

explode 、parse_url_tuple

4.3 UDF分類標準的擴大化

本來，udf/udtf/udaf3個標準是針對用戶自定義函式分類的；
但是，現在可以將這個分類標準擴大到hive中所有的函式，包括內置函式和自定義函式；
不要被UD這兩個字母所影響， Built-in Aggregate Functions (UDAF).

函式相關的常用幫助命令

--顯示所有的函式和運算子
show functions;
--查看運算子或者函式的使用說明
describe function +;
desc function 
--使用extended 可以查看更加詳細的使用說明
describe function extended count;

05_Apache Hive 內置函式梳理字串函式

--字串截取函式：substr(str, pos[, len]) 或者  substring(str, pos[, len])
select substr("angelababy",-2); --pos是從1開始的索引，如果為負數則倒著數
select substr("angelababy",2,2);

--正則運算式替換函式：regexp_replace(str, regexp, rep)
select regexp_replace('100-200', '(\\d+)', 'num'); --正則分組

--正則運算式決議函式：regexp_extract(str, regexp[, idx]) 提取正則匹配到的指定組內容
select regexp_extract('100-200', '(\\d+)-(\\d+)', 2);

--URL決議函式：parse_url 注意要想一次決議出多個 可以使用parse_url_tuple這個UDTF函式
select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST');

--分割字串函式: split(str, regex)
select split('apache hive', '\\s+');--匹配一個或者多個空白符

--json決議函式：get_json_object(json_txt, path)
--$表示json物件
select get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"}, {"website":"cloud.itcast.com","name":"carbondata 中文檔案"}]', '$.[1].website');

06_Apache Hive 內置函式梳理時間日期、數值

6.1 Date Functions 日期函式

日期和時間戳數字之間的轉換

注意：日期的標準格式如果不是標準需要轉換成為標準格式

unix_timestamp 日期轉unix時間戳

from_unixtime unix時間戳轉日期

date_add

date_sub

datediff

--獲取當前日期: current_date
select current_date();
--獲取當前時間戳: current_timestamp
--同一查詢中對current_timestamp的所有呼叫均回傳相同的值，
select current_timestamp();
--獲取當前UNIX時間戳函式: unix_timestamp
select unix_timestamp();
--日期轉UNIX時間戳函式: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期轉UNIX時間戳函式: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX時間戳轉日期函式: from_unixtime
select from_unixtime(1620723323);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比較函式: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函式: date_add
select date_add('2012-02-28',10);
--日期減少函式: date_sub
select date_sub('2012-01-1',10);

6.2 Mathematical Functions 數學函式

round 取整

rand 取隨機值

--取整函式: round  回傳double型別的整數值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函式: round(double a, int d) 回傳指定精度d的double型別
select round(3.1415926,4);
--向下取整函式: floor
select floor(3.1415926);
select floor(-3.1415926);
--向上取整函式: ceil
select ceil(3.1415926);
select ceil(-3.1415926);
--取亂數函式: rand 每次執行都不一樣 回傳一個0到1范圍內的亂數
select rand();
--指定種子取亂數函式: rand(int seed) 得到一個穩定的亂數序列
select rand(5);

07_Apache Hive 內置函式梳理條件轉換、集合、加密

7.1 Conditional Functions 條件函式

都重要，尤其是case when

--if條件判斷: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;

--空判斷函式: isnull( a )
select isnull("allen");
select isnull(null);

--非空判斷函式: isnotnull ( a )
select isnotnull("allen");
select isnotnull(null);

--空值轉換函式: nvl(T value, T default_value)
select nvl("allen","itcast");
select nvl(null,"itcast");

--非空查找函式: COALESCE(T v1, T v2, ...)
--回傳引數中的第一個非空值；如果所有值都為NULL，那么回傳NULL
select COALESCE(null,11,22,33);
select COALESCE(null,null,null,33);
select COALESCE(null,null,null);

--條件轉換函式: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;

7.2 Type Conversion Functions 型別轉換函式

前置知識：Hive中支持型別的隱式轉換有限制自動轉換不保證成功就顯示null

cast顯示型別轉換函式

--任意資料型別之間轉換:cast
select cast(12.14 as bigint);
select cast(12.14 as string);
select cast("hello" as int);
+-------+
|  _c0  |
+-------+
| NULL  |
+-------+

7.3 Data Masking Functions 資料脫敏函式

mask脫敏掩碼處理

資料脫敏：讓敏感資料不敏感 13455667788 —>134****7788

--mask
--將查詢回的資料，大寫字母轉換為X，小寫字母轉換為x，數字轉換為n，
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); --自定義替換的字母

--mask_first_n(string str[, int n]
--對前n個進行脫敏替換
select mask_first_n("abc123DEF",4);

--mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4);

--mask_show_first_n(string str[, int n])
--除了前n個字符，其余進行掩碼處理
select mask_show_first_n("abc123DEF",4);

--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);

--mask_hash(string|char|varchar str)
--回傳字串的hash編碼，
select mask_hash("abc123DEF");

7.4 Misc. Functions 其他雜項函式、加密函式

--如果你要呼叫的java方法所在的jar包不是hive自帶的 可以使用add jar添加進來
--hive呼叫java方法: java_method(class, method[, arg1[, arg2..]])
select java_method("java.lang.Math","max",11,22);

--反射函式: reflect(class, method[, arg1[, arg2..]])
select reflect("java.lang.Math","max",11,22);

--取哈希值函式:hash
select hash("allen");

--current_user()、logged_in_user()、current_database()、version()

--SHA-1加密: sha1(string/binary)
select sha1("allen");

--SHA-2家族演算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("allen",224);
select sha2("allen",512);

--crc32加密:
select crc32("allen");

--MD5加密: md5(string/binary)
select md5("allen");

08_Apache Hive explode函式的使用與限制（UDTF表生成函式）

explode屬于UDTF函式，表生成函式，輸入一行資料輸出多行資料，

功能：

explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows.

--explode接收map array型別的引數 把map或者array的元素輸出，一行一個元素，

explode(array(11,22,33))         11
	                             22
	                             33
	                             
	                             
select explode(`array`(11,22,33,44,55));
select explode(`map`("id",10086,"name","allen","age",18));

栗子

將NBA總冠軍球隊資料使用explode進行拆分，并且根據奪冠年份進行倒序排序，

--step1:建表
create table the_nba_championship(
           team_name string,
           champion_year array<string>
) row format delimited
fields terminated by ','
collection items terminated by '|';

--step2:加載資料檔案到表中
load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nba_championship;

--step3:驗證
select * from the_nba_championship;

--step4:使用explode函式對champion_year進行拆分 俗稱炸開
select explode(champion_year) from the_nba_championship;

--想法是正確的 sql執行確實錯誤的
select team_name,explode(champion_year) from the_nba_championship;
--錯誤資訊
UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF 在 SELECT 子句之外不受支持，也不在運算式中嵌套？？？

如果資料不是map或者array 如何使用explode函式呢？

想法設法使用split subsrt regex_replace等函陣列合使用把資料變成array或者map.

create table the_nba_championship_str(
           team_name string,
           champion_year string
) row format delimited
fields terminated by ',';

load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nba_championship_str;

09_Apache Hive lateral view側視圖的使用

側視圖的原理是將UDTF的結果構建成一個類似于視圖的表，然后將原表中的每一行和UDTF函式輸出的每一行進行連接，生成一張新的虛擬表

背景
- UDTF函式生成的結果可以當成一張虛擬的表，但是無法和原始表進行組合查詢
```
select name,explode(location) from test_message;
--這個sql就是錯誤的  相當于執行組合查詢 
```
- 從理論層面推導，對兩份資料進行join就可以了
- 但是，hive專門推出了lateral view側視圖的語，滿足上述需要，
功能：把UDTF函式生成的結果和原始表進行關聯，便于用戶在select時間組合查詢、 lateral view是UDTf的好基友好搭檔，實際中經常配合使用，

語法：

--lateral view側視圖基本語法如下
select …… from tabelA lateral view UDTF(xxx) 別名 as col1,col2,col3……;

--針對上述NBA冠軍球隊年份排名案例，使用explode函式+lateral view側視圖，可以完美解決
select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as year;

--根據年份倒序排序
select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as year
order by b.year desc;

--統計每個球隊獲取總冠軍的次數 并且根據倒序排序
select a.team_name ,count(*) as nums
from the_nba_championship a lateral view explode(champion_year) b as year
group by a.team_name
order by nums desc;

10_Apache Hive 行列轉換多行轉單列（collect_list、concat_ws）

資料收集函式

collect_set --把多行資料收集為一行  回傳set集合  去重無序
collect_list --把多行資料收集為一行  回傳list集合  不去重有序

字串拼接函式

concat  --直接拼接字串
concat_ws --指定分隔符拼接

select concat("it","cast","And","heima");
select concat("it","cast","And",null);

select concat_ws("-","itcast","And","heima");
select concat_ws("-","itcast","And",null);

栗子

--原表
+----------------+----------------+----------------+--+
| row2col2.col1  | row2col2.col2  | row2col2.col3  |
+----------------+----------------+----------------+--+
| a              | b              | 1              |
| a              | b              | 2              |
| a              | b              | 3              |
| c              | d              | 4              |
| c              | d              | 5              |
| c              | d              | 6              |
+----------------+----------------+----------------+--+

--目標表
+-------+-------+--------+--+
| col1  | col2  |  col3  |
+-------+-------+--------+--+
| a     | b     | 1-2-3  |
| c     | d     | 4-5-6  |
+-------+-------+--------+--+

--建表
create table row2col2(
                         col1 string,
                         col2 string,
                         col3 int
)row format delimited fields terminated by '\t';

--加載資料到表中
load data local inpath '/root/hivedata/r2c2.txt' into table row2col2;
select * from row2col2;

--最終SQL實作
select
    col1,
    col2,
    concat_ws(',', collect_list(cast(col3 as string))) as col3
from
    row2col2
group by
    col1, col2;

11_Apache Hive 行列轉換單列轉多行（explode、lateral view）

技術原理： explode+lateral view

例子

--原表
+-------+-------+--------+--+
| col1  | col2  |  col3  |
+-------+-------+--------+--+
| a     | b     | 1,2,3  |
| c     | d     | 4,5,6  |
+-------+-------+--------+--+

--目標表
+----------------+----------------+----------------+--+
| row2col2.col1  | row2col2.col2  | row2col2.col3  |
+----------------+----------------+----------------+--+
| a              | b              | 1              |
| a              | b              | 2              |
| a              | b              | 3              |
| c              | d              | 4              |
| c              | d              | 5              |
| c              | d              | 6              |
+----------------+----------------+----------------+--+

--創建表
create table col2row2(
                         col1 string,
                         col2 string,
                         col3 string
)row format delimited fields terminated by '\t';

--加載資料
load data local inpath '/root/hivedata/c2r2.txt' into table col2row2;

select * from col2row2;

select explode(split(col3,',')) from col2row2;

--SQL最終實作
select
    col1,
    col2,
    lv.col3 as col3
from
    col2row2
        lateral view
            explode(split(col3, ',')) lv as col3;

12_Apache Hive json格式資料處理

在hive中，沒有json類的存在，一般使用string型別來修飾，叫做json字串，簡稱json串，

在hive中，處理json資料的兩種方式

hive內置了兩個用于決議json的函式

json_tuple
--是UDTF 表生成函式  輸入一行，輸出多行  一次提取讀個值  可以單獨使用 也可以配合lateral view側視圖使用

get_json_object
--是UDF普通函式，輸入一行 輸出一行 一次只能提取一個值 多次提取多次使用

使用JsonSerDe 類決議，在加載json資料到表中的時候完成決議動作

栗子

--創建表
create table tb_json_test1 (
    json string
);

--加載資料
load data local inpath '/root/hivedata/device.json' into table tb_json_test1;

select * from tb_json_test1;

-- get_json_object UDF函式 最大弊端是一次只能決議提取一個欄位
select
    --獲取設備名稱
    get_json_object(json,"$.device") as device,
    --獲取設備型別
    get_json_object(json,"$.deviceType") as deviceType,
    --獲取設備信號強度
    get_json_object(json,"$.signal") as signal,
    --獲取時間
    get_json_object(json,"$.time") as stime
from tb_json_test1;

--json_tuple 這是一個UDTF函式 可以一次決議提取多個欄位
--單獨使用 決議所有欄位
select
    json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
from tb_json_test1;

--搭配側視圖使用
select
    json,device,deviceType,signal,stime
from tb_json_test1
         lateral view json_tuple(json,"device","deviceType","signal","time") b
         as device,deviceType,signal,stime;


--方式2： 使用JsonSerDe類在建表的時候決議資料
--建表的時候直接使用JsonSerDe決議
create table tb_json_test2 (
                               device string,
                               deviceType string,
                               signal double,
                               `time` string
)
    ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED AS TEXTFILE;

load data local inpath '/root/hivedata/device.json' into table tb_json_test2;

select * from tb_json_test2;

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/423710.html

標籤：其他

上一篇：原始碼決議Spark各個ShuffleWriter的實作機制（二）——BypassMergeSortShuffleWriter

下一篇：Flink常見機制

2022-02-09大資料學習日志——Hadoop離線階段——Hive引數配置、函式應用