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 utilização. 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.

Pré-requisitos

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

• Conferir 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

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ê verá o seguinte:

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

2. Clique em Abrir Console do Google. O laboratório ativa recursos e depois abre outra guia com a página Fazer login.

   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. Caso seja preciso, copie o Nome de usuário no painel Detalhes do laboratório e cole esse nome na caixa de diálogo Fazer login. Clique em Avançar.

4. Copie a Senha no painel Detalhes do laboratório e a cole na caixa de diálogo Olá. Clique em Avançar.

   Importante: você precisa usar as credenciais do painel à esquerda. Não use suas credenciais do Google Cloud Ensina. Observação: se você usar sua própria conta do Google Cloud neste laboratório, é possível que receba cobranças adicionais.

5. 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 GCP vai ser aberto nesta guia.

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

Tarefa 1: o console do BigQuery

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 2: crie um novo conjunto de dados para armazenar as tabelas

Primeiro, crie um novo conjunto de dados chamado e-commerce no BigQuery.

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

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 trabalham 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 do produto

Neste laboratório, você vai analisar um novo conjunto de dados com base nas avaliações do produto.

Tarefa 3: projeto do BigQuery

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 se ele não estiver aparecendo para você.

Tarefa 4: conheça o conjunto de dados de sentimento do produto

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.

1. Primeiro, crie uma cópia da tabela criada 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 ecommerce para exibir a guia products.

Tarefa 5: analise os dados

1. Acesse o conjunto de dados data-to-insights > ecommerce > products e clique na guia Visualizar 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. Conheça o conjunto de dados de sentimento do produto

Tarefa 6: mescle 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 7: anexe 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 curingas de tabela.

• Union é um operador SQL que anexa linhas de conjuntos de resultados diferentes.
• Curingas 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 e _TABLE_SUFFIX para filtrar as informações.

2. Escreva uma consulta que use o caractere curinga (*) da 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.

Termine a Quest

Este laboratório autoguiado faz parte da Quest BigQuery for Data Warehousing. Uma Quest é uma série de laboratórios relacionados que formam um programa de aprendizado. Ao concluir uma Quest, você ganha um selo como reconhecimento da sua conquista. É possível publicar os selos e incluir um link para eles no seu currículo on-line ou nas redes sociais. Inscreva-se nesta Quest ou em outra que tenha este laboratório para receber os créditos de conclusão na hora. Confira o catálogo do Google Cloud Ensina para acessar todas as Quests disponíveis.

Comece o próximo laboratório

Continue a Quest com o laboratório Como trabalhar com dados do tipo JSON, matriz e struct no BigQuery ou confira as sugestões abaixo:

• Explorar dados da NCAA com o BigQuery
• Cloud Composer: como copiar tabelas do BigQuery em diferentes locais

Próximas etapas / Saiba mais

• Você já tem uma conta do Google Analytics e quer consultar seus próprios conjuntos de dados no BigQuery? Siga as instruções deste guia de exportação.
• Se quiser praticar mais a sintaxe SQL de mesclagem com JOIN, leia as seções sobre JOIN na documentação do BigQuery.
• Teste o Google Pesquisa de Datasets.

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

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.