Chengru's Blog

【Big-Data】Lateral View多列转多行

 
  • 2022年 09月19日

What is Lateral view

The LATERAL VIEW clause is used in conjunction with generator functions such as EXPLODE, which will generate a virtual table containing one or more rows. LATERAL VIEW will apply the rows to each original output row.

简单来说，就是想把单行映射到产出表的多行，可以使用lateral view。

How to use it

使用指南：[Hive]Lateral View使用指南_@SmartSi的博客-CSDN博客_lateral view

官方文档：LATERAL VIEW Clause - Spark 3.3.0 Documentation (apache.org)

多列转多行

需求描述：原来表有多列，例如，id, src_a, src_b, src_c，现在转变成id, src，而src是原来三列变成了三行，即多列转多行的操作。

实现方法：hive实现多列转行_面向搜索引擎写bug的博客-CSDN博客_hive 多列转行

select
    a.id,
    b.label,
    b.value
from
    test0912_wkl a LATERAL VIEW explode (
        map(
            'yuwen',
            yuwen,
            'shuxue',
            shuxue,
            'yingyu',
            yingyu
        )
    ) b as label, value

select
    t.uid as src,
    ori as dst
from
    (
        select
            a.id,
            a.email,
            a.phone,
            a.uuid
        from
            tablea a
            join (
                select
                    id,
                    count(distinct uuid) as uuid_count
                from
                    tablea
                where
                    date = '${date}'
                    and id != 'false'
                    and uuid != 0
                    and id is not null
                group by
                    id
            ) b on a.id = b.id
        where
            a.date = '${date}'
        union
        select
            id,
            email,
            phone,
            uuid
        from
            tablea
        where
            date = '${date}'
            and (
                email is not null
                or phone is not null
                or id is not null
            )
            and uuid != 0
    ) t lateral view explode (
        array (
            email,
            id,
            phone
        )
    ) p as ori
搜索