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

湖南汽車工程職業(yè)學院教師授課教案第12周第5-8課時累計96課時課程名稱數(shù)據(jù)倉庫HIVE應(yīng)用授課課題HIVE內(nèi)置函數(shù)（三）授課專業(yè)班級大數(shù)據(jù)2101班、2102班、2103班教學目標1、掌握條件函數(shù)的運用2、掌握字符串函數(shù)的運用3、運用表生成函數(shù)教學要點教學重點條件函數(shù)、字符串函數(shù)教學難點表生成函數(shù)課型一體化授課教法與學法（教具）示范演示法、任務(wù)驅(qū)動教學法、分組合作教學法課后作業(yè)完成智課堂作業(yè)、看視頻熟練操作教學后記（教師課后填寫）1、結(jié)合項目要多練；2、鼓勵學生之間要相互請教、相互幫忙，合作解決問題，有利于他們能力的增長；3、掌握思維導圖對于知識體系構(gòu)建的重要性。授課教師王海霞、黃史記【課前復習】日期函數(shù)的使用【作業(yè)點評】展示智課堂學生作業(yè)完成情況，并做點評。【學習目標】以“學生信息系統(tǒng)”項目為實操載體，完成條件函數(shù)、字符串函數(shù)和表生成函數(shù)的使用。【課程導入】前面我們已經(jīng)認識了常用的內(nèi)置函數(shù)（聚合函數(shù)、集合函數(shù)、數(shù)學函數(shù)、類型轉(zhuǎn)換函數(shù)和時間函數(shù)），今天繼續(xù)學習內(nèi)置函數(shù)中的條件函數(shù)、字符串函數(shù)和表生成函數(shù)的使用?！拘抡n講授】一、條件函數(shù)條件函數(shù)是根據(jù)條件判斷結(jié)果返回指定值，以下通過表6-6介紹Hive內(nèi)置的條件函數(shù)。表6-SEQ表6-\*ARABIC6Hive內(nèi)置的條件函數(shù)條件函數(shù)名返回類型用法描述IF()TIF(BOOLEAN

testCondition,

T

valueTrue,

T

valueFalseOrNull)：若指定判斷條件testCondition的返回結(jié)果為true，則返回值valueTrue，反之則返回值valueFalseOrNull。NVL()TNVL(T

value,

T

default_value)：如果value值為NULL則返回值default_value。COALESCE()TCOALESCE(T

v1,

T

v2,

...)：返回第一個非空值，其中v1、v2可以是值或列，若v1和v2為列，則返回列v1和列v2中第一個非空值。CASETCASE

a

WHEN

b

THEN

c

WHEN

d

THEN

e

ELSE

f

END：如果a=b則返回c，若a=d則返回e，否則返回f。CASE

WHEN

a

THEN

b

WHEN

c

THEN

d

ELSE

e

END：若a為true則返回b，若c為true則返回d，否則返回eISNULL()BOOLEANISNULL(a)：若a為NULL則返回true，否則返回false,與關(guān)系運算符A

IS

NULL一致。ISNOTNULL()BOOLEANISNOTNULL(a)：若a為NULL則返回false，否則返回true,與關(guān)系運算符A

IS

NOTNULL一致。【講練結(jié)合】【例6-15】使用IF函數(shù)對每個學生Hive課程評定等級，90分及以上評定為“優(yōu)秀”，否則評定為“合格”。SELECTstname,IF(course_scores['Hive']>=90,'優(yōu)秀','合格')FROMstudentdb.student;圖6-SEQ圖6-\*ARABIC19使用IF()函數(shù)進行判斷以上查詢結(jié)果表明，使用IF()條件函數(shù)進行判斷，當列course_scores['Hive']的值大于等于90時，返回優(yōu)秀，否則返回合格，共兩種情況。因此，學生Zhoumei的分數(shù)為98分，被評定為優(yōu)秀，其他同學則被評定為合格。【例6-16】使用CASE函數(shù)對每個學生Hive課程評定等級，90分及以上評定為“優(yōu)秀”，80至90之間評定為“良好”，60至80之間評定為“合格”，否則評定為“不合格”。SELECTstname,CASEWHENcourse_scores['Hive']>=90THEN'優(yōu)秀'WHENcourse_scores['Hive']>=80ANDcourse_scores['Hive']<90THEN'良好'WHENcourse_scores['Hive']>=60ANDcourse_scores['Hive']<80THEN'合格'ELSE'不合格'ENDFROMstudentdb.student;圖6-SEQ圖6-\*ARABIC20使用CASE()函數(shù)進行判斷以上查詢結(jié)果表明，使用CASE()條件函數(shù)進行判斷，當列course_scores['Hive']的值大于等于90時，返回優(yōu)秀；當列course_scores['Hive']的值大于等于80且小于90時，返回良好；當列course_scores['Hive']的值大于等于60且小于80時，返回合格；否則返回不合格，共4種情況。因此，學生Zhoumei的Hive分數(shù)為98分，被評定為優(yōu)秀；學生Wangyan分數(shù)為85，被評定為良好；學生Zhoudan等分數(shù)在60到80之間，被評定為合格；學生Wubo的分數(shù)為NULL，因此被評定為不合格。【新課講授】二、字符串函數(shù)字符串函數(shù)主要針對字符串數(shù)據(jù)類型的列或數(shù)據(jù)進行操作，下面通過表6-7介紹Hive內(nèi)置的常用字符串函數(shù)。表6-SEQ表6-\*ARABIC7Hive內(nèi)置的常用字符串函數(shù)字符函數(shù)名返回類型用法描述ASCII()INTascii(stringstr)：返回str中首個ASCII字符串的整數(shù)值CONCAT()STRINGCONCAT(STRING|BINARY

A,

STRING|BINARY

B,...)：對二進制字節(jié)碼或字符串按順序進行拼接，沒有分隔符。SUBSTR()STRINGSUBSTR(STRING|BINARY

str,

INT

start)：從start位置開始截取二進制字節(jié)碼或字符串str。SUBSTR(STRING|BINARY

str,

INT

start,

INT

len)：從start位置截取二進制字節(jié)碼或字符串str的指定長度len。CONCAT_WS()STRINGCONCAT_WS(STRING

SEP,

STRING

A,

STRING

B...)：以指定分隔符SEP對字符串按順序進行拼接。CONCAT_WS(STRING

SEP,

ARRAY<STRING>)：以指定分隔符SEP將ARRAY中的元素拼接成字符串。DECODE()STRINGDECODE(BINARY

bin,

STRING

charset)：使用指定字符集charset將二進制bin解析成字符串，字符集包括US-ASCII、ISO-8859-1、UTF-8等。ENCODE()BINARYENCODE(STRING

src,

STRING

charset)：使用指定字符集charset將字符串src解析成二進制數(shù)據(jù)，支持的字符集同DECODE()。FORMAT_NUMBER()stringFORMAT_NUMBER(numberx,INTd)：將數(shù)值X轉(zhuǎn)換成"#,###,###.##"格式字符串，并保留d位小數(shù)，如果d為0，將進行四舍五入且不保留小數(shù)GET_JSON_OBJECT()STRINGGET_JSON_OBJECT(STRING

json_string,

STRING

path)：從指定路徑path上的JSON字符串抽取出JSON對象json_string的值。INSTR()INTINSTR(STRING

str,

STRING

substr)：查找字符串str中子字符串substr出現(xiàn)的位置，若查找失敗則返回0。LENGTH()INTLENGTH(STRING

str)：獲取字符串str的長度。LOCATE()INTLOCATE(STRING

substr,STRING

str,INT

pos)：查找字符串str的pos位置后，字符串substr第一次出現(xiàn)的位置。LOWER()STRINGLOWER(STRING

str)：將字符串str的所有字母轉(zhuǎn)換成小寫字母。LPAD()STRINGLPAD(STRING

str,

INT

len,

STRING

pad)：返回字符串str指定長度len的內(nèi)容，若字符串str不滿足長度len，則從字符串左邊填充指定字符串pad，使字符串str的長度等于len。RPAD()STRINGRPAD(STRING

str,

INT

len,

STRING

pad)：返回字符串str指定長度len的內(nèi)容，若字符串str不滿足長度len則從字符串右邊填充指定字符串pad，使字符串str的長度等于len。LTRIM()STRINGLTRIM(STRING

str)：去除字符串str前面的空格。RTRIM()STRINGRTRIM(STRING

str)：去除字符串右邊的空格。TRIM()STRINGTRIM(STRING

str)：去除字符串str前后的空格。PARSE_URL()STRINGPARSE_URL(STRING

urlstring,

STRING

partToExtract,...)：返回從URL字符串urlstring中抽取指定部分的內(nèi)容，partToExtract指抽取的部分，包含HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE和

USERINFO。REGEXP_EXTRACT()STRINGREGEXP_EXTRACT(STRING

subject,STRING

pattern,INT

index)：抽取字符串subject中符合正則表達式pattern的第index部分的字符串。REGEXP_REPLACE()STRINGREGEXP_REPLACE(STRING

initial_string,STRING

pattern,STRING

replacement)：按照Java正則表達式pattern將字符串initial_string中符合條件的部分替換成replacement所指定的字符串。REPEAT()STRINGREPEAT(STRING

str,

INT

n)：重復輸出n次字符串str。REVERSE()STRINGREVERSE(STRING

str)：反轉(zhuǎn)字符串str。SPLIT(S)ARRAYSPLIT(STRING

str,

STRING

patten)：按照正則表達式patten來分割字符串str，將分割后的字符串以數(shù)組的形式返回。STR_TO_MAP()MAPSTR_TO_MAP(TEXT,DELIMITER1,DELIMITER2)：將字符串TEXT轉(zhuǎn)為MAP，DELIMITER1是鍵值對之間的分隔符，默認為逗號，DELIMITER2是鍵值之間的分隔符，默認為=。SUBSTR()STRINGSUBSTR(STRING|BINARY

str,

INT

start)：從start位置開始截取二進制字節(jié)碼或字符串str。SUBSTR(STRING|BINARY

str,

INT

start,

INT

len)：從start位置截取二進制字節(jié)碼或字符串str的指定長度len。SUBSTRING_INDEX()STRINGSUBSTRING_INDEX(STRING

str,

STRING

delim,

INT

count)：截取字符串str第count分隔符delim之前的內(nèi)容，如count為正數(shù)則從左邊開始截取，如果為負數(shù)則從右邊開始截取。TRANSLATE()STRINGTRANSLATE(STRING

input,

STRING

from,

STRING

to)：將字符串input中的字符串from替換為字符串to。UPPER()STRINGUPPER(STRING

str)：將字符串str的所有字母轉(zhuǎn)換為大寫。INITCAP()STRINGNITCAP(STRING

str)：將字符串str的首字母大寫。ISNOTNULL()BOOLEANISNOTNULL(a)

若a為NULL則返回false，否則返回true，與關(guān)系運算符A

IS

NOT

NULL一致。SENTENCES()ARRAY<ARRAY<STRING>>SENTENCES(STRINGstr,STRINGlang,STRINGlocale)：字符串str將被轉(zhuǎn)換成單詞數(shù)組，如：sentences('Hellothere!Howareyou?')=(("Hello","there"),("How","are","you"))NGRAMS()ARRAY<STRUCT<STRING,DOUBLE>>NGRAMS(ARRAY<ARRAY<STRING>>,INTN,INT

K,INTpf)：返回出現(xiàn)次數(shù)TOPK的的子序列，n表示子序列的長度?！局v練結(jié)合】【例6-17】使用ASCII()函數(shù)，返回'Hello,Hive!'中首個ASCII字符的整數(shù)值。SELECTASCII('Hello,Hive!');圖6-SEQ圖6-\*ARABIC21使用ASCII()函數(shù)獲取ascii碼值以上查詢結(jié)果表明，返回了字符'H'的ascii碼值72。【例6-18】使用CONCAT()函數(shù)將student表中的stid和stname依次拼接顯示。SELECTCONCAT('學號：',stid,'，姓名：',stname)FROMstudentdb.student;圖6-SEQ圖6-\*ARABIC22使用CONCAT()函數(shù)拼接字符串以上查詢結(jié)果顯示，四個字符串：'學號：'、stid、'，姓名：'、stname依次拼接在一起?！纠?-19】使用CONCAT_WS()函數(shù)對字段stid、stname和class使用分隔符','進行連接。SELECTCONCAT_WS(',',stid,stname,class)FROMstudentdb.student;圖6-SEQ圖6-\*ARABIC23使用CONCAT_WS()函數(shù)拼接字符串以上查詢結(jié)果顯示，CONCAT_WS()函數(shù)中第一個參數(shù)逗號為分隔符，將之后所有參數(shù)使用該分隔符進行了分隔顯示?！纠?-20】使用CONCAT_WS()函數(shù)對roommate中的元素使用分隔符','進行連接。SELECTCONCAT_WS('-',roommate)FROMstudentdb.student;圖6-SEQ圖6-\*ARABIC24使用CONCAT_WS()函數(shù)拼接數(shù)組元素以上查詢結(jié)果顯示，CONCAT_WS(STRINGSEP,ARRAY<STRING>)函數(shù)，通過第一個參數(shù)指定分隔符，能依次拼接第二個參數(shù)ARRAY中的各元素?！纠?-21】使用FORMAT_NUMBER()函數(shù)將student表中的course_scores字段中的Hive成績轉(zhuǎn)換為字符串，并保留0位小數(shù)。SELECTFORMAT_NUMBER(course_scores['Hive'],0)FROMstudentdb.student;圖6-SEQ圖6-\*ARABIC25使用FORMAT_NUMBER()函數(shù)實現(xiàn)數(shù)值轉(zhuǎn)換字符串以上查詢結(jié)果顯示，F(xiàn)ORMAT_NUMBER(numberx,INTd)函數(shù)將學生課程Hive分數(shù)進行四舍五入并保留0位小數(shù)后轉(zhuǎn)換成字符串。【例6-22】以下是json格式的學生數(shù)據(jù)信息，使用GET_JSON_OBJECT()函數(shù)取出以下數(shù)據(jù)中第1個學生的姓名。學生數(shù)據(jù)信息如下：{ "student":[{ "stid":"2018010101", "stname":"Zhangdan" },{ "stid":"2018010102", "stname":"Zhoumei" },{ "stid":"2018010201", "stname":"Wangyan" }]}SELECTGET_JSON_OBJECT('{"student":[{"stid":"2018010101","stname":"Zhangdan"},{"stid":"2018010102","stname":"Zhoumei"},{"stid":"2018010201","stname":"Wangyan"}]}','$.student[0].stname')stname;圖6-SEQ圖6-\*ARABIC26使用GET_JSON_OBJECT()函數(shù)獲取json格式中數(shù)據(jù)（一）以上查詢結(jié)果顯示，GET_JSON_OBJECT()函數(shù)獲取了第一個參數(shù)json數(shù)據(jù)中的第0個數(shù)據(jù)中的stname信息，即Zhangdan。命令中第二個參數(shù)使用$表示json變量標識，此處$可以理解為json對象本身，然后使用.或[]讀取對象或數(shù)組?！纠?-23】以下是對象包含數(shù)組類型的json格式學生數(shù)據(jù)信息，使用GET_JSON_OBJECT()函數(shù)取出數(shù)據(jù)中第2個學生的姓名。學生數(shù)據(jù)信息如下：{ "class":{ "classname":"BigData1801", "student":[{ "stid":"2018010101", "stname":"Zhangdan" },{ "stid":"2018010102", "stname":"Zhoumei" },{ "stid":"2018010201", "stname":"Wangyan" }] }}SELECTGET_JSON_OBJECT('{"class":{"classname":"BigData1801","student":[{"stid":"2018010101","stname":"Zhangdan"},{"stid":"2018010102","stname":"Zhoumei"},{"stid":"2018010201","stname":"Wangyan"}]}}','$.class.student[1].stname')stname;圖6-SEQ圖6-\*ARABIC27使用GET_JSON_OBJECT()函數(shù)獲取json格式中數(shù)據(jù)（二）【例6-24】使用LENGTH()函數(shù)返回字符串“Helloworld”的長度。SELECTstname,LENGTH(stname)stname_lengthFROMstudentdb.student;圖6-SEQ圖6-\*ARABIC28使用LENGTH()函數(shù)計算字符串長度以上查詢結(jié)果顯示，使用LENGTH()函數(shù)統(tǒng)計字符串長度時，一個空格也算做一個字符。【例6-25】使用LPAD()函數(shù)對字符串'Data'左邊用字符串'Big'進行填充。SELECTLPAD('Data',10,'Big'),LPAD('Data',2,'Big');圖6-SEQ圖6-\*ARABIC29使用LPAD()函數(shù)填充字符串以上查詢結(jié)果顯示，第一個命令使用LPAD()函數(shù)對字符串'Data'的左邊用字符串'Big'進行填充，直到字符串長度填充到10為止。第二個命令使用LPAD()函數(shù)進行填充時，由于字符串'Data'本身長度大于2，則不會填充且去掉字符串中多余的'ta'部分?！纠?-26】使用LTRIM()、RTRIM()、TRIM()函數(shù)刪除字符串中各位置的空格。SELECTCONCAT_WS(',','Begin',LTRIM('HelloHive'),'End');SELECTCONCAT_WS(',','Begin',RTRIM('HelloHive'),'End');SELECTCONCAT_WS(',','Begin',TRIM('HelloHive'),'End');圖6-SEQ圖6-\*ARABIC30使用LTRIM()、RTRIM()、TRIM()函數(shù)刪除字符串中空格以上查詢結(jié)果顯示，LTRIM()函數(shù)刪除掉了原字符串左邊的所有空格,RTRIM()函數(shù)刪除了字符串右邊空格，TRIM()函數(shù)刪除了字符串左右兩邊空格。【例6-27】使用SENTENCES()函數(shù)將字符串'Helloworld!HelloBigData。HelloHadoop.Hellohive!'轉(zhuǎn)換為句子數(shù)組。SELECTSENTENCES('Helloworld!HelloBigData。Helloworld,HelloHive!');圖6-SEQ圖6-\*ARABIC31使用SENTENCES()函數(shù)將字符串轉(zhuǎn)換為句子數(shù)組以上查詢結(jié)果顯示，SENTENCES()函數(shù)將字符串以“！”、“。”和“，”等標點符號作為第一維度分隔符，將str拆分成包含4個元素的一維句子數(shù)組；每個句子數(shù)組元素又以空格作為第二維度分隔符將每個句子數(shù)組元素分割轉(zhuǎn)換為單詞數(shù)組?！纠?-28】使用NGRAMS()函數(shù)統(tǒng)計以下字符串分詞后出現(xiàn)頻次最高的3個單詞。SELECTNGRAMS(SENTENCES('Helloworld!HelloBigData。Helloworld,HelloHive!'),1,3)count_word;圖6-SEQ圖6-\*ARABIC32使用NGRAMS()函數(shù)統(tǒng)計字符串詞頻以上查詢結(jié)果顯示，NGRAMS()函數(shù)通常與SENTENCES()函數(shù)一起使用，首先分詞，然后對連續(xù)1個單詞序列即每個單詞做詞頻統(tǒng)計，之后倒序排列分詞統(tǒng)計結(jié)果，最后將單詞出現(xiàn)頻次最高的前3個單詞統(tǒng)計結(jié)果返回。【例6-29】使用SPLIT()函數(shù)分割以下字符串。SELECTSPLIT('Ilovebigdatatechnology','')split_string;圖6-SEQ圖6-\*ARABIC33使用SPLIT()函數(shù)分割字符串以上查詢結(jié)果顯示，SPLIT()函數(shù)以空格為分隔符將字符串分割為5個部分，并將分割后5個部分的字符串以數(shù)組形式返回?！纠?-30】使用STR_TO_MAP()函數(shù)將以下字符串按照指定的分隔符轉(zhuǎn)換為map。SELECTSTR_TO_MAP('stname:Wubo,class:Network1801')map1,STR_TO_MAP('stname=Zhangdan/class=BigData1801','/','=')map2;圖6-SEQ圖6-\*ARABIC34使用STR_TO_MAP()函數(shù)轉(zhuǎn)換字符串以上查詢結(jié)果顯示，第一個STR_TO_MAP()函數(shù)使用默認分隔符逗號分割鍵值對，使用默認分隔符冒號分割鍵與值；第二個STR_TO_MAP()函數(shù)使用指定的分隔符'/'分割鍵值對，使用指定分隔符'='分割鍵與值。【新課講授】三、表生成函數(shù)與聚合函數(shù)“相反的”一類函數(shù)就是表生成函數(shù)，表生成函數(shù)是將指定數(shù)據(jù)或列中的數(shù)據(jù)拆分成多行數(shù)據(jù)，主要應(yīng)用于集合類型數(shù)據(jù)。下面通過表6-8介紹Hive內(nèi)置的常用表生成函數(shù)。