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

Mang sức mạnh của Python đến BigQuery bằng các UDF được quản lý

1. Giới thiệu

Ngôn ngữ truy vấn có cấu trúc (SQL) là tiêu chuẩn ngành để phân tích kho dữ liệu. Tuy nhiên, việc thể hiện logic phức tạp theo quy trình, các phép tính toán học, việc làm sạch văn bản hoặc quy trình chuẩn bị cho hoạt động học máy bằng SQL thuần tuý có thể rất khó khăn.

Trước đây, các nhóm dữ liệu đã trích xuất các tập dữ liệu khổng lồ từ BigQuery khi cần xử lý tuỳ chỉnh phức tạp bằng Python, xử lý các tập dữ liệu đó trong các máy ảo hoặc cụm tuỳ chỉnh bên ngoài và tải kết quả trở lại. Phương pháp này làm tăng độ trễ mạng, tăng rủi ro tuân thủ bằng cách di chuyển dữ liệu và tạo ra chi phí quản lý cơ sở hạ tầng.

Hàm do người dùng xác định (UDF) Python được BigQuery quản lý giải quyết những vấn đề này bằng cách chạy mã tuỳ chỉnh trên các tài nguyên không máy chủ tự động mở rộng quy mô lên đến hàng triệu hàng. Google Cloud quản lý quá trình biên dịch, tạo hình ảnh, vá bảo mật và thực thi, cho phép bạn chạy các phép tính tuỳ chỉnh ngay tại nơi dữ liệu của bạn lưu trú.

Trong lớp học lập trình này, bạn sẽ xây dựng một quy trình phân tích và tiền xử lý văn bản trên dữ liệu cộng đồng của StackOverflow, chuẩn bị dữ liệu đó cho báo cáo hạ nguồn và học máy.

Điều kiện tiên quyết

Một dự án trên Google Cloud đã bật tính năng thanh toán.
Có kiến thức cơ bản về các khái niệm SQL, IAM và BigQuery.

Kiến thức bạn sẽ học được

Cách gọi một UDF Python công khai được biên dịch sẵn trên một tập dữ liệu công khai để phân tích các bản phân phối dữ liệu.
Cách triển khai UDF Python tuỳ chỉnh của riêng bạn bằng cách sử dụng beautifulsoup4 để làm sạch dữ liệu không có cấu trúc.
Cách thiết lập một kết nối BigQuery Cloud Resource để tải các thành phần học máy xuống một cách an toàn và thực hiện việc mã hoá ML cục bộ bằng thư viện Hugging Face Transformers bằng cách sử dụng tính năng lưu vào bộ nhớ đệm vùng chứa trong bộ nhớ.
Cách kết hợp các bước này thành một quy trình SQL duy nhất có hiệu suất cao.

2. Thiết lập và yêu cầu

Khởi động Cloud Shell

Mặc dù có thể vận hành Google Cloud từ xa trên máy tính xách tay, nhưng trong lớp học lập trình này, bạn sẽ sử dụng Google Cloud Shell, một môi trường dòng lệnh chạy trên Cloud.

Truy cập vào Google Cloud Console, sau đó chọn hoặc tạo một dự án trên Google Cloud.
⚠️ Ghi lại mã dự án. Bạn sẽ sử dụng khoá này trong suốt phòng thí nghiệm này.

Mở Cloud Shell trong một thẻ mới: https://shell.cloud.google.com/.
Nếu được nhắc, hãy nhấp vào Uỷ quyền.
Thay thế PROJECT_ID và dán lệnh sau vào dòng lệnh:

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

Áp dụng các biến số cho phiên hoạt động:

source ./env.sh

Bật API và tạo tập dữ liệu BigQuery

Bật các dịch vụ cần thiết của Google Cloud trong dự án và tạo tập dữ liệu mục tiêu:

# 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. Khám phá Phân phối dữ liệu bằng UDF Python công khai

Trước khi triển khai mã tuỳ chỉnh, bạn nên khám phá tập dữ liệu và lọc bỏ nhiễu chất lượng thấp. Trong bước này, bạn sẽ phân tích các câu hỏi trên StackOverflow để tìm người dùng đang hoạt động và hiểu được phân phối thống kê về điểm số câu hỏi của họ.

Tại sao nên sử dụng UDF Python cho việc này?

Việc tính toán nhiều bách phân vị chính xác (chẳng hạn như bách phân vị thứ 25, 50, 75 và 95) trên các mảng dữ liệu được nhóm là một việc phức tạp và tốn nhiều tài nguyên trong SQL thuần tuý. Các hàm phân tích SQL chuẩn như PERCENTILE_CONT dự kiến sẽ có các cột hàng phẳng thay vì mảng lồng nhau. Để tính toán chính xác các phân vị của mảng được tổng hợp trước theo hàng, bạn sẽ phải viết các truy vấn phụ chi tiết để huỷ lồng, sắp xếp và tổng hợp lại cho từng chỉ số phân vị, điều này không hiệu quả.

Bằng cách sử dụng NumPy (thư viện khoa học được tối ưu hoá cao của Python) trong một UDF, bạn có thể tính toán chính xác các phân vị toán học trên một mảng số chỉ bằng một dòng mã.

Thực thi

Google Cloud lưu trữ một số UDF công khai được biên dịch sẵn (Nhấp vào thẻ Thường dùng). Vì BigQuery yêu cầu so khớp kiểu rõ ràng, nên chúng ta sẽ sử dụng một biểu thức bảng chung (CTE) để tổng hợp trước dữ liệu và truyền các mảng số nguyên thành mảng dấu phẩy động bằng cách sử dụng biểu thức UNNEST.

Chạy truy vấn sau trong Bảng điều khiển 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;

Nhờ đó, bạn có thể hiểu ngay hiệu suất của người dùng mà không cần định cấu hình quyền hoặc viết mã Python tuỳ chỉnh trước.

Xác minh kết quả

Vì truy vấn này trả về các loại mảng lồng nhau (scores và score_percentiles), nên thẻ Results (Kết quả) dạng bảng mặc định trong BigQuery Studio có thể hiển thị đầu ra được làm phẳng hoặc bị cắt bớt, khiến bạn khó kiểm tra các phần tử mảng.

Cách xem đầu ra có cấu trúc, lồng nhau:

Trong ngăn kết quả truy vấn, hãy tìm thanh thẻ (mặc định là Kết quả).
Nhấp vào thẻ JSON.

Bạn sẽ thấy một mảng JSON có cấu trúc biểu thị các hàng, tương tự như sau:

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

Tìm hiểu về đầu ra

scores: Mảng đầy đủ gồm điểm số thô của câu hỏi do mỗi người dùng riêng biệt đăng.
score_percentiles: Một mảng chứa 4 giá trị dấu phẩy động được tính toán. Các giá trị này hoàn toàn tương ứng với các phân vị được yêu cầu: phân vị thứ [25th, 50th, 75th, and 95th]. Ví dụ: đối với người dùng 533463, điểm bách phân vị thứ 95 của các câu hỏi của họ là khoảng 8.85, cho thấy các câu hỏi hàng đầu của họ có điểm số cao.

4. Làm sạch văn bản một cách tự nhiên bằng cách tạo UDF tuỳ chỉnh

Sau khi xác định được người dùng mục tiêu, chúng ta cần phân tích nội dung bài đăng của họ. Tuy nhiên, các bài đăng thô trên diễn đàn thường chứa các thực thể và thẻ HTML lộn xộn. Chúng ta cần loại bỏ những phần này để cải thiện khả năng đọc và giảm chi phí mô hình hạ nguồn.

Để hiểu lý do cần phải làm như vậy, trước tiên, hãy xem xét nội dung thô, chưa định dạng của bài đăng trên Stack Overflow. Chạy truy vấn sau đây trong Bảng điều khiển 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;

Nếu kiểm tra đầu ra, bạn sẽ thấy nhiều thẻ định dạng như <p>, <b>, <code> và các thẻ khác được nhúng bên trong văn bản. Việc xử lý trực tiếp các dữ liệu này bằng cách sử dụng các trình phân tích từ vựng học máy hạ nguồn sẽ tạo ra những nhiễu không cần thiết và làm tăng chi phí tiếp nhận mã thông báo một cách giả tạo.

Tại sao nên sử dụng UDF Python cho việc này?

Việc phân tích cú pháp HTML một cách đáng tin cậy bằng cách sử dụng biểu thức chính quy (Regex) trong SQL thuần tuý rất dễ bị lỗi và dễ gặp phải lỗi phân tích cú pháp. Việc chạy một thư viện Python mạnh mẽ như beautifulsoup4 ngay trong các truy vấn của bạn là một cách đáng tin cậy để loại bỏ thẻ.

Chạy truy vấn DDL sau để triển khai hàm clean_html liên tục trong tập dữ liệu của bạn:

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

Xác minh đầu ra của hàm bằng một truy vấn đơn giản:

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

Bạn sẽ thấy văn bản đã được loại bỏ mà không có các phần tử HTML:

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

5. Tích hợp an toàn với bên ngoài và xử lý nâng cao bằng học máy

Giờ đây, khi đã có văn bản sạch, chúng ta cần chuẩn bị văn bản đó cho các mô hình học máy hoặc Mô hình ngôn ngữ lớn (LLM) như Gemma. LLM không thể đọc trực tiếp văn bản thô; chúng xử lý mã thông báo dạng số.

Để chuyển đổi văn bản rõ ràng thành mã thông báo, chúng ta sẽ nhập thư viện transformers của Hugging Face và tải một trình mã hoá từ Google T5 được huấn luyện trước ngay trong cơ sở dữ liệu của mình.

💡 Tại sao chúng ta cần có Kết nối an toàn?

Theo mặc định, các UDF Python của BigQuery chạy trong một vùng chứa biệt lập, an toàn mà không có quyền truy cập mạng bên ngoài. Vì cần tải các tệp mã hoá từ vựng T5 được huấn luyện trước xuống từ Hugging Face Hub công khai, nên chúng ta phải thiết lập một kết nối BigQuery Cloud Resource. Kết nối này đóng vai trò là một cổng an toàn, cho phép vùng chứa truy cập vào mạng bên ngoài để tìm nạp các thành phần cần thiết.

Ngoài ra, việc tạo mối kết nối này sẽ tự động cung cấp một Tài khoản dịch vụ Google Cloud riêng biệt. Nếu UDF Python của bạn cần tương tác với các API bên ngoài bị hạn chế hoặc tài nguyên đám mây bảo mật, bạn có thể cấp cho tài khoản dịch vụ này các quyền IAM cụ thể để xử lý việc xác thực một cách an toàn.

Tạo mối kết nối tài nguyên trên đám mây

Chạy truy vấn sau trong Bảng điều khiển BigQuery Studio để thiết lập một kết nối an toàn:

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

Tạo UDF của Trình mã hoá

Bây giờ, hãy triển khai UDF của trình mã hoá từ tuỳ chỉnh. Lưu ý cách hàm trợ giúp get_tokenizer() kiểm tra xem biến chung tokenizer đã được khởi chạy hay chưa trước khi thử tải xuống:

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

Kiểm thử mã hoá từ bằng một truy vấn đơn giản để xác minh rằng mã hoá từ đã tải thành công thành phần và trả về một mảng gồm các mã nhận dạng số nguyên:

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

Chuyển sang thẻ JSON trong bảng kết quả truy vấn để xem mảng có cấu trúc:

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

6. Chạy quy trình tiền xử lý toàn diện

Giờ đây, khi cả 3 bước của quy trình đều đã sẵn sàng, chúng ta có thể liên kết chúng với nhau thành một truy vấn SQL duy nhất bằng cách sử dụng Biểu thức bảng chung (CTE).

Quy trình này thể hiện một quy trình làm việc hiện đại về kỹ thuật dữ liệu:

Phân lập người dùng đang hoạt động và những câu hỏi có điểm số cao nhất của họ bằng cách sử dụng UDF phân vị công khai.
Xoá định dạng HTML thô khỏi văn bản cục bộ bằng cách sử dụng UDF clean_html của chúng tôi.
Chuyển đổi văn bản đã dọn dẹp thành mảng mã thông báo bằng cách sử dụng UDF mã hoá được lưu vào bộ nhớ đệm.

Thực thi truy vấn quy trình sau đây trong Bảng điều khiển 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;

Chuyển sang thẻ JSON trong BigQuery Studio để kiểm tra đầu ra có cấu trúc.

[{
  "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. Phụ lục: Cách hoạt động của quy trình và chi phí thực thi kiểm tra

Phần này cung cấp thông tin chi tiết về cơ chế cụ thể của truy vấn tiền xử lý toàn diện và minh hoạ cách theo dõi mức tiêu thụ chính xác của các vị trí cũng như chi phí của vùng chứa được quản lý trong quá trình thực thi.

Phân tích cấu trúc quy trình

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
)

Phân đoạn truy vấn đầu tiên này thu thập điểm số câu hỏi thô cho những người đóng góp tích cực trên Stack Overflow. Thao tác này hợp nhất điểm số của từng người dùng thành một mảng duy nhất (ARRAY_AGG) trong khi vẫn đảm bảo thứ tự sắp xếp xác định (ORDER BY id). Tập dữ liệu được lọc để chỉ bao gồm những người dùng có ít nhất 5 câu hỏi nhằm thiết lập một đường cơ sở thống kê hợp lệ.

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

Để xác định những người đóng góp hàng đầu, phân đoạn này sử dụng UDF percentiles Python công khai để tìm phân phối điểm số chính xác (phân vị thứ 25, 50, 75 và 95). Để tránh thực thi UDF có mức sử dụng tài nguyên tính toán cao này nhiều lần, phép tính được bao bọc trong một truy vấn con lồng nhau. Sau đó, điểm chuẩn phân vị thứ 95 sẽ được truy xuất trực tiếp từ mảng kết quả ở vị trí chỉ mục thứ ba (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)
)

Các câu hỏi ban đầu được kết hợp với danh sách người dùng đang hoạt động để truy xuất những bài đăng đáp ứng hoặc vượt quá ngưỡng phân vị thứ 95. Để ngăn lỗi so sánh loại cơ sở dữ liệu, điểm đo điểm chuẩn được chuyển đổi rõ ràng thông qua thao tác CAST thành loại FLOAT64 trước khi đánh giá.

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
)

Nội dung thô của bài đăng thường chứa mã đánh dấu lộn xộn và mã HTML chung làm giảm chất lượng dữ liệu đầu vào cho hoạt động học máy ở hạ nguồn. Thay vì sử dụng các biểu thức chính quy phức tạp, quy trình này sẽ gọi UDF clean_html Python tuỳ chỉnh của chúng tôi. Công cụ này tự động tạo một thời gian chạy Python bên trong một vùng chứa riêng biệt, sử dụng thư viện BeautifulSoup để loại bỏ các phần tử một cách gọn gàng và xuất văn bản thuần tuý, dễ đọc.

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
)

Để chuẩn bị bản xem trước văn bản rõ ràng cho quá trình tiếp nhận mô hình tạo sinh, quy trình này sẽ gọi UDF tokenize Python tuỳ chỉnh của chúng tôi trên một đoạn 120 ký tự. UDF sẽ kết nối an toàn với Hugging Face Hub để tải các tham số của trình mã hoá từ vựng Google T5 xuống. Vì phiên bản mã hoá từ được tải vào một biến chung, nên vùng chứa khởi động nhanh sẽ lưu cấu hình vào bộ nhớ đệm, cho phép các hàng tiếp theo trải qua quá trình mã hoá từ nhanh trong bộ nhớ mà không bị độ trễ mạng.

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;

Khối truy vấn cuối cùng sẽ xuất tập dữ liệu đã xử lý. Thay vì thực thi UDF mã hoá lần thứ hai để đếm số lượng mã thông báo được tạo, hàm ARRAY_LENGTH gốc của BigQuery sẽ được áp dụng trực tiếp cho mảng token_ids đã tính toán trước. Chiến lược này giúp giảm số chu kỳ CPU dư thừa, các thao tác trên vùng chứa và tổng chi phí thực thi.

Kiểm tra mức tiêu thụ vị trí và chi phí UDF được quản lý

Mặc dù BigQuery đang triển khai các trang tổng quan toàn diện về khả năng hiển thị chi phí ngay trong giao diện người dùng Bảng điều khiển Google Cloud, nhưng các kỹ sư có thể kiểm tra theo chương trình mức tiêu thụ chính xác của các vị trí và chi phí thực thi vùng chứa được quản lý của mọi truy vấn bằng cách sử dụng mã nhận dạng công việc BigQuery.

Để kiểm tra quá trình thực thi truy vấn, hãy tìm ID công việc của bạn.

Trong BigQuery Studio, bạn có thể tìm thấy thông tin này bằng cách chuyển đến thẻ Nhật ký truy vấn ở cuối bảng điều khiển
Nhấp vào truy vấn quy trình đã thực hiện
Trong bảng điều khiển chi tiết Job Information (Thông tin về công việc), hãy tìm trường Job ID (ID công việc).

Sau khi bạn xác định được ID công việc thuần tuý, hãy thay thế JOB_ID trong truy vấn bên dưới và thực thi truy vấn đó trong 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";

Chuyển sang thẻ JSON trong BigQuery Studio để kiểm tra đầu ra có cấu trúc. Bạn sẽ nhận được một tải trọng tương tự như sau:

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

Tìm hiểu về kết quả:

total_slot_ms: Tổng thời gian tính toán (tính bằng mili giây) được dùng trong tất cả các giai đoạn truy vấn. Đối với quy trình hợp nhất này, quá trình thực thi thường mất trung bình khoảng 815 nghìn mili giây cho mỗi vị trí.
external_service_costs: Một mảng phân tích các tài nguyên được sử dụng bên ngoài công cụ phân tích BigQuery tiêu chuẩn.
external_service: Giá trị "MANAGED_ROUTINE_EXECUTION" xác nhận rằng chi phí này thuộc về hoạt động lưu trữ thực thi vùng chứa không máy chủ cụ thể, lưu trữ môi trường UDF Python tuỳ chỉnh của chúng tôi.
slot_ms: Giá trị "3000" biểu thị chính xác số mili giây của tài nguyên điện toán chuyên dụng được sử dụng trong thời gian chạy vùng chứa khởi động để thực thi logic Python.
billing_method: Giá trị "SERVICES_SKU" cho biết các khoản phí này đối với vùng chứa theo ngôn ngữ địa phương được tính linh hoạt thông qua SKU Dịch vụ BigQuery chuyên biệt dựa trên thời lượng thực thi vùng chứa và mức sử dụng bộ nhớ. Với mức giá điện toán tiêu chuẩn cho nhiều khu vực ở Hoa Kỳ là 0,06 USD cho mỗi giờ sử dụng khoảng trống (tham khảo Trang định giá dịch vụ BigQuery), chi phí thực thi thuần tuý của 3.000 mili giây khoảng trống được tính là (3.000 mili giây / 3.600.000 mili giây) * 0,06 USD = 0,00005 USD, cho thấy một quy trình làm việc tiết kiệm chi phí.

8. Dọn dẹp tài nguyên trên đám mây

Để tránh phát sinh phí liên tục hoặc tiêu thụ hạn mức dự án, hãy xoá tập dữ liệu và các kết nối BigQuery trong 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. Xin chúc mừng!

Bạn đã hoàn tất lớp học lập trình về cách tạo và bảo mật UDF Python trong thời gian chạy BigQuery Serverless.

Trong lớp học lập trình này, bạn đã tìm hiểu cách:

Khám phá dữ liệu bằng UDF công khai: Gọi UDF công khai được biên dịch sẵn bằng Python trên các tập dữ liệu của Stack Overflow để thực hiện các thao tác phần trăm toán học trên các mảng được tổng hợp.
Tích hợp các gói của bên thứ ba: Triển khai một UDF liên tục tuỳ chỉnh bằng cách sử dụng thời gian chạy Python tiêu chuẩn và thư viện beautifulsoup4 để loại bỏ các thẻ HTML thô một cách tự nhiên trong các truy vấn SQL.
Định cấu hình các kết nối bên ngoài an toàn: Tạo một BigQuery Cloud Resource Connection để cấp quyền truy cập mạng đi một cách an toàn cho các vùng chứa UDF riêng biệt nhằm tìm nạp các thành phần bên ngoài mà không cần mã hoá cứng thông tin đăng nhập.
Triển khai tính năng Mã hoá cục bộ bằng tính năng Lưu vào bộ nhớ đệm: Nhập thư viện transformers Hugging Face để tải một trình mã hoá T5, sử dụng các biến chung để lưu vào bộ nhớ đệm các tệp cấu hình và xử lý các hàng bên trong vùng chứa nóng.
Kiểm tra hiệu suất và chi phí thực thi: Truy vấn theo chương trình các khung hiển thị INFORMATION_SCHEMA.JOBS theo khu vực bằng cách sử dụng mã công việc BigQuery để theo dõi mức tiêu thụ vị trí (total_slot_ms) và chi phí sử dụng vùng chứa (external_service_costs).

Tiếp theo là gì?

Xem lại các phương pháp hay nhất và hạn mức: Tìm hiểu về các hạn mức thực thi, tính đồng thời và các yếu tố cần cân nhắc về bộ nhớ trong hướng dẫn về hạn mức UDF Python của BigQuery.
Tìm hiểu sâu về việc tối ưu hoá chi phí: Tìm hiểu cách tính phí cho các môi trường UDF được chứa trong vùng chứa và các lệnh gọi kết nối bên ngoài trong tài liệu về giá của BigQuery.
Xây dựng các ứng dụng dựa trên AI trong BigQuery: Vượt xa thao tác văn bản cơ bản và tìm hiểu cách xây dựng các hệ thống đa phương thức toàn diện. Tham gia Lớp học lập trình Xây dựng một trang web thương mại về xe dựa trên AI bằng BigQuery và các mô hình Gemini để tận dụng tính năng tìm kiếm ngữ nghĩa và các mô hình Gemini ngay trong kho dữ liệu của bạn.