Como trazer o poder do Python para o BigQuery com UDFs gerenciadas

1. Introdução

A linguagem de consulta estruturada (SQL) é o padrão do setor para análise de data warehouse. No entanto, expressar lógica processual complexa, cálculos matemáticos, limpeza de texto ou fluxos de trabalho de preparação de machine learning em SQL puro pode ser muito desafiador.

Historicamente, as equipes de dados extraíam conjuntos de dados enormes do BigQuery quando era necessário um processamento personalizado complexo do Python, processavam esses dados em máquinas virtuais ou clusters personalizados externos e carregavam os resultados de volta. Essa abordagem introduz alta latência de rede, aumenta os riscos de conformidade ao mover dados e cria uma sobrecarga de gerenciamento de infraestrutura.

As funções definidas pelo usuário (UDFs) do Python gerenciadas pelo BigQuery resolvem esses problemas executando código personalizado em recursos sem servidor que são escalonados automaticamente para milhões de linhas. O Google Cloud gerencia a compilação, a criação de imagens, a aplicação de patches de segurança e a execução, permitindo que você execute cálculos personalizados diretamente onde os dados estão.

Neste codelab, você vai criar um pipeline analítico e de pré-processamento de texto nos dados da comunidade do Stack Overflow, preparando-os para relatórios downstream e machine learning.

Pré-requisitos

• Ter um projeto do Google Cloud com o faturamento ativado.
• Entendimento básico de SQL, IAM e conceitos do BigQuery.

O que você vai aprender

• Como chamar uma UDF pública do Python pré-compilada em um conjunto de dados público para analisar distribuições de dados.
• Como implantar sua própria UDF personalizada do Python usando beautifulsoup4 para limpar dados não estruturados.
• Como configurar uma conexão a recursos do Cloud BigQuery para fazer o download seguro de recursos de machine learning e realizar a tokenização de ML local com a biblioteca de transformadores do Hugging Face usando o armazenamento em cache de contêiner na memória.
• Como encadear essas etapas em um único pipeline SQL de alta performance.

2. Configuração e requisitos

Iniciar Cloud Shell

Embora o Google Cloud possa ser operado remotamente do seu laptop, neste codelab você usará o Google Cloud Shell, um ambiente de linha de comando executado no Cloud.

1. Navegue até o console do Google Cloud e selecione ou crie um projeto na nuvem do Google Cloud.
2. ⚠️ Anote o ID do projeto. Você vai usá-lo ao longo deste laboratório.

3. Abra o Cloud Shell em uma nova guia: https://shell.cloud.google.com/.
4. Se for solicitado, clique em Autorizar.
5. Substitua PROJECT_ID e cole o comando a seguir no terminal:

cat << 'EOF' > env.sh
#!/bin/bash
# env.sh: Environment variables for BigQuery Python UDFs codelab

# ⚠️ Replace 'YOUR_PROJECT_ID' with your actual Google Cloud Project ID
export PROJECT_ID="YOUR_PROJECT_ID"
export REGION="us"
export BQ_DATASET="python_udfs"
export BQ_RESOURCE_CONN="external_api_connection"
EOF

Aplique as variáveis à sua sessão ativa:

source ./env.sh

💡 Por que a região "us"?

O conjunto de dados público bigquery-public-data.stackoverflow que vamos usar reside na multirregião us. Para consultá-lo usando suas UDFs personalizadas do Python sem erros entre regiões, o conjunto de dados de destino (python_udfs) precisa estar localizado na mesma região (us).

Ativar APIs e criar um conjunto de dados do BigQuery

Ative os serviços necessários do Google Cloud no seu projeto e crie o conjunto de dados de destino:

# Enable API Services
gcloud services enable \
  bigquery.googleapis.com \
  bigqueryconnection.googleapis.com --quiet

# Create BigQuery Dataset
bq mk --location=${REGION} --dataset ${PROJECT_ID}:${BQ_DATASET}

3. Conhecer as distribuições de dados com uma UDF pública do Python

Antes de implantar o código personalizado, é útil explorar o conjunto de dados e filtrar ruídos de baixa qualidade. Nesta etapa, você vai analisar as perguntas do Stack Overflow para encontrar usuários ativos e entender a distribuição estatística das pontuações das perguntas.

Por que usar uma UDF do Python para isso?

O cálculo de vários percentis exatos (como o 25º, 50º, 75º e 95º percentis) em matrizes de dados agrupados é complexo e exige muitos recursos em SQL puro. As funções analíticas SQL padrão, como PERCENTILE_CONT, esperam colunas planas de linhas em vez de matrizes aninhadas. Para calcular percentis exatos de matrizes pré-agregadas por linha, você precisaria escrever subconsultas detalhadas que desagrupam, classificam e reagrupam para cada métrica de percentil, o que é ineficiente.

Ao usar o NumPy, a biblioteca científica altamente otimizada do Python dentro de uma UDF, é possível calcular percentis matemáticos exatos em uma matriz de números com uma única linha de código.

Execução

O Google Cloud hospeda várias UDFs públicas pré-compiladas (clique na guia Rotinas). Como o BigQuery exige correspondência de tipo explícita, vamos usar uma expressão de tabela comum (CTE, na sigla em inglês) para pré-agregar os dados e converter as matrizes de números inteiros em matrizes de ponto flutuante usando uma expressão UNNEST.

Execute a consulta a seguir no console do BigQuery Studio:

WITH raw_user_scores AS (
  -- 1. Pre-aggregate user scores into an array
  SELECT 
    owner_user_id, 
    ARRAY_AGG(score) AS scores
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`
  WHERE 
    owner_user_id IS NOT NULL
  GROUP BY 
    owner_user_id
  HAVING 
    ARRAY_LENGTH(scores) >= 5
  LIMIT 5
)
SELECT 
  owner_user_id,
  scores,
  -- 2. Cast arrays to FLOAT64 and call the public percentile Python UDF
  `bigquery-public-data.python_udfs.percentiles`(
    ARRAY(SELECT CAST(s AS FLOAT64) FROM UNNEST(scores) AS s), 
    [25.0, 50.0, 75.0, 95.0]
  ) AS score_percentiles
FROM 
  raw_user_scores;

Isso permite entender a performance do usuário instantaneamente, sem precisar configurar permissões ou escrever código Python personalizado primeiro.

Como verificar os resultados

Como essa consulta retorna tipos de matriz aninhada (scores e score_percentiles), a guia Resultados tabular padrão no BigQuery Studio pode mostrar uma saída achatada ou truncada, dificultando a inspeção dos elementos da matriz.

Para conferir a saída estruturada e aninhada:

1. No painel de resultados da consulta, localize a barra de guias (que é definida como Resultados por padrão).
2. Clique na guia JSON.

Você verá uma matriz JSON estruturada que representa as linhas, semelhante a esta:

[{
  "owner_user_id": "533463",
  "scores": ["0", "0", "-1", "0", "0", "2", "-1", "1", "0", "0", "-1", "0", "-3", "1", "1", "0", "1", "2", "3", "1", "0", "0", "1", "0", "0", "3", "6", "11", "0", "1", "0", "0", "3", "17", "0", "1", "1", "3", "5", "-2", "1", "-1", "-1", "2", "3", "0", "0", "0", "5", "0", "4", "0", "0", "0", "3", "3", "0", "140", "0", "1", "3", "0", "0", "-2", "-1", "0", "0", "2", "0", "9", "9", "0", "0", "1", "0", "0", "1", "-1", "0", "0", "0", "0"],
  "score_percentiles": ["0.0", "0.0", "1.75", "8.8500000000000085"]
}, {
  "owner_user_id": "13502536",
  "scores": ["0", "1", "0", "-5", "0", "1", "0", "1", "0", "0", "-2", "0", "1", "0", "1", "0", "0", "1", "0", "1", "0", "0"],
  "score_percentiles": ["0.0", "0.0", "1.0", "1.0"]
}, {
  "owner_user_id": "1170153",
  "scores": ["1", "0", "1", "0", "1", "0", "2", "0", "0", "0", "10", "5", "1", "0", "0", "2", "0", "2", "3", "-1", "1", "0", "1", "0", "0", "1", "0", "2", "0", "4", "0", "3", "0", "0", "2", "0", "0", "1", "0"],
  "score_percentiles": ["0.0", "0.0", "1.5", "4.1000000000000014"]
}, {
  "owner_user_id": "8558174",
  "scores": ["0", "0", "-1", "1", "2", "0"],
  "score_percentiles": ["0.0", "0.0", "0.75", "1.75"]
}, {
  "owner_user_id": "1073044",
  "scores": ["0", "1", "0", "0", "2", "2", "2", "1", "1", "1", "2", "1", "0", "2", "3", "1"],
  "score_percentiles": ["0.75", "1.0", "2.0", "2.25"]
}]

Entender a saída

• scores: a matriz completa de pontuações de perguntas brutas postadas por cada usuário exclusivo.
• score_percentiles: uma matriz que contém quatro valores de ponto flutuante calculados. Eles correspondem exatamente aos percentis solicitados: [25th, 50th, 75th, and 95th]. Por exemplo, para o usuário 533463, a pontuação do 95º percentil das perguntas é de aproximadamente 8.85, indicando que as principais perguntas têm uma pontuação alta.

4. Limpar texto nativamente criando uma UDF personalizada

Depois que os usuários de destino forem identificados, vamos analisar o conteúdo da postagem. No entanto, as postagens brutas do fórum geralmente contêm tags e entidades HTML confusas. Precisamos remover esses elementos para melhorar a legibilidade e reduzir os custos do modelo downstream.

Para entender por que isso é necessário, vamos inspecionar primeiro a aparência do corpo da postagem bruta e não formatada do Stack Overflow. Execute a consulta a seguir no console do BigQuery Studio:

SELECT
  id,
  title,
  body AS raw_html_body
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
  -- Check specific questions that we will use in our final pipeline
WHERE
  id IN (9, 17, 33969)
ORDER BY
  id ASC;

Se você examinar a saída, verá uma combinação de tags de formatação, como <p>, <b>, <code>e outras incorporadas no texto. O processamento direto delas usando tokenizadores de machine learning downstream introduziria ruído desnecessário e aumentaria artificialmente os custos de ingestão de tokens.

Por que usar uma UDF do Python para isso?

A análise confiável de HTML usando expressões regulares (Regex) em SQL puro é frágil e propensa a erros de análise. A execução de uma biblioteca Python robusta, como beautifulsoup4, diretamente nas consultas oferece uma maneira confiável de remover tags.

Execute a consulta DDL a seguir para implantar a função clean_html persistente no conjunto de dados:

⚠️ Observação sobre a execução de SQL :

1. Ao copiar consultas SQL para o console do BigQuery, lembre-se de substituir YOUR_PROJECT_ID pelo seu ID do projeto do Google Cloud.
2. Como o BigQuery provisiona ambientes de contêiner de forma assíncrona, a primeira consulta que invoca uma nova UDF pode apresentar um atraso de inicialização a frio. É possível monitorar o status da implantação na lista Rotinas do conjunto de dados no painel do Explorer.

CREATE OR REPLACE FUNCTION `YOUR_PROJECT_ID.python_udfs.clean_html`(html_content STRING)
RETURNS STRING
LANGUAGE python
OPTIONS (
  runtime_version = 'python-3.11',
  entry_point = 'strip_tags',
  packages = ['beautifulsoup4>=4.12.0']
) AS r'''
from bs4 import BeautifulSoup

def strip_tags(html_content):
    if not html_content:
        return ""
    soup = BeautifulSoup(html_content, "html.parser")
    return soup.get_text(separator=" ")
''';

Verifique a saída da função com uma consulta simples:

SELECT `YOUR_PROJECT_ID.python_udfs.clean_html`('<p>Hello <b>world</b>!</p>') AS cleaned_text;

O texto removido será exibido sem elementos HTML:

+----------------+
| cleaned_text   |
+----------------+
| Hello  world ! |
+----------------+

5. Proteger integrações externas e processamento avançado de ML

Agora que temos texto limpo, precisamos prepará-lo para modelos de machine learning ou modelos de linguagem grandes (LLMs), como o Gemma. Os LLMs não podem ler texto bruto diretamente. Eles processam IDs de token numéricos.

Para converter nosso texto limpo em tokens, vamos importar a biblioteca transformers do Hugging Face e carregar um tokenizador T5 do Google pré-treinado diretamente no nosso banco de dados.

💡 Por que precisamos de uma conexão segura?

Por padrão, as UDFs do Python do BigQuery são executadas dentro de um contêiner seguro e isolado, sem acesso à rede de saída. Como precisamos fazer o download dos arquivos do tokenizador T5 pré-treinado do Hugging Face Hub público, precisamos estabelecer uma conexão a recursos do Cloud BigQuery. Essa conexão atua como um gateway seguro, concedendo ao contêiner a saída de rede para buscar os recursos necessários.

Além disso, a criação dessa conexão provisiona automaticamente uma conta de serviço exclusiva do Google Cloud. Se a UDF do Python precisar interagir com APIs externas restritas ou recursos seguros da nuvem, você poderá conceder a essa conta de serviço permissões específicas do IAM para processar a autenticação com segurança.

Criar a conexão a recursos do Cloud

Execute a consulta a seguir no console do BigQuery Studio para estabelecer uma conexão segura:

CREATE CONNECTION IF NOT EXISTS `YOUR_PROJECT_ID.us.external_api_connection`
OPTIONS (
  connection_type = "CLOUD_RESOURCE",
  friendly_name = "Hugging Face Hub Egress Connection",
  description = "Connection used to securely download model configs from public ML hubs"
);

Criar a UDF do tokenizador

Agora, implante a UDF do tokenizador personalizado. Observe como a função auxiliar get_tokenizer() verifica se a variável global tokenizer já foi inicializada antes de tentar um download:

CREATE OR REPLACE FUNCTION `YOUR_PROJECT_ID.python_udfs.tokenize`(text STRING)
RETURNS ARRAY<INT64>
LANGUAGE python
WITH CONNECTION `YOUR_PROJECT_ID.us.external_api_connection`
OPTIONS (
  runtime_version = 'python-3.11',
  entry_point = 'tokenize',
  packages = ['transformers', 'sentencepiece']
) AS r'''
from transformers import T5TokenizerFast

# Initialize global variable for in-memory container caching
tokenizer = None

def get_tokenizer():
    global tokenizer
    if tokenizer is None:
        # Securely download T5 tokenizer config from Hugging Face Hub (runs once per warm container)
        tokenizer = T5TokenizerFast.from_pretrained("t5-base")
    return tokenizer

def tokenize(text):
    if not text:
        return []
    try:
        t = get_tokenizer()
        # Convert raw clean text into integer token IDs
        return [int(x) for x in t.encode(text)]
    except Exception:
        return []
''';

Teste o tokenizador com uma consulta simples para verificar se ele faz o download do recurso e retorna uma matriz de IDs de números inteiros:

SELECT `YOUR_PROJECT_ID.python_udfs.tokenize`('Hello world!') AS token_ids;

Mude para a guia JSON no painel de resultados da consulta para conferir a matriz estruturada:

[
  {
    "token_ids": ["8774", "296", "55", "1"]
  }
]

6. Executar o pipeline de pré-processamento de ponta a ponta

Agora que todas as três etapas do nosso pipeline estão prontas, podemos encadeá-las em uma única consulta SQL usando expressões de tabela comuns (CTEs).

Esse pipeline representa um fluxo de trabalho moderno de engenharia de dados:

1. Isole os usuários ativos e as perguntas com maior pontuação usando a UDF de percentil pública.
2. Remova a formatação HTML bruta do texto localmente usando nossa UDF clean_html.
3. Converta o texto limpo em matrizes de token usando nossa UDF de tokenização armazenada em cache.

Execute a consulta de pipeline a seguir no console do BigQuery Studio:

WITH raw_user_scores AS (
  -- Step 1: Pre-aggregate scores to safely run percentiles with deterministic ordering
  SELECT 
    owner_user_id, 
    ARRAY_AGG(score ORDER BY id ASC) AS scores
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`
  WHERE 
    owner_user_id IS NOT NULL
  GROUP BY 
    owner_user_id
  HAVING 
    ARRAY_LENGTH(scores) >= 5
  ORDER BY 
    owner_user_id ASC
  LIMIT 3
),
active_users AS (
  -- Step 1: Extract exact percentile limits using the public UDF)
  SELECT 
    owner_user_id,
    percentiles_arr AS score_percentiles,
    -- Extract the 95th percentile score from the array's 4th element (OFFSET 3) directly
    percentiles_arr[OFFSET(3)] AS p95_score
  FROM (
    SELECT 
      owner_user_id,
      `bigquery-public-data.python_udfs.percentiles`(
        ARRAY(SELECT CAST(s AS FLOAT64) FROM UNNEST(scores) AS s), 
        [25.0, 50.0, 75.0, 95.0]
      ) AS percentiles_arr
    FROM 
      raw_user_scores
  )
),
target_questions AS (
  -- Isolate high-scoring questions from active users
  SELECT 
    q.id,
    q.owner_user_id,
    q.title,
    q.body AS raw_body,
    u.score_percentiles
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions` q
  JOIN 
    active_users u ON q.owner_user_id = u.owner_user_id
  WHERE 
    -- Explicit cast for robust comparison
    q.score >= CAST(u.p95_score AS FLOAT64)
),
cleaned_data AS (
  -- Step 2: Clean HTML tags natively
  SELECT 
    id,
    owner_user_id,
    title,
    score_percentiles,
    `YOUR_PROJECT_ID.python_udfs.clean_html`(raw_body) AS cleaned_body
  FROM 
    target_questions
),
tokenized_data AS (
  -- Step 3: Perform local ML tokenization on the clean preview text
  SELECT 
    id,
    owner_user_id,
    title,
    score_percentiles,
    SUBSTR(cleaned_body, 1, 120) AS cleaned_body_preview,
    `YOUR_PROJECT_ID.python_udfs.tokenize`(SUBSTR(cleaned_body, 1, 120)) AS token_ids
  FROM 
    cleaned_data
)
SELECT 
  id,
  owner_user_id,
  title,
  score_percentiles,
  cleaned_body_preview AS cleaned_body,
  token_ids,
  ARRAY_LENGTH(token_ids) AS token_count
FROM 
  tokenized_data
ORDER BY 
  id ASC;

Mude para a guia JSON no BigQuery Studio para examinar a saída estruturada.

[{
  "id": "9",
  "owner_user_id": "1",
  "title": "How do I calculate someone\u0027s age based on a DateTime type birthday?",
  "score_percentiles": ["22.5", "61.5", "346.75", "1762.0"],
  "cleaned_body": "Given a DateTime representing a person\u0027s birthday, how do I calculate their age in years?",
  "token_ids": ["9246", "3", "9", "7678", "13368", "9085", "3", "9", "568", "31", "7", "3591", "6", "149", "103", "27", "11837", "70", "1246", "16", "203", "58", "1"],
  "token_count": "23"
}, {
  "id": "17",
  "owner_user_id": "2",
  "title": "Binary Data in MySQL",
  "score_percentiles": ["3.5", "10.0", "90.0", "184.09999999999997"],
  "cleaned_body": "How do I store binary data in MySQL ?",
  "token_ids": ["571", "103", "27", "1078", "14865", "331", "16", "27563", "3", "58", "1"],
  "token_count": "11"
}, {
  "id": "33969",
  "owner_user_id": "3",
  "title": "Best way to implement request throttling in ASP.NET MVC?",
  "score_percentiles": ["3.25", "14.0", "24.75", "175.25"],
  "cleaned_body": "We\u0027re experimenting with various ways to throttle user actions in a given time period : Limit question/answer posts Limi",
  "token_ids": ["101", "31", "60", "3", "26718", "28", "796", "1155", "12", "28731", "1139", "2874", "16", "3", "9", "787", "97", "1059", "3", "10", "18185", "822", "87", "3247", "3321", "3489", "10908", "23", "1"],
  "token_count": "29"
}]

7. Apêndice: como o pipeline funciona e custos de execução de auditoria

Esta seção oferece uma análise detalhada da mecânica específica da consulta de pré-processamento de ponta a ponta e demonstra como monitorar o consumo exato de slots e os custos de contêiner gerenciado da execução.

Detalhes da arquitetura do pipeline

WITH raw_user_scores AS (
  SELECT 
    owner_user_id, 
    ARRAY_AGG(score ORDER BY id ASC) AS scores
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`
  WHERE 
    owner_user_id IS NOT NULL
  GROUP BY 
    owner_user_id
  HAVING 
    ARRAY_LENGTH(scores) >= 5
  ORDER BY 
    owner_user_id ASC
  LIMIT 3
)

Esse primeiro segmento de consulta coleta pontuações de perguntas brutas para colaboradores ativos do Stack Overflow. Ele consolida as pontuações de cada usuário em uma única matriz (ARRAY_AGG) e aplica uma ordem de classificação determinística (ORDER BY id). O conjunto de dados é filtrado para incluir apenas usuários com pelo menos cinco perguntas para estabelecer uma linha de base estatística válida.

active_users AS (
  SELECT 
    owner_user_id,
    percentiles_arr AS score_percentiles,
    -- Extract the 95th percentile score from the array's 4th element (OFFSET 3) directly
    percentiles_arr[OFFSET(3)] AS p95_score
  FROM (
    SELECT 
      owner_user_id,
      `bigquery-public-data.python_udfs.percentiles`(
        ARRAY(SELECT CAST(s AS FLOAT64) FROM UNNEST(scores) AS s), 
        [25.0, 50.0, 75.0, 95.0]
      ) AS percentiles_arr
    FROM 
      raw_user_scores
  )
)

Para identificar os principais colaboradores, esse segmento usa a UDF pública do Python percentiles para encontrar distribuições de pontuação exatas (25º, 50º, 75º e 95º percentis). Para evitar a execução dessa UDF computacionalmente intensiva várias vezes, o cálculo é encapsulado em uma subconsulta aninhada. O benchmark do 95º percentil é recuperado diretamente da matriz resultante na posição de índice três (OFFSET(3)).

target_questions AS (
  -- Isolate high-scoring questions from active users
  SELECT 
    q.id,
    q.owner_user_id,
    q.title,
    q.body AS raw_body,
    u.score_percentiles
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions` q
  JOIN 
    active_users u ON q.owner_user_id = u.owner_user_id
  WHERE 
    -- Explicit cast for robust comparison
    q.score >= CAST(u.p95_score AS FLOAT64)
)

As perguntas originais são unidas à lista de usuários ativos para recuperar postagens que atendam ou excedam o limite do 95º percentil. Para evitar erros de comparação de tipo de banco de dados, a pontuação de benchmark é convertida explicitamente por uma operação CAST em um tipo FLOAT64 antes da avaliação.

cleaned_data AS (
  -- Clean HTML tags natively
  SELECT 
    id,
    owner_user_id,
    title,
    score_percentiles,
    `YOUR_PROJECT_ID.python_udfs.clean_html`(raw_body) AS cleaned_body
  FROM 
    target_questions
)

Os corpos de postagens brutas geralmente contêm marcação confusa e boilerplate HTML que degradam as entradas de machine learning downstream. Em vez de usar expressões regulares complexas, o pipeline chama nossa UDF clean_html personalizada do Python. Ele inicia dinamicamente um ambiente de execução do Python dentro de um contêiner isolado, usando a biblioteca BeautifulSoup para remover elementos de forma limpa e gerar texto simples e legível.

tokenized_data AS (
  -- Perform local ML tokenization on the clean preview text (called only once)
  SELECT 
    id,
    owner_user_id,
    title,
    score_percentiles,
    SUBSTR(cleaned_body, 1, 120) AS cleaned_body_preview,
    `YOUR_PROJECT_ID.python_udfs.tokenize`(SUBSTR(cleaned_body, 1, 120)) AS token_ids
  FROM 
    cleaned_data
)

Para preparar a visualização de texto limpo para ingestão de modelo generativo, o pipeline invoca nossa UDF tokenize personalizada do Python em uma fatia de 120 caracteres. A UDF entra em contato com o Hugging Face Hub para fazer o download dos parâmetros do tokenizador T5 do Google. Como a instância do tokenizador é carregada em uma variável global, o contêiner quente armazena em cache a configuração, permitindo que as linhas subsequentes passem por uma tokenização rápida na memória sem latência de rede.

SELECT 
  id,
  owner_user_id,
  title,
  score_percentiles,
  cleaned_body_preview AS cleaned_body,
  token_ids,
  ARRAY_LENGTH(token_ids) AS token_count
FROM 
  tokenized_data
ORDER BY 
  id ASC;

O bloco de consulta final gera o conjunto de dados processado. Em vez de executar a UDF de tokenização uma segunda vez para contar os tokens gerados, a função nativa ARRAY_LENGTH do BigQuery é aplicada diretamente à matriz token_ids pré-calculada. Essa estratégia reduz os ciclos de CPU redundantes, as operações de contêiner e os custos gerais de execução.

Auditoria do consumo de slots e custos de UDF gerenciados

Embora o BigQuery esteja lançando painéis abrangentes de visibilidade de custos diretamente na interface do console do Google Cloud, os engenheiros podem auditar programaticamente o consumo exato de slots e os custos de execução de contêiner gerenciado de qualquer consulta usando IDs de job do BigQuery.

Para auditar a execução da consulta, localize o código da tarefa.

1. No BigQuery Studio, você pode encontrar isso navegando até a guia Histórico de consultas na parte de baixo do console.
2. Clique na consulta de pipeline executada.
3. No painel de detalhes Informações do job, localize o campo código da tarefa.

💡 Entender formatos de código da tarefa

No console do Google Cloud, um código da tarefa completo do BigQuery é estruturado no seguinte formato:

{YOUR_PROJECT_ID}:{REGION}.{JOB_ID}

Por exemplo, um código da tarefa completo real pode ter esta aparência:

my-gcp-project:us.bquxjob_2d801c6d_19ecde92e15

Ao consultar as tabelas de metadados regionais (INFORMATION_SCHEMA.JOBS), é necessário isolar e usar apenas o componente {JOB_ID} puro (por exemplo, bquxjob_2d801c6d_19ecde92e15). Não inclua o ID do projeto ou o prefixo da região anterior, porque isso impedirá que a visualização de metadados corresponda ao registro de execução.

Depois de identificar o código da tarefa puro, substitua o JOB_ID na consulta abaixo e execute-o no BigQuery Studio:

SELECT 
  job_id,
  total_slot_ms,
  external_service_costs
FROM 
  `YOUR_PROJECT_ID.region-us`.INFORMATION_SCHEMA.JOBS
WHERE 
  job_id = "JOB_ID";

Mude para a guia JSON no BigQuery Studio para examinar a saída estruturada. Você vai receber um payload semelhante a este:

[{
  "job_id": "bquxjob_1234f5a_67ea8c9051a",
  "total_slot_ms": "815459",
  "external_service_costs": [{
    "external_service": "MANAGED_ROUTINE_EXECUTION",
    "bytes_processed": null,
    "bytes_billed": null,
    "slot_ms": "3000",
    "reserved_slot_count": null,
    "billing_method": "SERVICES_SKU"
  }]
}]

Entender a saída:

• total_slot_ms: o tempo total de computação em milissegundos usado em todas as etapas da consulta. Para esse pipeline unificado, a execução geralmente tem uma média de 815 mil milissegundos de slot.
• external_service_costs: uma matriz que detalha os recursos utilizados fora do mecanismo de análise padrão do BigQuery.
• external_service: o valor "MANAGED_ROUTINE_EXECUTION" confirma que o custo pertence especificamente à execução do contêiner sem servidor que hospeda nosso ambiente de UDF personalizado do Python.
• slot_ms: o valor "3000" representa os milissegundos exatos de recursos de computação especializados consumidos dentro do ambiente de execução do contêiner quente para executar a lógica do Python.
• billing_method: o valor "SERVICES_SKU" indica que essas cobranças de contêiner localizadas são faturadas dinamicamente pela SKU de serviços especializados do BigQuery com base na duração da execução do contêiner e na sobrecarga de memória. No preço de computação padrão da multirregião dos EUA de US $0,06 por hora de slot (consulte a página de preços dos serviços do BigQuery), o custo de execução puro de 3.000 milissegundos de slot é calculado como (3.000 ms / 3.600.000 ms) * US$ 0,06 = US$0,00005, demonstrando um fluxo de trabalho econômico.

⚠️ Observação:o custo real da transação na sua fatura pode ser um pouco maior porque esse valor cobre apenas a execução do contêiner quente. Ele não inclui as taxas padrão de verificação de dados sob demanda para leitura do conjunto de dados público do Stack Overflow ou o custo de criação de rotina única para empacotar BeautifulSoup/Transformers durante inicializações a frio.

8. Limpar recursos da nuvem

Para evitar cobranças contínuas ou consumir cotas de projetos, exclua o conjunto de dados e as conexões do BigQuery no Cloud Shell:

# Cleanup BigQuery routines
bq rm -f --routine ${PROJECT_ID}:${BQ_DATASET}.clean_html
bq rm -f --routine ${PROJECT_ID}:${BQ_DATASET}.tokenize

# Cleanup connection
bq rm -f --connection --location=${REGION} ${PROJECT_ID}.${REGION}.${BQ_RESOURCE_CONN}

# Cleanup BigQuery Dataset
bq rm -r -f -d ${PROJECT_ID}:${BQ_DATASET}

9. Parabéns!

Você concluiu o codelab sobre como criar e proteger UDFs do Python no ambiente de execução sem servidor do BigQuery.

Neste codelab, você aprendeu a:

• Explorar dados com UDFs públicas:chame UDFs públicas do Python pré-compiladas em conjuntos de dados do Stack Overflow para realizar operações de percentil matemático em matrizes agregadas.
• Integrar pacotes de terceiros:implante uma UDF persistente personalizada usando o ambiente de execução padrão do Python e a biblioteca beautifulsoup4 para remover tags HTML brutas nativamente nas consultas SQL.
• Configurar conexões externas seguras:crie uma conexão de recursos do Cloud BigQuery para conceder acesso seguro à rede de saída de contêineres de UDF isolados para buscar recursos externos sem codificar credenciais.
• Implementar a tokenização local com o armazenamento em cache na memória:importe a biblioteca transformers do Hugging Face para carregar um tokenizador T5, usando variáveis globais para armazenar em cache arquivos de configuração e processar linhas dentro de contêineres quentes.
• Auditar a performance e os custos de execução:consulte programaticamente as visualizações INFORMATION_SCHEMA.JOBS regionais usando IDs de job do BigQuery para acompanhar o consumo de slots (total_slot_ms) e os custos de uso do contêiner (external_service_costs).

Qual é a próxima etapa?

• Analisar práticas recomendadas e cotas: saiba mais sobre limites de execução, simultaneidade e considerações de memória no guia de limites de UDF do Python do BigQuery.
• Aprofundar a otimização de custos: entenda como o faturamento funciona para ambientes de UDF em contêineres e chamadas de conexão externa na documentação de preços do BigQuery.
• Criar aplicativos com tecnologia de IA no BigQuery:vá além da manipulação básica de texto e aprenda a construir sistemas multimodais de ponta a ponta. Execute o codelab Criar um marketplace de veículos com tecnologia de IA usando o BigQuery e os modelos do Gemini para aproveitar a pesquisa semântica e os modelos do Gemini diretamente no seu data warehouse.