GSP413

Informações gerais

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.

Você usará um conjunto de dados de e-commerce com milhões de registros do Google Analytics referentes à Google Merchandise Store (em inglês). Será necessário conferir os campos e as linhas disponíveis para receber insights.

Este laboratório explica como criar novas tabelas de relatórios usando as funções JOIN e UNION do SQL.

Situação: a equipe de marketing enviou para você e a equipe de ciência de dados todas as avaliações de produtos no seu site de e-commerce. Vocês vão trabalhar juntos para criar um data warehouse no BigQuery com dados de três origens:

• Dados de e-commerce do site
• Níveis de estoque de inventário de produtos e tempos de lead
• Análise de sentimento das avaliações dos produtos

Atividades

Neste laboratório, você vai aprender a executar as seguintes tarefas:

• Examinar novos dados de e-commerce na análise de sentimento.
• Mesclar conjuntos de dados e criar novas tabelas.
• Anexar dados históricos com a função UNION e caracteres curinga de tabela.

Configuração e requisitos

Antes de clicar no botão Start Lab

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:

• Acesso a um navegador de Internet padrão (recomendamos o Chrome).

Observação: para executar este laboratório, use o modo de navegação anônima ou uma janela anônima do navegador. Isso evita conflitos entre sua conta pessoal e a conta de estudante, o que poderia causar cobranças extras na sua conta pessoal.

• Tempo para concluir o laboratório---não se esqueça: depois de começar, não será possível pausar o laboratório.

Observação: não use seu projeto ou conta do Google Cloud neste laboratório para evitar cobranças extras na sua conta.

Como iniciar seu laboratório e fazer login no console do Google Cloud

1. 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:

   • O botão Abrir console do Google Cloud
   • O tempo restante
   • As credenciais temporárias que você vai usar neste laboratório
   • Outras informações, se forem necessárias

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

   Observação: se aparecer a caixa de diálogo Escolher uma conta, clique em Usar outra conta.

3. Se necessário, copie o Nome de usuário abaixo e cole na caixa de diálogo Fazer login.

   {{{user_0.username | "Nome de usuário"}}}

   Você também encontra o Nome de usuário no painel Detalhes do laboratório.

4. Clique em Seguinte.

5. Copie a Senha abaixo e cole na caixa de diálogo de boas-vindas.

   {{{user_0.password | "Senha"}}}

   Você também encontra a Senha no painel Detalhes do laboratório.

6. Clique em Seguinte.

   Importante: você precisa usar as credenciais fornecidas no laboratório, e não as da sua conta do Google Cloud. Observação: se você usar sua própria conta do Google Cloud neste laboratório, é possível que receba cobranças adicionais.

7. Acesse as próximas páginas:

   • Aceite os Termos e Condições.
   • Não adicione opções de recuperação nem autenticação de dois fatores (porque essa é uma conta temporária).
   • Não se inscreva em testes gratuitos.

Depois de alguns instantes, o console do Google Cloud será aberto nesta guia.

Observação: clique em Menu de navegação no canto superior esquerdo para acessar uma lista de produtos e serviços do Google Cloud.

Abrir o console do BigQuery

1. No Console do Google Cloud, selecione o menu de navegação > BigQuery:

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.

2. Clique em OK.

O console do BigQuery vai abrir.

Tarefa 1: criar um novo conjunto de dados para armazenar as tabelas

Para começar, crie um novo conjunto chamado e-commerce no BigQuery para armazenar suas tabelas.

1. No painel à esquerda, selecione o nome do seu projeto do BigQuery (qwiklabs-gcp-xxxx).

2. Clique nos três pontos ao lado do nome do projeto e selecione Criar conjunto de dados.

A caixa de diálogo Criar conjunto de dados será aberta.

3. Defina o ID do conjunto de dados como ecommerce e não altere as outras opções.

4. Clique em Criar conjunto de dados.

Clique em Verificar meu progresso para conferir o andamento do objetivo. Crie um conjunto de dados para armazenar as tabelas

Tarefa 2: examinar o conjunto de dados de sentimento dos produtos

A equipe de ciência de dados passou todas as avaliações de produtos pela API e calculou a média da pontuação de sentimento e magnitude para cada um deles.

O projeto com o conjunto de dados da equipe de marketing é o data-to-insights. O BigQuery não exibe por padrão os próprios conjuntos de dados públicos. As consultas neste laboratório usarão o conjunto de dados data-to-insights, mesmo que ele não esteja aparecendo para você.

1. Primeiro, crie uma cópia da tabela feita pela equipe de ciência de dados para você poder ler as informações:

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` Observação: isso é apenas para você conferir as informações. As consultas neste laboratório usarão o projeto data-to-insights.

2. Clique no conjunto de dados e-commerce para mostrar a tabela products.

Examinar os dados usando as guias "Visualização" e "Esquema"

1. Acesse o conjunto de dados > e-commerce > produtos e clique na guia Visualização para ver os dados.

2. Clique na guia Esquema.

Crie uma consulta que mostre os 5 principais produtos que receberam avaliações com o sentimento mais positivo

1. No Editor de consultas, escreva sua consulta SQL.

Possível solução:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. Revise sua consulta para mostrar os cinco principais produtos que receberam avaliações com o sentimento mais negativo e filtre os valores NULL.

Possível solução:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

Qual é o produto avaliado com o sentimento mais negativo?

Clique em Verificar meu progresso para conferir o andamento do objetivo. Examine o conjunto de dados de sentimento dos produtos

Tarefa 3: mesclar conjuntos de dados para extrair insights

Situação: é o primeiro dia do mês, e a equipe de inventário informou que o campo orderedQuantity no conjunto de dados está desatualizado. Ela precisa da sua ajuda para consultar o total de vendas por produto em 01/08/2017 e cruzar informações com os níveis atuais em estoque para descobrir quais produtos precisam de reposição primeiro.

Calcule o volume de vendas diário por productSKU

1. Crie uma nova tabela no seu conjunto de dados ecommerce com as seguintes características:

• Nome sales_by_sku_20170801
• Origem dos dados data-to-insights.ecommerce.all_sessions_raw
• Incluir apenas resultados distintos
• Retornar productSKU
• Retornar a quantidade total encomendada (productQuantity). Dica: use SUM() com uma condição IFNULL.
• Filtrar apenas as vendas feitas em 20170801
• Usar ORDER BY para classificar as SKUs com o maior número de pedidos primeiro

Possível solução:

# pull what sold on 08/01/2017 CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 skus sold

2. Clique na tabela sales_by_sku e selecione a guia Visualizar.

Quantas SKUs de produtos distintos foram vendidas?

Resposta: 462

Em seguida, aprimore seus dados de vendas com informações de inventário de produtos mesclando os dois conjuntos de dados.

Mescle dados de vendas e de inventário

1. Usando uma operação JOIN, aprimore os dados de e-commerce do site com estes campos do conjunto de dados do inventário de produtos:

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

2. Complete a consulta parcialmente escrita:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

Possível solução:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. Modifique a consulta que você escreveu para realizar as seguintes ações:

• Incluir um campo calculado de (total_ordered / stockLevel) com o nome "ratio" Dica: use SAFE_DIVIDE(field1,field2) para evitar erros de divisão por 0 quando o estoque acabar.
• Filtrar os resultados para incluir apenas produtos que já atingiram 50% ou mais do inventário no início do mês.

Possível solução:

# calculate ratio and filter SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # gone through more than 50% of inventory for the month WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

Clique em Verificar meu progresso para conferir o andamento do objetivo. Mescle conjuntos de dados para extrair insights

Tarefa 4: anexar outros registros

Sua equipe internacional já fez vendas na loja em 02/08/2017, e você quer registrá-las nas tabelas de vendas diárias.

Crie uma nova tabela vazia para armazenar vendas por productSKU para 02/08/2017

1. Para o esquema, especifique os seguintes campos:

• O nome da tabela como ecommerce.sales_by_sku_20170802
• productSKU STRING
• total_ordered como um campo INT64

Possível solução:

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. Verifique se agora você tem duas tabelas de vendas compartilhadas por data. Para isso, use o menu suspenso ao lado de Sales_by_sku nos resultados da tabela ou atualize a página para conferir as informações no menu à esquerda:

3. Insira o registro de vendas que você recebeu da equipe de vendas:

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. Acesse a tabela para conferir se o registro aparece. Selecione o nome da tabela para exibir os resultados.

Anexe dados históricos

Há várias maneiras de anexar dados que têm o mesmo esquema. Duas formas comuns são usar UNIONs e caracteres curinga de tabela.

• Union é um operador SQL que anexa linhas de conjuntos de resultados diferentes.
• Caracteres curinga de tabela permitem consultar várias tabelas usando instruções SQL concisas. Tabelas curinga estão disponíveis apenas no SQL padrão.

1. Escreva uma consulta UNION que resultará em todos os registros das duas tabelas abaixo:

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 Observação: a diferença entre UNION e UNION ALL é que UNION não incluirá registros duplicados.

Qual erro é comum com o uso de várias tabelas de vendas diárias? Você precisará escrever muitas instruções UNION em sequência.

É melhor usar o caractere curinga de tabela e _TABLE_SUFFIX para filtrar as informações.

2. Escreva uma consulta que use o caractere curinga de tabela (*) para selecionar todos os registros de ecommerce.sales_by_sku_ do ano de 2017.

Possível solução:

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. Modifique a consulta anterior para adicionar um filtro e limitar os resultados à data de 02/08/2017.

Possível solução:

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' Observação: outra opção é criar uma tabela particionada que possa ingerir automaticamente dados de vendas diárias na partição correta.

Clique em Verificar meu progresso para conferir o andamento do objetivo. Anexe outros registros

Parabéns!

Você analisou exemplos de dados de e-commerce criando tabelas de relatórios e manipulando visualizações com funções SQL JOIN e UNION.

Próximas etapas / Saiba mais

• Você tem uma conta do Google Analytics e quer consultar seus próprios conjuntos de dados no BigQuery? Siga este guia de exportação.
• Se quiser praticar mais a sintaxe SQL de mesclagem com JOIN, consulte as seções sobre JOIN na documentação do BigQuery.
• Teste o Google Pesquisa de Datasets.
• Confira outros laboratórios:
  • Explorar dados da NCAA com o BigQuery
  • Cloud Composer: como copiar tabelas do BigQuery em diferentes locais

Treinamento e certificação do Google Cloud

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 31 de outubro de 2023

Copyright 2024 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.