Visão geral

Neste laboratório, você vai aprender a importar dados de arquivos de texto CSV para o Cloud SQL e fazer algumas análises de dados básicas usando consultas simples.

O conjunto de dados usado neste laboratório, disponibilizado pela NYC Taxi and Limousine Commission, inclui todas as corridas feitas em táxis amarelos e verdes na cidade de Nova York de 2009 até hoje, além de todas as corridas de veículos para locação (FHV, na sigla em inglês) de 2015 até hoje. Os registros contêm campos que coletam datas/horários de embarque e desembarque, os locais de embarque e desembarque, as distâncias percorridas, as tarifas detalhadas, os tipos de tarifas, os tipos de pagamentos e as contagens de passageiros informadas pelos motoristas.

Com esse conjunto de dados, podemos demonstrar diversos conceitos e técnicas da ciência de dados. Eles são usados em vários dos laboratórios do curso de engenharia de dados.

Objetivos

• Criar uma instância do Cloud SQL
• Criar um banco de dados do Cloud SQL
• Importar dados de texto para o Cloud SQL
• Verificar a integridade dos dados

Configuração e requisitos

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

1. Faça login no Qwiklabs em uma janela anônima.

2. Confira o tempo de acesso do laboratório (por exemplo, 1:15:00) e finalize todas as atividades nesse prazo.
   Não é possível pausar o laboratório. Você pode reiniciar o desafio, mas vai precisar refazer todas as etapas.

3. Quando tudo estiver pronto, clique em Começar o laboratório.

4. Anote as credenciais (Nome de usuário e Senha). É com elas que você vai fazer login no Console do Google Cloud.

5. Clique em Abrir Console do Google.

6. Clique em Usar outra conta, depois copie e cole as credenciais deste laboratório nos locais indicados.
   Se você usar outras credenciais, vai receber mensagens de erro ou cobranças.

7. Aceite os termos e pule a página de recursos de recuperação.

Ative o Google Cloud Shell

O Google Cloud Shell é uma máquina virtual com ferramentas de desenvolvimento. Ele tem um diretório principal permanente de 5 GB e é executado no Google Cloud.

O Cloud Shell oferece acesso de linha de comando aos recursos do Google Cloud.

1. No console do Cloud, clique no botão "Abrir o Cloud Shell" na barra de ferramentas superior direita.

   

2. Clique em Continuar.

O provisionamento e a conexão do ambiente podem demorar um pouco. Quando você estiver conectado, já estará autenticado, e o projeto estará definido com seu PROJECT_ID. Exemplo:

A gcloud é a ferramenta de linha de comando do Google Cloud. Ela vem pré-instalada no Cloud Shell e aceita preenchimento com tabulação.

• Para listar o nome da conta ativa, use este comando:

gcloud auth list

Saída:

Credentialed accounts: - @.com (active)

Exemplo de saída:

Credentialed accounts: - google1623327_student@qwiklabs.net

• Para listar o ID do projeto, use este comando:

gcloud config list project

Saída:

[core] project =

Exemplo de saída:

[core] project = qwiklabs-gcp-44776a13dea667a6 Observação: a documentação completa da gcloud está disponível no guia com informações gerais sobre a gcloud CLI .

Tarefa 1: Preparar o ambiente

• Crie as variáveis de ambiente, que serão usadas mais adiante no laboratório para seu ID do projeto, e o bucket de armazenamento que conterá os dados:

export PROJECT_ID=$(gcloud info --format='value(config.project)') export BUCKET=${PROJECT_ID}-ml export REGION=$(gcloud org-policies describe constraints/gcp.resourceLocations \ --effective \ --project=$(gcloud config get-value project) \ --format="json" | jq -r ' .spec.rules[].values.allowedValues[] | select( test("^(us|europe|asia|northamerica|southamerica|australia)-(east|west|central|southeast|northeast|south|north)[0-9]+$") ) ' | head -n1) echo $REGION

Quando solicitado, pressione Y para aceitar a alteração.

Tarefa 2: Criar uma instância do Cloud SQL

1. Para criar uma instância do Cloud SQL, use os seguintes comandos:

gcloud sql instances create taxi \ --tier=db-n1-standard-1 \ --activation-policy=ALWAYS \ --region=$REGION

Esse processo leva alguns minutos.

Teste a tarefa concluída

Clique em Verificar meu progresso para conferir a tarefa realizada. Se ela tiver sido concluída corretamente, você vai receber uma pontuação de avaliação.

Crie uma instância do Cloud SQL.

2. Defina uma senha raiz para a instância do Cloud SQL:

gcloud sql users set-password root --host % --instance taxi \ --password Passw0rd

3. Quando a senha for solicitada, digite Passw0rd e pressione Enter para atualizar a senha raiz.

Observação: a senha inserida Passw0rd será mascarada e não ficará visível no terminal da nuvem.

4. Agora crie uma variável de ambiente com o endereço IP do Cloud Shell:

export ADDRESS=$(wget -qO - http://ipecho.net/plain)/32

5. Autorize a instância do Cloud Shell a ter acesso de gerenciamento à sua instância SQL:

gcloud sql instances patch taxi --authorized-networks $ADDRESS

6. Quando solicitado, pressione Y para aceitar a alteração.

Teste a tarefa concluída

Clique em Verificar meu progresso para conferir a tarefa realizada. Se ela tiver sido concluída corretamente, você vai receber uma pontuação de avaliação.

Autorize a instância do Cloud Shell a acessar sua instância SQL.

7. Para saber o endereço IP da instância do Cloud SQL, execute o seguinte comando:

MYSQLIP=$(gcloud sql instances describe \ taxi --format="value(ipAddresses.ipAddress)")

8. Confira a variável MYSQLIP:

echo $MYSQLIP

Na saída, você recebe um endereço IP.

9. Para criar a tabela de corridas de táxi, faça login na interface de linha de comando mysql:

mysql --host=$MYSQLIP --user=root \ --password --verbose

10. Quando uma senha for solicitada, digite Passw0rd.

11. Cole o código abaixo na linha de comando para criar o esquema da tabela trips:

create database if not exists bts; use bts; drop table if exists trips; create table trips ( vendor_id VARCHAR(16), pickup_datetime DATETIME, dropoff_datetime DATETIME, passenger_count INT, trip_distance FLOAT, rate_code VARCHAR(16), store_and_fwd_flag VARCHAR(16), payment_type VARCHAR(16), fare_amount FLOAT, extra FLOAT, mta_tax FLOAT, tip_amount FLOAT, tolls_amount FLOAT, imp_surcharge FLOAT, total_amount FLOAT, pickup_location_id VARCHAR(16), dropoff_location_id VARCHAR(16) );

Teste a tarefa concluída

Clique em Verificar meu progresso para conferir a tarefa realizada. Se ela tiver sido concluída corretamente, você vai receber uma pontuação de avaliação.

Crie um banco de dados bts e a tabela trips.

12. Na interface de linha de comando mysql, digite os seguintes comandos para verificar a importação:

describe trips;

13. Consulte a tabela trips:

select distinct(pickup_location_id) from trips;

Como ainda não há informações no banco de dados, será exibido um conjunto vazio.

14. Saia do console interativo mysql:

exit

Tarefa 3: Adicionar dados à instância do Cloud SQL

Agora você copiará para o sistema local os arquivos CSV das corridas de táxi de Nova York que estão no Cloud Storage. Para manter o uso de recursos baixo, você trabalhará apenas com um subconjunto dos dados (cerca de 20 mil linhas).

1. Execute estes comandos na linha de comando:

gcloud storage cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_1.csv trips.csv-1 gcloud storage cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_2.csv trips.csv-2

2. Conecte-se ao console interativo mysql para carregar dados do arquivo local:

mysql --host=$MYSQLIP --user=root --password --local-infile

3. Quando uma senha for solicitada, digite Passw0rd.

4. No console interativo mysql, escolha o banco de dados:

use bts;

5. Carregue os dados do arquivo CSV local usando local-infile:

LOAD DATA LOCAL INFILE 'trips.csv-1' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id); LOAD DATA LOCAL INFILE 'trips.csv-2' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id);

Tarefa 4: Verificar a integridade dos dados

Quando você importa dados de uma fonte, é sempre necessário verificar a integridade deles. Isso significa verificar se os dados estão de acordo com o que você esperava.

1. Consulte a tabela trips para identificar as regiões de embarque exclusivas:

select distinct(pickup_location_id) from trips;

A consulta retornará 159 IDs exclusivos.

2. Vamos começar a analisar a coluna trip_distance. Digite a seguinte consulta no console:

select max(trip_distance), min(trip_distance) from trips;

A expectativa é que a distância das corridas seja maior que 0 e menor que 1.600 km. A consulta indica uma distância máxima de 136 km, o que faz sentido, e uma distância mínima de 0, o que parece ser um bug.

3. Quantas corridas no conjunto de dados têm a distância de 0?

select count(*) from trips where trip_distance = 0;

Existem 155 corridas com essa característica no banco de dados. É preciso analisar melhor essas corridas. O valor pago delas é diferente de zero. Talvez sejam transações fraudulentas?

4. Vamos tentar encontrar mais dados que não estejam de acordo com o esperado. Nossa expectativa é que a coluna fare_amount seja positiva. Digite a seguinte consulta para conferir se isso é verdade no banco de dados:

select count(*) from trips where fare_amount < 0;

Serão retornadas 14 corridas. Essas corridas também precisam ser investigadas. Talvez haja uma explicação racional para a existência de corridas com números negativos. Mas a função do engenheiro de dados é garantir que não haja bugs no pipeline que poderiam causar esse resultado.

5. Por fim, vamos investigar a coluna payment_type.

select payment_type, count(*) from trips group by payment_type;

O resultado da consulta indica que há quatro tipos de pagamento, em que:

• O tipo de pagamento 1 tem 13.863 linhas.
• O tipo de pagamento 2 tem 6.016 linhas.
• O tipo de pagamento 3 tem 113 linhas.
• O tipo de pagamento 4 tem 32 linhas.

Consultando a documentação, descobrimos que o tipo 1 é cartão de crédito, o tipo 2 é dinheiro em espécie e o tipo 4 é contestação. Os números fazem sentido.

6. Saia do console interativo "mysql":

exit

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.