hadoop - Hive中的数据解析

Question

我有类似的数据

state:us,gallery:45,fin:UN,mode:normal,rate:3346 

作为配置单元表之一中的字符串数据类型。我想提取数据像

state gallery fin mode    rate
------------------------------
us    45      UN  normal  3346

作为目标表的行。我如何使用 hive 做到这一点

需要一个通用的方法来代替逗号，有时＃可能会来
那是
状态？我们的图库？45＃fin？UN＃mode？normal＃rate？3346

Answer 1

with t as (select 'state:us,gallery:45,fin:UN,mode:normal,rate:3346' as mycol)

select  mycol_map['state']      as state
       ,mycol_map['gallery']    as gallery
       ,mycol_map['fin']        as fin
       ,mycol_map['mode']       as mode
       ,mycol_map['rate']       as rate

from   (select  str_to_map(mycol) as mycol_map
        from    t
        ) t

+-------+---------+-----+--------+------+
| state | gallery | fin |  mode  | rate |
+-------+---------+-----+--------+------+
| us    |      45 | UN  | normal | 3346 |
+-------+---------+-----+--------+------+