Informações gerais

Neste laboratório, você vai analisar e executar consultas em 2 conjuntos de dados públicos diferentes, tanto de forma separada como em conjunto, para gerar insights úteis e interessantes.

O que vamos abordar

Neste laboratório, você vai aprender a:

• Fazer consultas interativas no console do BigQuery
• Mesclar e executar análises em vários conjuntos de dados

Pré-requisitos

Este é um laboratório de nível fundamental que exige uma certa experiência com BigQuery e SQL.

Introdução

Neste laboratório, usaremos dois conjuntos de dados públicos no BigQuery: dados meteorológicos da Administração Oceânica e Atmosférica Nacional dos EUA (NOAA, na sigla em inglês) e dados de aluguel de bicicletas dos cidadãos de Nova York.

Você vai conhecer vários aspectos do Google Cloud Platform que trazem benefícios para cientistas:

1. Computação sem servidor: não é preciso baixar dados para sua máquina. O conjunto de dados permanece na nuvem enquanto você trabalha com ele.
2. Facilidade de uso: execução de consultas SQL sob demanda no conjunto de dados, sem necessidade de preparo prévio, como a criação e índices. Isso facilita muito a análise de dados.
3. Escalonamento: a análise de dados pode ser feita em conjuntos extremamente grandes, de forma interativa. Não é preciso usar uma amostra de dados para agilizar o trabalho.
4. Possibilidade de compartilhamento: é possível executar consultas em diferentes conjuntos de dados, sem qualquer problema. O BigQuery é uma excelente opção de compartilhamento de conjuntos de dados. Também é possível manter os dados privados ou permitir o acesso de pessoas específicas. Nem todos os dados precisam ser públicos.

O resultado final? Você irá comprovar se o aluguel de bicicletas diminui nos dias chuvosos.

Configuração e requisitos

Configuração do laboratório

Para cada laboratório, você recebe um novo projeto do Google Cloud e um conjunto de recursos por um determinado período sem custo financeiro.

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: para acessar uma lista de produtos e serviços do Google Cloud, clique no Menu de navegação no canto superior esquerdo ou digite o nome do serviço ou produto no campo Pesquisar.

Tarefa 1: analisar os dados de aluguel de bicicletas

Nesta tarefa, você vai analisar o conjunto de dados público bigquery-public-data, especificamente new_york_citibike, examinando o esquema da tabela citibike_trips. Também irá executar consultas para analisar a duração das viagens e calcular a distância total percorrida por cada bicicleta.

1. No Menu de navegação () do console do Google Cloud, clique em BigQuery.

A caixa de mensagem "Olá! Este é o BigQuery no console do Cloud" vai aparecer. Ela tem um link para o guia de início rápido e uma lista com as atualizações da interface.

2. Clique em Concluído.

3. No painel à esquerda, clique em + Adicionar e em Marcar um projeto com estrela por nome. Depois, na janela pop-up, digite bigquery-public-data e, finalmente, clique em Marcar com estrela.

4. No console do BigQuery, você vai ver dois projetos no painel esquerdo, um deles com o ID do projeto do Qwiklabs e o outro chamado bigquery-public-data.

5. No painel esquerdo do console do BigQuery, selecione a tabela bigquery-public-data > new_york_citibike > citibike_trips.

6. Na janela "Tabela (citibike_trips)", clique na guia Esquema.

7. Examine os nomes das colunas e os tipos de dados.

8. Clique em + Consulta SQL para escrever uma nova consulta.

Digite a seguinte consulta:

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(tripduration, 10)[OFFSET (5)] AS typical_duration, COUNT(tripduration) AS num_trips FROM `bigquery-public-data.new_york_citibike.citibike_trips` WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

9. Clique em Executar. Confira o resultado e identifique o que essa consulta faz.

   Dica: a duração típica dos 10 aluguéis mais comuns de viagem única

10. Em seguida, execute a consulta abaixo para descobrir outro fato interessante: a distância total percorrida por cada bicicleta do conjunto de dados. A consulta é limitada aos 5 resultados principais.

WITH trip_distance AS ( SELECT bikeid, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data.new_york_citibike.citibike_trips`, `bigquery-public-data.new_york_citibike.citibike_stations` as s, `bigquery-public-data.new_york_citibike.citibike_stations` as e WHERE start_station_name = s.name AND end_station_name = e.name) SELECT bikeid, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bikeid ORDER BY total_distance DESC LIMIT 5 Observação: nesta consulta, também usamos a outra tabela do conjunto de dados chamada citibike_stations para ter informações sobre as estações de bicicletas.

Tarefa 2: analisar o conjunto de dados meteorológicos

Nesta tarefa, você vai acessar a tabela ghcnd_2015 para analisar o conjunto de dados ghcn_d e executar uma consulta para extrair dados de chuva de uma estação meteorológica de Nova York.

1. No painel esquerdo do console do BigQuery, selecione o projeto recém-adicionado, bigquery-public-data, e escolha ghcn_d > ghcnd_2015.

2. Por último, clique na guia Visualização. O console vai mostrar o seguinte:

Examine as colunas e alguns dos valores de dados.

3. Clique em + Consulta SQL para escrever uma nova consulta e insira o seguinte:

SELECT wx.date, wx.value/10.0 AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE id = 'USW00094728' AND qflag IS NULL AND element = 'PRCP' ORDER BY wx.date

4. Clique em Executar.

Essa consulta retornará a precipitação pluviométrica (em mm) de todos os dias ao longo de 2015, de uma estação meteorológica em Nova York que tem o ID informado na consulta. A estação corresponde a NEW YORK CNTRL PK TWR ).

Tarefa 3: encontrar a correlação entre a chuva e o aluguel de bicicletas

Nesta tarefa, você vai analisar a correlação entre a chuva e o aluguel de bicicletas, mesclando o conjunto de dados new_york_citibike.citibike_trips com o conjunto de dados meteorológicos ghcn_d.ghcnd_2015 para determinar o número médio de viagens de bicicleta em dias chuvosos e dias secos.

E se você mesclar os dados de aluguel de bicicletas com os dados meteorológicos para descobrir se houve menos locações nos dias chuvosos?

1. Clique em + Consulta SQL para escrever uma nova consulta e insira o seguinte:

WITH bicycle_rentals AS ( SELECT COUNT(starttime) as num_trips, EXTRACT(DATE from starttime) as trip_date FROM `bigquery-public-data.new_york_citibike.citibike_trips` GROUP BY trip_date ), rainy_days AS ( SELECT date, (MAX(prcp) > 5) AS rainy FROM ( SELECT wx.date AS date, IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE wx.id = 'USW00094728' ) GROUP BY date ) SELECT ROUND(AVG(bk.num_trips)) AS num_trips, wx.rainy FROM bicycle_rentals AS bk JOIN rainy_days AS wx ON wx.date = bk.trip_date GROUP BY wx.rainy

2. Clique em Executar.

Agora, você verá os resultados da combinação dos conjuntos de dados de aluguel de bicicletas e das informações meteorológicas, provenientes de origens completamente diferentes:

A execução da consulta demonstra que sim, há uma redução de 47% no uso de bicicletas dos cidadãos de Nova York, em dias chuvosos.

Resumo

Neste laboratório, você fez consultas ad hoc em dois conjuntos de dados. Você consultou os dados sem precisar configurar clusters, criar índices etc. Além disso, mesclou dois conjuntos de dados e extraiu insights interessantes. Tudo isso sem sair do navegador.

Parabéns!

Você aprendeu a executar consultas muito interessantes no BigQuery.

Finalize o laboratório

Clique em Terminar o laboratório após a conclusão. O Google Cloud Ensina remove os recursos usados e limpa a conta por você.

Você vai poder avaliar sua experiência no laboratório. Basta selecionar o número de estrelas, digitar um comentário e clicar em Enviar.

O número de estrelas indica o seguinte:

• 1 estrela = muito insatisfeito
• 2 estrelas = insatisfeito
• 3 estrelas = neutro
• 4 estrelas = satisfeito
• 5 estrelas = muito satisfeito

Feche a caixa de diálogo se não quiser enviar feedback.

Para enviar seu feedback, fazer sugestões ou correções, use a guia Suporte.

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