この記事について

株式会社うるるでは、2014年からTreasure DataとAWS S3に運営サービスのアクセスログを流し込んでいます。今回はアクセスログをS3からBigQueryに流し込み、サービス退会者の動向を探ってみました。

BigQueryを採用した理由

BigQueryとはGoogleが提供するビッグデータのストレージサービスです。RDBMSのような体裁を持っているため、クエリを発行してログを検索することが可能です。

Googleの虎の子「BigQuery」をFluentdユーザーが使わない理由がなくなった理由 #gcpja - Qiita

アクセスログを外部サービスに流し込むとき、既にFluentdを利用するのがディファクトスタンダード。Fluentdのbigqueryプラグインを使えば、オンタイムでBigQueryにアクセスログを流し込む事ができます。

とはいえBigQueryは有料なので、「Hadoopを利用してビッグデータ解析基盤を作ったらどう？」という検討もあったりしました。この判断には、以下のはてぶ記事が参考になりました。

外部サービスを使うと決めた段階で、Amazon Redshiftも検討に上がりました。ここでの判断基準は性能で、RedshiftとBigQueryでは速さにかなりの開きがあります。~~ザックリ調べたところですが、1TBに対する検索速度は大体以下のような感じになるらしいです。~~

Hadoop	Redshift	BigQuery
~~1000s~~	~~100s~~	1s

~~BigQueryは1TBに対する検索速度が1秒未満と、クエリの発行速度の速さが強みです。しかもインデックス無しです。~~

(2015.03.24修正)
Redshiftもチューニング次第でBigQuery以上のパフォーマンスが出るそうです。また、BigQueryは高速ですが、1秒未満のパフォーマンスは出ないとのことです。詳しくはコメント欄をご参照ください。(_ _)

この驚異的な速度は5000台以上の計算機に並列処理させているからだそうです。ただしこのパフォーマンスはデータ量が大きい場合のみで、数十GB程度が対象だと逆に遅くなったりする（十数秒）ようです。

BigQueryはデータのストレージに対する課金もRedshiftより安いとのこと。1GBあたり約1.2円/月。(2015.03.11現在、以下同)対して、デメリットはクエリの発行回数毎に課金がされることで、$5 / 1TBです。データの保存量がTB単位で、クエリを発行する&頻繁に使う可能性があるなら、Redshiftの方に分があるかもしれません。

今回のケースでは対象データ量がせいぜい数十GB ~ 数百GB（＝数円〜数十円）であることと、業務上、頻繁にクエリを発行するわけでもないので、「性能もあるしBigQueryにするのが良いだろう」ってことでBigQueryにしてみました。

S3からBigQueryへのアクセスログの移行

ここからは「S3→BigQuery」の流し込みについて、です。
前提は以下。

・ファイルは( json | csv )形式。
・ファイルは圧縮されていても良い。（Googleが上手くやってくれる）
・Google Cloud StrageにBucketを作っておく。

S3に溜まっていたログがrawだったので、ポジションファイルを初期化して改めてログを入れ直しました。jsonファイルへの変換はfluentdのS3プラグインがやるので楽チンなのですが、timeが取得できなかったり、json形式に変換するときにエラーが出たりするなどのエラーが頻発した箇所でもあります。そこでfluentdを1系から2系にバージョンアップしたり、rubyのto_jsonメソッドを書き換えたりして対応しました。

以上の前提を満たせばS3からBigQueryへの移行は準備はできたかと思います。
手順は以下。

１、gsutilを準備する。
２、gsutilでS3からGoogle Cloud Strageにログを移す。
３、Googleのbq loadでGoogle Cloud StrageからBigQueryにログをinsert。
４、待つ。

１、gsutilを準備する。

gsutil(英語)

gsutilインストール(英語)

gsutilはクラウドストレージのバケット上でcpやlsができるコマンドツールです。
例えば以下のような感じで使うことができます。

//google cloud strageのディレクトリをls
gsutil ls gs://uspto-pair/applications/0800401

//ローカルのtar.zipをgoogle cloud strageにcp
gsutil cp *.tar.zip gs://my_bucket/2014/01/

//s3からgoogle cloud strageにcp
gsutil cp -r s3://s3-bucket/* gs://my_bucket/

Google Cloud StrageやS3の情報を取得するには設定が必要です。

Google Cloud Strageへのデータ移行

gsutilのインストールが終わったら

gsutil config

と打ち、出てくるURLをコピペし、ブラウザからアクセスして流れに従って認証処理をします。
これで~/.botoファイルが作られます。

S3

~/.botoファイルで以下の二行のコメントアウトを外し、アクセスキーとシークレットキーを入力します。

aws_access_key_id = [your aws access key ID]
aws_secret_access_key = [your aws secret access key]

２、GoogleのgsutilコマンドでS3からGoogle Cloud Strageにログを移す。

gsutil cp -r s3://s3-bucket/* gs://my_bucket/

で可能ですが、これだと同期処理になってしまい遅いので、-mオプションをつけて非同期でcpします。

gsutil -m cp -r s3://s3-bucket/* gs://my_bucket/

数十GB程度であればものの数時間で終わります。

３、Googleのbq loadでGoogle Cloud StrageからBigQueryにログをinsert。

コマンドの使い方は以下の通り。

bq load —schema=[Schemaファイル.json] —source_format=[CSV|NEWLINE_DELIMITED_JSON] —max_bad_records=[許容するエラーレコード数] [projectId:dataset.table] gs://[bucket]/[ディレクトリ]/*

bq loadを使った場合、一度でinsertできるファイル量はmax 10000ですが、アクセスログはファイル量が多くなり超過する可能性があります。対策は以下

フォルダワイルドカードを使わずディレクトリ単位で指定してloadする。
シェルスクリプトで自動化する。

といった所でしょうか。アクセスログの取り方によってはディレクトリ内のファイル数がそれでも10000を超過してしまう可能性がありますが、スクリプトで条件分岐すれば問題ありません。

自動化ですが、私はアクセスログの取得時期やS3のディレクトリ構成に合わせ、PHPで以下のように組みました。

<?php
for($year = 2014; $year <= 2015; $year ++){
  if($year == 2014){
    $sm = 10;
  } else {
    $sm = 1;
  }
  if($year == 2015){
    $lm = 3;
  } else {
    $lm = 12;
  }
  for($month = $sm;$month <= $lm; $month++ ){
    if($year == 2014 && $month == $sm){
      $sd = 26;
    } else {
      $sd = 1;
    }
    for($day = $sd; $day <= 31; $day++){
      $count = upload($year, $month, $day);
      load_to_bigquery($year, $month, $day, $count);
    }
  }
}

function load_to_bigquery($year, $month, $day, $count){
  for($i = 0;$i <= $count; $i ++){
    $c = "bq load —schema=[Schemaファイル.json] --source_format=[CSV|NEWLINE_DELIMITED_JSON] --max_bad_records=[許容するエラーレコード数] [projectId:dataset.table] gs://[bucketその２]/".$year."/".sprintf("%02d", $month)."/".sprintf("%02d", $day)."/".$i."/*";
    $output = shell_exec($c);
    echo $output;
  }
}

function upload($year, $month, $day){
  $c = "gsutil ls gs://[bucketその１]/".$year."/".sprintf("%02d", $month)."/".sprintf("%02d", $day)."/*";
  $output = shell_exec($c);
  if($output){
    $data = explode("\n",$output);
    $di = 0;
    $ccp_h = "gsutil -m cp ";
    $ccp_to = "gs://<bucketその２>/".$year."/".sprintf("%02d", $month)."/".sprintf("%02d", $day)."/";

    $count = (int)( count($data) / 10000 );
    $last_max = count($data) % 10000;
    echo $count . "/". $last_max. PHP_EOL;

    for($i = 0; $i <= $count; $i++){
      $ccp = "";
      if($i == $count){
        $last_count = $last_max;
      } else {
        $last_count = ( $i + 1 ) * 10000;
      }
      for($k = $i * 10000; $k < $last_count ; $k++){
        $ccp .= $data[$k] . " ";
      }
      $c = $ccp_h. $ccp. $ccp_to.$i."/";
     $output = shell_exec($c);
      echo $output;
    }
    return $count;
  }
}
?>

４、待つ。

あとはこれを実行して寝て待ちます。BigQueryへの挿入は非同期で時間がかかるようです。尚、BigQueryへのInsertは10万行あたり1.2円で、約3000万行がInsertされました。ここまでの作業で500円かかっていません。

アクセスログの活用法 - 退会ユーザの動向調査 -

せっかくBigQueryにアクセスログが入ったので、サービスの退会ユーザの動向を探ることにしました。まず退会ユーザのIDをサービスのDBからCSV形式で吐き出し、Google DriveのSpread Sheetに貼付けました。Spread Sheetに貼付けることでGoogle App Scriptから利用可能になったので、スクリプトエディタからBigQueryのAPiを叩いてサービスの検索機能の利用平均回数を取ってみました。

継続ユーザと退会ユーザでは検索機能の利用率に10倍の開きがあり、どうやら退会する有料ユーザは検索機能を上手く使えていない（使っていない）ようだということが分かりました。この結果が直接役立つことは無いかもしれませんが、今後の方針を決める際に何らかの指標になってくれそうです。

アクセス解析を始める前に知っておきたいS3→BigQueryのこと。