Zenn
🚀

How to Pivot/Unpivot Table in Hive/Presto (PySpark)

2022/03/17に公開
#
メモ
#
presto
#
hive
#
pyspark
tech

データを集計する際、ある column で GROUPBY した後にその column の値ごとに column を作成したいみたいなことがよくある (いわゆる「横持ちにしたい」というやつ)。
例えば、以下のように各日付での grade ごとの pv の平均が見たいなどといった場合である。

date  name  grade  pv            date    grade_1  grade_2       
----------------------           ------------------------  
0501  John      1   3            0501        6.5     17.0  
0501  Mary      1  10     ->     0502        3.0     null
0501  Anna      2  14         
0501  Yuan      2  20   
0502  Taro      1   5 
0502  John      1   1

残念ながら Hive/Presto には PIVOT 句が存在しないため、以下のどちらかの方法で愚直に実装するしかない[1]。

  1. GROUP BY の後に CASE 句を使って該当データのみ抽出する方法
  2. GROUP BY の後に MAP を使って該当データのみ抽出する方法

ここでは、このそれぞれの場合にどのような操作を行えば良いのかをまとめる。

テーブル準備
hive
-- for pivot
CREATE TEMPORARY TABLE tmp (
    `date`  STRING
    , name  STRING
    , grade INT
    , pv    INT
);

INSERT INTO tmp VALUES
    ('0501', 'John', 1, 3)
    , ('0501', 'Mary', 1, 10)
    , ('0501', 'Anna', 2, 14)
    , ('0501', 'Yuan', 2, 20)
    , ('0502', 'Taro', 1, 5)
    , ('0502', 'John', 1, 1) 
;
presto
-- for pivot
SELECT
    *
FROM (
    VALUES
        ('0501', 'John', 1, 3)
        , ('0501', 'Mary', 1, 10)
        , ('0501', 'Anna', 2, 14)
        , ('0501', 'Yuan', 2, 20)
        , ('0502', 'Taro', 1, 5)
	, ('0502', 'John', 1, 1)
) AS tmp(date, name, grade, pv)
;

1. CASE 句を使って該当データのみ抽出する方法

SQL の標準記法のみを用いて記述できるため、最もシンプルでかつ Hive, Presto のどちらでも使える方法である。
2. の方法は Hive では使えないため、Hive でテーブルを pivot したければこちらの方法一択[2]。

-- backquote is unnecessary in Presto
SELECT
    `date`
    , AVG(CASE WHEN grade = 1 THEN pv END) AS grade_1
    , AVG(CASE WHEN grade = 2 THEN pv END) AS grade_2
FROM
    tmp
    GROUP BY
        `date`
;

2. MAP を使って該当データのみ抽出する方法

集約後の値を (key, value) 形式で保持させるような関数は Hive 標準では存在しないため、Presto のみで使える方法である。

presto
SELECT
    date
    , ELEMENT_AT(kv, 1) AS grade_1  -- key error　を防ぐため
    , ELEMENT_AT(kv, 2) AS grade_2  -- key error　を防ぐため
FROM (
    SELECT
        date
        , MAP_AGG(grade, pv) as kv
    FROM (
        SELECT
            date
            , grade
            , AVG(pv) as pv
        FROM 
	    tmp
            GROUP BY
                date
                , grade
        )
        GROUP BY
            date

番外編: PySpark

Hive/Presto とは全く関係ないが、テーブルの pivot をしたい場合 pyspark の pivot method がとても便利なので載せておく。

テーブル準備
pyspark
tmp = spark.createDataFrame(
    data = [
        ['0501', 'John', 1, 3],
        ['0501', 'Mary', 1, 10],
        ['0501', 'Anna', 2, 14],
        ['0501', 'Yuan', 2, 20],
        ['0502', 'Taro', 1, 5],
        ['0502', 'John', 1, 1]
    ],
    schema = ['date', 'name', 'grade', 'pv']
).repartition(1)
# default だと大量の partition ができてしまうので repartition しておく

# どれか
tmp.groupby('date').pivot('grade').avg('pv').show()
tmp.groupby('date').pivot('grade').agg({'pv': 'sum'}).show()
tmp.groupby('date').pivot('grade').agg(functions.avg('pv')).show()

# result
+----+---+----+
|date|  1|   2|
+----+---+----+
|0501|6.5|17.0|
|0502|3.0|null|
+----+---+----+

テーブルを pivot したければ pyspark がおすすめ。

脚注

  1. そのため、Hive/Presto で pivot しようというのは基本的に良くない考えである ↩︎

  2. 頑張ればできなくはないが複雑すぎるのでそこまで頑張る意味はないと思われる ↩︎

Discussion