（Hive・HiveQL）グループごとに日時を集計して上位を表示する

複数サーバがそれぞれ出すアクセスログを集計し、サーバごとの時間当たり最大アクセス数とその時間を出そうとしています。
Hadoop/Hiveを使ったログ解析のしくみがすでにあるので、これに機能追加するかたちで実装しています。

Hadoop内で必要なログは収集しました。
希望するかたちの解析結果を出すため、Hiveを使います。
HiveはHiveQLというSQLと似たスクリプトを書くことで、いい感じにデータ分析できます。
ようするにSQLが分かればできるわけで、この記事ではことさらHiveであることを強調する必要はなさそうです。

まずは必要なカラムだけを取ってきてサブクエリにできるSELECT文を書いてみました。

SELECT
	HOST,
	CASE REGEXP_EXTRACT(COL1,"[0-9]{2}\/[A-Za-z]{3}\/[0-9]{4}:[0-9]{2}:[0-9]{2}:[0-9]{2}",0)
    		WHEN '' THEN REGEXP_EXTRACT(COL4,"[0-9]{2}\/[A-Za-z]{3}\/[0-9]{4}:[0-9]{2}:[0-9]{2}:[0-9]{2}",0)
    		ELSE REGEXP_EXTRACT(COL1,"[0-9]{2}\/[A-Za-z]{3}\/[0-9]{4}:[0-9]{2}:[0-9]{2}:[0-9]{2}",0)
	END AS DT
FROM
	TABLE

ログはファイル構成が統一されておらず、アクセス時間が1カラム目の場合と4カラム目の場合が混在しているため、1カラムにそろえる必要があります。
日時のパターンにあてはまるかどうか見てみて、あてはまるほうをdtという名前のカラムにしています。

CASE文使えるんですね。けっこうなんでもできそうです。
タイムスタンプに変換してNULLでないほうを使えば...と思ったのですが、こっちのほうが短く書けた。

これをhostとdtでグループにし、行数をcountすれば、サーバごとの時間あたりのアクセス数がわかります。

SELECT
    HOST,
    DT,
    CNT
FROM (
    SELECT
        HOST,
        DT,
        CNT,
        ROW_NUMBER() OVER (PARTITION BY HOST ORDER BY CNT DESC) AS ROWNUM
    FROM (
        SELECT
            HOST,
            DT,
            COUNT(DT) AS CNT
        FROM (
            SELECT
                HOST,
                CASE REGEXP_EXTRACT(COL1,"[0-9]{2}\/[A-Za-z]{3}\/[0-9]{4}:[0-9]{2}:[0-9]{2}:[0-9]{2}",0)
                    WHEN '' THEN REGEXP_EXTRACT(COL4,"[0-9]{2}\/[A-Za-z]{3}\/[0-9]{4}:[0-9]{2}:[0-9]{2}:[0-9]{2}",0)
                    ELSE REGEXP_EXTRACT(COL1,"[0-9]{2}\/[A-Za-z]{3}\/[0-9]{4}:[0-9]{2}:[0-9]{2}:[0-9]{2}",0)
                END AS DT
            FROM
                TABLE
        ) T
        GROUP BY
            HOST,
            DT
        ORDER BY
            HOST,
            CNT DESC
        ) T2
    ) T3
WHERE
    ROWNUM = 1
;

長い・・・

グループごとにトップNを表示する方法にはいろいろあるようで、ググるとみんな苦労しているようですが、
わたしはROW_NUMBERを使うことで落ち着きました。

Hiveのバージョンは1.2.1です。

HiveQLの関数について参考になる：
LanguageManual UDF - Apache Hive - Apache Software Foundation