Hive中数组array的相关应用

select array(1,3,5) c1,split('a,c,b',',') c2;
+----------+----------------+--+
|    c1    |       c2       |
+----------+----------------+--+
| [1,3,5]  | ["a","c","b"]  |
+----------+----------------+--+

select array(1,3,5)[0] c1,array('a','b','c')[3] c2;
+-----+-------+--+
| c1  |  c2   |
+-----+-------+--+
| 1   | NULL  |
+-----+-------+--+

select array_max(array(1,3,5)) c1,array_max(array('a','b','c')) c2;
+-----+-----+--+
| c1  | c2  |
+-----+-----+--+
| 5   | c   |
+-----+-----+--+

select array_min(array(1,3,5)) c1,array_min(array('a','b','c')) c2;
+-----+-----+--+
| c1  | c2  |
+-----+-----+--+
| 1   | a   |
+-----+-----+--+

select array_join(array(1,3,5),'_') c1,concat_ws('#',array('a','b','c')) c2;
+--------+--------+--+
|   c1   |   c2   |
+--------+--------+--+
| 1_3_5  | a#b#c  |
+--------+--------+--+

select array_sort(array(1,5,3)) c1,array_sort(array('c','b','a')) c2;
+----------+----------------+--+
|    c1    |       c2       |
+----------+----------------+--+
| [1,3,5]  | ["a","b","c"]  |
+----------+----------------+--+

split(reverse(concat_ws(delimiter,array_sort(array))),delimiter)：按降序排序单字符元素数组。reverse(string)：反转字符串反转。思路：a.将数组升序排序；b.转成字符串；c.反转字符串；d.将字符串拆成数组。

select split(reverse(concat_ws('#',array_sort(array('c','a','b')))),'#') c1;
+----------------+--+
|       c1       |
+----------------+--+
| ["c","b","a"]  |
+----------------+--+

按降序排列长字符元素数组。explode(array)：炸裂数组（列转行）；row_number()over()：开窗排序函数；collect_list()：收集数据成数组（列转行）。思路：a.炸裂数组；b.按照id开窗分组为元素降序排序，并与元素拼接；c.按照id分组，收集所有元素，注意要用collect_list（不去重），不能用collect_set（去重）并拼接成字符串；d.去掉排序序号和连接符之后再拆分成数组。

select keyid,split(regexp_replace(concat_ws('\073',collect_list(e_str)),'(^|\073)([0-9]+_)','$1'),'\073') a
from (
  select keyid,concat_ws('_',row_number()over(partition by keyid order by e desc),e) e_str
  from (
    select keyid,explode(a) e
    from (select 1 keyid,array('dbc','aef','ghij','aef','ghij','aef','ghij','aef','ghij','aef','ghij') a) t0
    ) t1
  ) t2
group by keyid;


+--------+---------------------------------------------------------------------------+--+
| keyid  |                                     a                                     |
+--------+---------------------------------------------------------------------------+--+
| 1      | ["ghij","ghij","ghij","ghij","ghij","dbc","aef","aef","aef","aef","aef"]  |
+--------+---------------------------------------------------------------------------+--+

array_contains(array, value)：判断数组中是否包含指定的值。包含返回true，否则返回false。

select array_contains(array(1,2,3), 2) c1,array_contains(array('a','b','c'), 'd') c2;
+-------+--------+--+
|  c1   |   c2   |
+-------+--------+--+
| true  | false  |
+-------+--------+--+

array_position(array, value)：返回value在数组中的位置，如果不存在则返回0。注意位置和角标不同，角标=位置-1。

select array_position(array(1,2,3), 4) c1,array_position(array('a','b','c'), 'a') c2;
+-----+-----+--+
| c1  | c2  |
+-----+-----+--+
| 0   | 1   |
+-----+-----+--+

array_remove(array, value)：去除数组中的所有value元素。

select array_remove(array(1,2,3,2), 2) c1,array_remove(array('a','b','c'), 'd') c2;
+--------+----------------+--+
|   c1   |       c2       |
+--------+----------------+--+
| [1,3]  | ["a","b","c"]  |
+--------+----------------+--+

array_except(array1, array2)：在数组array1中去除掉所有数组array2中的元素（数组array1比数组array2多的元素）。

select array_except(array(1,2,3,2),array(2,3)) c1,array_except(array('a','b','c'),array('d')) c2;
+------+----------------+--+
|  c1  |       c2       |
+------+----------------+--+
| [1]  | ["a","b","c"]  |
+------+----------------+--+

array_intersect(array1, array2)：返回一个包含所有同时在数组array1和数组array2中的元素的数组（数组array1和数组array2的交集元素)。

select array_intersect(array(1,2,3,2),array(2,3)) c1,array_intersect(array('a','b','c'),array('d')) c2;
+--------+-----+--+
|   c1   | c2  |
+--------+-----+--+
| [2,3]  | []  |
+--------+-----+--+

split(concat_ws(delimiter,array1,array2,array3),delimiter)：把相同类型的多个数组合并成一个数组。

select split(concat_ws('\073',array('a','c'),array('b','c'),array('d')),'\073') c1;
+------------------------+--+
|           c1           |
+------------------------+--+
| ["a","c","b","c","d"]  |
+------------------------+--+

在把数组array插入到hive表（有array类型字段）中时，如果报错，有可能是数组中没有元素。

select id,collect_set(b) b_ar
from (
select 1 id, null b
union 
select 1 id, null b
) t
group by id;

+-----+-------+--+
| id  | b_ar  |
+-----+-------+--+
| 1   | []    |
+-----+-------+--+

解决办法：a.判断数组元素个数，size(array)=0时，直接插入NULL值；

select id,if(size(collect_set(b))=0,null,collect_set(b)) b_ar
from (
select 1 id, null b
union 
select 1 id, null b
) t
group by id;

+-----+-------+--+
| id  | b_ar  |
+-----+-------+--+
| 1   | NULL  |
+-----+-------+--+

解决办法：b.拼接成字符串（默认变成””），再拆分成数组。

select id,concat_ws('\073',(collect_set(b))) b_str
from (
select 1 id, null b
union 
select 1 id, null b
) t
group by id;

+-----+--------+--+
| id  | b_str  |
+-----+--------+--+
| 1   |        |
+-----+--------+--+


select id,split(concat_ws('\073',(collect_set(b))),'\073') b_ar
from (
select 1 id, null b
union 
select 1 id, null b
) t
group by id;

+-----+-------+--+
| id  | b_ar  |
+-----+-------+--+
| 1   | [""]  |
+-----+-------+--+