數(shù)據(jù)倉庫Hive應(yīng)用實戰(zhàn) 教案85-88課時：HIVE內(nèi)置函數(shù)（一）

湖南汽車工程職業(yè)學(xué)院教師授課教案第11周第5-8課時累計88課時課程名稱數(shù)據(jù)倉庫HIVE應(yīng)用授課課題HIVE內(nèi)置函數(shù)（一）授課專業(yè)班級大數(shù)據(jù)2101班、2102班、2103班教學(xué)目標(biāo)1、掌握Hive內(nèi)置函數(shù)的應(yīng)用，能夠在HiveQL語句中靈活運用聚合函數(shù)。2、掌握Hive內(nèi)置函數(shù)的應(yīng)用，能夠在HiveQL語句中靈活運用數(shù)學(xué)函數(shù)。3、掌握Hive內(nèi)置函數(shù)的應(yīng)用，能夠在HiveQL語句中靈活運用集合函數(shù)。教學(xué)要點教學(xué)重點聚合函數(shù)、數(shù)學(xué)函數(shù)教學(xué)難點聚合函數(shù)的靈活使用課型一體化授課教法與學(xué)法（教具）示范演示法、任務(wù)驅(qū)動教學(xué)法、分組合作教學(xué)法課后作業(yè)完成智課堂作業(yè)、看視頻熟練操作教學(xué)后記（教師課后填寫）1、結(jié)合項目要多練；2、鼓勵學(xué)生之間要相互請教、相互幫忙，合作解決問題，有利于他們能力的增長授課教師王海霞、黃史記【課前復(fù)習(xí)】導(dǎo)出倉庫數(shù)據(jù)的方式有哪些？基本語法又是什么？【作業(yè)點評】展示智課堂學(xué)生作業(yè)完成情況，并做點評?！緦W(xué)習(xí)目標(biāo)】能在HIVE中熟練使用聚合函數(shù)、數(shù)學(xué)函數(shù)和集合函數(shù)?！菊n程導(dǎo)入】Hive同傳統(tǒng)的關(guān)系數(shù)據(jù)庫一樣含有大量內(nèi)置函數(shù)，Hive內(nèi)置函數(shù)可以實現(xiàn)某些固定功能，方便用戶直接使用，使用內(nèi)置函數(shù)可以滿足日常開發(fā)中所涉及的常見開發(fā)需求。但是，對于任何一個系統(tǒng)而言，都不可能將實際用戶的所有需求都事先考慮周全。Hive也一樣，對于一些特殊的用戶需求，Hive已提供的內(nèi)置函數(shù)是滿足不了的，因此一個系統(tǒng)的開放性就顯得尤為重要了。也就是說，雖然系統(tǒng)不能事先考慮到所有可能的用戶需求，但卻可以提供一個開發(fā)的接口，允許開發(fā)人員根據(jù)個性化的需求來定義特殊功能函數(shù)的實現(xiàn)，這就是Hive用戶自定義函數(shù)，它可以根據(jù)實際使用場景編寫函數(shù)?！拘抡n講授】Hive內(nèi)部提供了多種類型的函數(shù)供用戶使用，包括聚合函數(shù)、集合函數(shù)、數(shù)學(xué)函數(shù)等，這些函數(shù)統(tǒng)稱為Hive內(nèi)置函數(shù)。通過對Hive內(nèi)置函數(shù)的靈活運用，可以提高程序可讀性及執(zhí)行速度。本任務(wù)以“學(xué)生信息系統(tǒng)”項目為實操載體，完成理解Hive函數(shù)和函數(shù)分類、應(yīng)用聚合函數(shù)、集合函數(shù)、數(shù)學(xué)函數(shù)、類型轉(zhuǎn)換函數(shù)、日期函數(shù)、條件函數(shù)、字符串函數(shù)和表生成函數(shù)等學(xué)習(xí)目標(biāo)。一、初識Hive函數(shù)在Hive客戶端，通過命令SHOWFUNCTIONS命令能查看系統(tǒng)所有的內(nèi)置函數(shù)，默認(rèn)內(nèi)置271個函數(shù)，不同Hive版本，內(nèi)置函數(shù)數(shù)量稍有不同；通過命令DESCRIBEFUNCTION<function_name>;能查看某個函數(shù)的描述；通過命令DESCRIBEFUNCTIONEXTENDED<function_name>;能查看某個函數(shù)詳細的使用方法?！局v練結(jié)合】【例1】使用命令查看COUNT()函數(shù)相關(guān)信息。【新課講授】二、Hive函數(shù)分類Hive中的內(nèi)置函數(shù)、自定義函數(shù)均分為三類：UDF、UDTF和UDAF。UDF，全稱User-Defined-Function，也稱普通函數(shù)。特點是一進一出，即輸入一行數(shù)據(jù)，輸出一行數(shù)據(jù)。比如：UPPER()函數(shù)、SUBSTRING()函數(shù)?！局v練結(jié)合】【例2】使用命令驗證UPPER()函數(shù)為UDF。UDAF，全稱User-DefinedAggregationFunction，也稱聚合函數(shù)。特點是多進一出，即輸入多行數(shù)據(jù)，輸出一行數(shù)據(jù)。比如：COUNT()函數(shù)、SUM()函數(shù)。UDTF，全稱User-DefinedTable-GeneratingFunction，也稱表生成函數(shù)或炸裂函數(shù)。特點是一進多出，即輸入一行數(shù)據(jù)，輸出多行數(shù)據(jù)。比如：EXPLODE函數(shù)。【新課講授】三、聚合函數(shù)聚合函數(shù)是按照特定條件對一組值執(zhí)行計算，以總結(jié)出關(guān)于組的結(jié)論，因此，聚合函數(shù)通常與SELECT語句和GROUPBY子句一起使用，即針對某一組數(shù)據(jù)執(zhí)行計算。聚合函數(shù)名返回值類型用法描述COUNT()BIGINTCOUNT(*)：統(tǒng)計總行數(shù)，包括含有NULL值的行COUNT(expr)：統(tǒng)計提供非NULL的expr表達式值的行數(shù)COUNT(DISTINCTexpr[,expr])：統(tǒng)計提供非NULL且去重后的expr表達式值的行數(shù)SUM()DOUBLESUM(col)：求指定列的和SUM(DISTINCTcol)：求去重后的列的和AVG()DOUBLEAVG(col)：求指定列的平均值A(chǔ)VG(DISTINCTcol)：求去重后的列的平均值MIN()DOUBLEMIN(col)：求指定列的最小值MAX()DOUBLEMAX(col)：求指定列的最大值VAR_POP()DOUBLEVAR_POP(col)：求指定列數(shù)值的方差VAR_SAMP()DOUBLEVAR_SAMP(col)：求指定列數(shù)值的樣本方差STDDEV_POP()DOUBLESTDDEV_POP(col)：求指定列數(shù)值的標(biāo)準(zhǔn)偏差STDDEV_SAMP()DOUBLESTDDEV_SAMP(col)：求指定列數(shù)值的樣本標(biāo)準(zhǔn)偏差COVAR_POP()DOUBLECOVAR_POP(col1,col2)：求指定列數(shù)值的總體協(xié)方差COVAR_SAMP()DOUBLECOVAR_SAMP(col1,col2)：求指定列數(shù)值的樣本協(xié)方差CORR()DOUBLECORR(col1,col2)：求兩列數(shù)值的皮爾遜相關(guān)系數(shù)COLLECT_SET()ARRAYCOLLECT_SET(col)：返回消除了重復(fù)元素的數(shù)組COLLECT_LIST()ARRAYCOLLECT_LIST(col)：返回允許重復(fù)元素的數(shù)組【講練結(jié)合】【例3】使用COUNT函數(shù)統(tǒng)計出student表中參加了Hive課程學(xué)習(xí)的人數(shù)。SELECTCOUNT(course_scores['Hive'])FROMstudentdb.student;【例4】使用COUNT函數(shù)統(tǒng)計出student表中學(xué)生是來自幾個不同的城市。SELECTCOUNT(DISTINCTaddress.city)FROMstudentdb.student;【例5】使用AVG函數(shù)分班統(tǒng)計出student表中各班Hive課程和Python課程的平均分。SELECTclass,AVG(course_scores['Hive'])ASavg_Hive_score,AVG(course_scores['Python'])ASavg_Python_scoreFROMstudentdb.studentGROUPBYclass;【例6】使用MIN函數(shù)和MAX函統(tǒng)計統(tǒng)計出student表中各班Python課程的最低分和最高分。SELECTclass,MIN(course_scores['Python'])ASmin_Python_score,MAX(course_scores['Python'])ASmax_Python_scoreFROMstudentdb.studentGROUPBYclass;【新課講授】四、集合函數(shù)集合函數(shù)是針對集合數(shù)據(jù)類型進行操作。以下通過表6-2介紹Hive內(nèi)置的集合函數(shù)。Hive內(nèi)置的集合函數(shù)集合函數(shù)名返回值類型用法描述SIZE

()INTSIZE

(MAP)：求MAP數(shù)據(jù)類型列的長度。SIZE

(ARRAY)：求ARRAY數(shù)據(jù)類型列的長度。MAP_KEYS()ARRAYMAP_KEYS(Map)：返回MAP數(shù)據(jù)類型列的所有KEY。MAP_VALUES()AYYAYMAP_VALUES(Map)：返回MAP數(shù)據(jù)類型列的所有VALUE。ARRAY_CONTAINS()BOOLEANARRAY_CONTAINS(ARRAY，value)：返回ARRAY數(shù)據(jù)類型列中是否包含值value，若包含則返回值為true，反之返回值為false。SORT_ARRAY()ARRAYSORT_ARRAY(ARRAY)：按自然順序?qū)RRAY數(shù)據(jù)類型列中的值進行排序?！局v練結(jié)合】【例7】使用SIZE函數(shù)統(tǒng)計student表中'Zhangdan'同學(xué)有多少個室友。SELECTSIZE(roommate)FROMstudentdb.studentWHEREstname='Zhangdan';圖6-SEQ圖6-\*ARABIC10統(tǒng)計'Zhangdan'同學(xué)的室友人數(shù)【例8】使用ARRAY_CONTAINS函數(shù)統(tǒng)計student表中學(xué)號為2018010201的室友名字。SELECTstname,ARRAY_CONTAINS(roommate,'2018010201')is_roommateFROMstudentdb.student;圖6-SEQ圖6-\*ARABIC11統(tǒng)計學(xué)號為2018010201的室友名字【例9】使用ARRAY_CONTAINS函數(shù)和COLLECT_LIST函數(shù)將所有學(xué)生的Hive課程分?jǐn)?shù)排序。SELECTSORT_ARRAY(COLLECT_LIST(course_scores['Hive']))FROMstudentdb.student;圖6-SEQ圖6-\*ARABIC12排序所有同學(xué)的Hive課程分?jǐn)?shù)【新課講授】四、數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)是針對數(shù)字類型的值進行計算。下面通過表6-3介紹Hive內(nèi)置的常用數(shù)學(xué)函數(shù)。表6-SEQ表6-\*ARABIC3Hive內(nèi)置的常用數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)名返回值類型用法描述ROUND()DOUBLEROUND

(DOUBLE

a)：返回數(shù)字a四舍五入后的值。ROUND

(DOUBLE

a，INT

b)：返回數(shù)字a四舍五入后的值，保留小數(shù)點后b位。FLOOR()BIGINTFLOOR(DOUBLE

a)：返回數(shù)字a向下取整的值。CEIL()BIGINTCEIL(DOUBLE

a)：返回數(shù)字a向上取整的值。RAND()DOUBLERAND()：返回0~1的隨機值。RAND(INT

seed)：通過隨機因子seed返回0~1的隨機值。EXP()DOUBLEEXP(DOUBLE/DECIMAL

a)：返回自然常數(shù)e的a次方值。LN()DOUBLELN(DOUBLE/DECIMAL

a)：返回以自然常數(shù)e為底a的對數(shù)值。LOG()DOUBLELOG(DOUBLE/DECIMAL

base，DOUBLE/DECIMAL

a)：返回以base為底a的對數(shù)值。LOG2(DOUBLE/DECIMAL

a)：返回以2為底a的對數(shù)值。LOG10(DOUBLE/DECIMAL

a)：返回以10為底a的對數(shù)值。POW()DOUBLEPOW(DOUBLE/DECIMAL

a，DOUBLE/DECIMAL

p)：返回a的p次方。SQRT()DOUBLESQRT(DOUBLE/DECIMAL

a)：返回a的平方根。ABS()DOUBLEABS(DOUBLE

a)：返回a的絕對值。SIN()DOUBLESIN

(DOUBLE/DECIMAL

a)：返回a的正弦值。ASIN()DOUBLEASIN(DOUBLEa),ASIN(DECIMALa)：求a的反正弦值COS()DOUBLECOS

(DOUBLE/DECIMAL

a)：返回a的余弦值。ACOS()DOUBLEACOS

(DOUBLE/DECIMAL

a)：返回a的反余弦值。TAN()DOUBLETAN

(DOUBLE/DECIMAL

a)：返回a的正切值。ATAN()DOUBLEATAN

(DOUBLE/DECIMAL

a)：返回a的反正切值。PI()DOUBLE返回圓周率

Pi

的值。GREATEST()TGREATEST(T

v1,T

v2…)：返回列表中的最大值，若存在NULL，則認(rèn)定NULL為最大值，其中v1、v2可以是值或列，若v1和v2為列，則返回兩列中每行的最大值。LEAST()TLEAST(T

v1,T

v2…)：返回列表中的最小值，若存在NULL，則認(rèn)定NULL為最小值，其中v1、v2可以是值或列，若v1和v2為列，則返回兩列中每行的最小值。SIGN()DOUBLESIGN

(DOUBLE/DECIMAL

a)：如果

a

是正數(shù)，返回1.0；如果a是負數(shù)，返回-1.0；如果a是0，返回

0.0。E()DOUBLE返回自然常數(shù)e的值。NEGATIVE()INT/DOUBLENEGATIVE

(DOUBLE/INT

a)：返回a的相反值。FACTORIAL()BIGINTFACTORIAL(INTa)：求a的階乘CBRT()DOUBLECBRT(DOUBLEa)：求a的立方根