Associate Data Practitioner

Execute SQL to Create, Train, and Evaluate Models Using BigQuery ML

Create Machine Learning Models Using SQL

BigQuery ML (BQML) is the GCP service that lets data practitioners build machine learning models directly inside BigQuery using standard SQL queries. This avoids the need to move data to external tools and simplifies the entire machine learning workflow.

The core of the process is the CREATE MODEL SQL statement. This statement tells BigQuery what type of model to build, where to find the training data, and what options to use during training. Understanding the different model types helps you pick the right one for your problem.

Supervised learning models work when your data includes a known answer (called a label) that the model learns to predict:

• Linear regression predicts continuous numbers like prices or temperatures. You set the model type to 'LINEAR_REG'.
• Logistic regression predicts categories like spam or not spam, yes or no. You set the model type to 'LOGISTIC_REG'.
• Both require the input_label_cols option to tell the model which column contains the answer it should learn to predict.

Unsupervised learning models find patterns without predefined answers:

• K-means clustering groups data into clusters based on similarity. You set the model type to 'KMEANS' and use the num_clusters option to specify how many groups to create.

After creating a model, you use ML.EVALUATE to check its performance and ML.PREDICT to make predictions on new data.

Evaluate Model Performance Using SQL Functions

Once a model is trained, you must check how well it works before using it for real predictions. The ML.EVALUATE function generates performance metrics that reveal whether the model is accurate enough for your needs.

The metrics you look at depend on the type of problem the model solves. For classification models that predict categories, you examine accuracy, precision, and recall to see how often the model categorizes correctly. For regression models that predict numbers, you examine mean squared error to measure how far predictions are from actual values.

After evaluation, you can use the model for inference on new data, or register it in the Vertex AI Model Registry for advanced deployment and monitoring. This registration enables online serving and ongoing performance tracking as new data arrives.

SQL Syntax for Model Creation

The CREATE MODEL statement is the foundation of building models in BigQuery ML. Writing the statement correctly determines whether the model trains properly and produces useful predictions. The statement must specify the correct model type, point to the training data table, and include appropriate options.

Model optimization involves testing different approaches to improve performance. You might compare models like linear regression against random forest to see which performs better on your data. BigQuery ML provides evaluation functions that show how well each model fits your data.

Model evaluation happens after training using a separate test dataset. You can review results in different formats including pandas DataFrames or BigQuery DataFrames to understand where the model succeeds and where it needs improvement.

Model inference uses the trained model to generate predictions on new data. The ML.PREDICT function applies the model to fresh datasets, whether the model was created in BigQuery ML or imported from another source.

The Data Science Agent in BigQuery can assist by generating Python code using libraries like sklearn. It can also help create SQL queries for BigQuery ML when you use keywords like "SQL" in your prompts, making the workflow more efficient.