הוספת העוצמה של Python ל-BigQuery באמצעות פונקציות מוגדרות על ידי המשתמש (UDF) מנוהלות

1. מבוא

שפת שאילתות מובנית (SQL) היא התקן המקובל בתחום לניתוח מחסני נתונים. עם זאת, יכול להיות מאוד מאתגר לבטא ב-SQL טהור לוגיקה פרוצדורלית מורכבת, חישובים מתמטיים, ניקוי טקסט או תהליכי עבודה של הכנה ללמידת מכונה.

בעבר, צוותי נתונים נהגו לחלץ מערכי נתונים גדולים מ-BigQuery כשנדרש עיבוד מותאם אישית מורכב ב-Python, לעבד אותם במכונות וירטואליות או באשכולות מותאמים אישית חיצוניים, ולטעון את התוצאות בחזרה. הגישה הזו גורמת לחביון גבוה ברשת, מגדילה את הסיכונים לתאימות בגלל העברת נתונים ויוצרת תקורה של ניהול התשתית.

פונקציות מנוהלות מוגדרות על ידי המשתמש (UDF) ב-Python ב-BigQuery פותרות את הבעיות האלה על ידי הפעלת קוד מותאם אישית במשאבים ללא שרת, שמתרחבים אוטומטית למיליוני שורות. ‫Google Cloud מנהל את ההידור, בניית התמונות, תיקוני האבטחה וההפעלה, ומאפשר לכם להריץ חישובים מותאמים אישית ישירות במקום שבו הנתונים שלכם נמצאים.

בשיעור Codelab הזה תבנו פייפליין לניתוח ולעיבוד מקדים של נתונים מקהילת StackOverflow, ותכינו אותם לדיווח וללמידת מכונה.

דרישות מוקדמות

  • פרויקט ב-Google Cloud שהחיוב בו מופעל.
  • הבנה בסיסית של מושגי SQL,‏ IAM ו-BigQuery.

מה תלמדו

  • איך קוראים לפונקציית UDF ציבורית של Python שעברה קומפילציה מראש במערך נתונים ציבורי כדי לנתח התפלגויות של נתונים.
  • איך פורסים פונקציה מוגדרת על ידי המשתמש (UDF) ב-Python בהתאמה אישית באמצעות beautifulsoup4 כדי לנקות נתונים לא מובנים.
  • איך מגדירים קישור למשאבים ב-Cloud של BigQuery כדי להוריד בצורה מאובטחת נכסי למידת מכונה ולבצע המרה לטוקנים מקומית של למידת מכונה באמצעות ספריית הטרנספורמציה של Hugging Face, תוך שימוש בשמירה במטמון של קונטיינרים בזיכרון.
  • איך לשרשר את השלבים האלה לצינור SQL יחיד עם ביצועים גבוהים.

2. הגדרה ודרישות

מפעילים את Cloud Shell

אפשר להפעיל את Google Cloud מרחוק מהמחשב הנייד, אבל ב-Codelab הזה נשתמש ב-Google Cloud Shell, סביבת שורת פקודה שפועלת בענן.

  1. עוברים אל Google Cloud Console ובוחרים או יוצרים פרויקט ב-Google Cloud.
  2. ‫⚠️ שימו לב למזהה הפרויקט. תשתמשו בו לאורך כל שיעור ה-Lab הזה.

39b6a5563d69ccfb.png

  1. פותחים את Cloud Shell בכרטיסייה חדשה: https://shell.cloud.google.com/.
  2. אם מתבקשים, לוחצים על 'הרשאה'.
  3. מחליפים את PROJECT_ID ומדביקים את הפקודה הבאה בטרמינל:
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

מחילים את המשתנים על הסשן הפעיל:

source ./env.sh

הפעלת ממשקי API ויצירת מערך נתונים ב-BigQuery

מפעילים את שירותי Google Cloud הנדרשים בפרויקט ויוצרים את מערך הנתונים המיועד:

# 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. היכרות עם התפלגויות נתונים באמצעות פונקציית UDF ציבורית ב-Python

לפני שפורסים קוד בהתאמה אישית, כדאי לעיין בקבוצת הנתונים ולסנן רעשי רקע באיכות נמוכה. בשלב הזה, תנתחו שאלות ב-StackOverflow כדי למצוא משתמשים פעילים ולהבין את ההתפלגות הסטטיסטית של ציוני השאלות שלהם.

למה כדאי להשתמש בפונקציית UDF ב-Python?

חישוב של כמה אחוזונים מדויקים (כמו אחוזון 25, 50, 75 ו-95) במערכים מקובצים של נתונים הוא מורכב ודורש הרבה משאבים ב-SQL טהור. פונקציות אנליטיות של SQL סטנדרטי, כמו PERCENTILE_CONT, מצפות לעמודות שטוחות של שורות ולא למערכים מקוננים. כדי לחשב אחוזונים מדויקים של מערכים שעברו צבירה מראש לכל שורה, צריך לכתוב שאילתות משנה מפורטות שמבטלות את הקינון, ממיינות ומבצעות צבירה מחדש לכל מדד אחוזון, וזה לא יעיל.

באמצעות NumPy, ספריית Python מותאמת במיוחד למדע בתוך UDF, אפשר לחשב אחוזונים מתמטיים מדויקים במערך של מספרים בשורה אחת של קוד.

הרצה

ב-Google Cloud יש כמה פונקציות UDF ציבוריות שעברו קומפילציה מראש (לוחצים על הכרטיסייה Routines). מכיוון ש-BigQuery דורש התאמה מפורשת של סוגים, נשתמש בביטוי טבלה נפוץ (CTE) כדי לבצע צבירה מראש של הנתונים ולהמיר את מערכי המספרים השלמים למערכי נקודה צפה באמצעות ביטוי UNNEST.

מריצים את השאילתה הבאה במסוף 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;

כך תוכלו להבין את ביצועי המשתמשים באופן מיידי בלי שתצטרכו להגדיר הרשאות או לכתוב קוד Python בהתאמה אישית.

אימות התוצאות

מכיוון שהשאילתה הזו מחזירה סוגי מערכים מקוננים (scores ו-score_percentiles), יכול להיות שבכרטיסייה Results הטבלאית שמוגדרת כברירת מחדל ב-BigQuery Studio יוצגו פלט שטוח או קטוע, ולכן יהיה קשה לבדוק את רכיבי המערך.

כדי לראות את הפלט המובנה והמוטמע:

  1. בחלונית של תוצאות השאילתה, מאתרים את סרגל הכרטיסיות (שמוגדר כברירת מחדל לתוצאות).
  2. לוחצים על הכרטיסייה JSON.

אמור להופיע מערך JSON מובנה שמייצג את השורות, בדומה לזה:

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

הסבר על הפלט

  • ‫scores: מערך מלא של ציוני השאלות הגולמיים שפורסמו על ידי כל משתמש ייחודי.
  • ‫score_percentiles: מערך שמכיל ארבעה ערכים מחושבים של נקודה צפה. הם תואמים בדיוק לאחוזונים המבוקשים: [25th, 50th, 75th, and 95th] אחוזונים. לדוגמה, הציון של המשתמש 533463 באחוזון ה-95 של השאלות שלו הוא בערך 8.85, מה שמצביע על כך שהשאלות המובילות שלו קיבלו ציון גבוה.

4. ניקוי טקסט באופן מקורי על ידי יצירת פונקציה מוגדרת על ידי המשתמש (UDF) בהתאמה אישית

אחרי שמזהים את משתמשי היעד, אנחנו רוצים לנתח את תוכן הפוסטים שלהם. עם זאת, פוסטים גולמיים בפורומים מכילים לעיתים קרובות תגי HTML וייצוגים של ישויות מבולגנים. אנחנו צריכים להסיר אותם כדי לשפר את הקריאות ולהפחית את העלויות של המודלים בהמשך.

כדי להבין למה זה נחוץ, בואו נבדוק קודם איך נראה הגוף של הפוסט ב-Stack Overflow בפורמט גולמי. מריצים את השאילתה הבאה במסוף 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;

אם בודקים את הפלט, רואים שילוב של תגי עיצוב כמו <p>,‏ <b>,‏ <code> ואחרים שמוטמעים בטקסט. עיבוד ישיר של הנתונים האלה באמצעות טוקנייזרים של למידת מכונה במורד הזרם יגרום לרעשי רקע מיותרים ולניפוח מלאכותי של עלויות ההטמעה של הטוקנים.

למה כדאי להשתמש בפונקציית UDF ב-Python?

ניתוח מהימן של HTML באמצעות ביטויים רגולריים (Regex) ב-SQL טהור הוא תהליך רגיש ומועד לשגיאות ניתוח. הפעלת ספריית Python חזקה כמו beautifulsoup4 ישירות בשאילתות היא דרך אמינה להסרת תגים.

מריצים את שאילתת ה-DDL הבאה כדי לפרוס את הפונקציה המתמשכת clean_html בקבוצת הנתונים:

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

מאמתים את הפלט של הפונקציה באמצעות שאילתה פשוטה:

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

אמור להופיע הטקסט ללא רכיבי HTML:

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

5. שילובים חיצוניים מאובטחים ועיבוד מתקדם של למידת מכונה

אחרי שיש לנו טקסט נקי, אנחנו צריכים להכין אותו למודלים של למידת מכונה או למודלים גדולים של שפה (LLM) כמו Gemma. מודלים מסוג LLM לא יכולים לקרוא טקסט גולמי ישירות, אלא מעבדים מזהי טוקנים מספריים.

כדי להמיר את הטקסט הנקי שלנו לטוקנים, נייבא את ספריית transformers של Hugging Face ונטען טוקנייזר של Google T5 שאומן מראש ישירות בתוך מסד הנתונים שלנו.

יצירת קישור למשאבים ב-Cloud

מריצים את השאילתה הבאה במסוף BigQuery Studio כדי ליצור חיבור מאובטח:

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

יצירת פונקציית UDF של Tokenizer

עכשיו פורסים את פונקציית ה-UDF של הטוקנייזר בהתאמה אישית. שימו לב איך פונקציית העזר get_tokenizer() בודקת אם המשתנה הגלובלי tokenizer כבר אותחל לפני שהיא מנסה לבצע הורדה:

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

בודקים את הטוקנייזר באמצעות שאילתה פשוטה כדי לוודא שהנכס הורד בהצלחה ומוחזר מערך של מזהי מספרים שלמים:

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

עוברים לכרטיסיית JSON בחלונית של תוצאות השאילתה כדי לראות את המערך המובנה:

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

6. הפעלת צינור לעיבוד מקדים מקצה לקצה

עכשיו, אחרי שכל שלושת השלבים בצינור מוכנים, אפשר לשרשר אותם לשאילתת SQL אחת באמצעות ביטויי טבלה נפוצים (CTEs).

צינור הנתונים הזה מייצג תהליך עבודה מודרני של הנדסת נתונים:

  1. אפשר לבודד משתמשים פעילים ואת השאלות שלהם שקיבלו את הניקוד הכי גבוה באמצעות פונקציית UDF ציבורית של אחוזון.
  2. מסירים את העיצוב של ה-HTML הגולמי מהטקסט באופן מקומי באמצעות פונקציית clean_html UDF.
  3. ממירים את הטקסט הנקי למערכי טוקנים באמצעות פונקציית tokenize UDF שנשמרה במטמון.

מריצים את שאילתת הצינור הבאה במסוף 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;

עוברים לכרטיסייה JSON ב-BigQuery Studio כדי לבדוק את הפלט המובנה.

[{
  "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. נספח: איך צינור עיבוד הנתונים פועל ואיך מבצעים ביקורת על עלויות ההפעלה

בקטע הזה מוסבר בפירוט על המנגנונים הספציפיים של שאילתת העיבוד המקדים מקצה לקצה, ומוצגות דוגמאות לאופן שבו אפשר לעקוב אחרי צריכת המשבצות המדויקת והעלויות של הקונטיינרים המנוהלים במהלך ההרצה.

פירוט ארכיטקטורת צינור עיבוד הנתונים

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
)

בפלח השאילתה הראשון נאספים ציונים גולמיים של שאלות של משתתפים פעילים ב-Stack Overflow. הוא מאחד את הציונים של כל משתמש למערך יחיד (ARRAY_AGG) תוך הקפדה על סדר מיון דטרמיניסטי (ORDER BY id). מערך הנתונים מסונן כך שיכלול רק משתמשים שענו לפחות על חמש שאלות, כדי ליצור בסיס סטטיסטי תקף.

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

כדי לזהות את התורמים המובילים, הפלח הזה משתמש ב-percentiles Python UDF ציבורי כדי למצוא התפלגויות מדויקות של ציונים (האחוזונים ה-25, ה-50, ה-75 וה-95). כדי להימנע מהפעלה חוזרת של הפונקציה המורכבת הזו, החישוב מופיע בשאילתת משנה מקוננת. לאחר מכן, מדד ההשוואה של האחוזון ה-95 מאוחזר ישירות מהמערך שמתקבל במיקום האינדקס השלישי (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)
)

השאלות המקוריות מצורפות לרשימת המשתמשים הפעילים כדי לאחזר פוסטים שעמדו בסף של אחוזון 95 או חרגו ממנו. כדי למנוע שגיאות בהשוואה של סוגי מסדי נתונים, ציון ההשוואה מומר באופן מפורש באמצעות פעולת CAST לסוג FLOAT64 לפני ההערכה.

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
)

גופי פוסטים גולמיים מכילים לעיתים קרובות סימון מבולגן וקוד HTML סטנדרטי שמפחיתים את איכות הקלט של למידת מכונה בהמשך. במקום להשתמש בביטויים רגולריים מורכבים, צינור הנתונים קורא ל-UDF של Python בהתאמה אישית clean_html. הוא מפעיל באופן דינמי סביבת זמן ריצה של Python בתוך קונטיינר מבודד, ומשתמש בספריית BeautifulSoup כדי להסיר אלמנטים בצורה נקייה ולהפיק טקסט פשוט וקריא.

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
)

כדי להכין את התצוגה המקדימה של הטקסט הנקי להזנה למודל גנרטיבי, צינור הנתונים מפעיל את פונקציית ה-UDF של Python בהתאמה אישית שלנו על פרוסת טקסט של 120 תווים.tokenize ה-UDF פונה בצורה מאובטחת אל Hugging Face Hub כדי להוריד את הפרמטרים של Google T5 tokenizer. מכיוון שמופע ה-tokenizer נטען למשתנה גלובלי, המטמון של מאגר התגים במצב פעיל שומר את ההגדרה, וכך שורות עוקבות עוברות טוקניזציה מהירה בזיכרון ללא השהיה ברשת.

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;

בלוק השאילתה הסופי מוציא את מערך הנתונים המעובד. במקום להפעיל את פונקציית ה-UDF של הטוקניזציה בפעם השנייה כדי לספור את הטוקנים שנוצרו, פונקציית ARRAY_LENGTH המקורית של BigQuery מופעלת ישירות על מערך token_ids שחושב מראש. האסטרטגיה הזו מקטינה את מספר מחזורי ה-CPU המיותרים, את פעולות הקונטיינר ואת עלויות הביצוע הכוללות.

ביקורת על צריכת משבצות ועלויות של פונקציות מוגדרות על ידי המשתמש (UDF) בניהול

בזמן שאנחנו משיקים ב-BigQuery מרכזי בקרה מקיפים של נראות העלויות ישירות בממשק המשתמש של מסוף Google Cloud, מהנדסים יכולים לבצע ביקורת באופן פרוגרמטי על צריכת המשבצות המדויקת ועל עלויות הביצוע של קונטיינרים מנוהלים של כל שאילתה באמצעות מזהי משימות של BigQuery.

כדי לבדוק את הביצוע של השאילתה, מאתרים את מזהה העבודה.

  1. ב-BigQuery Studio, אפשר למצוא את זה בכרטיסייה Query history בתחתית המסוף.
  2. לוחצים על שאילתת צינור עיבוד הנתונים שהופעלה.
  3. בחלונית הפרטים Job Information (פרטי המשימה), מאתרים את השדה Job ID (מזהה המשימה).

אחרי שמזהים את מזהה המשימה הטהור, מחליפים את JOB_ID בשאילתה שלמטה ומריצים אותה ב-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";

עוברים לכרטיסייה JSON ב-BigQuery Studio כדי לבדוק את הפלט המובנה. אמורה להתקבל מטען ייעודי (payload) שדומה לזה:

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

הסבר על הפלט:

  • ‫total_slot_ms: זמן החישוב הכולל באלפיות השנייה שנעשה בו שימוש בכל שלבי השאילתה. בצינור עיבוד הנתונים המאוחד הזה, זמן הביצוע הממוצע הוא בערך 815 אלף אלפיות שנייה של משבצות זמן.
  • ‫external_service_costs: מערך שמפרט את המשאבים שנעשה בהם שימוש מחוץ למנוע הניתוח הרגיל של BigQuery.
  • external_service: הערך MANAGED_ROUTINE_EXECUTION מאשר שהעלות שייכת באופן ספציפי לאירוח של הרצת קונטיינר בלי שרת (serverless), שבו מתארחת סביבת ה-UDF המותאמת אישית של Python.
  • ‫slot_ms: הערך 3000 מייצג את מספר המילישניות המדויק של משאבי מחשוב מיוחדים שנצרכו בתוך זמן הריצה של הקונטיינר החם כדי להריץ את הלוגיקה של Python.
  • ‫billing_method: הערך SERVICES_SKU מציין שהחיובים האלה על מאגרי תגים מקומיים מחויבים באופן דינמי דרך המק"ט הייעודי של שירותי BigQuery, על סמך משך ההפעלה של מאגר התגים והתקורה של הזיכרון. במחיר סטנדרטי של 0.06 $לשעת משבצת (slot) לחישוב במספר אזורים בארה"ב (ראו דף התמחור של שירותי BigQuery), עלות הביצוע הטהורה של 3,000 מילישניות של משבצת מחושבת כך: (3,000ms / 3,600,000ms) * $0.06 = $0.00005 USD. זהו תהליך עבודה חסכוני.

8. ניקוי משאבי הענן

כדי להימנע מחיובים מתמשכים או מניצול של מכסות הפרויקט, צריך למחוק את מערך הנתונים ואת החיבורים ב-BigQuery ב-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. מעולה!

סיימתם את שיעור ה-Codelab בנושא יצירה ואבטחה של פונקציות UDF של Python בסביבת זמן הריצה של BigQuery Serverless.

ב-codelab הזה למדתם איך:

  • ניתוח נתונים באמצעות פונקציות UDF ציבוריות: אפשר להפעיל פונקציות UDF ציבוריות של Python שעברו קומפילציה מראש במערכי נתונים של Stack Overflow כדי לבצע פעולות מתמטיות של אחוזונים במערכים מצטברים.
  • שילוב חבילות של צד שלישי: אפשר לפרוס פונקציה מתמידה מותאמת אישית באמצעות זמן הריצה הסטנדרטי של Python והספרייה beautifulsoup4 כדי להסיר תגי HTML גולמיים באופן מקורי בתוך שאילתות SQL.
  • הגדרת חיבורים חיצוניים מאובטחים: יוצרים קישור למשאבים ב-Cloud של BigQuery כדי להעניק בצורה מאובטחת גישה יוצאת מבודדת של קונטיינרים של UDF לרשת, לצורך אחזור נכסים חיצוניים בלי לכתוב פרטי כניסה בתוך הקוד.
  • הטמעה של טוקניזציה מקומית באמצעות שמירת נתונים במטמון בזיכרון: מייבאים את ספריית Hugging Face transformers כדי לטעון טוקנייזר T5, ומשתמשים במשתנים גלובליים כדי לשמור במטמון קובצי הגדרה ולעבד שורות בתוך קונטיינרים חמים.
  • ביקורת על ביצועים ועלויות: אפשר להריץ שאילתות באופן פרוגרמטי בתצוגות של INFORMATION_SCHEMA.JOBS לפי אזור באמצעות מזהי עבודות של BigQuery כדי לעקוב אחרי צריכת משבצות (total_slot_ms) ועלויות שימוש במכולות (external_service_costs).

מה השלב הבא?