Hive函数-内置函数

内容较多，可见《Hive官方文档》

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

1. 查看系统自带的函数
   hive> show functions;
2. 显示自带的函数的用法
   hive> desc function upper;
3. 详细显示自带的函数的用法
   hive> desc function extended upper;

常用内置函数（concat 、concat_ws、cast、get_json_object、parse_url）

#字符串连接函数： concat 
  select concat('abc','def’,'gh');
#带分隔符字符串连接函数： concat_ws 
  select concat_ws(',','abc','def','gh');
#cast类型转换
  select cast(1.5 as int);
#get_json_object(json 解析函数，用来处理json，必须是json格式)
   select get_json_object('{"name":"jack","age":"20"}','$.name');
#URL解析函数
   select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');
#explode：把map集合中每个键值对或数组中的每个元素都单独生成一行的形式

补充、常用函数：

1、数值函数（round、floor、ceil、rand、abs）

• 指定精度取整函数 : round
  语法: round(double a, int d)
  返回值: DOUBLE
  说明: 返回指定精度d的double类型
  举例：
  hive> select round(3.1415926,4);

  3.1416
  

• 向下取整函数 : floor
  语法: floor(double a)
  返回值: BIGINT
  说明: 返回等于或者小于该double变量的最大的整数
  举例：
  hive> select floor(3.1415926);
  3

  hive> select floor(25);
  25
  

• 向上取整函数 : ceil
  语法: ceil(double a)
  返回值: BIGINT
  说明: 返回等于或者大于该double变量的最小的整数
  举例：
  hive> select ceil(3.1415926);
  4

  hive> select ceil(46);
  46
  

• 取随机数函数 : rand
  语法: rand(),rand(int seed)
  返回值: double
  说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
  举例：
  hive> select rand();

  0.5577432776034763
  

• 绝对值函数 : abs
  语法: abs(double a) abs(int a)
  返回值: double int
  说明: 返回数值a的绝对值
  举例：
  hive> select abs(-3.9) from dual;
  3.9

  hive> select abs(10.9);
  10.9
  

2、日期函数（to_date、year、month、day、hour…）

• to_date(string timestamp):返回时间字符串中的日期部分,
  • 如to_date(‘1970-01-01 00:00:00’)=‘1970-01-01’
• current_date:返回当前日期
• current_timestamp:返回当前日期和时间
• year(date)：返回日期date的年,类型为int
  • 如year(‘2019-01-01’)=2019
• month(date)：返回日期date的月,类型为int,
  • 如month(‘2019-01-01’)=1
• day(date): 返回日期date的天,类型为int,
  • 如day(‘2019-01-01’)=1
• hour(date):返回日期date的时，类型为int
• weekofyear(date1)：返回日期date1位于该年第几周。
  • 如weekofyear(‘2019-03-06’)=10
• datediff(date1,date2):返回日期date1与date2相差的天数
  • 如datediff(‘2019-03-06’,‘2019-03-05’)=1
• date_add(date1,int1):返回日期date1加上int1的日期
  • 如date_add(‘2019-03-06’,1)=‘2019-03-07’
• date_sub(date1,int1):返回日期date1减去int1的日期
  • 如date_sub(‘2019-03-06’,1)=‘2019-03-05’
• months_between(date1,date2):返回date1与date2相差月份
  • 如months_between(‘2019-03-06’,‘2019-01-01’)=2
• add_months(date1,int1):返回date1加上int1个月的日期，int1可为负数
  • 如add_months(‘2019-02-11’,-1)=‘2019-01-11’
• last_day(date1):返回date1所在月份最后一天
  • 如last_day(‘2019-02-01’)=‘2019-02-28’
• trunc(date1,string1):返回日期最开始年份或月份。string1可为年(YYYY/YY/YEAR)或月(MONTH/MON/MM)。
  • 如trunc(‘2019-03-06’,‘MM’)=‘2019-03-01’，trunc(‘2019-03-06’,‘YYYY’)=‘2019-01-01’
• unix_timestamp():返回当前时间的unix时间戳，可指定日期格式。
  • 如unix_timestamp(‘2019-03-06’,‘yyyy-mm-dd’)=1546704180
• from_unixtime():返回unix时间戳的日期，可指定格式。
  • 如select from_unixtime(unix_timestamp(‘2019-03-06’,‘yyyy-MM-dd’),‘yyyyMMdd’)=‘20190306’

3、条件函数(if、case when、coalesce、isnull)

• if(boolean,t1,t2):若布尔值成立，则返回t1,反正返回t2。

  • 如if(1>2,100,200)返回200

• case when boolean then t1 else t2 end:若布尔值成立，则t1,否则t2,可加多重判断

• coalesce(v0,v1,v2):返回参数中的第一个非空值,若所有值均为null,则返回null。

  • 如coalesce(null,1,2)返回1

• isnull(a):若a为null则返回true，否则返回false

  – < 5000 屌丝
  – >= 5000 and < 10000 白领
  – >=10000 高富帅
  SELECT empid,deptid,sex,salary,
  CASE
  WHEN salary < 5000
  THEN ‘屌丝’
  WHEN salary >= 5000 AND salary < 10000
  THEN ‘白领’
  WHEN salary >= 10000
  THEN ‘高富帅’
  END AS LEVEL,
  CASE sex
  WHEN ‘male’
  THEN ‘靓仔’
  ELSE ‘靓妹’
  END AS alis
  FROM employee;

  – male 靓仔
  – female 靓妹
  SELECT empid,deptid,sex,salary,
  CASE sex
  WHEN ‘male’
  THEN ‘靓仔’
  ELSE ‘靓妹’
  END AS alis
  FROM employee;

  SELECT empid,deptid,sex,salary,
  CASE
  WHEN sex =‘male’
  THEN ‘靓仔’
  ELSE ‘靓妹’
  END AS alis
  FROM employee;

  SELECT empid,deptid,sex,salary,
  CASE
  WHEN sex =‘male’
  THEN ‘靓仔’
  ELSE ‘靓妹’
  END AS alis
  FROM employee;

  SELECT empid,deptid,sex,salary,
  IF(sex=‘male’,‘靓仔’,‘靓妹’)
  AS alis
  FROM employee;

4、字符串函数（length、concat、trim、rtrim、substr…等）

• length(string1):返回字符串长度
• concat(string1,string2):返回拼接string1及string2后的字符串
• concat_ws(sep,string1,string2):返回按指定分隔符拼接的字符串
• lower(string1):返回小写字符串，同lcase(string1)。upper()/ucase()：返回大写字符串
• trim(string1):去字符串左右空格，ltrim(string1):去字符串左空格。rtrim(string1):去字符串右空
• repeat(string1，int1)：返回重复string1字符串int1次后的字符串
• reverse(string1):返回string1反转后的字符串。
  • 如reverse(‘abc’)返回’cba’
• rpad(string1,len1,pad1):以pad1字符右填充string1字符串，至len1长度。
  • 如rpad(‘abc’,5,‘1’)返回’abc11’。lpad()：左填充
• split(string1,pat1):以pat1正则分隔字符串string1,返回数组。
  • 如split(‘a,b,c’,’,’)返回[“a”,“b”,“c”]
• substr(string1,index1,int1):以index位置起截取int1个字符。
  • 如substr(‘abcde’,1,2)返回’ab’

5、类型转换（cast）

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。

• cast(value AS TYPE)
  • select cast(‘1’ as DOUBLE); 返回1.0

6、爆炸函数（EXPLODE与LATERAL VIEW）

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行

• 原数据
  《疑犯追踪》 悬疑,动作,科幻,剧情
  《Lie to me》 悬疑,警匪,动作,心理,剧情
  《战狼2》 战争,动作,灾难

• 需求：将电影分类中的数组数据展开。结果如下：
  《疑犯追踪》 悬疑
  《疑犯追踪》 动作
  《疑犯追踪》 科幻
  《疑犯追踪》 剧情
  《Lie to me》 悬疑
  《Lie to me》 警匪
  《Lie to me》 动作
  《Lie to me》 心理
  《Lie to me》 剧情
  《战狼2》 战争
  《战狼2》 动作
  《战狼2》 灾难

1. 创建hive表并加载数据
   create table movie_info(
   movie string,
   category array)
   row format delimited fields terminated by “\t”
   collection items terminated by “,”;

   load data local inpath "/export/servers/hivedatas/movie.txt" into table movie_info;