噂の超高速分析データベースを試してみよう～ログ解析，機械学習なんでもござれ～

第4回　あのシステムもビッグデータ分析や機械学習に活用中！ 超高速データベース「Vertica」とは？［後編②］

初出：WEB+DB PRESS Vol.97（2017年2月24日発売）

Vertica, データベース, Facebook, 機械学習, SQL, ログ分析, Hadoop

この記事を読むのに必要な時間：およそ 5.5 分

前回はHadoopやSparkなどとの連携について見てきました。今回は，得られたデータに対し，実際の分析に入っていくことにしましょう。

ログ分析用のSQL拡張関数

ログ分析のためのロジックは，SQL関数を組み合わせて作成したり，ユーザー定義関数（UDF）を自分で作成する場合が多いのではないでしょうか。Verticaには，すぐに活用できるログ分析用のSQL拡張関数が準備されています（ANSI SQL-99の標準SQLに基づく⁠）⁠。これによって，複雑なSQLを使わず，ログ分析のための集計処理を簡単かつ効率的に実装できます。代表的な関数を表1にまとめます。

表1　おもなSQL拡張関数

関数説明
Time Series Analytics時系列の補間
Event Pattern Matchingイベントのパターンマッチング
Geospatial Analytics地理空間分析

「Time Series Analytics」は，細かい時間軸で動きを把握しなければならない入札取引のようなケースで役立ちます。たとえば，複数の株式の入札取引を格納しているテーブルがあり，2秒間隔で入札イベントを参照する必要があるとします。ところが，入札イベントは一定の時間間隔では発生しないので，A株の入札イベントが3時と3時5秒に，B株の入札イベントが3時と3時6秒に発生した場合，通常のSQLを使うと，銘柄別に入札価格の動きを比較することができません（図1⁠）⁠。しかしTime Series関数を使うことで，「⁠株式」列でグループ化し，「⁠日付」列で並び替えられたウィンドウの時間データを読んで，2秒間隔になるように結果セットを整形するといったことが簡単にできます（図2⁠）⁠。

図1　Time Series関数のサンプルSQL（関数なし）

SELECT * FROM TickStore ORDER BY 日付;

        日付         | 株式 | 入札価格
---------------------+------+----------
 2017-02-24 03:00:00 | A    |     10.0
 2017-02-24 03:00:00 | B    |      7.0
 2017-02-24 03:00:05 | A    |     10.5
 2017-02-24 03:00:06 | B    |      6.7

図2　Time Series関数のサンプルSQL（関数あり）

SELECT
    slice_time,
    株式,
    TS_LAST_VALUE(入札価格, 'LINEAR') 入札価格
FROM
    TickStore TIMESERIES slice_time AS '2 seconds' OVER(PARTITION BY 株式 ORDER BY 日付)
;
     slice_time      | 株式 | 入札価格
---------------------+------+----------
 2017-02-24 03:00:00 | B    |      6.9
 2017-02-24 03:00:02 | B    |      6.8
 2017-02-24 03:00:04 | B    |      6.7
 2017-02-24 03:00:06 | B    |
 2017-02-24 03:00:00 | A    |     10.2
 2017-02-24 03:00:02 | A    |     10.4
 2017-02-24 03:00:04 | A    |

「Event Pattern Matching」では，クリックストリーム（Webサイトの訪問者がページを渡り歩いた軌跡）パターンを抽出するような場合に役立ちます。どのサイトのどのような情報を経て，どのバナーをクリックして購入に至ったか，といった行動履歴を1つのパターンとして，これに合致した計測データだけを即座に抽出できます。たとえば，図3のようなテーブルがあるとします。このテーブルには，2人の訪問者（uidが1と2）がWebサイトにアクセスした履歴が格納されており，どのURLからたどって来たのかの情報があります。このテーブルにEvent Pattern Matching関数を使うことで，ユーザーごとの行動履歴を表示できます（図4⁠）⁠。

図3　Event Pattern Matching関数のサンプルSQL（関数なし）

select * from clickstream_log order by ts;

 uid | sid |    ts    |        refURL         |       pageURL         | action
-----+-----+----------+-----------------------+-----------------------+--------
   1 | 100 | 12:00:00 | a.example.com         | b.example.com/home    | V
   1 | 100 | 12:01:00 | b.example.com/home    | b.example.com/floby   | V
   1 | 100 | 12:02:00 | b.example.com/floby   | b.example.com/shamwow | V
   1 | 100 | 12:03:00 | b.example.com/shamwow | b.example.com/buy     | P
   2 | 100 | 12:10:00 | a.example.com         | b.example.com/home    | V
   2 | 100 | 12:11:00 | b.example.com/home    | b.example.com/forks   | V
   2 | 100 | 12:13:00 | b.example.com/forks   | b.example.com/buy     | P

図4　Event Pattern Matching関数のサンプルSQL（関数あり）

SELECT uid, sid, ts, refurl, pageurl, action, event_name(),  pattern_id(),  match_id()
FROM clickstream_log
MATCH
  (PARTITION BY uid, sid ORDER BY ts
   DEFINE
     Entry    AS RefURL  NOT ILIKE '%b.example.com%' AND PageURL ILIKE '%b.example.com%',
     Onsite   AS PageURL ILIKE     '%b.example.com%' AND Action='V',
     Purchase AS PageURL ILIKE     '%b.example.com%' AND Action = 'P'
   PATTERN
     P AS (Entry Onsite* Purchase)
   ROWS MATCH FIRST EVENT);

 uid | sid |    ts    |        refurl         |       pageurl         | action | event_name | pattern_id | match_id
-----+-----+----------+-----------------------+-----------------------+--------+------------+------------+----------
┌―――――――――――――――――――――――――――――――――――――――――――――――――――――――――┐
│ 1 | 100 | 12:00:00 | a.example.com         | b.example.com/home    | V      | Entry      |          1 |        1 │
│ 1 | 100 | 12:01:00 | b.example.com/home    | b.example.com/floby   | V      | Onsite     |          1 |        2 │
│ 1 | 100 | 12:02:00 | b.example.com/floby   | b.example.com/shamwow | V      | Onsite     |          1 |        3 │
│ 1 | 100 | 12:03:00 | b.example.com/shamwow | b.example.com/buy     | P      | Purchase   |          1 |        4 │
└―――――――――――――――――――――――――――――――――――――――――――――――――――――――――┘
┌―――――――――――――――――――――――――――――――――――――――――――――――――――――――――┐
│ 2 | 100 | 12:10:00 | a.example.com         | b.example.com/home    | V      | Entry      |          1 |        1 │
│ 2 | 100 | 12:11:00 | b.example.com/home    | b.example.com/forks   | V      | Onsite     |          1 |        2 │
│ 2 | 100 | 12:13:00 | b.example.com/forks   | b.example.com/buy     | P      | Purchase   |          1 |        3 │
└―――――――――――――――――――――――――――――――――――――――――――――――――――――――――┘

テーブル内のデータから，Entry，Onsite，Purchaseの条件に一致するものを分類し，event_nameにタグ付けをする。これによって，uid1とuid2のユーザーがサイトを訪問してから購入するまでの履歴を追うことができるようになる。

「Geospatial Analytics」では，地域単位のアクティビティの集計を効率的に行うことができます。Verticaでは，地理情報を点，線，多角形のオブジェクトで扱います。これらのオブジェクト間の距離や，オブジェクトどうしが交差する点，面積などを計算して地理情報を分析します（図5⁠）⁠。空間情報を持つファイルであるシェープファイル，WKT/WKB，GeoJSONフォーマットをVertica内に取り込むことができ，SQLや地理空間情報関数を使って処理できます。たとえば，タクシードライバーの座標と乗客の座標から最短距離を算出することで瞬時にマッチングを行うといった使い方ができます。シェープファイルをVerticaに取り込む例を図6に，BostonとNew Yorkの距離をSQLで算出させる例を図7に示します。Bostonの経度（71.0598 W）と緯度（42.3601 N⁠）⁠，New Yorkの経度（74.0059 W）と緯度（40.7128 N）をSQLに与えるだけで簡単に距離を算出できます。

図5　Geospatial Analyticsのオブジェクト

図5　Geospatial Analyticsのオブジェクト

図6　シェープファイルのインポート

COPY table_name(columns) WITH SOURCE STV_ShpSource(file = 'filename') PARSER STV_ShpParser() ;

図7　New YorkとBostonの距離を算出

SELECT ST_Distance(ST_GeographyFromText('Point(-71.0598 42.3601)'), ST_GeographyFromText('Point(-74.0059 40.7128)')
 USING PARAMETERS spheroid = true);

ST_Distanse
------------------
306423.562065704

以上のように，Verticaには用途に合わせたSQL拡張関数が用意されているため，簡単な実装だけでVerticaの強力な高速分析処理能力をフル活用できるのです。

Vertica, データベース, Facebook, 機械学習, SQL, ログ分析, Hadoop

著者プロフィール

倉岡洋義（くらおかひろよし）

株式会社アシスト データベース技術本部 技術統括部 技術1部4課

学生時代に研究室のSunワークステーションの管理者になったのをきっかけにSIerに就職。

Linux系インフラエンジニアの道を歩みながら，Oracle RACのクラスタリング技術に衝撃を受けてアシストに転職。

Oracle，InfiniDBを経て現在はVerticaデータベースを担当。

趣味はピアノと筋トレ。