Usa BigQuery ML para predecir resultados de partidos de baloncesto

En este instructivo, se usa BigQuery ML para predecir intentos de tiros de tres puntos en básquetbol. BigQuery ML permite a los usuarios crear y ejecutar modelos de aprendizaje automático en BigQuery mediante consultas de SQL. El objetivo es democratizar el aprendizaje automático. A fin de lograrlo, se habilita a los profesionales de SQL para que compilen modelos con sus herramientas existentes y se elimina la necesidad de trasladar datos con el fin de aumentar la velocidad de desarrollo.

En este instructivo, usarás la muestra conjunto de datos públicos de básquetbol de la NCAA para BigQuery con el fin de crear un modelo que prediga los intentos de tiros de tres puntos.

Objetivos

En este instructivo usarás:

  • BigQuery ML para crear un modelo de regresión lineal con la declaración CREATE MODEL
  • La función ML.EVALUATE para evaluar el modelo de AA
  • La función ML.PREDICT para hacer predicciones con el modelo de AA

Costos

En este instructivo, se usan componentes facturables de Cloud Platform, incluidos los siguientes:

  • BigQuery
  • BigQuery ML
  • Cloud Datalab

Para obtener más información sobre los costos de BigQuery, consulta la página de precios de BigQuery.

Para obtener más información sobre los costos de BigQuery ML, consulta la página de precios de BigQuery ML.

Para obtener más información sobre los costos de Cloud Datalab, consulta la página de precios de Cloud Datalab.

Antes de comenzar

  1. Accede a tu Cuenta de Google.

    Si todavía no tienes una cuenta, regístrate para obtener una nueva.

  2. Selecciona o crea un proyecto de GCP.

    Ir a la página Administrar recursos

  3. Asegúrate de tener habilitada la facturación para tu proyecto.

    Aprende a habilitar la facturación

  4. BigQuery se habilita de forma automática en proyectos nuevos. Para activar BigQuery en un proyecto preexistente, ve a Habilita las BigQuery API necesarias.

    Habilita las API

Paso uno: Configura Cloud Datalab

En este instructivo, debes configurar Cloud Datalab con Cloud Shell. Cloud Datalab proporciona un entorno de notebook basado en Jupyter que usas para trabajar con BigQuery ML y BigQuery.

Antes de configurar Cloud Datalab, debes habilitar la API de Compute Engine y la API de Cloud Source Repositories. Para obtener más información sobre cómo habilitar estas API, consulta Inhabilita y habilita servicios.

Para configurar Cloud Datalab en una VM de Compute Engine, haz lo siguiente:

  1. Ve a GCP Console.

    GCP Console

  2. Haz clic en Activar Cloud Shell (Activate Cloud Shell).

    Activa el ícono de Cloud Shell

  3. En la ventana de Cloud Shell, ingresa el comando siguiente a fin de configurar la herramienta gcloud y usar us-central1-a como tu zona predeterminada para el SDK de Cloud. Esta zona contendrá tu instancia de Cloud Datalab.

    gcloud config set compute/zone us-central1-a
    
  4. Ingresa el comando siguiente a fin de crear una VM de Compute Engine (llamada mltutorial), que se usa para ejecutar el contenedor de Cloud Datalab. Este comando también crea una conexión SSH con tu VM y asigna la interfaz web remota de Cloud Datalab al puerto local 8081. Si se te solicita, ingresa el número correspondiente a tu zona predeterminada de Compute Engine.

    datalab create mltutorial
    

    Cuando veas el mensaje The connection to Datalab is now open and will remain until this command is killed, estás conectado a la instancia. Es posible que la interfaz de la línea de comandos ya no produzca resultados una vez que se establezca la conexión SSH.

  5. En la ventana de Cloud Shell, haz clic en el ícono Ícono de vista previa web de Vista previa web.

  6. Selecciona Cambiar puerto.

  7. En el diálogo Cambiar puerto de vista previa, ingresa 8081 y, luego, haz clic en Cambiar y obtener vista previa. Esto abre Cloud Datalab en una pestaña nueva.

    gcloud compute ssh --quiet --zone "us-central1-a" --ssh-flag="-N" --ssh-flag="-L" --ssh-flag="localhost:8081:localhost:8080" "${USER}@mltutorial"
    
  8. Haz clic en Notebook para crear un notebook nuevo. El notebook se abre en una pestaña nueva.

  9. En la barra de título, haz clic en Notebook sin título para cambiar el nombre del notebook.

  10. En el diálogo Renombrar notebook, ingresa BigQuery ML tutorial y, luego, haz clic en Ok.

  11. En la primera celda de código, ingresa lo siguiente a fin de actualizar a la última versión de la biblioteca cliente de Python para BigQuery.

    !pip install --upgrade google-cloud-bigquery
    
  12. Haz clic en Ejecutar > Ejecutar desde esta celda. Los resultados de la consulta aparecen debajo del bloque del código. La instalación se completa cuando ves el mensaje siguiente:

    Successfully installed ...

  13. El código produce una respuesta de tamaño considerable. Para ocultar la respuesta, haz clic en el menú de la celda y selecciona Collapse (contraer) a fin de contraer la celda.

    Menú de la celda

  14. Haz clic en Agregar código para crear una celda de código nueva.

  15. Ingresa el siguiente código a fin de importar la biblioteca cliente de Python para BigQuery y, luego, inicializar un cliente. El cliente de BigQuery se usa para enviar y recibir mensajes de la API de BigQuery.

    from google.cloud import bigquery
    client = bigquery.Client()
  16. Haz clic en Run > Run from this cell (Ejecutar > Ejecutar desde esta celda). Este comando no produce respuesta.

Paso dos: crea tu conjunto de datos

A continuación, crea un conjunto de datos de BigQuery para almacenar tu modelo de AA. Para crear tu conjunto de datos, haz lo siguiente:

  1. Haz clic en Add Code (agregar código) para crear una celda de código nueva.

  2. Ingresa el comando siguiente para crear tu conjunto de datos.

    dataset = bigquery.Dataset(client.dataset('bqml_tutorial'))
    dataset.location = 'US'
    client.create_dataset(dataset)
  3. Haz clic en Run > Run from this cell (Ejecutar > Ejecutar desde esta celda). El resultado del comando debe verse de la siguiente manera:

    Dataset '[project_ID]:bqml_tutorial' successfully created.

Paso tres: Genera tus datos de entrenamiento y atributos

A continuación, debes generar tus datos de entrenamiento y atributos. Primero debes generar los atributos de entrada del modelo mediante una consulta a la tabla bigquery-public-data.ncaa_basketball.mbb_teams_games_sr en el conjunto de datos públicos de básquetbol de la NCAA. Después de generar los atributos de entrada, debes generar tus datos de entrenamiento.

Genera los atributos de entrada (ingeniería de atributos)

Los atributos de entrada incluyen la media y la desviación estándar de las estadísticas del juego anterior, tanto para los equipos locales como para los equipos visitantes mediante diferentes ventanas de tiempo. Las ventanas de tiempo utilizadas son 10, 5 y 1 juegos antes del juego actual. La columna team-id (tanto para los equipos locales como para los equipos visitantes) y la columna season, también se usan como atributos únicos, lo que contribuye a las estadísticas promedio del modelo.

Debido a la longitud de la consulta que se usa para generar los atributos de entrada, debes usar una consulta guardada en la IU web clásica de BigQuery para crear la tabla.

Para generar los datos de los atributos de entrada, haz lo siguiente:

  1. Haz clic en el botón de abajo para abrir la consulta de datos de entrada.

    Abrir la consulta de entrada de los atributos

    Cuando el tiempo lo permita, examina la consulta.

  2. Debajo de la ventana de consulta, haz clic en Mostrar opciones.

  3. En la sección Tabla de destino, haz clic en Seleccionar tabla.

  4. En el cuadro de diálogo Seleccionar tabla de destino, haz lo siguiente:

    1. En Proyecto, selecciona tu proyecto.
    2. Para Conjunto de datos, selecciona bqml_tutorial.
    3. En el campo ID de tabla, ingresa cume_games.
    4. Haz clic en Aceptar.
  5. Haz clic en Ejecutar consulta. Esto crea un trabajo de consulta que escribe los resultados de las consultas en la tabla que especificaste.

    Como alternativa, si olvidas especificar una tabla de destino antes de ejecutar tu consulta, puedes hacer clic en el botón Guardar como tabla en la ventana de resultados para copiar la tabla temporal en una tabla permanente.

  6. La consulta tardará unos segundos en completarse. Tras la ejecución de la consulta, tu tabla (cume_games) aparecerá en el panel de navegación de la IU web de BigQuery.

Genera tus datos de entrenamiento

Después de generar los atributos de entrada, debes generar tus datos de entrenamiento. Los datos de entrenamiento se escriben en la tabla bqml_tutorial.wide_games. Cuando generas tus datos de entrenamiento, debes excluir un juego para usarlo para predecir resultados.

En esta consulta, autocombinas la tabla cume_games que creaste al ejecutar la consulta anterior. Debes hacer esto para generar las estadísticas del equipo local y del equipo visitante antes del juego actual.

Debido a la longitud de la consulta que se usa para generar tus datos de entrenamiento, debes usar una consulta guardada en la IU web clásica de BigQuery para crear la tabla.

Para generar tus datos de entrenamiento, haz lo siguiente:

  1. Haz clic en el botón de abajo para abrir la consulta de datos de entrada.

    Abrir la consulta de datos de entrenamiento

    Cuando el tiempo lo permita, examina la consulta.

  2. Debajo de la ventana de consulta, haz clic en Mostrar opciones.

  3. En la sección Tabla de destino, haz clic en Seleccionar tabla.

  4. En el cuadro de diálogo Seleccionar tabla de destino, haz lo siguiente:

    1. En Proyecto, selecciona tu proyecto.
    2. Para Conjunto de datos, selecciona bqml_tutorial.
    3. En el campo ID de tabla, ingresa wide_games.
    4. Haz clic en Aceptar.
  5. Haz clic en Ejecutar consulta. Esto crea un trabajo de consulta que escribe los resultados de las consultas en la tabla que especificaste.

    Como alternativa, si olvidas especificar una tabla de destino antes de ejecutar tu consulta, puedes hacer clic en el botón Guardar como tabla en la ventana de resultados para copiar la tabla temporal en una tabla permanente.

  6. La consulta tardará unos segundos en completarse. Tras la ejecución de la consulta, tu tabla (wide_games) aparecerá en el panel de navegación de la IU web de BigQuery.

Paso cuatro: Crea tu modelo

A continuación, debes crear un modelo de regresión lineal. El modelo se utiliza para predecir los intentos combinados de goles de campo de tres puntos según las estadísticas del juego anterior. La consulta de SQL estándar utiliza una declaración CREATE MODEL para crear el modelo. Aunque la declaración CREATE MODEL puede crear y entrenar a tu modelo, en este instructivo entrenas el modelo por separado.

Para ejecutar la consulta CREATE MODEL para crear tu modelo, haz lo siguiente:

  1. Cambia a tu notebook de Cloud Datalab.

  2. Haz clic en Agregar código para crear una celda de código nueva.

  3. La biblioteca cliente de Python para BigQuery proporciona un comando automático que te permite ejecutar consultas con un código mínimo. Para cargar los comandos automáticos de la biblioteca cliente, ingresa el código siguiente.

    %load_ext google.cloud.bigquery
    
  4. Haz clic en Run > Run from this cell (Ejecutar > Ejecutar desde esta celda). Este comando no produce respuesta.

  5. Haz clic en Add Code (agregar código) para crear una celda de código nueva.

  6. La biblioteca cliente de BigQuery proporciona una celda automática %%bigquery, la cual ejecuta una consulta de SQL y muestra los resultados como un marco de datos de Pandas. Ingresa la siguiente consulta de SQL estándar en la celda. No se requiere usar el prefijo #standardSQL para la biblioteca cliente. SQL estándar es la sintaxis de consulta predeterminada.

Jupyter

%%bigquery
CREATE OR REPLACE MODEL bqml_tutorial.ncaa_model OPTIONS(
  model_type='linear_reg', max_iteration=50) AS
SELECT
  * EXCEPT(
    game_id, season, scheduled_date,
    total_three_points_made,
    total_three_points_att),
  total_three_points_att as label
FROM
  bqml_tutorial.wide_games
WHERE
  # remove the game to predict
  game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'

Python

sql = """
    CREATE OR REPLACE MODEL `bqml_tutorial.ncaa_model`
    OPTIONS (
        model_type='linear_reg',
        max_iteration=50 ) AS
    SELECT
        * EXCEPT (
            game_id, season, scheduled_date,
            total_three_points_made,
            total_three_points_att),
        total_three_points_att as label
    FROM
        `bqml_tutorial.wide_games`
    WHERE
        # remove the game to predict
        game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'
"""
df = client.query(sql).to_dataframe()
print(df)

  1. Haz clic en Ejecutar > Ejecutar desde esta celda.

  2. La consulta tarda varios minutos en completarse. Una vez completada la primera iteración, tu modelo (ncaa_model) aparece en el panel de navegación de la IU web de BigQuery. Dado que la consulta usa una declaración CREATE MODEL, no verás los resultados de la consulta. El resultado es una string vacía.

Paso cinco: Obtén estadísticas de entrenamiento

Para ver los resultados del entrenamiento del modelo, puedes usar la función ML.TRAINING_INFO o puedes ver las estadísticas en la IU web de BigQuery. Actualmente, esta funcionalidad no está disponible en la IU web clásica de BigQuery. En este instructivo, usas la función ML.TRAINING_INFO.

Un algoritmo de aprendizaje automático examina muchos ejemplos y trata de encontrar un modelo que minimice la pérdida. Este proceso se llama minimización del riesgo empírico.

La pérdida es la multa para una mala predicción, un número que indica qué tan mala fue la predicción del modelo en un ejemplo individual. Si la predicción del modelo es perfecta, la pérdida es cero; de lo contrario, es mayor. El objetivo de entrenar a un modelo es encontrar un conjunto de pesos y ordenadas al origen que tengan, en promedio, una pérdida baja en todos los ejemplos.

Para ver las estadísticas de entrenamiento del modelo que se generaron cuando se ejecutó la consulta CREATE MODEL, haz lo siguiente:

  1. Haz clic en Add Code (agregar código) para crear una celda de código nueva.

  2. Ingresa la siguiente consulta de SQL estándar en la celda.

Jupyter

%%bigquery
SELECT
  *
FROM
  ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)

Python

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)
"""
df = client.query(sql).to_dataframe()
print(df)

  1. Haz clic en Run > Run from this cell (Ejecutar > Ejecutar desde esta celda).

  2. Cuando se completa la consulta, los resultados aparecen debajo de ella. Los resultados deberían verse de la siguiente manera:

    Resultado de ML.TRAINING_INFO

    La columna Pérdida de datos de entrenamiento representa la métrica de pérdida calculada después de la iteración dada en el conjunto de datos de entrenamiento. Como se realizó una regresión lineal, esta columna es el error cuadrático medio. La columna Evaluation Data Loss (Pérdida de datos de evaluación) es la misma métrica de pérdida calculada en el conjunto de datos de exclusión (datos que se usan a fin de validar el modelo y, por lo tanto, no se incluyen en el entrenamiento).

    Para obtener más detalles sobre la función ML.TRAINING_INFO, consulta la Referencia de sintaxis de BigQuery ML.

Paso seis: Evalúa tu modelo

Después de crear tu modelo, debes evaluar el rendimiento del mismo con la función ML.EVALUATE.

Para ejecutar la consulta ML.EVALUATE que evalúa el modelo, haz lo siguiente:

  1. Haz clic en Add Code (agregar código) para crear una celda de código nueva.

  2. Ingresa la siguiente consulta de SQL estándar en la celda.

Jupyter

%%bigquery
WITH eval_table AS (
SELECT
  *,
  total_three_points_att AS label
FROM
  `bqml_tutorial.wide_games` )
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
    TABLE eval_table)

Python

sql = """
    WITH eval_table AS (
        SELECT
            *,
            total_three_points_att AS label
        FROM
            `bqml_tutorial.wide_games` )
    SELECT
        *
    FROM
        ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
            TABLE eval_table)
"""
df = client.query(sql).to_dataframe()
print(df)

  1. Haz clic en Ejecutar > Ejecutar desde esta celda.

  2. Cuando se completa la consulta, los resultados aparecen debajo de ella. Los resultados deberían verse de la siguiente manera:

    Salida de ML.EVALUATE

    Debido a que realizaste una regresión lineal, los resultados incluyen las siguientes columnas:

    • mean_absolute_error
    • mean_squared_error
    • mean_squared_log_error
    • median_absolute_error
    • r2_score
    • explained_variance

Una métrica importante en los resultados de la evaluación es el error absoluto promedio. El error absoluto promedio es la diferencia absoluta promedio entre la etiqueta predicha y la etiqueta real. En este caso es de alrededor de 7.

Paso siete: Usa tu modelo para predecir resultados

Ahora que ya evaluaste tu modelo, el siguiente paso es usar la función ML.PREDICT para predecir los intentos de goles de campo de tres puntos en el juego final de la NCAA de 2018: Michigan versus Villanova.

Para ejecutar la consulta que usa la función ML.PREDICT:

  1. Haz clic en Agregar código para crear una célula de código nueva.

  2. Ingresa la siguiente consulta de SQL estándar en la celda.

Jupyter

%%bigquery
  WITH game_to_predict AS (
  SELECT
    *
  FROM
    `bqml_tutorial.wide_games`
  WHERE
    game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
SELECT
  truth.game_id AS game_id,
  total_three_points_att,
  predicted_total_three_points_att
FROM (
  SELECT
    game_id,
    predicted_label AS predicted_total_three_points_att
  FROM
    ML.PREDICT(MODEL `bqml_tutorial.ncaa_model`,
      table game_to_predict) ) AS predict
JOIN (
  SELECT
    game_id,
    total_three_points_att AS total_three_points_att
  FROM
    game_to_predict) AS truth
ON
  predict.game_id = truth.game_id

Python

sql = """
    WITH game_to_predict AS (
        SELECT
            *
        FROM
            `bqml_tutorial.wide_games`
        WHERE
            game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
    SELECT
        truth.game_id AS game_id,
        total_three_points_att,
        predicted_total_three_points_att
    FROM (
        SELECT
            game_id,
            predicted_label AS predicted_total_three_points_att
        FROM
            ML.PREDICT(MODEL `bqml_tutorial.ncaa_model`,
            table game_to_predict) ) AS predict
    JOIN (
        SELECT
            game_id,
            total_three_points_att AS total_three_points_att
        FROM
            game_to_predict) AS truth
    ON
        predict.game_id = truth.game_id
"""
df = client.query(sql).to_dataframe()
print(df)

  1. Haz clic en Ejecutar > Ejecutar desde esta celda.

  2. Cuando se completa la consulta, los resultados aparecen debajo de ella. Los resultados deberían verse de la siguiente manera. Dado que el entrenamiento del modelo no es determinista, los resultados pueden diferir.

    Resultado de ML.PREDICT

El valor total_three_points_att es el número real de goles de campo que se marcaron en el juego final: 50. La predicción del modelo es 43.41.

Limpieza

Sigue estos pasos para evitar que se apliquen cargos a tu cuenta de Google Cloud Platform por los recursos que usaste en este instructivo:

  • Puedes borrar el proyecto que creaste.
  • O puedes mantener el proyecto y borrar la VM de Cloud Datalab.

Borra tu VM de Cloud Datalab

Si borras tu proyecto, quitas la VM de Cloud Datalab. Si no deseas borrar el proyecto de Cloud Platform, puedes borrar la VM de Cloud Datalab.

Para borrar la VM de Cloud Datalab, haz lo siguiente:

  1. Abre la página instancias de VM de Compute Engine.

    Ir a la página instancias de VM

  2. Marca la instancia mltutorial y luego haz clic en Delete (borrar).

  3. Cuando se te solicite, haz clic en Delete (borrar).

Borra tu proyecto

Para borrar el proyecto, haz lo siguiente:

  1. En la GCP Console, dirígete a la página Proyectos.

    Ir a la página Proyectos

  2. En la lista de proyectos, selecciona el proyecto que deseas borrar y haz clic en Borrar.
  3. En el cuadro de diálogo, escribe el ID del proyecto y, luego, haz clic en Cerrar para borrar el proyecto.

Pasos siguientes

¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…

Documentación de BigQuery ML