sql—是否有通过配置单元(hql)将行值转换为列键的方法?

xfb7svmp  于 2021-06-24  发布在  Hive
关注(0)|答案(2)|浏览(333)

我有一个大数据库,有些值是用复杂类型格式化的。例如,“sat”列键的格式如下:

id       sat
5  'a:100, b:200'
6  'b:300, c:150'
7  'some_other_unknown_key:900'

我想像下面的格式转换表,并做一些额外的统计。

id        a       b        c  some_other_unknown_key
5        100    200    null   null
6        null    300    150   null
7        null    null   null  900

有没有办法不通过hql创建新的表呢?

hgb9j2n6

hgb9j2n61#

您还可以尝试将字符串转换为有效的json(添加双引号和花括号),然后使用json\u tuple提取列,但是无论如何您应该知道列名并在查询中列出它们,配置单元无法动态选择列。
演示:

with your_table as (--Use your table instead of this
select stack(3, 5, 'a:100, b:200',
                6, 'b:300, c:150',
                7, 'some_other_unknown_key:900'
             ) as (id, sat) 
)

 select t.id, p.a, p.b, p.c, p.some_other_unknown_key
   from your_table t
        lateral view outer json_tuple(concat('{',regexp_replace(t.sat,'([a-z_A-Z]*):','\\"$1\\":'),'}'), 'a','b','c','some_other_unknown_key') p as a,b,c,some_other_unknown_key;

结果:

OK
id      a       b       c       some_other_unknown_key
5       100     200     NULL    NULL
6       NULL    300     150     NULL
7       NULL    NULL    NULL    900
Time taken: 0.128 seconds, Fetched: 3 row(s)

如果数据包含查询中未列出的某些键,则不会提取这些键。

7qhs6swi

7qhs6swi2#

使用 REGXP_EXTRACT ```
select id, regexp_extract(foo,'a:([0-9]+)',1) as a,
regexp_extract(foo,'b:([0-9]+)',1) as b,
regexp_extract(foo,'c:([0-9]+)',1) as c from t;

相关问题