IMPORTANTE:

Conclua este laboratório prático usando um computador ou notebook.

Só 5 tentativas são permitidas por laboratório.

É comum não acertar todas as questões na primeira tentativa e precisar refazer uma tarefa. Isso faz parte do processo de aprendizado.

Depois que o laboratório é iniciado, não é possível pausar o tempo. Depois de 1h30, o laboratório será finalizado, e você vai precisar recomeçar.

Para saber mais, confira as Dicas técnicas do laboratório.

Visão geral da atividade

Um pipeline de dados é uma série de processos que transportam dados de diferentes fontes para um destino com a finalidade de armazenamento e análise.

Eles aumentam a performance e a eficiência da transformação de dados, automatizando o fluxo das informações e simplificando a forma como os dados são administrados.

Usar pipelines de SQL ajuda a melhorar a performance, eliminando etapas desnecessárias. Isso porque eles leem e gravam dados diretamente no destino, sem a necessidade de criar conjuntos de dados intermediários, o que economiza tempo e recursos, além de melhorar a precisão dos resultados.

Os pipelines de SQL também ajudam a otimizar a capacidade de armazenamento de dados, porque é possível usá-los para excluir ou arquivar dados que não são mais necessários. Isso pode liberar espaço nos seus sistemas de armazenamento e aumentar a performance.

Criar pipelines de SQL eficientes e flexíveis pode ser um desafio, especialmente porque as necessidades das organizações mudam rapidamente. No entanto, eles podem se adaptar até mesmo às necessidades de dados mais complexas quando são planejados e projetados com cuidado e atenção.

Neste laboratório, você vai gerenciar dados e usar o SQL para criar um pipeline eficiente e flexível que atenda a uma necessidade comercial.

Cenário

Nos últimos anos, os lucros da TheLook eCommerce foram às alturas graças às vendas on-line, mas os tempos de entrega não acompanharam esse crescimento e houve uma queda na satisfação do cliente.

Como analista de dados na nuvem da TheLook eCommerce, você recebeu um convite do Caique, líder da equipe de logística, para colaborar no processo de desenvolvimento de um pipeline de dados de coleta, limpeza, transformação e carregamento de informações relacionadas às entregas aos clientes, incluindo a distância percorrida entre o centro de distribuição e cada cliente.

Essas informações vão ajudar a equipe de logística a encontrar formas de melhorar os tempos de entrega e aumentar a satisfação do cliente, como realocar os centros existentes e abrir novos, ou investir em novos métodos de transporte.

Você vai usar suas habilidades no BigQuery e no SQL para projetar um pipeline flexível que ofereça à equipe de logística dados confiáveis para monitorar melhor a performance das entregas e que seja facilmente atualizado de acordo com as mudanças nas necessidades de dados da equipe.

Primeiro, você vai criar um conjunto de dados e definir esquemas de tabela para os dados que serão ingeridos. Em seguida, vai executar e analisar uma série de transformações. Depois, vai aplicar as transformações aos dados antes de carregar os dados transformados em tabelas recém-definidas. Por fim, você vai formalizar essas consultas em um procedimento armazenado.

Configuração

Antes de clicar em "Começar o laboratório"

Leia as instruções a seguir. 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.

Neste laboratório prático, você pode fazer as atividades por conta própria em um ambiente cloud de verdade, 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. No painel Detalhes do laboratório à esquerda, você verá o seguinte:

   • Tempo restante
   • O botão Abrir console do Google Cloud
   • As credenciais temporárias que você vai usar neste laboratório
   • Outras informações, se forem necessárias
   Observação: se for preciso pagar pelo laboratório, um pop-up vai aparecer para você escolher a forma de pagamento.

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). A página de login será aberta em uma nova guia do navegador.

   Dica: é possível organizar as guias em janelas separadas, lado a lado, para alternar facilmente entre elas.

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

3. Se necessário, copie o Nome de usuário do Google Cloud abaixo e cole na caixa de diálogo de login. Clique em Próximo.

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

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

4. Copie a Senha do Google Cloud abaixo e cole na caixa de diálogo seguinte. Clique em Próximo.

{{{user_0.password | "Senha do Google Cloud"}}}

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

Importante: você precisa usar as credenciais fornecidas no laboratório, e não as da sua conta do Google Cloud. Observação: usar sua própria conta do Google Cloud neste laboratório pode gerar cobranças extras.

5. Nas próximas páginas:
   • Aceite os Termos e Condições
   • Não adicione opções de recuperação nem autenticação de dois fatores nesta conta temporária
   • Não se inscreva em testes gratuitos

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

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

Tarefa 1. Criar um conjunto de dados

Nesta tarefa, você vai criar um conjunto de dados no projeto existente, definir o esquema e estabelecer os tipos de dados.

1. No console do Cloud, no Menu de navegação (), selecione BigQuery.

Observação: talvez apareça a caixa de mensagem Olá! Este é o BigQuery no console do Cloud, com links para o guia de início rápido e as notas da versão das atualizações da interface. Clique em Concluído para continuar.

Serão exibidos o Menu de navegação, o painel Explorador e o Editor de consultas.

2. Clique no ícone Ver ações () ao lado do ID do projeto e, em seguida, clique em Criar banco de dados.

Observação: talvez seja necessário selecionar um projeto primeiro. Para isso, clique em Selecionar um projeto na barra de título do console do Google Cloud e, em seguida, escolha o link do projeto na caixa de diálogo Selecionar um projeto.

3. Defina o ID do conjunto de dados como thelook_ecommerce. Verifique se o local está definido como EUA e não mude os valores dos outros campos.

4. Clique em Criar conjunto de dados.

Agora, thelook_ecommerce vai aparecer abaixo do nome do seu projeto.

5. No Editor de consultas, clique no ícone Escrever nova consulta (+) para abrir uma nova guia Sem título e executar a consulta.

Observação: sempre que você executa uma nova consulta no Editor de consultas, pode substituir a consulta antiga copiando e colando a nova sobre a anterior na mesma guia Sem título ou clicar no ícone Escrever nova consulta (+) para abrir uma nova guia Sem título e fazer a consulta.

6. Copie e cole a consulta abaixo na guia Sem título para criar a tabela product_order_fulfillment:

--Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64);

7. Clique em Executar.

Observação: essa consulta cria uma nova tabela chamada product_orders_fulfillment, se necessário, ou substitui a atual pela nova definição de tabela.

8. Clique em Ir para a tabela e analise o esquema da tabela e os tipos de dados que foram criados.

Embora esse conjunto de dados esteja vazio no momento, é neste local onde os dados serão preenchidos após o carregamento.

Clique em Verificar meu progresso para conferir se você concluiu a tarefa corretamente.

Criar um conjunto de dados

Tarefa 2. Criar uma tabela usando os resultados da consulta

Sua análise está sendo expandida para incluir um estudo da proximidade dos centros de distribuição aos clientes que fazem os pedidos. Para isso, você vai precisar calcular os valores com base nas localizações geográficas.

Nesta tarefa, você vai criar pontos geométricos com base nos valores de latitude e longitude fornecidos nas tabelas original users e distribution_centers.

1. Copie e cole a consulta abaixo no Editor de consultas: --Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY);

Essa consulta cria as definições para as tabelas de clientes e de centros, que vão incluir as colunas originais das tabelas "users" e "distribution_centers". Ela também adiciona às duas tabelas as colunas point_location, que terão o tipo de dados definido como geográfico.

2. Clique em Executar.

Clique em Verificar meu progresso para conferir se você concluiu a tarefa corretamente.

Criar novas tabelas para armazenar dados relevantes

Tarefa 3. Executar uma transformação nos dados do BigQuery

Nesta tarefa, você vai criar geometrias de pontos usando a função geográfica ST_GEOGPOINT, em que você pode chamar ST_GEOGPOINT(lon, lat).

Primeiro, execute as seguintes consultas SQL para criar e preencher as tabelas de centros e clientes. Essas consultas carregam os dados de uma instrução SELECT, já que o local original (longitude, latitude) dos dados está no conjunto de dados público thelook_ecommerce do BigQuery.

Um conjunto de dados público do Google é qualquer conjunto de dados armazenado no BigQuery e disponibilizado para o público em geral por meio do Programa de conjuntos de dados públicos do Google Cloud. Eles facilitam o uso de dados públicos prontamente disponíveis no BigQuery sem a necessidade de carregá-los e mantê-los.

1. Copie a consulta a seguir no Editor de consultas:

--load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters;

Essa consulta carrega a tabela de centros, incluindo a transformação geográfica.

2. Clique em Executar.

Observação: clique em Ir para a tabela e analise o esquema da tabela e os tipos de dados que foram criados.

3. Agora copie a consulta a seguir no Editor de consultas:

--load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users;

Essa consulta carrega a tabela de clientes, incluindo a transformação geográfica.

4. Clique em Executar.

Em seguida, você vai precisar calcular os valores dos dados para análise.

Devido à importância da relação entre clientes e centros de distribuição, você vai usar essas localizações de pontos geográficos para determinar o centro de distribuição mais próximo de cada cliente e a distância entre os locais.

Para isso, use a função geográfica, ST_DISTANCE e calcule a distância mínima entre o local de um cliente e um centro de distribuição.

5. Copie a seguinte subconsulta escalar no Editor de consultas:

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

Essa consulta calcula a distância em quilômetros.

6. Clique em Executar.

Conforme exibido na captura de tela, a consulta retorna a distância entre cada cliente e o centro de distribuição mais próximo. Analise a instrução SELECT para entender como ela deriva o cálculo da distância.

7. Expanda o menu suspenso Salvar e selecione Salvar consulta.

A caixa de diálogo Salvar consulta será aberta.

8. No campo Nome, digite Calcular a distância do cliente até o centro de distribuição mais próximo. Mantenha os valores padrão para as demais configurações.
9. Clique em Salvar.
10. No painel Explorador, clique duas vezes na consulta salva no menu suspenso Consultas salvas para executá-la novamente.

Clique em Verificar meu progresso para conferir se você concluiu a tarefa corretamente.

Executar uma transformação nos dados do BQ

Tarefa 4. Criar um procedimento armazenado para garantir atualizações mais fáceis

Um procedimento armazenado é um conjunto de instruções SQL que são salvas em um banco de dados e podem ser executadas como uma única unidade.

Nesta tarefa, você vai criar um procedimento armazenado para incluir todas as definições de tabela, transformações e instruções de ingestão que você gerou de forma manual e sequencial até agora nas tarefas 1, 2 e 3.

Para criar um procedimento armazenado (nesse caso, sp_create_load_tables), adicione a instrução CREATE OR REPLACE PROCEDURE no início do script, seguida do nome do procedimento armazenado e da instrução BEGIN. Depois de adicionar todos os componentes do procedimento, adicione a instrução END.

Para isso, você pode colocar suas definições de tabela e suas partes de ingestão de dados. É possível inserir todas as definições na frente ou agrupá-las por tabela, o que preferir. No entanto, neste laboratório, cada definição de tabela é seguida pela parte de ingestão de dados e pelas outras transformações feitas nas etapas anteriores.

Você também pode criar colunas na sua tabela de clientes que possam armazenar os distance_to_closest_center e closest_centervalues calculados. Isso é útil porque, se o esquema mudar ou se você quiser incluir colunas ou gerar colunas calculadas, poderá adicioná-las facilmente ou atualizar o procedimento armazenado. Depois da atualização, o procedimento armazenado pode ser programado para execução regular.

Agora, crie um procedimento armazenado que vai executar todas as etapas realizadas anteriormente nas tarefas 1, 2 e 3 como uma única unidade. O código a seguir vai atualizar as tabelas e preenchê-las adequadamente.

1. Copie a seguinte consulta na nova guia Sem título:

CREATE OR REPLACE PROCEDURE `thelook_ecommerce.sp_create_load_tables`() BEGIN --Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64) ; --load the product_order_fulfillment table from public dataset CREATE OR REPLACE TABLE thelook_ecommerce.product_orders_fulfillment AS SELECT items.*, products.id AS product_id_products, products.name AS product_name, products.category AS product_category, FROM bigquery-public-data.thelook_ecommerce.order_items AS items JOIN bigquery-public-data.thelook_ecommerce.products AS products ON (items.product_id = products.id); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters ; --Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users ; END ;

2. Clique em Executar.

Observação: o procedimento armazenado cria todas as tabelas necessárias e as preenche com os dados transformados. No entanto, ele não retorna os resultados da consulta que mostram a distância entre os clientes e o centro de distribuição mais próximo. Passe à Etapa 7 abaixo para fazer isso.

3. Expanda o menu suspenso Salvar e selecione Salvar consulta. A caixa de diálogo Salvar consulta será aberta.
4. No campo Nome, digite sp_create_load_tables. Mantenha os valores padrão para as demais configurações.
5. Clique em Salvar. Esse procedimento armazenado será exibido na seção Rotinas do conjunto de dados:
6. Clique em Executar para concluir esse procedimento armazenado.

Como mostrado na Etapa 5 da Tarefa 3 acima, execute a instrução SELECT SQL para descobrir a distância entre cada cliente e o centro de distribuição mais próximo a ele.

7. Copie a seguinte subconsulta escalar no Editor de consultas:

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

Essa consulta calcula a distância em quilômetros.

8. Clique em Executar.

Clique em Verificar meu progresso para conferir se você concluiu a tarefa corretamente.

Crie um procedimento armazenado para facilitar as atualizações. Observação: você não vai executar uma consulta programada neste laboratório, mas é importante entender as várias maneiras de atualizar dados e manter seus pipelines de SQL.

Para configurar uma consulta programada, basta clicar em Programação no Editor de consultas. A janela "Nova consulta programada" é aberta para você especificar os detalhes dela. É possível fazer isso com qualquer consulta que precise ser atualizada regularmente.

Conclusão

Bom trabalho!

Você usou o SQL e o BigQuery para criar um pipeline de dados simples e flexível que atende a uma necessidade comercial.

Primeiro, você criou um conjunto de dados e definiu esquemas de tabela para os dados que serão ingeridos. Em seguida, executou e analisou uma série de transformações.

Depois, você aplicou as transformações aos dados antes de carregar os dados transformados em tabelas recém-definidas.

Por fim, formalizou essas consultas em um procedimento armazenado para incluir valores calculados de interesse e como configurar consultas programadas para atualizações regulares.

Neste laboratório, você aprendeu a importância de criar conjuntos de dados e definir esquemas de tabelas para as informações que eles contêm. Ao executar e examinar uma série de transformações, você desenvolveu as habilidades para aplicar transformações aos dados antes de carregar os dados transformados em tabelas recém-definidas. Também aprendeu a formalizar essas consultas em um procedimento armazenado para incluir valores calculados de interesse e como configurar consultas programadas para atualizações regulares.

Você está no caminho certo para entender como usar o BigQuery a fim de criar e gerenciar pipelines de SQL.

Finalize o laboratório

Antes de encerrar o laboratório, certifique-se de que você concluiu todas as tarefas. Quando tudo estiver pronto, clique em Terminar o laboratório e depois em Enviar.

Depois que você finalizar um laboratório, não será mais possível acessar o ambiente do laboratório nem o trabalho que você concluiu nele.

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 empresas e produtos podem ser marcas registradas das empresas a que estão associados.