Before you begin
- Labs create a Google Cloud project and resources for a fixed time
- Labs have a time limit and no pause feature. If you restart it, you'll have to start from the beginning.
- Click Start lab to begin
Create a new dataset
/ 25
Identify a key field in your ecommerce dataset
/ 25
Pitfall: non-unique key
/ 25
Join pitfall solution
/ 25
O BigQuery é um banco de dados de análise NoOps, totalmente gerenciado e de baixo custo desenvolvido pelo Google. Com ele, você pode consultar muitos terabytes de dados sem ter que gerenciar uma infraestrutura ou precisar de um administrador de banco de dados. O BigQuery usa SQL e está disponível no modelo de pagamento por uso. Assim, você pode se concentrar na análise dos dados para encontrar informações relevantes.
A mesclagem de tabelas de dados gera insights significativos sobre o conjunto de dados. No entanto, alguns problemas comuns podem corromper os resultados ao mesclar dados. Este laboratório explica como evitá-los. Tipos de mesclagem:
Para mais informações, consulte a página sobre mesclagens.
Você usará um conjunto de dados de comércio eletrônico com milhões de registros do Google Analytics referentes à Google Merchandise Store e carregados no BigQuery. Com uma cópia do conjunto de dados, você analisará os campos e linhas disponíveis para extrair insights.
Se você quiser informações sobre sintaxe para acompanhar e atualizar as consultas, consulte Sintaxe de consultas SQL padrão.
Neste curso, você vai aprender a:
Leia estas instruções. Os laboratórios são cronometrados e não podem ser pausados. O timer é iniciado quando você clica em Começar o laboratório e mostra por quanto tempo os recursos do Google Cloud vão ficar disponíveis.
Este laboratório prático permite que você realize as atividades em um ambiente real de nuvem, não em uma simulação ou demonstração. Você vai receber novas credenciais temporárias para fazer login e acessar o Google Cloud durante o laboratório.
Confira os requisitos para concluir o laboratório:
Clique no botão Começar o laboratório. Se for preciso pagar, você verá um pop-up para selecionar a forma de pagamento. No painel Detalhes do laboratório à esquerda, você vai encontrar o seguinte:
Se você estiver usando o navegador Chrome, clique em Abrir console do Google Cloud ou clique com o botão direito do mouse e selecione Abrir link em uma janela anônima.
O laboratório ativa os recursos e depois abre a página Fazer login em outra guia.
Dica: coloque as guias em janelas separadas lado a lado.
Se necessário, copie o Nome de usuário abaixo e cole na caixa de diálogo Fazer login.
Você também encontra o Nome de usuário no painel Detalhes do laboratório.
Clique em Seguinte.
Copie a Senha abaixo e cole na caixa de diálogo de boas-vindas.
Você também encontra a Senha no painel Detalhes do laboratório.
Clique em Seguinte.
Acesse as próximas páginas:
Depois de alguns instantes, o console do Google Cloud será aberto nesta guia.
Você verá a caixa de mensagem Olá! Este é o BigQuery no Console do Cloud. Ela tem um link para o guia de início rápido e as notas de versão.
O console do BigQuery vai abrir.
No projeto do BigQuery, crie um novo conjunto de dados intitulado ecommerce
.
A caixa de diálogo Criar conjunto de dados será aberta.
Defina o ID do conjunto de dados como ecommerce
.
Deixe as demais opções nos valores padrão e clique em Criar conjunto de dados.
No painel esquerdo, você verá uma tabela ecommerce
relacionada no projeto.
Selecione Verificar meu progresso para conferir o objetivo.
Cenário: a equipe cria um novo conjunto de dados com os níveis de estoque de cada um dos produtos à venda no seu site de ecommerce. Conheça melhor os produtos do site e os campos que podem ser mesclados com outros conjuntos de dados.
O projeto com o novo conjunto de dados é chamado data-to-insights.
A caixa de mensagem "Este é o BigQuery" vai aparecer no console do Cloud.
Clique em Concluído.
Por padrão, o BigQuery não mostra conjuntos de dados públicos. Para abrir o projeto contendo esse tipo de dados, copie data-to-insights.
Clique em + Adicionar > Marcar um projeto com estrela por nome e copie o nome "data-to-insights".
Clique em Marcar com estrela.
O projeto data-to-insights
é listado na seção "Explorer".
Em seguida, conheça melhor os produtos e campos do site para criar consultas e analisar o conjunto de dados.
No painel esquerdo, acesse a seção "Recursos" e navegue até data-to-insights
> ecommerce
> all_sessions_raw
.
À direita, no "Editor de consultas", clique na guia Esquema para ver os campos e as informações sobre eles.
Examine os produtos e campos mais detalhadamente. Conheça melhor os produtos do site e os campos que podem ser mesclados com outros conjuntos de dados.
Nesta seção, você verá quantos nomes e SKUs de produtos estão no site e se algum desses campos é exclusivo.
Nos resultados de paginação no console, procure o número total de registros retornados.
Será que esses resultados indicam que existem muitas SKUs exclusivas de produtos? Uma das primeiras consultas que você vai executar como analista de dados é observar a exclusividade dos valores dos dados.
DISTINCT
:Agora, determine quais produtos têm mais de uma SKU e quais SKUs têm mais de um nome de produto.
Resultados:
O catálogo do site de ecommerce mostra que cada nome de produto pode ter diversas opções (como tamanho e cor), que são vendidas como SKUs separadas.
Então você percebeu que um produto pode ter 12 SKUs. E quanto a uma SKU? Pode pertencer a mais de um produto?
Na próxima seção, você vai entender por que esse relacionamento de dados de muitos para muitos é um problema.
Selecione Verificar meu progresso para conferir o objetivo.
No rastreamento de estoque, as SKUs foram feitas para identificar cada produto de maneira exclusiva. Para nós, serão a base da condição JOIN ao pesquisar informações de outras tabelas. Como vamos estudar em breve, a presença de uma chave não exclusiva pode causar sérios problemas aos dados.
'GGOEGPJC019099'
.Possível solução:
v2ProductName |
productSKU |
7" Dog Frisbee |
GGOEGPJC019099 |
7" Dog Frisbee |
GGOEGPJC019099 |
Google 7-inch Dog Flying Disc Blue |
GGOEGPJC019099 |
Nos resultados da consulta, parece que há três nomes diferentes para o mesmo produto. No exemplo, um dos nomes tem um caractere especial, e o outro é um pouco diferente dos demais:
Observe o impacto de mesclar um conjunto de dados com vários produtos da mesma SKU. Primeiro, confira o conjunto de dados do inventário de produtos (a tabela de produtos
) para saber se a SKU é exclusiva.
Agora você tem dois conjuntos de dados: um para o nível de estoque e outro para análises do nosso site. Use JOIN para relacionar o conjunto de dados do inventário com os nomes de produtos e SKUs do site para ter o nível de estoque associado a cada produto à venda no site.
Em seguida, expanda a consulta anterior para aplicar SUM (soma) ao estoque disponível por produto.
Ah, não! O resultado é 154 x 3 = 462 ou contagem tripla do inventário. Isso é chamado de correlação não intencional (um tópico que vamos revisar mais tarde).
Selecione Verificar meu progresso para conferir o objetivo.
Quais são as opções para resolver o dilema da contagem tripla? Primeiro, é preciso selecionar apenas SKUs distintas do site antes de mesclar com outros conjuntos de dados.
Pode haver mais de um nome de produto (como 7" Dog Frisbee) compartilhando uma única SKU.
Agora, em vez de ter uma linha para cada nome de produto, você terá apenas uma linha para cada SKU exclusiva.
Agora está tudo pronto para mesclar o conjunto de dados do inventário de produtos novamente.
Parece que 819 SKUs foram perdidas após a mesclagem dos conjuntos de dados. Investigue adicionando mais detalhes nos campos (uma coluna SKU de cada conjunto de dados):
Parece que as SKUs estão nos dois conjuntos de dados após a mesclagem dos 1.090 registros. Como encontrar os registros perdidos?
O tipo padrão de JOIN é o INNER JOIN que retorna registros somente se houver uma correspondência nas tabelas à esquerda e à direita que foram mescladas.
Possível solução:
Você usou LEFT JOIN para retornar todas as 1.909 SKUs originais do site nos resultados.
Quantas SKUs faltam no conjunto de inventário de produtos?
Possível solução:
Pergunta: quantos produtos faltam?
Resposta: 819 produtos estão faltando (SKU IS NULL) no conjunto de dados do inventário de produtos.
E o contrário? Há algum produto no conjunto de dados do inventário de produtos que não está no site?
Possível solução:
Resposta: sim. Faltam duas SKUs de produtos no conjunto de dados do site.
Em seguida, adicione mais campos do conjunto de dados do inventário de produtos para saber mais detalhes.
Por que os produtos abaixo estão faltando no conjunto de dados do site de ecommerce?
website_SKU |
SKU |
name |
orderedQuantity |
stockLevel |
restockingLeadTime |
sentimentScore |
sentimentMagnitude |
null |
GGOBJGOWUSG69402 |
USB wired soundbar - in store only |
10 |
15 |
2 |
1.0 |
1.0 |
null |
GGADFBSBKS42347 |
PC gaming speakers |
0 |
100 |
1 |
null |
null |
Possíveis respostas:
Por que os novos produtos não aparecem no conjunto de dados do site?
E se você quisesse uma consulta que listasse todos os produtos que não constam no site ou no inventário?
Possível solução:
Você tem 819 + 2 = 821 SKUs.
LEFT JOIN + RIGHT JOIN = FULL JOIN de produtos, que retorna todos os registros de ambas as tabelas, sem depender das chaves de mesclagem correspondentes. Depois, basta filtrar as instâncias sem correspondência em um dos lados.
Não saber a relação entre as chaves das tabelas de dados (1:1, 1:N, N:N) pode gerar resultados inesperados, além de prejudicar significativamente o desempenho da consulta.
O último tipo de mesclagem é CROSS JOIN.
Crie uma nova tabela com a porcentagem de desconto que você quer aplicar em todo o site nos produtos da categoria "Promoção".
No painel à esquerda, agora está listado o site_wide_promotion
, na seção "Recursos" do projeto e conjunto de dados.
Acompanhe o impacto da adição não intencional de mais de um registro na tabela de descontos.
Agora, vamos conferir os valores de dados na tabela de promoção.
Quantos registros foram retornados?
Resposta: 3
O que acontecerá se você aplicar o desconto novamente a todos os 82 produtos em promoção?
Quantos produtos foram retornados?
Resposta: em vez de 82, foram retornados 246. São muito mais registros do que havia na tabela inicial.
Para investigar a causa, analise uma SKU de produto.
Qual foi o impacto da CROSS JOIN?
Resposta: como existem três códigos de desconto para correlacionar, o conjunto de dados original é multiplicado por 3.
A solução é conhecer as relações entre os dados antes da mesclagem e não presumir que as chaves são exclusivas.
Selecione Verificar meu progresso para conferir o objetivo.
Você concluiu este laboratório e lidou com alguns problemas sérios da mesclagem de SQL ao identificar registros duplicados e reconhecer quando usar cada tipo de JOIN. Bom trabalho!
Esses treinamentos ajudam você a aproveitar as tecnologias do Google Cloud ao máximo. Nossas aulas incluem habilidades técnicas e práticas recomendadas para ajudar você a alcançar rapidamente o nível esperado e continuar sua jornada de aprendizado. Oferecemos treinamentos que vão do nível básico ao avançado, com opções de aulas virtuais, sob demanda e por meio de transmissões ao vivo para que você possa encaixá-las na correria do seu dia a dia. As certificações validam sua experiência e comprovam suas habilidades com as tecnologias do Google Cloud.
Manual atualizado em 3 de fevereiro de 2024
Laboratório testado em 20 de setembro de 2023
Copyright 2025 Google LLC. Todos os direitos reservados. Google e o logotipo do Google são marcas registradas da Google LLC. Todos os outros nomes de produtos e empresas podem ser marcas registradas das respectivas empresas a que estão associados.