複数サーバがそれぞれ出すアクセスログを集計し、サーバごとの時間当たり最大アクセス数とその時間を出そうとしています。
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