BigQuery ML (BQML) allows data analysts and data scientists to build and operationalize machine learning models directly inside BigQuery using SQL. This integration simplifies the process of creating, training, and deploying machine learning models without needing to move data out of BigQuery.

1. Machine Learning (ML): A subset of artificial intelligence (AI) that enables systems to learn from data and improve from experience without being explicitly programmed.
2. BigQuery ML: A feature in BigQuery that allows users to create and execute machine learning models using SQL queries.
3. SQL: Structured Query Language, used for managing and manipulating relational databases.

• Ease of Use: Leverage SQL skills to build ML models.
• Scalability: Utilize BigQuery's infrastructure to handle large datasets.
• Integration: Seamlessly integrate with other Google Cloud services.
• Cost-Effective: Pay only for the queries you run, with no additional infrastructure costs.

1. Data Preparation: Clean and preprocess your data.
2. Model Creation: Use SQL to define and create your ML model.
3. Model Training: Train the model using your dataset.
4. Model Evaluation: Evaluate the model's performance.
5. Prediction: Use the trained model to make predictions on new data.

Ensure your data is clean and structured. For this example, we'll use a public dataset available in BigQuery.

Create a linear regression model to predict the body mass of penguins based on their features.

The model creation step also trains the model using the specified dataset.

Evaluate the model's performance using the ML.EVALUATE function.

Use the trained model to make predictions on new data.

1. Objective: Create a logistic regression model to predict the species of penguins based on their features.
2. Dataset: Use the same bigquery-public-data.ml_datasets.penguins dataset.

Steps:

1. Create the Model:

   CREATE OR REPLACE MODEL `my_dataset.penguin_species_model`
   OPTIONS(model_type='logistic_reg') AS
   SELECT
     species AS label,
     flipper_length_mm,
     bill_length_mm,
     bill_depth_mm
   FROM
     `bigquery-public-data.ml_datasets.penguins`
   WHERE
     species IS NOT NULL
   

2. Evaluate the Model:

   SELECT
     *
   FROM
     ML.EVALUATE(MODEL `my_dataset.penguin_species_model`,
     (
       SELECT
         species AS label,
         flipper_length_mm,
         bill_length_mm,
         bill_depth_mm
       FROM
         `bigquery-public-data.ml_datasets.penguins`
       WHERE
         species IS NOT NULL
     ))
   

3. Make Predictions:

   SELECT
     *
   FROM
     ML.PREDICT(MODEL `my_dataset.penguin_species_model`,
     (
       SELECT
         flipper_length_mm,
         bill_length_mm,
         bill_depth_mm
       FROM
         `bigquery-public-data.ml_datasets.penguins`
       WHERE
         species IS NULL
     ))
   

1. Model Creation:

   CREATE OR REPLACE MODEL `my_dataset.penguin_species_model`
   OPTIONS(model_type='logistic_reg') AS
   SELECT
     species AS label,
     flipper_length_mm,
     bill_length_mm,
     bill_depth_mm
   FROM
     `bigquery-public-data.ml_datasets.penguins`
   WHERE
     species IS NOT NULL
   

2. Model Evaluation:

   SELECT
     *
   FROM
     ML.EVALUATE(MODEL `my_dataset.penguin_species_model`,
     (
       SELECT
         species AS label,
         flipper_length_mm,
         bill_length_mm,
         bill_depth_mm
       FROM
         `bigquery-public-data.ml_datasets.penguins`
       WHERE
         species IS NOT NULL
     ))
   

3. Prediction:

   SELECT
     *
   FROM
     ML.PREDICT(MODEL `my_dataset.penguin_species_model`,
     (
       SELECT
         flipper_length_mm,
         bill_length_mm,
         bill_depth_mm
       FROM
         `bigquery-public-data.ml_datasets.penguins`
       WHERE
         species IS NULL
     ))
   

In this section, you learned the basics of BigQuery ML, including its benefits and the typical workflow for creating and using machine learning models in BigQuery. You also practiced creating, evaluating, and using a model to make predictions using SQL. This foundational knowledge prepares you for more advanced topics in BigQuery ML.