Hive SQL学习之路–知识点1
split函数
1.用法
split(str,regex)
将字符串按照regex进行分割成字符串数组
常见用法:
select split(‘http://sale.jd.com/act/dfsdg.html‘,’/’) 结果:[“http:”,”“,”sale.jd.com”,”act”,”dfsdg.html”]
select split(‘http://sale.jd.com/act/dfsdg.html‘,’/’)[0] 结果:http:
其中,regex 为模式匹配参数,如果有特殊字符需要转义。
select split(“192\\168\\1\\1”,’\\’)[0] 结果:192
select split(‘192\\168\\1\\1’,’\\’)[0] 结果:192
select split(‘http://sale.jd.com‘,’//’)[1] 结果:sale.jd.com
like函数
1. Hive中LIKE查询使用通配符’%’的一个BUG–当转义符’\’遇到通配符’%’或’_’
IF函数
IF( Test Condition, True Value, False Value )
The IF condition evaluates the “Test Condition” and if the “Test Condition” is true, then it returns the “True Value”. Otherwise, it returns the False Value.
Example: IF(1=1, ‘working’, ‘not working’) returns ‘working’
COALESCE( value1,value2,… )
The COALESCE function returns the fist not NULL value from the list of values. If all the values in the list are NULL, then it returns NULL.
Example: COALESCE(NULL,NULL,5,NULL,4) returns 5
CASE Statement
The syntax for the case statement is:
CASE [ expression ]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
WHEN conditionn THEN resultn
ELSE result
END
Here expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition1, condition2, … conditionn).
HIVE中的引号问题