นำศักยภาพของ Python มาสู่ BigQuery ด้วย UDF ที่มีการจัดการ

1. บทนำ

ภาษาการค้นหาที่มีโครงสร้าง (SQL) คือมาตรฐานอุตสาหกรรมสำหรับการวิเคราะห์คลังข้อมูล อย่างไรก็ตาม การแสดงตรรกะขั้นตอนที่ซับซ้อน การคำนวณทางคณิตศาสตร์ การล้างข้อความ หรือเวิร์กโฟลว์การเตรียมแมชชีนเลิร์นนิงใน SQL ล้วนๆ อาจเป็นเรื่องที่ท้าทายอย่างยิ่ง

ในอดีต ทีมข้อมูลได้ดึงข้อมูลชุดข้อมูลขนาดใหญ่จาก BigQuery เมื่อจำเป็นต้องมีการประมวลผล Python ที่กำหนดเองที่ซับซ้อน ประมวลผลในเครื่องเสมือนหรือคลัสเตอร์ที่กำหนดเองภายนอก และโหลดผลลัพธ์กลับ แนวทางนี้ทำให้เกิดเวลาในการตอบสนองของเครือข่ายสูง เพิ่มความเสี่ยงในการปฏิบัติตามข้อกำหนดด้วยการย้ายข้อมูล และสร้างค่าใช้จ่ายในการจัดการโครงสร้างพื้นฐาน

ฟังก์ชันที่ผู้ใช้กำหนด (UDF) ของ Python ที่มีการจัดการของ BigQuery จะช่วยแก้ปัญหาเหล่านี้ได้ด้วยการเรียกใช้โค้ดที่กำหนดเองในทรัพยากรแบบ Serverless ซึ่งจะปรับขนาดโดยอัตโนมัติให้รองรับได้ถึงล้านแถว Google Cloud จะจัดการการคอมไพล์ การสร้างอิมเมจ การแก้ไขด้านความปลอดภัย และการดำเนินการ ซึ่งช่วยให้คุณเรียกใช้การคำนวณที่กำหนดเองได้โดยตรงในตำแหน่งที่ข้อมูลอยู่

ใน Codelab นี้ คุณจะได้สร้างไปป์ไลน์การวิเคราะห์และการประมวลผลข้อความล่วงหน้าในข้อมูลชุมชน StackOverflow เพื่อเตรียมข้อมูลสำหรับการรายงานและการแมชชีนเลิร์นนิงขั้นปลาย

ข้อกำหนดเบื้องต้น

• โปรเจ็กต์ Google Cloud ที่เปิดใช้การเรียกเก็บเงิน
• ความเข้าใจพื้นฐานเกี่ยวกับแนวคิด SQL, IAM และ BigQuery

สิ่งที่คุณจะได้เรียนรู้

• วิธีเรียกใช้ UDF ของ Python สาธารณะที่คอมไพล์ไว้ล่วงหน้าในชุดข้อมูลสาธารณะเพื่อวิเคราะห์การกระจายข้อมูล
• วิธีติดตั้งใช้งาน UDF ของ Python ที่กำหนดเองโดยใช้ beautifulsoup4 เพื่อล้างข้อมูลที่ไม่มีโครงสร้าง
• วิธีกำหนดค่าการเชื่อมต่อทรัพยากรระบบคลาวด์ BigQuery เพื่อดาวน์โหลดชิ้นงานแมชชีนเลิร์นนิงอย่างปลอดภัยและทำการแปลงโทเค็น ML ในเครื่องด้วยไลบรารี Transformers ของ Hugging Face โดยใช้การแคชคอนเทนเนอร์ในหน่วยความจำ
• วิธีเชื่อมโยงขั้นตอนเหล่านี้เป็นไปป์ไลน์ SQL ประสิทธิภาพสูงรายการเดียว

2. การตั้งค่าและข้อกำหนด

เริ่มต้น Cloud Shell

แม้ว่าคุณจะใช้งาน Google Cloud จากระยะไกลในแล็ปท็อปได้ แต่ใน Codelab นี้คุณจะใช้ Google Cloud Shell ซึ่งเป็นสภาพแวดล้อมบรรทัดคำสั่งที่ทำงานในระบบคลาวด์

1. ไปที่ Google Cloud Console จากนั้นเลือกหรือสร้างโปรเจ็กต์ Google Cloud
2. ⚠️ จดรหัสโปรเจ็กต์ คุณจะต้องใช้รหัสนี้ตลอดทั้งแล็บ

3. เปิด Cloud Shell ในแท็บใหม่: https://shell.cloud.google.com/
4. หากได้รับข้อความแจ้ง ให้คลิก "ให้สิทธิ์"
5. แทนที่ 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

💡 ทำไมจึงต้องเป็นภูมิภาค "us"

bigquery-public-data.stackoverflow ชุดข้อมูลสาธารณะที่เราจะใช้จะอยู่ในusหลายภูมิภาค หากต้องการค้นหาโดยใช้ UDF ของ Python ที่กำหนดเองโดยไม่มีข้อผิดพลาดข้ามภูมิภาค ชุดข้อมูลเป้าหมาย (python_udfs) ต้องอยู่ในภูมิภาคเดียวกัน (us)

เปิดใช้ 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 สาธารณะที่คอมไพล์ไว้ล่วงหน้าหลายรายการ (คลิกแท็บรูทีน) เนื่องจาก BigQuery กําหนดให้ต้องมีการจับคู่ประเภทอย่างชัดเจน เราจึงจะใช้ Common Table Expression (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 อาจแสดงเอาต์พุตที่ยุบหรือตัดทอน ซึ่งทําให้ตรวจสอบองค์ประกอบอาร์เรย์ได้ยาก

วิธีดูเอาต์พุตที่มีโครงสร้างและซ้อนกัน

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: อาร์เรย์ที่มีค่าจุดลอยตัวที่คำนวณแล้ว 4 ค่า ซึ่งสอดคล้องกับเปอร์เซ็นไทล์ที่ขออย่างแน่นอน: [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 แบบถาวรใช้งานได้ในชุดข้อมูล

⚠️ หมายเหตุเกี่ยวกับการเรียกใช้ SQL:

1. เมื่อคัดลอกคำค้นหา SQL ไปยัง BigQuery Console อย่าลืมแทนที่ YOUR_PROJECT_ID ด้วยรหัสโปรเจ็กต์ Google Cloud จริง
2. เนื่องจาก BigQuery จัดสรรสภาพแวดล้อมของคอนเทนเนอร์แบบไม่พร้อมกัน การค้นหาแรกที่เรียกใช้ UDF ใหม่จึงอาจเกิดความล่าช้าเนื่องจากการเริ่มต้นแบบเย็น คุณตรวจสอบสถานะการติดตั้งใช้งานได้ในรายการกิจวัตรของชุดข้อมูลในแผง Explorer

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. การผสานรวมภายนอกที่ปลอดภัยและการประมวลผล ML ขั้นสูง

ตอนนี้เรามีข้อความที่สะอาดแล้ว เราจึงต้องเตรียมข้อความสำหรับโมเดลแมชชีนเลิร์นนิงหรือโมเดลภาษาขนาดใหญ่ (LLM) เช่น Gemma LLM ไม่สามารถอ่านข้อความดิบได้โดยตรง แต่จะประมวลผลรหัสโทเค็นที่เป็นตัวเลข

เราจะนำเข้าtransformersไลบรารีของ Hugging Face และโหลดโทเค็นไนเซอร์ Google T5 ที่ผ่านการฝึกมาล่วงหน้าลงในฐานข้อมูลโดยตรงเพื่อแปลงข้อความที่คลีนแล้วให้เป็นโทเค็น

💡 เหตุใดเราจึงต้องใช้การเชื่อมต่อที่ปลอดภัย

โดยค่าเริ่มต้น UDF ของ BigQuery Python จะทํางานภายในคอนเทนเนอร์ที่ปลอดภัยและแยกต่างหากโดยไม่มีสิทธิ์เข้าถึงเครือข่ายขาออก เนื่องจากเราต้องดาวน์โหลดไฟล์โทเค็นไนเซอร์ T5 ที่ผ่านการฝึกมาก่อนจาก Hugging Face Hub สาธารณะ เราจึงต้องสร้างการเชื่อมต่อทรัพยากรระบบคลาวด์ BigQuery การเชื่อมต่อนี้ทำหน้าที่เป็นเกตเวย์ที่ปลอดภัย ซึ่งให้สิทธิ์การออกเครือข่ายขาออกจากคอนเทนเนอร์เพื่อดึงข้อมูลชิ้นงานที่จำเป็น

นอกจากนี้ การสร้างการเชื่อมต่อนี้ยังจัดสรรบัญชีบริการ Google Cloud ที่ไม่ซ้ำกันโดยอัตโนมัติด้วย หาก UDF ของ Python ต้องโต้ตอบกับ API ภายนอกที่ถูกจำกัดหรือทรัพยากรระบบคลาวด์ที่ปลอดภัย คุณสามารถให้สิทธิ์ 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. เรียกใช้ไปป์ไลน์การประมวลผลล่วงหน้าแบบครบวงจร

ตอนนี้ขั้นตอนทั้ง 3 ของไปป์ไลน์พร้อมแล้ว เราจึงสามารถเชื่อมโยงขั้นตอนเหล่านั้นเข้าด้วยกันเป็นคำค้นหา SQL เดียวได้โดยใช้ Common Table Expressions (CTEs)

ไปป์ไลน์นี้แสดงเวิร์กโฟลว์วิศวกรรมข้อมูลที่ทันสมัย

1. แยกผู้ใช้ที่ใช้งานอยู่และคำถามที่ได้คะแนนสูงสุดโดยใช้ UDF เปอร์เซ็นไทล์แบบสาธารณะ
2. ลบการจัดรูปแบบ HTML ดิบออกจากข้อความในเครื่องโดยใช้ UDF clean_html
3. แปลงข้อความที่ทำความสะอาดแล้วเป็นอาร์เรย์โทเค็นโดยใช้ 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) ระบบจะกรองชุดข้อมูลให้รวมเฉพาะผู้ใช้ที่มีคำถามอย่างน้อย 5 ข้อเพื่อสร้างพื้นฐานทางสถิติที่ถูกต้อง

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

กลุ่มนี้ใช้ percentilesUDF ของ Python แบบสาธารณะเพื่อค้นหาการกระจายคะแนนที่แน่นอน (เปอร์เซ็นไทล์ที่ 25, 50, 75 และ 95) เพื่อระบุ Top Contributor เราจึงห่อหุ้มการคำนวณไว้ในคําค้นหาย่อยแบบซ้อนกันเพื่อหลีกเลี่ยงการเรียกใช้ UDF ที่ต้องใช้การคำนวณอย่างเข้มข้นนี้หลายครั้ง จากนั้นระบบจะดึงข้อมูลเกณฑ์เปรียบเทียบเปอร์เซ็นไทล์ที่ 95 จากอาร์เรย์ผลลัพธ์โดยตรงที่ตำแหน่งดัชนี 3 (OFFSET(3))

target_questions AS (
  -- Isolate high-scoring questions from active users
  SELECT 
    q.id,
    q.owner_user_id,
    q.title,
    q.body AS raw_body,
    u.score_percentiles
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions` q
  JOIN 
    active_users u ON q.owner_user_id = u.owner_user_id
  WHERE 
    -- Explicit cast for robust comparison
    q.score >= CAST(u.p95_score AS FLOAT64)
)

ระบบจะรวมคำถามเดิมเข้ากับรายชื่อผู้ใช้ที่ใช้งานอยู่เพื่อดึงโพสต์ที่ตรงตามหรือเกินเกณฑ์เปอร์เซ็นไทล์ที่ 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 ซึ่งทำให้ข้อมูลอินพุตของแมชชีนเลิร์นนิงในภายหลังมีคุณภาพลดลง ไปป์ไลน์จะเรียกใช้ clean_htmlUDF ของ Python ที่กำหนดเองแทนการใช้นิพจน์ทั่วไปที่ซับซ้อน โดยจะเปิดใช้รันไทม์ 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
)

ไปป์ไลน์จะเรียกใช้ tokenizeUDF ของ Python ที่กำหนดเองในชิ้นส่วนที่มีอักขระ 120 ตัวเพื่อเตรียมตัวอย่างข้อความที่สะอาดสำหรับการนำเข้าโมเดล Generative UDF จะติดต่อ Hugging Face Hub อย่างปลอดภัยเพื่อดาวน์โหลดพารามิเตอร์โทเค็นไนเซอร์ T5 ของ Google เนื่องจากอินสแตนซ์โทเค็นไนเซอร์โหลดลงในตัวแปรส่วนกลาง คอนเทนเนอร์ที่อุ่นจึงแคชการกำหนดค่า ทำให้แถวต่อๆ ไปสามารถทำโทเค็นไนซ์ในหน่วยความจำได้อย่างรวดเร็วโดยไม่มีเวลาในการตอบสนองของเครือข่าย

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 ที่คำนวณไว้ล่วงหน้าแทนที่จะเรียกใช้ UDF การสร้างโทเค็นเป็นครั้งที่ 2 เพื่อให้นับโทเค็นที่สร้างขึ้น กลยุทธ์นี้ช่วยลดรอบ CPU ที่ซ้ำซ้อน การดำเนินการคอนเทนเนอร์ และต้นทุนการดำเนินการโดยรวม

การตรวจสอบต้นทุนการใช้สล็อตและ UDF ที่มีการจัดการ

ในขณะที่ BigQuery กำลังเปิดตัวแดชบอร์ดการแสดงต้นทุนที่ครอบคลุมใน UI ของคอนโซล Google Cloud โดยตรง วิศวกรสามารถตรวจสอบการใช้สล็อตที่แน่นอนและค่าใช้จ่ายในการเรียกใช้คอนเทนเนอร์ที่มีการจัดการของการค้นหาใดๆ ได้โดยใช้รหัสงาน BigQuery

หากต้องการตรวจสอบการเรียกใช้คําค้นหา ให้ค้นหารหัสงาน

1. ใน BigQuery Studio คุณจะดูข้อมูลนี้ได้โดยไปที่แท็บประวัติการค้นหาที่ด้านล่างของคอนโซล
2. คลิกคำค้นหาของไปป์ไลน์ที่ดำเนินการ
3. ในแผงรายละเอียดข้อมูลงาน ให้ค้นหาช่องรหัสงาน

💡 ทำความเข้าใจรูปแบบรหัสงาน

ในคอนโซล Google Cloud รหัสงาน 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, 000 มิลลิวินาทีของสล็อต
• external_service_costs: อาร์เรย์ที่แสดงรายละเอียดทรัพยากรที่ใช้ภายนอกเครื่องมือวิเคราะห์ BigQuery มาตรฐาน
• external_service: ค่า "MANAGED_ROUTINE_EXECUTION" ยืนยันว่าต้นทุนเป็นของโฮสติ้งการเรียกใช้คอนเทนเนอร์แบบ Serverless ที่โฮสต์สภาพแวดล้อม 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 USD ซึ่งแสดงให้เห็นเวิร์กโฟลว์ที่คุ้มค่า

⚠️ หมายเหตุ: ค่าใช้จ่ายในการทำธุรกรรมจริงในใบแจ้งยอดการเรียกเก็บเงินอาจสูงกว่าเล็กน้อย เนื่องจากตัวเลขนี้ครอบคลุมเฉพาะการดำเนินการของคอนเทนเนอร์ที่อุ่น โดยไม่รวมค่าธรรมเนียมการสแกนข้อมูลแบบออนดีมานด์มาตรฐานสำหรับการอ่านชุดข้อมูล Stack Overflow สาธารณะ หรือค่าใช้จ่ายในการสร้างรูทีนแบบครั้งเดียวเพื่อแพ็กเกจ BeautifulSoup/Transformers ในระหว่างการเริ่มต้นแบบเย็น

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 ภายในรันไทม์แบบ Serverless ของ BigQuery เสร็จสมบูรณ์แล้ว

ใน Codelab นี้ คุณได้เรียนรู้วิธีการต่อไปนี้

• สำรวจข้อมูลด้วย UDF สาธารณะ: เรียกใช้ UDF สาธารณะของ Python ที่คอมไพล์ไว้ล่วงหน้าในชุดข้อมูล Stack Overflow เพื่อดำเนินการเปอร์เซ็นไทล์ทางคณิตศาสตร์ในอาร์เรย์ที่รวบรวม
• ผสานรวมแพ็กเกจของบุคคลที่สาม: ใช้ UDF แบบถาวรที่กำหนดเองโดยใช้รันไทม์ Python มาตรฐานและไลบรารี beautifulsoup4 เพื่อลบแท็ก HTML ดิบๆ ภายในคำค้นหา SQL โดยตรง
• กำหนดค่าการเชื่อมต่อภายนอกที่ปลอดภัย: สร้างการเชื่อมต่อทรัพยากร Cloud ของ BigQuery เพื่อให้สิทธิ์เข้าถึงเครือข่ายขาออกแก่คอนเทนเนอร์ UDF ที่แยกต่างหากอย่างปลอดภัยเพื่อดึงข้อมูลชิ้นงานภายนอกโดยไม่ต้องฮาร์ดโค้ดข้อมูลเข้าสู่ระบบ
• ใช้โทเค็นในเครื่องด้วยการแคชในหน่วยความจำ: นำเข้าไลบรารี transformers ของ Hugging Face เพื่อโหลดโทเค็นไนเซอร์ T5 โดยใช้ตัวแปรส่วนกลางเพื่อแคชไฟล์การกำหนดค่าและประมวลผลแถวภายในคอนเทนเนอร์ที่พร้อมใช้งาน
• ตรวจสอบประสิทธิภาพและต้นทุนการดำเนินการ: ค้นหามุมมอง INFORMATION_SCHEMA.JOBS ระดับภูมิภาคโดยใช้รหัสงาน BigQuery เพื่อติดตามการใช้สล็อต (total_slot_ms) และต้นทุนการใช้คอนเทนเนอร์ (external_service_costs) โดยอัตโนมัติ

สิ่งต่อไปที่ควรทำ

• ดูแนวทางปฏิบัติแนะนำและโควต้า: ดูข้อมูลเกี่ยวกับขีดจำกัดการดำเนินการ การเกิดขึ้นพร้อมกัน และข้อควรพิจารณาเกี่ยวกับหน่วยความจำในคู่มือขีดจำกัดของ UDF Python ใน BigQuery
• เจาะลึกการเพิ่มประสิทธิภาพค่าใช้จ่าย: ทำความเข้าใจวิธีการเรียกเก็บเงินสำหรับสภาพแวดล้อม UDF ที่ใช้คอนเทนเนอร์และคำขอการเชื่อมต่อภายนอกในเอกสารประกอบเกี่ยวกับราคาของ BigQuery
• สร้างแอปพลิเคชันที่ทำงานด้วยระบบ AI ภายใน BigQuery: เรียนรู้เพิ่มเติมเกี่ยวกับการจัดการข้อความขั้นพื้นฐานและวิธีสร้างระบบมัลติโมดัลแบบครบวงจร ดูCodelab สร้างมาร์เก็ตเพลสยานยนต์ที่ทำงานด้วยระบบ AI ด้วยโมเดล BigQuery และ Gemini เพื่อใช้ประโยชน์จากการค้นหาเชิงความหมายและโมเดล Gemini ภายในคลังข้อมูลโดยตรง