Hive函数练习

1. 空函数赋值
   NVL(value,default_value)
   如果value为null,则nvl函数返回default_value的值，否则返回value的值，如果两个参数都为null话返回null
   
2. 时间类操作
   date_format(field,format)
   date_add(field,number) 在此基础上加number天
   
   datediff(date1,date2) 时间相见操作 即前面的时间减去后者的天数
   
   regexp_replace 分隔符替换操作
   
3. case when
   求不同部门男女各多少人
   数据 /student/emp_sex.txt

   悟空	A	男
   大海	A	男
   宋宋	B	男
   凤姐	A	女
   婷姐	B	女
   婷婷	B	女
   

   首先创建表

   create table emp(
   	name string,
   	id string,
   	sex string
   ) 
   row format  delimited fields  terminated by '\t';
   

   加载本地数据到hdfs
   load data local inpath '/student/emp_sex.txt' into table student.emp;
   查询

   select id,
   sum(case sex when '男'  then 1 else 0 end) male_count, 
   sum(case sex when '女'  then 1 else 0 end) female_count
   from emp group by id;
   

4. 行转列
   CONCAT(string A/col, string B/col...)　返回输入字符串连接后的结果，支持任意个输入字符串;
   CONCAT_WS(separator, str1, str2,...) 是一个特殊形式的CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL，返回值也将为NULL。这个函数会跳过分隔符参数后的任何NULL 和空字符串。分隔符将被加到被连接的字符串之间;
   COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段
   需求: 把星座和血型一样的人归类到一起
   数据：

   孙悟空	白羊座	A
   大海	射手座	A
   宋宋	白羊座	B
   猪八戒	白羊座	A
   凤姐	射手座	A
   

   创建hive表

   create table person_info(
   	name string, 
   	constellation string, 
   	blood_type string
   )row format delimited fields terminated by "\t";
   

   sql实现

   select t1.base, 
   		concat_ws('|',collect_set(t1.name)) name 
   from (
   		select name,concat(constellation,",",blood_type) base 
   		      from person_info) t1
   group by t1.base;
   

   结果如下
   
5. 列转行
   EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行
   LATERAL VIEW
   用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
   解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
   需求：　将电影分类中的数组数据展开
   数据/student/movie.txt

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

   创建表

   create table movie(
   		movie string, 
   		category array<string>
   ) row format delimited fields terminated by "\t"
   collection items terminated by ",";
   

   装载数据到数据库
   load data local inpath '/student/movie.txt' into table movie;
   执行的sql

   select movie,category_name 
   from movie 
   lateral view explode(category) table_tmp(表的别名) as category_name(列的别名);
   

   [1,2,3,4]格式转化为４行
   SELECT explode(myCol) AS myNewCol FROM myTable;
   Map拆开的形式
   SELECT explode(myMap) AS (myMapKey, myMapValue) FROM myMapTable;

还有hive很多的内置函数参考网址大象教程