Hive參數(shù)配置與函數(shù)、運(yùn)算符使用

Hive參數(shù)配置與函數(shù)、運(yùn)算符使用Hive客戶端與屬性配置Hive內(nèi)置運(yùn)算符Hive函數(shù)入門Hive函數(shù)高階掌握Hive命令行及參數(shù)配置方式理解Hive內(nèi)置運(yùn)算符的使用掌握Hive常見的內(nèi)置函數(shù)使用掌握用戶自定義函數(shù)UDF開發(fā)理解UDTF函數(shù)、LateralView側(cè)視圖掌握Hive中基礎(chǔ)聚合、理解增強(qiáng)聚合掌握Hive中的窗口函數(shù)使用了解HiveSampling抽樣函數(shù)Hive客戶端與屬性配置01CLIsandCommands客戶端和命令ConfigurationProperties屬性配置$HIVE_HOME/bin/hive是一個shellUtil,通常稱之為hive的第一代客戶端或者舊客戶端，主要功能有兩個：交互式或批處理模式運(yùn)行Hive查詢。注意，此時作為客戶端，需要并且能夠訪問的是Hivemetastore服務(wù)，而不是hiveserver2服務(wù)。hive相關(guān)服務(wù)的啟動，比如metastore服務(wù)。CLIsandCommandsHiveCLI可以通過運(yùn)行"hive-H"或者"hive--help"來查看命令行選項(xiàng)。標(biāo)記為紅色的為重要的參數(shù)。CLIsandCommandsHiveCLI-e<quoted-query-string>

執(zhí)行命令行-e參數(shù)后指定的sql語句運(yùn)行完退出。

-f<filename>

執(zhí)行命令行-f參數(shù)后指定的sql文件運(yùn)行完退出。

-H,--help

打印幫助信息

--hiveconf<property=value>

設(shè)置參數(shù)

-S,--silent

靜默模式

-v,--verbose

詳細(xì)模式，將執(zhí)行sql回顯到console

--serviceservice_name

啟動hive的相關(guān)服務(wù)功能一：BatchMode批處理模式當(dāng)使用-e或-f選項(xiàng)運(yùn)行bin/hive時，它將以批處理模式執(zhí)行SQL命令。所謂的批處理可以理解為一次性執(zhí)行，執(zhí)行完畢退出。CLIsandCommandsHiveCLI#-------BatchMode批處理模式-----------

#-e

$HIVE_HOME/bin/hive-e'showdatabases'

#-f

cd~

#編輯一個sql文件里面寫上合法正確的sql語句

vimhive.sql

showdatabases;

#執(zhí)行從客戶端所在機(jī)器的本地磁盤加載文件

$HIVE_HOME/bin/hive-f/root/hive.sql

#也可以從其他文件系統(tǒng)加載sql文件執(zhí)行

$HIVE_HOME/bin/hive-fhdfs://<namenode>:<port>/hive-script.sql

$HIVE_HOME/bin/hive-fs3://mys3bucket/s3-script.sql

#-i進(jìn)入交互模式之前運(yùn)行初始化腳本

$HIVE_HOME/bin/hive-i/home/my/hive-init.sql

#使用靜默模式將數(shù)據(jù)從查詢中轉(zhuǎn)儲到文件中

$HIVE_HOME/bin/hive-S-e'select*fromitheima.student'>a.txt功能二：InteractiveShell交互式模式所謂交互式模式可以理解為客戶端和hive服務(wù)一直保持連接，除非手動退出客戶端。CLIsandCommandsHiveCLI/export/server/hive/bin/hive

hive>showdatabases;

OK

default

itcast

itheima

Timetaken:0.028seconds,Fetched:3row(s)

hive>useitcast;

OK

Timetaken:0.027seconds

hive>exit;功能三：啟動Hive服務(wù)比如metastore服務(wù)和hiveserver2服務(wù)的啟動。CLIsandCommandsHiveCLI#----------啟動服務(wù)-------------------

#--hiveconf

$HIVE_HOME/bin/hive--hiveconfhive.root.logger=DEBUG,console

#--service

$HIVE_HOME/bin/hive--servicemetastore

$HIVE_HOME/bin/hive--servicehiveserver2$HIVE_HOME/bin/beeline被稱之為第二代客戶端或者新客戶端，是一個JDBC客戶端，是官方強(qiáng)烈推薦使用的Hive命令行工具。和第一代客戶端相比，性能加強(qiáng)安全性提高。Beeline在嵌入式模式和遠(yuǎn)程模式下均可工作。在嵌入式模式下，它運(yùn)行嵌入式Hive(類似于HiveCLI)；遠(yuǎn)程模式下beeline通過Thrift連接到單獨(dú)的HiveServer2服務(wù)上，這也是官方推薦在生產(chǎn)環(huán)境中使用的模式。CLIsandCommandsBeelineCLIBeeline支持的參數(shù)非常多，可以通過官方文檔進(jìn)行查詢https:///confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-Beeline%E2%80%93NewCommandLineShellCLIsandCommandsBeelineCLI客戶端常見的使用方式如下所示：在啟動hiveserver2服務(wù)的前提下使用beeline遠(yuǎn)程連接HS2服務(wù)。CLIsandCommandsBeelineCLI客戶端[root@node3~]#/export/server/hive/bin/beeline

Beelineversion3.1.2byApacheHive

beeline>!connectjdbc:hive2://node1:10000

Connectingtojdbc:hive2://node1:10000

Enterusernameforjdbc:hive2://node1:10000:root

Enterpasswordforjdbc:hive2://node1:10000:

Connectedto:ApacheHive(version3.1.2)

Driver:HiveJDBC(version3.1.2)

Transactionisolation:TRANSACTION_REPEATABLE_READ

0:jdbc:hive2://node1:10000>CLIsandCommands客戶端和命令ConfigurationProperties屬性配置Hive除了默認(rèn)的屬性配置之外，還支持用戶使用時修改配置；修改Hive配置之前，作為用戶需要掌握兩件事：有哪些屬性支持用戶修改，屬性的功能、作用是什么；支持哪種方式進(jìn)行修改，是臨時生效還是永久生效的。HiveConfigurationProperties概述Hive配置屬性是在HiveConf.Java類中管理的，可以參考文件以獲取當(dāng)前使用版中可用的配置屬性列表；從Hive0.14.0開始，會從HiveConf.java類中直接生成配置模板文件hive-default.xml.template；詳細(xì)的配置參數(shù)大全可以參考Hive官網(wǎng)配置參數(shù)，在頁面使用ctrl+f進(jìn)行搜索。/confluence/display/Hive/Configuration+PropertiesHiveConfigurationProperties概述在$HIVE_HOME/conf路徑下，可以添加一個hive-site.xml文件，把需要定義修改的配置屬性添加進(jìn)去，這個配置文件會影響到基于這個Hive安裝包的任何一種服務(wù)啟動、客戶端使用方式。比如使用MySQL作為元數(shù)據(jù)的存儲介質(zhì)，把連接MySQL的相關(guān)屬性配置在hive-site.xml文件中，這樣不管是本地模式還是遠(yuǎn)程模式啟動，不管客戶端本地連接還是遠(yuǎn)程連接，都將訪問同一個元數(shù)據(jù)存儲介質(zhì)。HiveConfigurationProperties方式一：hive-site.xml<configuration>

<!--存儲元數(shù)據(jù)mysql相關(guān)配置-->

<property>

<name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value>

</property>

<property>

<name>javax.jdo.option.ConnectionDriverName</name>

<value>com.mysql.jdbc.Driver</value>

</property>

</configuration>hiveconf是一個命令行的參數(shù)，用于在使用HiveCLI或者BeelineCLI的時候指定配置參數(shù)。這種方式的配置在整個的會話session中有效，會話結(jié)束，失效。比如在啟動hive服務(wù)的時候，為了更好的查看啟動詳情，可以通過hiveconf參數(shù)修改日志級別：HiveConfigurationProperties方式二：--hiveconf命令行參數(shù)$HIVE_HOME/bin/hive--hiveconfhive.root.logger=DEBUG,console在HiveCLI或Beeline中使用set命令為set命令之后的所有SQL語句設(shè)置配置參數(shù)，這個也是會話級別的。這種方式也是用戶日常開發(fā)中使用最多的一種配置參數(shù)方式。因?yàn)镠ive倡導(dǎo)一種：誰需要、誰配置、誰使用的一種思想，避免你的屬性修改影響其他用戶的修改。HiveConfigurationProperties方式三：set命令#啟用hive動態(tài)分區(qū)，需要在hive會話中設(shè)置兩個參數(shù)：

sethive.exec.dynamic.partition=true;

sethive.exec.dynamic.partition.mode=nonstrict;hivemetastore-site.xml、hiveserver2-site.xmlHiveMetastore會加載可用的hive-site.xml以及hivemetastore-site.xml配置文件。HiveServer2會加載可用的hive-site.xml以及hiveserver2-site.xml。如果HiveServer2以嵌入式模式使用元存儲，則還將加載hivemetastore-site.xml。HiveConfigurationProperties方式四：服務(wù)特定配置文件配置方式優(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的配置HiveConfigurationProperties總結(jié)Hive內(nèi)置運(yùn)算符02整體上，Hive支持的運(yùn)算符可以分為三大類：關(guān)系運(yùn)算、算術(shù)運(yùn)算、邏輯運(yùn)算。官方參考文檔：/confluence/display/Hive/LanguageManual+UDF也可以使用下述方式查看運(yùn)算符的使用方式。Hive內(nèi)置運(yùn)算符概述--顯示所有的函數(shù)和運(yùn)算符

showfunctions;

--查看運(yùn)算符或者函數(shù)的使用說明

describefunctioncount;

--使用extended可以查看更加詳細(xì)的使用說明

describefunctionextendedcount;在Hive中創(chuàng)建一個空表dual，用于測試各種運(yùn)算符的功能。Hive內(nèi)置運(yùn)算符測試環(huán)境準(zhǔn)備--1、創(chuàng)建表dual

createtabledual(idstring);

--2、加載一個文件dual.txt到dual表中

--dual.txt只有一行內(nèi)容：內(nèi)容為一個空格

--3、在select查詢語句中使用dual表完成運(yùn)算符、函數(shù)功能測試

select1+1fromdual;

關(guān)系運(yùn)算符算術(shù)運(yùn)算符邏輯運(yùn)算符關(guān)系運(yùn)算符是二元運(yùn)算符，執(zhí)行的是兩個操作數(shù)的比較運(yùn)算。每個關(guān)系運(yùn)算符都返回boolean類型結(jié)果（TRUE或FALSE）。Hive內(nèi)置運(yùn)算符關(guān)系運(yùn)算符?等值比較:=、==?不等值比較:<>、!=?小于比較:<?小于等于比較:<=?大于比較:>?大于等于比較:>=?空值判斷:ISNULL?非空判斷:ISNOTNULL?LIKE比較:LIKE?JAVA的LIKE操作:RLIKE?REGEXP操作:REGEXPHive內(nèi)置運(yùn)算符關(guān)系運(yùn)算符--1、Hive中關(guān)系運(yùn)算符

--isnull空值判斷

select1fromdualwhere'itcast'isnull;

--isnotnull非空值判斷

select1fromdualwhere'itcast'isnotnull;

--like比較：_表示任意單個字符%表示任意數(shù)量字符

--否定比較：NOTAlikeB

select1fromdualwhere'itcast'like'it_';

select1fromdualwhere'itcast'like'it%';

select1fromdualwherenot'itcast'like'hadoo_';

--rlike：確定字符串是否匹配正則表達(dá)式，是REGEXP_LIKE()的同義詞。

select1fromdualwhere'itcast'rlike'^i.*t$';

select1fromdualwhere'123456'rlike'^\\d+$';--判斷是否全為數(shù)字

select1fromdualwhere'123456aa'rlike'^\\d+$';

--regexp：功能與rlike相同用于判斷字符串是否匹配正則表達(dá)式

select1fromdualwhere'itcast'regexp'^i.*t$';關(guān)系運(yùn)算符算術(shù)運(yùn)算符邏輯運(yùn)算符算術(shù)運(yùn)算符操作數(shù)必須是數(shù)值類型。分為一元運(yùn)算符和二元運(yùn)算符：

一元運(yùn)算符,只有一個操作數(shù);二元運(yùn)算符有兩個操作數(shù),運(yùn)算符在兩個操作數(shù)之間。Hive內(nèi)置運(yùn)算符算術(shù)運(yùn)算符?加法操作:+?減法操作:-?乘法操作:*?除法操作:/?取整操作:div?取余操作:%?位與操作:&?位或操作:|?位異或操作:^?位取反操作:~Hive內(nèi)置運(yùn)算符算術(shù)運(yùn)算符--2、Hive中算術(shù)運(yùn)算符

--取整操作:div給出將A除以B所得的整數(shù)部分。例如17div3得出5。

select17div3;

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

select17%3;

--位與操作:&A和B按位進(jìn)行與操作的結(jié)果。與表示兩個都為1則結(jié)果為1

select4&8fromdual;--4轉(zhuǎn)換二進(jìn)制：01008轉(zhuǎn)換二進(jìn)制：1000

select6&4fromdual;--4轉(zhuǎn)換二進(jìn)制：01006轉(zhuǎn)換二進(jìn)制：0110

--位或操作:|A和B按位進(jìn)行或操作的結(jié)果或表示有一個為1則結(jié)果為1

select4|8fromdual;

select6|4fromdual;

--位異或操作:^A和B按位進(jìn)行異或操作的結(jié)果異或表示兩個不同則結(jié)果為1

select4^8fromdual;

select6^4fromdual;關(guān)系運(yùn)算符算術(shù)運(yùn)算符邏輯運(yùn)算符Hive內(nèi)置運(yùn)算符邏輯運(yùn)算符?與操作:AANDB?或操作:AORB?非操作:NOTA、!A?在:AIN(val1,val2,...)?不在:ANOTIN(val1,val2,...)?邏輯是否存在:[NOT]EXISTS(subquery)--3、Hive邏輯運(yùn)算符

--與操作:AANDB如果A和B均為TRUE，則為TRUE，否則為FALSE。如果A或B為NULL，則為NULL。

select1fromdualwhere3>1and2>1;

--或操作:AORB如果A或B或兩者均為TRUE，則為TRUE，否則為FALSE。

select1fromdualwhere3>1or2!=2;

--非操作:NOTA、!A如果A為FALSE，則為TRUE；如果A為NULL，則為NULL。否則為FALSE。

select1fromdualwherenot2>1;

select1fromdualwhere!2=1;

--在:AIN(val1,val2,...)如果A等于任何值，則為TRUE。

select1fromdualwhere11in(11,22,33);

--不在:ANOTIN(val1,val2,...)如果A不等于任何值，則為TRUE

select1fromdualwhere11notin(22,33,44);[NOT]EXISTS語法：SELECT…FROMtableWHERE[NOT]EXISTS(subquery)功能：將主查詢的數(shù)據(jù)，放到子查詢中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果（TRUE或FALSE）來決定主查詢的數(shù)據(jù)結(jié)果是否得以保留。Hive內(nèi)置運(yùn)算符邏輯運(yùn)算符--邏輯是否存在:[NOT]EXISTS(subquery)

--將主查詢的數(shù)據(jù)，放到子查詢中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果（TRUE或FALSE）來決定主查詢的數(shù)據(jù)結(jié)果是否得以保留。

selectA.*fromA

whereexists(selectB.idfromBwhereA.id=B.id);HiveFunctions函數(shù)入門03Hive函數(shù)概述及分類標(biāo)準(zhǔn)Hive內(nèi)置函數(shù)Hive用戶自定義函數(shù)（UDF、UDTF、UDAF）案例：UDF實(shí)現(xiàn)手機(jī)號****加密Hive內(nèi)建了不少函數(shù)，用于滿足用戶不同使用需求，提高SQL編寫效率：使用showfunctions查看當(dāng)下可用的所有函數(shù)；通過describefunctionextendedfuncname來查看函數(shù)的使用方式。HiveFunctions概述Hive的函數(shù)分為兩大類：內(nèi)置函數(shù)（Built-inFunctions）、用戶定義函數(shù)UDF（User-DefinedFunctions）：內(nèi)置函數(shù)可分為：數(shù)值類型函數(shù)、日期類型函數(shù)、字符串類型函數(shù)、集合函數(shù)、條件函數(shù)等；用戶定義函數(shù)根據(jù)輸入輸出的行數(shù)可分為3類：UDF、UDAF、UDTF。HiveFunctions分類標(biāo)準(zhǔn)根據(jù)函數(shù)輸入輸出的行數(shù)：UDF（User-Defined-Function）普通函數(shù)，一進(jìn)一出UDAF（User-DefinedAggregationFunction）聚合函數(shù)，多進(jìn)一出UDTF（User-DefinedTable-GeneratingFunctions）表生成函數(shù)，一進(jìn)多出HiveFunctions用戶定義函數(shù)UDF分類標(biāo)準(zhǔn)UDFUDAFUDTFUDF分類標(biāo)準(zhǔn)本來針對的是用戶自己編寫開發(fā)實(shí)現(xiàn)的函數(shù)。UDF分類標(biāo)準(zhǔn)可以擴(kuò)大到Hive的所有函數(shù)中：包括內(nèi)置函數(shù)和用戶自定義函數(shù)。因?yàn)椴还苁鞘裁搭愋偷暮瘮?shù)，一定滿足于輸入輸出的要求，那么從輸入幾行和輸出幾行上來劃分沒有任何問題。千萬不要被UD（User-Defined）這兩個字母所迷惑，照成視野的狹隘。比如Hive官方文檔中，針對聚合函數(shù)的標(biāo)準(zhǔn)就是內(nèi)置的UDAF類型。HiveFunctionsUDF分類標(biāo)準(zhǔn)擴(kuò)大化Hive函數(shù)概述及分類標(biāo)準(zhǔn)Hive內(nèi)置函數(shù)Hive用戶自定義函數(shù)（UDF、UDTF、UDAF）案例：UDF實(shí)現(xiàn)手機(jī)號****加密內(nèi)置函數(shù)（build-in）指的是Hive開發(fā)實(shí)現(xiàn)好，直接可以使用的函數(shù),也叫做內(nèi)建函數(shù)。官方文檔地址：/confluence/display/Hive/LanguageManual+UDF內(nèi)置函數(shù)根據(jù)應(yīng)用歸類整體可以分為8大種類型，我們將對其中重要的，使用頻率高的函數(shù)使用進(jìn)行詳細(xì)講解。Hive內(nèi)置函數(shù)概述字符串函數(shù)日期函數(shù)數(shù)學(xué)函數(shù)條件函數(shù)類型轉(zhuǎn)換函數(shù)數(shù)據(jù)脫敏函數(shù)集合函數(shù)其他雜項(xiàng)函數(shù)Hive內(nèi)置函數(shù)（1/8）StringFunctions字符串函數(shù)?正則表達(dá)式替換函數(shù)：regexp_replace?正則表達(dá)式解析函數(shù)：regexp_extract?URL解析函數(shù)：parse_url?json解析函數(shù)：get_json_object?空格字符串函數(shù)：space?重復(fù)字符串函數(shù)：repeat?首字符ascii函數(shù)：ascii?左補(bǔ)足函數(shù)：lpad?右補(bǔ)足函數(shù)：rpad?分割字符串函數(shù):split?集合查找函數(shù):find_in_set?字符串長度函數(shù)：length?字符串反轉(zhuǎn)函數(shù)：reverse?字符串連接函數(shù)：concat?帶分隔符字符串連接函數(shù)：concat_ws?字符串截取函數(shù)：substr,substring?字符串轉(zhuǎn)大寫函數(shù)：upper,ucase?字符串轉(zhuǎn)小寫函數(shù)：lower,lcase?去空格函數(shù)：trim?左邊去空格函數(shù)：ltrim?右邊去空格函數(shù)：rtrimHive內(nèi)置函數(shù)（1/8）StringFunctions字符串函數(shù)------------StringFunctions字符串函數(shù)------------

selectconcat("angela","baby");

--帶分隔符字符串連接函數(shù)：concat_ws(separator,[string|array(string)]+)

selectconcat_ws('.','www',array('itcast','cn'));

--字符串截取函數(shù)：substr(str,pos[,len])或者substring(str,pos[,len])

selectsubstr("angelababy",-2);--pos是從1開始的索引，如果為負(fù)數(shù)則倒著數(shù)

selectsubstr("angelababy",2,2);

--正則表達(dá)式替換函數(shù)：regexp_replace(str,regexp,rep)

selectregexp_replace('100-200','(\\d+)','num');

--正則表達(dá)式解析函數(shù)：regexp_extract(str,regexp[,idx])提取正則匹配到的指定組內(nèi)容

selectregexp_extract('100-200','(\\d+)-(\\d+)',2);

--URL解析函數(shù)：parse_url注意要想一次解析出多個可以使用parse_url_tuple這個UDTF函數(shù)

selectparse_url('/path/p1.php?query=1','HOST');

--分割字符串函數(shù):split(str,regex)

selectsplit('apachehive','\\s+');

--json解析函數(shù)：get_json_object(json_txt,path)

--$表示json對象

selectget_json_object('[{"website":"","name":"allenwoon"},{"website":"","name":"carbondata中文文檔"}]','$.[1].website');

Hive內(nèi)置函數(shù)（2/8）DateFunctions日期函數(shù)?獲取當(dāng)前日期:

current_date?獲取當(dāng)前時間戳:

current_timestamp?UNIX時間戳轉(zhuǎn)日期函數(shù):from_unixtime?獲取當(dāng)前UNIX時間戳函數(shù):unix_timestamp?日期轉(zhuǎn)UNIX時間戳函數(shù):unix_timestamp?指定格式日期轉(zhuǎn)UNIX時間戳函數(shù):unix_timestamp?抽取日期函數(shù):to_date?日期轉(zhuǎn)年函數(shù):year?日期轉(zhuǎn)月函數(shù):month?日期轉(zhuǎn)天函數(shù):day?日期轉(zhuǎn)小時函數(shù):hour?日期轉(zhuǎn)分鐘函數(shù):minute?日期轉(zhuǎn)秒函數(shù):second?日期轉(zhuǎn)周函數(shù):weekofyear?日期比較函數(shù):datediff?日期增加函數(shù):date_add?日期減少函數(shù):date_subHive內(nèi)置函數(shù)（2/8）DateFunctions日期函數(shù)-----------DateFunctions日期函數(shù)-----------------

--獲取當(dāng)前日期:current_date

selectcurrent_date();

--獲取當(dāng)前時間戳:current_timestamp

--同一查詢中對current_timestamp的所有調(diào)用均返回相同的值。

selectcurrent_timestamp();

--獲取當(dāng)前UNIX時間戳函數(shù):unix_timestamp

selectunix_timestamp();

--日期轉(zhuǎn)UNIX時間戳函數(shù):unix_timestamp

selectunix_timestamp("2011-12-0713:01:03");

--指定格式日期轉(zhuǎn)UNIX時間戳函數(shù):unix_timestamp

selectunix_timestamp('2011120713:01:03','yyyyMMddHH:mm:ss');

--UNIX時間戳轉(zhuǎn)日期函數(shù):from_unixtime

selectfrom_unixtime(1618238391);

selectfrom_unixtime(0,'yyyy-MM-ddHH:mm:ss');

--日期比較函數(shù):datediff日期格式要求'yyyy-MM-ddHH:mm:ss'or'yyyy-MM-dd'

selectdatediff('2012-12-08','2012-05-09');

--日期增加函數(shù):date_add

selectdate_add('2012-02-28',10);

--日期減少函數(shù):date_sub

selectdate_sub('2012-01-1',10);Hive內(nèi)置函數(shù)（3/8）MathematicalFunctions數(shù)學(xué)函數(shù)?取整函數(shù):round?指定精度取整函數(shù):round?向下取整函數(shù):floor?向上取整函數(shù):ceil?取隨機(jī)數(shù)函數(shù):rand?二進(jìn)制函數(shù):bin?進(jìn)制轉(zhuǎn)換函數(shù):conv?絕對值函數(shù):absHive內(nèi)置函數(shù)（3/8）MathematicalFunctions數(shù)學(xué)函數(shù)----MathematicalFunctions數(shù)學(xué)函數(shù)-------------

--取整函數(shù):round返回double類型的整數(shù)值部分（遵循四舍五入）

selectround(3.1415926);

--指定精度取整函數(shù):round(doublea,intd)返回指定精度d的double類型

selectround(3.1415926,4);

--向下取整函數(shù):floor

selectfloor(3.1415926);

selectfloor(-3.1415926);

--向上取整函數(shù):ceil

selectceil(3.1415926);

selectceil(-3.1415926);

--取隨機(jī)數(shù)函數(shù):rand每次執(zhí)行都不一樣返回一個0到1范圍內(nèi)的隨機(jī)數(shù)

selectrand();

--指定種子取隨機(jī)數(shù)函數(shù):rand(intseed)得到一個穩(wěn)定的隨機(jī)數(shù)序列

selectrand(2);Hive內(nèi)置函數(shù)（4/8）CollectionFunctions集合函數(shù)?集合元素size函數(shù):

size(Map<K.V>)size(Array<T>)?取map集合keys函數(shù):

map_keys(Map<K.V>)?取map集合values函數(shù):

map_values(Map<K.V>)?判斷數(shù)組是否包含指定元素:

array_contains(Array<T>,value)?數(shù)組排序函數(shù):sort_array(Array<T>)Hive內(nèi)置函數(shù)（4/8）CollectionFunctions集合函數(shù)-------CollectionFunctions集合函數(shù)--------------

describefunctionextendedsort_array;

--集合元素size函數(shù):size(Map<K.V>)size(Array<T>)

selectsize(`array`(11,22,33));

selectsize(`map`("id",10086,"name","zhangsan","age",18));

--取map集合keys函數(shù):map_keys(Map<K.V>)

selectmap_keys(`map`("id",10086,"name","zhangsan","age",18));

--取map集合values函數(shù):map_values(Map<K.V>)

selectmap_values(`map`("id",10086,"name","zhangsan","age",18));

--判斷數(shù)組是否包含指定元素:array_contains(Array<T>,value)

selectarray_contains(`array`(11,22,33),11);

selectarray_contains(`array`(11,22,33),66);

--數(shù)組排序函數(shù):sort_array(Array<T>)

selectsort_array(`array`(12,2,32));主要用于條件判斷、邏輯判斷轉(zhuǎn)換這樣的場合Hive內(nèi)置函數(shù)（5/8）ConditionalFunctions條件函數(shù)?if條件判斷:

if(booleantestCondition,TvalueTrue,TvalueFalseOrNull)?空判斷函數(shù):

isnull(a)?非空判斷函數(shù):

isnotnull(a)?空值轉(zhuǎn)換函數(shù):

nvl(Tvalue,Tdefault_value)?非空查找函數(shù):

COALESCE(Tv1,Tv2,...)?條件轉(zhuǎn)換函數(shù):

CASEaWHENbTHENc[WHENdTHENe]*[ELSEf]END?nullif(a,b):

如果a=b，則返回NULL；否則返回NULL。否則返回一個?assert_true:

如果'condition'不為真，則引發(fā)異常，否則返回nullHive內(nèi)置函數(shù)（5/8）ConditionalFunctions條件函數(shù)-----ConditionalFunctions條件函數(shù)------------------

describefunctionextendedassert_true;

--使用之前課程創(chuàng)建好的student表數(shù)據(jù)

select*fromstudentlimit3;

--if條件判斷:if(booleantestCondition,TvalueTrue,TvalueFalseOrNull)

selectif(1=2,100,200);

selectif(sex='男','M','W')fromstudentlimit3;

--空判斷函數(shù):isnull(a)

selectisnull("allen");

selectisnull(null);

--非空判斷函數(shù):isnotnull(a)

selectisnotnull("allen");

selectisnotnull(null);

--空值轉(zhuǎn)換函數(shù):nvl(Tvalue,Tdefault_value)

selectnvl("allen","itcast");

selectnvl(null,"itcast");

Hive內(nèi)置函數(shù)（5/8）ConditionalFunctions條件函數(shù)--非空查找函數(shù):COALESCE(Tv1,Tv2,...)

--返回參數(shù)中的第一個非空值；如果所有值都為NULL，那么返回NULL

selectCOALESCE(null,11,22,33);

selectCOALESCE(null,null,null,33);

selectCOALESCE(null,null,null);

--條件轉(zhuǎn)換函數(shù):CASEaWHENbTHENc[WHENdTHENe]*[ELSEf]END

selectcase100when50then'tom'when100then'mary'else'tim'end;

selectcasesexwhen'男'then'male'else'female'endfromstudentlimit3;

--nullif(a,b):

--果a=b，則返回NULL，否則返回一個

selectnullif(11,11);

selectnullif(11,12);

--assert_true(condition)

--如果'condition'不為真，則引發(fā)異常，否則返回null

SELECTassert_true(11>=0);

SELECTassert_true(-1>=0);主要用于顯式的數(shù)據(jù)類型轉(zhuǎn)換：Hive內(nèi)置函數(shù)（6/8）TypeConversionFunctions類型轉(zhuǎn)換函數(shù)----TypeConversionFunctions類型轉(zhuǎn)換函數(shù)-----------------

describefunctionextendedcast;

--任意數(shù)據(jù)類型之間轉(zhuǎn)換:cast

selectcast(12.14asbigint);

selectcast(12.14asstring);主要完成對數(shù)據(jù)脫敏轉(zhuǎn)換功能，屏蔽原始數(shù)據(jù)，主要如下：Hive內(nèi)置函數(shù)（7/8）DataMaskingFunctions數(shù)據(jù)脫敏函數(shù)?mask?mask_first_n(stringstr[,intn]?mask_last_n(stringstr[,intn])?mask_show_first_n(stringstr[,intn])?mask_show_last_n(stringstr[,intn])?mask_hash(string|char|varcharstr)Hive內(nèi)置函數(shù)（7/8）DataMaskingFunctions數(shù)據(jù)脫敏函數(shù)----DataMaskingFunctions數(shù)據(jù)脫敏函數(shù)------------

describefunctionextendedmask;

--mask

--將查詢回的數(shù)據(jù)，大寫字母轉(zhuǎn)換為X，小寫字母轉(zhuǎn)換為x，數(shù)字轉(zhuǎn)換為n。

selectmask("abc123DEF");

selectmask("abc123DEF",'-','.','^');--自定義替換的字母

--mask_first_n(stringstr[,intn]

--對前n個進(jìn)行脫敏替換

selectmask_first_n("abc123DEF",4);

--mask_last_n(stringstr[,intn])

selectmask_last_n("abc123DEF",4);

--mask_show_first_n(stringstr[,intn])

--除了前n個字符，其余進(jìn)行掩碼處理

selectmask_show_first_n("abc123DEF",4);

--mask_show_last_n(stringstr[,intn])

selectmask_show_last_n("abc123DEF",4);

--mask_hash(string|char|varcharstr)

--返回字符串的hash編碼。

selectmask_hash("abc123DEF");Hive內(nèi)置函數(shù)（8/8）Misc.Functions其他雜項(xiàng)函數(shù)?hive調(diào)用java方法:java_method(class,method[,arg1[,arg2..]])?反射函數(shù):reflect(class,method[,arg1[,arg2..]])?取哈希值函數(shù):hash?current_user()、logged_in_user()、current_database()、version()?SHA-1加密:sha1(string/binary)?SHA-2家族算法加密：sha2(string/binary,int)(SHA-224,SHA-256,SHA-384,SHA-512)?crc32加密:?MD5加密:md5(string/binary)Hive內(nèi)置函數(shù)（8/8）Misc.Functions其他雜項(xiàng)函數(shù)-----Misc.Functions其他雜項(xiàng)函數(shù)---------------

describefunctionextendedsurrogate_key;

--hive調(diào)用java方法:java_method(class,method[,arg1[,arg2..]])

selectjava_method("java.lang.Math","max",11,22);

--反射函數(shù):reflect(class,method[,arg1[,arg2..]])

selectreflect("java.lang.Math","max",11,22);

--取哈希值函數(shù):hash

selecthash("allen");

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

--SHA-1加密:sha1(string/binary)

selectsha1("allen");

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

selectsha2("allen",224);

selectsha2("allen",512);

--crc32加密:

selectcrc32("allen");

--MD5加密:md5(string/binary)

selectmd5("allen");

Hive函數(shù)概述及分類標(biāo)準(zhǔn)Hive內(nèi)置函數(shù)Hive用戶自定義函數(shù)（UDF、UDTF、UDAF）案例：UDF實(shí)現(xiàn)手機(jī)號****加密用戶自定義函數(shù)簡稱UDF，源自于英文user-definedfunction。根據(jù)函數(shù)輸入輸出的行數(shù)可以分為3類，分別是：UDF（User-Defined-Function）普通函數(shù)，一進(jìn)一出UDAF（User-DefinedAggregationFunction）聚合函數(shù)，多進(jìn)一出UDTF（User-DefinedTable-GeneratingFunctions）表生成函數(shù)，一進(jìn)多出Hive用戶自定義函數(shù)分類UDFUDAFUDTFUDF普通函數(shù)特點(diǎn)是一進(jìn)一出，也就是輸入一行輸出一行。比如round這樣的取整函數(shù)，接收一行數(shù)據(jù)，輸出的還是一行數(shù)據(jù)。Hive用戶自定義函數(shù)（1/3）UDF普通函數(shù)UDFUDAF

聚合函數(shù)，A所代表的單詞就是Aggregation聚合的意思。多進(jìn)一出，也就是輸入多行輸出一行。比如count、sum這樣的函數(shù)。Hive用戶自定義函數(shù)（2/3）UDAF聚合函數(shù)?count:統(tǒng)計(jì)檢索到的總行數(shù)。?sum:求和?avg:求平均?min:最小值?max:最大值?數(shù)據(jù)收集函數(shù)（去重）:collect_set(col)?數(shù)據(jù)收集函數(shù)（不去重）:collect_list(col)UDAFHive用戶自定義函數(shù)（2/3）UDAF聚合函數(shù)selectsexfromstudent;

selectcollect_set(sex)fromstudent;

selectcollect_list(sex)fromstudent;UDTF

表生成函數(shù)，T所代表的單詞是Table-Generating表生成的意思。特點(diǎn)是一進(jìn)多出，也就是輸入一行輸出多行。這類型的函數(shù)作用返回的結(jié)果類似于表，同時，UDTF函數(shù)也是我們接觸比較少的函數(shù)。比如explode函數(shù)。Hive用戶自定義函數(shù)（3/3）UDTF表生成函數(shù)UDTFHive函數(shù)概述及分類標(biāo)準(zhǔn)Hive內(nèi)置函數(shù)Hive用戶自定義函數(shù)（UDF、UDTF、UDAF）案例：UDF實(shí)現(xiàn)手機(jī)號****加密HiveUDF用戶自定義函數(shù)開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密

在企業(yè)中處理數(shù)據(jù)的時候，對于敏感數(shù)據(jù)往往需要進(jìn)行脫敏處理。比如手機(jī)號。我們常見的處理方式是將手機(jī)號中間4位進(jìn)行****處理。Hive中沒有這樣的函數(shù)可以直接實(shí)現(xiàn)功能，雖然可以通過各種函數(shù)的嵌套調(diào)用最終也能實(shí)現(xiàn)，但是效率不高，現(xiàn)要求自定義開發(fā)實(shí)現(xiàn)Hive函數(shù)，滿足上述需求。

1、能夠?qū)斎霐?shù)據(jù)進(jìn)行非空判斷、手機(jī)號位數(shù)判斷2、能夠?qū)崿F(xiàn)校驗(yàn)手機(jī)號格式，把滿足規(guī)則的進(jìn)行****處理3、對于不符合手機(jī)號規(guī)則的數(shù)據(jù)直接返回，不處理寫一個java類，繼承UDF，并重載evaluate方法，方法中實(shí)現(xiàn)函數(shù)的業(yè)務(wù)邏輯；重載意味著可以在一個java類中實(shí)現(xiàn)多個函數(shù)功能；程序打成jar包，上傳HS2服務(wù)器本地或者HDFS;客戶端命令行中添加jar包到Hive的classpath：hive>addJAR/xxxx/udf.jar;注冊成為臨時函數(shù)（給UDF命名）：createtemporaryfunction函數(shù)名as'UDF類全路徑';HQL中使用函數(shù)。案例：開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密UDF實(shí)現(xiàn)步驟IDEA中創(chuàng)建Maven工程，添加下述pom依賴，用于開發(fā)HiveUDF；完整pom.xml請參考課程附件資料案例：開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密開發(fā)環(huán)境準(zhǔn)備<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>

案例：開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密步驟1：編寫業(yè)務(wù)代碼/**

*@description:hive自定義函數(shù)UDF實(shí)現(xiàn)對手機(jī)號中間4位進(jìn)行****加密

*@author:Itcast

*/

publicclassEncryptPhoneNumberextendsUDF{

/**

*重載evaluate方法實(shí)現(xiàn)函數(shù)的業(yè)務(wù)邏輯

*@paramphoNum入?yún)ⅲ何醇用苁謾C(jī)號

*@return返回：加密后的手機(jī)號字符串

*/

publicStringevaluate(StringphoNum){

StringencryptPhoNum=null;

//手機(jī)號不為空并且為11位

if(StringUtils.isNotEmpty(phoNum)&&phoNum.trim().length()==11){

//判斷數(shù)據(jù)是否滿足中國大陸手機(jī)號碼規(guī)范

Stringregex="^(1[3-9]\\d{9}$)";

Patternp=Pattern.compile(regex);

Matcherm=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;

}

returnencryptPhoNum;

}

}IDEA中使用集成的Maven插件進(jìn)行打包，這里會把依賴一起打入jar包案例：開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密步驟2：打jar包上傳把jar包上傳到Hiveserver2服務(wù)運(yùn)行所在機(jī)器的Linux系統(tǒng)上傳HDFS文件系統(tǒng)也可以，后續(xù)路徑指定清楚即可案例：開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密步驟3：jar包上傳HS2服務(wù)器本地在客戶端中使用命令把jar包添加至classpath。案例：開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密步驟4：添加jar至HiveClasspath通俗來說就是給用戶編寫的函數(shù)起個名字案例：開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密步驟5：注冊臨時函數(shù)createtemporaryfunction函數(shù)名as'UDF類全路徑';案例：開發(fā)HiveUDF實(shí)現(xiàn)手機(jī)號****加密步驟6：注冊成功，使用UDFHive函數(shù)高階04UDTF之explode函數(shù)LateralView側(cè)視圖Aggregation聚合函數(shù)WindowsFunctions窗口函數(shù)Sampling抽樣函數(shù)explode接收map、array類型的數(shù)據(jù)作為輸入，然后把輸入數(shù)據(jù)中的每個元素拆開變成一行數(shù)據(jù)，一個元素一行。explode執(zhí)行效果正好滿足于輸入一行輸出多行，所有叫做UDTF函數(shù)。HiveUDTF之explode函數(shù)功能介紹UDTF一般情況下，explode函數(shù)可以直接單獨(dú)使用即可；也可以根據(jù)業(yè)務(wù)需要結(jié)合lateralview側(cè)視圖一起使用。explode(array)將array里的每個元素生成一行；explode(map)將map里的每一對元素作為一行，其中key為一列，value為一列；HiveUDTF之explode函數(shù)功能介紹HiveUDTF之explode函數(shù)使用selectexplode(`array`(11,22,33))asitem;

selectexplode(`map`("id",10086,"name","zhangsan","age",18));HiveUDTF之explode函數(shù)NBA總冠軍球隊(duì)名單分析練習(xí)explode函數(shù)的使用感悟什么叫做UDTF表生成函數(shù)發(fā)現(xiàn)UDTF函數(shù)使用限制有一份數(shù)據(jù)《The_NBA_Championship.txt》，關(guān)于部分年份的NBA總冠軍球隊(duì)名單；第一個字段表示球隊(duì)名稱，第二個字段是獲取總冠軍的年份；字段之間以，分割；總冠軍年份之間以|進(jìn)行分割。需求：使用Hive建表映射成功數(shù)據(jù)，對數(shù)據(jù)拆分，要求拆分之后數(shù)據(jù)如下所示：HiveUDTF之explode函數(shù)業(yè)務(wù)需求HiveUDTF之explode函數(shù)建表加載數(shù)據(jù)--step1:建表

createtablethe_nba_championship(

team_namestring,

champion_yeararray<string>

)rowformatdelimited

fieldsterminatedby','

collectionitemsterminatedby'|';

--step2:加載數(shù)據(jù)文件到表中

loaddatalocalinpath'/root/hivedata/The_NBA_Championship.txt'intotablethe_nba_championship;

--step3:驗(yàn)證

select*

fromthe_nba_championship;HiveUDTF之explode函數(shù)SQL實(shí)現(xiàn)業(yè)務(wù)需求--step4:使用explode函數(shù)對champion_year進(jìn)行拆分俗稱炸開

selectexplode(champion_year)fromthe_nba_championship;

selectteam_name,explode(champion_year)fromthe_nba_championship;在select條件中，如果只有explode函數(shù)表達(dá)式，程序執(zhí)行是沒有任何問題的；但是如果在select條件中，包含explode和其他字段，就會報錯；如何理解這個錯誤？為什么在select的時候，explode的旁邊不支持其他字段的同時出現(xiàn)？HiveUDTF之explode函數(shù)執(zhí)行錯誤UDTF'sarenotsupportedoutsidetheSELECTclause,nornestedinexpressionsexplode函數(shù)屬于UDTF表生成函數(shù)，explode執(zhí)行返回的結(jié)果可以理解為一張?zhí)摂M的表，其數(shù)據(jù)來源于源表；在select中只查詢源表數(shù)據(jù)沒有問題，只查詢explode生成的虛擬表數(shù)據(jù)也沒問題，但是不能在只查詢源表的時候，既想返回源表字段又想返回explode生成的虛擬表字段；通俗點(diǎn)講，有兩張表，不能只查詢一張表但是又想返回分別屬于兩張表的字段；HiveUDTF之explode函數(shù)UDTF語法限制源表(ABC)生成的結(jié)果（虛擬的表）explode(A)select從SQL層面上來說上述問題的解決方案是：對兩張表進(jìn)行join關(guān)聯(lián)查詢;Hive專門提供了語法lateralView側(cè)視圖，專門用于搭配explode這樣的UDTF函數(shù)，以滿足上述需要。HiveUDTF之explode函數(shù)UDTF語法限制解決--step5:lateralview+explode

selecta.team_name,b.year

fromthe_nba_championshipalateralviewexplode(champion_year)basyear

orderbyb.yeardesc;UDTF之explode函數(shù)LateralView側(cè)視圖Aggregation聚合函數(shù)WindowsFunctions窗口函數(shù)Sampling抽樣函數(shù)LateralView是一種特殊的語法，主要搭配UDTF類型函數(shù)一起使用，用于解決UDTF函數(shù)的一些查詢限制的問題。一般只要使用UDTF，就會固定搭配lateralview使用。官方鏈接：/confluence/display/Hive/LanguageManual+LateralViewHiveLateralView側(cè)視圖概念將UDTF的結(jié)果構(gòu)建成一個類似于視圖的表，然后將原表中的每一行和UDTF函數(shù)輸出的每一行進(jìn)行連接，生成一張新的虛擬表。這樣就避免了UDTF的使用限制問題。使用lateralview時也可以對UDTF產(chǎn)生的記錄設(shè)置字段名稱，產(chǎn)生的字段可以用于groupby、orderby、limit等語句中，不需要再單獨(dú)嵌套一層子查詢。HiveLateralView側(cè)視圖原理針對explode案例中NBA冠軍球隊(duì)年份排名案例，使用explode函數(shù)+lateralview側(cè)視圖，可以完美解決：HiveLateralView側(cè)視圖LateralView+UDTF--lateralview側(cè)視圖基本語法如下

select……fromtabelAlateralviewUDTF(xxx)別名ascol1,col2,col3……;

selecta.team_name,b.year

fromthe_nba_championshipalateralviewexplode(champion_year)basyear;

--根據(jù)年份倒序排序

selecta.team_name,b.year

fromthe_nba_championshipalateralviewexplode(champion_year)basyear

orderbyb.yeardesc;

--統(tǒng)計(jì)每個球隊(duì)獲取總冠軍的次數(shù)并且根據(jù)倒序排序

selecta.team_name,count(*)asnums

fromthe_nba_championshipalateralviewexplode(champion_year)basyear

groupbya.team_name

orderbynumsdesc;UDTF之explode函數(shù)LateralView側(cè)視圖Aggregation聚合函數(shù)WindowsFunctions窗口函數(shù)Sampling抽樣函數(shù)聚合函數(shù)的功能是：對一組值執(zhí)行計(jì)算并返回單一的值。聚合函數(shù)是典型的輸入多行輸出一行，使用Hive的分類標(biāo)準(zhǔn)，屬于UDAF類型函數(shù)。通常搭配GroupBy語法一起使用，分組后進(jìn)行聚合操作。HiveAggregation聚合函數(shù)概述HQL提供了幾種內(nèi)置的UDAF聚合函數(shù)