Menghadirkan kemampuan Python ke BigQuery dengan UDF terkelola

1. Pengantar

Structured Query Language (SQL) adalah standar industri untuk analisis data warehouse. Namun, mengekspresikan logika prosedural yang kompleks, komputasi matematika, pembersihan teks, atau alur kerja persiapan machine learning dalam SQL murni bisa sangat menantang.

Tim data secara historis mengekstrak set data besar dari BigQuery saat pemrosesan Python kustom yang kompleks diperlukan, memprosesnya di cluster atau mesin virtual kustom eksternal, dan memuat kembali hasilnya. Pendekatan ini menimbulkan latensi jaringan yang tinggi, meningkatkan risiko kepatuhan dengan memindahkan data, dan menimbulkan overhead pengelolaan infrastruktur.

Fungsi yang Ditentukan Pengguna (UDF) Python Terkelola BigQuery menyelesaikan masalah ini dengan menjalankan kode kustom pada resource tanpa server yang otomatis diskalakan ke jutaan baris. Google Cloud mengelola kompilasi, pembuatan image, patching keamanan, dan eksekusi, sehingga Anda dapat menjalankan perhitungan kustom langsung di tempat data Anda berada.

Dalam codelab ini, Anda akan membangun pipeline analitik dan pra-pemrosesan teks pada data komunitas StackOverflow, yang akan disiapkan untuk pelaporan hilir dan machine learning.

Prasyarat

• Project Google Cloud yang mengaktifkan penagihan.
• Pemahaman dasar tentang konsep SQL, IAM, dan BigQuery.

Yang akan Anda pelajari

• Cara memanggil UDF Python publik yang telah dikompilasi sebelumnya pada set data publik untuk menganalisis distribusi data.
• Cara men-deploy UDF Python kustom Anda sendiri menggunakan beautifulsoup4 untuk membersihkan data tidak terstruktur.
• Cara mengonfigurasi koneksi Resource Cloud BigQuery untuk mendownload aset machine learning secara aman dan melakukan tokenisasi ML lokal dengan library transformer Hugging Face menggunakan caching penampung dalam memori.
• Cara menggabungkan langkah-langkah ini ke dalam satu pipeline SQL berperforma tinggi.

2. Penyiapan dan persyaratan

Mulai Cloud Shell

Meskipun Google Cloud dapat dioperasikan dari jarak jauh menggunakan laptop Anda, dalam codelab ini, Anda akan menggunakan Google Cloud Shell, lingkungan command line yang berjalan di Cloud.

1. Buka Konsol Google Cloud, lalu pilih atau buat project Google Cloud.
2. ⚠️ Catat Project ID. Anda akan menggunakannya di seluruh lab ini.

3. Buka Cloud Shell di tab baru: https://shell.cloud.google.com/.
4. Jika diminta, klik Authorize.
5. Ganti PROJECT_ID dan tempel perintah berikut ke terminal:

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

Terapkan variabel ke sesi aktif Anda:

source ./env.sh

💡 Mengapa Wilayah "us"?

Set data publik bigquery-public-data.stackoverflow yang akan kita gunakan berada di multi-region us. Untuk membuat kueri menggunakan UDF Python kustom tanpa error lintas region, set data target (python_udfs) harus ditempatkan bersama di region yang sama (us).

Aktifkan API dan Buat Set Data BigQuery

Aktifkan layanan Google Cloud yang diperlukan di project Anda dan buat set data target:

# 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. Menjelajahi Distribusi Data dengan UDF Python Publik

Sebelum men-deploy kode kustom, sebaiknya jelajahi set data dan filter derau berkualitas rendah. Pada langkah ini, Anda akan menganalisis pertanyaan StackOverflow untuk menemukan pengguna aktif dan memahami distribusi statistik skor pertanyaan mereka.

Mengapa menggunakan UDF Python untuk hal ini?

Menghitung beberapa persentil yang tepat (seperti persentil ke-25, 50, 75, dan 95) pada array data yang dikelompokkan sangatlah rumit dan membutuhkan banyak resource dalam SQL murni. Fungsi analisis SQL standar seperti PERCENTILE_CONT mengharapkan kolom datar baris, bukan array bertingkat. Untuk menghitung persentil yang tepat dari array yang telah diagregasi sebelumnya per baris, Anda harus menulis subkueri verbose yang memisahkan, mengurutkan, dan menggabungkan kembali untuk setiap metrik persentil, yang tidak efisien.

Dengan menggunakan NumPy, library ilmiah Python yang sangat dioptimalkan di dalam UDF, Anda dapat menghitung persentil matematika yang tepat pada array angka dengan satu baris kode.

Eksekusi

Google Cloud menghosting beberapa UDF publik yang telah dikompilasi sebelumnya (Klik tab Rutinitas). Karena BigQuery memerlukan pencocokan jenis eksplisit, kita akan menggunakan ekspresi tabel umum (CTE) untuk melakukan pra-agregasi data dan melakukan transmisi array bilangan bulat ke array floating point menggunakan ekspresi UNNEST.

Jalankan kueri berikut di Konsol 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;

Dengan begitu, Anda dapat memahami performa pengguna secara instan tanpa perlu mengonfigurasi izin atau menulis kode Python kustom terlebih dahulu.

Memverifikasi Hasil

Karena kueri ini menampilkan jenis array bertingkat (scores dan score_percentiles), tab Hasil tabular default di BigQuery Studio dapat menampilkan output yang diratakan atau dipangkas, sehingga sulit untuk memeriksa elemen array.

Untuk melihat output bertingkat yang terstruktur:

1. Di panel hasil kueri, temukan tab bar (yang secara default adalah Results).
2. Klik tab JSON.

Anda akan melihat array JSON terstruktur yang merepresentasikan baris, mirip dengan berikut ini:

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

Memahami Output

• scores: Array lengkap skor pertanyaan mentah yang diposting oleh setiap pengguna unik.
• score_percentiles: Array yang berisi empat nilai floating-point yang dihitung. Nilai ini sama persis dengan persentil yang diminta: persentil [25th, 50th, 75th, and 95th]. Misalnya, untuk pengguna 533463, skor persentil ke-95 dari pertanyaannya adalah sekitar 8.85, yang menunjukkan bahwa pertanyaan teratasnya memiliki skor tinggi.

4. Membersihkan Teks Secara Native dengan Membuat UDF Kustom

Setelah pengguna target diidentifikasi, kita ingin menganalisis konten postingan mereka. Namun, postingan forum mentah sering kali berisi tag dan entitas HTML yang tidak rapi. Kita perlu menghapus karakter ini untuk meningkatkan keterbacaan dan mengurangi biaya model hilir.

Untuk memahami mengapa hal ini diperlukan, mari kita periksa terlebih dahulu seperti apa tampilan sebenarnya dari isi postingan Stack Overflow yang mentah dan tidak diformat. Jalankan kueri berikut di Konsol 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;

Jika Anda memeriksa output, Anda akan melihat campuran tag pemformatan seperti <p>, <b>, <code>, dan lainnya yang disematkan di dalam teks. Memprosesnya secara langsung menggunakan tokenizer machine learning hilir akan menimbulkan noise yang tidak perlu dan meningkatkan biaya penyerapan token secara artifisial.

Mengapa menggunakan UDF Python untuk hal ini?

Mengurai HTML secara andal menggunakan ekspresi reguler (Regex) dalam SQL murni rentan dan cenderung menimbulkan error penguraian. Menjalankan library Python yang andal seperti beautifulsoup4 langsung dalam kueri Anda menawarkan cara yang andal untuk menghapus tag.

Jalankan kueri DDL berikut untuk men-deploy fungsi clean_html persisten di set data Anda:

⚠️ Catatan tentang eksekusi SQL:

1. Saat menyalin kueri SQL ke Konsol BigQuery, jangan lupa untuk mengganti YOUR_PROJECT_ID dengan Project ID Google Cloud Anda yang sebenarnya.
2. Karena BigQuery menyediakan lingkungan penampung secara asinkron, kueri pertama yang memanggil UDF baru dapat mengalami penundaan cold start. Anda dapat memantau status deployment di bagian daftar Rutinitas set data di panel 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=" ")
''';

Verifikasi output fungsi dengan kueri sederhana:

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

Anda akan melihat teks yang telah dihapus tanpa elemen HTML:

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

5. Mengamankan Integrasi Eksternal dan Pemrosesan ML Tingkat Lanjut

Setelah memiliki teks yang bersih, kita perlu mempersiapkannya untuk model machine learning atau Model Bahasa Besar (LLM) seperti Gemma. LLM tidak dapat membaca teks mentah secara langsung; LLM memproses ID token numerik.

Untuk mengonversi teks bersih menjadi token, kita akan mengimpor library transformers Hugging Face dan memuat tokenizer Google T5 yang telah dilatih sebelumnya langsung di dalam database.

💡 Mengapa kita memerlukan Koneksi Aman?

Secara default, UDF Python BigQuery berjalan di dalam penampung terisolasi yang aman tanpa akses jaringan keluar. Karena kita perlu mendownload file tokenizer T5 yang telah dilatih sebelumnya dari Hugging Face Hub publik, kita harus membuat koneksi Resource Cloud BigQuery. Koneksi ini berfungsi sebagai gateway yang aman, yang memberikan akses keluar jaringan ke penampung untuk mengambil aset yang diperlukan.

Selain itu, pembuatan koneksi ini akan otomatis menyediakan Akun Layanan Google Cloud yang unik. Jika UDF Python Anda perlu berinteraksi dengan API eksternal yang dibatasi atau resource cloud yang aman, Anda dapat memberikan izin IAM tertentu ke akun layanan ini untuk menangani autentikasi secara aman.

Buat Koneksi Resource Cloud

Jalankan kueri berikut di Konsol BigQuery Studio untuk membuat koneksi yang aman:

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

Buat UDF Tokenizer

Sekarang, deploy UDF tokenizer kustom. Perhatikan cara fungsi bantuan get_tokenizer() memeriksa apakah variabel global tokenizer sudah diinisialisasi sebelum mencoba mendownload:

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

Uji tokenizer dengan kueri sederhana untuk memverifikasi bahwa tokenizer berhasil mendownload aset dan menampilkan array ID bilangan bulat:

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

Beralihlah ke tab JSON di panel hasil kueri Anda untuk melihat array terstruktur:

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

6. Menjalankan pipeline praprosesan end-to-end

Setelah ketiga langkah pipeline siap, kita dapat menggabungkannya menjadi satu kueri SQL menggunakan Common Table Expressions (CTE).

Pipeline ini merepresentasikan alur kerja data engineering modern:

1. Isolasi pengguna aktif dan pertanyaan dengan skor tertinggi mereka menggunakan UDF persentil publik.
2. Hapus pemformatan HTML mentah dari teks secara lokal menggunakan UDF clean_html kami.
3. Konversi teks yang sudah dibersihkan menjadi array token menggunakan UDF tokenize yang di-cache.

Jalankan kueri pipeline berikut di Konsol 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;

Beralihlah ke tab JSON di BigQuery Studio untuk memeriksa output terstruktur.

[{
  "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. Lampiran: Cara Kerja Pipeline & Biaya Eksekusi Audit

Bagian ini memberikan pembahasan mendalam tentang mekanisme spesifik kueri praprosesan menyeluruh dan menunjukkan cara memantau biaya penampungan slot dan biaya penampungan yang dikelola yang tepat dari eksekusi Anda.

Rincian Arsitektur Pipeline

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
)

Segmen kueri pertama ini mengumpulkan skor pertanyaan mentah untuk kontributor Stack Overflow aktif. Dataset ini menggabungkan skor setiap pengguna ke dalam satu array (ARRAY_AGG) sekaligus menerapkan urutan pengurutan deterministik (ORDER BY id). Dataset difilter untuk hanya menyertakan pengguna dengan minimal lima pertanyaan untuk menetapkan dasar statistik yang valid.

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

Untuk mengidentifikasi kontributor teratas, segmen ini menggunakan UDF Python percentiles publik untuk menemukan distribusi skor persis (persentil ke-25, ke-50, ke-75, dan ke-95). Untuk menghindari eksekusi UDF yang intensif secara komputasi ini beberapa kali, penghitungan dibungkus dalam subkueri bertingkat. Tolok ukur persentil ke-95 kemudian diambil langsung dari array yang dihasilkan pada posisi indeks ketiga (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)
)

Pertanyaan asli digabungkan dengan daftar pengguna aktif untuk mengambil postingan yang memenuhi atau melampaui nilai persentil ke-95. Untuk mencegah error perbandingan jenis database, skor benchmark dikonversi secara eksplisit melalui operasi CAST menjadi jenis FLOAT64 sebelum evaluasi.

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
)

Isi postingan mentah sering kali berisi markup yang berantakan dan boilerplate HTML yang menurunkan kualitas input machine learning hilir. Daripada menggunakan ekspresi reguler yang kompleks, pipeline memanggil UDF Python clean_html kustom kami. Secara dinamis, fungsi ini akan meluncurkan runtime Python di dalam container terisolasi, menggunakan library BeautifulSoup untuk menghapus elemen secara bersih dan menghasilkan teks biasa yang mudah dibaca.

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
)

Untuk menyiapkan pratinjau teks bersih agar dapat diproses oleh model generatif, pipeline memanggil UDF Python tokenize kustom kami pada slice 120 karakter. UDF dengan aman menghubungi Hub Hugging Face untuk mendownload parameter tokenizer Google T5. Karena instance tokenizer dimuat ke dalam variabel global, container yang sudah di-warm akan menyimpan konfigurasi dalam cache, sehingga baris berikutnya dapat menjalani tokenisasi dalam memori yang cepat tanpa latensi jaringan.

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;

Blok kueri akhir menampilkan set data yang diproses. Daripada menjalankan UDF tokenisasi untuk kedua kalinya guna menghitung token yang dihasilkan, fungsi ARRAY_LENGTH bawaan BigQuery diterapkan langsung ke array token_ids yang telah dihitung sebelumnya. Strategi ini mengurangi siklus CPU yang berlebihan, operasi container, dan biaya eksekusi secara keseluruhan.

Mengaudit Penggunaan Slot & Biaya UDF Terkelola

Saat BigQuery meluncurkan dasbor visibilitas biaya yang komprehensif langsung di UI Konsol Google Cloud, engineer dapat mengaudit secara terprogram biaya eksekusi penampung terkelola dan konsumsi slot yang tepat dari kueri apa pun menggunakan ID Tugas BigQuery.

Untuk mengaudit eksekusi kueri, temukan ID pekerjaan Anda.

1. Di BigQuery Studio, Anda dapat menemukannya dengan membuka tab Query history di bagian bawah konsol
2. Klik kueri pipeline yang dijalankan
3. Di panel detail Job Information, temukan kolom ID pekerjaan.

💡 Memahami Format ID Pekerjaan

Di Konsol Google Cloud, ID Tugas BigQuery lengkap disusun dalam format berikut:

{YOUR_PROJECT_ID}:{REGION}.{JOB_ID}

Misalnya, ID pekerjaan lengkap di dunia nyata mungkin terlihat seperti ini:

my-gcp-project:us.bquxjob_2d801c6d_19ecde92e15

Saat membuat kueri tabel metadata regional (INFORMATION_SCHEMA.JOBS), Anda harus mengisolasi dan hanya menggunakan komponen {JOB_ID} murni (misalnya, bquxjob_2d801c6d_19ecde92e15). Jangan sertakan project ID atau awalan region sebelumnya, karena tindakan ini akan mencegah tampilan metadata cocok dengan catatan eksekusi Anda.

Setelah Anda mengidentifikasi ID pekerjaan murni, ganti JOB_ID dalam kueri di bawah dan jalankan di 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";

Beralihlah ke tab JSON di BigQuery Studio untuk memeriksa output terstruktur. Anda akan menerima payload yang mirip dengan berikut ini:

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

Memahami output:

• total_slot_ms: Total waktu komputasi dalam milidetik yang digunakan di semua tahap kueri. Untuk pipeline terpadu ini, eksekusi biasanya rata-rata sekitar 815 ribu milidetik slot.
• external_service_costs: Array yang mengelompokkan resource yang digunakan di luar mesin analisis BigQuery standar.
• external_service: Nilai "MANAGED_ROUTINE_EXECUTION" mengonfirmasi bahwa biaya tersebut secara khusus terkait dengan hosting eksekusi container serverless yang menghosting lingkungan UDF Python kustom kami.
• slot_ms: Nilai "3000" mewakili milidetik persis resource komputasi khusus yang digunakan dalam runtime container yang sudah di-warm untuk mengeksekusi logika Python.
• billing_method: Nilai "SERVICES_SKU" menunjukkan bahwa biaya penampung yang dilokalkan ini ditagih secara dinamis melalui SKU Layanan BigQuery khusus berdasarkan durasi eksekusi penampung dan overhead memori. Dengan harga komputasi multi-region standar AS sebesar $0,06 per jam slot (lihat Halaman Harga Layanan BigQuery), biaya eksekusi murni 3.000 slot-md dihitung sebagai (3.000 md / 3.600.000 md) * $0,06 = $0,00005 USD, yang menunjukkan alur kerja yang hemat biaya.

⚠️ Catatan: Biaya transaksi sebenarnya pada laporan penagihan Anda mungkin sedikit lebih tinggi karena angka ini hanya mencakup eksekusi penampung hangat. Biaya ini tidak mencakup biaya pemindaian data sesuai permintaan standar untuk membaca set data Stack Overflow publik, atau biaya build rutin satu kali untuk memaketkan BeautifulSoup/Transformers selama cold start.

8. Membersihkan resource cloud

Untuk menghindari biaya berkelanjutan atau penggunaan kuota project, hapus set data dan koneksi BigQuery Anda di 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. Selamat!

Anda telah menyelesaikan codelab tentang membangun dan mengamankan UDF Python dalam runtime BigQuery Serverless.

Dalam codelab ini, Anda telah mempelajari cara:

• Menjelajahi Data dengan UDF Publik: Panggil UDF Python publik yang telah dikompilasi sebelumnya pada set data Stack Overflow untuk melakukan operasi persentil matematika pada array yang diagregasi.
• Mengintegrasikan Paket Pihak Ketiga: Deploy persistent UDF kustom yang memanfaatkan runtime Python standar dan library beautifulsoup4 untuk menghapus tag HTML mentah secara native di dalam Kueri SQL Anda.
• Mengonfigurasi Koneksi Eksternal yang Aman: Buat Koneksi Resource Cloud BigQuery untuk memberikan akses jaringan keluar yang aman dan terisolasi ke penampung UDF guna mengambil aset eksternal tanpa meng-hardcode kredensial.
• Terapkan Tokenisasi Lokal dengan Cache Dalam Memori: Impor library transformers Hugging Face untuk memuat tokenizer T5, dengan memanfaatkan variabel global untuk meng-cache file konfigurasi dan memproses baris di dalam container aktif.
• Audit Performa dan Biaya Eksekusi: Buat kueri tampilan INFORMATION_SCHEMA.JOBS regional secara terprogram menggunakan ID Tugas BigQuery untuk melacak konsumsi slot (total_slot_ms) dan biaya penggunaan penampung (external_service_costs).

Apa selanjutnya?

• Tinjau Praktik Terbaik & Kuota: Pelajari batas eksekusi, konkurensi, dan pertimbangan memori dalam panduan batas UDF Python BigQuery.
• Mempelajari Pengoptimalan Biaya: Pahami cara kerja penagihan untuk lingkungan UDF yang dikontainerkan dan panggilan koneksi eksternal dalam dokumentasi Harga BigQuery.
• Membangun Aplikasi yang Didukung AI di dalam BigQuery: Melampaui manipulasi teks dasar dan pelajari cara membuat sistem multimodal menyeluruh. Ikuti codelab Membangun Marketplace Kendaraan yang Didukung AI dengan Model BigQuery dan Gemini untuk memanfaatkan penelusuran semantik dan model Gemini langsung di dalam data warehouse Anda.