BigQueryによるデータ分析のための前処理Tips

こんにちは。
使うSQLが200行を超えるのが当たり前になってきたデータチームの後藤です。 本記事では、VASILYデータチームで利用しているBigQueryによるデータの前処理のTipsを紹介します。

f:id:vasilyjp:20171204053405j:plain

VASILYではサービスのマスタデータやログデータをGoogle BigQueryに集約して分析に活用しています。機械学習やデータ分析のための前処理を行う際、軽量なデータであれば抽出結果をPythonに渡して処理させることもできます。しかし、分析環境のメモリに載り切らないほど大きなデータを扱う場合、BigQuery内で前処理を済ませてしまうと時間と計算資源の節約になることが多いです。

今回はBigQueryからアクセスできるパブリックデータの1つ、hacker newsのデータを集計しながらTipsを紹介したいと思います。

以下に登場するSQLはStandard SQLと呼ばれる仕様にもとづいています。
Standard SQL自体については、弊社の過去の記事が参考になります。

tech.vasily.jp

欠落した日付を埋める

GROUP BY句で日毎のレコード数をカウントする際、データに含まれない日付は欠落してしまいます。

通常のGROUP BY句の場合

以下のSQLは、hacker newsに投稿された日毎の記事の数を集計します。

SQL

#standardSQL
SELECT
  DATE(time_ts) AS publish_date
  , COUNT(*) AS article_cnt
FROM
  `bigquery-public-data.hacker_news.stories`
WHERE
  time_ts IS NOT NULL
GROUP BY
  publish_date
ORDER BY
  publish_date
LIMIT 10

2006年10月16日や18日〜20日のデータが存在しないため、対応する日付が欠落しています。

Results

Row  publish_date    article_cnt  
1   2006-10-09  18   
2   2006-10-10  12   
3   2006-10-11  5    
4   2006-10-12  6    
5   2006-10-13  2    
6   2006-10-14  2    
7   2006-10-15  1    
8   2006-10-17  1    
9   2006-10-21  1    
10  2006-10-22  1    

このままでは扱いづらいので、GENERATE_DATE_ARRAY関数を用いて、日付の列を生成することで対処します。生成した日付列にGROUP BY句で集計した結果をLEFT JOINすると日付の欠損がないデータを作成することができます。

指定した日付列を生成する

GENERATE_DATE_ARRAY関数に、明示的に日付を渡して日付列を生成します。以下の例では、2006年10月9日〜2006年10月22日までの日付列を事前に生成し、そこに集計結果をLEFT JOINしています。

SQL

#standardSQL
WITH
  -- 日付列の生成
  date_series AS (
  SELECT
    publish_date
  FROM
    UNNEST(GENERATE_DATE_ARRAY( DATE('2006-10-09'), DATE('2006-10-22') )) AS publish_date)

SELECT
  a.publish_date AS publish_date
  -- nullを0に置換
  , IFNULL(article_num, 0) AS article_num
FROM 
    date_series AS a
LEFT JOIN (
  SELECT
    DATE(time_ts) AS publish_date
    , COUNT(1) AS article_num
  FROM
    `bigquery-public-data.hacker_news.stories`
  WHERE
    time_ts IS NOT NULL
  GROUP BY
    publish_date) AS b
ON
  a.publish_date = b.publish_date
LIMIT 14

上記のSQLを実行すると、以下のように出現しなかった日付に対して0が対応付けられたデータを得ることができます。

Results

Row  publish_date    article_num  
1   2006-10-09  18   
2   2006-10-10  12   
3   2006-10-11  5    
4   2006-10-12  6    
5   2006-10-13  2    
6   2006-10-14  2    
7   2006-10-15  1    
8   2006-10-16  0    
9   2006-10-17  1    
10  2006-10-18  0    
11  2006-10-19  0    
12  2006-10-20  0    
13  2006-10-21  1    
14  2006-10-22  1     

相対的な日付列を生成する

CURRENT_DATE()、DATE_ADD()を組み合わせることで「昨日から7日前まで」といった相対的な日付列を生成することもできます。以下の例では、CURRENT_DATE関数に'Asia/Tokyo'を渡して、東京の時刻で処理しています。

SQL

#standardSQL
WITH
  date_series AS (
  SELECT
    create_date
  FROM
    UNNEST(GENERATE_DATE_ARRAY( DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -7 DAY), 
      DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -1 day) )) AS create_date)

SELECT
  create_date
FROM
  date_series

Results

Row  create_date  
1   2017-11-22   
2   2017-11-23   
3   2017-11-24   
4   2017-11-25   
5   2017-11-26   
6   2017-11-27   
7   2017-11-28   

クロス集計の欠落を埋める

上記の例と同じ方法で、日付×属性といったクロス集計をした際の欠落も埋めることができます。 WITH句で日付と属性をCROSS JOINした結果を用意し、そこに集計結果をLEFT JOINすることで欠落のないデータを作成することができます。

フィールドの生成とCROSS JOINを利用する

以下の例では、記事の投稿が多いTop10のAuthorが2015年1月1〜2月1日の各日に投稿した記事の数を集計しています。

SQL

#standardSQL
WITH
  -- 記事の投稿数が多いTop10ユーザーの集計
  top10_users AS (
  SELECT
    author
    , COUNT(1) AS article_cnt
  FROM
    `bigquery-public-data.hacker_news.stories`
  WHERE
    author IS NOT NULL
  GROUP BY
    author
  ORDER BY
    article_cnt DESC
  LIMIT
    10),

-- 日付列の生成
date_series AS (
  SELECT
    publish_date
  FROM
    UNNEST(GENERATE_DATE_ARRAY( DATE('2015-01-01'), DATE('2015-02-01') )) AS publish_date),

-- 欠落の無いフィールド
author_cross_date AS  (
  SELECT
    author
    , publish_date
  FROM
    -- 暗黙的カンマ CROSS JOIN
    top10_users
    , date_series)

SELECT
  a.author
  , a.publish_date
  , IFNULL(b.article_cnt, 0) AS article_cnt
FROM 
  author_cross_date AS a
LEFT JOIN (
  SELECT
    author
    , DATE(time_ts) AS publish_date
    , COUNT(1) AS article_cnt
  FROM
    `bigquery-public-data.hacker_news.stories`
  GROUP BY
    author
    , publish_date) AS b
ON
  a.author = b.author
  AND a.publish_date = b.publish_date
ORDER BY author, publish_date

Results

Row  author  publish_date    article_cnt  
1   ColinWright 2015-01-01  0    
2   ColinWright 2015-01-02  1    
3   ColinWright 2015-01-03  3    
4   ColinWright 2015-01-04  2    
5   ColinWright 2015-01-05  0    
6   ColinWright 2015-01-06  2     

<中略>

315 tokenadult  2015-01-27  0    
316 tokenadult  2015-01-28  1    
317 tokenadult  2015-01-29  0    
318 tokenadult  2015-01-30  1    
319 tokenadult  2015-01-31  2    
320 tokenadult  2015-02-01  1    

以下は、得られた結果を2次元の表としてみたものです。各Authorの投稿がない日には0が入っていることがわかります。 f:id:vasilyjp:20171204053522p:plain

誕生日から年齢を算出する

年齢は日毎に変化するデータなので、誕生日から算出します。日付を'YYYYMMDD'のフォーマットに変換して、 (基準日-誕生日)/10000を計算することで算出できます。

例えば、 1988年6月24日生まれの人は2017年12月4日時点で、 (20171204 - 19880624) /  10000 = 29.058となり29歳であることがわかります。

この考え方を使って、hacker newsの各記事が投稿されてからの経過年数を算出してみます。

SQL

#standardSQL
SELECT
  id
  , DATE(time_ts) AS publish_date
  -- FORMAT_DATE関数を用いて、日付を8桁の整数に変換する
  , CAST((CAST(FORMAT_DATE('%Y%m%d',
          CURRENT_DATE()) AS INT64) - CAST(FORMAT_DATE('%Y%m%d',
          DATE(time_ts) ) AS INT64)) / 10000 AS INT64) AS age
FROM
  `bigquery-public-data.hacker_news.stories`
LIMIT
  10

Results

Row  id  publish_date    age  
1   7330177 2014-03-02  3    
2   3671730 2014-05-31  3    
3   6059920 2014-05-31  3    
4   6528376 2014-05-31  3    
5   4697562 2014-05-31  3    
6   2249839 2014-05-31  3    
7   1578400 2014-05-31  3    
8   3563175 2014-05-31  3    
9   6969930 2013-12-27  4    
10  6990072 2013-12-31  4    

曜日の情報を付与する

曜日の情報を付与する場合、dayofweekを利用します。1〜7の整数が振られ、それぞれ日曜日〜土曜日に対応します。 以下のクエリでは曜日ごとの記事の数を集計してみます。

SQL

#standardSQL
SELECT
  day_of_week
  , COUNT(*) AS article_cnt
FROM (
  SELECT
    id
    , DATE(time_ts) AS publish_date
    , EXTRACT(dayofweek
    FROM
      DATE(time_ts)) AS day_of_week
  FROM
    `bigquery-public-data.hacker_news.stories`
  WHERE
    time_ts IS NOT NULL)
GROUP BY
  day_of_week
ORDER BY
  day_of_week

以下の結果から、土日の投稿数が平日の投稿数の半数程度であることがわかります。

Results

Row  day_of_week article_cnt  
1   1   160002   
2   2   310330   
3   3   339530   
4   4   333913   
5   5   326648   
6   6   294343   
7   7   169322    

リテンションレートを計算する

基準日に登録したユーザーの継続率を追う

N日継続率(Retention Rate)とは、ある日にサービスを使い始めたユーザー全体のうち、そのN日後に再度サービスを利用したユーザーの割合のことを指します。毎日利用されることを目指しているサービスでは、この指標を高めることがサービス改善の指針になります。

以下のクエリは基準日に登録したユーザーのN日継続率を集計します。

SQL

#standardSQL
  WITH first_publish_authors AS (
  -- 基準日に初めて投稿したユーザーのみ抽出
  SELECT
    author
  FROM (
    SELECT
      author
      , MIN(DATE(time_ts)) AS first_publish_date
    FROM
      `bigquery-public-data.hacker_news.stories`
    WHERE
      author IS NOT NULL
      AND time_ts IS NOT NULL
    GROUP BY
      author
    ORDER BY
      first_publish_date)
  WHERE
    -- 基準日を指定
    first_publish_date = DATE('2015-01-01'))

SELECT
  publish_date
  -- 基準日に投稿したユーザーの各日のリテンションレート(%)を計算
  , COUNT(1) / (SELECT COUNT(1) FROM first_publish_authors ) * 100 AS retention_rate
FROM (
  SELECT
    -- 基準日に投稿したユーザーがその後に投稿したレコードを集計
    author
    , DATE(time_ts) AS publish_date
  FROM
    `bigquery-public-data.hacker_news.stories`
  WHERE
    author IN (SELECT author FROM first_publish_authors)
    AND time_ts IS NOT NULL
  GROUP BY
    author
    , publish_date)
GROUP BY
  publish_date
ORDER BY
  publish_date
LIMIT 5

2015年1月1日に登録されたhacker newsの投稿者の場合、約10%のユーザーがその翌日にも投稿したことがわかります。

Results

Row  publish_date    retention_rate   
1   2015-01-01  100.0    
2   2015-01-02  9.803921568627452    
3   2015-01-03  7.8431372549019605   
4   2015-01-04  3.9215686274509802   
5   2015-01-05  1.9607843137254901   
6   2015-01-06  3.9215686274509802   
7   2015-01-07  3.9215686274509802   
8   2015-01-08  5.88235294117647     
9   2015-01-09  3.9215686274509802   

<省略>

大きなデータを取得する

サイズの大きな抽出結果は一括で取得できないことがあります。そんな状況ではORDER BY句を使いデータの並びを一意に固定してから、LIMITとOFFSETを利用して少しずつデータを取得します。

LIMIT OFFSETを利用する

以下のクエリではid順にデータをソートした後、OFFSETで指定した最初の1000レコードを飛ばして、1001番目から1500番目までの500レコードを抽出しています。毎回ソートしてから取得するので効率は悪いですが、OFFSETを増やしていくことで最終的にすべてのデータを取得することができます。

SQL

SELECT
  id
  , title
FROM
  `bigquery-public-data.hacker_news.stories`
ORDER BY
  id
LIMIT
  500
OFFSET
  1000

LIMIT OFFSETの注意点

抽出するデータのサイズが巨大な場合、OFFSETが大きくなるに従ってメモリ使用量を圧迫します。その結果、上記のクエリでは一定のOFFSETを超えると、以下のようなエラーを吐いて落ちることがあります。

Query Failed
Error: Resources exceeded during query execution: The query could not be executed in the allotted memory. ORDER BY operator used too much memory..

このようなエラーを防ぐには、利用するメモリの量を減らす必要があります。取得するidを先に抽出し、そのあとtitleをLEFT JOINすることでサイズの大きなデータを取得できるようになります。 (このSQLは非常に効率が悪いと思っています。より良い表現があればご教授いただきたいです)

SQL

#standardSQL
SELECT
  a.id AS id
  , b.title AS title
FROM (
  SELECT
    id
  FROM
    `bigquery-public-data.hacker_news.stories`
  WHERE
    title IS NOT NULL
    AND author IS NOT NULL
  ORDER BY
    id
  LIMIT
    500
  OFFSET
    1000) AS a
LEFT JOIN (
  SELECT
    id
    , title
  FROM
    `bigquery-public-data.hacker_news.stories`) AS b
ON
  a.id = b.id

画像URLの内容を確認する

画像URLが入ったフィールドを抽出した際、どんな画像が入っているかを把握したい場合に手軽に確認できるテクニックです。

以下のクエリでは、hacker_newsのコメントデータからJPEG画像のURLを抽出します。

SQL

SELECT
  id
  , URL
FROM (
  SELECT
    id
    , REGEXP_EXTRACT_ALL(text, r'(?i:(?:(?:(?:ftp|https?):\/\/)(?:www\.)?|www\.)(?:[\da-z-_\.]+)(?:[a-z\.]{2,7})(?:[\/\w\.-_\?\&]*).jpg\/?)') AS URL
  FROM
    `bigquery-public-data.hacker_news.comments`
  WHERE
    -- '.jpg'が含まれているレコードだけを対象にする
    text LIKE '%.jpg%'
  ORDER BY
    id),
  UNNEST(URL) AS URL
GROUP BY
  id
  , URL
  LIMIT 100

クエリの抽出結果をSend to Gogle Sheetsボタンを押してスプレッドシートに保存します。 f:id:vasilyjp:20171204053739p:plain

URLが含まれるセルをimage関数に渡します。image関数は画像のURLを渡すと、URL先の画像を表示する関数です。

f:id:vasilyjp:20171204053758p:plain

Results

以下のように、URLの画像の内容が把握できました。

f:id:vasilyjp:20171204053816p:plain

まとめ

本記事では、VASILYデータチームが活用しているBigQueryのTipsを紹介しました。データによって必要となる処理は様々だとは思いますが、ここに記載したSQLの一部でも参考になったなら幸いです。

より体系的に分析のためのSQLを学びたい方には、以下の書籍がおすすめです。高度なデータ分析のためのSQLの例がPostgreSQL、Hive、Redshift、BigQuery、SparkSQLの5つの仕様に対応して上手く書き分けられている良書です。

最後に

弊社では、ファッションに関するデータに強い関心がありデータ分析や機械学習の腕に覚えのある方を募集しています。