मैनेज किए गए यूडीएफ़ की मदद से, BigQuery में Python की सुविधाओं का इस्तेमाल करना

1. परिचय

स्ट्रक्चर्ड क्वेरी लैंग्वेज (एसक्यूएल), डेटा वेयरहाउस के विश्लेषण के लिए इंडस्ट्री स्टैंडर्ड है. हालांकि, प्योर एसक्यूएल में मुश्किल प्रोसेस के लॉजिक, गणित की गणनाओं, टेक्स्ट को साफ़ करने या मशीन लर्निंग की तैयारी के वर्कफ़्लो को दिखाना बहुत मुश्किल हो सकता है.

डेटा टीमों ने पहले, BigQuery से बड़े डेटासेट निकाले थे. ऐसा तब किया गया था, जब कस्टम Python प्रोसेसिंग की ज़रूरत थी. इसके बाद, उन्होंने बाहरी कस्टम वर्चुअल मशीनों या क्लस्टर में डेटा को प्रोसेस किया और नतीजों को वापस लोड किया. इस तरीके से, नेटवर्क की लेटेन्सी बढ़ जाती है. साथ ही, डेटा को ट्रांसफ़र करने से, नियमों के उल्लंघन का खतरा बढ़ जाता है. इसके अलावा, इंफ़्रास्ट्रक्चर मैनेजमेंट का ओवरहेड भी बढ़ जाता है.

BigQuery के मैनेज किए गए Python के उपयोगकर्ता तय किए गए फ़ंक्शन (यूडीएफ़) इन समस्याओं को हल करते हैं. इसके लिए, वे सर्वरलेस संसाधनों पर कस्टम कोड चलाते हैं. ये संसाधन, लाखों लाइनों के हिसाब से अपने-आप स्केल हो जाते हैं. Google Cloud, कंपाइलेशन, इमेज बिल्डिंग, सुरक्षा पैचिंग, और एक्ज़ीक्यूशन को मैनेज करता है. इससे, आपके पास अपने डेटा के हिसाब से सीधे तौर पर कस्टम कैलकुलेशन करने का विकल्प होता है.

इस कोडलैब में, StackOverflow कम्यूनिटी के डेटा पर विश्लेषण और टेक्स्ट-प्रीप्रोसेसिंग पाइपलाइन बनाने का तरीका बताया गया है. इससे डेटा को डाउनस्ट्रीम रिपोर्टिंग और मशीन लर्निंग के लिए तैयार किया जा सकेगा.

ज़रूरी शर्तें

  • बिलिंग की सुविधा वाला Google क्लाउड प्रोजेक्ट.
  • SQL, IAM, और BigQuery के बुनियादी सिद्धांतों की जानकारी.

आपको क्या सीखने को मिलेगा

  • डेटा डिस्ट्रिब्यूशन का विश्लेषण करने के लिए, सार्वजनिक डेटासेट पर पहले से कंपाइल किए गए सार्वजनिक Python UDF को कॉल करने का तरीका.
  • अव्यवस्थित डेटा को साफ़ करने के लिए, beautifulsoup4 का इस्तेमाल करके अपना कस्टम Python UDF कैसे डिप्लॉय करें.
  • मेमोरी में मौजूद कंटेनर कैश मेमोरी का इस्तेमाल करके, मशीन लर्निंग ऐसेट को सुरक्षित तरीके से डाउनलोड करने और Hugging Face transformers लाइब्रेरी की मदद से लोकल एमएल टोकनाइज़ेशन करने के लिए, BigQuery Cloud Resource कनेक्शन को कॉन्फ़िगर करने का तरीका.
  • इन चरणों को एक ही बेहतर परफ़ॉर्मेंस वाली एसक्यूएल पाइपलाइन में कैसे जोड़ा जाए.

2. सेटअप और ज़रूरी शर्तें

Cloud Shell शुरू करें

Google Cloud को अपने लैपटॉप से रिमोटली ऐक्सेस किया जा सकता है. हालांकि, इस कोडलैब में Google Cloud Shell का इस्तेमाल किया जाएगा. यह क्लाउड में चलने वाला कमांड लाइन एनवायरमेंट है.

  1. Google Cloud Console पर जाएं. इसके बाद, Google Cloud प्रोजेक्ट चुनें या बनाएं.
  2. ⚠️ प्रोजेक्ट आईडी नोट करें. इस लैब में इसका इस्तेमाल किया जाएगा.

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

एपीआई चालू करना और 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. सार्वजनिक Python UDF की मदद से, डेटा डिस्ट्रिब्यूशन एक्सप्लोर करना

कस्टम कोड लागू करने से पहले, डेटासेट को एक्सप्लोर करना और कम क्वालिटी वाले नॉइज़ को फ़िल्टर करना मददगार होता है. इस चरण में, StackOverflow पर पूछे गए सवालों का विश्लेषण किया जाएगा. इससे आपको सक्रिय उपयोगकर्ताओं का पता चलेगा. साथ ही, उनके सवालों के स्कोर के आंकड़ों के डिस्ट्रिब्यूशन के बारे में जानकारी मिलेगी.

इसके लिए, Python UDF का इस्तेमाल क्यों करें?

ग्रुप किए गए डेटा के ऐरे पर कई सटीक पर्सेंटाइल (जैसे कि 25वां, 50वां, 75वां, और 95वां पर्सेंटाइल) कैलकुलेट करना, प्योर एसक्यूएल में मुश्किल होता है. साथ ही, इसमें ज़्यादा संसाधनों की ज़रूरत होती है. स्टैंडर्ड एसक्यूएल के विश्लेषण फ़ंक्शन, जैसे कि PERCENTILE_CONT नेस्ट किए गए ऐरे के बजाय, लाइनों के फ़्लैट कॉलम की उम्मीद करते हैं. हर लाइन के लिए, पहले से एग्रीगेट की गई ऐरे के सटीक पर्सेंटाइल का हिसाब लगाने के लिए, आपको ऐसी सबक्वेरी लिखनी होंगी जिनमें बहुत ज़्यादा जानकारी दी गई हो. इनमें हर पर्सेंटाइल मेट्रिक के लिए, अननेस्ट, सॉर्ट, और फिर से एग्रीगेट करने की प्रोसेस शामिल होती है. यह प्रोसेस सही नहीं है.

NumPy का इस्तेमाल करके, यूडीएफ़ में Python की साइंटिफ़िक लाइब्रेरी को ऑप्टिमाइज़ किया जा सकता है. इससे, एक लाइन के कोड की मदद से संख्याओं के ऐरे पर सटीक गणितीय पर्सेंटाइल का हिसाब लगाया जा सकता है.

लागू करना

Google Cloud, कई पहले से कंपाइल किए गए सार्वजनिक यूडीएफ़ होस्ट करता है. इसके लिए, रूटीन टैब पर क्लिक करें. BigQuery में टाइप मैचिंग की ज़रूरत होती है. इसलिए, हम डेटा को पहले से एग्रीगेट करने के लिए, कॉमन टेबल एक्सप्रेशन (सीटीई) का इस्तेमाल करेंगे. साथ ही, 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 में डिफ़ॉल्ट टेबल वाले नतीजे टैब में, फ़्लैट किया गया या काटा गया आउटपुट दिख सकता है. इससे ऐरे एलिमेंट की जांच करना मुश्किल हो जाता है.

स्ट्रक्चर्ड और नेस्ट किया गया आउटपुट देखने के लिए:

  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. कस्टम यूडीएफ़ बनाकर, टेक्स्ट को नेटिव तरीके से साफ़ करना

टारगेट किए गए उपयोगकर्ताओं की पहचान हो जाने के बाद, हमें उनकी पोस्ट के कॉन्टेंट का विश्लेषण करना है. हालांकि, फ़ोरम की रॉ पोस्ट में अक्सर गड़बड़ एचटीएमएल टैग और इकाइयां होती हैं. हम इन शब्दों को हटा देते हैं, ताकि टेक्स्ट को आसानी से पढ़ा जा सके और डाउनस्ट्रीम मॉडल की लागत कम हो सके.

यह क्यों ज़रूरी है, यह समझने के लिए पहले यह देखते हैं कि बिना फ़ॉर्मैट वाली 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> वगैरह. इन्हें सीधे तौर पर डाउनस्ट्रीम मशीन लर्निंग टोकनाइज़र का इस्तेमाल करके प्रोसेस करने से, गैर-ज़रूरी नॉइज़ आ जाएगी. साथ ही, टोकन इनजेस्ट करने की लागत में बनावटी तौर पर बढ़ोतरी हो जाएगी.

इसके लिए, Python UDF का इस्तेमाल क्यों करें?

प्योर एसक्यूएल में रेगुलर एक्सप्रेशन (रेगुलर एक्सप्रेशन) का इस्तेमाल करके, एचटीएमएल को भरोसेमंद तरीके से पार्स करना मुश्किल होता है. साथ ही, इसमें पार्सिंग से जुड़ी गड़बड़ियां होने की आशंका होती है. beautifulsoup4 जैसी मज़बूत Python लाइब्रेरी को सीधे तौर पर अपनी क्वेरी में चलाने से, टैग हटाने का एक भरोसेमंद तरीका मिलता है.

अपने डेटासेट में परसिस्टेंट 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;

आपको एचटीएमएल एलिमेंट के बिना टेक्स्ट दिखेगा:

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

5. सुरक्षित बाहरी इंटिग्रेशन और बेहतर एमएल प्रोसेसिंग

अब हमारे पास साफ़ टेक्स्ट है. हमें इसे मशीन लर्निंग मॉडल या Gemma जैसे लार्ज लैंग्वेज मॉडल (एलएलएम) के लिए तैयार करना होगा. एलएलएम, रॉ टेक्स्ट को सीधे तौर पर नहीं पढ़ सकते. वे संख्या वाले टोकन आईडी को प्रोसेस करते हैं.

हम अपने क्लीन टेक्स्ट को टोकन में बदलने के लिए, Hugging Face की transformers लाइब्रेरी को इंपोर्ट करेंगे. साथ ही, पहले से ट्रेन किए गए Google T5 टोकनाइज़र को सीधे अपने डेटाबेस में लोड करेंगे.

Cloud Resource Connection बनाना

सुरक्षित कनेक्शन बनाने के लिए, BigQuery Studio Console में यह क्वेरी चलाएं:

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 बनाना

अब, कस्टम टोकनाइज़र यूडीएफ़ को डिप्लॉय करें. ध्यान दें कि 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 क्वेरी में जोड़ सकते हैं.

यह पाइपलाइन, डेटा इंजीनियरिंग के आधुनिक वर्कफ़्लो को दिखाती है:

  1. सार्वजनिक प्रतिशत यूडीएफ़ का इस्तेमाल करके, सक्रिय उपयोगकर्ताओं और उनके सबसे ज़्यादा स्कोर वाले सवालों को अलग करें.
  2. हमारे clean_html UDF का इस्तेमाल करके, टेक्स्ट से रॉ एचटीएमएल फ़ॉर्मैटिंग को स्थानीय तौर पर हटाएं.
  3. कैश किए गए tokenize UDF का इस्तेमाल करके, साफ़ किए गए टेक्स्ट को टोकन ऐरे में बदलें.

BigQuery Studio Console में, यह पाइपलाइन क्वेरी चलाएं:

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;

स्ट्रक्चर्ड आउटपुट देखने के लिए, BigQuery Studio में JSON टैब पर जाएं.

[{
  "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वां पर्सेंटाइल) का पता चलता है. इस 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
)

पोस्ट के रॉ डेटा में अक्सर गड़बड़ मार्कअप और एचटीएमएल बॉयलरप्लेट होता है. इससे डाउनस्ट्रीम मशीन लर्निंग के इनपुट की क्वालिटी खराब हो जाती है. जटिल रेगुलर एक्सप्रेशन का इस्तेमाल करने के बजाय, पाइपलाइन हमारे कस्टम clean_html Python UDF को कॉल करती है. यह अलग किए गए कंटेनर में 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
)

जनरेटिव मॉडल में शामिल करने के लिए, साफ़ किए गए टेक्स्ट की झलक तैयार करने के लिए पाइपलाइन, 120 वर्णों वाले स्लाइस पर हमारे कस्टम tokenize Python UDF को शुरू करती है. UDF, Google T5 के टोकनाइज़र पैरामीटर डाउनलोड करने के लिए, Hugging Face Hub से सुरक्षित तरीके से संपर्क करता है. टोकनाइज़र इंस्टेंस को ग्लोबल वैरिएबल में लोड किया जाता है. इसलिए, वार्म कंटेनर कॉन्फ़िगरेशन को कैश मेमोरी में सेव करता है. इससे बाद की पंक्तियों को नेटवर्क की लेटेन्सी के बिना, मेमोरी में मौजूद टोकनाइज़र का इस्तेमाल करके तेज़ी से टोकन में बदला जा सकता है.

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;

फ़ाइनल क्वेरी ब्लॉक, प्रोसेस किए गए डेटासेट को आउटपुट करता है. जनरेट किए गए टोकन की गिनती करने के लिए, टोकनाइज़ेशन यूडीएफ़ को दूसरी बार लागू करने के बजाय, BigQuery के नेटिव ARRAY_LENGTH फ़ंक्शन को सीधे तौर पर पहले से कैलकुलेट की गई token_ids ऐरे पर लागू किया जाता है. इस रणनीति से, सीपीयू के गैर-ज़रूरी साइकल, कंटेनर ऑपरेशन, और एक्ज़ीक्यूशन की कुल लागत कम हो जाती है.

स्लॉट के इस्तेमाल और मैनेज किए गए यूडीएफ़ की लागत की ऑडिट करना

BigQuery, Google Cloud Console के यूज़र इंटरफ़ेस (यूआई) में सीधे तौर पर, लागत की पूरी जानकारी देने वाले डैशबोर्ड लॉन्च कर रहा है. हालांकि, इंजीनियर BigQuery जॉब आईडी का इस्तेमाल करके, किसी भी क्वेरी के लिए स्लॉट के इस्तेमाल और मैनेज किए गए कंटेनर को चलाने की लागत की प्रोग्राम के हिसाब से ऑडिट कर सकते हैं.

क्वेरी के एक्ज़ीक्यूशन की ऑडिट करने के लिए, अपना जॉब आईडी ढूंढें.

  1. BigQuery Studio में, आपको यह जानकारी कंसोल में सबसे नीचे मौजूद क्वेरी का इतिहास टैब में मिलेगी
  2. पाइपलाइन की उस क्वेरी पर क्लिक करें जिसे आपने चलाया है
  3. नौकरी की जानकारी वाले पैनल में, जॉब आईडी फ़ील्ड ढूंढें.

प्योर जॉब आईडी की पहचान करने के बाद, नीचे दी गई क्वेरी में 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";

स्ट्रक्चर्ड आउटपुट की जांच करने के लिए, BigQuery Studio में JSON टैब पर जाएं. आपको कुछ इस तरह का पेलोड मिलेगा:

[{
  "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" वैल्यू से पता चलता है कि यह लागत, सर्वरलेस कंटेनर एक्ज़ीक्यूशन से जुड़ी है. यह कंटेनर, हमारे कस्टम Python UDF एनवायरमेंट को होस्ट करता है.
  • slot_ms: "3000" वैल्यू, Python लॉजिक को लागू करने के लिए, वार्म कंटेनर रनटाइम में इस्तेमाल किए गए खास कंप्यूट संसाधनों के सटीक मिलीसेकंड को दिखाती है.
  • billing_method: "SERVICES_SKU" वैल्यू से पता चलता है कि स्थानीय भाषा में कंटेनर के लिए तय किए गए शुल्क, BigQuery की खास सेवाओं के एसकेयू के हिसाब से डाइनैमिक तरीके से बिल किए जाते हैं. यह शुल्क, कंटेनर को एक्ज़ीक्यूट करने की अवधि और मेमोरी ओवरहेड के आधार पर तय किया जाता है. अमेरिका में, एक से ज़्यादा क्षेत्रों में कंप्यूटिंग के लिए, स्टैंडर्ड कीमत 0.06 डॉलर प्रति स्लॉट-घंटा है. BigQuery की सेवाओं की कीमत वाले पेज पर जाकर इसके बारे में जानें. इस हिसाब से, 3,000 स्लॉट-एमएस की कुल कीमत (3,000 मि॰से॰ / 36,00,000 मि॰से॰) * 0.06 डॉलर = 0.00005 डॉलर होती है. इससे पता चलता है कि यह वर्कफ़्लो, कम लागत में पूरा हो जाता है.

8. क्लाउड संसाधनों को मिटाना

लगातार शुल्क लगने या प्रोजेक्ट के कोटे का इस्तेमाल होने से बचने के लिए, Cloud Shell में जाकर अपना BigQuery डेटासेट और कनेक्शन मिटाएं:

# 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. बधाई हो!

आपने BigQuery Serverless runtime में Python UDF बनाने और उन्हें सुरक्षित करने से जुड़ा कोडलैब पूरा कर लिया हो.

इस कोडलैब में, आपने ये सीखा:

  • सार्वजनिक यूडीएफ़ की मदद से डेटा एक्सप्लोर करना: एग्रीगेट किए गए ऐरे पर गणितीय पर्सेंटाइल की कार्रवाइयां करने के लिए, Stack Overflow के डेटासेट पर पहले से कंपाइल किए गए सार्वजनिक Python यूडीएफ़ कॉल करें.
  • तीसरे पक्ष के पैकेज इंटिग्रेट करना: स्टैंडर्ड Python रनटाइम और beautifulsoup4 लाइब्रेरी का इस्तेमाल करके, कस्टम परसिस्टेंट यूडीएफ़ डिप्लॉय करें. इससे, आपकी एसक्यूएल क्वेरी में मौजूद रॉ एचटीएमएल टैग को हटाया जा सकेगा.
  • सुरक्षित बाहरी कनेक्शन कॉन्फ़िगर करना: BigQuery Cloud Resource Connection बनाएं, ताकि अलग किए गए यूडीएफ़ कंटेनर को सुरक्षित तरीके से आउटबाउंड नेटवर्क ऐक्सेस दिया जा सके. इससे वे क्रेडेंशियल को हार्डकोड किए बिना, बाहरी ऐसेट फ़ेच कर पाएंगे.
  • इन-मेमोरी कैशिंग के साथ लोकल टोकनाइज़ेशन लागू करें: T5 टोकनाइज़र लोड करने के लिए, Hugging Face transformers लाइब्रेरी इंपोर्ट करें. साथ ही, कॉन्फ़िगरेशन फ़ाइलों को कैश करने और वार्म कंटेनर में मौजूद लाइनों को प्रोसेस करने के लिए, ग्लोबल वैरिएबल का इस्तेमाल करें.
  • क्वेरी की परफ़ॉर्मेंस और लागत की जांच करें: BigQuery जॉब आईडी का इस्तेमाल करके, प्रोग्राम के हिसाब से क्षेत्रीय INFORMATION_SCHEMA.JOBS व्यू की क्वेरी करें. इससे स्लॉट के इस्तेमाल (total_slot_ms) और कंटेनर के इस्तेमाल की लागत (external_service_costs) को ट्रैक किया जा सकता है.

आगे क्या करना है?