hive行列转置，爆炸函数实例

Hive的常用函数（nvl，case when，行转列，列转行，爆炸函数）

---

综述：

在Hive中有六类常用的函数，除了本文讲述的四类函数之外，还有rank函数，和开窗函数，Hive中的自定义函数一般被分成了三类

UDF:uer-defined-function 一进一出
UDAF:user-defined-aggregation-function 多进一出
UDTF:user-defined-generating-function 一进多出

我们可以通过相关的命令来获取某个函数的用法：

1. show functions 查询所有的自带函数

2. desc function extended case; 获得例子+用法

3. desc function case; 获得函数的用法

 NVL：

nvl(value,default_value)如果value为null，则返回default_value的值，否则返回value

1. select comm,nvl(comm,-1) from emp;

2. 0: jdbc:hive2://hadoop108:10000> select comm,nvl(comm,-1) from emp;

3. +---------+---------+--+

4. | comm | _c1 |

5. +---------+---------+--+

6. | NULL | -1.0 |

7. | 300.0 | 300.0 |

8. | 500.0 | 500.0 |

9. | NULL | -1.0 |

10. | 1400.0 | 1400.0 |

11. | NULL | -1.0 |

12. | NULL | -1.0 |

13. | NULL | -1.0 |

14. | NULL | -1.0 |

15. | 0.0 | 0.0 |

16. | NULL | -1.0 |

17. | NULL | -1.0 |

18. | NULL | -1.0 |

19. | NULL | -1.0 |

20. +---------+---------+--+

22. 如果员工的comm为NULL，则用领导id代替

23. select comm,nvl(comm,mgr) from emp;

24. 0: jdbc:hive2://hadoop108:10000> select comm,nvl(comm,mgr) from emp;

25. +---------+---------+--+

26. | comm | _c1 |

27. +---------+---------+--+

28. | NULL | 7902.0 |

29. | 300.0 | 300.0 |

30. | 500.0 | 500.0 |

31. | NULL | 7839.0 |

32. | 1400.0 | 1400.0 |

33. | NULL | 7839.0 |

34. | NULL | 7839.0 |

35. | NULL | 7566.0 |

36. | NULL | NULL |

37. | 0.0 | 0.0 |

38. | NULL | 7788.0 |

39. | NULL | 7698.0 |

40. | NULL | 7566.0 |

41. | NULL | 7782.0 |

42. +---------+---------+--+

case when:

1. [isea@hadoop108 datas]$ cat emp_sex.txt

2. 悟空 A 男

3. 八戒 A 男

4. 刘备 B 男

5. 嫦娥 A 女

6. 大乔 B 女

7. 小乔 B 女

10. 需求：求出部门和对应的员工的性别人数

11. A 2 1

12. B 1 2

14. 创建hive表并导入数据：

15. create table emp_sex(name string,dept_id string,sex string)

16. row format delimited

17. fields terminated by '\t';

19. load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;

20. 0: jdbc:hive2://hadoop108:10000> select * from emp_sex;

21. +---------------+------------------+--------------+--+

22. | emp_sex.name | emp_sex.dept_id | emp_sex.sex |

23. +---------------+------------------+--------------+--+

24. | 悟空 | A | 男 |

25. | 八戒 | A | 男 |

26. | 刘备 | B | 男 |

27. | 嫦娥 | A | 女 |

28. | 大乔 | B | 女 |

29. | 小乔 | B | 女 |

30. +---------------+------------------+--------------+--+

32. > select dept_id,count(sex) from emp_sex group by dept_id;

33. +----------+------+--+

34. | dept_id | _c1 |

35. +----------+------+--+

36. | A | 3 |

37. | B | 3 |

38. +----------+------+--+

41. 我们来尝试分析一下，如果单纯的使用group by 对dept_id 分组和聚合函数sum，我们求的是部门下所有的

42. 员工的总数，并不是对于区分性别，所以我们需要一种方式，当检查到sex为男的时候，记录一下man变量为

43. 1，当检查到sex为女的时候对women 加1

45. 所以使用下面的查询语句：

46. select dept_id,sum(case sex when '男' then 1 else 0 end) male_count,

47. sum(case sex when '女' then 1 else 0 end) female_count

48. from emp_sex group by dept_id;

49. +----------+-------------+---------------+--+

50. | dept_id | male_count | female_count |

51. +----------+-------------+---------------+--+

52. | A | 2 | 1 |

53. | B | 1 | 2 |

54. +----------+-------------+---------------+--+

56. 或者使用下面这种功能类似的写法

57. select dept_id,sum(case when sex = '男' then 1 else 0 end) male_count,

58. sum(case when sex = '女' then 1 else 0 end) female_count

59. from emp_sex group by dept_id;

61. +----------+-------------+---------------+--+

62. | dept_id | male_count | female_count |

63. +----------+-------------+---------------+--+

64. | A | 2 | 1 |

65. | B | 1 | 2 |

66. +----------+-------------+---------------+--+

行转列，CONCAT，CONCAT_WS，COLLECT_SET：

什么是行转列呢？

1. 将类似于下面的形式：

2. A

3. B

4. C

5. 转化为类似于下面的形式：

6. A B C

7. 即将多行转化为一行，排在一行了，就成了一列对吧

关于行转列主要有三个函数，

1. concat:

2. select concat('liubei','xihuan','xiaoqiao');

4. 0: jdbc:hive2://hadoop108:10000> select concat('liubei','xihuan','xiaoqiao');

5. OK

6. +-----------------------+--+

7. | _c0 |

8. +-----------------------+--+

9. | liubeixihuanxiaoqiao |

10. +-----------------------+--+

12. concat_ws:

13. 0: jdbc:hive2://hadoop108:10000> select concat_ws('|',array('liiubei','xihuan','xiaoqiao'));

14. OK

15. +--------------------------+--+

16. | _c0 |

17. +--------------------------+--+

18. | liiubei|xihuan|xiaoqiao |

19. +--------------------------+--+

20. 1 row selected (0.096 seconds)

22. collect_set(col)将传入的内容去重，并放置到一个数组中。

24. [isea@hadoop108 datas]$ cat contellation.txt

25. 悟空 白羊座 A

26. 张飞 射手座 A

27. 刘备 白羊座 B

28. 八戒 白羊座 A

29. 小乔 射手座 A

31. 创建hive表并导入数据：

32. create table person_info(name string,contellation string,blood_type string)

33. row format delimited

34. fields terminated by '\t';

36. load data local inpath '/opt/module/datas/contellation.txt' into table person_info;

38. 0: jdbc:hive2://hadoop108:10000> select * from person_info;

39. OK

40. +-------------------+---------------------------+-------------------------+--+

41. | person_info.name | person_info.contellation | person_info.blood_type |

42. +-------------------+---------------------------+-------------------------+--+

43. | 悟空 | 白羊座 | A |

44. | 张飞 | 射手座 | A |

45. | 刘备 | 白羊座 | B |

46. | 八戒 | 白羊座 | A |

47. | 小乔 | 射手座 | A |

48. +-------------------+---------------------------+-------------------------+--+

49. 把星座和血型一样的人归类到一起:

51. t：先将星座和血型归类到一起，

52. select name,concat(contellation,",",blood_type) base

53. from person_info;

55. +-------+--------+--+

56. | name | base |

57. +-------+--------+--+

58. | 悟空 | 白羊座,A |

59. | 张飞 | 射手座,A |

60. | 刘备 | 白羊座,B |

61. | 八戒 | 白羊座,A |

62. | 小乔 | 射手座,A |

63. +-------+--------+--+

65. select t.base,concat_ws('|',collect_set(name)) names

66. from (

67. select name,concat(contellation,",",blood_type) base

68. from person_info

69. ) t

70. group by t.base;

72. +---------+--------+--+

73. | t.base | names |

74. +---------+--------+--+

75. | 射手座,A | 张飞|小乔 |

76. | 白羊座,A | 悟空|八戒 |

77. | 白羊座,B | 刘备 |

78. +---------+--------+--+

79. 这里使用group by 将base分组，使用collect_set将所有name去重后存放到一个数组中，在使用concat_ws

80. 将数组中的元素连接起来。

列转行：EXPLODE，LATERAL VIEW：

什么是列转行呢？

1. 把类似于

2. A , B , C

3. 转化为

4. A

5. B

6. C

列转行主要有两个函数：

1. select explode(array('liubei','zhangfei','guanyu'));

2. +-----------+--+

3. | col |

4. +-----------+--+

5. | liubei |

6. | zhangfei |

7. | guanyu |

8. +-----------+--+

9. select explode(map('liubei','18','zhangfei','19'));

11. +-----------+--------+--+

12. | key | value |

13. +-----------+--------+--+

14. | liubei | 18 |

15. | zhangfei | 19 |

16. +-----------+--------+--+

19. 来看一下爆炸函数的局限性：在使用爆炸函数的时候，select后面只能跟爆炸函数，其他的不能跟

20. select '1',explode(array('liubei','zhangfei','guanyu'));

21. 0: jdbc:hive2://hadoop108:10000> select '1',explode(array('liubei','zhangfei','guanyu'));

22. FAILED: SemanticException [Error 10081]: UDTF s are not supported outside the SELECT clause, nor nested in expressions

25. lateral view:斜写视图，为了解决爆炸函数的局限性，结合UDTF函数(如，explode())一起使用。

26. 将电影分类中的数组数据展开

27. 期望：

28. 《疑犯追踪》 悬疑

29. 《疑犯追踪》 动作

30. 《疑犯追踪》 科幻

31. 《疑犯追踪》 剧情

32. 《Lie to me》 悬疑

33. 《Lie to me》 警匪

34. 《Lie to me》 动作

35. 《Lie to me》 心理

36. 《Lie to me》 剧情

37. 《战狼2》 战争

38. 《战狼2》 动作

39. 《战狼2》 灾难

42. [isea@hadoop108 datas]$ cat move_info.txt

43. 《疑犯追踪》 悬疑,动作,科幻,剧情

44. 《Lie to me》 悬疑,警匪,动作,心理,剧情

45. 《战狼2》 战争,动作,灾难

47. 创建hive表并导入数据：

48. create table movie_info(name string,category array<string>)

49. row format delimited

50. fields terminated by '\t'

51. collection items terminated by ',';

53. load data local inpath '/opt/module/datas/move_info.txt' into table movie_info;

55. 下面使用测写视图的方式，在求出爆炸函数结果的同时，求出除了爆炸函数字段之外的字段。需要添加爆炸之

56. 后的表名，和爆炸之后的字段名，这个字段名可以放置于select之后，查询爆炸之后的字段值。

58. select name,category_info

59. from movie_info

60. lateral view explode(category) tmp_tbl as category_info;

62. +--------------+----------------+--+

63. | name | category_info |

64. +--------------+----------------+--+

65. | 《疑犯追踪》 | 悬疑 |

66. | 《疑犯追踪》 | 动作 |

67. | 《疑犯追踪》 | 科幻 |

68. | 《疑犯追踪》 | 剧情 |

69. | 《Lie to me》 | 悬疑 |

70. | 《Lie to me》 | 警匪 |

71. | 《Lie to me》 | 动作 |

72. | 《Lie to me》 | 心理 |

73. | 《Lie to me》 | 剧情 |

74. | 《战狼2》 | 战争 |

75. | 《战狼2》 | 动作 |

76. | 《战狼2》 | 灾难 |

77. +--------------+----------------+--+

80. 这里需要指出的是：在键表的时候一定不能对于集合数据类型，如果没有添加

81. collection items terminated by ','表中的数据是这样的：

82. +------------------+----------------------+--+

83. | movie_info.name | movie_info.category |

84. +------------------+----------------------+--+

85. | 《疑犯追踪》 | ["悬疑,动作,科幻,剧情"] |

86. | 《Lie to me》 | ["悬疑,警匪,动作,心理,剧情"] |

87. | 《战狼2》 | ["战争,动作,灾难"] |

88. +------------------+----------------------+--+

89. category是一个完整的字符串，也即array中只有一个元素

90. 添加了分隔符号之后是这样的：

91. +------------------+-----------------------------+--+

92. | movie_info.name | movie_info.category |

93. +------------------+-----------------------------+--+

94. | 《疑犯追踪》 | ["悬疑","动作","科幻","剧情"] |

95. | 《Lie to me》 | ["悬疑","警匪","动作","心理","剧情"] |

96. | 《战狼2》 | ["战争","动作","灾难"] |

97. +------------------+-----------------------------+--+