sql—配置单元中的聚合id属性值记录

9jyewag0  于 2021-06-24  发布在  Hive
关注(0)|答案(1)|浏览(261)

我有一张表格,格式如下

ID  Property  Value

1    name      Tim

1    location  USA

1    age       30

2    name      Jack

2    location  UK

2    age       27

我想要以下格式的输出

ID  name  location age

1   Tim    USA     30

2   Jack   UK      27

用python我能做到

table_agg = table.groupby('ID')[['Property','Value']].apply(lambda x: dict(x.values))

p = pd.DataFrame(list(table_agg))

如何在配置单元中编写查询?

6ju8rftf

6ju8rftf1#

您可以使用collect\u list、map函数对数据进行分组,然后访问 array 基于密钥。
例子:

hive> create table t1(id int,property string,valu string) stored as orc;
hive> insert into t1 values(1,"name","Tim"),(1,"location","USA"),(1,"age","30"),(2,"name","Jack"),(2,"location","UK"),(2,"age","27");

hive> select id,
       va[0]["name"]name,
       va[1]["location"]location,
       va[2]["age"]age 
      from (
           select id,collect_list(map(property,value))va 
               from <table_name> group by id
          )t;

结果:

id      name    location        age
1       Tim     USA             30
2       Jack    UK              27

相关问题