Hive

参考文献

1. https://cwiki.apache.org/confluence/display/hive/Languagemanual+udf

トイデータを定義する

各データを配列として定義し、さらに全データを配列に束ね、LATERAL VIEW で各データを各レコードに展開し、インデックスで各列の値をとっているだけである。

WITH tenki AS (
    SELECT
        data[0] AS toshi,
        data[1] AS tenki1,
        data[2] AS tenki2,
        data[3] AS tenki3
    FROM
    (
        SELECT
            ARRAY(
                ARRAY("札幌", "晴", "雪", "晴"),
                ARRAY("東京", "晴", "曇", "曇"),
                ARRAY("京都", "曇", "雪", "晴")
            ) AS data_
    ) x
    LATERAL VIEW EXPLODE(data_) lv AS data
)

SELECT * FROM tenki;

+--------------+---------------+---------------+---------------+--+
| tenki.toshi  | tenki.tenki1  | tenki.tenki2  | tenki.tenki3  |
+--------------+---------------+---------------+---------------+--+
| 札幌         | 晴            | 雪            | 晴            |
| 東京         | 晴            | 曇            | 曇            |
| 京都         | 曇            | 雪            | 晴            |
+--------------+---------------+---------------+---------------+--+

各レコードの V1～V3 列の値のユニークセットをとる

COLLECT_SET は aggregate function なので、V1～V3 列を同名の列にしておいて aggregate する必要がある。

SELECT
    toshi,
    COLLECT_SET(tenki0)
FROM
(
    SELECT toshi, tenki1 AS tenki0 FROM tenki
    UNION ALL
    SELECT toshi, tenki2 AS tenki0 FROM tenki
    UNION ALL
    SELECT toshi, tenki3 AS tenki0 FROM tenki
) x
GROUP BY
    toshi
;

+--------+------------------+--+
| toshi  |      _c1         |
+--------+------------------+--+
| 京都   | ["曇","晴","雪"] |
| 札幌   | ["晴","雪"]      |
| 東京   | ["晴","曇"]      |
+--------+------------------+--+

以下でも同じである。記述量は少ないが Hive にさせている処理は多い。

SELECT
    toshi,
    COLLECT_SET(tenki0)
FROM
(
    SELECT
        toshi, 
        ARRAY(tenki1, tenki2, tenki3) AS tenkis
    FROM tenki
) x
LATERAL VIEW EXPLODE(tenkis) lv AS tenki0
GROUP BY
    toshi
;

+--------+------------------+--+
| toshi  |      _c1         |
+--------+------------------+--+
| 京都   | ["曇","雪","晴"] |
| 札幌   | ["晴","雪"]      |
| 東京   | ["晴","曇"]      |
+--------+------------------+--+

ひらがなをカタカナにする

無理やりである。

SELECT
    translate(
        'びっぐさんだーまうんてん',
        CONCAT(
            'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほ',
            'まみむめもやゆよらりるれろわゐゑをんゔがぎぐげござじずぜぞ',
            'だぢづでどばびぶべぼぱぴぷぺぽぁぃぅぇぉっゃゅょゎ'
        ),
        CONCAT(
            'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホ',
            'マミムメモヤユヨラリルレロワヰヱヲンヴガギグゲゴザジズゼゾ',
            'ダヂヅデドバビブベボパピプペポァィゥェォッャュョヮ'
        )
    )
;

+---------------+--+
|      _c0      |
+---------------+--+
| ビッグサンダーマウンテン  |
+---------------+--+