Exploiter la puissance de Python dans BigQuery avec les UDF gérées

1. Introduction

Le langage de requête structuré (SQL) est la norme du secteur pour l'analyse des entrepôts de données. Toutefois, exprimer une logique procédurale complexe, des calculs mathématiques, le nettoyage de texte ou des workflows de préparation au machine learning en SQL pur peut être très difficile.

Les équipes de données ont toujours extrait d'énormes ensembles de données de BigQuery lorsqu'un traitement Python personnalisé complexe était nécessaire. Elles les traitaient ensuite dans des clusters ou des machines virtuelles personnalisés externes, puis chargeaient les résultats. Cette approche introduit une latence réseau élevée, augmente les risques de non-conformité en déplaçant les données et crée une surcharge de gestion de l'infrastructure.

Les fonctions définies par l'utilisateur (UDF) Python gérées par BigQuery résolvent ces problèmes en exécutant du code personnalisé sur des ressources sans serveur qui évoluent automatiquement pour traiter des millions de lignes. Google Cloud gère la compilation, la création d'images, l'application de correctifs de sécurité et l'exécution, ce qui vous permet d'exécuter des calculs personnalisés directement là où se trouvent vos données.

Dans cet atelier de programmation, vous allez créer un pipeline d'analyse et de prétraitement de texte sur les données de la communauté Stack Overflow, en les préparant pour le reporting en aval et le machine learning.

Prérequis

  • Un projet Google Cloud avec facturation activée.
  • Connaissances de base des concepts SQL, IAM et BigQuery.

Points abordés

  • Comment appeler une UDF Python publique précompilée sur un ensemble de données public pour analyser les distributions de données.
  • Découvrez comment déployer votre propre UDF Python personnalisée à l'aide de beautifulsoup4 pour nettoyer des données non structurées.
  • Configurer une connexion de ressource cloud BigQuery pour télécharger de manière sécurisée des composants de machine learning et effectuer une tokenisation ML locale avec la bibliothèque Hugging Face Transformers à l'aide de la mise en cache des conteneurs en mémoire.
  • Comment enchaîner ces étapes dans un seul pipeline SQL hautes performances.

2. Préparation

Démarrer Cloud Shell

Bien que Google Cloud puisse être utilisé à distance depuis votre ordinateur portable, nous allons nous servir de Google Cloud Shell pour cet atelier de programmation, un environnement de ligne de commande exécuté dans le cloud.

  1. Accédez à la console Google Cloud, puis sélectionnez ou créez un projet Google Cloud.
  2. ⚠️ Notez l'ID du projet. Vous l'utiliserez tout au long de cet atelier.

39b6a5563d69ccfb.png

  1. Ouvrez Cloud Shell dans un nouvel onglet : https://shell.cloud.google.com/.
  2. Si vous y êtes invité, cliquez sur "Autoriser".
  3. Remplacez PROJECT_ID et collez la commande suivante dans le 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

Appliquez les variables à votre session active :

source ./env.sh

Activer les API et créer un ensemble de données BigQuery

Activez les services Google Cloud nécessaires dans votre projet et créez l'ensemble de données cible :

# 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. Explorer les distributions de données avec une UDF Python publique

Avant de déployer du code personnalisé, il est utile d'explorer l'ensemble de données et de filtrer le bruit de mauvaise qualité. Dans cette étape, vous allez analyser les questions StackOverflow pour trouver des utilisateurs actifs et comprendre la distribution statistique des scores de leurs questions.

Pourquoi utiliser une UDF Python pour cela ?

Le calcul de plusieurs centiles exacts (comme les 25e, 50e, 75e et 95e centiles) sur des tableaux de données groupés est complexe et gourmand en ressources en SQL pur. Les fonctions analytiques SQL standards, comme PERCENTILE_CONT, attendent des colonnes de lignes plates plutôt que des tableaux imbriqués. Pour calculer les centiles exacts des tableaux préagrégés par ligne, vous devriez écrire des sous-requêtes détaillées qui annulent l'imbrication, trient et réagrègent pour chaque métrique de centile, ce qui est inefficace.

En utilisant NumPy, la bibliothèque scientifique hautement optimisée de Python dans une UDF, vous pouvez calculer des centiles mathématiques exacts sur un tableau de nombres avec une seule ligne de code.

Exécution

Google Cloud héberge plusieurs UDF publiques précompilées (cliquez sur l'onglet Routines). Comme BigQuery exige une correspondance explicite des types, nous allons utiliser une expression de table commune (CTE) pour préagréger les données et caster les tableaux d'entiers en tableaux à virgule flottante à l'aide d'une expression UNNEST.

Exécutez la requête suivante dans la 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;

Vous pouvez ainsi comprendre instantanément les performances des utilisateurs sans avoir à configurer d'autorisations ni à écrire de code Python personnalisé au préalable.

Vérifier les résultats

Étant donné que cette requête renvoie des types de tableaux imbriqués (scores et score_percentiles), l'onglet Résultats tabulaire par défaut de BigQuery Studio peut afficher une sortie aplatie ou tronquée, ce qui rend difficile l'inspection des éléments du tableau.

Pour afficher la sortie structurée et imbriquée :

  1. Dans le volet des résultats de la requête, recherchez la barre d'onglets (qui est définie par défaut sur Résultats).
  2. Cliquez sur l'onglet JSON.

Vous devriez voir un tableau JSON structuré représentant les lignes, semblable à ce qui suit :

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

Comprendre le résultat

  • scores : tableau complet des scores bruts des questions publiés par chaque utilisateur unique.
  • score_percentiles : tableau contenant quatre valeurs à virgule flottante calculées. Ils correspondent exactement aux centiles demandés : [25th, 50th, 75th, and 95th] centiles. Par exemple, pour l'utilisateur 533463, le score au 95e centile de ses questions est d'environ 8.85, ce qui indique que ses principales questions obtiennent un score élevé.

4. Nettoyer le texte de manière native en créant une UDF personnalisée

Une fois les utilisateurs cibles identifiés, nous souhaitons analyser le contenu de leurs posts. Toutefois, les posts de forum bruts contiennent souvent des balises et des entités HTML malpropres. Nous devons les supprimer pour améliorer la lisibilité et réduire les coûts des modèles en aval.

Pour comprendre pourquoi cela est nécessaire, examinons d'abord à quoi ressemble le corps brut et non formaté du post Stack Overflow. Exécutez la requête suivante dans la 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;

Si vous examinez le résultat, vous verrez un mélange de balises de mise en forme telles que <p>, <b>, <code> et d'autres intégrées au texte. Le traitement direct de ces éléments à l'aide de tokenizers de machine learning en aval introduirait du bruit inutile et augmenterait artificiellement les coûts d'ingestion de jetons.

Pourquoi utiliser une UDF Python pour cela ?

L'analyse fiable du code HTML à l'aide d'expressions régulières (Regex) en SQL pur est fragile et sujette à des erreurs d'analyse. L'exécution d'une bibliothèque Python robuste telle que beautifulsoup4 directement dans vos requêtes offre un moyen fiable de supprimer les tags.

Exécutez la requête LDD suivante pour déployer la fonction persistante clean_html dans votre ensemble de données :

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

Vérifiez le résultat de la fonction avec une requête simple :

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

Le texte dépouillé devrait s'afficher sans les éléments HTML :

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

5. Sécuriser les intégrations externes et le traitement ML avancé

Maintenant que nous avons un texte propre, nous devons le préparer pour les modèles de machine learning ou les grands modèles de langage (LLM) comme Gemma. Les LLM ne peuvent pas lire directement du texte brut. Ils traitent des ID de jetons numériques.

Pour convertir notre texte propre en jetons, nous allons importer la bibliothèque transformers de Hugging Face et charger un tokenizer Google T5 pré-entraîné directement dans notre base de données.

Créer la connexion de ressource Cloud

Exécutez la requête suivante dans la console BigQuery Studio pour établir une connexion sécurisée :

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

Créer l'UDF du tokenizer

Déployez maintenant l'UDF du tokenizer personnalisé. Notez que la fonction d'assistance get_tokenizer() vérifie si la variable globale tokenizer est déjà initialisée avant de tenter un téléchargement :

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 []
''';

Testez le tokenizer avec une requête simple pour vérifier qu'il télécharge correctement le composant et renvoie un tableau d'ID entiers :

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

Passez à l'onglet "JSON" du panneau des résultats de requête pour afficher le tableau structuré :

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

6. Exécuter le pipeline de prétraitement de bout en bout

Maintenant que les trois étapes de notre pipeline sont prêtes, nous pouvons les enchaîner dans une seule requête SQL à l'aide d'expressions de table courantes (CTE).

Ce pipeline représente un workflow d'ingénierie des données moderne :

  1. Isolez les utilisateurs actifs et leurs questions les plus populaires à l'aide de la fonction définie par l'utilisateur percentile publique.
  2. Supprimez la mise en forme HTML brute du texte en local à l'aide de notre UDF clean_html.
  3. Convertissez le texte nettoyé en tableaux de jetons à l'aide de notre UDF de tokenisation mise en cache.

Exécutez la requête de pipeline suivante dans la 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;

Passez à l'onglet "JSON" dans BigQuery Studio pour examiner la sortie structurée.

[{
  "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. Annexe : Fonctionnement du pipeline et audit des coûts d'exécution

Cette section présente en détail les mécanismes spécifiques de la requête de prétraitement de bout en bout et explique comment surveiller la consommation exacte de slots et les coûts des conteneurs gérés de votre exécution.

Répartition de l'architecture du 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
)

Ce premier segment de requête collecte les scores bruts des questions pour les contributeurs actifs de Stack Overflow. Il consolide les scores de chaque utilisateur dans un seul tableau (ARRAY_AGG) tout en appliquant un ordre de tri déterministe (ORDER BY id). L'ensemble de données est filtré pour n'inclure que les utilisateurs ayant répondu à au moins cinq questions afin d'établir une base statistique valide.

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

Pour identifier les principaux contributeurs, ce segment utilise la fonction Python définie par l'utilisateur percentiles publique afin de trouver les distributions exactes des scores (25e, 50e, 75e et 95e centiles). Pour éviter d'exécuter plusieurs fois cette fonction définie par l'utilisateur gourmande en ressources de calcul, le calcul est encapsulé dans une sous-requête imbriquée. Le benchmark du 95e centile est ensuite récupéré directement à partir du tableau obtenu à la position d'index 3 (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)
)

Les questions d'origine sont associées à la liste des utilisateurs actifs pour récupérer les posts qui ont atteint ou dépassé le seuil du 95e centile. Pour éviter les erreurs de comparaison de type de base de données, le score de référence est converti explicitement en type FLOAT64 via une opération CAST avant l'évaluation.

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
)

Les corps bruts des posts contiennent souvent un balisage et un code HTML bruts qui dégradent les entrées de machine learning en aval. Au lieu d'utiliser des expressions régulières complexes, le pipeline appelle notre UDF Python clean_html personnalisée. Il lance dynamiquement un environnement d'exécution Python dans un conteneur isolé, en utilisant la bibliothèque BeautifulSoup pour supprimer proprement les éléments et générer du texte brut et lisible.

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
)

Pour préparer l'aperçu du texte propre à l'ingestion du modèle génératif, le pipeline appelle notre UDF Python tokenize personnalisée sur une tranche de 120 caractères. L'UDF contacte de manière sécurisée le Hugging Face Hub pour télécharger les paramètres du tokenizer Google T5. Étant donné que l'instance du tokenizer est chargée dans une variable globale, le conteneur préchauffé met en cache la configuration, ce qui permet aux lignes suivantes de subir une tokenisation rapide en mémoire sans latence réseau.

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;

Le bloc de requête final génère l'ensemble de données traité. Plutôt que d'exécuter la fonction UDF de tokenisation une deuxième fois pour compter les jetons générés, la fonction ARRAY_LENGTH native de BigQuery est appliquée directement au tableau token_ids précalculé. Cette stratégie réduit les cycles de processeur redondants, les opérations de conteneur et les coûts d'exécution globaux.

Auditer la consommation de slots et les coûts des UDF gérées

BigQuery déploie des tableaux de bord complets de visibilité des coûts directement dans l'UI de la console Google Cloud. Les ingénieurs peuvent auditer de manière programmatique la consommation exacte d'emplacements et les coûts d'exécution des conteneurs gérés de n'importe quelle requête à l'aide des ID de job BigQuery.

Pour auditer l'exécution de votre requête, recherchez votre ID de job.

  1. Dans BigQuery Studio, vous pouvez y accéder en accédant à l'onglet Historique des requêtes en bas de la console.
  2. Cliquez sur la requête de pipeline exécutée.
  3. Dans le panneau d'informations Informations sur la tâche, recherchez le champ ID de la tâche.

Une fois que vous avez identifié votre Job ID pur, remplacez JOB_ID dans la requête ci-dessous et exécutez-la dans 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";

Passez à l'onglet JSON dans BigQuery Studio pour examiner la sortie structurée. Vous devriez recevoir une charge utile semblable à la suivante :

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

Comprendre le résultat :

  • total_slot_ms : temps de calcul total en millisecondes utilisé dans toutes les étapes de la requête. Pour ce pipeline unifié, l'exécution prend en moyenne 815 000 millisecondes de créneau.
  • external_service_costs : tableau qui détaille les ressources utilisées en dehors du moteur d'analyse BigQuery standard.
  • external_service : la valeur "MANAGED_ROUTINE_EXECUTION" confirme que le coût appartient spécifiquement à l'hébergement de l'exécution de conteneurs sans serveur qui héberge notre environnement d'UDF Python personnalisé.
  • slot_ms : la valeur "3000" représente le nombre exact de millisecondes de ressources de calcul spécialisées consommées dans le conteneur d'exécution à chaud pour exécuter la logique Python.
  • billing_method : la valeur "SERVICES_SKU" indique que ces frais de conteneur localisés sont facturés de manière dynamique via le SKU spécialisé BigQuery Services en fonction de la durée d'exécution du conteneur et de la surcharge mémoire. Au tarif standard de calcul multirégional aux États-Unis de 0,06 $ par emplacement et par heure (consultez la page des tarifs des services BigQuery), le coût d'exécution pur de 3 000 emplacements-ms est calculé comme suit : (3 000 ms / 3 600 000 ms) * 0,06 $ = 0,00005 $, ce qui démontre l'efficacité du workflow en termes de coûts.

8. Nettoyer les ressources cloud

Pour éviter que des frais ne vous soient facturés en continu ou que vous ne consommiez les quotas de votre projet, supprimez votre ensemble de données et vos connexions BigQuery dans 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. Félicitations !

Vous avez terminé l'atelier de programmation sur la création et la sécurisation d'UDF Python dans l'environnement d'exécution sans serveur BigQuery.

Dans cet atelier de programmation, vous avez appris à :

  • Explorer les données avec des UDF publiques : appelez des UDF Python publiques précompilées sur les ensembles de données Stack Overflow pour effectuer des opérations mathématiques de centiles sur des tableaux agrégés.
  • Intégrer des packages tiers : déployez une UDF persistante personnalisée utilisant l'environnement d'exécution Python standard et la bibliothèque beautifulsoup4 pour supprimer les tags HTML bruts de vos requêtes SQL.
  • Configurer des connexions externes sécurisées : créez une connexion de ressource cloud BigQuery pour accorder de manière sécurisée aux conteneurs UDF isolés un accès réseau sortant afin de récupérer des composants externes sans coder en dur les identifiants.
  • Implémenter la tokenisation locale avec la mise en cache en mémoire : importez la bibliothèque Hugging Face transformers pour charger un tokenizer T5, en utilisant des variables globales pour mettre en cache les fichiers de configuration et traiter les lignes dans des conteneurs actifs.
  • Auditez les performances et les coûts d'exécution : interrogez par programmation les vues INFORMATION_SCHEMA.JOBS régionales à l'aide des ID de job BigQuery pour suivre la consommation de slots (total_slot_ms) et les coûts d'utilisation des conteneurs (external_service_costs).

Et ensuite ?