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 pode aproveitar o modelo de pagamento por uso. Assim, você pode se concentrar na análise dos dados para encontrar informações relevantes.

Este laboratório se concentra em como arquitetar um data warehouse para o desempenho de consultas. Neste laboratório, você vai comparar um esquema relacional tradicional com mesclagens com um esquema desnormalizado. Você também usará o plano de execução de consultas do BigQuery para avaliar de forma quantificável as compensações de desempenho.

Atividades deste laboratório

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

• Carregar um arquivo de valores separados por vírgula (CSV) em uma tabela do BigQuery usando a interface da Web.
• Carregar um arquivo JavaScript® Object Notation (JSON) em uma tabela do BigQuery usando a interface de linha de comando (CLI).
• Transformar dados e mesclar tabelas usando a interface da Web.
• Armazenar os resultados da consulta em uma tabela de destino.
• Consultar uma tabela de destino usando a interface da Web para confirmar se os dados foram transformados e carregados corretamente.

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:

Saída:

Exemplo de saída:

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

Saída:

Exemplo de saída:

Abrir o BigQuery no Console do Cloud

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

Você vai 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 lista as atualizações da IU.

2. Clique em Concluído.

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

No projeto do BigQuery, crie um novo conjunto de dados denominado liquor_sales.

1. Na seção Explorador, clique no ícone "Conferir ações" do lado do ID do projeto e selecione Criar conjunto de dados.

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

2. Defina o ID do conjunto de dados como liquor_sales. Deixe as demais opções nos valores padrão e clique em Criar conjunto de dados.

No painel esquerdo, será exibida uma tabela liquor_sales relacionada no projeto.

Tarefa 2: carregue e consulte os dados relacionais

Nesta seção, você medirá o desempenho da consulta para dados relacionais no BigQuery.

O BigQuery oferece suporte a JOINs grandes e o desempenho do JOIN é bom. No entanto, o BigQuery é um armazenamento de dados colunar e o desempenho máximo é alcançado em conjuntos de dados desnormalizados. Como o armazenamento do BigQuery é barato e escalonável, é uma boa prática desnormalizar e pré-mesclar os conjuntos de dados em tabelas homogêneas. Em outras palavras, você troca recursos de computação por recursos de armazenamento (sendo este último mais eficiente e econômico).

Nesta seção, você fará o seguinte:

• Fazer upload de um conjunto de tabelas de um esquema relacional (em um 3º formulário normal).
• Executar consultas nas tabelas relacionais.
• Observar o desempenho das consultas para comparar com o desempenho das mesmas consultas em uma tabela de um esquema desnormalizado contendo as mesmas informações.

Faça o upload de tabelas que possuem um esquema relacional. O esquema relacional consiste nas seguintes tabelas:

Este é um diagrama do esquema relacional.

Criar a tabela de vendas

1. Na seção Explorador, clique no ícone "Exibir ações" próximo ao conjunto de dados liquor_sales, selecione Abrir e, em seguida, clique em Criar tabela.

2. Na página Criar tabela, na seção Origem, faça o seguinte:

• Em Criar tabela de, selecione Google Cloud Storage.
• Informe o caminho para o nome do bucket do Google Cloud Storage:

• Em Formato de arquivo, escolha CSV.

3. Na seção Destino, configure o seguinte:

• Em Nome da tabela, insira vendas.
• Deixe os campos de destino restantes com seus padrões.

4. Na seção Esquema, configure o seguinte:

• Clique em Editar como texto.
• Copie e cole o esquema abaixo:

5. Clique em Opções avançadas para exibir e configurar estas opções:

• Em Delimitador de campo, verifique se Vírgula está selecionada.
• Como sales.csv contém uma única linha de cabeçalho, defina Linhas de cabeçalho para pular como 1.
• Verifique Novas linhas com consulta.
• Deixe os demais valores como estão e clique em Criar tabela.

O BigQuery gera um job de carregamento para criar a tabela e fazer o upload dos dados nela. Isso pode levar alguns segundos.

6. Clique em Histórico pessoal para acompanhar o progresso do trabalho.

Criar as tabelas restantes

Crie as tabelas restantes no esquema relacional usando a linha de comando do Cloud Shell.

1. Crie a tabela categoria:

2. Crie a tabela convenience_store:

3. Crie a tabela condado:

4. Crie a tabela item:

5. Crie a tabela loja:

6. Crie a tabela fornecedor:

7. Volte para a interface da Web do BigQuery Confirme a exibição das novas tabelas carregadas no conjunto de dados de liquor_sales. Atualize o navegador, se for necessário.

Consultar dados relacionais

Em seguida, use o Editor de consultas para consultar seus dados.

1. Na caixa de código do Editor de consultas, clique em Mais > Configurações de consulta.

2. Na seção Gerenciamento de recursos, Preferências de cache, desmarque a caixa de seleçãoUsar resultados em cache e clique em Salvar. Se você precisar executar a consulta mais de uma vez, não deverá usar resultados armazenados em cache.

3. Na janela do Editor de consultas, insira a seguinte consulta nas tabelas relacionais e clique em Executar:

4. Na parte inferior, na seção Resultados da consulta, clique na guia Resultados. Observe o tempo de conclusão da consulta. Um exemplo é exibido abaixo (o tempo de execução pode variar).

Isso será comparado ao tempo para consultar um conjunto de dados nivelado em seções posteriores.

Tarefa 3: carregar e consultar dados nivelados

Nesta seção, você desnormaliza os esquemas e analisa as vendas de bebidas alcoólicas no estado de Iowa usando os dados nivelados. A execução desta consulta nos dados nivelados deve ser mais rápida do que a execução nos dados relacionais. Você saberá a hora de comparar e confirmar.

Um esquema desnormalizado nivela os dados relacionais em uma única linha. Por exemplo, no esquema desnormalizado, as tabelas county_number, county, store, name, address, city, zipcode, store_location, county_number e cstore são campos contendo todos os campos das tabelas County, Store e Convenience_store.

O diagrama a seguir mostra o esquema desnormalizado.

Criar a tabela iowa_sales_denorm

1. No painel esquerdo, selecione conjunto de dados liquor_sales e clique em Criar tabela à direita.

A caixa de diálogo Criar tabela é aberta.

2. Na seção Fonte, configure o seguinte:

• Em Criar tabela de, selecione Google Cloud Storage.
• Informe o caminho para o nome do bucket do Google Cloud Storage:

• Em Formato de arquivo, escolha CSV.

3. Na seção Destino, configure o seguinte:

• Para Nome da tabela, digite iowa_sales_denorm.
• Deixe os campos de destino restantes com seus padrões.

4. Na seção Esquema, configure o seguinte:

• Clique em Editar como texto.
• Copie e cole o esquema abaixo:

5. Na seção Opções avançadas, configure o seguinte:

• Em Delimitador de campo, verifique se Vírgula está selecionada.
• Como iowa_sales_denorm.csv contém uma única linha de cabeçalho, para Linhas de cabeçalho a serem ignoradas, digite 1.
• Verifique Novas linhas com consulta.
• Deixe os demais valores como estão e clique em Criar tabela.

O BigQuery gera um job de carregamento para criar a tabela e fazer o upload dos dados nela. Isso pode levar alguns segundos.

6. Clique em Histórico pessoal para acompanhar o progresso do trabalho.

7. Insira e execute a seguinte consulta na tabela com um esquema desnormalizado (esta consulta produz os mesmos resultados que a consulta na seção anterior):

8. Na parte inferior, na seção Resultados da consulta, clique na guia Resultados e observe o tempo de conclusão da consulta. Isso será comparado ao tempo para consultar um conjunto de dados nivelado em seções posteriores.

9. Observe o tempo que a consulta leva para ser executada subtraindo o horário de início do horário de término.

A consulta correspondente à tabela com esquema desnormalizado é executada um pouco mais rápido e possui uma sintaxe mais simples. Sempre que possível, faça a pré-mesclagem dos conjuntos de dados em tabelas homogêneas para otimizar o desempenho no BigQuery.

Comparar o desempenho da consulta com os detalhes da execução

1. Selecione HISTÓRICO DO PROJETO.

2. Clique no primeiro trabalho de consulta executado no esquema relacional normalizado e, em seguida, clique em ABRIR COMO NOVA CONSULTA.

3. Selecione Detalhes de execução.

O plano de execução tem duas seções principais:

• Tempo médio e máximo do worker por tipo de trabalho por etapa

• Comparativos de mercado de desempenho de alto nível

  • Tempo decorrido: tempo total para o processamento da consulta.
  • Tempo de slot consumido: se a consulta não fosse processada em paralelo em várias máquinas, quanto tempo levaria para ser processada.
  • Bytes embaralhados: embaralhamento automático de dados na memória para processamento paralelo massivo.
  • Bytes espalhados no disco: se os dados não puderem ser processados na memória, quanto foi espalhado no disco permanente (geralmente a responsável é a distorção dos dados).

1. Primeiro, compare os tempos de comparativo de mercado entre cada uma das consultas que executamos.

2. Em seguida, compare o tipo de trabalho em que os workers passaram mais tempo.

Consulta 1. Detalhes de execução do esquema relacional

Consulta 2. Detalhes de execução do esquema desnormalizado

Observações:

• A consulta desnormalizada (nº 2) é mais rápida e usa menos tempo de slot para obter o mesmo resultado.
• A consulta relacional (nº 1) tem muito mais estágios de entrada e gasta a maior parte do tempo do worker mesclando os conjuntos de dados.
• A consulta desnormalizada (nº 2) gasta a maior parte do tempo lendo a entrada de dados e exibindo os resultados. O tempo gasto em agregações e mesclagens é mínimo.
• Nenhuma das consultas resultou em bytes espalhados no disco, o que sugere que nossos conjuntos de dados provavelmente não estão distorcidos (ou são significativamente grandes o suficiente para serem vazados da memória de um worker individual).

Como evitar antipadrões de desempenho

Agora que você está familiarizado com o design eficaz de esquemas de banco de dados, é hora de praticar a otimização de algumas consultas mal escritas.

A consulta abaixo está lenta. O que você pode fazer para corrigi-la?

1. Copie e cole a consulta abaixo no Editor de consultas e execute a consulta para obter um comparativo de mercado.

Objetivo: contar todas as organizações sem fins lucrativos dos EUA que declararam tributos em papel (não eletrônico) em 2015.

O que podemos fazer para melhorar o desempenho?

2. Compare com a solução abaixo:

3. Execute a versão atualizada e acompanhe o tempo.

4. Limpe o Editor de consultas.

A nova consulta abaixo está sendo executada lentamente. (Execute a consulta para obter um comparativo de mercado; pare após 30 segundos se ela não for concluída).

Objetivo: utilizando o Employer Identification Number (EIN) como campo de ligação, mescle a tabela de declarações de impostos de renda com a tabela de nomes organizacionais, e são retornados os nomes de todas as organizações que apresentaram declarações em 2015.

5. Adicione esta consulta no Editor de consultas e clique em Executar:

6. Corrija a consulta acima. (Dica: lembre-se da condição correta do campo JOIN para nosso esquema).

Compare com a solução abaixo.

7. Adicione esta consulta no Editor de consultas e clique em Executar:

8. Execute a versão atualizada e acompanhe o tempo.

Você percebe algum tipo de melhora? Qual a velocidade da consulta executada?

Lições aprendidas

Parabéns!

Isso conclui este laboratório prático que analisa o design eficaz do esquema do BigQuery e o desempenho da consulta. Você carregou arquivos CVS e JSON em tabelas do BigQuery, transformou dados e mesclou tabelas, armazenou resultados de consulta e confirmou que os dados foram transformados e carregados corretamente.

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.