MaxCompute单字段拆分多行多列

解决场景

数据导入MaxCompute后，需要把某个字段String类型（多键值（key-value ）对 ） 拆分成多行，每行有都有key, value两列。

原数据：

• 比如uid (1234), region(cn-hz), tags, 其中tags值为“{k1:v1,k2:v2,k3:k4}”

  

期望处理结果：

转换成uid ,region,key,value

详细方案

1. 准备表
2. 插入数据
3. 处理数据，查询处理结果

注意

• 边界情况
• tags带特殊字符（,:等）需要自行测试

# 表结构

create table if not exists t1(
    region string,
    uid int,
    tags string
)partitioned by (ds string) lifecycle 1;


# 插入数据 ${dstime} 是变量，给定某天的值

insert overwrite table t1 partition (ds='${dstime}')
select "cn-hz" as region,1234 as uid,"{k1:v1,k2:v2,k3:k4}" as tags ;


# 结果拆分，这里考虑到如果tags的key包含“,” 或者 “:” 则可能需要对于数据做一些转义处理，
# 下面例子是假设tags不包含“,” 或者 “:” 
# TRANS_ARRAY 
# 2 表示不变化的字段的个数， 
# “,”表示分割tags的分割符， 
# uid,region是不变化的字段，
# SUBSTR(tags,2,LENGTH(tags)-2) 处理前后{}后的字段，如果后面的k1有引号需要注意引号的处理

SELECT  uid, region, SPLIT_PART(tags, ":", 1)  as key , SPLIT_PART(tags, ":", 2)  as value FROM (
SELECT  TRANS_ARRAY(
            2 
            ,","
            ,uid
            ,region
            ,SUBSTR(tags,2,LENGTH(tags)-2)
        ) AS (uid,region,tags)
FROM    t1
WHERE   ds = "20201112"
)tmp
;
           

参考文档：

TRANS_ARRAY 相关使用说明