BigQuery ML

Remember what you did in BigQuery ML and how it can help you with Machine Learning models using SQL.

When to Use BigQuery?

If a custom model on structured data is required, BigQuery ML can be used to build and train Machine Learning models using simple SQL.

To easily determine whether you're forecasting or classifying:

If it's a numeric datatype (e.g., units sold, profits), you're doing forecasting.
If it's a string value, you're typically doing classification.

A typical example of a classification model is a "linear regression" model used for forecasting.

Pros and Cons of BigQuery

Pro: Once the connection is set up, you can re-run the same query to get the latest data from the spreadsheet.

Con: Since external data is not stored natively in BigQuery, it does not benefit from BigQuery's caching mechanism, which can affect performance when rerunning the same queries.

Compare Training Between "Logistic Regression" and "Boosted Tree Classifier"

The comparison result is represented in a ROC (Receiver Operating Characteristic) curve. The closer the curve is to 1, the more precise the model.

This process is part of experimentation and hyperparameter tuning.

Other Performance Metrics: Apart from ROC-AUC, available metrics include accuracy, precision, and recall.

Boosted Tree Classifier


				CREATE OR REPLACE MODEL `ecommerce.classification_model_3`

				OPTIONS

				(model_type='BOOSTED_TREE_CLASSIFIER' 

				, l2_reg = 0.1 

				, num_parallel_tree = 8

				, max_tree_depth = 10,

				labels = ['will_buy_on_return_visit']) AS


				WITH all_visitor_stats AS (

Second Example: Logistic Regression

Create a dataset called advdata and build a logistic regression model called txtclass. The model is designed for classification, where the label is source.


				CREATE OR REPLACE MODEL advdata.txtclass
				OPTIONS (model_type='logistic_reg', input_label_cols=['source'])
				AS

Evaluate the ML model:


				SELECT * FROM ML.EVALUATE(MODEL advdata.txtclass)

Make predictions with the ML model:


				SELECT * FROM ML.PREDICT(MODEL advdata.txtclass

				,(SELECT 'government' AS word1, 'shutdown' AS word2, 'leaves' AS word3, 'workers' AS word4, 'reeling' AS word5

				UNION ALL SELECT 'unlikely', 'partnership', 'in', 'home', 'gives'

				UNION ALL SELECT 'fitbit', 's', 'fitness', 'tracker', 'is'

				UNION ALL SELECT 'downloading', 'the', 'android', 'studio', 'project'

Supported Models in BigQuery ML

BigQuery ML supports various models to perform different types of machine learning tasks:

Linear regression: For forecasting, e.g., predicting sales of an item on a given day. Labels must be real-valued.
Binary logistic regression: For classification, e.g., predicting customer purchase behavior. Labels have two possible values.
Multiclass logistic regression: For classifying into multiple categories, e.g., "low-value," "medium-value," or "high-value." Labels can have up to 50 unique values.
K-means clustering: For data segmentation, e.g., identifying customer segments. This unsupervised learning technique does not require labels.
Matrix Factorization: For creating product recommendation systems.
Time series: For forecasting time-based data, e.g., sales over a period. Handles anomalies, seasonality, and holidays.
Boosted Tree: For XGBoost-based classification and regression models.
Deep Neural Network (DNN): For TensorFlow-based classification and regression models.
AutoML Tables: For codeless model creation without feature engineering.
TensorFlow model importing: Allows importing and prediction using previously trained TensorFlow models.

Advantages of BigQuery ML

BigQuery ML offers several advantages over traditional ML approaches in cloud-based data warehouses:

Empowers data analysts to build and run models using SQL, without needing Python or Java.
Eliminates the need to export data, bringing ML directly to the data.
Speeds up model development and experimentation by avoiding the complexity of data export and reformatting.

Disadvantages of Data Export:

Increases complexity due to the need for multiple tools.
Reduces speed because moving large datasets for Python-based ML frameworks is slower than using BigQuery.
Requires multiple steps, restricting data experimentation.
Legal restrictions, like HIPAA, can prevent exporting data.

BigQuery ML Overview

When to Use BigQuery?

Where to Find BigQuery?

Pros and Cons of BigQuery

Caching in BigQuery

Compare Training Between "Logistic Regression" and "Boosted Tree Classifier"

Linear Regression

Boosted Tree Classifier

Logistic Regression

Second Example: Logistic Regression

Supported Models in BigQuery ML

Advantages of BigQuery ML