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

前回は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に与えるだけで簡単に距離を算出できます。

図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の強力な高速分析処理能力をフル活用できるのです。

機械学習

昨今、大きな話題になっている機械学習を用いた分析処理は、データマイニングツールやR、Pythonなどを利用するのが一般的です。しかし、大量データを対象にした分析ではパフォーマンスが課題になることが多く、データを間引いたり、ソースデータをあらかじめ加工しなければならない場合もあります。

Verticaは、機械学習アルゴリズムもデータベースに内蔵しています。そのため、データベース内で（データソースを間引くことなく）そのまま高速処理することが可能です。Verticaで実装されている機械学習アルゴリズムを表2にまとめています。

表2　Verticaの機械学習アルゴリズム一覧

アルゴリズム	モデル作成	評価	予測分析
線形回帰分析	○	○	○
ロジスティック回帰分析	○	○	○
K平均法	○	○	○

学習データをもとにこれらのアルゴリズムを使うことで、数値や確率の予測、クラスタリングを行うことができます。Verticaで機械学習を行うことには、次のようなメリットがあります。

SQLで機械学習を行うことができる
RやPythonクライアントと比較して高速に処理できる（Verticaで並列分散処理）
SQL分析と機械学習分析のプラットフォームを分ける必要がない

それでは、自動車のデータを使った具体的な例を見てみましょう。車種別に「燃費（mpg⁠）⁠」やエンジンの「シリンダー数（cyl⁠）⁠」⁠「⁠馬力（hp⁠）⁠」⁠「⁠重量（wt⁠）⁠」などのデータを持つテーブルがあります（図8⁠）⁠。これらのデータをVerticaに読み込ませて学習させます（モデリング⁠）⁠。

図8　学習させるテーブルデータ

       carName       | mpg  | cyl | disp  | hp  | drat |  wt   | qsec  | vs | am | gear | carb |  tf
---------------------+------+-----+-------+-----+------+-------+-------+----+----+------+------+-------
 Fiat X1-9           | 27.3 |   4 |    79 |  66 | 4.08 | 1.935 |  18.9 |  1 |  1 |    4 |    1 | train
 Merc 230            | 22.8 |   4 | 140.8 |  95 | 3.92 |  3.15 |  22.9 |  1 |  0 |    4 |    2 | train
 Merc 280C           | 17.8 |   6 | 167.6 | 123 | 3.92 |  3.44 |  18.9 |  1 |  0 |    4 |    4 | train
 Merc 450SE          | 16.4 |   8 | 275.8 | 180 | 3.07 |  4.07 |  17.4 |  0 |  0 |    3 |    3 | train
 AMC Javelin         | 15.2 |   8 |   304 | 150 | 3.15 | 3.435 |  17.3 |  0 |  0 |    3 |    2 | test

図9では、線形回帰分析アルゴリズムを用いて、「⁠mtcarsテーブル」の中から「cyl列」「⁠hp列」「⁠wt列」の3つの列データを説明変数とし、「⁠mpg列」を目的変数とした線形回帰モデル「linModel」を作成しています。

図9　線形回帰モデルの作成

SELECT v_ml.linearReg('linModel', 'mtcars', 'mpg', 'cyl,hp,wt', '--exclude_columns= carName --description my_linearReg_model');

図10では、作成した線形回帰モデルを使って、「⁠燃費（mpg⁠）⁠」の値を予測させています。左の列から、車種（carName⁠）⁠、正しい燃費（observed⁠）⁠、機械学習によって予測させた値（predicted）が表示されています。

図10　機械学習による予測結果

       carName       | observed |    predicted
---------------------+----------+-----------------
 AMC Javelin         |     15.2 | 17.6357311618035
 Cadillac Fleetwood  |     10.4 | 10.8954543679367
 Camaro Z28          |     13.3 | 14.6442677270797
 Chrysler Imperial   |     14.7 | 10.1449185542106
 Datsun 710          |     22.8 | 25.9667989028515
 Dodge Challenger    |     15.5 | 17.3663828863913
 Duster 360          |     14.3 | 15.4998446019185
 Ferrari Dino        |     19.7 | 21.1799824379912
 Fiat 128            |     32.4 | 26.8325147580537
 Fiat X1-9           |     27.3 | 27.6722476166918

このように、Vertica内のテーブルデータの分布（説明変数）を学習させて、予測させたいデータ（目的変数）をVerticaに計算させることができます。

なお、説明変数となるデータを読み込ませたあと、データプレパレーションとしてデータの偏りを事前になくし、データを正規化することで、より高速かつ精度の高い予測値を得られます。そのために、Verticaではデータを正規化するSQL関数機能が提供されています。SQL関数1つで簡単にデータの正規化が行えます。

進化し続けるVertica

本稿で紹介してきたように、Verticaは単なるデータベースの枠を超え、「⁠ビッグデータ分析プラットフォーム」としてさまざまなツールとの連携機能や独自の分析機能を提供し続けています。また稼働環境についても、オンプレミスや仮想化環境だけでなく、Amazon Web ServiceやMicrosoft Azureといったクラウド環境にも対応しており、その活躍の場を広げています。

さっそく試してみよう！

前後編にわたって、Verticaの「大量データを高速に処理できるしくみ」と「ビッグデータ分析基盤としての強力な機能」を紹介してきました。今回、誌面の都合で紹介できませんでしたが、ほかの多くの列指向型データベースでは実装が追いついていない主キー設定といった基本機能の保証など、Verticaには数多くの優れた機能が搭載されています。

「百聞は一見に如かず」と言います。まずは既存データベースの性能課題を解消することを目的に、将来のビッグデータ活用も見据えてVerticaを試してみてはいかがでしょうか。Verticaには、3ノード構成、1TBまでの範囲であれば機能制限なしで利用でき、オンプレミス環境とクラウド環境のどちらにも対応した評価版「Vertica Community Edition」があります。この評価版のダウンロード／インストール、テストデータのロード、プロジェクションの最適化方法などを網羅したドキュメント「はじめてのVertica」が「Vertica技術情報サイト」からダウンロードできますので、詳細情報をご覧ください。