Sfruttare la potenza di Python in BigQuery con le UDF gestite

1. Introduzione

Structured Query Language (SQL) è lo standard di settore per l'analisi dei data warehouse. Tuttavia, esprimere logica procedurale complessa, calcoli matematici, pulizia del testo o flussi di lavoro di preparazione del machine learning in SQL puro può essere molto difficile.

I team di dati hanno sempre estratto enormi set di dati da BigQuery quando era necessaria un'elaborazione Python personalizzata complessa, li hanno elaborati in macchine virtuali o cluster personalizzati esterni e hanno ricaricato i risultati. Questo approccio introduce un'elevata latenza di rete, aumenta i rischi di conformità spostando i dati e crea un sovraccarico di gestione dell'infrastruttura.

Le funzioni definite dall'utente (UDF) Python gestite di BigQuery risolvono questi problemi eseguendo codice personalizzato su risorse serverless che vengono scalate automaticamente a milioni di righe. Google Cloud gestisce la compilazione, la creazione di immagini, l'applicazione di patch di sicurezza e l'esecuzione, consentendoti di eseguire calcoli personalizzati direttamente dove risiedono i tuoi dati.

In questo codelab creerai una pipeline di analisi e preelaborazione del testo sui dati della community di Stack Overflow, preparandoli per il reporting e il machine learning downstream.

Prerequisiti

  • Un progetto Google Cloud con la fatturazione abilitata.
  • Conoscenza di base dei concetti di SQL, IAM e BigQuery.

Cosa imparerai a fare

  • Come chiamare una UDF Python pubblica precompilata su un set di dati pubblico per analizzare le distribuzioni dei dati.
  • Come eseguire il deployment della tua UDF Python personalizzata utilizzando beautifulsoup4 per pulire i dati non strutturati.
  • Come configurare una connessione alle risorse Cloud BigQuery per scaricare in modo sicuro gli asset di machine learning ed eseguire la tokenizzazione ML locale con la libreria Hugging Face Transformers utilizzando la memorizzazione nella cache dei container in memoria.
  • Come concatenare questi passaggi in una singola pipeline SQL ad alte prestazioni.

2. Configurazione e requisiti

Avvia Cloud Shell

Sebbene Google Cloud possa essere gestito da remoto dal tuo laptop, in questo codelab utilizzerai Google Cloud Shell, un ambiente a riga di comando in esecuzione nel cloud.

  1. Vai alla console Google Cloud, poi seleziona o crea un progetto Google Cloud.
  2. ⚠️ Prendi nota dell'ID progetto. Lo utilizzerai durante tutto il lab.

39b6a5563d69ccfb.png

  1. Apri Cloud Shell in una nuova scheda: https://shell.cloud.google.com/.
  2. Se richiesto, fai clic su Autorizza.
  3. Sostituisci PROJECT_ID e incolla il seguente comando nel terminale:
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

Applica le variabili alla sessione attiva:

source ./env.sh

Abilita le API e crea il set di dati BigQuery

Abilita i servizi Google Cloud necessari nel tuo progetto e crea il set di dati di destinazione:

# 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. Esplorare le distribuzioni dei dati con una UDF Python pubblica

Prima di eseguire il deployment del codice personalizzato, è utile esplorare il set di dati e filtrare il rumore di bassa qualità. In questo passaggio, analizzerai le domande di Stack Overflow per trovare gli utenti attivi e comprendere la distribuzione statistica dei punteggi delle loro domande.

Perché utilizzare una UDF Python per questo?

Il calcolo di percentili esatti multipli (come il 25°, il 50°, il 75° e il 95° percentile) su array di dati raggruppati è complesso e richiede molte risorse in SQL puro. Le funzioni di analisi SQL standard come PERCENTILE_CONT prevedono colonne piatte di righe anziché array nidificati. Per calcolare i percentili esatti degli array preaggregati per riga, dovresti scrivere sottoquery dettagliate che separano, ordinano e riaggregano per ogni metrica percentile, il che è inefficiente.

Utilizzando NumPy, la libreria scientifica altamente ottimizzata di Python all'interno di una UDF, puoi calcolare i percentili matematici esatti su un array di numeri con una sola riga di codice.

Esecuzione

Google Cloud ospita diverse UDF pubbliche precompilate (fai clic sulla scheda Routine). Poiché BigQuery richiede una corrispondenza esplicita dei tipi, utilizzeremo un'espressione di tabella comune (CTE) per pre-aggregare i dati e convertire gli array di numeri interi in array di rappresentazione in virgola mobile utilizzando un'espressione UNNEST.

Esegui la seguente query nella console 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;

In questo modo puoi comprendere immediatamente il rendimento degli utenti senza dover prima configurare le autorizzazioni o scrivere codice Python personalizzato.

Verifica dei risultati

Poiché questa query restituisce tipi di array nidificati (scores e score_percentiles), la scheda Risultati tabellare predefinita in BigQuery Studio potrebbe mostrare un output compresso o troncato, rendendo difficile l'ispezione degli elementi dell'array.

Per visualizzare l'output strutturato e nidificato:

  1. Nel riquadro dei risultati della query, individua la barra delle schede (che per impostazione predefinita è Risultati).
  2. Fai clic sulla scheda JSON.

Dovresti visualizzare un array JSON strutturato che rappresenta le righe, simile al seguente:

[{
  "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"]
}]

Informazioni sull'output

  • scores: l'array completo dei punteggi grezzi delle domande pubblicati da ogni utente unico.
  • score_percentiles: un array contenente quattro valori con virgola mobile calcolati. Questi corrispondono esattamente ai percentili richiesti: [25th, 50th, 75th, and 95th] percentili. Ad esempio, per l'utente 533463, il punteggio del 95° percentile delle sue domande è circa 8.85, il che indica che le sue domande migliori hanno un punteggio elevato.

4. Pulire il testo in modo nativo creando una UDF personalizzata

Una volta identificati gli utenti target, vogliamo analizzare i contenuti dei loro post. Tuttavia, i post grezzi dei forum spesso contengono tag ed entità HTML disordinati. Dobbiamo rimuoverli per migliorare la leggibilità e ridurre i costi del modello downstream.

Per capire perché è necessario, esaminiamo prima l'aspetto del corpo del post di Stack Overflow non formattato. Esegui la seguente query nella console 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 esamini l'output, vedrai un mix di tag di formattazione come <p>, <b>, <code> e altri incorporati nel testo. L'elaborazione diretta tramite i tokenizer di machine learning downstream introdurrebbe rumore non necessario e aumenterebbe artificialmente i costi di importazione dei token.

Perché utilizzare una UDF Python per questo?

L'analisi dell'HTML in modo affidabile utilizzando le espressioni regolari (Regex) in SQL puro è fragile e soggetta a errori di analisi. L'esecuzione di una libreria Python solida come beautifulsoup4 direttamente all'interno delle query offre un modo affidabile per rimuovere i tag.

Esegui la seguente query DDL per eseguire il deployment della funzione clean_html persistente nel tuo set di dati:

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=" ")
''';

Verifica l'output della funzione con una semplice query:

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

Dovresti vedere il testo senza elementi HTML:

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

5. Integrazioni esterne sicure ed elaborazione ML avanzata

Ora che abbiamo il testo pulito, dobbiamo prepararlo per i modelli di machine learning o i modelli linguistici di grandi dimensioni (LLM) come Gemma. Gli LLM non possono leggere direttamente il testo non elaborato, ma elaborano gli ID token numerici.

Per convertire il testo pulito in token, importeremo la libreria transformers di Hugging Face e caricheremo un tokenizer Google T5 pre-addestrato direttamente nel nostro database.

Crea la connessione alle risorse Cloud

Esegui la seguente query nella console BigQuery Studio per stabilire una connessione sicura:

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"
);

Crea la funzione definita dall'utente Tokenizer

Ora esegui il deployment della funzione definita dall'utente del tokenizer personalizzato. Nota come la funzione helper get_tokenizer() controlla se la variabile globale tokenizer è già inizializzata prima di tentare un 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 []
''';

Prova il tokenizer con una query semplice per verificare che scarichi correttamente l'asset e restituisca un array di ID interi:

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

Passa alla scheda JSON nel riquadro dei risultati della query per visualizzare l'array strutturato:

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

6. Esegui la pipeline di preelaborazione end-to-end

Ora che tutti e tre i passaggi della pipeline sono pronti, possiamo concatenarli in un'unica query SQL utilizzando le espressioni di tabella comuni (CTE).

Questa pipeline rappresenta un moderno flusso di lavoro di data engineering:

  1. Isola gli utenti attivi e le loro domande con il punteggio più alto utilizzando la funzione definita dall'utente percentile pubblica.
  2. Rimuovi la formattazione HTML non elaborata dal testo in locale utilizzando la nostra UDF clean_html.
  3. Converti il testo pulito in array di token utilizzando la nostra funzione definita dall'utente tokenize memorizzata nella cache.

Esegui la seguente query della pipeline nella console 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;

Passa alla scheda JSON in BigQuery Studio per esaminare l'output strutturato.

[{
  "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. Appendice: come funziona la pipeline e costi di esecuzione dell'audit

Questa sezione fornisce un'analisi approfondita dei meccanismi specifici della query di preelaborazione end-to-end e mostra come monitorare il consumo esatto di slot e i costi dei container gestiti dell'esecuzione.

Suddivisione dell'architettura della 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
)

Questo primo segmento di query raccoglie i punteggi grezzi delle domande per i collaboratori attivi di Stack Overflow. Consolida i punteggi di ogni utente in un unico array (ARRAY_AGG) applicando un ordine di ordinamento deterministico (ORDER BY id). Il set di dati viene filtrato in modo da includere solo gli utenti con almeno cinque domande per stabilire una base statistica valida.

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
  )
)

Per identificare i principali collaboratori, questo segmento utilizza la funzione definita dall'utente (UDF) Python pubblica percentiles per trovare le distribuzioni esatte dei punteggi (25°, 50°, 75° e 95° percentile). Per evitare di eseguire questa UDF a elevato consumo di risorse di calcolo più volte, il calcolo viene racchiuso in una sottoquery nidificata. Il benchmark del 95° percentile viene quindi recuperato direttamente dall'array risultante nella posizione dell'indice tre (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)
)

Le domande originali vengono unite all'elenco degli utenti attivi per recuperare i post che hanno raggiunto o superato la soglia del 95° percentile. Per evitare errori di confronto del tipo di database, il punteggio di benchmark viene convertito in modo esplicito tramite un'operazione CAST in un tipo FLOAT64 prima della valutazione.

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
)

I corpi dei post non elaborati contengono spesso markup e boilerplate HTML disordinati che peggiorano gli input di machine learning downstream. Anziché utilizzare espressioni regolari complesse, la pipeline chiama la nostra UDF Python clean_html personalizzata. Avvia dinamicamente un runtime Python all'interno di un container isolato, utilizzando la libreria BeautifulSoup per rimuovere gli elementi in modo pulito e restituire testo semplice e leggibile.

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
)

Per preparare l'anteprima del testo pulito per l'importazione del modello generativo, la pipeline richiama la nostra UDF Python tokenize personalizzata su una sezione di 120 caratteri. La UDF contatta in modo sicuro Hugging Face Hub per scaricare i parametri del tokenizer Google T5. Poiché l'istanza del tokenizer viene caricata in una variabile globale, il container caldo memorizza nella cache la configurazione, consentendo alle righe successive di essere sottoposte a una rapida tokenizzazione in memoria senza latenza di rete.

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;

Il blocco di query finale restituisce il set di dati elaborato. Anziché eseguire la UDF di tokenizzazione una seconda volta per conteggiare i token generati, la funzione nativa ARRAY_LENGTH di BigQuery viene applicata direttamente all'array token_ids precalcolato. Questa strategia riduce i cicli della CPU ridondanti, le operazioni sui container e i costi di esecuzione complessivi.

Controllo del consumo di slot e dei costi delle UDF gestite

Mentre BigQuery implementa dashboard complete di visibilità dei costi direttamente nell'interfaccia utente della console Google Cloud, gli ingegneri possono controllare a livello di programmazione i costi esatti di consumo degli slot e di esecuzione dei container gestiti di qualsiasi query utilizzando gli ID job BigQuery.

Per controllare l'esecuzione della query, individua l'ID job.

  1. In BigQuery Studio, puoi trovare questa informazione nella scheda Cronologia query nella parte inferiore della console.
  2. Fai clic sulla query della pipeline eseguita
  3. Nel riquadro dei dettagli Informazioni sul job, individua il campo ID job.

Una volta identificato l'ID job puro, sostituisci JOB_ID nella query riportata di seguito ed eseguila in 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";

Passa alla scheda JSON in BigQuery Studio per esaminare l'output strutturato. Dovresti ricevere un payload simile al seguente:

[{
  "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"
  }]
}]

Comprendere l'output:

  • total_slot_ms: il tempo di calcolo totale in millisecondi utilizzato in tutte le fasi della query. Per questa pipeline unificata, l'esecuzione in genere è in media di circa 815.000 millisecondi di slot.
  • external_service_costs: un array che suddivide le risorse utilizzate al di fuori del motore di analisi BigQuery standard.
  • external_service: il valore "MANAGED_ROUTINE_EXECUTION" conferma che il costo appartiene specificamente all'hosting di esecuzione dei container serverless che ospitano il nostro ambiente UDF Python personalizzato.
  • slot_ms: il valore "3000" rappresenta i millisecondi esatti delle risorse di calcolo specializzate consumate all'interno del runtime del contenitore caldo per eseguire la logica Python.
  • billing_method: il valore "SERVICES_SKU" indica che questi addebiti per i container localizzati vengono fatturati in modo dinamico tramite lo SKU specializzato BigQuery Services in base alla durata di esecuzione del container e all'overhead di memoria. Al prezzo standard di calcolo multiregionale degli Stati Uniti di 0,06 $per ora slot (consulta la pagina dei prezzi dei servizi BigQuery), il costo di esecuzione puro di 3000 slot-ms viene calcolato come (3000 ms / 3.600.000 ms) * 0,06 $ = 0,00005$, il che dimostra un flusso di lavoro conveniente.

8. Libera spazio nelle risorse cloud

Per evitare addebiti continui o il consumo di quote del progetto, elimina il set di dati e le connessioni BigQuery in 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. Complimenti!

Hai completato il codelab sulla creazione e la protezione delle UDF Python all'interno del runtime serverless di BigQuery.

In questo codelab hai imparato a:

  • Esplora i dati con le UDF pubbliche:chiama le UDF Python pubbliche precompilate sui set di dati di Stack Overflow per eseguire operazioni matematiche sui percentili su array aggregati.
  • Integra pacchetti di terze parti:esegui il deployment di una funzione definita dall'utente persistente personalizzata utilizzando il runtime Python standard e la libreria beautifulsoup4 per rimuovere i tag HTML non elaborati in modo nativo all'interno delle query SQL.
  • Configura le connessioni esterne sicure:crea una connessione alle risorse Cloud BigQuery per concedere in modo sicuro ai container UDF isolati l'accesso alla rete in uscita per recuperare asset esterni senza codificare le credenziali.
  • Implementa la tokenizzazione locale con memorizzazione nella cache in memoria:importa la libreria Hugging Face transformers per caricare un tokenizer T5, utilizzando le variabili globali per memorizzare nella cache i file di configurazione ed elaborare le righe all'interno dei container attivi.
  • Controlla le prestazioni e i costi di esecuzione:esegui query a livello di programmazione sulle viste INFORMATION_SCHEMA.JOBS regionali utilizzando gli ID job BigQuery per monitorare il consumo di slot (total_slot_ms) e i costi di utilizzo dei container (external_service_costs).

Passaggi successivi