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 相關使用說明