<jupyter_start><jupyter_text>BigQuery ML<jupyter_code>service_account_file='../../service-account.json'
import os
os.environ['GOOGLE_APPLICATION_CREDENTIALS']=service_account_file
%load_ext google.cloud.bigquery
import json
with open(service_account_file, 'r') as f:
    sa = json.load(f)
project_id=sa['project_id']
os.environ['GOOGLE_CLOUD_PROJECT']=project_id
%load_ext google.cloud.bigquery<jupyter_output><empty_output><jupyter_text>Create the Model<jupyter_code>%%bigquery

#%sql -d standard

CREATE OR REPLACE 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'<jupyter_output><empty_output><jupyter_text>Evaluate the Model<jupyter_code>%%bigquery
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'))<jupyter_output><empty_output><jupyter_text>Make Predictions<jupyter_code>%%bigquery

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;
%%bigquery 
select * from ML.FEATURE_INFO(MODEL `bqml_tutorial.sample_model`)
%%bigquery 
select * from ML.TRAINING_INFO(MODEL `bqml_tutorial.sample_model`)
%%bigquery 
select * from ML.WEIGHTS(MODEL `bqml_tutorial.sample_model`)
%%bigquery 
select * from ML.CENTROIDS(MODEL `bqml_tutorial.sample_model`)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11044776119402985, 'fraction_numerical': 0.024307036247334754, 'mean_word_length': 7.2027972027972025, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '275131', 'license': 'Apache-2.0', 'n_tokens_mistral': 937, 'n_tokens_neox': 884, 'n_words': 156, 'path': 'demos/BigQuery ML Logistic Regression.ipynb', 'repo_name': 'paulleroyza/gcp-training', 'size': '2345'}