Apache Pig で 縦持ち→横持ち, 横持ち→縦持ちの変換

グループごとの列にデータをサマリする縦持ち→横持ち変換とか、その逆の横持ち→縦持ち変換とか、いやな感じの処理ですが、ちょくちょく出くわします。これを Pig でやっつけるにはどうすれば良いか。

縦持ち → 横持ち

こんな TSV ファイルがあったとします。列はそれぞれ (商品, 年度, 四半期, 売上) です。

ゲバ棒	2011	Q1	20
ゲバ棒	2011	Q3	15
ゲバ棒	2011	Q4	10
ゲバ棒	2012	Q1	10
ゲバ棒	2012	Q2	20
火炎瓶	2011	Q2	25
火炎瓶	2011	Q3	30
火炎瓶	2011	Q4	15

これを (商品名, 年度, 上半期売上, 下半期売上) としてサマリしたい。

Pig で書くと次のようになります。

tate = LOAD 'tate.tsv' AS (item, fisyear, quarter, amount);

tate_half_amount = FOREACH tate GENERATE
    item
    , fisyear
    , (quarter <= 'Q2' ? amount : 0) AS h1_amount
    , (quarter >= 'Q3' ? amount : 0) AS h2_amount;

by_item_fisyear = GROUP tate_half_amount BY (item, fisyear);

yoko = FOREACH by_item_fisyear GENERATE
    group.item
    , group.fisyear
    , SUM(tate_half_amount.h1_amount)
    , SUM(tate_half_amount.h2_amount);

STORE yoko INTO 'yoko';

次の SQL と同等です。

SELECT
    item
    , fisyear
    , SUM(CASE WHEN quarter <= 'Q2' THEN amount ELSE 0 END) AS h1_amount
    , SUM(CASE WHEN quarter >= 'Q3' THEN amount ELSE 0 END) AS h2_amount
FROM
    tate
GROUP BY
    item, fisyear

結果 (商品名, 年度, 上半期売上, 下半期売上) は次のようになります。

ゲバ棒	2011	20	25
ゲバ棒	2012	30	0
火炎瓶	2011	25	45

横持ち → 縦持ち (TOBAG, FLATTEN による解法)

横持ちにしたデータを、 (商品名, 年度, 半期, 売上) として半期ごとに行を持つ縦持ちデータにしたい。

Pig で書くと次のようになります。 TOBAG 関数でバッグ *1 を作って、 FLATTEN 演算子 *2 でタプルごとに行を作っています。 UNION でも OK なはずですが、後述する通りうまく行かなかった 勘違い。行けてた。。

-- 型指定「:int」無しでもよしなにやってくれるはずが、外すとエラーになった
yoko = LOAD 'yoko' AS (item, fisyear, h1_amount: int, h2_amount: int);

tate_with_zero = FOREACH yoko GENERATE
    item
    , fisyear
    , FLATTEN(TOBAG(('H1', h1_amount), ('H2', h2_amount))) AS (half, amount);

tate_half = FILTER tate_with_zero BY amount > 0;

STORE tate_half INTO 'tate_half';

結果 (商品名, 年度, 半期, 売上) は次のようになります。

ゲバ棒	2011	H1	20
ゲバ棒	2011	H2	25
ゲバ棒	2012	H1	30
火炎瓶	2011	H1	25
火炎瓶	2011	H2	45

横持ち → 縦持ち (UNION による解法)

横持ち → 縦持ち変換は次のようにもできます。

yoko = LOAD 'yoko' AS (item, fisyear, h1_amount, h2_amount);

h1 = FOREACH yoko GENERATE item, fisyear, 'H1' AS half, h1_amount AS amount;
h2 = FOREACH yoko GENERATE item, fisyear, 'H2' AS half, h2_amount AS amount;

tate_with_zero = UNION h1, h2;

tate_half = FILTER tate_with_zero BY amount > 0;

STORE tate_half INTO 'tate_half2';

DUMP tate_half;

これは次の SQL と同等です。 SQL の UNION ALL が Pig の UNION に相当します。

(
    SELECT
        item, fisyear, 'H1' AS half, h1_amount AS amount
    FROM
        yoko
) UNION ALL (
    SELECT
        item, fisyear, 'H2' AS half, h2_amount AS amount
    FROM
        yoko
)

UNION の片割れごとに Map 処理が走るので、少しだけ非効率的かも。

*1:タプルの配列みたいなもの。

*2:Hive の EXPLODE に相当する。