ログ解析にhstoreを利用してみよう

ログ解析にhstoreを利用してみよう

夏目 伸彦

 

前回では、生ログデータをテーブルに取り込みました。早速解析を始めたいところですが、ちょっと待ってください。 ログ解析をする上で最も気になる点の一つにユーザーの行動等の分析があると思います。今回のログデータの中で、ユーザーの要求、動きが表れているデータは検索条件を表すparamsです。しかしながらparamsはtext型で保持しているため、このままでは扱いやすい形とは言えません。ここで活躍するのがhstoreというデータ型です。

hstoreとは

PostgreSQLにはhstore型というキーと値の集合を単一の列に保持できる型があります。paramsの中身であるパラメータ文字列も同様にキーと値の組合せなので、これを使わない手はありません。また、手続き型言語のハッシュテーブル（言語によっては辞書型、マップ等とも呼ばれる）とよく似ているため、手続き型言語になじみのある方には取っつきやすいはずです。

hstoreのインストール

hstoreはコアの機能ではなく、contribとして本体と共に提供されています。今回は PostgreSQL 9.0のソースからビルドとインストールを行いました。RPM版ではパッケージから追加でインストールすることもできますし、Windows版であればPostgreSQLのインストール時にオプションでインストールすることができます。 実際にhstoreを利用するには、インストールの後、以下の方法でDBへの登録が必要です。

$ psql log_db -f ${PGHOME}/share/contrib/hstore.sql

${PGHOME} はPostgreSQLのインストール先です。 これでhstoreを利用できるようになりました。

=# select 'a => 1'::hstore;
  hstore  
----------
 "a"=>"1"
(1 row)

上記のように実行できれば、インストールが成功しています。

ところでhstoreのテキスト表現は

key1 => value1, key2 => value2
"key-1" => "val,ue"

となり、=> によってキーと値が連結されます。キーと値それぞれについて、空白文字、カンマ (,)、等号 (=)、大なり (>) を含む場合には二重引用句を用いる必要があります。

演算子 関数の紹介

hstore を扱う際の基本的な演算子、関数を以下に紹介します。

演算子
  • hstore -> text
    • hstore内のkeynameをキーとする値を返します。
  • hstore || hstore
  • hstoreを結合し、両方に含まれるキーと値を持つ新しいhstoreを返します。キーが重複する場合は、右辺の値が使われます。
関数
  • akeys(hstore)
    • hstoreの全てのキーをtext型の配列として返します。
  • avals(hstore)
    • hstoreの全ての値をtext型の配列として返します。
  • skeys(hstore)
    • hstoreの全てのキーを複数の行にして返します。
  • svals(hstore)
    • hstoreの全ての値を複数の行にして返します。
  • delete(hstore, keyname)
    • hstoreのkeynameをキーとする値を削除します。
  • each(hstore)
    • hstoreのキーと値を集合として返します。

9.0 からの新機能

PostgreSQL 9.0では、hstoreが大幅にパワーアップしました。その一例を以下に記します。

#= 演算子
  • record #= hstore
    • 集合に対して、hstoreのキーと同一名称のカラムをその値で書き換えることが出来ます。
CREATE TABLE test (id integer, name text, class text, sex text);
INSERT INTO test VALUES (1, 'bob', 'A', 'male'), 
  (2, 'tom', 'B', 'male');

SELECT (rec).* FROM (SELECT test #= 'sex=>female, class=>C'
   AS rec FROM test )s;

の結果は

 id | name | class |  sex
----+------+-------+-------
  1 |  tom |   C   | female
  2 |  bob |   C   | female

となります。

populate_record

hstoreを定義済みのレコードの型式に変換します。その際、hstoreのカラム名と一致するキーに対する値がそのカラムの値となります。 (上のtestを利用しています。)

 

SELECT * FROM populate_record(null::test,
  'id=>1, name=>matt, sex=>male'::hstore);
 id | name | class |  sex
----+------+-------+-------
  1 | matt |       |  male

classという値を持ったキーは存在しないため、classはNULLとなっています。 これらの機能を上手に使うことが出来れば、UPDATEを効率よく実行できます。

 

データを扱いやすい形にしよう

それでは、実際にlog_tableのparamsを扱いやすい形にしていきましょう。

ここでは2通りの方法を紹介ます。

1.テキスト表現形式に変換して、hstoreにキャスト

SELECT
  regexp_replace(params, E'([^\\t]+)\\t([^\\t]*)\\t?',
     E'"\\1"=>"\\2",', 'g')::hstore AS hparams
FROM
  log_table;

上記の方法は、正規表現でhstoreのテキスト表現へと置換し、それをhstoreにキャストしています。具体的には

 

sort 安い順 region ハワイ country ハワイ島

という文字列を

"sort"=>"安い順","region"=>"ハワイ","country"=>"ハワイ島",

という文字列へと置換しています。データの性質上、params内にカンマ等が入り得るため、二重引用符にてそれぞれをくくっています。上記のようにキーと値を=>にて結び、カンマでそれぞれの組を区切った文字列はhstoreにそのままキャストすることが可能です。

2.配列に変換してからhstoreへ

SELECT
  hstore(string_to_array(params, E'\t')) AS hparams
FROM
  log_table;

9.0からhstore(text[])という関数が追加され、キーと値を交互に要素とするtextの配列からhstoreを取得できます。速度も上記の方法より速くなっています。

SELECT hstore(array['a', '1', 'b', '2']);
       hstore       
--------------------
 "a"=>"1", "b"=>"2"
(1 row)

扱いやすくなった！

log_tableのparams列を上記の手法によってhstoreに変換しただけのテーブルをlog_table_hとしました。

CREATE TABLE log_table_h AS
SELECT
  sessionid, atime, seq, elapse, action,
  regexp_replace(params, E'([^\\t]+)\\t([^\\t]*)\\t?', 
    E'"\\1"=>"\\2",', 'g')::hstore AS hparams
FROM
  log_table;

hstoreにしたことによって、例えば「行き先をアメリカにしたユーザ数を取得したい」というニーズにも簡単に答えられます。

SELECT
  count(DISTINCT sessionid)
FROM
  log_table_h
WHERE
  hparams->'country' = 'アメリカ';

 count 
-------
   633
(1 row)

また、汎用転置インデックスのGINを使うと、hstore型の列にインデックスを張ることができます。

CREATE INDEX gin_hparams ON log_table_h USING gin(hparams);

SELECT
  count(DISTINCT sessionid)
FROM
  log_table_h
WHERE
  hparams @> 'country => "アメリカ"';

                              QUERY PLAN
-----------------------------------------------------------------------
 Aggregate  (cost=584.31..584.32 rows=1 width=0)
   ->  Bitmap Heap Scan on log_table_h  
                 (cost=5.65..583.86 rows=177 width=0)
         Recheck Cond: (hparams @> '"country"=>"アメリカ"'::hstore)
         ->  Bitmap Index Scan on log_table_h_hparams_idx  
                     (cost=0.00..5.61 rows=177 width=0)
               Index Cond: (hparams @> '"country"=>"アメリカ"'::hstore)

 count 
-------
   633
(1 row)

ここで、"@>"は、「左辺のhstoreが右辺のhstoreを包含するか」という演算子です。 EXPLAINの結果から、インデックスが使われて検索性能が向上していることが確認できます。

まとめ

Webのログには「キー＝値」の組み合わせがつきものです。これをうまく扱うためのデータ型である、hstoreについて紹介しました。また実際のログデータでどのように適用できるかをご紹介しました。今回紹介した例に限らず、様々な局面で力を発揮するので、記憶の片隅に入れておくとよいでしょう。

(2010年8月9日 公開)