Hive列转行函数详解

一、Hive列转行函数map

Hive列转行函数map的作用是将Map类型的列拆成多行，每一行对应Map中的一个键值对。这个函数会返回两个列，一个列包含键，一个列包含值。

SELECT key, value
FROM table_name
LATERAL VIEW explode(map_column) map_table AS key, value;

上述代码中，table_name是你想要处理的表的名称，map_column是你想要拆分的Map类型列的名称。

需要注意的是，使用map函数时输入数据必须是Map类型，否则会出错。

二、Hive列转行函数介绍

Hive列转行函数是一类将多列数据转换成多行的函数。常用的函数有：stack、inline等。

1、使用Hive列转行函数stack

Hive列转行函数stack将多列数据同步转换成多行。

SELECT col1, col2, col3
FROM (
    SELECT stack(3, col1, col2, col3) AS (col, value)
    FROM table_name
) sub_query
WHERE value IS NOT NULL;

上述代码中，table_name是你想要处理的表的名称，col1、col2、col3是你想要进行转换的列。这个例子中col1、col2、col3的元素数量必须相同，否则会出现错误。

2、使用Hive列转行函数inline

在Hive中，列和行的关系比较复杂。如果要转换成行，则需要使用LATERAL VIEW EXPLODE，但这种方法很麻烦。Hive开发者认为这种方法使用不方便，于是引入了Hive列转行函数inline。

SELECT id, value
FROM table_name
LATERAL VIEW inline(array(col1, col2, col3)) sub_query AS value;

上述代码中，table_name是你想要处理的表的名称，col1、col2、col3是需要转换的列。inline将这三个列转换成一个数组，sub_query就是这个数组。

三、Hive列转行函数的其他相关函数

1、Hive列转行函数impala

Impala是Hadoop生态系统中Hive的竞争对手。Impala提供了类似于Hive的列转行函数，例如stack和explode。

SELECT stack(3, col1, col2, col3) AS (col, value)
FROM table_name;

上述代码中col1、col2、col3是需要转换的列，table_name是需要处理的表的名称。

2、Oracle列转行函数

Oracle也提供了类似于Hive的列转行函数。unpivot是Oracle中的列转行函数，可以将多列数据转换成多行。

SELECT *
FROM table_name
UNPIVOT (
    value FOR col_name IN (col1, col2, col3)
);

上述代码中，col1、col2、col3是需要转换的列，table_name是需要处理的表的名称。col_name是转换后的列名，value是所有的值。

3、Hive行转列函数

Hive行转列函数与列转行函数相反，将多行数据转换成多列。

SELECT id, 
    MAX(CASE WHEN col_name = 'col1' THEN value END) AS col1,
    MAX(CASE WHEN col_name = 'col2' THEN value END) AS col2,
    MAX(CASE WHEN col_name = 'col3' THEN value END) AS col3
FROM (
    SELECT id, 'col1' AS col_name, col1 AS value FROM table_name
    UNION ALL
    SELECT id, 'col2' AS col_name, col2 AS value FROM table_name
    UNION ALL
    SELECT id, 'col3' AS col_name, col3 AS value FROM table_name
) sub_query
GROUP BY id;

上述代码中，table_name是需要处理的表的名称，col1、col2、col3是需要进行行转列的列。

4、Hive转字符串函数

Hive转字符串函数可以将多列数据拼接成一个字符串。

SELECT CONCAT_WS(',', col1, col2, col3) AS col_string
FROM table_name;

上述代码中，table_name是需要处理的表的名称，col1、col2、col3是需要进行字符串拼接的列。CONCAT_WS函数将这三列用逗号分隔，生成一个字符串。

5、Hive列转行拼接

Hive列转行拼接函数可以将一列数据拆分成多行，然后将拆分出来的行拼接成一个字符串。

SELECT col, CONCAT(col_value, ',')
FROM (
    SELECT col, explode(split(col_string, ',')) AS col_value
    FROM table_name
) sub_query;

上述代码中，table_name是需要处理的表的名称，col_string是需要拆分的列，sub_query使用explode函数将col_string拆分成多行，然后使用CONCAT函数将这些行拼接成一个字符串。

6、Hive行转列列转行

Hive行转列函数和列转行函数是相关的。例如，这个例子是行转列和列转行的组合，可以将一个列的数据拆分成多行，然后将这些行进行行转列操作，最后又进行了列转行操作。

SELECT id, day, event, COUNT(*) AS cnt
FROM (
    SELECT id, day, explode(split(events, ',')) AS event
    FROM table_name
) sub_query
GROUP BY id, day, event;

上述代码中，table_name是需要处理的表的名称，events是需要拆分的列，sub_query运用explode函数将events列拆分成多行，然后将这些行进行行转列操作，再将结果进行列转行操作。

原创文章，作者：RWDBP，如若转载，请注明出处：https://www.506064.com/n/331810.html