Python-Funktionen in BigQuery mit verwalteten UDFs nutzen

1. Einführung

Die Structured Query Language (SQL) ist der Branchenstandard für die Data-Warehouse-Analyse. Es kann jedoch sehr schwierig sein, komplexe prozedurale Logik, mathematische Berechnungen, Textbereinigungs- oder Machine-Learning-Vorbereitungsworkflows in reinem SQL auszudrücken.

In der Vergangenheit haben Datenteams riesige Datasets aus BigQuery extrahiert, wenn eine komplexe benutzerdefinierte Python-Verarbeitung erforderlich war. Sie haben sie auf externen benutzerdefinierten virtuellen Maschinen oder Clustern verarbeitet und die Ergebnisse wieder geladen. Dieser Ansatz führt zu einer hohen Netzwerklatenz, erhöht die Compliance-Risiken durch das Verschieben von Daten und verursacht einen hohen Aufwand für die Infrastrukturverwaltung.

Verwaltete benutzerdefinierte Python-Funktionen (User-Defined Functions, UDFs) von BigQuery lösen diese Probleme, indem sie benutzerdefinierten Code auf serverlosen Ressourcen ausführen, die automatisch auf Millionen von Zeilen skaliert werden. Google Cloud verwaltet Kompilierung, Image-Erstellung, Sicherheitspatches und Ausführung, sodass Sie benutzerdefinierte Berechnungen direkt dort ausführen können, wo sich Ihre Daten befinden.

In diesem Codelab erstellen Sie eine Analyse- und Textvorverarbeitungs-Pipeline für Stack Overflow-Communitydaten, um sie für nachgelagerte Berichte und Machine Learning vorzubereiten.

Voraussetzungen

  • Google Cloud-Projekt mit aktivierter Abrechnungsfunktion.
  • Grundkenntnisse in SQL, IAM und BigQuery.

Lerninhalte

  • Eine vorkompilierte öffentliche Python-UDF für ein öffentliches Dataset aufrufen, um Datenverteilungen zu analysieren.
  • Eigene benutzerdefinierte Python-UDF mit beautifulsoup4 bereitstellen, um unstrukturierte Daten zu bereinigen.
  • Eine BigQuery-Cloud-Ressourcenverbindung konfigurieren, um Machine-Learning-Assets sicher herunterzuladen und lokale ML-Tokenisierung mit der Hugging Face-Bibliothek „transformers“ mithilfe des In-Memory-Container-Caching durchzuführen.
  • Diese Schritte zu einer einzigen leistungsstarken SQL-Pipeline verketten.

2. Einrichtung und Anforderungen

Cloud Shell starten

Sie können Google Cloud von Ihrem Laptop aus per Fernzugriff nutzen. In diesem Codelab verwenden Sie jedoch Google Cloud Shell, eine Befehlszeilenumgebung, die in der Cloud ausgeführt wird.

  1. Rufen Sie die Google Cloud Console auf und wählen Sie ein Google Cloud-Projekt aus oder erstellen Sie eines.
  2. ⚠️ Notieren Sie sich die Projekt-ID. Sie verwenden sie in diesem Lab.

39b6a5563d69ccfb.png

  1. Öffnen Sie Cloud Shell in einem neuen Tab: https://shell.cloud.google.com/.
  2. Wenn Sie dazu aufgefordert werden, klicken Sie auf **Autorisieren**.
  3. Ersetzen Sie PROJECT_ID und fügen Sie den folgenden Befehl in das Terminal ein:
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

Wenden Sie die Variablen auf Ihre aktive Sitzung an:

source ./env.sh

APIs aktivieren und BigQuery-Dataset erstellen

Aktivieren Sie die erforderlichen Google Cloud-Dienste in Ihrem Projekt und erstellen Sie das Ziel-Dataset:

# 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. Datenverteilungen mit einer öffentlichen Python-UDF untersuchen

Bevor Sie benutzerdefinierten Code bereitstellen, ist es hilfreich, das Dataset zu untersuchen und Rauschen von geringer Qualität herauszufiltern. In diesem Schritt analysieren Sie Stack Overflow-Fragen, um aktive Nutzer zu finden und die statistische Verteilung ihrer Fragenbewertungen zu verstehen.

Warum eine Python-UDF verwenden?

Die Berechnung mehrerer genauer Perzentile (z. B. des 25., 50., 75. und 95. Perzentils) für gruppierte Datenarrays ist in reinem SQL komplex und ressourcenintensiv. Standard-SQL-Analysefunktionen wie PERCENTILE_CONT erwarten flache Spalten mit Zeilen anstelle von verschachtelten Arrays. Um genaue Perzentile von voraggregierten Arrays pro Zeile zu berechnen, müssten Sie ausführliche Unterabfragen schreiben, die für jede Perzentilmetrik entnesten, sortieren und neu aggregieren, was ineffizient ist.

Mit NumPy, der hochoptimierten wissenschaftlichen Bibliothek von Python in einer UDF, können Sie genaue mathematische Perzentile für ein Array von Zahlen mit einer einzigen Codezeile berechnen.

Ausführung

Google Cloud hostet mehrere vorkompilierte öffentliche UDFs (klicken Sie auf den Tab Routinen). Da BigQuery eine explizite Typübereinstimmung erfordert, verwenden wir einen Common Table Expression (CTE), um die Daten vorab zu aggregieren und die Ganzzahl-Arrays mit einem UNNEST-Ausdruck in Gleitkomma-Arrays umzuwandeln.

Führen Sie die folgende Abfrage in der BigQuery Studio-Konsole aus:

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;

So können Sie die Nutzerleistung sofort nachvollziehen, ohne Berechtigungen konfigurieren oder zuerst benutzerdefinierten Python-Code schreiben zu müssen.

Ergebnisse überprüfen

Da diese Abfrage verschachtelte Array-Typen (scores und score_percentiles) zurückgibt, kann auf dem standardmäßigen tabellarischen Tab Ergebnisse in BigQuery Studio eine vereinfachte oder abgeschnittene Ausgabe angezeigt werden, was die Überprüfung der Array-Elemente erschwert.

So rufen Sie die strukturierte, verschachtelte Ausgabe auf:

  1. Suchen Sie im Bereich mit den Abfrageergebnissen die Tab-Leiste (standardmäßig Ergebnisse).
  2. Klicken Sie auf den Tab JSON.

Sie sollten ein strukturiertes JSON-Array sehen, das die Zeilen darstellt, ähnlich dem folgenden:

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

Ausgabe verstehen

  • scores: Das vollständige Array der Rohbewertungen für Fragen, die von den einzelnen Nutzern gepostet wurden.
  • score_percentiles: Ein Array mit vier berechneten Gleitkommawerten. Diese entsprechen genau den angeforderten Perzentilen: [25th, 50th, 75th, and 95th] Perzentil. Für Nutzer 533463 beträgt die 95. Perzentilbewertung für seine Fragen beispielsweise etwa 8.85, was darauf hindeutet, dass seine besten Fragen eine hohe Bewertung haben.

4. Text nativ bereinigen, indem Sie eine benutzerdefinierte UDF erstellen

Sobald die Zielnutzer identifiziert sind, möchten wir ihre Post-Inhalte analysieren. Roh-Forumsposts enthalten jedoch oft unübersichtliche HTML-Tags und -Entitäten. Wir müssen diese entfernen, um die Lesbarkeit zu verbessern und die Kosten für nachgelagerte Modelle zu senken.

Um zu verstehen, warum das erforderlich ist, sehen wir uns zuerst an, wie der Roh-Post-Text von Stack Overflow tatsächlich aussieht. Führen Sie die folgende Abfrage in der BigQuery Studio-Konsole aus:

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;

Wenn Sie die Ausgabe untersuchen, sehen Sie eine Mischung aus Formatierungstags wie <p>, <b>, <code> und anderen, die in den Text eingebettet sind. Wenn Sie diese direkt mit nachgelagerten Machine-Learning-Tokenizern verarbeiten, wird unnötiges Rauschen eingeführt und die Kosten für die Tokenaufnahme werden künstlich in die Höhe getrieben.

Warum eine Python-UDF verwenden?

Das zuverlässige Parsen von HTML mit regulären Ausdrücken (Regex) in reinem SQL ist fehleranfällig und kann zu Parsingfehlern führen. Wenn Sie eine robuste Python-Bibliothek wie beautifulsoup4 direkt in Ihren Abfragen ausführen, können Sie Tags zuverlässig entfernen.

Führen Sie die folgende DDL-Abfrage aus, um die persistente Funktion clean_html in Ihrem Dataset bereitzustellen:

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

Überprüfen Sie die Ausgabe der Funktion mit einer einfachen Abfrage:

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

Sie sollten den bereinigten Text ohne HTML-Elemente sehen:

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

5. Externe Integrationen und erweiterte ML-Verarbeitung sichern

Nachdem wir den Text bereinigt haben, müssen wir ihn für Machine-Learning-Modelle oder Large Language Models (LLMs) wie Gemma vorbereiten. LLMs können Rohtext nicht direkt lesen, sondern verarbeiten numerische Token-IDs.

Um unseren bereinigten Text in Tokens umzuwandeln, importieren wir die transformers-Bibliothek von Hugging Face und laden einen vortrainierten Google T5-Tokenizer direkt in unsere Datenbank.

Cloud-Ressourcenverbindung erstellen

Führen Sie die folgende Abfrage in der BigQuery Studio-Konsole aus, um eine sichere Verbindung herzustellen:

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

Tokenizer-UDF erstellen

Stellen Sie nun die benutzerdefinierte Tokenizer-UDF bereit. Beachten Sie, dass die get_tokenizer() Hilfsfunktion prüft, ob die globale Variable tokenizer bereits initialisiert wurde, bevor ein Download versucht wird:

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

Testen Sie den Tokenizer mit einer einfachen Abfrage, um zu prüfen, ob das Asset erfolgreich heruntergeladen wurde und ein Array von Ganzzahl-IDs zurückgegeben wird:

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

Wechseln Sie im Bereich mit den Abfrageergebnissen zum Tab „JSON“, um das strukturierte Array zu sehen:

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

6. End-to-End-Vorverarbeitungs-Pipeline ausführen

Nachdem alle drei Schritte unserer Pipeline abgeschlossen sind, können wir sie mit Common Table Expressions (CTEs) zu einer einzigen SQL-Abfrage verketten.

Diese Pipeline stellt einen modernen Data-Engineering-Workflow dar:

  1. Aktive Nutzer und ihre am besten bewerteten Fragen mit der öffentlichen Perzentil-UDF isolieren.
  2. Roh-HTML-Formatierung mit unserer clean_html-UDF lokal aus dem Text entfernen.
  3. Den bereinigten Text mit unserer im Cache gespeicherten tokenize-UDF in Token-Arrays umwandeln.

Führen Sie die folgende Pipeline-Abfrage in der BigQuery Studio-Konsole aus:

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;

Wechseln Sie in BigQuery Studio zum Tab „JSON“, um die strukturierte Ausgabe zu sehen.

[{
  "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. Anhang: Funktionsweise der Pipeline und Überprüfung der Ausführungskosten

In diesem Abschnitt wird die spezifische Funktionsweise der End-to-End-Vorverarbeitungsabfrage ausführlich erläutert und gezeigt, wie Sie den genauen Slotverbrauch und die Kosten für verwaltete Container Ihrer Ausführung überwachen können.

Architektonische Aufschlüsselung der 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
)

Dieses erste Abfragesegment erfasst die Rohbewertungen für Fragen von aktiven Stack Overflow-Beitragenden. Die Bewertungen der einzelnen Nutzer werden in einem einzigen Array zusammengefasst (ARRAY_AGG), während eine deterministische Sortierreihenfolge erzwungen wird (ORDER BY id). Das Dataset wird so gefiltert, dass nur Nutzer mit mindestens fünf Fragen berücksichtigt werden, um eine gültige statistische Baseline zu erstellen.

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

Um die besten Beitragenden zu ermitteln, werden in diesem Segment mit der öffentlichen Python-UDF percentiles genaue Bewertungsverteilungen (25., 50., 75. und 95. Perzentil) ermittelt. Um diese rechenintensive UDF nicht mehrmals ausführen zu müssen, wird die Berechnung in eine verschachtelte Unterabfrage eingeschlossen. Die 95. Perzentil-Benchmark wird dann direkt aus dem resultierenden Array an der Indexposition drei (OFFSET(3)) abgerufen.

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

Die ursprünglichen Fragen werden mit der Liste der aktiven Nutzer verknüpft, um Posts abzurufen, die den Schwellenwert des 95. Perzentils erreicht oder überschritten haben. Um Fehler beim Vergleich von Datenbanktypen zu vermeiden, wird die Benchmark-Bewertung vor der Auswertung explizit mit einem CAST-Vorgang in den Typ FLOAT64 konvertiert.

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
)

Roh-Post-Texte enthalten häufig unübersichtliche Auszeichnungen und HTML-Boilerplate, die die nachgelagerten Machine-Learning-Eingaben beeinträchtigen. Anstelle von komplexen regulären Ausdrücken ruft die Pipeline unsere benutzerdefinierte Python-UDF clean_html auf. Sie startet dynamisch eine Python-Laufzeitumgebung in einem isolierten Container und verwendet die BeautifulSoup-Bibliothek, um Elemente sauber zu entfernen und einfachen, lesbaren Text auszugeben.

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
)

Um die Vorschau des bereinigten Texts für die Aufnahme in das generative Modell vorzubereiten, ruft die Pipeline unsere benutzerdefinierte Python-UDF tokenize für einen 120-Zeichen-Ausschnitt auf. Die UDF stellt eine sichere Verbindung zum Hugging Face Hub her, um die Google T5-Tokenizer-Parameter herunterzuladen. Da die Tokenizer-Instanz in eine globale Variable geladen wird, speichert der warme Container die Konfiguration im Cache, sodass nachfolgende Zeilen schnell im Arbeitsspeicher tokenisiert werden können, ohne dass es zu einer Netzwerklatenz kommt.

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;

Der letzte Abfrageblock gibt das verarbeitete Dataset aus. Anstatt die Tokenisierungs-UDF ein zweites Mal auszuführen, um die generierten Tokens zu zählen, wird die native ARRAY_LENGTH-Funktion von BigQuery direkt auf das vorab berechnete token_ids-Array angewendet. Diese Strategie reduziert redundante CPU-Zyklen, Container-Vorgänge und die Gesamtkosten für die Ausführung.

Slotverbrauch und Kosten für verwaltete UDFs überprüfen

Während BigQuery umfassende Dashboards zur Kostentransparenz direkt in der Google Cloud Console-UI einführt, können Entwickler den genauen Slotverbrauch und die Kosten für die Ausführung verwalteter Container für jede Abfrage programmatisch mit BigQuery-Job-IDs überprüfen.

Suchen Sie Ihre Job-ID, um die Ausführung Ihrer Abfrage zu überprüfen.

  1. In BigQuery Studio finden Sie sie unten in der Konsole auf dem Tab Abfrageverlauf.
  2. Klicken Sie auf die ausgeführte Pipeline-Abfrage.
  3. Suchen Sie im Detailbereich Jobinformationen das Feld Job-ID.

Sobald Sie Ihre reine Job-ID ermittelt haben, ersetzen Sie JOB_ID in der folgenden Abfrage und führen Sie sie in BigQuery Studio aus:

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

Wechseln Sie in BigQuery Studio zum Tab JSON, um die strukturierte Ausgabe zu sehen. Sie sollten eine Nutzlast ähnlich der folgenden erhalten:

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

Ausgabe verstehen:

  • total_slot_ms: Die gesamte Berechnungszeit in Millisekunden, die in allen Abfragephasen verwendet wurde. Bei dieser einheitlichen Pipeline beträgt die Ausführung in der Regel etwa 815.000 Slot-Millisekunden.
  • external_service_costs: Ein Array, das die Ressourcen aufschlüsselt, die außerhalb der Standard-BigQuery-Analyse-Engine verwendet werden.
  • external_service: Der Wert "MANAGED_ROUTINE_EXECUTION" bestätigt, dass die Kosten speziell für die serverlose Containerausführung gelten, die unsere benutzerdefinierte Python-UDF-Umgebung hostet.
  • slot_ms: Der Wert "3000" gibt die genauen Millisekunden der spezialisierten Compute-Ressourcen an, die in der Laufzeitumgebung des warmen Containers für die Ausführung der Python-Logik verbraucht wurden.
  • billing_method: Der Wert "SERVICES_SKU" gibt an, dass diese lokalisierten Containergebühren dynamisch über die spezielle BigQuery Services-SKU basierend auf der Ausführungsdauer des Containers und dem Arbeitsspeicher-Overhead abgerechnet werden. Bei den Standardpreisen für Compute-Ressourcen in der US-Multiregion von 0,06 $ pro Slotstunde (siehe Preisseite für BigQuery Services) werden die reinen Ausführungskosten von 3.000 Slot-Millisekunden wie folgt berechnet: (3.000 ms / 3.600.000 ms) * 0,06 $ = 0,00005 $. Dies zeigt einen kosteneffizienten Workflow.

8. Cloud-Ressourcen bereinigen

Löschen Sie Ihr BigQuery-Dataset und Ihre Verbindungen in Cloud Shell, um fortlaufende Gebühren zu vermeiden oder Projektkontingente nicht zu überschreiten:

# 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. Glückwunsch!

Sie haben das Codelab zum Erstellen und Sichern von Python-UDFs in der serverlosen BigQuery-Laufzeitumgebung abgeschlossen.

In diesem Codelab haben Sie Folgendes gelernt:

  • Daten mit öffentlichen UDFs untersuchen:Rufen Sie vorkompilierte öffentliche Python-UDFs für Stack Overflow-Datasets auf, um mathematische Perzentilvorgänge für aggregierte Arrays auszuführen.
  • Drittanbieterpakete einbinden:Stellen Sie eine benutzerdefinierte persistente UDF bereit, die die Standard-Python-Laufzeitumgebung und die beautifulsoup4-Bibliothek verwendet, um Roh-HTML-Tags nativ in Ihren SQL-Abfragen zu entfernen.
  • Sichere externe Verbindungen konfigurieren:Erstellen Sie eine BigQuery-Cloud-Ressourcenverbindung, um isolierten UDF-Containern sicher ausgehenden Netzwerkzugriff zu gewähren, damit sie externe Assets abrufen können, ohne Anmeldedaten fest zu codieren.
  • Lokale Tokenisierung mit In-Memory-Caching implementieren:Importieren Sie die transformers-Bibliothek von Hugging Face, um einen T5-Tokenizer zu laden und globale Variablen zu verwenden, um Konfigurationsdateien im Cache zu speichern und Zeilen in warmen Containern zu verarbeiten.
  • Ausführungsleistung und -kosten überprüfen:Fragen Sie regionale INFORMATION_SCHEMA.JOBS-Ansichten programmatisch mit BigQuery-Job-IDs ab, um den Slotverbrauch (total_slot_ms) und die Kosten für die Containernutzung (external_service_costs) zu verfolgen.

Nächste Schritte