Hivesql解析多层Json数据

首先介绍下背景：在之前的一次需求中，需要将mongo表数据导入Hive表使用，导入Hive表时，mongo表数据被存成了一行行的Json格式的字符串，后续使用需要解析出Json中的各个字段。

先贴一条要解析的数据（格式化后的），数据我做了一些处理，去除了大部分类似的字段，只保留了比较有代表的几类。这是一条有着多重结构的Json，我要解析字段并将其打平成多条数据。

{
    "id":"1001",
    "sKUs":"20230909,20230908,20230907",
    "shiftInfo":[
        {
            "shiftId":"100101",
            "resInfo":[
                {"resId":"10010101","ordId":"ord001"},
                {"resId":"10010102"}
            ]
        },
        {
            "shiftId":"100102",
            "resInfo":[
                {"resId":"10010201","ordId":"ord003"},
                {"resId":"10010202","ordId":"ord004"}
            ]
        }
    ]
}

在hive表中，它长这样，存在z_test0907表的col字段中

首先，需要介绍下Hivesql中解析json的几个函数

1. get_json_object(jsonString, ‘$.key’)

   它可以返回jsonString中所传key的内容，不过一次只能返回一个字段，想要返回多个字段时，需要多写几个，获取没有的字段会返回null。例如，想要从上面数据中获取 id和skus字段，可以这么写

select
    get_json_object(col,'$.id') as `id`,
    get_json_object(col,'$.sKUs') as sKUs,
    get_json_object(col,'$.sss') as sss
from z_test0907;

结果如下：

2. json_tuple(col,‘key1’,‘key2’,…)

   json_tuple可以看作是get_json_object函数的加强版，它可以一次取出多个字段，获取没有的字段时一样会返回null，获取 id和skus字段，可以这么写

select
    json_tuple(col,'id','sKus','sss') as (`id`,sKUs,sss)
from z_test0907;

结果如下：

3. explode(Array/Map)

   explode称为炸裂函数，可以将一行数据炸裂成多行，它的入参只能是Map或Array，所以大部分情况下搭配split函数使用。比如，我们想要获取sKUs字段中的每个值，可以使用前文中的get_json_object函数、split函数与之结合。

   注意，直接使用explode函数时禁止出现其他表达式，否则会报错。

// 首先，使用get_json_object 获取sKUs字段
// 观察sKUs字段，可以发现它是用英文逗号隔开的，使用split函数将其分成Array
// 使用explode函数将生成的Array炸裂至多行
select
    explode(split(get_json_object(col,'$.sKUs'),',')) as sku,
    // explode(split(get_json_object(col,'$.sKUs'),',')) as sku2
    // col
from z_test0907;
// 放开sql任意中任意一行注释都会报错
// Error: Error while compiling statement: FAILED: SemanticException 3:0 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'sku2' (state=42000,code=40000)

结果如下：

4. 搭配使用 lateral VIEW (outer) 行转列函数

   行转列函数可以搭配udtf函数，比如前文提到的 explode和json_tuple函数，它需要跟在所查询表的后面，且必须加别名，from后可以跟多个 lateral VIEW ，如果所解析的列字段存在null值，需要加 outer，否则会报错。看一下具体示例就会明白了。

示例1：搭配json_tuple函数

// 搭配json_tuple函数
select
    t_alias.id,
    t_alias2.id2,
    t_alias.sKUs,
    t_alias.shiftInfo
from z_test0907
lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
lateral VIEW outer json_tuple(col,'id')t_alias2 as id2

结果如下：

示例2：搭配explode函数

// 搭配explode函数
select
    sku
from
(
    select
        get_json_object(col,'$.sKUs') as skus
    from z_test0907
)a lateral VIEW outer explode(split(a.skus,',')) t_alias as sku;

结果如下：

好了，介绍完可能会用到的的函数，我们现在正式开始解析

解析数据前，先观察数据的层级结构，根据实际需求情况确定要取哪些字段，它们分别在哪一层级、在什么结构里。这里说一个小技巧，面对{}使用 json_tuple,面对[]使用explode，从外向里一层一层解析。

废话不多说，开始解析。观察数据结构。

{
    "id":"1001",
    "sKUs":"20230909,20230908,20230907",
    "shiftInfo":[
        {
            "shiftId":"100101",
            "resInfo":[
                {"resId":"10010101","ordId":"ord001"},
                {"resId":"10010102"}
            ]
        },
        {
            "shiftId":"100102",
            "resInfo":[
                {"resId":"10010201","ordId":"ord003"},
                {"resId":"10010202","ordId":"ord004"}
            ]
        }
    ]
}

1. 最外面是{}，好，我们使用 json_tuple

select
    t_alias.id,
    t_alias.sKUs,
    t_alias.shiftInfo
from z_test0907
lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo

2. 解析sKUs，使用炸裂函数，将其分为sku

select
    a.id,
    skus.sku,
    a.shiftInfo
from 
(
    select
        t_alias.id,
        t_alias.sKUs,
        t_alias.shiftInfo
    from z_test0907
    lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
)a lateral VIEW outer explode(split(a.skus,',')) skus as sku;

好的，现在sku字段获取成功了，2条数据炸裂成了6条

3. 接下来，解析shiftInfo。

   shiftInfo是个Array，使用explode，但是呢，虽然这里看起来是数组，但它实际只是数组样式的字符串，我们需要逐步将其用多个步骤包括split函数转为数组。

第一步，去除shiftInfo最外层的[]

 regexp_extract(a.shiftInfo,'^\[(.+)\]$',1) 

第二步， 由于shiftInfo 是用 逗号 分割的，会和数据中其他逗号混淆，为了避免分割错误，将其转为其他字符，这里尽量使用数据中不会出现的符号，我这里是将 , 转为了 ||

// 因为是多层结构，内层也可能有{}，所以不能直接用 },{ 需要特殊处理下
 replace(regexp_extract(a.shiftInfo,'^\[(.+)\]$',1),'},{"shiftId"', '}||{"shiftId"')

第三步，使用split函数用 || 分割

split(replace(regexp_extract(a.shiftInfo,'^\[(.+)\]$',1),'},{"shiftId"', '}||{"shiftId"'),'\|\|'

第四步，好了，我们现在可以使用 explode函数了，将前几步合并起来，则得到下面的sql

select
    b.id,
    b.sku,
    shiftList.shift
from
(
    select
        a.id,
        skus.sku,
        replace(regexp_extract(shiftInfo,'^\[(.+)\]$',1),'},{"shiftId"', '}||{"shiftId"') as shiftInfo
    from
    (
        select
            t_alias.id,
            t_alias.sKUs,
            t_alias.shiftInfo
        from z_test0907
        lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
    )a lateral VIEW outer explode(split(a.skus,',')) skus as sku
)b lateral VIEW outer explode(split(shiftInfo,'\|\|'))shiftList AS shift

我们成功将shift分出来了，现在我们有12条数据了。

接下来，就是同样的方法，一层一层向下解析，最后，我们得到的最终sql为：

select
    e.id,
    e.sku,
    e.shiftId,
    res.resId,
    res.ordId
from (
    select
        d.id,
        d.sku,
        d.shiftId,
        resList.res
    from
    (
        select
            c.id,
            c.sku,
            rer_alias.shiftId,
            replace(regexp_extract(rer_alias.resInfo,'^\[(.+)\]$',1),'},{"resId"', '}||{"resId"') as resInfo
        from
        (
            select
                b.id,
                b.sku,
                shiftList.shift
            from
            (
                select
                    a.id,
                    skus.sku,
                    replace(regexp_extract(shiftInfo,'^\[(.+)\]$',1),'},{"shiftId"', '}||{"shiftId"') as shiftInfo
                from
                (
                    select
                        t_alias.id,
                        t_alias.sKUs,
                        t_alias.shiftInfo
                    from z_test0907
                    lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
                )a lateral VIEW outer explode(split(a.skus,',')) skus as sku
            )b lateral VIEW outer explode(split(shiftInfo,'\|\|'))shiftList AS shift
        )c lateral VIEW outer json_tuple(shift,'shiftId','resInfo')rer_alias as shiftId,resInfo
    )d lateral VIEW outer explode(split(resInfo,'\|\|'))resList AS res
)e lateral VIEW outer json_tuple(res,'resId','ordId')res as resId,ordId

而我们最终得到的结果为

到此解析正式结束，我们只需要将解析结果保存到目标表，就可以随时查询调用了。

为了便于理解，我每一次解析都只解析一个，其实 lateral VIEW outer 函数可以一次使用多个，对于同一层的解析，完全可以放在一个sql里，比如，第一层中的 炸裂 sKUs和解析shiftInfo。

select
    a.id,
    skus.sku,
    shift
from
(
    select
        t_alias.id,
        t_alias.sKUs,
        t_alias.shiftInfo
    from z_test0907
    lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
)a lateral VIEW outer explode(split(a.skus,',')) skus as sku
lateral VIEW outer explode(split(replace(regexp_extract(shiftInfo,'^\[(.+)\]$',1),'},{"shiftId"', '}||{"shiftId"'),'\|\|'))shiftList AS shift

结果和上面是一致的

结语，以上所介绍的方法比较简单且繁琐，由于笔者技术有限，只使用了几种简单函数多层子查询嵌套得出结果，sql并不算优雅美观，如果数据结构再复杂一点或者数据有些其他变化，则很可能无法正确解析，如果有大佬有更好的方法，欢迎评论区留言或链接指路。

Life is fantastic !