Edited at

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の前処理と、特徴量生成を行います


参考文献