hive行转列函数PIVOT

原创

mob649e816ab022 2023-07-22 11:19:50 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e816ab022的原创作品，请联系作者获取转载授权，否则将追究法律责任

Hive行转列函数PIVOT

在Hive中，行转列是指将一行数据转换成多列数据。这在某些情况下非常有用，例如将每个用户的多个属性转换为单独的列。Hive提供了一个内置函数PIVOT，用于实现行转列的操作。

PIVOT函数简介

PIVOT函数是Hive中的一个高级函数，它将一列数据转换为多列。它的语法如下：

PIVOT (aggregate_expression, pivot_column, [column_list])

其中，aggregate_expression是要进行聚合的表达式，pivot_column是要转换的列，column_list是要转换的目标列。aggregate_expression必须是聚合函数，例如SUM、COUNT等。

使用示例

下面我们通过一个示例来说明如何使用PIVOT函数进行行转列。

假设我们有一个用户表user_info，包含用户ID、用户名和用户属性。属性以键值对的形式存储在一列中，例如property列的值是key1:value1,key2:value2,key3:value3。

我们希望将每个属性转换为单独的列，并计算每个用户的属性数量。可以使用PIVOT函数来实现这个目标。

首先，创建一个名为user_info的表，并插入一些示例数据：

CREATE TABLE user_info (
  user_id INT,
  user_name STRING,
  property STRING
);

INSERT INTO user_info VALUES
  (1, 'user1', 'key1:value1,key2:value2,key3:value3'),
  (2, 'user2', 'key1:value1,key2:value2'),
  (3, 'user3', 'key1:value1,key3:value3');

接下来，使用PIVOT函数将属性转换为列，并计算每个用户的属性数量：

SELECT 
  user_id,
  user_name,
  COUNT(key1) AS count_key1,
  COUNT(key2) AS count_key2,
  COUNT(key3) AS count_key3
FROM (
  SELECT
    user_id,
    user_name,
    TRANSFORM (property)
    USING 'python my_script.py'
    AS (key STRING, value STRING)
  FROM user_info
) AS t
PIVOT (
  COUNT(value)
  FOR key IN (key1, key2, key3)
) AS p
GROUP BY user_id, user_name;

在上面的示例中，我们使用了TRANSFORM函数来将属性列转换为两列（key和value）。通过指定转换脚本'python my_script.py'，我们可以在转换时对属性进行进一步处理。

然后，我们使用PIVOT函数将属性的值转换为列。在这里，我们使用COUNT函数对每个属性进行计数。最后，通过GROUP BY语句对结果进行分组。