arrays - Hive中的数组拆分

Question

我有一个配置单元表“my_table”，其中包含一个数组<bigint>列Recommendation_ids

基础数据:

user_id|recommended_ids                                                                                   
1111198|[179995, 11186, 553248631043262469, 601906, 178276, 602901, 180032, 180016, 178286, 553248632557170387]
11|[178286655, 5532486]

我需要这样的输出(前5个Recommendation_ids为5列，也可以少于5列)

user_id col_1     col_2   col_3              col_4  col_5
1111198 179995    11186   553248631043262469 601906 178276
11      178286655 5532486 

我正在做如下

select
user_id
,split(recommended_ids,",")[0] as col_1
,split(recommended_ids,",")[1] as col_2
,split(recommended_ids,",")[2] as col_3
,split(recommended_ids,",")[3] as col_4
,split(recommended_ids,",")[4] as col_5
from my_table

但是低于错误

失败:无法将ClassCastException org.apache.hadoop.hive.serde2.objectinspector.StandardListObjectInspector强制转换为org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector

Answer 1

因为该列已经是array，所以您只需按索引即可获取元素。无需split。

select
user_id
,recommended_ids[0] as col_1
,recommended_ids[1] as col_2
,recommended_ids[2] as col_3
,recommended_ids[3] as col_4
,recommended_ids[4] as col_5
from my_table
where size(recommended_ids) >= 5 --to filter for array lengths >= 5