Cómo usar BigQuery ML para predecir resultados de partidos de básquetbol

Este instructivo usa BigQuery ML para predecir intentos de goles de campo 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 permitiendo a los profesionales de SQL crear modelos con sus herramientas existentes y aumentar la velocidad de desarrollo mediante la eliminación de la necesidad de mover datos.

En este instructivo usarás la muestra conjunto de datos público de básquetbol de la NCAA para BigQuery para crear un modelo que prediga los intentos de goles de campo 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

Este instructivo usa componentes facturables de Cloud Platform, incluidos los siguientes:

  • Google BigQuery
  • BigQuery ML
  • Cloud Datalab

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

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

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

Antes de comenzar

  1. Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Google Cloud Platform project.

    Go to the Manage resources page

  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 nuevos proyectos. Para activar BigQuery en un proyecto preexistente, ve a Habilita las BigQuery API necesarias.

    Habilita las API

Público

Este es un instructivo avanzado diseñado para científicos de datos.

Un científico de datos usa SQL estándar de BigQuery y BigQuery ML para:

  • Explorar, analizar y visualizar datos
  • Limpiar conjuntos de datos para usarlos en modelos estadísticos
  • Crear modelos estadísticos y de AA
  • Crear, entrenar, evaluar y mejorar algoritmos y modelos de AA
  • Explorar datos en un almacén de datos como BigQuery

Un científico de datos usa varias herramientas, que incluyen:

  • La herramienta de línea de comandos de BigQuery (para consultar datos con SQL estándar)
  • Software estadístico como RStudio y Matlab
  • Cloud Datalab (notebooks de Jupyter)
  • Cloud ML Engine
  • Lenguajes de programación como Python y Java
  • Bibliotecas de AA como MLlib y TensorFlow
  • Herramientas de visualización como Cloud Datalab y Google Data Studio

Dado que este instructivo está dirigido a científicos de datos, no se explican muchos detalles relacionados con los conceptos del AA. Para ver un instructivo de introducción más detallado que utilice la IU web de BigQuery, consulta Cómo comenzar a usar BigQuery ML para analistas de datos.

Para ver descripciones de los conceptos presentados aquí, consulta lo siguiente:

Paso uno: Configura Cloud Datalab

En este instructivo, debes configurar Cloud Datalab con Google Cloud Shell. Cloud Datalab proporciona un entorno de notebook basado en Jupyter que tú 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 Cómo habilitar y también inhabilitar servicios.

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

  1. Ve a Cloud Platform Console.

    Cloud Platform Console

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

    Activar el ícono de Cloud Shell

  3. En la ventana de Cloud Shell, ingresa el siguiente comando para configurar la herramienta gcloud para 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 siguiente comando para 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 mapea 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, significa que 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 Vista previa en la web.

  6. Selecciona Cambiar puerto.

  7. En el diálogo Cambiar el puerto de la 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 Aceptar.

  11. En la primera celda de código, ingresa lo siguiente para 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 siguiente mensaje:

    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) para contraer la celda.

    Menú de la celda

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

  15. Ingresa el siguiente código para importar la biblioteca cliente de Python para BigQuery y así 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 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 Agregar código para crear una célula de código nueva.

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

    dataset = bigquery.Dataset(client.dataset('bqml_tutorial'))
    dataset.location = 'US'
    client.create_dataset(dataset)
  3. Haz clic en 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 utiliza 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. En Conjunto de datos, selecciona bqml_tutorial.
    3. En el campo ID de la 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 la consulta en la tabla que especificaste.

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

  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 utiliza para generar tus datos de entrenamiento, debes usar una consulta guardada en la IU web 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. En Conjunto de datos, selecciona bqml_tutorial.
    3. En el campo ID de la 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 la consulta en la tabla que especificaste.

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

  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 célula 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 mágicos de la biblioteca cliente, ingresa el siguiente código.

    %load_ext google.cloud.bigquery
    
  4. Haz clic en Ejecutar > Ejecutar desde esta celda. Este comando no produce respuesta.

  5. Haz clic en Agregar código para crear una célula 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 el uso del 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

Antes de probar esta muestra, sigue las instrucciones de configuración para Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para 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 de modelos, puedes usar la función ML.TRAINING_INFO o puedes ver las estadísticas en la IU web de BigQuery. Actualmente, esta funcionalidad no se encuentra disponible en la IU web clásica de BigQuery. En este instructivo utilizas 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 ponderaciones y sesgos que tengan una pérdida baja en promedio 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 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
SELECT
  *
FROM
  ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)

Python

Antes de probar esta muestra, sigue las instrucciones de configuración para Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Python.

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)
"""
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 la consulta. 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 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 no se incluyen en el entrenamiento para validar el modelo).

    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 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 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

Antes de probar esta muestra, sigue las instrucciones de configuración para Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para 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 la consulta. Los resultados deberían verse de la siguiente manera:

    Resultado 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

Antes de probar esta muestra, sigue las instrucciones de configuración para Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para 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 la consulta. 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 eliminar la VM de Cloud Datalab.

Cómo eliminar tu VM de Cloud Datalab

Al eliminar tu proyecto se elimina la VM de Cloud Datalab. Si no deseas eliminar el proyecto de Cloud Platform, puedes eliminar la VM de Cloud Datalab.

Para eliminar la VM de Cloud Datalab:

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

    Ir a la página de instancias de VM

  2. Marca la instancia mltutorial y luego haz clic en Borrar.

  3. Cuando se te solicite, haz clic en Borrar.

Cómo borrar tu proyecto

Para borrar el proyecto, haz lo siguiente:

  1. In the GCP Console, go to the Projects page.

    Go to the Projects page

  2. In the project list, select the project you want to delete and click Delete .
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Pasos siguientes

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

Enviar comentarios sobre…

¿Necesitas ayuda? Visita nuestra página de asistencia.