Como usar o BigQuery ML para prever resultados de basquete

Neste tutorial, usaremos o BigQuery ML para prever tentativas de três pontos no basquete. Com ele, os usuários criam e executam modelos de machine learning no BigQuery usando consultas SQL. O objetivo é democratizar o aprendizado de máquina, de modo que os especialistas em SQL criem modelos usando as respectivas ferramentas atuais e aumentem a velocidade de desenvolvimento ao eliminar a necessidade de movimentar os dados.

Neste tutorial, você usa a amostra do conjunto de dados públicos do NCAA Basketball Data para BigQuery e cria um modelo para prever tentativas de três pontos.

Objetivos

Neste tutorial, você usará:

  • o BigQuery ML para criar um modelo de regressão linear usando a instrução CREATE MODEL;
  • a função ML.EVALUATE para avaliar o modelo de ML;
  • a função ML.PREDICT para fazer previsões usando o modelo de ML.

Custos

Componentes faturáveis do Cloud Platform são usados neste tutorial, como os seguintes:

  • BigQuery
  • BigQuery ML
  • Datalab

Para ver mais informações sobre os custos do BigQuery, consulte a página de preços do BigQuery.

Para saber mais sobre os custos de ML do BigQuery, veja a página de preços de ML do BigQuery.

Para mais informações sobre os custos do Datalab, consulte a página preços do Datalab.

Antes de começar

  1. Faça login na sua Conta do Google.

    Se você ainda não tiver uma, inscreva-se.

  2. No Console do Cloud, na página de seletor de projetos, selecione ou crie um projeto do Cloud.

    Acessar a página do seletor de projetos

  3. Verifique se a cobrança está ativada para o seu projeto do Google Cloud. Saiba como confirmar se a cobrança está ativada para o seu projeto.

  4. O BigQuery é ativado automaticamente em novos projetos. Para ativar o BigQuery em um projeto preexistente, acesse Ative a API BigQuery.

    Ative a API

Etapa 1: configurar o Datalab

Neste tutorial, você configura o Datalab usando o Cloud Shell. O Datalab oferece um ambiente de notebook baseado em Jupyter para você trabalhar com o ML do BigQuery e o BigQuery.

Antes de configurar o Datalab, você precisa ativar a API Compute Engine e a API Cloud Source Repositories. Para ver informações sobre como ativar essas APIs, consulte Como ativar e desativar serviços.

Para configurar o Datalab em uma VM do Compute Engine:

  1. Acesse o Console do Cloud.

    Console do Cloud

  2. Clique em Ativar o Cloud Shell.

    Ícone

  3. Na janela do Cloud Shell, digite o seguinte comando para configurar a ferramenta gcloud para usar us-central1-a como sua zona padrão para o SDK do Cloud. Essa zona conterá sua instância do Datalab.

    gcloud config set compute/zone us-central1-a
    
  4. Digite o seguinte comando para criar uma VM do Compute Engine (chamada mltutorial) usada para executar o contêiner do Datalab. Esse comando também cria uma conexão SSH com a VM e mapeia a interface da Web do Datalab remota para a porta localhost 8081. Se solicitado, insira o número correspondente à zona padrão do Compute Engine.

    datalab create mltutorial
    

    Depois de ver a mensagem The connection to Datalab is now open and will remain until this command is killed, você estará conectado à instância. É possível que a interface de linha de comando deixe de produzir saídas quando a conexão SSH for estabelecida.

  5. Na janela do Cloud Shell, clique no ícone Ícone de visualização da Web Visualização da Web.

  6. Selecione Alterar porta.

  7. Na caixa de diálogo Alterar porta de visualização, insira 8081 e clique em Alterar e visualizar. Isso abre o Datalab em uma nova guia.

    gcloud compute ssh --quiet --zone "us-central1-a" --ssh-flag="-N" --ssh-flag="-L" --ssh-flag="localhost:8081:localhost:8080" "${USER}@mltutorial"
    
  8. Clique em Bloco de notas para criar um novo bloco de notas. Ele será aberto em uma nova guia.

  9. Na barra de título, clique em Notebook sem título para alterar o nome do notebook.

  10. Na caixa de diálogo Renomear notebook, digite BigQuery ML tutorial e clique em Ok.

  11. Na primeira célula de código, digite o comando abaixo para atualizar para a versão mais recente da biblioteca de cliente em Python do BigQuery.

    !pip install --upgrade google-cloud-bigquery
    
  12. Clique em Executar > Executar desta célula. Os resultados da consulta são exibidos abaixo do bloco de código. A instalação estará concluída quando você visualizar a seguinte mensagem:

    Successfully installed ...

  13. O código produzirá saída. Para ocultá-la, clique no menu da célula e selecione Recolher.

    Menu da célula

  14. Clique em Adicionar código para criar uma nova célula de código.

  15. Digite o código abaixo para importar a biblioteca de cliente em Python do BigQuery e inicializar um cliente. O cliente do BigQuery é usado para enviar e receber mensagens da API do BigQuery.

    from google.cloud import bigquery
    client = bigquery.Client()
    # We use a unique dataset ID for this example to avoid collisions with
    # other invocations of this tutorial.  In practice, you could leverage
    # a persistent dataset and not create/destroy it with each invocation.
    dataset_id = "bqml_tutorial_{}".format(str(uuid.uuid4().hex))
  16. Clique em Executar > Executar desta célula. Esse comando não produz saída.

Etapa 2: criar o conjunto de dados

Veja a seguir como criar um conjunto de dados do BigQuery para armazenar seu modelo de ML. Para criar o conjunto de dados, faça o seguinte:

  1. Clique em Adicionar código para criar uma nova célula de código.

  2. Digite o comando abaixo para criar o conjunto de dados.

    dataset = bigquery.Dataset(client.dataset(dataset_id))
    dataset.location = 'US'
    client.create_dataset(dataset)
  3. Clique em Executar > Executar desta célula. A saída de comando terá a seguinte aparência:

    Dataset '[project_ID]:bqml_tutorial' successfully created.

Etapa 3: gerar dados de treinamento e recursos

Veja a seguir como gerar dados de treinamento e recursos. Primeiro, gere os recursos de entrada do modelo consultando a tabela bigquery-public-data.ncaa_basketball.mbb_teams_games_sr no conjunto de dados públicos NCAA Basketball Data. Após gerar os recursos de entrada, gere os dados de treinamento.

Gerar os recursos de entrada - Engenharia de recursos

Os recursos de entrada incluem a média e o desvio padrão das estatísticas de jogos anteriores das equipes locais e visitantes, usando diferentes janelas de tempo. As janelas de tempo usadas são 10, 5 e 1 jogos antes do jogo atual. A coluna team-id, para equipes locais e visitantes, e a coluna season também são usadas como recursos simples, o que contribui com a média estatística para o modelo.

Devido à dimensão da consulta usada para gerar os recursos de entrada, use uma consulta salva na IU da Web clássica do BigQuery para criar a tabela.

Para gerar os dados do recurso de entrada, faça o seguinte:

  1. Clique no botão abaixo para abrir a consulta de dados de entrada.

    Abrir a consulta de entrada do recurso

    No tempo permitido, examine a consulta.

  2. Sob a janela de consulta, clique em Mostrar Opções.

  3. Na seção Tabela de destino, clique em Selecionar tabela.

  4. Na caixa de diálogo Selecionar tabela de destino:

    1. Em Projeto, selecione seu projeto.
    2. Em Conjunto de dados, escolha bqml_tutorial.
    3. No campo ID da tabela, insira cume_games.
    4. Clique em OK.
  5. Clique em Executar consulta. Isso cria um job de consulta que grava os resultados da consulta na tabela que você especificou.

    Como alternativa, se você esquecer de especificar uma tabela de destino antes de executar a consulta, será possível copiar a tabela temporária para uma permanente clicando no botão Salvar como tabela na janela de resultados.

  6. A conclusão da consulta levará alguns segundos. Depois que a consulta é executada, a tabela (cume_games) é exibida no painel de navegação da IU da Web do BigQuery.

Gerar dados de treinamento

Após gerar os recursos de entrada, gere os dados de treinamento. Os dados de treinamento são gravados na tabela bqml_tutorial.wide_games. Ao gerar esses dados, exclua um jogo para prever os resultados.

Nessa consulta, você mescla automaticamente a tabela cume_games criada ao executar a consulta anterior. Isso é necessário para gerar as estatísticas das equipes locais e visitantes, antes do jogo atual.

Devido ao tamanho da consulta usada para gerar seus dados de treinamento, use uma consulta salva na interface da Web clássica do BigQuery para criar a tabela.

Para gerar seus dados de treinamento, faça o seguinte:

  1. Clique no botão abaixo para abrir a consulta de dados de entrada.

    Abrir a consulta de dados de treinamento

    No tempo permitido, examine a consulta.

  2. Sob a janela de consulta, clique em Mostrar Opções.

  3. Na seção Tabela de destino, clique em Selecionar tabela.

  4. Na caixa de diálogo Selecionar tabela de destino:

    1. Em Projeto, selecione seu projeto.
    2. Em Conjunto de dados, escolha bqml_tutorial.
    3. No campo ID da tabela, insira wide_games.
    4. Clique em OK.
  5. Clique em Executar consulta. Isso cria um job de consulta que grava os resultados da consulta na tabela que você especificou.

    Como alternativa, se você esquecer de especificar uma tabela de destino antes de executar a consulta, será possível copiar a tabela temporária para uma permanente clicando no botão Salvar como tabela na janela de resultados.

  6. A conclusão da consulta levará alguns segundos. Depois que a consulta é executada, a tabela (wide_games) é exibida no painel de navegação da IU da Web do BigQuery.

Etapa 4: criar modelo

Veja a seguir como criar um modelo de regressão linear. O modelo é usado para prever as tentativas de três pontos combinadas em um jogo com base nas estatísticas de jogos anteriores. A consulta SQL padrão usa uma instrução CREATE MODEL para criar o modelo. Embora a instrução CREATE MODEL possa criar e treinar seu modelo, neste tutorial, você treinará o modelo separadamente.

Para executar a consulta CREATE MODEL para criar seu modelo:

  1. Mude para seu notebook do Datalab.

  2. Clique em Adicionar código para criar uma nova célula de código.

  3. A biblioteca de cliente em Python do BigQuery fornece um comando mágico com que é possível executar consultas com uma codificação mínima. Para carregar os comandos mágicos da biblioteca de cliente, insira o código abaixo.

    %load_ext google.cloud.bigquery
    
  4. Clique em Executar > Executar desta célula. Esse comando não produz saída.

  5. Clique em Adicionar código para criar uma nova célula de código.

  6. A biblioteca de cliente do BigQuery fornece um comando mágico de célula, %%bigquery, que executa uma consulta SQL e retorna os resultados como um DataFrame do Pandas. Na célula, digite a consulta SQL padrão a seguir. O prefixo #standardSQL não é necessário para a biblioteca de cliente. O SQL padrão é a sintaxe de consulta padrão.

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 `{0}.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
        `{0}.wide_games`
    WHERE
        # remove the game to predict
        game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

  1. Clique em Executar > Executar desta célula.

  2. A consulta leva alguns minutos para ser concluída. Após a conclusão da primeira iteração, seu modelo (ncaa_model) é exibido no painel de navegação da IU da Web do BigQuery. Como a consulta usa uma instrução CREATE MODEL, você não vê os resultados da consulta. A saída será uma string vazia.

Etapa 5: receber estatísticas de treinamento

Para ver os resultados do treinamento de modelo, é possível usar a função ML.TRAINING_INFO ou visualizar as estatísticas na IU da Web do BigQuery. No momento, essa funcionalidade não está disponível na IU da Web clássica do BigQuery. Neste tutorial, você usa a função ML.TRAINING_INFO.

Para criar um modelo, um algoritmo de aprendizado de máquina examina vários exemplos e tenta encontrar um modelo que minimize a perda. Esse processo é chamado de "minimização de riscos empíricos".

Perda é a penalidade para uma previsão ruim, ou seja, um número que indica como a previsão do modelo foi ruim em um único exemplo. Para uma previsão de modelo perfeita, a perda é zero. Caso contrário, a perda é maior. O treinamento de um modelo visa encontrar um conjunto de ponderações e tendências com uma média de perda menor em todos os exemplos.

Para ver as estatísticas de treinamento do modelo que foram geradas quando você executou a consulta CREATE MODEL:

  1. Clique em Adicionar código para criar uma nova célula de código.

  2. Na célula, digite a consulta SQL padrão a seguir.

Jupyter

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

Python

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `{}.ncaa_model`)
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

  1. Clique em Executar > Executar desta célula.

  2. Quando a consulta for concluída, os resultados serão exibidos logo abaixo. Os resultados terão a aparência a seguir:

    Saída de ML.TRAINING_INFO

    A coluna Perda de dados de treinamento representa a métrica de perda calculada após a iteração especificada no conjunto de dados de treinamento. Como você executou uma regressão linear, essa coluna é o erro quadrático médio. A coluna Perda de dados de avaliação é a mesma métrica de perda calculada no conjunto de dados de validação, ou seja, dados do treinamento mantidos para validar o modelo.

    Para ver mais detalhes sobre a função ML.TRAINING_INFO, consulte a referência de sintaxe do BigQuery ML.

Etapa 6: avaliar o modelo

Depois de criar o modelo, utilize ML.EVALUATE para avaliar o desempenho dele.

Para executar a consulta ML.EVALUATE que avalia o modelo:

  1. Clique em Adicionar código para criar uma nova célula de código.

  2. Na célula, digite a consulta SQL padrão a seguir.

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
            `{0}.wide_games` )
    SELECT
        *
    FROM
        ML.EVALUATE(MODEL `{0}.ncaa_model`,
            TABLE eval_table)
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

  1. Clique em Executar > Executar desta célula.

  2. Quando a consulta for concluída, os resultados serão exibidos logo abaixo. Os resultados terão a aparência a seguir:

    Saída de ML.EVALUATE

    Como você executou uma regressão linear, os resultados incluem as seguintes colunas:

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

Uma métrica importante nos resultados da avaliação é a média de erro absoluto. A média de erro absoluto é a diferença média entre o rótulo previsto e o real. Neste caso, gira em torno de 7.

Etapa 7: usar o modelo para prever resultados

Agora que você avaliou seu modelo, a próxima etapa é usar a função ML.PREDICT para prever o total de tentativas de três pontos no jogo final do NCAA 2018: Michigan contra Villanova.

Para executar a consulta que usa a função ML.PREDICT:

  1. Clique em Adicionar código para criar uma nova célula de código.

  2. Na célula, digite a consulta SQL padrão a seguir.

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
            `{0}.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 `{0}.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
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

  1. Clique em Executar > Executar desta célula.

  2. Quando a consulta for concluída, os resultados serão exibidos logo abaixo. Os resultados terão a aparência a seguir. Como o treinamento de modelo não é determinístico, seus resultados podem ser diferentes.

    Saída de ML.PREDICT

O valor total_three_points_att é o número real de pontos marcados no último jogo: 50. A previsão do modelo é 43.41.

Limpeza

Para evitar que os recursos usados neste tutorial sejam cobrados na conta do Google Cloud Platform, faça uma das coisas a seguir:

  • exclua o projeto que você criou; ou
  • mantenha-o e exclua a VM do Datalab.

Como excluir sua VM do Datalab

A exclusão do projeto remove a VM do Datalab. Se não quiser excluir o projeto do Cloud Platform, é possível excluir a VM do Datalab.

Para excluir a VM do Datalab:

  1. Abra a página "Instâncias de VMs" do Compute Engine.

    Acessar a página "Instâncias de VMs"

  2. Marque a instância do mltutorial e clique em Excluir.

  3. Quando solicitado, clique em Excluir.

Como excluir o projeto

Para excluir o projeto, faça o seguinte:

  1. No Console do Cloud, acesse a página Gerenciar recursos:

    Acessar a página Gerenciar recursos

  2. Na lista de projetos, selecione o projeto que você quer excluir e clique em Excluir .
  3. Na caixa de diálogo, digite o ID do projeto e clique em Encerrar para excluí-lo.

A seguir