Migração de Tabelas Nativas para Tabelas Externas no BigQuery

Um Benchmark de Análise e Decisão

Introdução

Em nossos esforços contínuos para otimizar o armazenamento e processamento de dados, realizamos uma exploração detalhada sobre a migração de tabelas nativas para tabelas externas no Google BigQuery. Este relatório descreve nossas descobertas, avalia as implicações de desempenho e custo, e apresenta nossa decisão baseada na análise.

Contexto

Atualmente, utilizamos tabelas nativas no BigQuery para armazenamento e processamento de dados, frequentemente utilizando operações MERGE e obtendo dados do Google Cloud Storage (GCS) via PySpark. Embora essa configuração tenha sido eficaz, buscamos explorar alternativas que possam oferecer economia de custos e maior eficiência.

Análise

Comparação de Desempenho

Analisamos uma tabela com 25.302.708 linhas, 113 colunas e 2,88 GB de bytes físicos totais para comparar o desempenho de tabelas nativas e externas usando a ferramenta BigLake no BigQuery.

• Consulta SELECT *:
  - Tabela Nativa: 14,17 GB processados; 16 segundos
  - Tabela Externa: 18,81 GB processados; 19 segundos
• GROUP BY:
  - Tabela Nativa: 0,97 GB processados; 0 segundos
  - Tabela Externa: 5,58 GB processados; 2 segundos
  - Tabela Externa ZOrder: 5,46 GB processados; 2 segundos
  - Tabela Externa Particionada: 5,42 GB processados; 3 segundos
• WHERE1:
  - Tabela Nativa: 4,19 GB processados; 19 segundos
  - Tabela Externa: 10,28 GB processados; 18 segundos
  - Tabela Externa ZOrder: 10,44 GB processados; 19 segundos
  - Tabela Externa Particionada: 5,55 GB processados; 17 segundos
• WHERE2:
  - Tabela Nativa: 0,47 GB processados; 2 segundos
  - Tabela Externa: 11,09 GB processados; 5 segundos
  - Tabela Externa ZOrder: 7,56 GB processados; 8 segundos
  - Tabela Externa Particionada: 0,55 GB processados; 3 segundos

Os resultados indicam que as tabelas nativas superam as tabelas externas tanto em termos de bytes processados quanto em tempo de execução.

Resultados consolidados de bytes

Resultados consolidados de duração

Implicações de Custo

Embora as tabelas nativas demonstrem um desempenho superior nas consultas, as operações MERGE geram custos significativos:

• Estatísticas do trabalho MERGE:
  - Bytes Processados: 19,33 GB
  - Tempo de Execução: 43 segundos

Custos adicionais incluem tarefas PySpark no DataProc, registro de logs e maiores encargos de armazenamento no BigQuery em comparação ao GCS. Apesar desses custos, a possibilidade de redução de custos com tabelas externas é considerável.

Prós e Contras das Tabelas Externas

• Prós:
  - Redução de custos
  - Facilidade de uso com arquivos no formato delta lake
  - Potencial para atualizações mais rápidas
• Contras:
  - Acesso somente leitura no BigQuery
  - Desempenho mais lento em comparação ao acesso direto aos dados
  - Incapacidade de exportar dados diretamente
  - Visibilidade limitada dos detalhes da tabela

Caso de Uso

Criar uma tabela externa no BigQuery envolve conectar-se ao BigQuery e definir a tabela usando declarações SQL, especificando o URI do GCS para os dados da tabela.

CREATE EXTERNAL TABLE `<project-id>.<dataset>.<external_table>` 
WITH CONNECTION `<project-id>.<region>.<connection>`
OPTIONS (
 format ="DELTA_LAKE", 
 uris=['gs://<bucket>/path/to/delta']);

Também é possível criá-la utilizando o Client da biblioteca do BigQuery no Python:

from google.cloud.bigquery import Client, ExternalConfig, Table

def create_external_table_on_bq(
        self,
        source_uris: List[str],
        dataset: str,
        table: str,
        project_id: str,
        connection_id: Optional[str] = None,
        external_source_format: str = "DELTA_LAKE",
        exists_ok: bool = True,
    ) -> None:
        """
        Creates an external BigQuery table.
        If external table already exists, nothing is done.
        Else table_id is a native table, an exception is raised.

        Args:
            source_uris (List[str]): The URIs of the external data source.
            dataset (str): The name of the dataset where the table will be created.
            table (str): The name of the table to be created.
            project_id (str): The ID of the project where the table will be created.
            connection_id (str, optional): The ID of the connection to be used. Defaults to None (it'll create a BQ external table, not BigLake external table).
            external_source_format (str, optional): The format of the external data source. Defaults to "DELTA_LAKE".
            exists_ok (bool, optional): Whether to create the table if it already exists. Defaults to True.
        """
        client = Client()

        table_id = f"{project_id}.{dataset}.{table}"

        external_config = ExternalConfig(external_source_format)
        external_config.source_uris = source_uris
        external_config.connection_id = connection_id
        table_bq = Table(table_id)

        table_bq.external_data_configuration = external_config
        table_bq = client.create_table(table_bq, exists_ok=exists_ok)

        if table_bq.external_data_configuration:
            self.logger.info(
                f"External table {table_id} created successfully at {table_bq.modified}."
            )
        else:
            raise Exception(
                f"Failed to create external table {table_id}. Verify if the table is not a native table."
            )

Decisão

Após uma análise cuidadosa, recomendamos a migração de tabelas nativas para tabelas externas para dados provenientes de arquivos no formato delta lake particionados.

Próximos Passos

1. Iniciar um plano de migração em fases para transferir os dados existentes de tabelas nativas para tabelas externas.
2. Desenvolver diretrizes e melhores práticas para utilizar as tabelas externas de forma eficaz, considerando suas limitações.
3. Monitorar o desempenho e as implicações de custo após a migração, e iterar em estratégias para otimizar o uso das tabelas externas.
4. Fornecer treinamento e suporte aos stakeholders para garantir uma transição suave e maximizar os benefícios das tabelas externas no BigQuery.

Conclusão

A migração para tabelas externas apresenta uma oportunidade para simplificar operações, reduzir custos e aumentar a eficiência na gestão de dados no BigQuery. Embora existam desafios, os benefícios potenciais fazem desta uma decisão estratégica prudente para nossa organização. Este relatório serve como um guia abrangente para entender a lógica por trás da migração proposta e delineia passos acionáveis para sua implementação bem-sucedida.