LoginSignup
25
16

More than 3 years have passed since last update.

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

Last updated at Posted at 2019-07-22

これなに?

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

参考文献

25
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
25
16