Unlock the power of your data in the cloud! Get hands-on with Google Cloud's core data services like BigQuery and Looker to validate your practical skills in data ingestion, analysis, and management, and earn your Associate Data Practitioner certification!
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:
StudyCerts
StudyCerts'LINEAR_REG'.'LOGISTIC_REG'.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:
'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.
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.
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.
Prepare and test your skills
Prepare and test your skills
BigQuery ML (BQML) is the GCP service that lets data practitioners build machine learning models directly inside BigQuery using standard SQL queries, avoiding the need to move data to external tools and simplifying the entire machine learning workflow.
Supervised learning models, like linear regression and logistic regression, work when your data includes a known answer called a label that the model learns to predict. Unsupervised learning models, like K-means clustering, find patterns without predefined answers by grouping data into clusters based on similarity.
You evaluate a trained model in BigQuery ML using the ML.EVALUATE function, which generates performance metrics. For classification models, you examine accuracy, precision, and recall, while for regression models, you examine mean squared error to measure prediction accuracy.
A data analyst has trained a linear regression model in BigQuery ML to predict customer transaction amounts. The analyst executes the ML.EVALUATE SQL function on an evaluation dataset to assess the model's predictive accuracy.
Which metric returned by the evaluation function measures the average absolute difference between target values and predicted values, where a lower score indicates better model performance?