Big Query - Criando Tabelas Externas no BigQuery com Arquivos no Formato ORC

Introdução
O Google BigQuery é uma poderosa plataforma de análise de dados, que permite realizar consultas em grandes volumes de dados de forma rápida e escalável. Uma das funcionalidades mais úteis é a criação de tabelas externas, que permitem que você leia dados diretamente de arquivos armazenados no Google Cloud Storage (GCS) sem precisar carregar esses dados no BigQuery. Isso é especialmente útil quando se lida com grandes volumes de dados que não precisam ser manipulados frequentemente, mas ainda assim precisam ser acessados de maneira eficiente.

Neste artigo, vamos explorar a criação de uma tabela externa no BigQuery, vinculando-a a arquivos no formato ORC (Optimized Row Columnar), um formato de armazenamento columnar amplamente utilizado devido à sua alta eficiência de compressão e leitura.

📋 Estrutura da Tabela Externa no BigQuery

Para criar uma tabela externa no BigQuery, utilizamos o comando CREATE EXTERNAL TABLE. Esse comando permite que o BigQuery leia dados de fontes externas, como arquivos no Google Cloud Storage. A tabela criada no BigQuery age como uma interface que pode ser consultada, mas os dados reais permanecem armazenados externamente.

📝 Script de Criação de Tabela Externa

Aqui está o script que cria uma tabela externa no BigQuery vinculando-a a arquivos .orc armazenados no Google Cloud Storage (GCS):

CREATE EXTERNAL TABLE IF NOT EXISTS project.myds.myexternaltable 
WITH PARTITION COLUMNS ( 
    name STRING, 
    typeaction STRING, 
    date DATE ) 
OPTIONS ( 
    format = 'ORC', 
    uris = ['gs://mybucket/mydirfiles/*.orc'], 
    hive_partition_uri_prefix = 'gs://bmybucket/mydirfiles', 
    require_hive_partition_filter = false 
);  

🔍 Explicação do Script

• CREATE EXTERNAL TABLE IF NOT EXISTS
  Esta instrução cria uma tabela externa apenas se ela não existir. Caso a tabela já exista, ela será ignorada.

• project.myds.myexternaltable
  Define o nome completo da tabela, onde:

  • project é o nome do projeto no Google Cloud.
  • myds é o nome do dataset dentro do projeto.
  • myexternaltable é o nome da tabela externa a ser criada.

• WITH PARTITION COLUMNS
  Essa cláusula define as colunas de partição da tabela externa. No exemplo, temos três colunas de partição:

  • name (do tipo STRING )
  • typeaction (do tipo STRING )
  • date (do tipo DATE )

  O BigQuery usa essas colunas de partição para otimizar a leitura dos dados, ajudando a filtrar e organizar as consultas.

• OPTIONS
  Esta cláusula define as opções específicas para a tabela externa. Aqui, temos os seguintes parâmetros:

  • format = 'ORC'
    Especifica o formato de arquivo dos dados a serem lidos. No caso, estamos utilizando o formato ORC, que é um formato columnar que oferece alta compressão e leitura eficiente.

  • uris = ['gs://mybucket/mydirfiles/*.orc']
    Define o local dos arquivos que serão usados como fonte de dados. Aqui, estamos utilizando um padrão de URI (Uniform Resource Identifier) que aponta para todos os arquivos .orc dentro do diretório mydirfiles no bucket mybucket do Google Cloud Storage.

  • hive_partition_uri_prefix = 'gs://bmybucket/mydirfiles'
    Especifica o prefixo de URI utilizado para localizar as partições dos dados no Google Cloud Storage. O BigQuery usa esse prefixo para localizar os diretórios de partições, caso existam.

  • require_hive_partition_filter = false
    Esta opção define se o BigQuery deve exigir filtros de partição Hive ao ler os dados. Se definido como true , o BigQuery exigirá que as consultas especifiquem partições específicas ao acessar os dados. Nesse exemplo, estamos definindo como false , o que significa que o BigQuery não exigirá esses filtros.

⚡ Benefícios do Uso de Tabelas Externas

• 📊 Escalabilidade e Eficiência
  Criar tabelas externas permite que você consulte grandes volumes de dados sem a necessidade de carregá-los fisicamente no BigQuery. Isso pode ser altamente eficiente em termos de custos e desempenho, especialmente para dados que são apenas acessados ocasionalmente.

• 🌐 Armazenamento Externo
  Com as tabelas externas, os dados podem ser armazenados no Google Cloud Storage, aproveitando os benefícios de alta durabilidade e flexibilidade oferecidos pelo GCS.

• 🔄 Suporte a Vários Formatos
  BigQuery oferece suporte para diversos formatos de arquivo, incluindo ORC, Parquet, JSON, Avro, entre outros, o que proporciona grande flexibilidade na escolha do formato que melhor se adapta às suas necessidades.

• 🔍 Particionamento
  O uso de partições ajuda a otimizar a consulta e a leitura de dados, especialmente quando os arquivos são grandes. O BigQuery pode usar as colunas de partição para filtrar e processar apenas os dados relevantes para a consulta, melhorando a performance.

🖥️ Exemplo Prático de Consulta

Uma vez que a tabela externa tenha sido criada, podemos realizar consultas sobre ela da mesma maneira que uma tabela normal. Por exemplo, para recuperar todos os registros de um determinado name e date , podemos fazer:

 SELECT 
    name, 
    typeaction, 
    date 
FROM 
    project.myds.myexternaltable 
WHERE 
    name = 'Employee1' AND 
    date = '2025-01-01';  

🏁 Considerações Finais

As tabelas externas no BigQuery são uma excelente ferramenta para consultar dados armazenados no Google Cloud Storage sem precisar movê-los para o BigQuery. Elas oferecem grande flexibilidade e eficiência, especialmente quando se trabalha com grandes volumes de dados. Além disso, o suporte a diferentes formatos de arquivo e o particionamento de dados tornam o BigQuery uma plataforma altamente escalável e otimizada para análise de dados.

Se você está lidando com arquivos ORC ou qualquer outro formato suportado, criar tabelas externas no BigQuery é uma ótima maneira de integrar esses dados às suas consultas, garantindo rapidez e economia de armazenamento.

💡 Dica: Ao utilizar tabelas externas, lembre-se de configurar corretamente as partições para melhorar a performance de consultas e evitar a leitura desnecessária de grandes volumes de dados.