数据仓库Hive应用实战 教案93-96课时：HIVE内置函数（三）

湖南汽车工程职业学院教师授课教案第12周第5-8课时累计96课时课程名称数据仓库HIVE应用授课课题HIVE内置函数（三）授课专业班级大数据2101班、2102班、2103班教学目标1、掌握条件函数的运用2、掌握字符串函数的运用3、运用表生成函数教学要点教学重点条件函数、字符串函数教学难点表生成函数课型一体化授课教法与学法（教具）示范演示法、任务驱动教学法、分组合作教学法课后作业完成智课堂作业、看视频熟练操作教学后记（教师课后填写）1、结合项目要多练；2、鼓励学生之间要相互请教、相互帮忙，合作解决问题，有利于他们能力的增长；3、掌握思维导图对于知识体系构建的重要性。授课教师王海霞、黄史记【课前复习】日期函数的使用【作业点评】展示智课堂学生作业完成情况，并做点评。【学习目标】以“学生信息系统”项目为实操载体，完成条件函数、字符串函数和表生成函数的使用。【课程导入】前面我们已经认识了常用的内置函数（聚合函数、集合函数、数学函数、类型转换函数和时间函数），今天继续学习内置函数中的条件函数、字符串函数和表生成函数的使用。【新课讲授】一、条件函数条件函数是根据条件判断结果返回指定值，以下通过表6-6介绍Hive内置的条件函数。表6-SEQ表6-\*ARABIC6Hive内置的条件函数条件函数名返回类型用法描述IF()TIF(BOOLEAN

testCondition,

T

valueTrue,

T

valueFalseOrNull)：若指定判断条件testCondition的返回结果为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,与关系运算符A

IS

NULL一致。ISNOTNULL()BOOLEANISNOTNULL(a)：若a为NULL则返回false，否则返回true,与关系运算符A

IS

NOTNULL一致。【讲练结合】【例6-15】使用IF函数对每个学生Hive课程评定等级，90分及以上评定为“优秀”，否则评定为“合格”。SELECTstname,IF(course_scores['Hive']>=90,'优秀','合格')FROMstudentdb.student;图6-SEQ图6-\*ARABIC19使用IF()函数进行判断以上查询结果表明，使用IF()条件函数进行判断，当列course_scores['Hive']的值大于等于90时，返回优秀，否则返回合格，共两种情况。因此，学生Zhoumei的分数为98分，被评定为优秀，其他同学则被评定为合格。【例6-16】使用CASE函数对每个学生Hive课程评定等级，90分及以上评定为“优秀”，80至90之间评定为“良好”，60至80之间评定为“合格”，否则评定为“不合格”。SELECTstname,CASEWHENcourse_scores['Hive']>=90THEN'优秀'WHENcourse_scores['Hive']>=80ANDcourse_scores['Hive']<90THEN'良好'WHENcourse_scores['Hive']>=60ANDcourse_scores['Hive']<80THEN'合格'ELSE'不合格'ENDFROMstudentdb.student;图6-SEQ图6-\*ARABIC20使用CASE()函数进行判断以上查询结果表明，使用CASE()条件函数进行判断，当列course_scores['Hive']的值大于等于90时，返回优秀；当列course_scores['Hive']的值大于等于80且小于90时，返回良好；当列course_scores['Hive']的值大于等于60且小于80时，返回合格；否则返回不合格，共4种情况。因此，学生Zhoumei的Hive分数为98分，被评定为优秀；学生Wangyan分数为85，被评定为良好；学生Zhoudan等分数在60到80之间，被评定为合格；学生Wubo的分数为NULL，因此被评定为不合格。【新课讲授】二、字符串函数字符串函数主要针对字符串数据类型的列或数据进行操作，下面通过表6-7介绍Hive内置的常用字符串函数。表6-SEQ表6-\*ARABIC7Hive内置的常用字符串函数字符函数名返回类型用法描述ASCII()INTascii(stringstr)：返回str中首个ASCII字符串的整数值CONCAT()STRINGCONCAT(STRING|BINARY

A,

STRING|BINARY

B,...)：对二进制字节码或字符串按顺序进行拼接，没有分隔符。SUBSTR()STRINGSUBSTR(STRING|BINARY

str,

INT

start)：从start位置开始截取二进制字节码或字符串str。SUBSTR(STRING|BINARY

str,

INT

start,

INT

len)：从start位置截取二进制字节码或字符串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解析成二进制数据，支持的字符集同DECODE()。FORMAT_NUMBER()stringFORMAT_NUMBER(numberx,INTd)：将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数GET_JSON_OBJECT()STRINGGET_JSON_OBJECT(STRING

json_string,

STRING

path)：从指定路径path上的JSON字符串抽取出JSON对象json_string的值。INSTR()INTINSTR(STRING

str,

STRING

substr)：查找字符串str中子字符串substr出现的位置，若查找失败则返回0。LENGTH()INTLENGTH(STRING

str)：获取字符串str的长度。LOCATE()INTLOCATE(STRING

substr,STRING

str,INT

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

str)：将字符串str的所有字母转换成小写字母。LPAD()STRINGLPAD(STRING

str,

INT

len,

STRING

pad)：返回字符串str指定长度len的内容，若字符串str不满足长度len，则从字符串左边填充指定字符串pad，使字符串str的长度等于len。RPAD()STRINGRPAD(STRING

str,

INT

len,

STRING

pad)：返回字符串str指定长度len的内容，若字符串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中抽取指定部分的内容，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)：反转字符串str。SPLIT(S)ARRAYSPLIT(STRING

str,

STRING

patten)：按照正则表达式patten来分割字符串str，将分割后的字符串以数组的形式返回。STR_TO_MAP()MAPSTR_TO_MAP(TEXT,DELIMITER1,DELIMITER2)：将字符串TEXT转为MAP，DELIMITER1是键值对之间的分隔符，默认为逗号，DELIMITER2是键值之间的分隔符，默认为=。SUBSTR()STRINGSUBSTR(STRING|BINARY

str,

INT

start)：从start位置开始截取二进制字节码或字符串str。SUBSTR(STRING|BINARY

str,

INT

start,

INT

len)：从start位置截取二进制字节码或字符串str的指定长度len。SUBSTRING_INDEX()STRINGSUBSTRING_INDEX(STRING

str,

STRING

delim,

INT

count)：截取字符串str第count分隔符delim之前的内容，如count为正数则从左边开始截取，如果为负数则从右边开始截取。TRANSLATE()STRINGTRANSLATE(STRING

input,

STRING

from,

STRING

to)：将字符串input中的字符串from替换为字符串to。UPPER()STRINGUPPER(STRING

str)：将字符串str的所有字母转换为大写。INITCAP()STRINGNITCAP(STRING

str)：将字符串str的首字母大写。ISNOTNULL()BOOLEANISNOTNULL(a)

若a为NULL则返回false，否则返回true，与关系运算符A

IS

NOT

NULL一致。SENTENCES()ARRAY<ARRAY<STRING>>SENTENCES(STRINGstr,STRINGlang,STRINGlocale)：字符串str将被转换成单词数组，如：sentences('Hellothere!Howareyou?')=(("Hello","there"),("How","are","you"))NGRAMS()ARRAY<STRUCT<STRING,DOUBLE>>NGRAMS(ARRAY<ARRAY<STRING>>,INTN,INT

K,INTpf)：返回出现次数TOPK的的子序列，n表示子序列的长度。【讲练结合】【例6-17】使用ASCII()函数，返回'Hello,Hive!'中首个ASCII字符的整数值。SELECTASCII('Hello,Hive!');图6-SEQ图6-\*ARABIC21使用ASCII()函数获取ascii码值以上查询结果表明，返回了字符'H'的ascii码值72。【例6-18】使用CONCAT()函数将student表中的stid和stname依次拼接显示。SELECTCONCAT('学号：',stid,'，姓名：',stname)FROMstudentdb.student;图6-SEQ图6-\*ARABIC22使用CONCAT()函数拼接字符串以上查询结果显示，四个字符串：'学号：'、stid、'，姓名：'、stname依次拼接在一起。【例6-19】使用CONCAT_WS()函数对字段stid、stname和class使用分隔符','进行连接。SELECTCONCAT_WS(',',stid,stname,class)FROMstudentdb.student;图6-SEQ图6-\*ARABIC23使用CONCAT_WS()函数拼接字符串以上查询结果显示，CONCAT_WS()函数中第一个参数逗号为分隔符，将之后所有参数使用该分隔符进行了分隔显示。【例6-20】使用CONCAT_WS()函数对roommate中的元素使用分隔符','进行连接。SELECTCONCAT_WS('-',roommate)FROMstudentdb.student;图6-SEQ图6-\*ARABIC24使用CONCAT_WS()函数拼接数组元素以上查询结果显示，CONCAT_WS(STRINGSEP,ARRAY<STRING>)函数，通过第一个参数指定分隔符，能依次拼接第二个参数ARRAY中的各元素。【例6-21】使用FORMAT_NUMBER()函数将student表中的course_scores字段中的Hive成绩转换为字符串，并保留0位小数。SELECTFORMAT_NUMBER(course_scores['Hive'],0)FROMstudentdb.student;图6-SEQ图6-\*ARABIC25使用FORMAT_NUMBER()函数实现数值转换字符串以上查询结果显示，FORMAT_NUMBER(numberx,INTd)函数将学生课程Hive分数进行四舍五入并保留0位小数后转换成字符串。【例6-22】以下是json格式的学生数据信息，使用GET_JSON_OBJECT()函数取出以下数据中第1个学生的姓名。学生数据信息如下：{ "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()函数获取json格式中数据（一）以上查询结果显示，GET_JSON_OBJECT()函数获取了第一个参数json数据中的第0个数据中的stname信息，即Zhangdan。命令中第二个参数使用$表示json变量标识，此处$可以理解为json对象本身，然后使用.或[]读取对象或数组。【例6-23】以下是对象包含数组类型的json格式学生数据信息，使用GET_JSON_OBJECT()函数取出数据中第2个学生的姓名。学生数据信息如下：{ "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()函数获取json格式中数据（二）【例6-24】使用LENGTH()函数返回字符串“Helloworld”的长度。SELECTstname,LENGTH(stname)stname_lengthFROMstudentdb.student;图6-SEQ图6-\*ARABIC28使用LENGTH()函数计算字符串长度以上查询结果显示，使用LENGTH()函数统计字符串长度时，一个空格也算做一个字符。【例6-25】使用LPAD()函数对字符串'Data'左边用字符串'Big'进行填充。SELECTLPAD('Data',10,'Big'),LPAD('Data',2,'Big');图6-SEQ图6-\*ARABIC29使用LPAD()函数填充字符串以上查询结果显示，第一个命令使用LPAD()函数对字符串'Data'的左边用字符串'Big'进行填充，直到字符串长度填充到10为止。第二个命令使用LPAD()函数进行填充时，由于字符串'Data'本身长度大于2，则不会填充且去掉字符串中多余的'ta'部分。【例6-26】使用LTRIM()、RTRIM()、TRIM()函数删除字符串中各位置的空格。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()函数删除字符串中空格以上查询结果显示，LTRIM()函数删除掉了原字符串左边的所有空格,RTRIM()函数删除了字符串右边空格，TRIM()函数删除了字符串左右两边空格。【例6-27】使用SENTENCES()函数将字符串'Helloworld!HelloBigData。HelloHadoop.Hellohive!'转换为句子数组。SELECTSENTENCES('Helloworld!HelloBigData。Helloworld,HelloHive!');图6-SEQ图6-\*ARABIC31使用SENTENCES()函数将字符串转换为句子数组以上查询结果显示，SENTENCES()函数将字符串以“！”、“。”和“，”等标点符号作为第一维度分隔符，将str拆分成包含4个元素的一维句子数组；每个句子数组元素又以空格作为第二维度分隔符将每个句子数组元素分割转换为单词数组。【例6-28】使用NGRAMS()函数统计以下字符串分词后出现频次最高的3个单词。SELECTNGRAMS(SENTENCES('Helloworld!HelloBigData。Helloworld,HelloHive!'),1,3)count_word;图6-SEQ图6-\*ARABIC32使用NGRAMS()函数统计字符串词频以上查询结果显示，NGRAMS()函数通常与SENTENCES()函数一起使用，首先分词，然后对连续1个单词序列即每个单词做词频统计，之后倒序排列分词统计结果，最后将单词出现频次最高的前3个单词统计结果返回。【例6-29】使用SPLIT()函数分割以下字符串。SELECTSPLIT('Ilovebigdatatechnology','')split_string;图6-SEQ图6-\*ARABIC33使用SPLIT()函数分割字符串以上查询结果显示，SPLIT()函数以空格为分隔符将字符串分割为5个部分，并将分割后5个部分的字符串以数组形式返回。【例6-30】使用STR_TO_MAP()函数将以下字符串按照指定的分隔符转换为map。SELECTSTR_TO_MAP('stname:Wubo,class:Network1801')map1,STR_TO_MAP('stname=Zhangdan/class=BigData1801','/','=')map2;图6-SEQ图6-\*ARABIC34使用STR_TO_MAP()函数转换字符串以上查询结果显示，第一个STR_TO_MAP()函数使用默认分隔符逗号分割键值对，使用默认分隔符冒号分割键与值；第二个STR_TO_MAP()函数使用指定的分隔符'/'分割键值对，使用指定分隔符'='分割键与值。【新课讲授】三、表生成函数与聚合函数“相反的”一类函数就是表生成函数，表生成函数是将指定数据或列中的数据拆分成多行数据，主要应用于集合类型数据。下面通过表6-8介绍Hive内置的常用表生成函数。