こんにちは。 使うSQLが200行を超えるのが当たり前になってきたデータチームの後藤です。 本記事では、VASILYデータチームで利用しているBigQueryによるデータの前処理のTipsを紹介します。
VASILYではサービスのマスタデータやログデータをGoogle BigQueryに集約して分析に活用しています。機械学習やデータ分析のための前処理を行う際、軽量なデータであれば抽出結果をPythonに渡して処理させることもできます。しかし、分析環境のメモリに載り切らないほど大きなデータを扱う場合、BigQuery内で前処理を済ませてしまうと時間と計算資源の節約になることが多いです。
今回はBigQueryからアクセスできるパブリックデータの1つ、hacker newsのデータを集計しながらTipsを紹介したいと思います。
以下に登場するSQLはStandard SQLと呼ばれる仕様にもとづいています。 Standard SQL自体については、弊社の過去の記事が参考になります。
欠落した日付を埋める
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が入っていることがわかります。
誕生日から年齢を算出する
年齢は日毎に変化するデータなので、誕生日から算出します。日付を'YYYYMMDD'のフォーマットに変換して、を計算することで算出できます。
例えば、 1988年6月24日生まれの人は2017年12月4日時点で、となり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ボタンを押してスプレッドシートに保存します。
URLが含まれるセルをimage関数に渡します。image関数は画像のURLを渡すと、URL先の画像を表示する関数です。
Results
以下のように、URLの画像の内容が把握できました。
まとめ
本記事では、VASILYデータチームが活用しているBigQueryのTipsを紹介しました。データによって必要となる処理は様々だとは思いますが、ここに記載したSQLの一部でも参考になったなら幸いです。
より体系的に分析のためのSQLを学びたい方には、以下の書籍がおすすめです。高度なデータ分析のためのSQLの例がPostgreSQL、Hive、Redshift、BigQuery、SparkSQLの5つの仕様に対応して上手く書き分けられている良書です。
最後に
弊社では、ファッションに関するデータに強い関心がありデータ分析や機械学習の腕に覚えのある方を募集しています。
