Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

الاستفادة من إمكانات Python في BigQuery باستخدام الدوال المعرَّفة من قِبل المستخدم المُدارة

1- مقدمة

لغة الاستعلامات البنيوية (SQL) هي المعيار المتّبع في المجال لتحليل مستودعات البيانات. ومع ذلك، يمكن أن يكون التعبير عن منطق إجرائي معقّد أو عمليات حسابية رياضية أو تنظيف النصوص أو مهام سير عمل إعداد تعلُّم الآلة في لغة SQL فقط أمرًا صعبًا للغاية.

في السابق، كانت فِرق البيانات تستخرج مجموعات بيانات ضخمة من BigQuery عندما كانت هناك حاجة إلى معالجة مخصّصة معقّدة باستخدام Python، ثم تعالجها في آلات افتراضية أو مجموعات خارجية مخصّصة، وتحمّل النتائج مرة أخرى. يؤدي هذا النهج إلى زيادة وقت استجابة الشبكة، ويزيد من مخاطر الامتثال من خلال نقل البيانات، ويؤدي إلى زيادة تكاليف إدارة البنية الأساسية.

تعمل الدوال المعرَّفة من قِبل المستخدم (UDF) في BigQuery التي تتم إدارتها باستخدام Python على حلّ هذه المشاكل من خلال تشغيل رمز مخصّص على موارد بلا خادم يتم توسيع نطاقها تلقائيًا لتشمل ملايين الصفوف. تتولى Google Cloud عملية التجميع وإنشاء الصور وتطبيق تصحيحات الأمان والتنفيذ، ما يتيح لك إجراء عمليات حسابية مخصّصة مباشرةً في الموقع الجغرافي لبياناتك.

في هذا الدرس التطبيقي حول الترميز، ستنشئ مسارًا تحليليًا ومعالجة مسبقة للنصوص استنادًا إلى بيانات من منتدى StackOverflow، ما يجهّزها لإعداد التقارير وتعلُّم الآلة في المراحل اللاحقة.

المتطلبات الأساسية

مشروع على Google Cloud تم تفعيل الفوترة فيه
فهم أساسي لمفاهيم SQL وIAM وBigQuery

ما ستتعلمه

كيفية استدعاء دالة معرَّفة من قِبل المستخدم (UDF) في Python تم تجميعها مسبقًا وعامة على مجموعة بيانات عامة لتحليل توزيعات البيانات
كيفية نشر دالة معرَّفة من قِبل المستخدم (UDF) في Python مخصّصة باستخدام beautifulsoup4 لتنظيف البيانات غير المنظَّمة
كيفية إعداد اتصال بمورد على BigQuery Cloud لتنزيل مواد تعلُّم الآلة بشكل آمن وإجراء عملية تقسيم إلى رموز محلية باستخدام مكتبة Hugging Face `transformers` من خلال التخزين المؤقت للحاويات في الذاكرة
كيفية ربط هذه الخطوات في مسار SQL واحد عالي الأداء

2. الإعداد والمتطلبات

بدء Cloud Shell

يمكن تشغيل Google Cloud عن بُعد من جهاز الكمبيوتر المحمول، ولكن في هذا الدرس التطبيقي حول الترميز، ستستخدم Google Cloud Shell، وهي بيئة سطر أوامر تعمل في السحابة الإلكترونية.

انتقِل إلى Google Cloud Console، ثم اختَر مشروعًا على Google Cloud أو أنشِئ مشروعًا.
‫⚠️ سجِّل رقم تعريف المشروع. ستستخدمه طوال هذا المختبر.

افتح Cloud Shell في علامة تبويب جديدة: https://shell.cloud.google.com/.
انقر على "تفويض" إذا طُلب منك ذلك.
استبدِل 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

تفعيل واجهات برمجة التطبيقات وإنشاء مجموعة بيانات 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) عامة تم تجميعها مسبقًا مسبقًا (انقر على علامة التبويب الإجراءات الفرعية). بما أنّ 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)، قد تعرض علامة التبويب النتائج الجدولية التلقائية في BigQuery Studio ناتجًا مسطّحًا أو مقطوعًا، ما يصعّب فحص عناصر الصفيفة.

لعرض الناتج المنظَّم والمتداخل:

في لوحة نتائج طلب البحث، حدِّد شريط علامات التبويب (الذي يكون النتائج تلقائيًا).
انقر على علامة التبويب 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. لا يمكن للنماذج اللغوية الكبيرة قراءة النص الأولي مباشرةً، بل تعالج أرقام تعريف الرموز الرقمية.

لتحويل النص النظيف إلى رموز، سنستورد مكتبة transformers من Hugging Face ونحمّل أداة تقسيم إلى رموز Google T5 تم تدريبها مسبقًا مباشرةً داخل قاعدة البيانات.

💡 لماذا نحتاج إلى اتصال آمن؟

تلقائيًا، تعمل الدوال المعرَّفة من قِبل المستخدم (UDF) في BigQuery Python داخل حاوية آمنة ومعزولة بدون إمكانية الوصول إلى الشبكة الصادرة. بما أنّنا بحاجة إلى تنزيل ملفات أداة تقسيم إلى رموز T5 التي تم تدريبها مسبقًا من Hugging Face Hub العامة، علينا إنشاء اتصال بمورد على BigQuery Cloud. يعمل هذا الاتصال كبوابة آمنة، ما يمنح الحاوية إمكانية الوصول إلى الشبكة الصادرة لاسترجاع المواد المطلوبة.

بالإضافة إلى ذلك، يؤدي إنشاء هذا الاتصال إلى توفير حساب خدمة فريد على Google Cloud تلقائيًا. إذا كانت دالة معرَّفة من قِبل المستخدم (UDF) في Python بحاجة إلى التفاعل مع واجهات برمجة تطبيقات خارجية محظورة أو موارد سحابة إلكترونية آمنة، يمكنك منح حساب الخدمة هذا أذونات IAM محدّدة للتعامل مع المصادقة بشكل آمن.

إنشاء اتصال بمورد على السحابة الإلكترونية

نفِّذ طلب البحث التالي في وحدة تحكّم 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) لأداة تقسيم إلى رموز

الآن، انشر الدالة المعرَّفة من قِبل المستخدم (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 واحد باستخدام تعبيرات الجدول الشائعة (CTE).

يمثّل هذا المسار سير عمل حديثًا لهندسة البيانات:

عزل المستخدمين النشطين وأسئلتهم التي حصلت على أعلى النتائج باستخدام الدالة المعرَّفة من قِبل المستخدم (UDF) العامة للقيم المئوية
إزالة تنسيق HTML الأولي من النص محليًا باستخدام الدالة المعرَّفة من قِبل المستخدم (UDF) `clean_html`
تحويل النص النظيف إلى صفائف رموز باستخدام الدالة المعرَّفة من قِبل المستخدم (UDF) `tokenize` المخزّنة مؤقتًا

نفِّذ طلب بحث المسار التالي في وحدة تحكّم 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
  )
)

لتحديد أفضل المساهمين، تستخدم هذه الشريحة الدالة المعرَّفة من قِبل المستخدم (UDF) في Python العامة percentiles للعثور على توزيعات النتائج الدقيقة (القيم المئوية الـ 25 و50 و75 و95). لتجنُّب تنفيذ هذه الدالة المعرَّفة من قِبل المستخدم (UDF) التي تتطلب الكثير من العمليات الحسابية عدة مرات، يتم تضمين العملية الحسابية في طلب بحث فرعي متداخل. بعد ذلك، يتم استرجاع المقياس المرجعي للقيمة المئوية الـ 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 المخصّصة tokenize على شريحة مؤلفة من 120 حرفًا. تتواصل الدالة المعرَّفة من قِبل المستخدم (UDF) بشكل آمن مع Hugging Face Hub لتنزيل مَعلمات أداة تقسيم إلى رموز Google T5. بما أنّ يتم تحميل مثيل أداة تقسيم إلى رموز في متغيّر عام، تخزّن ذاكرة التخزين المؤقت للحاوية الدافئة الإعداد، ما يتيح للصفوف اللاحقة الخضوع لعملية تقسيم إلى رموز سريعة في الذاكرة بدون وقت استجابة للشبكة.

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) لأداة تقسيم إلى رموز مرة ثانية لحساب الرموز التي تم إنشاؤها، يتم تطبيق دالة BigQuery الأصلية ARRAY_LENGTH مباشرةً على صفيفة token_ids التي تم حسابها مسبقًا. يقلّل هذا الإجراء من دورات وحدة المعالجة المركزية الزائدة وعمليات الحاويات وتكاليف التنفيذ الإجمالية.

تدقيق استهلاك الخانات وتكاليف الدوال المعرَّفة من قِبل المستخدم (UDF) المُدارة

بينما تطرح BigQuery لوحات بيانات شاملة لعرض التكاليف مباشرةً في واجهة مستخدم Google Cloud Console، يمكن للمهندسين تدقيق الاستهلاك الدقيق للخانة وتكاليف تنفيذ الحاويات المُدارة لأي طلب بحث بشكل آلي باستخدام أرقام تعريف وظائف BigQuery.

لتدقيق تنفيذ طلب البحث، حدِّد رقم تعريف الوظيفة.

في BigQuery Studio، يمكنك العثور على هذا الرقم من خلال الانتقال إلى علامة التبويب سجلّ طلبات البحث في أسفل وحدة التحكّم.
انقر على طلب بحث المسار الذي تم تنفيذه.
في لوحة تفاصيل معلومات الوظيفة ، حدِّد حقل رقم تعريف الوظيفة.

💡 فهم تنسيقات أرقام تعريف الوظائف

في Google Cloud Console، يتم تنظيم رقم تعريف وظيفة BigQuery الكامل بالتنسيق التالي:

{YOUR_PROJECT_ID}:{REGION}.{JOB_ID}

على سبيل المثال، قد يبدو رقم تعريف وظيفة كامل من العالم الحقيقي على النحو التالي:

my-gcp-project:us.bquxjob_2d801c6d_19ecde92e15

عند الاستعلام عن جداول البيانات الوصفية الإقليمية (INFORMATION_SCHEMA.JOBS)، عليك عزل واستخدام مكوّن {JOB_ID} فقط (مثل bquxjob_2d801c6d_19ecde92e15). لا تُدرِج رقم تعريف المشروع أو بادئة المنطقة السابقة، لأنّ ذلك سيمنع عرض البيانات الوصفية من مطابقة سجلّ التنفيذ.

بعد تحديد رقم تعريف الوظيفة فقط، استبدِل 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 لفحص الناتج المنظَّم. من المفترض أن تتلقّى حمولة مشابهة لما يلي:

[{
  "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" أنّ التكلفة تنتمي تحديدًا إلى تنفيذ الحاوية بلا خادم التي تستضيف بيئة الدوال المعرَّفة من قِبل المستخدم (UDF) في Python المخصّصة.
slot_ms: تمثّل القيمة "3000" الملّي ثانية الدقيقة لموارد الحوسبة المتخصّصة المستهلَكة داخل وقت تشغيل الحاوية الدافئة لتنفيذ منطق Python.
billing_method: تشير القيمة "SERVICES_SKU" إلى أنّ هذه الرسوم المحلية للحاويات تتم فوترتها ديناميكيًا من خلال وحدة SKU المتخصّصة لخدمات BigQuery استنادًا إلى مدة تنفيذ الحاوية والذاكرة الزائدة. وفقًا لأسعار الحوسبة العادية في المنطقة المتعددة في الولايات المتحدة البالغة 0.06 دولار أمريكي لكل ساعة من الخانات (راجِع صفحة أسعار خدمات BigQuery)، يتم حساب تكلفة التنفيذ الصرفة البالغة 3,000 ملّي ثانية من الخانات على النحو التالي: (3,000 ملّي ثانية / 3,600,000 ملّي ثانية) * 0.06 دولار أمريكي = 0.00005 دولار أمريكي، ما يوضّح سير عمل فعّالاً من حيث التكلفة.

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. تهانينا!

لقد أكملت الدرس التطبيقي حول الترميز بشأن إنشاء الدوال المعرَّفة من قِبل المستخدم (UDF) في Python وتأمينها ضمن وقت تشغيل BigQuery بلا خادم.

في هذا الدرس التطبيقي حول الترميز، تعلّمت كيفية إجراء ما يلي:

استكشاف البيانات باستخدام الدوال المعرَّفة من قِبل المستخدم (UDF) العامة: استدعاء الدوال المعرَّفة من قِبل المستخدم (UDF) في Python العامة التي تم تجميعها مسبقًا على مجموعات بيانات Stack Overflow لإجراء عمليات القيم المئوية الرياضية على الصفائف المجمّعة
دمج حِزم الجهات الخارجية: نشر دالة معرَّفة من قِبل المستخدم (UDF) مستمرة مخصّصة تستخدم وقت تشغيل Python العادي ومكتبة beautifulsoup4 لإزالة علامات HTML الأولية بشكل أصلي داخل طلبات SQL
إعداد اتصالات خارجية آمنة: إنشاء اتصال بمورد على BigQuery Cloud لمنح حاويات الدوال المعرَّفة من قِبل المستخدم (UDF) المعزولة بشكل آمن إمكانية الوصول إلى الشبكة الصادرة لاسترجاع مواد خارجية بدون تضمين بيانات الاعتماد بشكل ثابت
تنفيذ عملية تقسيم إلى رموز محلية باستخدام التخزين المؤقت في الذاكرة: استيراد مكتبة transformers من Hugging Face لتحميل أداة تقسيم إلى رموز T5، باستخدام متغيّرات عامة لتخزين ملفات الإعداد مؤقتًا ومعالجة الصفوف داخل الحاويات الدافئة
تدقيق أداء التنفيذ والتكاليف: الاستعلام آليًا عن طرق عرض `INFORMATION_SCHEMA.JOBS` الإقليمية باستخدام أرقام تعريف وظائف BigQuery لتتبُّع استهلاك الخانات (total_slot_ms) وتكاليف استخدام الحاويات (external_service_costs)

ما هي الخطوات التالية؟

مراجعة أفضل الممارسات والحصص: التعرّف على حدود التنفيذ والتزامن والاعتبارات المتعلقة بالذاكرة في دليل حدود الدوال المعرَّفة من قِبل المستخدم (UDF) في BigQuery Python.
التعمّق في تحسين التكاليف: فهم كيفية عمل الفوترة لبيئات الدوال المعرَّفة من قِبل المستخدم (UDF) المستندة إلى الحاويات واستدعاءات الاتصال الخارجية في مستندات أسعار BigQuery.
إنشاء تطبيقات مستندة إلى الذكاء الاصطناعي داخل BigQuery: تجاوز معالجة النصوص الأساسية وتعلُّم كيفية إنشاء أنظمة متكاملة متعددة الوسائط اتّبِع الخطوات الواردة في الدرس التطبيقي حول الترميز إنشاء سوق للسيارات المستند إلى الذكاء الاصطناعي باستخدام BigQuery ونماذج Gemini للاستفادة من البحث الدلالي ونماذج Gemini مباشرةً داخل مستودع البيانات.