Bigquery ML

BigQuery ML 소개

• BigQuery에서 SQL 쿼리를 사용하여 머신러닝 모델을 만들고 예측

 

BigQuery ML 지원 모델

• Linear regression

• Binary logistic regression

• Multiclass logistic regression

• K-means clustering

• XG boost

• DNN

• Custom TensorFlow model importing

MODEL_TYPE = { 'LINEAR_REG' | 'LOGISTIC_REG' | 'KMEANS' |
'BOOSTED_TREE_REGRESSOR' | 'BOOSTED_TREE_CLASSIFIER' | 'DNN_CLASSIFIER' | 'DNN_REGRESSOR'

 

Importing TensorFlow models

• CREATE MODEL 문을 사용하여 학습된 TF 모델을 가져옴

• MODEL_PATH에 가져올 TF 모델의 Cloud Storage URI를 지정 (saved_model.pb)

 

Making predictions with imported TensorFlow models

• ML.PREDICT로 가져온 모델과 input_data를 사용하여 예측

  • SELECT 예측결과 FROM 모델, 모델에 넣을 데이터

 

BigQuery ML의 장점

• SQL을 사용하여 BigQuery에서 모델을 학습시키고 예측할 수 있음

• 데이터를 데이터 웨어하우스로 이동해야 할 필요가 없어서 속도가 향상됨

• 예측 시간이 90% 이상 감소

• 실시간 예측을 편리하고 빠르게 처리 가능

• 비용

• CMLE 일괄 예측: $0,0791 (시간당, 분단위 청구, 최소10분)

• BigQuery ML: BigQuery 정액제 요금에 포함 (2019/12/31까지)

 

실습

모델만들기

#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'

모델평가

#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'))

+--------------------+---------------------+--------------------+--------------------+---------------------+----------+
|     precision      |       recall        |      accuracy      |      f1_score      |      log_loss       | roc_auc  |
+--------------------+---------------------+--------------------+--------------------+---------------------+----------+
| 0.4451901565995526 | 0.08879964301651048 | 0.9716829479411401 | 0.1480654761904762 | 0.07921781778780206 | 0.970706 |
+--------------------+---------------------+--------------------+--------------------+---------------------+----------+

모델예측

#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

https://cloud.google.com/bigquery-ml/docs/bigqueryml-intro?hl=ko

BigQuery ML 소개  |  Google Cloud