在Hive中，如何组合多个表以产生包含对象数组的单行? [英] In Hive, how to combine multiple tables to produce single row containing array of objects?

问题描述

我有两个表，如下所示:

I have two tables as follows:

users table
==========================  
| user_id   name     age |  
|=========================  
|  1        pete      20 |  
|  2        sam       21 |  
|  3        nash      22 |  
==========================

hobbies table
======================================
| user_id   hobby         time_spent |
|=====================================
|  1        football          2      |
|  1        running           1      |
|  1        basketball        3      |
======================================

第一个问题:我想做一个Hive查询，该查询可以返回以下格式的行:

First question: I would like to make a single Hive query that can return rows in this format:

{ "user_id":1, "name":"pete", "hobbies":[ {hobby: "football", "time_spent": 2}, {"hobby": "running", "time_spent": 1}, {"hobby": "basketball", "time_spent": 3} ] }

第二个问题:嗜好表是否如下:

========================================
| user_id   hobby             scores   |
|=======================================
|  1        football          2,3,1    |
|  1        running           1,1,2,5  |
|  1        basketball        3,6,7    |
========================================

是否有可能获得行输出，其中scores是输出中的列表，如下所示:

Would it be possible to get the row output where scores is a list in the output as shown below:

{ "user_id":1, "name":"pete", "hobbies":[ {hobby: "football", "scores": [2, 3, 1]}, {"hobby": "running", "scores": [1, 1, 2, 5]}, {"hobby": "basketball", "scores": [3, 6, 7]} ] }

推荐答案

我能够找到第一个问题的答案

I was able to find the answer to my first question

select u.user_id, u.name, 
       collect_list(
           str_to_map(
                concat_ws(",", array(
                    concat("hobby:", h.hobby), 
                    concat("time_spent:", h.time_spent)
                ))
           )
       ) as hobbies
from users as u
join hobbies as h on u.user_id=h.user_id
group by u.user_id, u.name;

这篇关于在Hive中，如何组合多个表以产生包含对象数组的单行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！