Particionamento e clustering no BigQuery

1. Introdução

O BigQuery é um data warehouse corporativo de baixo custo, totalmente gerenciado e em escala de petabytes para análise. O BigQuery funciona sem servidores, Você não precisa configurar e gerenciar clusters.

Um conjunto de dados do BigQuery fica em um projeto do GCP e contém uma ou mais tabelas. Esses conjuntos de dados podem ser consultados com SQL.

Neste codelab, você 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 a dados. O particionamento e o clustering são essenciais para maximizar totalmente o desempenho e o custo do BigQuery nas consultas em um intervalo de dados específico. Isso resulta em uma 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 IU da Web do BigQuery, é possível usar a ferramenta de linha de comando bq para executar operações nos 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, você precisa criar um projeto do GCP ou selecionar um atual.

Criar um projeto

Para criar um novo projeto, siga estas etapas:

1. Se você ainda não tiver 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 novo projeto no menu de seleção de projetos:

4. Digite o nome do projeto e selecione Criar. 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 da BBC News, repositórios do GitHub, Stack Overflow e da Administração Oceânica e Atmosférica Nacional dos EUA (NOAA, na sigla em inglês). Você não precisa carregar esses conjuntos de dados no BigQuery. Você só precisa abrir os conjuntos de dados para navegar e consultá-los no BigQuery. Neste codelab, você trabalhará com o conjunto de dados público do Stack Overflow.

Procurar o 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 IU 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 exibe informações sobre o conjunto de dados do Stack Overflow.
2. No painel de navegação , selecione bigquery-public-data. O menu é expandido para listar os conjuntos de dados públicos. Cada conjunto de dados inclui uma ou mais tabelas.
3. Role para baixo e selecione stackoverflow. O menu se expande para listar as tabelas no conjunto de dados do Stack Overflow.
4. Selecione selos para conferir o esquema da tabela. Anote os nomes dos campos na tabela.
5. Acima de "Nomes dos campos", clique em Visualizar para conferir dados de exemplo 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 pelos conjuntos de dados é uma boa maneira de entender os dados com os quais você está trabalhando, mas a consulta em conjuntos de dados é onde o BigQuery realmente se destaca. Nesta seção, ensinamos como executar consultas do BigQuery. Não é preciso saber nada de SQL por enquanto. Você pode copiar e colar as consultas abaixo.

Para executar uma consulta, siga estas etapas:

1. Próximo ao 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 abaixo. 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 postadas a cada ano.

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

4. Como criar uma nova tabela

Na seção anterior, você consultou conjuntos de dados públicos que o BigQuery disponibiliza. Nesta seção, você vai criar uma nova tabela no BigQuery usando uma tabela atual. Você criará uma nova tabela com dados de amostra da tabela posts_questions do conjunto de dados público do Stack Overflow e, em seguida, consultará a tabela.

Crie um novo conjunto de dados

Para criar e carregar dados de tabelas 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 lado direito, no painel de detalhes, selecione Criar conjunto de dados.

3. Na caixa de diálogo Criar conjunto de dados, digite stackoverflow em ID do conjunto de dados. Deixe todas as outras configurações padrão como estã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, pode criar uma nova tabela nele. Para criar uma tabela com dados de uma tabela existente, consulte o conjunto de dados de postagens do Stack Overflow de 2018 e grave os resultados em uma nova tabela, concluindo as seguintes etapas:

1. Próximo ao 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 abaixo 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 projeto com os dados resultantes da execução de uma consulta no conjunto de dados bigquery-public-data.stackoverflow.posts_questions do BigQuery Stack Overflow.

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

Consulte a nova tabela com postagens do Stack Overflow de 2018 (em inglês)

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 outras estatísticas, como número de respostas, comentários, visualizações e favoritos. Siga estas etapas:

1. Próximo ao 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 abaixo:

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 que estão marcadas como android junto com a pergunta e algumas outras estatísticas.
4. Por padrão, o BigQuery armazena os resultados da consulta em cache. Execute a mesma consulta e você vai notar que o BigQuery levou muito menos tempo para retornar os resultados, porque ele retorna resultados do cache.
5. Execute a mesma consulta novamente, mas desta vez com o armazenamento em cache do BigQuery desativado. Desativaremos o cache no restante do laboratório para que a comparação de desempenho seja justa com as 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, é exibido o tempo que ela levou para ser concluída e o volume de dados processados para receber os resultados.

5. Como criar e consultar uma tabela particionada

Na seção anterior, você criou uma nova 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 armazenamento em cache desativado e observamos o desempenho da consulta. Nesta seção, você vai criar uma nova tabela particionada a partir da mesma tabela posts_questions do conjunto de dados públicos 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 a hora de ingestão de dados, a coluna TIMESTAMP/DATE ou uma coluna INTEGER. Criaremos 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 existente, consulte o conjunto de dados de postagens do Stack Overflow 2018 e grave os resultados em uma nova tabela. Siga estas etapas:

1. Próximo ao 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 abaixo 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 nova tabela questions_2018_partitioned no conjunto de dados stackoverflow do projeto, com os dados resultantes da execução de uma consulta no conjunto de dados bigquery-public-data.stackoverflow.posts_questions do BigQuery Stack Overflow.

Observação:essa etapa leva cerca de 2 minutos para criar uma nova tabela. Aguarde antes de prosseguir 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 outras estatísticas, como número de respostas, comentários, visualizações e favoritos. Siga estas etapas:

1. Próximo ao 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 abaixo:

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. Verifique a seção anterior para desativar o cache do BigQuery. A consulta retorna perguntas do Stack Overflow criadas em janeiro de 2018 que estão marcadas como android junto com a pergunta e algumas outras estatísticas.
4. Nos resultados da consulta, é exibido o tempo que ela levou para ser concluída e o volume de dados processados para receber os resultados.

O desempenho da consulta com a tabela particionada é melhor do que o da tabela não particionada, porque o BigQuery remove as partições, ou seja, verifica apenas as partições necessárias, processando menos dados e com execução mais rápida. 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 armazenamento em cache desativado e observamos o desempenho da consulta com tabelas particionadas e não particionadas. Nesta seção, você vai criar uma tabela em cluster usando a mesma tabela posts_questions do conjunto de dados públicos do Stack Overflow e observar o desempenho da consulta.

Quando uma tabela é agrupada no BigQuery, os dados dela 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 no clustering de colunas. Esses valores são usados para organizar os dados em diversos blocos no armazenamento do BigQuery. Essa ordem determina a ordem de classificação dos dados. Quando novos dados são adicionados a uma tabela ou partição específica, o BigQuery executa o 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.

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

Nesta seção, você criará uma nova tabela particionada em creation_date e agrupada na coluna tags com base no padrão de acesso da consulta. Para criar uma tabela clusterizada com dados de uma tabela ou consulta existente, consulte a tabela de postagens do Stack Overflow de 2018 e grave os resultados em uma nova tabela, seguindo estas etapas:

1. Próximo ao 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 abaixo 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 projeto com os dados resultantes da execução de uma consulta na tabela do BigQuery Stack Overflowbigquery-public-data.stackoverflow.posts_questions. A nova tabela é particionada em create_date e agrupada no cluster na coluna de tags.

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

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

Agora que você criou uma tabela em cluster do BigQuery, execute 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 outras estatísticas, como número de respostas, comentários, visualizações e favoritos. Siga estas etapas:

1. Próximo ao 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 abaixo:

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. Verifique a seção anterior para desativar o cache do BigQuery. A consulta retorna perguntas do Stack Overflow criadas em janeiro de 2018 que estão marcadas como android junto com a pergunta e algumas outras estatísticas.
4. Nos resultados da consulta, é exibido o tempo que ela levou para ser concluída e o volume de dados processados para receber os resultados.

Com uma tabela particionada e em cluster, a consulta verificou menos dados do que uma tabela particionada ou não particionada. A maneira 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ê pretenda continuar trabalhando com o 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 do lado esquerdo do BigQuery .
2. No painel de detalhes, selecione Excluir conjunto de dados.
3. Na caixa de diálogo Excluir conjunto de dados, digite stackoverflow e selecione Excluir para confirmar que você quer excluir o conjunto de dados.

Excluir o projeto

Para excluir o projeto do GCP que você criou 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 se o projeto atual é o que você criou para este codelab e selecione Encerrar.
4. Na caixa de diálogo Encerrar projeto, insira o ID do seu projeto (não o nome dele) e selecione Encerrar para confirmar.

Parabéns! Agora você aprendeu

• Como usar a IU da Web do BigQuery para criar uma nova tabela com base em tabelas atuais.
• Como criar e consultar tabelas particionadas e em cluster
• Como o particionamento e o clustering otimizam o desempenho e os custos das consultas

Observe que você não precisou configurar ou gerenciar clusters para trabalhar com conjuntos de dados.