如何为HIVE中的每个组将几列汇总到JSON文件中 [英] How to aggregate several columns into a JSON file for each group in HIVE

问题描述

我想将下表转换为两行的新表.在新表中，每个 user_id 都有一个JSON文件，用于汇总第一个表中的所有其他列.

I want to convert the following table into a new table with two rows. In the new table, each user_id has one JSON file to summarize all other columns in the first table.

<代码>user_id Reservation_id晚间价格AAA 10001 1100AAA 10002 1120BBB 20003 7 350至user_id Reservation_detailsAAA {10001:{'nights':1，'price':100}，10002:{'nights':1，'price':120}}BBB {20003:{'nights':7，'price':350}}

推荐答案

select      user_id
           ,concat("{",concat_ws(',',collect_list(concat(string(reservation_id),":{'nights':",string(nights),",'price':",string(price),"}"))),"}")  as reservation_details

from        mytable

group by    user_id       

---

+---------+-----------------------------------------------------------------+
| user_id |                       reservation_details                       |
+---------+-----------------------------------------------------------------+
| AAA     | {10001:{'nights':1,'price':100},10002:{'nights':1,'price':120}} |
| BBB     | {20003:{'nights':7,'price':350}}                                |
+---------+-----------------------------------------------------------------+

这篇关于如何为HIVE中的每个组将几列汇总到JSON文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！