Apache Hive 系列文章

1、apache-hive-3.1.2簡介及部署（三種部署方式-內(nèi)嵌模式、本地模式和遠(yuǎn)程模式）及驗(yàn)證詳解
2、hive相關(guān)概念詳解–架構(gòu)、讀寫文件機(jī)制、數(shù)據(jù)存儲
3、hive的使用示例詳解-建表、數(shù)據(jù)類型詳解、內(nèi)部外部表、分區(qū)表、分桶表
4、hive的使用示例詳解-事務(wù)表、視圖、物化視圖、DDL(數(shù)據(jù)庫、表以及分區(qū))管理詳細(xì)操作
5、hive的load、insert、事務(wù)表使用詳解及示例
6、hive的select（GROUP BY、ORDER BY、CLUSTER BY、SORT BY、LIMIT、union、CTE）、join使用詳解及示例
7、hive shell客戶端與屬性配置、內(nèi)置運(yùn)算符、函數(shù)（內(nèi)置運(yùn)算符與自定義UDF運(yùn)算符）
8、hive的關(guān)系運(yùn)算、邏輯預(yù)算、數(shù)學(xué)運(yùn)算、數(shù)值運(yùn)算、日期函數(shù)、條件函數(shù)和字符串函數(shù)的語法與使用示例詳解
9、hive的explode、Lateral View側(cè)視圖、聚合函數(shù)、窗口函數(shù)、抽樣函數(shù)使用詳解
10、hive綜合示例：數(shù)據(jù)多分隔符（正則RegexSerDe）、url解析、行列轉(zhuǎn)換常用函數(shù)（case when、union、concat和explode）詳細(xì)使用示例
11、hive綜合應(yīng)用示例：json解析、窗口函數(shù)應(yīng)用（連續(xù)登錄、級聯(lián)累加、topN）、拉鏈表應(yīng)用
12、Hive優(yōu)化-文件存儲格式和壓縮格式優(yōu)化與job執(zhí)行優(yōu)化（執(zhí)行計(jì)劃、MR屬性、join、優(yōu)化器、謂詞下推和數(shù)據(jù)傾斜優(yōu)化）詳細(xì)介紹及示例
13、java api訪問hive操作示例

本文介紹hive shell客戶端的屬性配置、內(nèi)置運(yùn)算符和函數(shù)的內(nèi)容及使用示例。
本文依賴hive環(huán)境可用。
本文分為三個(gè)部分，即shell客戶端屬性配置、內(nèi)置的運(yùn)算符使用和函數(shù)使用。

一、客戶端與屬性配置

1、CLIs and Commands客戶端和命令

1）、Beeline CLI

$HIVE_HOME/bin/beeline被稱之為第二代客戶端或者新客戶端，是一個(gè)JDBC客戶端，是官方強(qiáng)烈推薦使用的Hive命令行工具，和第一代客戶端相比，性能加強(qiáng)安全性提高。
Beeline在嵌入式模式和遠(yuǎn)程模式下均可工作。

• 在嵌入式模式下，它運(yùn)行嵌入式Hive(類似于Hive CLI)；
• 遠(yuǎn)程模式下beeline通過Thrift連接到單獨(dú)的HiveServer2服務(wù)上，這也是官方推薦在生產(chǎn)環(huán)境中使用的模式。
• beeline客戶端介紹https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-Beeline%E2%80%93NewCommandLineShell
  常見的使用方式如下所示，在啟動hiveserver2服務(wù)的前提下使用beeline遠(yuǎn)程連接：

[alanchan@server4 bin]$ pwd
/usr/local/bigdata/apache-hive-3.1.2-bin/bin
[alanchan@server4 bin]$ beeline
Beeline version 3.1.2 by Apache Hive
beeline> ! connect jdbc:hive2://server4:10000
Connecting to jdbc:hive2://server4:10000
Enter username for jdbc:hive2://server4:10000: alanchan
Enter password for jdbc:hive2://server4:10000: ********
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://server4:10000>

2、Configuration Properties屬性配置

Hive配置屬性是在HiveConf.Java類中管理的，可以參考文件以獲取當(dāng)前使用版中可用的配置屬性列表；
從Hive 0.14.0開始，會從HiveConf.java類中直接生成配置模板文件hive-default.xml.template；
詳細(xì)的配置參數(shù)大全可以參考Hive官網(wǎng)配置參數(shù)
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

1）、方式一：hive-site.xml

在$HIVE_HOME/conf路徑下，可以添加一個(gè)hive-site.xml文件，把需要定義修改的配置屬性添加進(jìn)去，這個(gè)配置文件會影響到基于這個(gè)Hive安裝包的任何一種服務(wù)啟動、客戶端使用方式。比如使用MySQL作為元數(shù)據(jù)的存儲介質(zhì)，把連接MySQL的相關(guān)屬性配置在hive-site.xml文件中，這樣不管是本地模式還是遠(yuǎn)程模式啟動，不管客戶端本地連接還是遠(yuǎn)程連接，都將訪問同一個(gè)元數(shù)據(jù)存儲介質(zhì)。

<configuration>
    <!-- 存儲元數(shù)據(jù)mysql相關(guān)配置 -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value> jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
</configuration>

2）、方式二：–hiveconf命令行參數(shù)

hiveconf是一個(gè)命令行的參數(shù)，用于在使用Hive CLI或者Beeline CLI的時(shí)候指定配置參數(shù)。
這種方式的配置在整個(gè)的會話session中有效，會話結(jié)束，失效。
比如在啟動hive服務(wù)的時(shí)候，為了更好的查看啟動詳情，可以通過hiveconf參數(shù)修改日志級別：

hive --hiveconf hive.root.logger=DEBUG,console

3）、方式三：set命令

在Hive CLI或Beeline中使用set命令為set命令之后的所有SQL語句設(shè)置配置參數(shù)，這個(gè)也是會話級別的。
這種方式也是用戶日常開發(fā)中使用最多的一種配置參數(shù)方式。
因?yàn)镠ive倡導(dǎo)一種：誰需要、誰配置、誰使用的一種思想，避免你的屬性修改影響其他用戶的修改

#啟用hive動態(tài)分區(qū)，需要在hive會話中設(shè)置兩個(gè)參數(shù)：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

4）、方式四：服務(wù)特定配置文件

hivemetastore-site.xml、hiveserver2-site.xml
Hive Metastore會加載可用的hive-site.xml以及hivemetastore-site.xml配置文件。
HiveServer2會加載可用的hive-site.xml以及hiveserver2-site.xml。
如果HiveServer2以嵌入式模式使用元存儲，則還將加載hivemetastore-site.xml。

5）、配置方式選擇

• 配置方式優(yōu)先級

set設(shè)置 > hiveconf參數(shù) > hive-site.xml配置文件
set參數(shù)聲明會覆蓋命令行參數(shù)hiveconf，命令行參數(shù)會覆蓋配置文件hive-site.xml設(shè)定
日常開發(fā)使用中，如果不是核心的需要全局修改的參數(shù)屬性，建議使用set命令進(jìn)行設(shè)置

• Hive也會讀入Hadoop的配置，因?yàn)镠ive是作為Hadoop的客戶端啟動的，Hive的配置會覆蓋Hadoop的配置

二、Hive內(nèi)置運(yùn)算符

Hive支持的運(yùn)算符可以分為三大類：關(guān)系運(yùn)算、算術(shù)運(yùn)算、邏輯運(yùn)算。
官方參考文檔：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
也可以使用下述方式查看運(yùn)算符的使用方式

--顯示所有的函數(shù)和運(yùn)算符
show functions;
--查看運(yùn)算符或者函數(shù)的使用說明
describe function count;
--使用extended 可以查看更加詳細(xì)的使用說明
describe function extended count;

1、關(guān)系運(yùn)算符及示例

關(guān)系運(yùn)算符是二元運(yùn)算符，執(zhí)行的是兩個(gè)操作數(shù)的比較運(yùn)算。
每個(gè)關(guān)系運(yùn)算符都返回boolean類型結(jié)果（TRUE或FALSE）。

• 環(huán)境準(zhǔn)備

--1、創(chuàng)建表dual
create table dual(id string);
--2、加載一個(gè)文件dual.txt到dual表中
--dual.txt只有一行內(nèi)容：內(nèi)容為一個(gè)空格
load data local inpath '/root/hivedata/dual.txt' into table dual;
--3、在select查詢語句中使用dual表完成運(yùn)算符、函數(shù)功能測試
select 1+1 from dual;

select 1+1;

• 示例

----------------Hive中關(guān)系運(yùn)算符--------------------------
--is null空值判斷
select 1 from dual where 'itcast' is null;

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

--like比較： _表示任意單個(gè)字符 %表示任意數(shù)量字符
--否定比較： 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：確定字符串是否匹配正則表達(dá)式，是REGEXP_LIKE()的同義詞。
select 1 from dual where 'itcast' rlike '^i.*t$';
select 1 from dual where '123456' rlike '^\\d+$';  --判斷是否全為數(shù)字
select 1 from dual where '123456aa' rlike '^\\d+$';

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

2、算術(shù)運(yùn)算符及示例

-------------------Hive中算術(shù)運(yùn)算符---------------------------------
--取整操作: div  給出將A除以B所得的整數(shù)部分。例如17 div 3得出5。
select 17 div 3;

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

--位與操作: &  A和B按位進(jìn)行與操作的結(jié)果。 與表示兩個(gè)都為1則結(jié)果為1
select 4 & 8 from dual;  --4轉(zhuǎn)換二進(jìn)制：0100 8轉(zhuǎn)換二進(jìn)制：1000
select 6 & 4 from dual;  --4轉(zhuǎn)換二進(jìn)制：0100 6轉(zhuǎn)換二進(jìn)制：0110

--位或操作: |  A和B按位進(jìn)行或操作的結(jié)果  或表示有一個(gè)為1則結(jié)果為1
select 4 | 8 from dual;
select 6 | 4 from dual;

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

3、邏輯運(yùn)算符及示例

--3、Hive邏輯運(yùn)算符
--與操作: 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);
--邏輯是否存在: [NOT] EXISTS (subquery)
--將主查詢的數(shù)據(jù)，放到子查詢中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果（TRUE 或 FALSE）來決定主查詢的數(shù)據(jù)結(jié)果是否得以保留。
select A.* from A
where exists (select B.id from B where A.id = B.id);

--其他運(yùn)算符
select 'it' || 'cast';
select concat()

select `array`(11,22,33)
from dual;

三、Hive Functions函數(shù)

1、Hive 函數(shù)概述及分類標(biāo)準(zhǔn)

通過show functions命令在hive客戶端查看當(dāng)下可用的所有函數(shù)；
通過describe function extended funcname命令在hive客戶端查看函數(shù)的使用方式。

2、分類標(biāo)準(zhǔn)

1）、Hive函數(shù)分類

• Hive的函數(shù)分為兩大類：內(nèi)置函數(shù)（Built-in Functions）、用戶定義函數(shù)UDF（User-Defined Functions）
• 內(nèi)置函數(shù)可分為：數(shù)值類型函數(shù)、日期類型函數(shù)、字符串類型函數(shù)、集合函數(shù)、條件函數(shù)等；
• 用戶定義函數(shù)根據(jù)輸入輸出的行數(shù)可分為3類：UDF、UDAF、UDTF。
  

2）、根據(jù)函數(shù)輸入輸出的行數(shù)

• UDF（User-Defined-Function）普通函數(shù)，一進(jìn)一出
• UDAF（User-Defined Aggregation Function）聚合函數(shù)，多進(jìn)一出
• UDTF（User-Defined Table-Generating Functions）表生成函數(shù)，一進(jìn)多出
  

2、Hive 內(nèi)置函數(shù)

內(nèi)置函數(shù)（build-in）指的是Hive已實(shí)現(xiàn)好，直接可以使用的函數(shù)，也叫做內(nèi)建函數(shù)。
官方文檔地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
內(nèi)置函數(shù)根據(jù)應(yīng)用歸類整體可以分為8大種類型。

1）、String Functions 字符串函數(shù)示例

------------String Functions 字符串函數(shù)------------
select concat("angela","baby");
--帶分隔符字符串連接函數(shù)：concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('win', 'com'));
--字符串截取函數(shù)：substr(str, pos[, len]) 或者  substring(str, pos[, len])
select substr("angelababy",-4); --pos是從1開始的索引，如果為負(fù)數(shù)則倒著數(shù)
select substr("angelababy",7,4);
0: jdbc:hive2://server4:10000> select substr("angelababy",-4); 
+-------+
|  _c0  |
+-------+
| baby  |
+-------+
0: jdbc:hive2://server4:10000> select substr("angelababy",7,4);
+-------+
|  _c0  |
+-------+
| baby  |
+-------+

--正則表達(dá)式替換函數(shù)：regexp_replace(str, regexp, rep)
select regexp_replace('100-200', '(\\d+)', 'num');
--正則表達(dá)式解析函數(shù)：regexp_extract(str, regexp[, idx]) 提取正則匹配到的指定組內(nèi)容
select regexp_extract('100-200', '(\\d+)-(\\d+)', 2);
--URL解析函數(shù)：parse_url 注意要想一次解析出多個(gè) 可以使用parse_url_tuple這個(gè)UDTF函數(shù)
select parse_url('http://www.win.com/path/p1.php?query=1', 'HOST');
--分割字符串函數(shù): split(str, regex)
select split('apache hive', '\\s+');
--json解析函數(shù)：get_json_object(json_txt, path)
--$表示json對象
select get_json_object('[{"website":"www.win.com","name":"allenwoon"}, {"website":"cloud.win.com","name":"carbondata 中文文檔"}]', '$.[1].website');
0: jdbc:hive2://server4:10000> select get_json_object('[{"website":"www.win.com","name":"allenwoon"}, {"website":"cloud.win.com","name":"carbondata 中文文檔"}]', '$.[1].website');
+----------------+
|      _c0       |
+----------------+
| cloud.win.com  |
+----------------+

--字符串長度函數(shù)：length(str | binary)
select length("angelababy");
--字符串反轉(zhuǎn)函數(shù)：reverse
select reverse("angelababy");
--字符串連接函數(shù)：concat(str1, str2, ... strN)
--字符串轉(zhuǎn)大寫函數(shù)：upper,ucase
select upper("angelababy");
select ucase("angelababy");
--字符串轉(zhuǎn)小寫函數(shù)：lower,lcase
select lower("ANGELABABY");
select lcase("ANGELABABY");
--去空格函數(shù)：trim 去除左右兩邊的空格
select trim(" angelababy ");
--左邊去空格函數(shù)：ltrim
select ltrim(" angelababy ");
--右邊去空格函數(shù)：rtrim
select rtrim(" angelababy ");
--空格字符串函數(shù)：space(n) 返回指定個(gè)數(shù)空格
select space(4);
--重復(fù)字符串函數(shù)：repeat(str, n) 重復(fù)str字符串n次
select repeat("angela",2);
--首字符ascii函數(shù)：ascii
select ascii("angela");  --a對應(yīng)ASCII 97
--左補(bǔ)足函數(shù)：lpad
select lpad('hi', 5, '??');  --???hi
select lpad('hi', 1, '??');  --h
--右補(bǔ)足函數(shù)：rpad
select rpad('hi', 5, '??');
--集合查找函數(shù): find_in_set(str,str_array)
select find_in_set('a','abc,b,ab,c,def');

2）、Date Functions 日期函數(shù)

--獲取當(dāng)前日期: current_date
select current_date();
--獲取當(dāng)前時(shí)間戳: current_timestamp
--同一查詢中對current_timestamp的所有調(diào)用均返回相同的值。
select current_timestamp();
--獲取當(dāng)前UNIX時(shí)間戳函數(shù): unix_timestamp
select unix_timestamp();
--日期轉(zhuǎn)UNIX時(shí)間戳函數(shù): unix_timestamp
select unix_timestamp("2022-10-21 17:47:21");
--指定格式日期轉(zhuǎn)UNIX時(shí)間戳函數(shù): unix_timestamp
select unix_timestamp('20221021 17:47:21','yyyyMMdd HH:mm:ss');
--UNIX時(shí)間戳轉(zhuǎn)日期函數(shù): from_unixtime
select from_unixtime(1666374441);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
0: jdbc:hive2://server4:10000> select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
+----------------------+
|         _c0          |
+----------------------+
| 1970-01-01 00:00:00  |
+----------------------+
--日期比較函數(shù): datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2022-10-21','2023-10-21');
0: jdbc:hive2://server4:10000> select datediff('2022-10-21','2023-10-21');
+-------+
|  _c0  |
+-------+
| -365  |
+-------+
--日期增加函數(shù): date_add
select date_add('2022-9-21',10);
0: jdbc:hive2://server4:10000> select date_add('2022-9-21',10);
+-------------+
|     _c0     |
+-------------+
| 2022-10-01  |
+-------------+

--日期減少函數(shù): date_sub
select date_sub('2022-10-01',10);


--抽取日期函數(shù): to_date
select to_date('2022-10-21 17:47:21');
--日期轉(zhuǎn)年函數(shù): year
select year('2022-10-21 17:47:21');
--日期轉(zhuǎn)月函數(shù): month
select month('2022-10-21 17:47:21');
--日期轉(zhuǎn)天函數(shù): day
select day('2022-10-21 17:47:21');
--日期轉(zhuǎn)小時(shí)函數(shù): hour
select hour('2022-10-21 17:47:21');
--日期轉(zhuǎn)分鐘函數(shù): minute
select minute('2022-10-21 17:47:21');
--日期轉(zhuǎn)秒函數(shù): second
select second('2022-10-21 17:47:21');
--日期轉(zhuǎn)周函數(shù): weekofyear 返回指定日期所示年份第幾周
select weekofyear('2022-10-21 17:47:21');

3）、Mathematical Functions 數(shù)學(xué)函數(shù)

--取整函數(shù): round  返回double類型的整數(shù)值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函數(shù): round(double a, int d) 返回指定精度d的double類型
select round(3.1415926,4);
--向下取整函數(shù): floor
select floor(3.1415926);
select floor(-3.1415926);
--向上取整函數(shù): ceil
select ceil(3.1415926);
select ceil(-3.1415926);
--取隨機(jī)數(shù)函數(shù): rand 每次執(zhí)行都不一樣 返回一個(gè)0到1范圍內(nèi)的隨機(jī)數(shù)
select rand();
--指定種子取隨機(jī)數(shù)函數(shù): rand(int seed) 得到一個(gè)穩(wěn)定的隨機(jī)數(shù)序列
select rand(3);

0: jdbc:hive2://server4:10000> select round(3.1415926);
+------+
| _c0  |
+------+
| 3    |
+------+
0: jdbc:hive2://server4:10000> select round(3.1415926,4);
+---------+
|   _c0   |
+---------+
| 3.1416  |
+---------+
0: jdbc:hive2://server4:10000> select floor(3.1415926);
+------+
| _c0  |
+------+
| 3    |
+------+
0: jdbc:hive2://server4:10000> select floor(-3.1415926);
+------+
| _c0  |
+------+
| -4   |
+------+
0: jdbc:hive2://server4:10000> select ceil(3.1415926);
+------+
| _c0  |
+------+
| 4    |
+------+
0: jdbc:hive2://server4:10000> select ceil(-3.1415926);
+------+
| _c0  |
+------+
| -3   |
+------+
0: jdbc:hive2://server4:10000> select rand();
+----------------------+
|         _c0          |
+----------------------+
| 0.45830703128392947  |
+----------------------+
0: jdbc:hive2://server4:10000> select rand(3);
+--------------------+
|        _c0         |
+--------------------+
| 0.731057369148862  |
+--------------------+

4）、二進(jìn)制函數(shù)

bin(BIGINT a)
select bin(18);
--進(jìn)制轉(zhuǎn)換函數(shù): conv(BIGINT num, int from_base, int to_base)
select conv(17,10,16);
--絕對值函數(shù): abs
select abs(-3.9);

0: jdbc:hive2://server4:10000> select bin(18);
+--------+
|  _c0   |
+--------+
| 10010  |
+--------+
0: jdbc:hive2://server4:10000> select conv(17,10,16);
+------+
| _c0  |
+------+
| 11   |
+------+
0: jdbc:hive2://server4:10000> select abs(-3.9);
+------+
| _c0  |
+------+
| 3.9  |
+------+

5）、Collection Functions 集合函數(shù)

--集合元素size函數(shù): 
size(Map<K.V>) 
size(Array<T>)
select size(`array`(11,22,33));
select size(`map`("id",10086,"name","zhangsan","age",18));

--取map集合keys函數(shù): 
map_keys(Map<K.V>)
select map_keys(`map`("id",10086,"name","zhangsan","age",18));
0: jdbc:hive2://server4:10000> select map_keys(`map`("id",10086,"name","zhangsan","age",18));
+----------------------+
|         _c0          |
+----------------------+
| ["id","name","age"]  |
+----------------------+

--取map集合values函數(shù): 
map_values(Map<K.V>)
select map_values(`map`("id",10086,"name","zhangsan","age",18));

--判斷數(shù)組是否包含指定元素: 
array_contains(Array<T>, value)
select array_contains(`array`(11,22,33),11);
select array_contains(`array`(11,22,33),66);

--數(shù)組排序函數(shù):sort_array(Array<T>)
select sort_array(`array`(12,2,32));

6）、Conditional Functions 條件函數(shù)

select * from student limit 3;

describe function extended isnull;

--if條件判斷: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select *,if(sex ='男','M','W') from student limit 3;
0: jdbc:hive2://server4:10000> select if(sex ='男','M','W') from student limit 3;
+------+
| _c0  |
+------+
| M    |
| W    |
| W    |
+------+
3 rows selected (0.107 seconds)
0: jdbc:hive2://server4:10000> select *,if(sex ='男','M','W') from student limit 3;
+--------------+---------------+--------------+--------------+---------------+------+
| student.num  | student.name  | student.sex  | student.age  | student.dept  | _c1  |
+--------------+---------------+--------------+--------------+---------------+------+
| 95001        | 李勇            | 男            | 20           | CS            | M    |
| 95002        | 劉晨            | 女            | 19           | IS            | W    |
| 95003        | 王敏            | 女            | 22           | MA            | W    |
+--------------+---------------+--------------+--------------+---------------+------+


--空判斷函數(shù): isnull( a )
select isnull("allen");
select isnull(null);

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

--空值轉(zhuǎn)換函數(shù): nvl(T value, T default_value)
select nvl("alan","win");
select nvl(null,"win");

--非空查找函數(shù): COALESCE(T v1, T v2, ...)
--返回參數(shù)中的第一個(gè)非空值；如果所有值都為NULL，那么返回NULL
select COALESCE(null,11,22,33);
select COALESCE(null,null,null,33);
select COALESCE(null,null,null);
0: jdbc:hive2://server4:10000> select COALESCE(null,11,22,33);
+------+
| _c0  |
+------+
| 11   |
+------+

--條件轉(zhuǎn)換函數(shù): 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;
select *,
    case sex 
        when '男' then 'male' 
            else 'female' end 
from student limit 3;
0: jdbc:hive2://server4:10000> select case sex when '男' then 'male' else 'female' end from student limit 3;
+---------+
|   _c0   |
+---------+
| male    |
| female  |
| female  |
+---------+
3 rows selected (0.095 seconds)
0: jdbc:hive2://server4:10000> select *,case sex when '男' then 'male' else 'female' end from student limit 3;
+--------------+---------------+--------------+--------------+---------------+---------+
| student.num  | student.name  | student.sex  | student.age  | student.dept  |   _c1   |
+--------------+---------------+--------------+--------------+---------------+---------+
| 95001        | 李勇            | 男            | 20           | CS            | male    |
| 95002        | 劉晨            | 女            | 19           | IS            | female  |
| 95003        | 王敏            | 女            | 22           | MA            | female  |
+--------------+---------------+--------------+--------------+---------------+---------+

--nullif( a, b ):
-- 如果a = b，則返回NULL，否則返回一個(gè)
select nullif(11,11);
select nullif(11,12);

--assert_true(condition)
--如果'condition'不為真，則引發(fā)異常，否則返回null
SELECT assert_true(11 >= 0);
SELECT assert_true(-1 >= 0);

7）、Type Conversion Functions 類型轉(zhuǎn)換函數(shù)

--任意數(shù)據(jù)類型之間轉(zhuǎn)換:cast
select cast(12.14 as bigint);
select cast(12.14 as string);
select cast("hello" as int);
0: jdbc:hive2://server4:10000> select cast("hello" as int);
+-------+
|  _c0  |
+-------+
| NULL  |
+-------+

8）、Data Masking Functions 數(shù)據(jù)脫敏函數(shù)

--mask
--將查詢回的數(shù)據(jù)，大寫字母轉(zhuǎn)換為X，小寫字母轉(zhuǎn)換為x，數(shù)字轉(zhuǎn)換為n。
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); --自定義替換的字母
0: jdbc:hive2://server4:10000> select mask("abc123DEF");
+------------+
|    _c0     |
+------------+
| xxxnnnXXX  |
+------------+
1 row selected (0.158 seconds)
0: jdbc:hive2://server4:10000> select mask("abc123DEF",'-','.','^'); 
+------------+
|    _c0     |
+------------+
| ...^^^---  |
+------------+

--mask_first_n(string str[, int n]
--對前n個(gè)進(jì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個(gè)字符，其余進(jì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");

9）、Misc. Functions 其他雜項(xiàng)函數(shù)

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

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

--取哈希值函數(shù):hash
select hash("alan");

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

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

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

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

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

0: jdbc:hive2://server4:10000> select java_method("java.lang.Math","max",11,22);
+------+
| _c0  |
+------+
| 22   |
+------+
0: jdbc:hive2://server4:10000> select reflect("java.lang.Math","max",11,22);
+------+
| _c0  |
+------+
| 22   |
+------+
0: jdbc:hive2://server4:10000> select hash("alan");
+----------+
|   _c0    |
+----------+
| 2996632  |
+----------+
0: jdbc:hive2://server4:10000> select sha1("alan");
+-------------------------------------------+
|                    _c0                    |
+-------------------------------------------+
| 91e38e63b890fbb214c8914809fde03c73e7f24d  |
+-------------------------------------------+
0: jdbc:hive2://server4:10000> select md5("alan");
+-----------------------------------+
|                _c0                |
+-----------------------------------+
| 02558a70324e7c4f269c69825450cec8  |
+-----------------------------------+

3、Hive 用戶自定義函數(shù)（UDF、UDTF、UDAF）

用戶自定義函數(shù)簡稱UDF，源自于英文user-defined function。
根據(jù)函數(shù)輸入輸出的行數(shù)可以分為3類，分別是：

• UDF（User-Defined-Function）普通函數(shù)，一進(jìn)一出
• UDAF（User-Defined Aggregation Function）聚合函數(shù)，多進(jìn)一出
• UDTF（User-Defined Table-Generating Functions）表生成函數(shù)，一進(jìn)多出
  

1）、示例需求：UDF實(shí)現(xiàn)手機(jī)號****加密

對于敏感數(shù)據(jù)往往需要進(jìn)行脫敏處理。比如手機(jī)號

• 能夠?qū)斎霐?shù)據(jù)進(jìn)行非空判斷、手機(jī)號位數(shù)判斷
• 能夠?qū)崿F(xiàn)校驗(yàn)手機(jī)號格式，把滿足規(guī)則的進(jìn)行****處理
• 對于不符合手機(jī)號規(guī)則的數(shù)據(jù)直接返回，不處理

2）、UDF實(shí)現(xiàn)步驟

1. 寫一個(gè)java類，繼承UDF，并重載evaluate方法，方法中實(shí)現(xiàn)函數(shù)的業(yè)務(wù)邏輯（重載意味著可以在一個(gè)java類中實(shí)現(xiàn)多個(gè)函數(shù)功能）
2. 程序打成jar包，上傳HS2服務(wù)器本地或者HDFS
3. 客戶端命令行中添加jar包到Hive的classpath： hive>add JAR /xxxx/udf.jar
4. 注冊成為臨時(shí)函數(shù)（給UDF命名）：create temporary function 函數(shù)名 as ‘UDF類全路徑’
5. HQL中使用函數(shù)

3）、pom.xml

<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>3.1.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>3.1.4</version>
    </dependency>
</dependencies>

4）、UDF和GenericUDF實(shí)現(xiàn)

UDF函數(shù)按照繼承類可以分為2種，一個(gè)是UDF，一個(gè)是GenericUDF，GenericUDF的開發(fā)會比UDF復(fù)雜一些，一般在以下幾種場景下考慮使用GenericUDF：

• 傳參情況復(fù)雜
  比如某UDF要傳參數(shù)有多種數(shù)量或多種類型的情況，在UDF中支持這種場景我們需要實(shí)現(xiàn)N個(gè)不同的evaluate()方法分別對應(yīng)N種場景的傳參，在GenericUDF我們只需在一個(gè)方法內(nèi)加上判斷邏輯，對不同的輸入路由到不同的處理邏輯上即可。還有比如某UDF參數(shù)既要支持String list參數(shù)，也要支持Integer list參數(shù)。Java不支持同一個(gè)方法重載參數(shù)只有泛型類型不一樣，所以該場景只能用GenericUDF。

• 需要傳非Writable的或復(fù)雜數(shù)據(jù)類型作為參數(shù)
  比如嵌套數(shù)據(jù)結(jié)構(gòu)，傳入Map的key-value中的value為list數(shù)據(jù)類型，或者比如數(shù)據(jù)域數(shù)量不確定的Struct結(jié)構(gòu)，都更適合使用GenericUDF在運(yùn)行時(shí)捕獲數(shù)據(jù)的內(nèi)部構(gòu)造

• 該UDF被大量、高頻地使用
  從收益上考慮，會盡可能地優(yōu)化一切可以優(yōu)化的地方，則GenericUDF相比UDF在operator中避免了多次反射轉(zhuǎn)化的資源消耗，更適合被考慮

• 該UDF函數(shù)功能未來預(yù)期的重構(gòu)、擴(kuò)展場景較多，需要做得足夠可擴(kuò)展，則GenericUDF在這方面更優(yōu)秀

• UDF

public class EncryptPhoneNumber extends UDF {
    /**
     * 重載evaluate方法 實(shí)現(xiàn)函數(shù)的業(yè)務(wù)邏輯
     * @param phoNum  入?yún)ⅲ何醇用苁謾C(jī)號
     * @return 返回：加密后的手機(jī)號字符串
     */
    public String evaluate(String phoNum){
        String encryptPhoNum = null;
        //手機(jī)號不為空 并且為11位
        if (StringUtils.isNotEmpty(phoNum) && phoNum.trim().length() == 11 ) {
            //判斷數(shù)據(jù)是否滿足中國大陸手機(jī)號碼規(guī)范
                String regex = "^(1[3-9]\\d{9}$)";
            Pattern p = Pattern.compile(regex);
            Matcher m = p.matcher(phoNum);
            if (m.matches()) {//進(jìn)入這里都是符合手機(jī)號規(guī)則的
                //使用正則替換 返回加密后數(shù)據(jù)
                encryptPhoNum = phoNum.trim().replaceAll("(\\d{3})\\d{4}(\\d{4})","$1****$2");
            }else{
                //不符合手機(jī)號規(guī)則 數(shù)據(jù)直接原封不動返回
                encryptPhoNum = phoNum;
            }
        }else{
            //不符合11位 數(shù)據(jù)直接原封不動返回
            encryptPhoNum = phoNum;
        }
        return encryptPhoNum;
    }
}

• GenericUDF

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.JavaStringObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector;

public class EncryptPhoneNumber extends GenericUDF {
	StringObjectInspector elementOI;
	/**
	 * Initialize this GenericUDF. This will be called once and only once per
	 * GenericUDF instance.
	 */
	@Override
	public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
		// 1. 檢查該記錄是否傳過來正確的參數(shù)數(shù)量
		if (arguments.length != 1) {
			throw new UDFArgumentException("輸入?yún)?shù)錯(cuò)誤，必須是一個(gè)參數(shù)。");
		}
		// 2. 檢查該條記錄是否傳過來正確的參數(shù)類型
		ObjectInspector a = arguments[0];
		if (!(a instanceof StringObjectInspector)) {
			throw new UDFArgumentException("輸入?yún)?shù)錯(cuò)誤，需要是一個(gè)字符串");
		}

		// 3. 檢查通過后，將參數(shù)賦值給成員變量ObjectInspector，為了在evaluate()中使用
		this.elementOI = (StringObjectInspector) a;
		return PrimitiveObjectInspectorFactory.javaStringObjectInspector;
	}

	/**
	 * Evaluate the GenericUDF with the arguments. 重載evaluate方法 實(shí)現(xiàn)函數(shù)的業(yè)務(wù)邏輯
	 */
	@Override
	public Object evaluate(DeferredObject[] arguments) throws HiveException {
		String phoNum = elementOI.getPrimitiveJavaObject(arguments[0].get()).toString();
		String encryptPhoNum = null;
		// 手機(jī)號不為空 并且為11位
		if (StringUtils.isNotEmpty(phoNum) && phoNum.trim().length() == 11) {
			// 判斷數(shù)據(jù)是否滿足中國大陸手機(jī)號碼規(guī)范
			String regex = "^(1[3-9]\\d{9}$)";
			Pattern p = Pattern.compile(regex);
			Matcher m = p.matcher(phoNum);
			if (m.matches()) {// 進(jìn)入這里都是符合手機(jī)號規(guī)則的
				// 使用正則替換 返回加密后數(shù)據(jù)
				encryptPhoNum = phoNum.trim().replaceAll("(\\d{3})\\d{4}(\\d{4})", "$1****$2");
			} else {
				// 不符合手機(jī)號規(guī)則 數(shù)據(jù)直接原封不動返回
				encryptPhoNum = phoNum;
			}
		} else {
			// 不符合11位 數(shù)據(jù)直接原封不動返回
			encryptPhoNum = phoNum;
		}
		return encryptPhoNum;
	}

	/**
	 * Get the String to be displayed in explain.
	 */
	@Override
	public String getDisplayString(String[] children) {
		return "this is a EncryptPhoneNumber pro.";
	}

	public static void main(String[] args) throws Exception {
		EncryptPhoneNumber ep = new EncryptPhoneNumber();
		JavaStringObjectInspector stringOI = PrimitiveObjectInspectorFactory.javaStringObjectInspector;

		JavaStringObjectInspector resultInspector = (JavaStringObjectInspector) ep.initialize(new ObjectInspector[] { stringOI });

		Object result = ep.evaluate(new DeferredObject[] { new DeferredJavaObject("13917885967") });
		System.out.println("result:" + result);
	}

}

5）、打包

mvn package clean -Dmaven.test.skip=true

mvn package -Dmaven.test.skip=true

6）、添加jar包到Hive的classpath

0: jdbc:hive2://server4:10000> add jar /usr/local/bigdata/hive-0.0.1-SNAPSHOT.jar;
No rows affected (0.01 seconds)

7）、注冊成為臨時(shí)函數(shù)

create temporary function 函數(shù)名 as 'UDF類全路徑';

create temporary function encryptPhoneNumber as 'org.hive.udf.EncryptPhoneNumber';

0: jdbc:hive2://server4:10000> create temporary function encryptPhoneNumber as 'org.hive.udf.EncryptPhoneNumber';
No rows affected (0.023 seconds)

8）、驗(yàn)證

0: jdbc:hive2://server4:10000> select encryptPhoneNumber("13788889999");
+--------------+
|     _c0      |
+--------------+
| 137****9999  |
+--------------+

以上，完成了hive shell客戶端的屬性配置、內(nèi)置運(yùn)算符和函數(shù)的介紹及使用示例。文章來源地址http://www.zghlxwxcb.cn/news/detail-704464.html

到了這里，關(guān)于7、hive shell客戶端與屬性配置、內(nèi)置運(yùn)算符、函數(shù)（內(nèi)置運(yùn)算符與自定義UDF運(yùn)算符）的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！