BigQuery + jupyterで機械学習を行う(その1: データセットの作成)

これなに?

Big Queryでデータセットのロード、前処理、特徴量生成、モデルの学習、評価を行う方法についての記事です
jupyter上で実行することで、よりシームレスにbqに接続し、クエリを実行すると共に、データを可視化し、モデルのブラッシュアップを行っていきます

まとめてると、割と長くなったので、この記事の中では、

• jupyter上でマジックコマンドを実行する方法
• bq上にあるdatasetの概要の確認方法

について説明します

特徴量生成、MLモデルの作成についてはまた別の記事にまとめます
なお、jupyterや機械学習自体の説明については基本せず、また、pythonでの処理は行わずすべてSQLで分析していきます

機械学習でBigQueryを使用するメリット

機械学習でBQを使う際にメリットだと個人的に感じるのは、

• 大量のログであっても容易にすばやくクレンジングや特徴量の作成が行える
• 一度作成したデータセットを簡単に保存しておける
• MLモデルを作る際にパラメータ調整やスケーリングなどの面倒な部分をよしなにやってくれる

ところです
さらにjupyter上でクエリを実行することで、

• シームレスにbqに接続し、クエリを実行できる
• bqからの返り値をpandas.DataFrameとして受け取ることで、sqlで表現できないような複雑な処理や可視化が可能
• 実行結果をまとめて、そのままレポートにできる(この記事もjupyterで書きました)

などのメリットがあります

分析対象

例としてなにか良いものがないか、bigquery-public-data から探してみます

!bq ls --project_id bigquery-public-data

google_analytics_sampleという、GAのトランザクションのdatasetがあったので、これで、ユーザーが特定の商品をクリックする確率(isClick)を予測してみようと思います

準備

datasetを生成

!bq mk --location=US google_analytics

datasetを作成します。データを読み込んだりする都合上今回使用する bigquery-public-data.google_analytics_sample と同じregionにする必要があるため、locationを USに指定します(デフォルトでもUSになりますが、一応指定します)

magic commandの認証

せっかくjupyterを使うので、magic commandを使用してbqに接続できるようにします。

!pip install google-cloud-bigquery

tokenのファイルを読み込ませます
詳細についてはこちらのドキュメントをご覧ください

from google.cloud.bigquery import magics
from google.oauth2 import service_account
credentials = (service_account.Credentials.from_service_account_file('[/path/to/key.json]'))
magics.context.credentials = credentials

これで準備は完了です

%%bigqueryの使い方について

まず、magic commandを使えるようにするために読み込みます

%load_ext google.cloud.bigquery

magic comannd %%bigquery を使用すると、cell内でbqのクエリを実行でき、更に返り値をpandas.DataFrameオブジェクトで受け取れるので、jupyter上で可視化などを行う際に非常に便利です。
使い方としては以下のような形です

%%bigquery [返り値を格納する変数名]　--project [project_id] --param [クエリ内で使いたい変数を格納したdictまたはjson]  

[実行するクエリ]

具体的な実行はこのようになります

PARAMS = {'LIMIT' : 5}
PROJECT ='[自身の作成したプロジェクトID]'

%%bigquery data --project $PROJECT  --params $PARAMS
SELECT * FROM  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801` LIMIT @LIMIT

data

	visitorId	visitNumber	visitId	visitStartTime	date	totals	trafficSource	device	geoNetwork	customDimensions	hits	fullVisitorId	userId	channelGrouping	socialEngagementType
0	None	1	1470117657	1470117657	20160801	{'visits': 1, 'hits': 3, 'pageviews': 3, 'time...	{'referralPath': '/yt/about/', 'campaign': '(n...	{'browser': 'Internet Explorer', 'browserVersi...	{'continent': 'Americas', 'subContinent': 'Nor...	[{'index': 4, 'value': 'North America'}]	[{'hitNumber': 1, 'time': 0, 'hour': 23, 'minu...	7194065619159478122	None	Social	Not Socially Engaged
1	None	151	1470083489	1470083489	20160801	{'visits': 1, 'hits': 3, 'pageviews': 3, 'time...	{'referralPath': '/yt/about/', 'campaign': '(n...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Americas', 'subContinent': 'Nor...	[{'index': 4, 'value': 'North America'}]	[{'hitNumber': 1, 'time': 0, 'hour': 13, 'minu...	8159312408158297118	None	Social	Not Socially Engaged

基本はこれだけで良いと思いますが、より詳細なオプションは公式ドキュメントを参考にしてください

tableの概要を確認

columnの名前と型を確認します

%%bigquery --project $PROJECT 
SELECT table_name, column_name, data_type  FROM `bigquery-public-data.google_analytics_sample.INFORMATION_SCHEMA.COLUMNS` 
WHERE table_name ='ga_sessions_20160801'

	table_name	column_name	data_type
0	ga_sessions_20160801	visitorId	INT64
1	ga_sessions_20160801	visitNumber	INT64
2	ga_sessions_20160801	visitId	INT64
3	ga_sessions_20160801	visitStartTime	INT64
4	ga_sessions_20160801	date	STRING
5	ga_sessions_20160801	totals	STRUCT<visits INT64, hits INT64, pageviews INT...
6	ga_sessions_20160801	trafficSource	STRUCT<referralPath STRING, campaign STRING, s...
7	ga_sessions_20160801	device	STRUCT<browser STRING, browserVersion STRING, ...
8	ga_sessions_20160801	geoNetwork	STRUCT<continent STRING, subContinent STRING, ...
9	ga_sessions_20160801	customDimensions	ARRAY<STRUCT<index INT64, value STRING>>
10	ga_sessions_20160801	hits	ARRAY<STRUCT<hitNumber INT64, time INT64, hour...
11	ga_sessions_20160801	fullVisitorId	STRING
12	ga_sessions_20160801	userId	STRING
13	ga_sessions_20160801	channelGrouping	STRING
14	ga_sessions_20160801	socialEngagementType	STRING

STRUCTの中身がよくわからないので、

!bq show  --project_id bigquery-public-data google_analytics_sample.ga_sessions_20160801

で確認してみます
目的変数であるisClick はかなり入れ込んでるので、少し面倒そうですね

   Last modified                             Schema                             Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Labels
 ----------------- ----------------------------------------------------------- ------------ ------------- ------------ ------------------- ------------------ --------
  23 Oct 22:12:27   |- visitorId: integer                                       1711         19920125
                    |- visitNumber: integer
                    |- visitId: integer
                    |- visitStartTime: integer
                    |- date: string
                    +- totals: record
 ...

予測のための説明変数は

hits.hour
hits.product.productPrice
device.isMObile
device.browser
geoNetwork.country

としましょう
細かいcolumnの説明はこちらにあるので、気になる人は御覧ください

学習に使用する変数のみを取り出す

下記のクエリで必要なもののみを取り出します

%%bigquery --project $PROJECT 
-- 使用する変数が入っている要素のみを抽出
WITH data AS(
SELECT hits, device, geoNetwork
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801` 
),
data2 AS(
SELECT hit.hour, hit.product, device.browser, device.isMobile, geoNetwork.country  FROM data, UNNEST(hits) AS hit
),
data3 AS(
SELECT hour, browser, isMobile, country, product.productPrice, product.isClick FROM data2, UNNEST(product) AS product 
)

SELECT  * FROM data3 LIMIT 5

	hour	browser	isMobile	country	productPrice	isClick
0	23	Internet Explorer	False	United States	25000000	None
1	23	Internet Explorer	False	United States	50000000	None
2	23	Internet Explorer	False	United States	100000000	None
3	23	Internet Explorer	False	United States	250000000	None
4	13	Chrome	False	United States	16990000	None

いちいち上のクエリを叩くのも面倒なので、最初に作成したdatasetに新たなtableを作成し、そこに取得した結果を保存します

%%bigquery --project $PROJECT 
CREATE  OR REPLACE TABLE google_analytics.ga_dataset AS

-- 以下は上と同様のクエリ
WITH data AS(
SELECT hits, device, geoNetwork
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801` 
),
data2 AS(
SELECT hit.hour, hit.product, device.browser, device.isMobile, geoNetwork.country  FROM data, UNNEST(hits) AS hit
),
data3 AS(
SELECT hour, browser, isMobile, country, product.productPrice, product.isClick FROM data2, UNNEST(product) AS product 
)

SELECT  * FROM data3 

%%bigquery --project $PROJECT 
-- 確認
SELECT * FROM `google_analytics.ga_dataset` LIMIT 5

	hour	browser	isMobile	country	productPrice	isClick
0	13	Chrome	False	United States	20990000	None
1	13	Chrome	False	United States	990000	None
2	7	Firefox	False	Canada	16990000	None
3	7	Firefox	False	Canada	16990000	None
4	13	Chrome	False	United States	12990000	None

一旦ここまでとして、次は学習に使用するdatasetの前処理と、特徴量生成を行います

参考文献

• スケーラブルデータサイエンス データエンジニアのための実践Google Cloud Platform

  • bqのTech LeadであるValliappa Lakshmananさんの本。日本語版が最近出た

• Google BigQuery: The Definitive Guide

  • 同じくValliappa Lakshmananさんの本。ただし、発売は来年

• ビッグデータ分析・活用のためのSQLレシピ

  • ここまでできるのかというレベルにSQLの黒魔術がいろいろのってます