Crie um modelo de aprendizagem automática no BigQuery ML através do SQL
Este tutorial mostra como criar um modelo de regressão logística através de consultas SQL do BigQuery ML.
O BigQuery ML permite-lhe criar e preparar modelos de aprendizagem automática no BigQuery através de consultas SQL. Isto ajuda a tornar a aprendizagem automática mais acessível, permitindo-lhe usar ferramentas familiares, como o editor de SQL do BigQuery, e também aumenta a velocidade de desenvolvimento, eliminando a necessidade de mover dados para um ambiente de aprendizagem automática separado.
Neste tutorial, vai usar o conjunto de dados de exemplo do Google Analytics para o BigQuery para criar um modelo que preveja se um visitante do Website vai fazer uma transação. Para ver informações sobre o esquema do conjunto de dados do Analytics, consulte o artigo Esquema do BigQuery Export no Centro de Ajuda do Analytics.
Para saber como criar modelos através da Trusted Cloud interface do utilizador da consola, consulte o artigo Trabalhe com modelos através de uma IU. (Pré-visualizar)
Objetivos
Este tutorial mostra como realizar as seguintes tarefas:
- Usando a declaração
CREATE MODEL
para criar um modelo de regressão logística binária. - Usando a função
ML.EVALUATE
para avaliar o modelo. - Usar a
ML.PREDICT
função para fazer previsões com o modelo.
Custos
Este tutorial usa componentes faturáveis do Trusted Cloud by S3NS, incluindo o seguinte:
- BigQuery
- BigQuery ML
Para mais informações sobre os custos do BigQuery, consulte a página de preços do BigQuery.
Para mais informações sobre os custos do BigQuery ML, consulte os preços do BigQuery ML.
Funções necessárias
Para criar um modelo e executar a inferência, tem de lhe ser concedidas as seguintes funções:
- Editor de dados do BigQuery (
roles/bigquery.dataEditor
) - Utilizador do BigQuery (
roles/bigquery.user
)
- Editor de dados do BigQuery (
Antes de começar
-
In the Trusted Cloud console, on the project selector page, select or create a Trusted Cloud project.
Roles required to select or create a project
- Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
-
Create a project: To create a project, you need the Project Creator
(
roles/resourcemanager.projectCreator
), which contains theresourcemanager.projects.create
permission. Learn how to grant roles.
-
Verify that billing is enabled for your Trusted Cloud project.
-
Make sure that you have the following role or roles on the project: BigQuery Data Editor, BigQuery Job User, Service Usage Admin
Check for the roles
-
In the Trusted Cloud console, go to the IAM page.
Go to IAM - Select the project.
-
In the Principal column, find all rows that identify you or a group that you're included in. To learn which groups you're included in, contact your administrator.
- For all rows that specify or include you, check the Role column to see whether the list of roles includes the required roles.
Grant the roles
-
In the Trusted Cloud console, go to the IAM page.
Aceder ao IAM - Selecione o projeto.
- Clique em Conceder acesso.
-
No campo Novos responsáveis, introduza o identificador do utilizador. Normalmente, este é o identificador de um utilizador num conjunto de identidades da força de trabalho. Para ver detalhes, consulte Represente utilizadores do pool de força de trabalho em políticas de IAM ou contacte o seu administrador.
- Na lista Selecionar uma função, selecione uma função.
- Para conceder funções adicionais, clique em Adicionar outra função e adicione cada função adicional.
- Clique em Guardar.
- O BigQuery é ativado automaticamente em novos projetos. Para ativar o BigQuery num projeto pré-existente, aceda a
Enable the BigQuery API.
Roles required to enable APIs
To enable APIs, you need the Service Usage Admin IAM role (
roles/serviceusage.serviceUsageAdmin
), which contains theserviceusage.services.enable
permission. Learn how to grant roles. -
Crie um conjunto de dados
Crie um conjunto de dados do BigQuery para armazenar o seu modelo de ML.
Consola
Na Trusted Cloud consola, aceda à página BigQuery.
No painel Explorador, clique no nome do projeto.
Clique em
Ver ações > Criar conjunto de dadosNa página Criar conjunto de dados, faça o seguinte:
Para o ID do conjunto de dados, introduza
bqml_tutorial
.Em Tipo de localização, selecione Várias regiões e, de seguida, selecione EUA (várias regiões nos Estados Unidos).
Deixe as restantes predefinições como estão e clique em Criar conjunto de dados.
bq
Para criar um novo conjunto de dados, use o comando
bq mk
com a flag --location
. Para uma lista completa de parâmetros possíveis, consulte a referência do comando bq mk --dataset
.
Crie um conjunto de dados com o nome
bqml_tutorial
com a localização dos dados definida comoUS
e uma descrição deBigQuery ML tutorial dataset
:bq --location=US mk -d \ --description "BigQuery ML tutorial dataset." \ bqml_tutorial
Em vez de usar a flag
--dataset
, o comando usa o atalho-d
. Se omitir-d
e--dataset
, o comando cria um conjunto de dados por predefinição.Confirme que o conjunto de dados foi criado:
bq ls
API
Chame o método datasets.insert
com um recurso de conjunto de dados definido.
{ "datasetReference": { "datasetId": "bqml_tutorial" } }
DataFrames do BigQuery
Antes de experimentar este exemplo, siga as instruções de configuração dos DataFrames do BigQuery no início rápido do BigQuery com os DataFrames do BigQuery. Para mais informações, consulte a documentação de referência do BigQuery DataFrames.
Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure o ADC para um ambiente de desenvolvimento local.
Crie um modelo de regressão logística
Crie um modelo de regressão logística com o conjunto de dados de exemplo do Analytics para o BigQuery.
SQL
Na Trusted Cloud consola, aceda à página BigQuery.
No editor de consultas, execute a seguinte declaração:
CREATE OR REPLACE MODEL `bqml_tutorial.sample_model` OPTIONS(model_type='logistic_reg') AS SELECT IF(totals.transactions IS NULL, 0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'
A consulta demora vários minutos a ser concluída. Após a conclusão da primeira iteração, o seu modelo (
sample_model
) aparece no painel de navegação. Uma vez que a consulta usa uma declaraçãoCREATE MODEL
para criar um modelo, não vê os resultados da consulta.
Detalhes da consulta
A declaração CREATE MODEL
cria o modelo e, em seguida, prepara o modelo
com os dados obtidos pela declaração SELECT
da sua consulta.
A cláusula OPTIONS(model_type='logistic_reg')
cria um modelo de regressão
logística. Um modelo de regressão logística divide os dados de entrada em duas classes e, em seguida, estima a probabilidade de os dados estarem numa das classes. O que está a tentar detetar, como se um email é spam, é representado por 1 e os outros valores são representados por 0. A probabilidade de um determinado valor pertencer à classe que está a tentar detetar é indicada por um valor entre 0 e 1.
Por exemplo, se um email receber uma estimativa de probabilidade de 0,9, existe uma probabilidade de 90% de o email ser spam.
A declaração SELECT
desta consulta obtém as seguintes colunas que são usadas pelo modelo para prever a probabilidade de um cliente concluir uma transação:
totals.transactions
: o número total de transações de comércio eletrónico na sessão. Se o número de transações forNULL
, o valor na colunalabel
é definido como0
. Caso contrário, é definido como1
. Estes valores representam os resultados possíveis. A criação de um alias com o nomelabel
é uma alternativa à definição da opçãoinput_label_cols=
na declaraçãoCREATE MODEL
.device.operatingSystem
: o sistema operativo do dispositivo do visitante.device.isMobile
— Indica se o dispositivo do visitante é um dispositivo móvel.geoNetwork.country
: o país de origem das sessões, com base no endereço IP.totals.pageviews
: o número total de visualizações de páginas na sessão.
A cláusula FROM
faz com que a consulta prepare o modelo usando as tabelas de amostra bigquery-public-data.google_analytics_sample.ga_sessions
.
Estas tabelas são divididas por data, pelo que as agrega usando um caráter universal no nome da tabela: google_analytics_sample.ga_sessions_*
.
A cláusula WHERE
— _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'
— limita o número de tabelas analisadas pela consulta. O intervalo de datas
analisado é de 1 de agosto de 2016 a 30 de junho de 2017.
DataFrames do BigQuery
Antes de experimentar este exemplo, siga as instruções de configuração dos DataFrames do BigQuery no início rápido do BigQuery com os DataFrames do BigQuery. Para mais informações, consulte a documentação de referência do BigQuery DataFrames.
Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure o ADC para um ambiente de desenvolvimento local.
Veja as estatísticas de perda do modelo
A aprendizagem automática consiste em criar um modelo que possa usar dados para fazer uma previsão. O modelo é essencialmente uma função que recebe entradas e aplica cálculos às entradas para produzir uma saída, ou seja, uma previsão.
Os algoritmos de aprendizagem automática funcionam através da análise de vários exemplos em que a previsão já é conhecida (como os dados do histórico de compras dos utilizadores) e do ajuste iterativo de vários pesos no modelo para que as previsões do modelo correspondam aos valores reais. O modelo faz isto minimizando o quão errado está através de uma métrica denominada perda.
A expetativa é que, para cada iteração, a perda diminua, idealmente, para zero. Uma perda de zero significa que o modelo é 100% preciso.
Ao preparar o modelo, o BigQuery ML divide automaticamente os dados de entrada em conjuntos de preparação e avaliação, para evitar o sobreajuste do modelo. Isto é necessário para que o algoritmo de preparação não se ajuste demasiado aos dados de preparação, de modo que não consiga generalizar para novos exemplos.
Use a Trusted Cloud consola para ver como a perda do modelo muda ao longo das iterações de preparação do modelo:
Na Trusted Cloud consola, aceda à página BigQuery.
No painel Explorador, expanda bqml_tutorial > Models e, de seguida, clique em sample_model.
Clique no separador Formação e consulte o gráfico Perda. O gráfico de Perda mostra a alteração na métrica de perda ao longo das iterações no conjunto de dados de treino. Se passar o cursor do rato sobre o gráfico, pode ver que existem linhas para Perda de treino e Perda de avaliação. Uma vez que fez uma regressão logística, o valor da perda de formação é calculado como perda logarítmica, usando os dados de formação. A perda de avaliação é a perda logarítmica calculada nos dados de avaliação. Ambos os tipos de perda representam valores de perda médios, calculados para todos os exemplos nos respetivos conjuntos de dados para cada iteração.
Também pode ver os resultados da preparação do modelo através da
função ML.TRAINING_INFO
.
Avalie o modelo
Avalie o desempenho do modelo através da função ML.EVALUATE
. A função ML.EVALUATE
avalia os valores previstos gerados pelo modelo em comparação com os dados reais. Para calcular métricas específicas da regressão logística, pode usar a função SQL ML.ROC_CURVE
ou a função bigframes.ml.metrics.roc_curve
BigQuery DataFrames.
Neste tutorial, está a usar um modelo de classificação binária que deteta transações. Os valores na coluna label
são as duas classes geradas pelo modelo: 0
(sem transações) e 1
(transação feita).
SQL
Na Trusted Cloud consola, aceda à página BigQuery.
No editor de consultas, execute a seguinte declaração:
SELECT * FROM ML.EVALUATE(MODEL `bqml_tutorial.sample_model`, ( SELECT IF(totals.transactions IS NULL, 0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
Os resultados devem ter o seguinte aspeto:
+--------------------+---------------------+---------------------+---------------------+---------------------+--------------------+ | precision | recall | accuracy | f1_score | log_loss | roc_auc | +--------------------+---------------------+---------------------+---------------------+---------------------+--------------------+ | 0.468503937007874 | 0.11080074487895716 | 0.98534315834767638 | 0.17921686746987953 | 0.04624221101176898 | 0.98174125874125873 | +--------------------+---------------------+---------------------+---------------------+---------------------+--------------------+
Como fez uma regressão logística, os resultados incluem as seguintes colunas:
precision
: uma métrica para modelos de classificação. A precisão identifica a frequência com que um modelo estava correto ao prever a classe positiva.recall
: uma métrica para modelos de classificação que responde à seguinte pergunta: de todas as etiquetas positivas possíveis, quantas o modelo identificou corretamente?accuracy
: A precisão é a fração de previsões em que um modelo de classificação acertou.f1_score
: uma medida da precisão do modelo. A pontuação F1 é a média harmónica da precisão e da revocação. O melhor valor de uma pontuação F1 é 1. O pior valor é 0.log_loss
: a função de perda usada numa regressão logística. Esta é a medida da distância entre as previsões do modelo e as etiquetas corretas.roc_auc
: a área abaixo da curva ROC. Esta é a probabilidade de um classificador ter mais confiança de que um exemplo positivo escolhido aleatoriamente é realmente positivo do que um exemplo negativo escolhido aleatoriamente é positivo. Para mais informações, consulte a secção Classificação no Curso intensivo de aprendizagem automática.
Detalhes da consulta
A declaração SELECT
inicial obtém as colunas do seu modelo.
A cláusula FROM
usa a função ML.EVALUATE
no seu modelo.
A declaração SELECT
aninhada e a cláusula FROM
são iguais às da consulta CREATE MODEL
.
A cláusula WHERE
— _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
— limita o número de tabelas analisadas pela consulta. O intervalo de datas
analisado é de 1 de julho de 2017 a 1 de agosto de 2017. Estes são os dados que está a usar para avaliar o desempenho preditivo do modelo. Foi recolhido no mês imediatamente a seguir ao período abrangido pelos dados de preparação.
DataFrames do BigQuery
Antes de experimentar este exemplo, siga as instruções de configuração dos DataFrames do BigQuery no início rápido do BigQuery com os DataFrames do BigQuery. Para mais informações, consulte a documentação de referência do BigQuery DataFrames.
Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure o ADC para um ambiente de desenvolvimento local.
Use o modelo para prever resultados
Use o modelo para prever o número de transações feitas por visitantes do Website de cada país.
SQL
Na Trusted Cloud consola, aceda à página BigQuery.
No editor de consultas, execute a seguinte declaração:
SELECT country, SUM(predicted_label) as total_predicted_purchases FROM ML.PREDICT(MODEL `bqml_tutorial.sample_model`, ( SELECT IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(geoNetwork.country, "") AS country FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801')) GROUP BY country ORDER BY total_predicted_purchases DESC LIMIT 10
Os resultados devem ter o seguinte aspeto:
+----------------+---------------------------+ | country | total_predicted_purchases | +----------------+---------------------------+ | United States | 220 | | Taiwan | 8 | | Canada | 7 | | India | 2 | | Turkey | 2 | | Japan | 2 | | Italy | 1 | | Brazil | 1 | | Singapore | 1 | | Australia | 1 | +----------------+---------------------------+
Detalhes da consulta
A declaração SELECT
inicial obtém a coluna country
e soma a coluna predicted_label
. A coluna predicted_label
é gerada pela função ML.PREDICT
. Quando usa a função ML.PREDICT
, o nome da coluna de saída para o modelo é predicted_<label_column_name>
. Para modelos de regressão linear, predicted_label
é o valor estimado de label
.
Para modelos de regressão logística, predicted_label
é a etiqueta que melhor descreve o valor de dados de entrada fornecido, 0
ou 1
.
A função ML.PREDICT
é usada para prever resultados com o seu modelo.
A declaração SELECT
aninhada e a cláusula FROM
são iguais às da consulta CREATE MODEL
.
A cláusula WHERE
— _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
— limita o número de tabelas analisadas pela consulta. O intervalo de datas
analisado é de 1 de julho de 2017 a 1 de agosto de 2017. Estes são os dados para os quais está a fazer previsões. Foram recolhidos no mês imediatamente a seguir ao período abrangido pelos dados de preparação.
As cláusulas GROUP BY
e ORDER BY
agrupam os resultados por país e ordenam-nos pela soma das compras previstas por ordem descendente.
A cláusula LIMIT
é usada aqui para apresentar apenas os 10 principais resultados.
DataFrames do BigQuery
Antes de experimentar este exemplo, siga as instruções de configuração dos DataFrames do BigQuery no início rápido do BigQuery com os DataFrames do BigQuery. Para mais informações, consulte a documentação de referência do BigQuery DataFrames.
Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure o ADC para um ambiente de desenvolvimento local.
Preveja as compras por utilizador
Prever o número de transações que cada visitante do Website vai fazer.
SQL
Esta consulta é idêntica à consulta na secção anterior, exceto pela cláusula GROUP BY
. Aqui, a cláusula GROUP BY
— GROUP BY fullVisitorId
— é usada para agrupar os resultados por ID do visitante.
Na Trusted Cloud consola, aceda à página BigQuery.
No editor de consultas, execute a seguinte declaração:
SELECT fullVisitorId, SUM(predicted_label) as total_predicted_purchases FROM ML.PREDICT(MODEL `bqml_tutorial.sample_model`, ( SELECT IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(geoNetwork.country, "") AS country, fullVisitorId FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801')) GROUP BY fullVisitorId ORDER BY total_predicted_purchases DESC LIMIT 10
Os resultados devem ter o seguinte aspeto:
+---------------------+---------------------------+ | fullVisitorId | total_predicted_purchases | +---------------------+---------------------------+ | 9417857471295131045 | 4 | | 112288330928895942 | 2 | | 2158257269735455737 | 2 | | 489038402765684003 | 2 | | 057693500927581077 | 2 | | 2969418676126258798 | 2 | | 5073919761051630191 | 2 | | 7420300501523012460 | 2 | | 0456807427403774085 | 2 | | 2105122376016897629 | 2 | +---------------------+---------------------------+
DataFrames do BigQuery
Antes de experimentar este exemplo, siga as instruções de configuração dos DataFrames do BigQuery no início rápido do BigQuery com os DataFrames do BigQuery. Para mais informações, consulte a documentação de referência do BigQuery DataFrames.
Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure o ADC para um ambiente de desenvolvimento local.
Limpar
Para evitar incorrer em cobranças na sua Trusted Cloud conta pelos recursos usados nesta página, siga estes passos.
Pode eliminar o projeto que criou ou manter o projeto e eliminar o conjunto de dados.
Elimine o conjunto de dados
A eliminação do projeto remove todos os conjuntos de dados e todas as tabelas no projeto. Se preferir reutilizar o projeto, pode eliminar o conjunto de dados que criou neste tutorial:
Na Trusted Cloud consola, aceda à página BigQuery.
No painel Explorador, selecione o conjunto de dados bqml_tutorial que criou.
Clique em > Eliminar.
AçõesNa caixa de diálogo Eliminar conjunto de dados, confirme o comando de eliminação escrevendo
delete
.Clique em Eliminar.
Elimine o projeto
Para eliminar o projeto:
- In the Trusted Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
O que se segue?
- Para saber mais sobre a aprendizagem automática, consulte o Machine Learning Crash Course.
- Para uma vista geral do BigQuery ML, consulte o artigo Introdução ao BigQuery ML.
- Para saber mais acerca da Trusted Cloud consola, consulte o artigo Usar a Trusted Cloud consola.