Hive将数据选择到结构数组中 [英] Hive select data into an array of structs

问题描述

我试图在Hive中找到一种方法来从平面源中选择数据并将其输出到命名结构数组中。这是我正在寻找的一个例子...

样本数据：

  house_id，first_name，last_name 
 1，bob，jones 
 1，jenny，jones 
 2，sally，johnson 
 3，john，smith 
 3，barb，smith 
  

所需输出：

  1 [{first_name：bob，last_name：jones}，{first_name：jenny，last_name：jones}] 
 2 [{first_name：sally，last_name：johnson}] 
 3 [{first_name：john，last_name：smith}，{first_name ：barb，last_name：smith}] 
  

我尝试了collect_list和collect_set，但他们只允许原始数据类型。有关我如何在Hive中进行此操作的任何想法？

解决方案

我会使用 jar ，它是一个更好的实现 collect （并且使用复杂的数据类型）。

查询：

  add jar /path/to/jar/brickhouse-0.7.1.jar; 
创建临时函数收集为'brickhouse.udf.collect.CollectUDAF'; 
 
 select house_id 
，从db.table 
收集（named_struct（first_name，first_name，last_name，last_name））
 by house_id 
  

输出：

<$ p $ b $ 1 [{first_name：bob，last_name：jones}，{first_name：jenny，last_name：jones}]
2 [{first_name：sally，last_name：johnson}]
3 [{first_name：john，last_name：smith}，{first_name barb，last_name：smith}]


I am trying to figure out a way in Hive to select data from a flat source and output into an array of named struct(s). Here is a example of what I am looking for...

Sample Data:

house_id,first_name,last_name
1,bob,jones
1,jenny,jones
2,sally,johnson
3,john,smith
3,barb,smith

Desired Output:

1   [{"first_name":"bob","last_name":"jones"},{"first_name":"jenny","last_name":"jones"}]
2   [{"first_name":"sally","last_name":"johnson"}]
3   [{"first_name":"john","last_name":"smith"},{"first_name":"barb","last_name":"smith"}]

I tried collect_list and collect_set but they only allow primitive data types. Any thoughts of how I might go about this in Hive?

解决方案

I would use this jar, it is a much better implementation of collect (and takes complex datatypes).

Query:

add jar /path/to/jar/brickhouse-0.7.1.jar;
create temporary function collect as 'brickhouse.udf.collect.CollectUDAF';

select house_id
  , collect(named_struct("first_name", first_name, "last_name", last_name))
from db.table
group by house_id

Output:

1   [{"first_name":"bob","last_name":"jones"}, {"first_name":"jenny","last_name":"jones"}]
2   [{"first_name":"sally","last_name":"johnson"}]
3   [{"first_name":"john","last_name":"smith"},{"first_name":"barb","last_name":"smith"}]

这篇关于Hive将数据选择到结构数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！