Como usar o BigQuery ML para predizer 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 machine learning, 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 ML;
  • a função ML.PREDICT para fazer previsões usando o modelo de ML.

Custos

Este tutorial usa componentes faturáveis do Cloud Platform, incluindo:

  • Google BigQuery
  • BigQuery ML
  • Cloud Datalab

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

Para saber mais informações sobre os custos do BigQuery ML, consulte a seção de Preços do BigQuery ML na página de preços.

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

Antes de começar

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

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

  2. Selecione ou crie um projeto do GCP.

    Acessar a página Gerenciar recursos

  3. Verifique se o faturamento foi ativado para o projeto.

    Saiba como ativar o faturamento

  4. O BigQuery é ativado automaticamente nos novos projetos. Para ativar o BigQuery em um projeto existente, acesse Ativar BigQuery API.

    Ativar a API

Público

Este é um tutorial avançado voltado para cientistas de dados.

Um cientista de dados usa o SQL padrão do BigQuery e o BigQuery ML para:

  • explorar, analisar e visualizar dados;
  • limpar conjuntos de dados para uso em modelos estatísticos;
  • criar modelos estatísticos e de ML;
  • criar, treinar, avaliar e aprimorar algoritmos e modelos de ML;
  • explorar dados em um armazenamento de dados, como o BigQuery.

Um cientista de dados usa diversas ferramentas, como:

  • a ferramenta de linha de comando do BigQuery, para consultar dados usando o SQL padrão;
  • software estatístico, como o RStudio e o Matlab;
  • Cloud Datalab (notebooks do Jupyter);
  • AI Platform;
  • linguagens de programação como Python e Java;
  • bibliotecas de ML, como MLlib e TensorFlow;
  • ferramentas de visualização, como o Cloud Datalab e o Google Data Studio.

Como este tutorial é destinado a cientistas de dados, muitos detalhes relacionados aos conceitos de ML não são explicados. Para consultar um tutorial introdutório mais detalhado que usa a IU da Web do BigQuery, acesse Primeiros passos com o BigQuery ML para analistas de dados.

Consulte nestes links as descrições dos conceitos aqui apresentados:

Etapa 1: configurar o Cloud Datalab

Neste tutorial, você configura o Cloud Datalab usando o Cloud Shell. Com o Cloud Datalab, você tem um ambiente de notebook baseado em Jupyter que poderá usar para trabalhar com o BigQuery ML e o BigQuery.

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

Para configurar o Cloud Datalab em uma VM do Compute Engine:

  1. Acesse o Console do GCP.

    Console do GCP

  2. Clique em Ativar o Cloud Shell.

    Ícone "Ativar Cloud Shell"

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

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

    datalab create mltutorial
    

    Após 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, digite 8081 e clique em Alterar e visualizar. Isso abre o Cloud 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 Notebook 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()
  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('bqml_tutorial'))
    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 de 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, selecione bqml_tutorial.
    3. No campo Código 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. Basta clicar no botão Salvar como tabela na janela de resultados.

  6. A conclusão da consulta levará alguns segundos. Após a execução da consulta, sua tabela cume_games será 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. Eles serão gravados na tabela bqml_tutorial.wide_games. Ao gerar esses dados, exclua um jogo para prever os resultados.

Nesta consulta, una 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, selecione bqml_tutorial.
    3. No campo Código 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. Basta clicar no botão Salvar como tabela na janela de resultados.

  6. A conclusão da consulta levará alguns segundos. Após a execução da consulta, sua tabela wide_games será 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. Na consulta SQL padrão, use uma instrução CREATE MODEL para criar o modelo. Com a instrução CREATE MODEL, você cria e treina seu modelo, mas neste tutorial o modelo é treinado separadamente.

Veja como executar a consulta CREATE MODEL para criar seu modelo:

  1. Mude para o notebook do Cloud 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 seguinte código.

    %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

Antes de testar esta amostra, siga as instruções de configuração do Python no Início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API do 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. Clique em Executar > Executar desta célula.

  2. A consulta leva alguns minutos para ser concluída. Depois que a primeira iteração for concluída, seu modelo (ncaa_model) será exibido no painel de navegação da IU da Web do BigQuery. Como uma instrução CREATE MODEL é usada na consulta, você não verá os resultados dela. A saída será uma string vazia.

Etapa 5: receber estatísticas de treinamento

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

Com um algoritmo de machine learning, você examina muitos exemplos para criar 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 geradas no momento em que a consulta CREATE MODEL foi executada, faça o seguinte:

  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

Antes de testar esta amostra, siga as instruções de configuração do Python no Início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API do BigQuery para Python.

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)
"""
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. Você executou uma regressão linear e, por isso, essa coluna é o erro quadrático médio (em inglês). 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 da sintaxe do BigQuery ML.

Etapa 6: avaliar o modelo

Após criar seu modelo, avalie o desempenho dele usando a função ML.EVALUATE.

Para executar a consulta ML.EVALUATE, que avalia o modelo, faça o seguinte:

  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

Antes de testar esta amostra, siga as instruções de configuração do Python no Início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API do 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. 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ê já avaliou seu modelo, a próxima etapa é usar a função ML.PREDICT para prever o total das tentativas de três pontos na final do NCAA 2018: Michigan versus 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

Antes de testar esta amostra, siga as instruções de configuração do Python no Início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API do 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. 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 cobranças dos recursos usados neste tutorial na conta do Google Cloud Platform:

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

Como excluir a VM do Cloud Datalab

A exclusão do projeto remove a VM do Cloud Datalab. Se não quiser que o projeto do Cloud Platform seja excluído, exclua a VM do Cloud Datalab.

Para excluir a VM do Cloud Datalab, faça o seguinte:

  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:

  1. No Console do GCP, acesse a página "Projetos".

    Acessar a página Projetos

  2. Na lista de projetos, selecione um e clique em Excluir projeto.
  3. Na caixa de diálogo, digite o código do projeto e clique em Encerrar para excluí-lo.

A seguir

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.