Particionamento e clustering no BigQuery

1. Introdução

O BigQuery é um data warehouse empresarial de baixo custo, totalmente gerenciado e com escala em petabytes para análise. O BigQuery funciona sem servidores, Não é preciso configurar e gerenciar clusters.

Um conjunto de dados do BigQuery fica em um projeto do GCP e contém uma ou mais tabelas. É possível consultar esses conjuntos de dados com SQL.

Neste codelab, você vai usar a IU da Web do BigQuery no Console do GCP para entender o particionamento e o clustering no BigQuery. O particionamento e o clustering de tabelas do BigQuery ajudam a estruturar seus dados para corresponder a padrões comuns de acesso aos dados. O particionamento e o clustering são essenciais para maximizar totalmente o desempenho e o custo do BigQuery ao consultar um intervalo de dados específico. Isso resulta na verificação de menos dados por consulta, e a remoção é determinada antes do horário de início da consulta.

Observação:além da interface da Web do BigQuery, você pode usar a ferramenta de linha de comando bq para realizar operações em conjuntos de dados do BigQuery. Para mais informações sobre a ferramenta de linha de comando bq, consulte Ferramenta de linha de comando bq.

Para mais informações sobre o BigQuery, consulte a documentação do BigQuery.

O que você vai aprender

• Como criar e consultar tabelas particionadas e em cluster
• Comparar o desempenho da consulta com tabelas particionadas e em cluster

O que é necessário

Confira os requisitos para concluir o laboratório:

• A versão mais recente do Google Chrome
• Uma conta de cobrança do Google Cloud Platform

2. Etapas da configuração

Para trabalhar com o BigQuery, crie ou selecione um projeto do GCP.

Criar um projeto

Para criar um novo projeto, siga estas etapas:

1. Se você ainda não tem uma Conta do Google (Gmail ou Google Apps), crie uma.
2. Faça login no Console do Google Cloud Platform ( console.cloud.google.com) e crie um novo projeto.
3. Se você não tiver projetos, clique no botão "Criar projeto":

Caso contrário, crie um projeto no menu de seleção:

4. Digite um nome para o projeto e selecione Criar. Anote o ID do projeto, um nome exclusivo em todos os projetos do Google Cloud.

3. Como trabalhar com conjuntos de dados públicos

O BigQuery permite trabalhar com conjuntos de dados públicos, incluindo BBC News, repositórios do GitHub, Stack Overflow e os conjuntos de dados da Administração Oceânica e Atmosférica Nacional dos EUA (NOAA, na sigla em inglês). Não é necessário carregar esses conjuntos de dados no BigQuery. Basta abrir os conjuntos de dados para navegar e consultar no BigQuery. Neste codelab, você vai trabalhar com o conjunto de dados públicos do Stack Overflow.

Navegar pelo conjunto de dados do Stack Overflow

O conjunto de dados do Stack Overflow contém informações sobre postagens, tags, selos, comentários, usuários e muito mais. Para navegar pelo conjunto de dados do Stack Overflow na interface da Web do BigQuery, siga estas etapas:

1. Abra o conjunto de dados do Stack Overflow. A IU da Web do BigQuery é aberta no Console do GCP e mostra informações sobre o conjunto de dados do Stack Overflow.
2. No painel de navegação , selecione bigquery-public-data. O menu é aberto para listar os conjuntos de dados públicos. Cada conjunto de dados é composto por uma ou mais tabelas.
3. Role a tela para baixo e selecione stackoverflow. O menu é aberto para listar as tabelas no conjunto de dados do Stack Overflow.
4. Selecione selos para conferir o esquema da tabela de selos. Observe os nomes dos campos na tabela.
5. Acima dos nomes dos campos, clique em Visualização para conferir dados de amostra da tabela de selos.

Para mais informações sobre todos os conjuntos de dados públicos disponíveis no BigQuery, consulte Conjuntos de dados públicos do Google BigQuery.

Consultar o conjunto de dados do Stack Overflow

Navegar por um conjunto de dados é uma boa maneira de entender os dados com que você está trabalhando, mas é ao consultar conjuntos de dados que o BigQuery realmente se destaca. Nesta seção, você vai aprender a executar consultas do BigQuery. Você não precisa saber nada de SQL neste momento. Você pode copiar e colar as consultas abaixo.

Para executar uma consulta, siga estas etapas:

1. No canto superior direito do console do GCP, selecione Escrever nova consulta.
2. Na área de texto do Editor de consultas, copie e cole a consulta SQL a seguir. O BigQuery valida a consulta, e a interface da Web mostra uma marca de seleção verde abaixo da área de texto para indicar que a sintaxe é válida.

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. Selecione Executar. A consulta retorna o número de postagens ou perguntas do Stack Overflow feitas a cada ano.

Observação:esta consulta usa o nome completo da tabela "posts_questions", incluindo o nome do projeto, do conjunto de dados e da tabela: bigquery-public-data.stackoverflow.posts_questions.

4. Como criar uma tabela

Na seção anterior, você consultou conjuntos de dados públicos disponibilizados pelo BigQuery. Nesta seção, você vai criar uma tabela no BigQuery com base em uma tabela atual. Você vai criar uma tabela com dados amostrados da tabela posts_questions do conjunto de dados público do Stack Overflow e depois consultar essa tabela.

Criar um conjunto de dados

Para criar e carregar dados de tabela no BigQuery, primeiro crie um conjunto de dados do BigQuery para armazenar os dados seguindo estas etapas:

1. No painel de navegação do console do GCP, selecione o nome do projeto criado como parte da configuração.
2. No painel de detalhes, à direita, selecione Criar conjunto de dados.

3. Na caixa de diálogo Criar conjunto de dados, em ID do conjunto de dados, digite stackoverflow. Mantenha todas as outras configurações padrão e clique em OK.

Criar uma nova tabela com postagens do StackOverflow de 2018

Agora que você criou um conjunto de dados do BigQuery, é possível criar uma tabela no BigQuery. Para criar uma tabela com dados de uma tabela atual, consulte o conjunto de dados de postagens do Stack Overflow de 2018 e grave os resultados em uma nova tabela. Para isso, siga estas etapas:

1. No canto superior direito do console do GCP, selecione Escrever nova consulta.

2. Na área de texto do Editor de consultas, copie e cole a seguinte consulta SQL para criar uma nova tabela, que é uma instrução DDL.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Selecione Executar. A consulta cria uma nova tabela questions_2018 no conjunto de dados stackoverflow do seu projeto com dados resultantes da execução de uma consulta no conjunto de dados do BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Observação:essa etapa leva cerca de dois minutos para criar uma nova tabela. Aguarde antes de continuar para a próxima etapa.

Consultar a nova tabela com postagens do Stack Overflow de 2018

Agora que você criou uma tabela do BigQuery, vamos executar uma consulta para retornar postagens do Stack Overflow com perguntas e títulos, além de algumas outras estatísticas, como número de respostas, comentários, visualizações e favoritos. Siga estas etapas:

1. No canto superior direito do console do GCP, selecione Escrever nova consulta.
2. Na área de texto do Editor de consultas, copie e cole a consulta SQL a seguir:

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Selecione Executar. A consulta retorna perguntas do Stack Overflow criadas em janeiro de 2018 e marcadas como android, além da pergunta e de algumas outras estatísticas.
4. Por padrão, o BigQuery armazena em cache os resultados da consulta. Execute a mesma consulta e você vai notar que o BigQuery levou muito menos tempo para retornar os resultados porque eles vieram do cache.
5. Execute a mesma consulta novamente, mas desta vez com o armazenamento em cache do BigQuery desativado. Vamos desativar o cache para o restante do laboratório para fazer uma comparação justa de desempenho com tabelas particionadas e em cluster, que serão executadas nas próximas seções. No editor de consultas, clique em Mais e selecione Configurações de consulta.
6. Em Preferência de cache, desmarque Usar resultados em cache.
7. Nos resultados da consulta, você vai encontrar o tempo necessário para a conclusão e o volume de dados processados para gerar os resultados.

5. Como criar e consultar uma tabela particionada

Na seção anterior, você criou uma tabela no BigQuery com dados da tabela posts_questions usando o conjunto de dados público do Stack Overflow. Consultamos esse conjunto de dados com o cache desativado e observamos o desempenho da consulta. Nesta seção, você vai criar uma tabela particionada com base na tabela posts_questions do mesmo conjunto de dados público do Stack Overflow e observar o desempenho da consulta.

Uma tabela particionada é uma tabela especial dividida em segmentos, chamados de partições, que facilitam a consulta e o gerenciamento dos dados. Normalmente, é possível dividir tabelas grandes em várias partições menores usando o tempo de ingestão de dados, a coluna TIMESTAMP/DATE ou uma coluna INTEGER. Vamos criar uma tabela particionada por DATE.

Saiba mais sobre tabelas particionadas aqui.

Criar uma nova tabela particionada com postagens do StackOverflow de 2018

Para criar uma tabela particionada com dados de uma tabela ou consulta atual, consulte o conjunto de dados de postagens do Stack Overflow de 2018 e grave os resultados em uma nova tabela. Para isso, siga estas etapas:

1. No canto superior direito do console do GCP, selecione Escrever nova consulta.

2. Na área de texto do Editor de consultas, copie e cole a seguinte consulta SQL para criar uma nova tabela, que é uma instrução DDL.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Selecione Executar. A consulta cria uma tabela questions_2018_partitioned no conjunto de dados stackoverflow do seu projeto com dados resultantes da execução de uma consulta no conjunto de dados do BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Observação:essa etapa leva cerca de dois minutos para criar uma nova tabela. Aguarde antes de continuar para a próxima etapa.

Consultar a tabela particionada com postagens do Stack Overflow de 2018

Agora que você criou uma tabela particionada do BigQuery, vamos executar a mesma consulta, desta vez na tabela particionada, para retornar postagens do Stack Overflow com perguntas e títulos, além de algumas outras estatísticas, como número de respostas, comentários, visualizações e favoritos. Siga estas etapas:

1. No canto superior direito do console do GCP, selecione Escrever nova consulta.
2. Na área de texto do Editor de consultas, copie e cole a consulta SQL a seguir:

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Selecione Executar com o armazenamento em cache do BigQuery desativado (consulte a seção anterior para desativar o cache do BigQuery). A consulta retorna perguntas do Stack Overflow criadas em janeiro de 2018 e marcadas como android, além da pergunta e de algumas outras estatísticas.
4. Nos resultados da consulta, você vai encontrar o tempo necessário para a conclusão e o volume de dados processados para gerar os resultados.

Você vai notar que o desempenho da consulta com a tabela particionada é melhor do que com a tabela não particionada, já que o BigQuery remove as partições, ou seja, verifica apenas as partições necessárias, processando menos dados e executando mais rápido. Isso otimiza os custos e o desempenho das consultas.

6. Como criar e consultar uma tabela em cluster

Na seção anterior, você criou uma tabela particionada no BigQuery com dados da tabela posts_questions no conjunto de dados público do Stack Overflow. Consultamos essa tabela com o cache desativado e observamos o desempenho da consulta com tabelas não particionadas e particionadas. Nesta seção, você vai criar uma tabela em cluster com base na tabela posts_questions do mesmo conjunto de dados público do Stack Overflow e observar o desempenho da consulta.

Quando uma tabela é agrupada em cluster no BigQuery, os dados são organizados automaticamente com base no conteúdo de uma ou mais colunas no esquema da tabela. As colunas especificadas são usadas para colocar dados relacionados. Quando os dados são gravados em uma tabela em cluster, o BigQuery os classifica usando os valores nas colunas de clustering. Esses valores são usados para organizar os dados em diversos blocos no armazenamento do BigQuery. A ordem das colunas de clustering determina a ordem de classificação dos dados. Quando novos dados são adicionados a uma tabela ou uma partição específica, o BigQuery executa um reclustering automático em segundo plano para restaurar a propriedade de classificação da tabela ou partição.

Saiba mais sobre como trabalhar com tabelas em cluster aqui.

Criar uma nova tabela em cluster com postagens do Stack Overflow de 2018

Nesta seção, você vai criar uma tabela particionada em creation_date e em cluster na coluna tags com base no padrão de acesso à consulta. Para criar uma tabela em cluster com dados de uma tabela ou consulta atual, consulte a tabela de postagens do Stack Overflow de 2018 e grave os resultados em uma nova tabela. Para isso, siga estas etapas:

1. No canto superior direito do console do GCP, selecione Escrever nova consulta.

2. Na área de texto do Editor de consultas, copie e cole a seguinte consulta SQL para criar uma nova tabela, que é uma instrução DDL.

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Selecione "Executar". A consulta cria uma nova tabela questions_2018_clustered no conjunto de dados stackoverflow do seu projeto com dados resultantes da execução de uma consulta na tabela do BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions. A nova tabela é particionada em "creation_date" e agrupada em cluster na coluna "tags".

Observação:essa etapa leva cerca de dois minutos para criar uma nova tabela. Aguarde antes de continuar para a próxima etapa.

Consultar a tabela em cluster com postagens do Stack Overflow de 2018

Agora que você criou uma tabela em cluster do BigQuery, vamos executar a mesma consulta novamente, desta vez na tabela particionada e em cluster, para retornar postagens do Stack Overflow com perguntas e títulos, além de algumas outras estatísticas, como número de respostas, comentários, visualizações e Favoritos. Siga estas etapas:

1. No canto superior direito do console do GCP, selecione Escrever nova consulta.
2. Na área de texto do Editor de consultas, copie e cole a consulta SQL a seguir:

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Selecione Executar com o armazenamento em cache do BigQuery desativado (consulte a seção anterior para desativar o cache do BigQuery). A consulta retorna perguntas do Stack Overflow criadas em janeiro de 2018 e marcadas como android, além da pergunta e de algumas outras estatísticas.
4. Nos resultados da consulta, você vai encontrar o tempo necessário para a conclusão e o volume de dados processados para gerar os resultados.

Com uma tabela particionada e em cluster, a consulta verificou menos dados do que uma tabela particionada ou não particionada. A forma como os dados são organizados por particionamento e clustering minimiza a quantidade de dados verificados pelos workers de slot, melhorando o desempenho da consulta e otimizando os custos.

7. Limpar

A menos que você planeje continuar trabalhando com seu conjunto de dados do Stack Overflow, exclua-o e exclua o projeto criado para este codelab.

Excluir o conjunto de dados do BigQuery

Para excluir o conjunto de dados do BigQuery, siga as etapas abaixo:

1. Selecione o conjunto de dados stackoverflow no painel de navegação à esquerda do BigQuery .
2. No painel de detalhes, selecione Excluir conjunto de dados.
3. Na caixa de diálogo Excluir conjunto de dados, insira stackoverflow e selecione Excluir para confirmar que você quer excluir o conjunto de dados.

Excluir o projeto

Para excluir o projeto do GCP criado para este codelab, siga estas etapas:

1. No menu de navegação do GCP, selecione IAM e Administrador.
2. No painel de navegação, selecione Configurações.
3. No painel de detalhes, confirme que seu projeto atual é o que você criou para este codelab e selecione Desligar.
4. Na caixa de diálogo Encerrar projeto, insira o ID do projeto (e não o nome) e selecione Encerrar para confirmar.

Parabéns! Agora você aprendeu

• Como usar a interface da Web do BigQuery para criar uma tabela com base em outras
• Como criar e consultar tabelas particionadas e em cluster
• Como o particionamento e o clustering otimizam a performance e os custos das consultas

Não foi necessário configurar ou gerenciar clusters para trabalhar com conjuntos de dados.