こんにちは、yagiです。

Google Cloud の　BigQuery ML を使って、SQLクエリベースで機械学習のモデル作成からトレーニング、モデルの評価、結果の予測までをやってみました。

SQLクエリベースでMLの実装の知識がなくても一連の流れ（モデル作成〜結果の予測）までができます。

BigQuery ML とは

BigQuery ML を使用すると、BigQuery で SQL クエリを使用して機械学習モデルを作成して実行できます。このチュートリアルでは、SQL のユーザーが簡単に機械学習を利用できるようにすることを目標としています。使い慣れたツールを使用してモデルを構築でき、データ移動の必要もないため、開発スピードを向上させることができます。

BigQuery ML で機械学習モデルを作成する

使ってみる

上記のドキュメントに従って、サンプルデータを利用して進めていきます。今回、モデルにはロジスティック回帰を利用します。

サンプルデータセットを利用して、Webサイトの訪問者のセッション内での電子商取引の合計数から、 Webサイトの訪問者の購入数（ユーザー別や国別など）を予測することができるモデルを作成します。

データセットを作成する

コンソールにログインし、 BigQuery API を有効にして、ML モデルを格納する BigQuery データセットを作成します。

リソースセクションでプロジェクトから「データセットを作成」を押下します。

データセットの作成ページで、データセットIDとマルチリージョンを米国を指定し、「データセットを作成」ボタンを押下します。

先ほどのデータセットが作成されたことを確認します。

作成したデータセットから10行だけデータを見て確認します。

以下のSQLをエディタへ入力して、「実行」ボタンを押下します。

select *
from  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
limit 10

なお、今回のデータセットはこちらからアクセス可能となっています。

モデルを作成してトレーニングする

以下のSQLを利用します。 このSQLでモデルを作成し、Select したデータでモデルをトレーニングできます。

#standardSQL
CREATE MODEL `bqml_tutorial.sample_model`
OPTIONS(model_type='logistic_reg') AS
SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(geoNetwork.country, "") AS country,
  IFNULL(totals.pageviews, 0) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'

• model_type='logistic_reg' ロジスティック回帰モデルを利用することを指定しています。

• totals.transactions セッション内の電子商取引の合計数で、こちらがNull値であれば0、それ以外は1となります。そちらを今回の教師ラベルとして指定します。

その他、デバイスがモバイルかどうか、IPアドレスに基づく（セッションが発生した）国およびページビューの合計数を取得してトレーニングします。

上記のSQLをエディタに貼り付け、「実行」ボタンを押下します。

クエリが完了すると、モデルの作成およびトレーニングが完了した状態となります。

そうすると、コンソール上から、モデルの詳細やトレーニングオプション、トレーニングタブからは損失や学習率、評価結果などが確認できるようになります。 スキーマタブからはスキーマに関する情報も一目で確認できるようになっています。

モデルを評価する

ML.EVALUATE 関数を利用して、モデルの評価を行います。

以下のSQLを実行します。

#standardSQL
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.sample_model`, (
SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(geoNetwork.country, "") AS country,
  IFNULL(totals.pageviews, 0) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))

ロジスティック回帰のため、以下の結果を得ました。

モデルを使用して結果を予測する

作成したモデルを利用して、国別および、ユーザーごとの購入数を予測します。

国別に購入回数を予測する

以下のSQLを利用して、国別購入回数の予測結果を取得します。

#standardSQL
SELECT
  country,
  SUM(predicted_label) as total_predicted_purchases
FROM
  ML.PREDICT(MODEL `bqml_tutorial.sample_model`, (
SELECT
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(totals.pageviews, 0) AS pageviews,
  IFNULL(geoNetwork.country, "") AS country
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
GROUP BY country
ORDER BY total_predicted_purchases DESC
LIMIT 10

GROUP BY と ORDER BY 句で、結果を国別に分類し、予測された購入回数の合計を降順で並べ替え、結果の10行だけ取得します。

結果は以下となります。

ユーザーごとの購入数を予測する

今度はユーザーごとの購入数の予測結果を取得します。

#standardSQL
SELECT
  fullVisitorId,
  SUM(predicted_label) as total_predicted_purchases
FROM
  ML.PREDICT(MODEL `bqml_tutorial.sample_model`, (
SELECT
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(totals.pageviews, 0) AS pageviews,
  IFNULL(geoNetwork.country, "") AS country,
  fullVisitorId
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
GROUP BY fullVisitorId
ORDER BY total_predicted_purchases DESC
LIMIT 10

購入数を降順に並べ替え、結果を10行取得しました。

感想

Google Cloud の　BigQuery ML を使って、SQLクエリベースで機械学習のモデル作成からトレーニング、モデルの評価、結果の予測までをやってみました。

SQLクエリベースで、MLの実装の知識や環境構築などをしなくても、モデル作成〜結果の予測までが実施できます。

また、GROUP BY 句や ORDER BY 句など使いつつ、降順の並べ替えや国別、ユーザー別などに分類した結果を直接取得できて便利でした。