Google BigQuery — um mergulho nos fundamentos e algumas aplicações

Texto escrito por: Davi Félix, Hugo R. V. Angulo e Kauã Fillipe

Fonte da imagem: Safetec

Um cientista de dados, entre outras coisas, tem que lidar diariamente com tarefas de extração e análise de informações a partir de uma ou várias bases de dados com o objetivo de gerar valor para sua organização, propor uma solução para um problema ou realizar uma tomada de decisão orientada por dados.

Quando se trata de projetos individuais ou a nível de estudo, é comum utilizar conjuntos e bases de dados de plataformas como o Kaggle. Nesse contexto, o uso de bibliotecas disponíveis em Python é uma solução suficiente para executar essas tarefas. É possível, inclusive, visualizar métricas que resumem os dados de forma rápida e passar pelas linhas e colunas dos conjuntos de dados sem qualquer problema. Isso acontece porque o tamanho dos conjuntos de dados não é tão grande.

Em cenários mais reais, tratando-se de grandes corporações em especial, não é raro que um cientista de dados tenha que realizar essas tarefas sobre um conjunto de dados da ordem de TeraBytes! Estamos falando do contexto de Big Data, em que é necessário buscar outras soluções para trabalhar com conjunto de dados muito grandes.

O BigQuery é um serviço cuja importância surge nesse contexto. Ele é uma solução da Google Cloud Platform utilizada para o armazenamento, consulta e manipulação de grandes volumes de dados. Esse texto se propõe a ser um primeiro mergulho no vasto mundo do BigQuery e se divide da seguinte forma:

1. O que é a Google Cloud Platform?
2. Data Lakehouse
3. O que é o BigQuery?
4. Estrutura do BigQuery
5. Orçamento e faturamento no BigQuery
   a) Exemplo de faturamento
6. Sandbox do BigQuery
7. Acessando o BigQuery
8. Ingestão de dados no BigQuery pelo Console
9. Ingestão de dados no BigQuery de modo programático
   a) Autenticação para os serviços do Google Cloud
   b) Realizando a ingestão de dados
10. Tipos de dados
    a) Array
    b) Date
    c) Time
    d) Datetime
    e) Timestamp
    f) Interval
    g) Struct
11. Consulta de dados no BigQuery
    a) Linguagem SQL
    b) Consultando dados a partir do console
    c) Funções do Google SQL
    d) Operadores
    e) Consultando dados programaticamente
12. Visualização de dados do BigQuery com o Looker Studio
13. Conclusões

1. O que é a Google Cloud Platform?

A computação em nuvem (cloud computing) é a oferta de recursos e serviços de computação através da internet. A solução do Google que conta com produtos de cloud computing é justamente a Google Cloud Platform (GCP).

Dentre alguns dos produtos mais conhecidos da GCP estão o Compute Engine, que permite a criação de máquinas virtuais na infraestrutura do Google; Cloud Storage, para armazenamento de dados não estruturados; e BigQuery, para armazenamento de dados estruturados, por exemplo.

Fonte da imagem: PacktPub

2. Data Lakehouse

Um Data Warehouse é utilizado para armazenar dados estruturados, ou seja, dados que possuem uma estrutura padronizada e bem definida, podendo ser organizados, portanto, na forma de tabelas. Tipicamente, os dados em um Data Warehouse servem apenas para leitura e consulta. Nesse sentido, a princípio, ele é utilizado com o intuito de gerar relatórios e análises de produtos e negócios, em uma abordagem de inteligência empresarial (Business Intelligence). O BigQuery é uma ferramenta da GCP que pode ser utilizada como um Data Warehouse e é o foco deste texto.

A título de curiosidade, um Data Lake é utilizado para armazenar todos os tipos de dados, incluindo não somente os estruturados como também os semiestruturados e os não estruturados, que podem ser imagens, vídeos, etc. Isso é necessário quando se quer, por exemplo, processar e ter acesso imediato a um volume grande de dados sem ter que transformá-los previamente. O Cloud Storage é uma ferramenta da GCP que pode ser utilizada como Data Lake.

Há propostas recentes do uso de Data Lakehouses, que, como o nome sugere, seriam uma combinação dos usos de um Data Warehouse e de um Data Lake. A GCP também oferece uma solução desse tipo, em que o armazenamento dos dados ocorre tanto no Cloud Storage como no BigQuery e a análise de dados é feita no BigQuery, além de utilizar outros serviços da GCP em conjunto. Não discutiremos esse tópico no texto, mas não deixa de ser uma demonstração interessante de como os casos de uso de Data Lake e Data Warehouse têm evoluído e sendo combinados, ainda que não sejam termos intercambiáveis.

Fonte da imagem: Google Cloud

3. O que é o BigQuery?

Fonte: Giphy

Já sabemos que o BigQuery — carinhosamente apelidado de “BQ” ao longo do texto — pode ser utilizado como Data Warehouse. Mas, além disso, ele possui alguns outros casos de uso bem interessantes:

• Acessar dados não estruturados e semiestruturados por meio da ferramenta BigLake;
• Análise de dados geoespaciais;
• Integração com Planilhas Google (Google Sheets) para analisar dados de volume grande no Sheets utilizando ferramentas dessa plataforma;
• Construção de modelos de Machine Learning em dados estruturados, semiestruturados e não estruturados com a ferramenta BigQuery ML;
• Execução de análises do BigQuery em dados armazenados em outros serviços de armazenamento em nuvem, como Amazon S3 e Azure.

Para saber em mais detalhes sobre esses e outros recursos e casos de uso do BigQuery, não deixe de conferir a documentação oficial desse serviço, na qual esse texto é fortemente baseado.

4. Estrutura do BigQuery

Fonte da imagem: Google Cloud

O BigQuery se estrutura de forma hierárquica. Um projeto (project) da Google Cloud é a entidade máxima que centraliza, organiza e aloca recursos da GCP. Para usar os serviços da GCP, é preciso primeiro criar um projeto ao qual serão atribuídos os serviços utilizados. Em particular, os projetos que armazenam e computam os dados podem ser diferentes.

A nível de armazenamento, o conjunto de dados (dataset) é a entidade que está imediatamente abaixo de um projeto. Um conjunto de dados é utilizado para a organização de tabelas e está relacionado a um local geográfico.

As tabelas (tables) são utilizadas para armazenar dados e são organizadas em linhas e colunas. O esquema (schema) de uma tabela define algumas regras para essa organização. Na especificação do esquema, são determinados os nomes e a descrição das colunas, os tipos de dados e modos para as colunas e de arredondamento, por exemplo.

A nível de computação, entende-se um job do BigQuery como uma ação ou operação que é executada dentro do BigQuery. Um job pode ser de carregamento (load), exportação ou extração (export), consulta (query) ou cópia de dados (copy). Ações mais rápidas dentro do BigQuery, como a listagem de recursos, não são consideradas jobs. Além disso, cada execução de job acarreta uma cobrança na conta de faturamento associado a um projeto da GCP.

5. Orçamento e faturamento no BigQuery

Fonte: Giphy

A cobrança do BigQuery é baseada principalmente em dois componentes: armazenamento e processamento de consultas. Aqui está uma visão geral de como essas cobranças funcionam:

1. Armazenamento:

• Armazenamento ativo: Você é cobrado com base na quantidade de dados armazenados em tabelas e partições do BigQuery. A cobrança é feita por gigabyte (GB) armazenado por mês.
• Armazenamento de longo prazo: Se os dados em uma tabela ou partição não forem modificados por 90 dias consecutivos, eles são automaticamente movidos para o armazenamento de longo prazo, que tem uma taxa mais baixa por GB por mês.
• Armazenamento de metadados: O BigQuery armazena informações sobre tabelas e esquemas, como nomes de colunas e tipos de dados. Essas informações são armazenadas como metadados, e você não é cobrado pelo armazenamento desses metadados.
• Carregamento e exportação de dados: Carregar e exportar dados para e do BigQuery geralmente é gratuito, mas há algumas exceções e limitações que você pode encontrar na documentação oficial.

1. Processamento de consultas:

• Pay-as-you-go (pague pelo uso): Você paga com base na quantidade de dados processados pelas suas consultas. O custo é calculado por terabyte (TB) processado. Cada projeto no BigQuery tem uma quantidade gratuita de processamento de consultas por mês.
• Reservas de slots (recursos de processamento): Alternativamente, você pode optar por reservar slots do BigQuery, que fornecem capacidade de processamento dedicada para suas consultas. Essa opção permite que você pague uma taxa fixa com base no número de slots reservados, e você pode escolher entre diferentes planos de compromisso (por exemplo, mensal ou anual).

Além disso, o BigQuery também oferece uma opção de streaming de dados em tempo real, pela qual você paga com base na quantidade de dados inseridos no serviço. Essa opção é útil quando você deseja analisar dados em tempo real à medida que eles chegam.

Exemplo de orçamento

Os preços podem variar dependendo da região em que os dados estão armazenados e de onde as consultas são executadas. As informações de pricing do BigQuery também podem ser consultadas na documentação oficial da GCP.

Suponhamos que faremos um orçamento mensal com 20GB de armazenamento e 2TB de consulta por mês:

Armazenamento:

1. Suponhamos que a taxa de armazenamento ativo seja de US$ 0,020 por GB por mês. Para 20 GB de dados armazenados:

20 GB * US$ 0,020/GB = US$ 0,40 por mês

Processamento de consultas:

1. Suponha que a taxa de processamento seja de US$ 5,00 por TB. Para 2 TB de consultas por mês:

2 TB * US$ 5,00/TB = US$ 10,00 por mês

Somando ambos os componentes, o preço para essa situação hipotética seria:

Armazenamento + Processamento de consultas
US$ 0,40 + US$ 10,00 = US$ 10,40 por mês

Agora levando em consideração uma simulação com valores reais usando a calculadora de pricing da GCP, o custo estimado para 20 GB de armazenamento e 2 TB de consulta por mês seria de aproximadamente US$ 11,48 para a localização em São Paulo, como mostra a figura abaixo (imagem de 27/11/2023).

Calculadora de Pricing com um exemplo de orçamento em 27/11/2023.

6. Sandbox do BigQuery

O Sandbox do Google Cloud BigQuery é um ambiente gratuito e limitado oferecido pelo Google para permitir que os usuários experimentem e aprendam sobre o BigQuery sem a necessidade de fornecer métodos de pagamento ou comprometer-se com um plano pago.

No Sandbox do BigQuery é possível executar consultas SQL, carregar e exportar dados, criar, atualizar, excluir tabelas e conjuntos de dados. No entanto, existem algumas limitações em comparação com a versão paga do BigQuery:

1. Capacidade de processamento de consultas: A cota diária de processamento de consultas é de 1 TB, enquanto no BigQuery pago, o limite é maior e pode ser aumentado conforme necessário.
2. Armazenamento: No Sandbox, você tem 10 GB de armazenamento ativo gratuito.
3. Conjuntos de dados: Os conjuntos de dados no Sandbox são excluídos automaticamente após 60 dias de inatividade. Na versão paga, os conjuntos de dados não são excluídos automaticamente.
4. Recursos de segurança e governança: O Sandbox não oferece suporte a alguns recursos avançados de segurança e governança, como a criptografia de dados com chaves.

A documentação oficial do Sandbox do BigQuery pode ser consultada no link: https://cloud.google.com/bigquery/docs/sandbox

7. Acessando o BigQuery

Para utilizar o BigQuery, é necessário ter uma conta ativa na Google Cloud Platform. Para isso, faça login com a sua conta do Google na plataforma e acesse o Console da GCP.

No Console, clique na aba ‘Selecione um projeto’ e depois em ‘Novo projeto’. Dê um nome ao seu projeto da GCP e selecione a organização à qual está vinculado, se esse for o caso. Na aba ‘Selecione um projeto’, escolha o projeto que você acabou de criar e veja o Painel exibido. Por meio dele, é possível monitorar a atividade do Projeto e se manter atualizado de informações pertinentes ao uso que é feito dos recursos da GCP. No Painel, vá até a seção de Recursos e clique em BigQuery. Alternativamente, você pode usar a barra de pesquisa ou o menu de navegação para acessar o BigQuery.

8. Ingestão de dados no BigQuery pelo console

Uma parte essencial para que o BigQuery seja utilizado é a ingestão dos dados na plataforma, ou seja, o processo de passar dados de diversas fontes para dentro de tabelas no BQ. É possível realizar essa tarefa tanto pelo console, ou seja, a partir da interface gráfica no site da GCP, ou de maneira programática usando as bibliotecas de cliente do BigQuery em qualquer linguagem de programação suportada. Essas bibliotecas são um conjunto de ferramentas de software pré-desenvolvidas (como classes, funções, etc.) que facilitam a interação via código com os serviços do Google Cloud Platform.

Fonte da imagem: Reddit

Para inserir dados a partir do console, primeiramente é preciso acessar o BigQuery dentro do console da GCP. Em seguida, selecione a opção “+ ADICIONAR” que aparecerá no painel Explorer.

Imagem com destaque no botão de adicionar dados no console do BigQuery.

Na próxima página, será possível escolher diferentes fontes a partir das quais os dados podem ser inseridos, como um arquivo local ou algum arquivo armazenado no Google Cloud Storage, que já foi mencionado anteriormente como um serviço de Data Lake.

Escolhemos a opção de arquivo local. Na página seguinte, a primeira seção é a de Origem, onde ainda é possível escolher mais opções para a origem dos dados — como Drive ou Amazon S3 — e o formato do arquivo de dados que será ingerido. No texto, usaremos um arquivo no formato CSV, com relações entre países e seus continentes. Esses mesmos dados serão usados posteriormente nos exemplos de consulta. Para escolher o arquivo, ainda na seção Origem, selecione a opção “Procurar” e escolha o arquivo no seu armazenamento interno.

Imagem mostrando os campos da seção Origem no formulário de ingestão de dados pelo console.

Na seção Destino, escolhemos para onde os dados serão alocados dentro do BQ. Para isso, selecionamos um projeto, um conjunto de dados, e uma tabela — conceitos vistos acima na estrutura do BQ. Caso o seu projeto ainda não tenha nenhum conjunto de dados, é possível selecionar a opção “CRIAR CONJUNTO DE DADOS”, que irá abrir um formulário ao lado da tela para que isso seja feito. No campo “Tabela”, é necessário escolher o nome para a tabela onde os dados serão ingeridos.

Na seção Esquema, é necessário detalhar como será o formato da tabela, especificando, para cada campo: seu nome, formato (tipo de dado guardado pela tabela), modo (“nullable” — pode ser nulo –, “repeated” — a coluna é composta por vários valores com o tipo especificado — ou “required” — indica que o campo é obrigatório), tamanho e descrição. Note que tamanho e descrição são opcionais.

Imagem destacando a parte do formulário de ingestão de dados no console usada para para especificar o esquema da tabela.

Também é possível que o esquema seja identificado automaticamente de acordo com o formato do arquivo enviado; para isso, basta marcar a opção “Detectar automaticamente”. Caso essa opção seja selecionada, nomes arbitrários serão dados para cada coluna, o que não é muito recomendado.

Fonte: X

Seguindo esses passos e usando estes dados, foi possível criar a tabela country_continent no BQ.

Tabela “country_continent” criada.

9. Ingestão de dados no BigQuery de modo programático

Fonte: iFunny

No caso acima, a tarefa de ingestão de dados é bem simples e poderia ser feita manual e diretamente, por uma pessoa, no Console da GCP. No entanto, algumas atividades mais complicadas exigem o uso de aplicativos e programas, escritos em alguma linguagem de programação, para serem realizadas. Por exemplo, se quisermos automatizar e realizar diariamente a ingestão de dados no BigQuery, pode ser que seja mais prático, dependendo do projeto, construir um script que seja executado todo dia para fazer isso. São nesses casos que a ingestão programática de dados se mostra essencial.

Autenticação para os serviços do Google Cloud

Para que seja possível gerenciar seus dados dentro do BigQuery a partir de um código de programação, é necessário algum tipo de autenticação para garantir as permissões relacionadas ao uso dos serviços do BigQuery. A ferramenta usada para isso será o Google Cloud CLI (gcloud), com a qual é possível autenticar-se em uma conta da GCP cujas credenciais serão utilizadas para todas as operações realizadas posteriormente.

Primeiramente, é preciso instalar a Google Cloud CLI seguindo as instruções da documentação de forma correspondente a cada sistema operacional.

Depois de instalada a ferramenta de linha de comando, execute o comando gcloud init no seu terminal e siga as instruções, autenticando-se na sua conta da GCP ao escolher a opção “Y” para a pergunta abaixo.

Terminal depois de digitado o comando ‘gcloud init’.

Em seguida, selecione o projeto que deseja utilizar, ou seja, aquele projeto que contém o conjunto de dados no qual você deseja realizar a ingestão de dados.

Opções de projeto para serem escolhidos ao inicializar a gclou CLI.

Por fim, é preciso configurar as credenciais padrão que serão utilizadas para autenticar as operações de todas as bibliotecas de cliente da Google Cloud Platform, incluindo as do BigQuery. Para isso, execute o comando gcloud auth application-default login e realize o login na aba do navegador que abrir.

Realizando a ingestão de dados

Os primeiros passos para realizar a ingestão de dados no BigQuery em um programa em Python é importar os módulos necessários.

from google.cloud import bigquery
import pandas as pd

Nesta parte do texto, utilizaremos dataframes com dados de viagens de bicicletas na cidade de Nova Iorque realizadas utilizando um sistema de compartilhamento de bicicletas. Os dados que serão utilizados são referentes aos anos de 2014 e 2015 e somam quase 500MB.

Primeiramente, vamos instalar um objeto de Cliente do BigQuery. Isso é importante para que as demais operações e jobs possam ser executados corretamente no Projeto certo.

# Instancia um objeto de Cliente do BigQuery
client = bigquery.Client()

Em seguida, é possível utilizar uma função que realiza chamadas à API para criar um conjunto de dados do BigQuery no seu projeto. Ela tem, como argumentos, o nome dado ao conjunto de dados e a localização geográfica em que ficará o dataset.

def cria_dataset(dataset_name: str, location: str) -> bigquery.Dataset:
"""
Cria um dataset no BQ e retorna o objeto correspondente
"""
  # Cria ID do conjunto de dados
  dataset_id = f"{client.project}.{dataset_name}"
  # Cria uma instância do BQ de conjunto de dados
  dataset = bigquery.Dataset(dataset_id)
  # Atribui uma localização válida do BQ ao conjunto de dados
  dataset.location = location
  # Cria o dataset
  return client.create_dataset(dataset, timeout=30)

É possível conferir a lista de localizações possíveis na documentação.

Depois que o dataset é criado, a função acima retorna um objeto do tipo Dataset que representa o conjunto de dados criado pela API. É possível acessar diversas propriedades, como o nome e o projeto relacionado a esse dataset.

Após isso, pode-se criar uma função que realiza a ingestão de dados e retorna o job associado.

def carrega_df_no_bq(project_name: str, database_name: str, table_name: str, dataframe: pd.DataFrame, load_job_config: bigquery.LoadJobConfig=None):
  """
  Faz a ingestão dos dados no BQ e retorna o Job associado
  """
  # Cria ID para a tabela
  table_id = f"{project_name}.{database_name}.{table_name}"
  # Realiza a ingestão dos dados de um Dataframe do Pandas para uma tabela do BigQuery
  return client.load_table_from_dataframe(dataframe, table_id, job_config=load_job_config)

Para os dataframes utilizados, em específico, é importante retomar algumas colunas, pois, durante a especificação do esquema, não são aceitos nomes de coluna que utilizam espaço. São válidos apenas letras, números e sublinhados.

renaming_dict = {
  'start station id': 'start_station_id', 
  'start station name': 'start_station_name',
  'start station latitude': 'start_station_latitude', 
  'start station longitude': 'start_station_longitude',
  'end station id': 'end_station_id', 
  'end station name': 'end_station_name', 
  'end station latitude': 'end_station_latitude',
  'end station longitude': 'end_station_longitude', 
  'birth year': 'birth_year'
}

Como os dados estão divididos em vários arquivos, é necessário fazer um loop para juntar todos esses dataframes em apenas um que será ingerido no BQ. Assumindo que todos os arquivos CSV com os dados de 2014 e 2015 estão em uma pasta “./data’, temos:

final_df = pd.DataFrame()
for df_name in os.listdir('./data'):
  # Escolhe o nome da tabela de acordo com o nome do dataset
  table_name = 'bike_2013' if '2013' in df_name else '2014'
  df = pd.read_csv(os.path.join("data", df_name))
  df.rename(columns=renaming_dict, inplace=True) # Renomeia as colunas
  final_df = pd.concat([final_df, df]) # Junta o dataset do loop com o atual

Tendo o dataset final, podemos criar um conjunto de dados no BQ e realizar a ingestão:

bq_dataset = cria_dataset("bikes", "us-east5")
load_job = carrega_df_no_bq(bq_dataset.project, bq_dataset.dataset_id, "bikes_2013", final_df)

No exemplo mostrado, o schema da tabela foi criado automaticamente. No entanto, no objeto LoadJob retornado, podemos ver o schema que foi criado para a tabela com:

print(load_job.schema)

É retornada uma lista de objetos SchemaField informando, respectivamente: nome, tipo, modo, valor padrão, descrição, etc. O significado de cada campo pode ser visto na documentação do objeto referenciada logo acima.

Ainda é possível configurar como o load irá acontecer a partir do objeto LoadJobConfig, que é passado no atributo job_config da função client.load_table_from_dataframe(). Algumas configurações importantes que podem ser alteradas são:

• autodect: booleano que, se verdadeiro, indica que o schema da tabela deve ser determinado automaticamente;
• create_disposition: pode ser “CREATE_IF_NEEDED” (valor padrão), indicando que uma tabela será criada caso a tabela informada não exista, ou “CREATE_NEVER”, indicando que os dados só serão ingeridos caso a tabela informada exista.
• write_disposition: pode ser “WRITE_APPEND” (valor padrão), indicando que os dados informados serão inseridos na tabela mantendo os dados existentes; “WRITE_TRUNCATE”, indicando que os dados e o schema existentes serão sobrescrevidos; ou “WRITE_EMPTY”, que indica que os dados serão inseridos apenas se a tabela estiver vazia e, caso contrário, um erro será retornado.
• schema: lista de campos indicando o esquema da tabela a ser inserida, em que cada campo deve ser informado como mostra a documentação.

Vale ressaltar que, principalmente ao ingerir dados via csv, é sempre uma boa prática detalhar o schema completo ao invés de usar a opção autodetect, uma vez que isso evita problemas de tipagem e inconsistência de dados.

Segue um exemplo de como seria a ingestão de dados feita anteriormente no console, mas agora de modo programático com as funções que já criamos ao longo do texto:

continents_dataframe = "https://gist.githubusercontent.com/davifelix5/88155ba205b22c26c1f994641b5f6b8a/raw/3298666913fd3f307e5e9a41d7ed26f0f572c068/country_continent.csv"
country_continent = pd.read_csv(continents_dataframe, header=None)
country_continent.columns = ['country_code', 'continent', 'country_name']
load_job_config = bigquery.LoadJobConfig(
    schema= [
        {'name': 'country_code', 'type': 'STRING', 'mode': 'NULLABLE'},
        {'name': 'country_name', 'type': 'STRING', 'mode': 'NULLABLE'},
        {'name': 'continent', 'type': 'STRING', 'mode': 'NULLABLE'},
      ]
)
carrega_df_no_bq(
  project_name=bq_dataset.project, 
  database_name=bq_dataset.dataset_id, 
  table_name="country_continent", 
  dataframe=country_continent,
  load_job_config=load_job_config
)

Também podemos ver pelo trecho acima um exemplo de como informar o schema.

10. Tipos de dados

Durante a parte de ingestão, foi citado que cada campo em uma tabela pode ter um formato/tipo. Nessa seção, iremos falar sobre alguns desses tipos de dados, como strings, números, datas, etc. Listamos alguns dos principais tipos e mais detalhes sobre cada um deles podem ser encontrados na documentação.

Array

O tipo de dado array, no BigQuery, é uma lista ordenada de zero ou mais elementos que sejam do mesmo tipo, contanto que não sejam outros arrays. Ou seja, podemos ter arrays de INT, BYTES, STRING, STRUCT, DATE, etc., mas não podemos ter arrays de arrays.

Um array pode ter elementos nulos, e uma expressão do tipo array pode ser usada na cláusula ORDER BY para ordenação, mas não pode ser usada em cláusulas de GROUP BY, PARTITION BY e DISTINCT para agrupamento. Fique tranquilo, os significados dessas cláusulas ainda serão aprendidos durante o texto. Além disso, um array não pode ser comparado diretamente a outro array. Um exemplo simples de array é:

Date

O tipo DATE representa uma data do calendário Gregoriano. Ela pode ser representada em vários formatos diferentes, mas, a princípio, não há indicações de horas, por exemplo. É, portanto, uma representação que se atém a dia, mês e ano. Abaixo, um exemplo do tipo DATE.

Time

O tipo TIME representa um horário do dia, em termos de horas, minutos, segundos, etc, como ilustrado abaixo.

Datetime

O tipo DATETIME representa uma data associada a um tempo conforme o calendário Gregoriano. Há indicação de dia, mês, ano e também de hora, minuto, segundo, etc, como mostrado abaixo.

Timestamp

O tipo TIMESTAMP é um carimbo de hora. Isto é, é uma representação global de um instante de tempo. Como se ele fosse um ponto absoluto, que não depende de nenhuma convenção ou fuso horário. É um número calculado em referência a um ponto inicial fixo e que está associado a um tempo.

Porém, um TIMESTAMP está associado a uma data, um horário e um fuso horário para facilitar a compreensão humana. Ou seja, um mesmo instante associado a um TIMESTAMP pode possuir diferentes representações a depender do fuso horário. A ilustração abaixo demonstra um TIMESTAMP.

É importante notar que a diferença básica entre um TIMESTAMP e um DATETIME é que o TIMESTAMP está associado a uma zona temporal (timezone) específica, enquanto o DATETIME não.

Interval

O tipo INTERVAL representa um intervalo de tempo. Pode ser dado um intervalo de tempo em dias (DAY), meses (MONTH), anos (YEAR), horas (HOUR), minutos (MINUTE), segundos (SECOND), etc., e pode ser associado a um valor positivo ou negativo.

Struct

Um struct é uma estrutura que contém diversos campos ordenados, cada um com um tipo definido e um nome (opcional). Por exemplo, um struct com campos latitude e longitude, ambos do tipo float64 pode ser criado da seguinte maneira: STRUCT<latitude float64, longitude float64>(-23.5693988, -46.6318049).

11. Consulta de dados no BigQuery

Linguagem SQL

A maneira mais comum de consultar dados armazenados de forma estruturada é feita a partir de consultas (ou queries) escritas seguindo o que é chamado de SQL (Structured Query Language). Essa linguagem foi criada para padronizar a forma de manipular bancos de dados relacionais, definindo comandos — as chamadas queries — para, dentre outras operações:

• consultar registros (Data Query Language — DQL);
• modificar registros (Data Manipulation Language — DML);
• estruturar o banco de dados (Data Definition Language — DDL).

Bancos de dados relacionais são aqueles em que os dados seguem uma estrutura fixa e bem definida, seguindo a ideia de um Data Warehouse, já mencionado anteriormente. Isso traz benefícios como uma grande consistência dos dados, mas pode trazer dificuldades em relação a escalabilidade, principalmente se o banco não é projetado de forma eficiente. Em bancos relacionais, de maneira semelhante ao BigQuery, os dados — registros — são armazenados em tabelas (tabela “clientes” e tabela “cursos”, por exemplo), sendo que cada tabela tem um conjunto de colunas (“nome”, “CPF”, “data-nascimento”, etc). Essas tabelas podem se relacionar com outras de modo a permitir o armazenamento e consulta eficiente de informações que envolvam registros em diferentes tabelas (cursos assinados pelos clientes, por exemplo). Logo, frente a essas semelhanças fundamentais, é fácil notar que, assim como a linguagem SQL é extremamente útil para manipular bancos de dados relacionais, ela também será extremamente adequada para manipular registros e tabelas no BigQuery.

É importante observar que, apesar do padrão SQL ser base para qualquer banco relacional, as consultas não são totalmente padronizadas, havendo diferentes dialetos de SQL para cada SGBD (Sistema de Gerenciamento de Banco de Dados). Isso não seria diferente para o BigQuery, principalmente pelo fato de ele não se tratar realmente de um banco de dados relacional clássico. Assim, o dialeto utilizado pelo BigQuery é chamado de Google SQL, do qual trataremos mais detalhadamente em breve.

Consultando dados a partir do console

Agora vamos colocar em prática o uso do Google SQL para consultar dados a partir do console do BigQuery. Para brincar um pouco com o console do BigQuery, vamos usar o BigQuery Sandbox, que já foi comentado acima, e um conjunto de dados público do Departamento do Censo dos Estados Unidos contendo algumas informações — como população média, taxas de fertilidade e de mortalidade e expectativa de vida — em diversos países.

Para acessar o conjunto de dados mencionado, pesquise por `census_bureau_international` na barra de pesquisas do painel “Explorer” e, caso nenhum resultado apareça, clique na opção “Pesquisar todos os projetos”. O resultado deve ser algo parecido com o que está retratado abaixo:

Pesquisando o conjunto de dados públicos que será utilizado.

Clicando no conjunto de dados observado no painel Explorer, é possível ver mais informações sobre ele, principalmente no campo “Descrição”. Além disso, clicando na seta do lado esquerdo do nome do conjunto de dados, podemos ver todas as tabelas contidas nele. Selecionando cada uma das tabelas, é possível ver detalhes sobre elas, como o esquema, já definido acima, e um preview dos dados armazenados na aba “Visualizar”.

Painel mostrando dados da tabela.

Para efeitos de esclarecimento durante a leitura do Turing Talks, quando introduzirmos uma query, as partes em que aparecem o padrão […] devem ser completadas por quem está elaborando a query, não fazendo realmente parte de sua sintaxe.

Vamos começar com uma consulta simples para selecionar alguns dados da tabela country_names_area. Para criar uma nova query, clique no “+” (indicado em amarelo) que aparece na parte superior do painel principal.

Botão para criar uma nova query

A query usada para selecionar, ou seja, consultar elementos do banco de dados (DQL) é escrita SELECT […] FROM […] (OBS: é comum para quem opera com SQL usar letras maiúsculas para escrever as palavras reservadas da linguagem, embora a sintaxe com letras minúsculas também seja aceita). O segundo parâmetro recebido pela query indica de qual tabela os dados serão selecionados e o primeiro parâmetro indica quais campos desta tabela serão mostrados na consulta. Caso você deseja selecionar todos os campos, não é necessário indicá-los, basta usar a notação *. Seguem alguns outros parâmetros que podem ser usados com a cláusula SELECT:

• SELECT DISTINCT […] FROM […] irá descartar dados duplicados do resultado
• SELECT * (EXCEPT […]) FROM […] irá mostrar todas as colunas menos a especificada depois do EXCEPT
• Para mostrar um nome diferente do nome original da coluna no resultado, é possível usar a cláusula AS [novo_nome], apelidando a coluna. Por exemplo SELECT country_name AS nome FROM […].

Fonte: blazesql

Para comentar suas queries, ou seja, adicionar pequenas explicações no texto que não serão consideradas no processamento da query, utiliza-se “--”.

As tabelas são indicadas por seus IDs, que podem ser obtidos clicando no botão de opções do lado direito do nome da tabela e selecionando a opção “Copiar ID”. Para escrever o ID, é necessário colocá-lo entre crases, o que cobre a aparição de caracteres especiais. Logo, a query para selecionar todos os dados da tabela `country_names_area`, mostrando todos os seus campos, fica:

--seleciona todos os campos da tabela Turing
SELECT * FROM `bigquery-public-data.census_bureau_international.country_names_area` 

Antes de executá-la, é interessante verificar a quantidade de dados que essa query irá processar. Neste momento, isso não importa muito por estarmos usando o Sandbox; no entanto, como vimos anteriormente, a quantidade de dados processada é importante para a cobrança feita no Big Query, o que faz com que uma query mal executada possa custar boas “doletas”. A informação da quantidade de dados processada é mostrada na parte superior direita do painel principal assim que a query é digitada corretamente. Além disso, é nesse mesmo lugar que um erro será mostrado caso a query não tenha sido digitada corretamente.

Quantidade de dados processados pela query.

Executando a query, é possível ver seus resultados de forma tabulada na parte inferior do painel principal. Nessa parte do painel, é possível:

• Mostrar o resultado no formato JSON, o que pode ser interessante caso eles venham a ser usados em alguma aplicação ou automação;
• Fornecer detalhes de execução, como o tempo decorrido para a execução da query;
• Ter uma breve visualização dos dados selecionados a partir da opção “Gráfico”.

Resultados de uma query.

Parabéns! Você realizou sua primeira query no console do BQ!

Fonte: tenor

É interessante notar que queries também podem ser salvas (botão “Salvar”), caso seja necessário executá-las novamente, ou programadas (botão “Programação”), caso seja necessário executá-las de forma recorrente, com seus resultados sendo enviados para uma outra tabela especificada.

Na parte inferior aos resultados da consulta, também é possível ver o histórico de queries executadas por você em todos os seus projetos ou apenas no projeto atual. Basta selecionar a seta apontando para cima localizada no canto inferior direito da tela.

No entanto, convenhamos que essa primeira query não foi lá tão útil. Não é tão comum querer selecionar todos os dados de uma tabela no BQ. As tabelas geralmente contém MUITOS dados, e raramente precisamos de todos eles. Além disso, não é uma boa prática desperdiçar processamento de dados selecionando todas as colunas da tabela quando isso não é necessário. Também é comum querer juntar dados de outras tabelas, como descobrir a densidade demográfica média dos países em um determinado ano a partir das tabelas `midyear_population` e `country_names_area`.

Agora, veremos como relacionar dados de diferentes tabelas, além de ordenar, filtrar, ordenar e limitar a quantidade de dados selecionados.

Para filtrar os dados, é utilizada a cláusula WHERE seguida de uma expressão que retorna um resultado booleano. Por exemplo, para selecionar apenas países cuja área é maior ou igual a 8.000.000 (oito milhões de) quilômetros quadrados, usa-se a cláusula WHERE country_area >= 8000000.

Para ordenar os resultados, usa-se a cláusula ORDER BY […] [ASC ou DESC] . O segundo parâmetro indica se a ordenação deve ser crescente ou decrescente (caso ele não seja passado, o padrão é ASC), enquanto o primeiro parâmetro indica uma coluna ou uma expressão a partir da qual a ordenação será feita. Por exemplo, para ordenar de forma decrescente a partir da área do país, usa-se ORDER BY country_area DESC. Um detalhe importante é que quando mais de um elemento é informados como parâmetro do ORDER BY […], a ordenação prioritária (feita por último) é para os elementos mais à esquerda.

Usando esses conceitos em uma mesma query, temos uma seleção dos países com área maior ou igual a 8.000.000 km² ordenados de forma decrescente pela sua área. A query e seu respectivo resultado estão apresentados abaixo:

SELECT
*
FROM `bigquery-public-data.census_bureau_international.country_names_area`
WHERE country_area >= 8000000
ORDER BY country_area DESC

Países com área maior ou igual a 8.000.000 km² ordenados de forma decrescente pela sua área.

Podemos perceber que essa query seleciona os cinco maiores países do mundo. Mas como obter, por exemplo, os 15 menores países do conjunto de dados sem ter que se preocupar em filtrar por área? Para um caso como esse, usa-se a cláusula LIMIT […], que trunca o resultado de uma query e a deixa com a quantidade de registros especificadas no primeiro parâmetro. A query que faz a seleção mencionada e seu resultado estão mostrados abaixo:

SELECT
country_name, country_area
FROM `bigquery-public-data.census_bureau_international.country_names_area`
ORDER BY country_area
LIMIT 15

Uso da cláusula LIMIT para truncar a quantidade de linhas selecionadas.

E se quiséssemos selecionar os países do quinto ao décimo lugar em tamanho no conjunto de dados? Para isso, é possível usar a cláusula LIMIT em conjunto com a cláusula OFFSET […], que especifica uma quantidade de linhas a serem ignoradas no começo do resultado — abordagem útil para paginação de resultados. A query que faz a seleção mencionada e seu resultado estão mostrados abaixo, sendo que a cláusula `OFFSET` é usada para ignorar os 4 primeiros maiores países:

SELECT country_name, country_area
FROM `bigquery-public-data.census_bureau_international.country_names_area`
ORDER BY country_area DESC 
LIMIT 6 -- trunca a consulta em 6 linhas
OFFSET 4 -- tira os primeiros 4 resultados

Países do quinto ao décimo lugar em tamanho do mundo

Algo que também é muito interessante para analisar os dados é agrupá-los. Por exemplo, na tabela age_specific_fertility_rates, temos taxas de fertilidade para o mesmo país tomadas em diferentes anos. Para calcular a média das taxas de fertilidade nos anos registrados para cada um dos países, é necessário agrupar os dados por país. Para isso, utiliza-se a cláusula GROUP BY […], que recebe como parâmetro as colunas a partir das quais os dados devem ser agrupados. Junto com o agrupamento, utiliza-se funções de agregação, responsáveis por retornar um único valor baseando-se em vários dados; por exemplo, depois de agrupar os dados por país, é necessário tomar um único valor representando todos os dados que compõem cada grupo para mostrá-lo no resultado. Dentre as formas de fazer isso estão a soma de todos os valores, a média, o valor mínimo, o valor máximo, etc. Para realizar esse tratamento de uma query, usamos as funções de agregação — SUM, AVG, MIN, MAX, etc. No caso dado como exemplo, usaremos a média, obtendo a query:

SELECT
country_name, AVG(total_fertility_rate)
FROM
`bigquery-public-data.census_bureau_international.age_specific_fertility_rates`
GROUP BY country_name

É possível perceber que a função AVG é chamada recebendo como parâmetro o nome da coluna em que a agregação será feita. No BigQuery, como veremos a seguir, não existem apenas as funções de agregação, mas diferentes tipos de funções, que também podem receber seus parâmetros específicos, para manipular os dados da forma necessária em determinada query.

Assim como a cláusula WHERE é usada para filtrar linhas em uma consulta, é possível utilizar a cláusula HAVING para filtrar grupos criados a partir da cláusula GROUP BY. Caso seja necessário consultar apenas os países cuja taxa de fertilidade média seja maior que 5, seria adicionado HAVING AVG(total_fertility_rate) > 5 ao final da query.

Um caso de agrupamento que também pode aparecer é quando há uma categoria e uma subcategoria (por exemplo, país e continente) das quais é necessário obter dados simultaneamente. Agrupando pelas categorias, não é possível obter dados relativos unicamente às subcategorias, e, agrupando pelas subcategorias, não é possível obter dados relativos a cada uma das categorias como um todo. Para lidar com essa situação, é possível utilizar o GROUP BY ROLLUP […], que realiza diferentes agrupamentos de maneira gradativa para cada uma das colunas passadas como parâmetro e depois junta os resultados.

Por exemplo, caso seja usado GROUP BY ROLLUP (continente, país), primeiro haverá um agrupamento juntando todos os registros da tabela (conjunto {}), outro agrupamento que junta todos os registros com o mesmo continente (conjunto {continente}) e, por fim, outro agrupamento com todos os registros que têm o mesmo país e mesmo continente ({continente, país}), sendo esses resultados concatenados no resultado final. Um exemplo de uso dessa cláusula será dado posteriormente.

Quando resultados de diferentes agrupamentos são concatenados, note que o valor que não foi usado para fazer o agrupamento de um determinado subconjunto do resultado não irá aparecer nesse subconjunto, ficando NULL. Por exemplo, se são concatenados resultados do agrupamento por país e por continente, nas linhas em que são mostrados agrupamentos por continentes, o valor dos países é nulo.

Um caso mais genérico do GROUP BY ROLLUP seria o uso da cláusula GROUP BY GROUPING SETS (col1, col2, …). A partir dela, é realizado um agrupamento separado para cada coluna informada no parâmetro e depois cada um dos resultados é concatenado: primeiro os valores são agrupados por {col1}, depois por {col2} e assim por diante, formando uma única tabela de resultado. Dentro dos grouping sets, é possível definir ROLLUP(col1, col2, …), que gerará grupos da forma{col1}, {col1, col2}, {col1, col2, col3}, (…) — como no GROUP BY ROLLUP — , ou também CUBE(col1, col2, …), que gerará grupos contendo todas as combinações possíveis de {col1, col2, …}, incluindo o conjunto vazio. Note que da mesma forma que é possível fazer o GROUP BY ROLLUP, também é possível fazer o GROUP BY CUBE.

Uma abordagem parecida com os exemplos de agrupamento estudados acima é o uso das window functions (funções de janela), no entanto, enquanto o GROUP BY retorna apenas um valor para cada agrupamento de linhas, as window functions, apesar de também calcularem seu resultado usando um agrupamento de linhas, retornam um valor para cada linha da tabela. Um exemplo desse comportamento seria o cálculo de uma soma cumulativa ou de um ranking, pois, enquanto é necessário analisar várias linhas para se obter a conclusão da posição de um elemento no ranking ou o valor atual da soma, existe um valor desse resultado para cada linha e não apenas um valor total para todo o agrupamento utilizado no cálculo do resultado.

Fonte: Giphy

As window functions devem sempre ser acompanhadas de uma cláusula OVER, que especificará a janela, ou seja, qual o agrupamento de dados que será utilizado para o cálculo dos valores em suas respectivas linhas.

Para definir uma janela, é possível especificar 3 partes:

• PARTITION BY: especificar colunas a partir das quais o dataset será dividido em partições tais quais cada partição tem uma combinação única de valores dessas colunas; a window function será aplicada em cada uma dessas partições independentemente (ex: rank de fertilidade dos países em cada continente — a partição é na coluna continente, havendo um rank para cada continente; sintaxe: `PARTITION BY continente`).
• ORDER BY: indica a coluna a partir da qual os valores devem ser ordenados no cálculo da window function.
• Frame specification: indica quais linhas da partição atual serão usadas para o cálculo da função (ex: para calcular somas cumulativas de um determinado valor, é necessário considerar todas as linhas do começo até a linha atual).

O frame specification pode ser feito considerando a quantidade de linhas (por exemplo, 5 linhas para baixo e 5 para cima), e para isso usamos a cláusula ROWS. Alternativamente, podemos tomar como base o valor da coluna especificada no ORDER BY, o que é feito pela cláusula RANGE (considere os países em que a população seja de 1000 habitantes maior ou 5000 habitantes maior do que a do países atual).

Para definir os intervalos desejados nas cláusulas ROWS e RANGE, usamos BETWEEN […] AND […]. Os extremos dos intervalos podem ser:

• Um valor numérico com o significado explicitado acima: indica o início do intervalo se acompanhando por PRECEDING e o fim do intervalo se acompanhado por FOLLOWING;
• UNBOUNDED: indica o extremo da tabela — primeiro valor se acompanhado por PRECEDING e último valor se acompanhando por FOLLOWING;
• CURRENT ROW: indica a linha atual.

Uma janela pode ser definida durante a query, depois da cláusula OVER, ou definida separadamente em uma query (cláusula WINDOW), sendo apenas referenciada por nome na query em que a window function está sendo chamada também depois da cláusula OVER.

Um exemplo de uso da window function seria a soma cumulativa de população por idade em um determinado país em relação ao gênero. Isso pode ser feito a partir da tabela `midyear_population_agespecific` com a seguinte query:

SELECT age, sex,
SUM(population) OVER (
PARTITION BY sex
ORDER BY age
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- define o intervalo da janela (começo até linha atual)
) as `Soma cumulativa população`
FROM `bigquery-public-data.census_bureau_international.midyear_population_agespecific`
WHERE country_name = 'Brazil' AND year = 2010 -- filtra os resultados para o Brasil em 2010

Primeiras linhas da consulta gerada.

Algumas linhas depois, quando começam os dados do tipo Female.

Caso a ideia fosse não filtrar por ano e país, mas sim apresentar todas as combinações possíveis de ano e país, a query seria:

SELECT age, country_name, year, sex,
SUM(population) OVER (
PARTITION BY sex, country_name, year
ORDER BY age
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- define o intervalo da janela (começo até linha atual)
) as `Soma cumulativa população`
FROM `bigquery-public-data.census_bureau_international.midyear_population_agespecific`
ORDER BY country_name, year, sex, age -- a prioridade de ordenação vai da esquerda para direita, ou seja, colunas mais a direta são ordenadas primeiro e, portanto, a ordenação que mais permanece mantida é a mais a esquerda.

Primeiras linhas do resultado da query acima.

A prioridade de ordenação acima foi escolhida para que o resultado fosse separado por país, ano e a forma da soma cumulativa para cada país se mantivesse como a da query anterior.

Na query acima, usamos a função SUM, já vista na parte de agregação, como uma window function. No entanto, é importante saber que existem várias outras window functions que podem ser utilizadas, sendo que algumas das funções mais importantes são aquelas relacionadas à navegação (navigation functions), para as quais é obrigatório o uso do ORDER BY na definição da janela.

Com essas funções, é possível recuperar diferentes valores de uma coluna a partir de um determinado agrupamento. A função LAST_VALUE(population), por exemplo, recupera o valor da coluna população na última linha do agrupamento feito pela janela. Mais funções de navegação podem ser vistas na documentação.

Um exemplo de utilização dessa função pode ser caso queiramos saber a diferença entre a população média e a máxima população média registrada para um determinado país em cada ano. Esse tipo de consulta não pode ser feita com o GROUP BY já que é necessário informações da linha (ano atual e população no ano atual) e do grupo de linhas (população máxima). Portanto, é necessário fazer a seleção a partir de uma da window function LAST_VALUE para encontrar a população máxima registrada.

SELECT year, country_name, midyear_population,
LAST_VALUE(midyear_population) OVER ( -- o último valor será o valor de maior população devido ao ORDER BY
PARTITION BY country_name
ORDER BY midyear_population
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- toda a tabela
) - midyear_population as diff_max, -- subtrai midyear_population do valor da retornado pela window function
FROM `bigquery-public-data.census_bureau_international.midyear_population`
WHERE year <= 2017 -- depois de 2017 os valores são projeções
ORDER BY country_name, year;

Alguns resultados da diferença entre a população máxima e média para cada ano.

Na query acima, percebemos que é possível utilizar operadores aritméticos nas listagens de colunas a serem selecionadas no SQL quando realizamos a operação LAST_VALUE(midyear_population) OVER window — midyear_population. O operador indica que, em cada linha, o segundo operando deve ser subtraído do primeiro. Também seria possível realizar soma, subtração, multiplicação, etc. com os valores de cada coluna durante uma seleção. (OBS: considere window como a expressão escrita na query acima).

Por fim, uma outra classe interessante de window functions são as numbering functions. Com a função RANK, por exemplo, é possível criar um ranqueamento das linhas de acordo com uma determinada informação.

Agora chegou a hora de conhecermos a estrela da SQL, a famosa cláusula JOIN. Ela é usada para relacionar dados de diferentes tabelas, ou seja, é como os dados salvos estruturadamente podem ser selecionados a partir de informações conjuntas de diferentes tabelas.

Um exemplo que pode se encaixar no dataset usado neste texto é o cálculo da densidade demográfica, que já foi mencionado acima, sendo necessário o uso de um JOIN por envolver dados tanto da tabela country_names_area quanto da tabela midyear_population.

Vamos entender o JOIN fazendo a consulta da densidade demográfica para cada país no ano 2000.

SELECT
pop.country_name,
area.country_area,
pop.midyear_population,
pop.midyear_population / area.country_area AS `Density`
FROM
`bigquery-public-data.census_bureau_international.country_names_area` AS area
JOIN
`bigquery-public-data.census_bureau_international.midyear_population` AS pop
ON pop.country_code = area.country_code
WHERE pop.year = 2000
ORDER BY `Density` DESC

Alguns elementos novos foram adicionados nesta query e vamos mergulhar em cada um deles para entendê-los melhor.

A cláusula AS permite o uso de aliases, ou seja, apelidos que podem ser dados aos campos ou tabelas. Ao ser adicionado depois de um campo, ele renomeia este campo na coluna gerada pelo resultado da query; já ao ser adicionado depois de uma tabela, essa tabela pode ser referenciada por seu apelido no restante da query, sem ser preciso escrever seu ID completo todas as vezes que for necessário referenciá-la. Também é possível apelidar tabelas e campos apenas colocando seus alias ao lado, sem o uso da cláusula AS explicitamente. Já o JOIN é usado para especificar quais tabelas estarão relacionadas dentro da query.

Para relacionar dados em tabelas diferentes, devem existir, em ambas as tabelas, colunas que podem ser usadas para caracterizar essa relação, como um ID em comum; neste caso, esse papel é feito pelo campo country_code, que aparece em ambas as tabelas e pode ser usado para identificar que um determinado registro, em qualquer uma das tabelas relacionadas, carrega informação a respeito de um mesmo país. As cláusulas responsáveis por mapear essa chave do relacionamento entre duas tabelas são a cláusula ON e USING.

A primeira é seguida de uma relação genérica entre as colunas das tabelas sendo relacionadas e a segunda é seguida de uma lista de colunas, presentes em ambas as tabelas, a partir das quais será feito um teste de igualdade para diferentes registros nas tabelas relacionadas; caso a condição de relação definida pela cláusula ON ou USING seja cumprida, os registros correspondentes são relacionados. OBS: a condição ON pop.country_code = area.country_code poderia ser substituída por USING (country_code).

O JOIN que é feito por padrão é o chamada INNER JOIN, que apenas retorna resultados que cumprem completamente a condição definida pela relação; ou seja, no caso que estamos estudando, o INNER JOIN retorna apenas registros que apresentam um valor country_code correspondente em cada uma das tabelas. No entanto, também é possível fazer OUTER JOIN. O LEFT OUTER JOIN mantém dados da coluna à esquerda do JOIN mesmo que esses registros não tenham correspondentes na tabela da direita, retornando como NULL dados que seriam obtidos a partir da relação entre as duas tabelas. O RIGHT OUTER JOIN tem comportamento análogo, mas mantendo os registros sem correspondentes na tabela à direita do JOIN. Por fim, o FULL OUTER JOIN também tem um comportamento análogo, mas mantém dados sem correspondentes em qualquer um dos lados. Seguem algumas imagens para a melhor compreensão dessas formas de relacionamento.

Fonte: Digital Guide Ionos

Fonte: iFunny

Por fim, uma última cláusula que achamos válido comentar por agora é a cláusula WITH, que tem a função de criar, a partir de uma subquery, uma tabela temporária dentro da consulta em que ela foi definida.

Uma subquery é uma consulta cujo resultado é usado dentro de uma outra query. A query abaixo, por exemplo, tem uma subquery que calcula a população máxima ao longo dos anos para cada um dos países que estão tendo sua área mostrada:

SELECT
country_code AS `País`,
country_area AS `Área`,
( -- começo da subquery
SELECT MAX(midyear_population)
FROM `bigquery-public-data.census_bureau_international.midyear_population`
WHERE country_code = areas.country_code
) AS `População máxima registrada` - fim da subquery
FROM
`bigquery-public-data.census_bureau_international.country_names_area` as areas

Essa cláusula é muitas vezes usada em exemplos de consultas no BQ para criar tabelas arbitrárias em tempo de execução a partir das quais as consultas serão feitas. Segue um exemplo da criação de uma tabela de membros do Turing USP:

WITH membros AS (
SELECT 'Kauã' as membro, 'kauafillipe@turing.usp' as email, 'DS' as area UNION ALL
SELECT 'Davi', 'davifelix@turing.usp', 'DS' UNION ALL
SELECT 'Ada', 'adalovelace@turing.usp', 'NLP' UNION ALL
SELECT 'Azank', 'azankinhopistola@turing.usp', 'DS' UNION ALL
SELECT 'Azélia', 'banks@turing.usp', 'Quant' UNION ALL
SELECT 'Hugo', 'hugo@turing.usp', 'Quant' UNION ALL
SELECT 'Liliane', 'liliastro@turing.usp', 'CV' UNION ALL
SELECT 'Alan', 'alan@turing.usp', 'DS' UNION ALL
SELECT 'Mario', 'aquele@turing.usp', 'RL' UNION ALL
SELECT 'Lady', 'gaga@turing.usp', 'DS'
) -- criação da tabela com a cláusula WITH
SELECT * FROM membros WHERE area = 'DS' - consulta na tabela

Primeiramente utilizamos uma subquery com vários SELECTs unidos entre si — o operador UNION ALL junta o resultado do SELECT à esquerda dele com o do SELECT à direita dele. A partir do resultado desse subquery, criamos uma tabela chamada membros usando a cláusula WITH e, por fim, realizamos uma consulta nessa tabela usando a cláusula WHERE.

PARABÉNS! Depois dessa visita às cláusulas e funcionalidade do GoogleSQL, você agora já tem uma ótima noção de como realizar diferentes tipos de consultas nos seus dados. É uma parte bem densa, mas esperamos que os exemplos tenham ajudado no entendimento de todos os conceitos abordados.

Fonte: Giphy

Para finalizar este resumo de GoogleSQL, segue um exemplo de uma query fazendo diversas relações e agrupamentos para calcular a fertilidade média de países e continentes, estando o resultado gerado por ela logo abaixo. Note que estamos usando uma tabela de continentes que não está na base pública e foi adicionada posteriormente apenas para execução desta query. Logo, não será possível replicar esta query no seu BQ Sandbox, a menos que você tenha seguido o passo na Ingestão de dados pelo console e ajuste o nome do projeto dentro da query — modern-voice-355621 — para o nome do seu projeto).

SELECT
 country_continent.continent as `Continente`,
 fertility.country_name as `País`,
 AVG(fertility.total_fertility_rate) as `Taxa de fertilidade média`
FROM
 `bigquery-public-data.census_bureau_international.age_specific_fertility_rates` AS fertility
JOIN
 `modern-voice-355621.meu_conjunto_de_dados.country_continent` AS country_continent
USING (country_code)
GROUP BY ROLLUP (country_continent.continent, fertility.country_name)
ORDER BY country_continent.continent

Primeiras linhas do resultado da query acima.

O resultado pode ser compreendido da seguinte forma: a primeira linha representa um agrupamento de todos os dados; a segunda linha, apenas do continente africano; as linhas seguintes, para cada país do continente africano. A mesma lógica irá acontecer nos outros continentes, por exemplo:

Segundo agrupamento do resultado da query acima.

Para fins didáticos da utilização da cláusula WITH, segue um exemplo de uma query que traz os mesmos resultados, mas é executada com a criação de uma tabela temporária. Note que essa não é a melhor maneira de resolver esse problema, é apenas um exemplo para o uso da cláusula WITH.

WITH country_continent AS (
  SELECT
  country_code AS cc_code,
  country_name AS cc_name,
  continent
  FROM `modern-voice-355621.meu_conjunto_de_dados.country_continent`
)
SELECT
country_continent.continent as `Continente`,
fertility.country_name as `País`,
AVG(fertility.total_fertility_rate) as `Taxa de fertilidade média`
FROM `bigquery-public-data.census_bureau_international.age_specific_fertility_rates` AS fertility
JOIN country_continent
ON country_continent.cc_code = fertility.country_code
GROUP BY ROLLUP (country_continent.continent, fertility.country_name)
ORDER BY country_continent.continent

Funções do GoogleSQL

Outro ponto interessante sobre o GoogleSQL é a presença de funções e operadores particulares dessa linguagem que podem ser utilizados para lidar especificamente com determinados tipos de dados, o que facilita bastante algumas tarefas. A lista de funções e operadores é extensa e recomenda-se a consulta da documentação oficial para mais informações a respeito do assunto. Neste texto, vamos nos ater a Array functions (funções de matriz), date/datetime/time/timestamp functions (funções de data, datetime, hora e carimbo de hora), de conversão e de consulta federada.

As Array functions são funções utilizadas para tratar dados do tipo Array. Por exemplo, a função ARRAY tem como parâmetro uma subquery e produz um array com um elemento para cada linha da subquery. A subquery deve resultar em uma tabela de uma única coluna, cujos valores serão transformados nos elementos do ARRAY e não podem ser do tipo ARRAY. Se a subquery não resultar em uma tabela com valores, é produzido um ARRAY vazio.

WITH membros AS (
SELECT 'Kauã' as membro, 'kauafillipe@turing.usp' as email, 'DS' as area UNION ALL
SELECT 'Davi', 'davifelix@turing.usp', 'DS' UNION ALL
SELECT 'Ada', 'adalovelace@turing.usp', 'NLP' UNION ALL
SELECT 'Azank', 'azankinhopistola@turing.usp', 'DS' UNION ALL
SELECT 'Azélia', 'banks@turing.usp', 'Quant' UNION ALL
SELECT 'Hugo', 'hugo@turing.usp', 'Quant' UNION ALL
SELECT 'Liliane', 'liliastro@turing.usp', 'CV' UNION ALL
SELECT 'Alan', 'alan@turing.usp', 'DS' UNION ALL
SELECT 'Mario', 'aquele@turing.usp', 'RL' UNION ALL
SELECT 'Lady', 'gaga@turing.usp', 'DS'
)
SELECT ARRAY(SELECT email FROM membros WHERE area = 'DS') AS emails_membros_ds

Resultado da query acima.

Para transformar uma subquery que produz uma tabela com múltiplas colunas em ARRAY, deve-se reescrever a subquery com a cláusula SELECT AS STRUCT, como mostra o exemplo abaixo.

SELECT ARRAY(SELECT as STRUCT membro, email FROM membros WHERE area = 'DS') AS emails_membros_ds

Resultado da query acima.

A função ARRAY_CONCAT pode ser utilizada para concatenar ARRAYS com elementos do mesmo tipo em um único ARRAY.

WITH membros AS (
  SELECT 'Kauã' as membro, 'kauafillipe@turing.usp' as email, 'DS' as area UNION ALL
  SELECT 'Davi', 'davifelix@turing.usp', 'DS' UNION ALL
  SELECT 'Ada', 'adalovelace@turing.usp', 'NLP' UNION ALL
  SELECT 'Azank', 'azankinhopistola@turing.usp', 'DS' UNION ALL
  SELECT 'Azélia', 'banks@turing.usp', 'Quant' UNION ALL
  SELECT 'Hugo', 'hugo@turing.usp', 'Quant' UNION ALL
  SELECT 'Liliane', 'liliastro@turing.usp', 'CV' UNION ALL
  SELECT 'Alan', 'alan@turing.usp', 'DS' UNION ALL
  SELECT 'Mario', 'aquele@turing.usp', 'RL' UNION ALL
  SELECT 'Lady', 'gaga@turing.usp', 'DS'
),
emails_ds AS (
SELECT ARRAY(SELECT email FROM membros WHERE area = 'DS') AS emails_membros_ds
),
emails_quant AS (
SEECT ARRAY(SELECT email FROM membros WHERE area = 'Quant') AS emails_membros_ds
)
SELECT ARRAY_CONCAT((SELECT * FROM emails_ds), (SELECT * FROM emails_quant)) AS emails_lista

Resultado da query acima.

Alternativamente, o operador || pode ser utilizado para realizar a mesma tarefa.

SELECT ARRAY(SELECT 'kauafillipe@turing.usp' UNION ALL
SELECT 'davifelix@turing.usp')||
ARRAY(SELECT 'alan.turing@usp' UNION ALL
SELECT'lovelace.turing@usp') AS arrays_concatenados

Resultado da query acima.

ARRAY_LENGTH é uma função de Arrays que pode ser usada para obter o tamanho de um ARRAY.

WITH membros AS (
SELECT 'Kauã' as membro, 'kauafillipe@turing.usp' as email, 'DS' as area UNION ALL
SELECT 'Davi', 'davifelix@turing.usp', 'DS' UNION ALL
SELECT 'Ada', 'adalovelace@turing.usp', 'NLP' UNION ALL
SELECT 'Azank', 'azankinhopistola@turing.usp', 'DS' UNION ALL
SELECT 'Azélia', 'banks@turing.usp', 'Quant' UNION ALL
SELECT 'Hugo', 'hugo@turing.usp', 'Quant' UNION ALL
SELECT 'Liliane', 'liliastro@turing.usp', 'CV' UNION ALL
SELECT 'Alan', 'alan@turing.usp', 'DS' UNION ALL
SELECT 'Mario', 'aquele@turing.usp', 'RL' UNION ALL
SELECT 'Lady', 'gaga@turing.usp', 'DS'
),
areas_emails AS (
SELECT area, ARRAY(SELECT email FROM membros WHERE area = m.area) AS emails FROM membros AS m GROUP BY area
)
SELECT area, emails, ARRAY_LENGTH(emails) AS tamanho FROM areas_emails

Resultado da query acima

Já a função ARRAY_REVERSE retorna um ARRAY com a ordem inversa dos elementos.

SELECT area, emails, ARRAY_LENGTH(emails) AS tamanho, ARRAY_REVERSE(emails) emails_inverso FROM areas_emails

Resultado da query acima.

Por fim, ARRAY_TO_STRING transforma um ARRAY em uma STRING. Isso é feito por meio da concatenação dos elementos do ARRAY e o resultado é transformado em STRING. Essa função tem como parâmetros a expressão que contém o ARRAY a ser transformado em STRING e o delimitador que será usado para marcar a separação dos elementos do ARRAY na STRING produzida. Há um parâmetro opcional null_text que pode ser usado para substituir valores NULL por um valor textual. Se esse parâmetro não for passado, os valores NULL são omitidos da STRING.

SELECT area, emails, ARRAY_LENGTH(emails) AS tamanho, ARRAY_TO_STRING(emails, ', ') AS emails_string FROM areas_emails

Resultado da query acima.

As funções de DATE, por sua vez, servem para manipular dados do tipo DATE. A função CURRENT_DATE, por exemplo, retorna a data atual. O parâmetro time_zone_expression pode ser usado para obter a data atual com referência a um fuso horário específico e usa como padrão o UTC.

SELECT CURRENT_DATE('America/Sao_Paulo') AS data_atual

A função DATE_ADD é usada para somar um intervalo de data à uma data. O intervalo de data pode ser um intervalo de dia (DAY), semana (WEEK), mês (MONTH), trimestre (QUARTER) ou ano (YEAR). O exemplo abaixo soma 3 semanas à data ‘2023–11–25’.

SELECT DATE_ADD(DATE '2023–11–25', INTERVAL 3 WEEK) AS tres_semanas_pra_frente

Resultado da query acima.

Analogamente, DATE_SUB é usado para subtrair um intervalo de data a uma data.

SELECT DATE_SUB(DATE '2023–11–25', INTERVAL 3 WEEK) AS tres_semanas_pra_tras

Resultado da query acima.

Já DATE_DIFF retorna a diferença entre duas datas. Ela recebe como parâmetros a primeira data, a segunda data e a referência especificada da parte de data em que se deseja expressar o resultado. Se a primeira data anteceder a segunda, o resultado é expresso como um número negativo. Do contrário, é um número positivo. A parte de data (ou date_part) representa uma parte de uma data e seus valores; na função DATE_DIFF, podem ser DAY (dia de uma data), WEEK (semana de uma data, considerando o início da semana no domingo), WEEK(<WEEKDAY>) (semana de uma data, considerando o início da semana como o dia especificado em <WEEKDAY>), MONTH (mês de uma data), QUARTER (trimestre de uma data), ISOWEEK (semana de uma data do padrão ISO) e ISOYEAR (ano de uma data do padrão ISO).

A diferença é calculada da seguinte maneira: conta-se a quantidade de intervalos do limite da referência especificado que aparecem entre as duas datas. Por exemplo, considere a seguinte consulta:

SELECT DATE_DIFF(DATE(2023,11, 07), DATE(2023, 11, 28), WEEK(TUESDAY))

Será calculada a diferença entre as datas 07/11/2023 e 28/11/2023, em termos de semanas. Além disso, considera-se, nessa consulta, que as semanas iniciam na terça-feira, já que se usa a parte de data WEEK(TUESDAY). A imagem abaixo ilustra como é realizado o cálculo.

Fonte da imagem: https://www.axnent.com/pt/calendario-de-novembro-de-2023/

Perceba, pela imagem, que o intervalo de semanas é -3. Pois, considerando o início de uma semana nas terças-feiras, temos 1–4 = -3 semanas entre as datas 07/11/2023 e 28/11/2023. Da mesma forma, se fosse realizada a consulta

SELECT DATE_DIFF(DATE(2023,11, 28), DATE(2023, 11, 07), WEEK(TUESDAY))

O resultado esperado seria 4–1 = 3 semanas, pois a primeira data é posterior à segunda. Porém, se fosse feita a consulta

SELECT DATE_DIFF(DATE(2023,11, 28), DATE(2023, 11, 06), WEEK(TUESDAY))

O resultado esperado seria 5–1 = 4 semanas, pois a data 06/11/2023, uma segunda-feira, é o último dia da semana que começa no dia 01/11/2023.

Se o parâmetro de parte de data for somente WEEK, considera-se que a semana inicia-se no domingo. Assim como nos exemplos anteriores, para alterar a referência de início de uma semana, é possível utilizar WEEK(DIA), em que DIA é o nome do dia da semana que se pretende adotar como referência da contagem. Sendo assim, WEEK(MONDAY) considera que as semanas começam todas as segundas-feiras, por exemplo. A consulta abaixo evidencia que o resultado da função DATE_DIFF pode variar bastante conforme os parâmetros de data passados, ainda que considere as mesmas datas.

SELECT
DATE_DIFF(DATE '2023–11–28', DATE '2023–11–25', WEEK(MONDAY)) AS WEEK_segunda,
DATE_DIFF(DATE '2023–11–28', DATE '2023–11–25', WEEK(TUESDAY)) AS WEEK_terca,
DATE_DIFF(DATE '2023–11–28', DATE '2023–11–25', WEEK(WEDNESDAY)) AS WEEK_quarta,
DATE_DIFF(DATE '2023–11–28', DATE '2023–11–25', WEEK(THURSDAY)) AS WEEK_quinta,
DATE_DIFF(DATE '2023–11–28', DATE '2023–11–25', WEEK(FRIDAY)) AS WEEK_sexta,
DATE_DIFF(DATE '2023–11–28', DATE '2023–11–25', WEEK(SATURDAY)) AS WEEK_sabado,
DATE_DIFF(DATE '2023–11–28', DATE '2023–11–25', WEEK) AS WEEK_domingo

Note que, olhando o calendário mostrado acima, 28/11/2023 é uma terça-feira e 25/11/2023 um sábado. O resultado deve ser positivo, pois a primeira data é superior à segunda em todas as consultas. Para WEEK(MONDAY), os dias 25 e 26 são atribuídos à semana anterior (semana 1) e os dias 27 e 28 à semana mais recente (semana 2). Como, 2–1 = 1 semana, o resultado esperado da primeira consulta é 1. O mesmo raciocínio pode ser aplicado para WEEK(TUESDAY) e WEEK(SUNDAY).

Para WEEK(SATURDAY), todos os dias são atribuídos à semana que começa no sábado 25/11/2023. Como entre as datas 28/11/2023 e 25/11/2023, não há diferença de semana, pois todos os dias estão na mesma semana, o resultado esperado da penúltima consulta é 0. O mesmo raciocínio pode ser aplicado para WEEK(WEDNESDAY), WEEK(THURSDAY) e WEEK(FRIDAY), que consideram todos os dias entre 28/11/2023 e 25/11/2023 pertencentes à mesma semana, que se inicia na quarta 22/11/2023, quinta 23/11/2023 e sexta 24/11/2023, respectivamente. A imagem abaixo ilustra o resultado obtido para todas essas consultas.

Resultado da query acima.

Alterando a parte de data de WEEK para DAY, obtém-se a quantidade de dias entre as duas datas. Deve ser número negativo, pois a primeira data antecede a segunda.

SELECT DATE_DIFF(DATE '2023–11–07', DATE '2023–11–28', DAY) AS dias

Resultado da query acima.

Para mais informações sobre as partes de data aceitas na função DATE_DIFF, consulte a documentação oficial.

A função EXTRACT pode ser usada para extrair uma parte de data de uma data. Por exemplo:

SELECT EXTRACT(MONTH FROM DATE '2023–11–25') AS mes

Resultado da query acima.

A função FORMAT_DATE, por sua vez, é uma função usada para ajustar uma data de acordo com um determinado formato. Por exemplo:

SELECT FORMAT_DATE('%d/%m/%y', DATE(2023, 11, 26)) AS data_brasil

Resultado da query acima.

As funções para manipular DATETIME, TIME e TIMESTAMP são semelhantes às apresentadas para DATE, com a alteração de prefixo ou sufixo DATE para DATETIME, TIME ou TIMESTAMP, conforme o caso. Para mais informações sobre as funções específicas de cada um desses tipos ou para mais detalhes dessas e de outras funções de DATE, cheque a documentação oficial.

As funções de conversão são utilizadas para realizar a conversão de um tipo de dado para outro tipo. As principais funções são CAST e PARSE_NUMERIC.

A função CAST é utilizada da seguinte forma: CAST(expressão AS tipo), como mostra o exemplo abaixo.

SELECT DATE_ADD(CAST('2023–11–25' AS DATE), INTERVAL 1 YEAR) AS proximo_ano

Resultado da query acima.

Note que, no exemplo acima, só foi possível somar 1 ano à data, pois foi realizada anteriormente a conversão de STRING para o tipo DATE. São várias as maneiras que isso pode ser feito e muitos os tipos de dados que podem ser convertidos. Por isso, recomenda-se a leitura da documentação oficial de funções de conversão para mais detalhes.

A função PARSE_NUMERIC converte dados do tipo STRING para um valor NUMERIC. Seu uso é semelhante ao CAST AS NUMERIC, porém aceita o uso de sinal depois do número e o uso de espaços entre o sinal e o número. O exemplo abaixo mostra um caso de uso dessa função no qual a vírgula é utilizada como delimitador que separa a parte inteira da decimal do número e o sinal negativo aparece depois do número, com a presença de um espaço entre ambos. Veja que a função PARSE_NUMERIC converte o texto para o tipo NUMERIC, mas ocorreria erro caso se tentasse a conversão com CAST AS NUMERIC.

SELECT PARSE_NUMERIC('3,.14 -') AS menos_pi

Resultado da query acima.

Por fim, vale mencionar a função EXTERNAL_QUERY. Ela é uma função de consulta federada e pode ser utilizada para realizar uma consulta em um banco de dados externo e trazer seu resultado para o BigQuery. É realizado um mapeamento entre o tipo de dado do banco externo e os tipos de dados do BigQuery, para que haja uma conversão de um para outro na construção da tabela do BQ. A função tem como parâmetros a consulta a ser realizada e o identificador da conexão com o banco de dados externo, por exemplo:

projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID,

Vale mencionar que, mesmo que seja utilizada a cláusula ORDER BY na consulta externa, o BigQuery não respeita essa ordenação e pode apresentar o resultado em uma ordem diferente. Além disso, metadados do banco de dados também podem ser acessados pela função.

No exemplo abaixo, acessamos uma base de dados externa com dados dos membros que já escreveram Turing Talks e selecionamos o nome de todos os membros e a data mais recente do Talks que produziram. Para se obter o resultado ordenado por nome, é preciso utilizar a cláusula ORDER BY 1 na consulta interna do BigQuery, pois o ORDER BY nome não será respeitado.

SELECT *
FROM
EXTERNAL_QUERY(
'projects/ttalks-bq/locations/us/connections/ext-bq',
'''SELECT nome, MAX(data) as data FROM membros_ttalks GROUP BY nome ORDER BY nome'''
)
ORDER BY 1

Operadores

Fonte: Giphy

Estudando as queries, entramos em contato com alguns operadores, como +, -, <, >=, <=, =, AND, OR, NOT, BETWEEN etc. No entanto, existe uma grande variedade de operadores para diferentes tipos de dados, que podem ser encontrados na documentação.

Um exemplo de operador interessante é o operador LIKE, que possibilita a comparação de strings a partir de padrões, sendo que:

• “%” representa uma quantidade qualquer de caracteres
• “_” representa um único caractere

Por exemplo: é possível buscar todas as pessoas em uma base de dados que contenham Silva no meio do nome a partir do seguinte padrão:

WITH nomes AS (
SELECT "Davi Da Silva" as nome UNION ALL
SELECT "Kauã Silva Rodrigues" UNION ALL
SELECT "Felipe Silva Almeida" UNION ALL
SELECT "Renata Pereira" UNION ALL
SELECT "Silvariano dos Santos"
)
SELECT nome FROM nomes WHERE nome LIKE "% Silva %"

Resultado da query acima.

Consultando dados programaticamente

Ufa! Mergulhamos no mundo do GoogleSQL e agora finalmente sabemos fazer diferentes consultas para extrair informações precisas a partir de um grande volume de dados.

Fonte: Tenor

No entanto, não é sempre que essas consultas são feitas com queries dentro do console, sendo muitas vezes necessário rotinas automatizadas para obtenção recorrente e mecânica de dados ou até para tratamentos mais refinados de dados obtidos a partir de queries como as vistas acima.

Tendo a gcloud CLI instalada e as credenciais padrões configuradas, iremos utilizar a mesma biblioteca em Python que foi utilizada na parte de Ingestão de dados no BigQuery de modo programático.

Tudo o que aprendemos na seleção de dados pelo console será aproveitado, visto que executaremos as mesmas queries. Como vimos na parte de ingestão, começamos importando os módulos necessários e instanciando o client do BigQuery.

from google.cloud import bigquery

client = bigquery.Client()

Para realizar uma consulta, é usado o método client.query(), que recebe a string da query que será executada e retorna um QueryJob.

query = """SELECT
country_name, country_area
FROM `bigquery-public-data.census_bureau_international.country_names_area`
ORDER BY country_area
LIMIT 15"""

query_job = client.query(query)

Para executar a query, é necessário chamar o método result() do QueryJob, que irá retornar um objeto iterador fornecendo as linhas dos resultados. Um objetivo iterador significa que o processamento dos resultados é lazy, ou seja, as linhas do resultado não são processadas de uma vez, mas sim cada uma delas é carregada na medida em que são solicitadas de maneira sequencial. Isso é útil para respostas com datasets muito grandes, já que carregar o resultado como um todo levaria a uma grande demanda de processamento e de memória.

res = query_job.result()

A partir do RowIterator, no entanto, já é possível ver a quantidade total de linhas que foram retornadas a partir do atributo total_rows.

print(res.total_rows)

Uma forma de acessar todas as linhas é por meio de um for loop. Cada elemento do RowIterator é um objeto do tipo Row, que representa uma linha do resultado, funcionando como um dicionário em que o nome das colunas é a chave para o seu respectivo valor na linha em questão.

for line in result:
    print(f'(Nome, Area): {line["country_name"]}, {line["country_area"]}')

Para datasets menores, em que não é necessário o processamento lazy, é possível converter o resultado diretamente para um pandas dataframe a partir do método to_dataframe().

df = query_job.to_dataframe()

Depois que o processo é executado, algumas informações sobre ele podem ser acessadas no QueryJob. Por exemplo, para ver o número de bytes processados:

print(query_job.total_bytes_processed)

A qualquer momento, caso seja necessário ver erros no job, é possível acessar:

print(query_job.error_result)

Também é possível passar como segundo parâmetro para o método .query() um objeto QueryJobConfig. Nele, por exemplo, é possível configurar para que o job funcione em dry_run, ou seja, em que um processo bem sucedido retornará um resultado vazio fornecendo apenas estatísticas da execução, como bytes processados.

dry_query_job = client.query(query, bigquery.QueryJobConfig(dry_run=True))
print(query_job.total_bytes_processed)

12. Visualização de dados do BigQuery com o Looker Studio

Uma vez que os dados já estão no BigQuery, pode ser interessante construir algumas visualizações que permitam resumi-los de forma a gerar valor e informação para o projeto que os utilizam. A GCP possui uma ferramenta própria e gratuita que possibilita a construção de relatórios de modo rápido e prático para auxiliar na visualização dos seus dados. É possível utilizar o Looker Studio para visualizar dados a partir de um esquema de tabela ou de uma consulta SQL no editor do BigQuery por meio do Console do BigQuery.

No primeiro caso, clique na tabela desejada no Console do BigQuery. Clique nos três pontos, depois em “Exportar” e, então, em “Explorar com o Looker Studio”.

Primeira maneira de explorar com o Looker Studio.

No segundo caso, basta abrir o editor do BigQuery e executar uma consulta em SQL. Na aba de “Resultados da consulta”, expanda a aba “Explorar dados” e selecione “Explorar com o Looker Studio”.

Segunda maneira de explorar com o Looker Studio.

Feito isso, o usuário é redirecionado para a interface do Looker Studio. A plataforma do Looker é bem intuitiva e fácil de usar. Este texto não pretende ser uma introdução ao uso dele, portanto é fortemente recomendado que seja consultada a documentação oficial do Looker Studio, que é bastante clara e compreensível, para mais informações.

Um relatório simples foi gerado com os dados do dataframe ‘2014–02 — Citi Bike trip data’, utilizado no início deste texto, transformado na tabela ‘eight’, para um entendimento do que pode ser feito com esse serviço e como os dados armazenados no BigQuery podem ser visualizados prontamente.

Exemplo de relatório.

13. Conclusões

Fonte: Criar Meme

O BigQuery é um serviço de computação em nuvem oferecido pelo Google que permite o armazenamento de dados, funcionando como um Data Warehouse, e a realização de consultas em SQL. Além disso, oferece integração com ferramentas de Machine Learning e de visualização de dados para complementar as atividades exercidas nessa plataforma.

O uso de soluções em nuvem como o BigQuery torna-se muito conveniente em cenários de Big Data, comum em grandes corporações, em que é necessário lidar com datasets de grande volume, e não apenas um conjunto de dados de tamanho pequeno ou médio — como aqueles presentes em sites como o Kaggle.

Nesse texto, você foi introduzido a algumas tarefas simples de serem feitas no BigQuery, tanto via Console como de modo programático. Você já sabe realizar a ingestão, a consulta e a visualização de dados nessa plataforma. Contudo, há uma gama de possibilidades mais complexas a serem exploradas no BigQuery. Para mais informações sobre o uso do BigQuery e do que pode ser feito nele, é de suma importância consultar a sua documentação oficial.

Vale lembrar, também, que nem todo problema precisa ou deve ser resolvido com o BigQuery. No geral, não se tratando de um cenário de Big Data, o uso de Data Warehouses ou Data Lakes pode apenas elevar a complexidade da solução do problema, sendo, então, uma abordagem desnecessária. Nesses casos, é possível que o uso de frameworks de linguagens de programação e o armazenamento dos dados em banco de dados convencionais seja o suficiente para se ter uma solução aceitável. É preciso sempre estudar e analisar o problema antes de escolher as ferramentas e o método de solucioná-lo!

Fonte: Giphy

Por hoje é “só”! Espero que você tenha gostado. Se quiser conhecer um pouco mais sobre o que fazemos no Turing USP, não deixe de nos seguir nas redes sociais: Facebook, Instagram, LinkedIn e, claro, acompanhar nossos posts no Medium. Para acompanhar ainda mais de perto e participar de nossas discussões e eventos, entre no nosso servidor do Discord.

Um abraço, e até mais!