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

マネージド UDF を使用して BigQuery に Python のパワーをもたらす

1. はじめに

構造化クエリ言語（SQL）は、データウェアハウス分析の業界標準です。ただし、複雑な手続き型ロジック、数学計算、テキストクリーニング、機械学習の準備ワークフローを純粋な SQL で表現することは非常に困難です。

これまで、データチームは、複雑なカスタム Python 処理が必要な場合に、 BigQuery から大規模なデータセットを抽出し、外部のカスタム仮想マシンまたはクラスタで処理して、結果をロードしていました。このアプローチでは、ネットワークレイテンシが大きくなり、データを移動することでコンプライアンスリスクが増大し、インフラストラクチャの管理オーバーヘッドが発生します。

BigQuery マネージド Python ユーザー定義関数（UDF）は、数百万行に自動的にスケーリングされるサーバーレスリソースでカスタムコードを実行することで、これらの問題を解決します。コンパイル、イメージのビルド、セキュリティパッチの適用、実行は Google Cloud が管理するため、データが存在する場所でカスタム計算を直接実行できます。

この Codelab では、StackOverflow コミュニティデータに対して分析とテキストの前処理を行うパイプラインを構築し、ダウンストリームのレポート作成と機械学習の準備を行います。

前提条件

課金を有効にした Google Cloud プロジェクト
SQL、IAM、BigQuery のコンセプトに関する基本的な知識

学習内容

一般公開データセットで事前コンパイル済みの一般公開 Python UDF を呼び出して、データの分布を分析する方法。
beautifulsoup4 を使用して独自のカスタム Python UDF をデプロイし、非構造化データをクリーンアップする方法。
BigQuery Cloud リソース接続を構成して、機械学習アセットを安全にダウンロードし、インメモリコンテナキャッシュを使用して Hugging Face transformers ライブラリでローカル ML トークン化を実行する方法。
これらの手順を 1 つの高性能 SQL パイプラインに連結する方法。

2. 設定と要件

Cloud Shell の起動

Google Cloud はノートパソコンからリモートで操作できますが、この Codelab では、Google Cloud Shell（Cloud 上で動作するコマンドライン環境）を使用します。

Google Cloud コンソールに移動し、Google Cloud プロジェクトを選択または作成します。
⚠️ プロジェクト ID をメモします。このラボ全体で使用します。

新しいタブで Cloud Shell（https://shell.cloud.google.com/）を開きます。
プロンプトが表示されたら、[承認] をクリックします。
PROJECT_ID を置き換えて、次のコマンドをターミナルに貼り付けます。

cat << 'EOF' > env.sh
#!/bin/bash
# env.sh: Environment variables for BigQuery Python UDFs codelab

# ⚠️ Replace 'YOUR_PROJECT_ID' with your actual Google Cloud Project ID
export PROJECT_ID="YOUR_PROJECT_ID"
export REGION="us"
export BQ_DATASET="python_udfs"
export BQ_RESOURCE_CONN="external_api_connection"
EOF

変数をアクティブセッションに適用します。

source ./env.sh

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. 一般公開 Python UDF を使用してデータの分布を調べる

カスタムコードをデプロイする前に、データセットを調べて品質の低いノイズを除外すると便利です。このステップでは、StackOverflow の質問を分析してアクティブユーザーを見つけ、質問スコアの統計分布を把握します。

Python UDF を使用する理由

グループ化されたデータの配列に対して、複数の正確なパーセンタイル（25 パーセンタイル、50 パーセンタイル、75 パーセンタイル、95 パーセンタイルなど）を計算することは、純粋な SQL では複雑でリソースを大量に消費します。PERCENTILE_CONT などの標準 SQL 分析関数は、ネストされた配列ではなく、行のフラットな列を想定しています。行ごとに事前集計された配列の正確なパーセンタイルを計算するには、パーセンタイル指標ごとにネスト解除、並べ替え、再集計を行う詳細なサブクエリを作成する必要があります。これは非効率的です。

UDF 内で Python の高度に最適化された科学ライブラリである NumPy を使用すると、1 行のコードで数値配列の正確な数学的パーセンタイルを計算できます。

実行

Google Cloud は、いくつかの事前コンパイル済みの一般公開 UDF をホストしています（ [ルーティン] タブをクリック）。BigQuery では明示的な型の一致が必要なため、共通テーブル式（CTE）を使用してデータを事前集計し、UNNEST 式を使用して整数配列を浮動小数点配列にキャストします。

BigQuery Studio コンソールで次のクエリを実行します。

WITH raw_user_scores AS (
  -- 1. Pre-aggregate user scores into an array
  SELECT 
    owner_user_id, 
    ARRAY_AGG(score) AS scores
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`
  WHERE 
    owner_user_id IS NOT NULL
  GROUP BY 
    owner_user_id
  HAVING 
    ARRAY_LENGTH(scores) >= 5
  LIMIT 5
)
SELECT 
  owner_user_id,
  scores,
  -- 2. Cast arrays to FLOAT64 and call the public percentile Python UDF
  `bigquery-public-data.python_udfs.percentiles`(
    ARRAY(SELECT CAST(s AS FLOAT64) FROM UNNEST(scores) AS s), 
    [25.0, 50.0, 75.0, 95.0]
  ) AS score_percentiles
FROM 
  raw_user_scores;

これにより、権限を構成したり、カスタム Python コードを記述したりすることなく、ユーザーのパフォーマンスをすぐに把握できます。

結果の確認

このクエリはネストされた配列型（scores と score_percentiles）を返すため、BigQuery Studio のデフォルトの表形式の [結果] タブにフラット化または切り捨てられた出力が表示され、配列要素の検査が困難になることがあります。

構造化されたネストされた出力を表示するには:

クエリ結果ペインで、タブバー（デフォルトは [結果]）を見つけます。
[JSON] タブをクリックします。

次のように、行を表す構造化された JSON 配列が表示されます。

[{
  "owner_user_id": "533463",
  "scores": ["0", "0", "-1", "0", "0", "2", "-1", "1", "0", "0", "-1", "0", "-3", "1", "1", "0", "1", "2", "3", "1", "0", "0", "1", "0", "0", "3", "6", "11", "0", "1", "0", "0", "3", "17", "0", "1", "1", "3", "5", "-2", "1", "-1", "-1", "2", "3", "0", "0", "0", "5", "0", "4", "0", "0", "0", "3", "3", "0", "140", "0", "1", "3", "0", "0", "-2", "-1", "0", "0", "2", "0", "9", "9", "0", "0", "1", "0", "0", "1", "-1", "0", "0", "0", "0"],
  "score_percentiles": ["0.0", "0.0", "1.75", "8.8500000000000085"]
}, {
  "owner_user_id": "13502536",
  "scores": ["0", "1", "0", "-5", "0", "1", "0", "1", "0", "0", "-2", "0", "1", "0", "1", "0", "0", "1", "0", "1", "0", "0"],
  "score_percentiles": ["0.0", "0.0", "1.0", "1.0"]
}, {
  "owner_user_id": "1170153",
  "scores": ["1", "0", "1", "0", "1", "0", "2", "0", "0", "0", "10", "5", "1", "0", "0", "2", "0", "2", "3", "-1", "1", "0", "1", "0", "0", "1", "0", "2", "0", "4", "0", "3", "0", "0", "2", "0", "0", "1", "0"],
  "score_percentiles": ["0.0", "0.0", "1.5", "4.1000000000000014"]
}, {
  "owner_user_id": "8558174",
  "scores": ["0", "0", "-1", "1", "2", "0"],
  "score_percentiles": ["0.0", "0.0", "0.75", "1.75"]
}, {
  "owner_user_id": "1073044",
  "scores": ["0", "1", "0", "0", "2", "2", "2", "1", "1", "1", "2", "1", "0", "2", "3", "1"],
  "score_percentiles": ["0.75", "1.0", "2.0", "2.25"]
}]

出力について

scores: 一意のユーザーごとに投稿された質問の生のスコアの完全な配列。
score_percentiles: 計算された 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> などの書式設定タグがテキスト内に埋め込まれていることがわかります。ダウンストリームの機械学習トークナイザーを使用してこれらを直接処理すると、不要なノイズが発生し、トークンの取り込みコストが不必要に増加します。

Python UDF を使用する理由

純粋な SQL で正規表現（Regex）を使用して HTML を確実に解析することは、脆弱で解析エラーが発生しやすくなります。クエリ内で beautifulsoup4 などの堅牢な Python ライブラリを直接実行すると、タグを確実に削除できます。

次の DDL クエリを実行して、データセットに永続的な clean_html 関数をデプロイします。

CREATE OR REPLACE FUNCTION `YOUR_PROJECT_ID.python_udfs.clean_html`(html_content STRING)
RETURNS STRING
LANGUAGE python
OPTIONS (
  runtime_version = 'python-3.11',
  entry_point = 'strip_tags',
  packages = ['beautifulsoup4>=4.12.0']
) AS r'''
from bs4 import BeautifulSoup

def strip_tags(html_content):
    if not html_content:
        return ""
    soup = BeautifulSoup(html_content, "html.parser")
    return soup.get_text(separator=" ")
''';

簡単なクエリで関数の出力を確認します。

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

HTML 要素のないテキストが表示されます。

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

5. 外部統合と高度な ML 処理を保護する

テキストがクリーンになったので、機械学習モデルまたは Gemma などの大規模言語モデル（LLM）用に準備する必要があります。LLM は生のテキストを直接読み取ることができず、数値トークン ID を処理します。

クリーンなテキストをトークンに変換するには、Hugging Face の transformers ライブラリをインポートし、事前トレーニング済みの Google T5 トークナイザーをデータベースに直接ロードします。

💡 安全な接続が必要な理由

デフォルトでは、BigQuery Python UDF は、アウトバウンドネットワークアクセスがない安全な分離コンテナ内で実行されます。事前トレーニング済みの T5 トークナイザーファイルを一般公開の Hugging Face Hub からダウンロードする必要があるため、BigQuery Cloud リソース接続を確立する必要があります。この接続はセキュアゲートウェイとして機能し、コンテナに必要なアセットを取得するためのアウトバウンドネットワークエグレスを許可します。

また、この接続を作成すると、一意の Google Cloud サービスアカウントが自動的にプロビジョニングされます。Python UDF が制限付きの外部 API または安全なクラウドリソースとやり取りする必要がある場合は、このサービスアカウントに特定の IAM 権限を付与して、認証を安全に処理できます。

Cloud リソース接続を作成する

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

簡単なクエリでトークナイザーをテストして、アセットが正常にダウンロードされ、整数 ID の配列が返されることを確認します。

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

クエリ結果パネルの [JSON] タブに切り替えて、構造化された配列を表示します。

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

6. エンドツーエンドの前処理パイプラインを実行する

パイプラインの 3 つのステップがすべて準備できたので、共通テーブル式（CTE）を使用して 1 つの SQL クエリに連結できます。

このパイプラインは、最新のデータエンジニアリングワークフローを表しています。

一般公開のパーセンタイル UDF を使用して、アクティブユーザーとその上位の質問を分離します。
clean_html UDF を使用して、テキストから生の HTML 書式設定をローカルで削除します。
キャッシュされたトークン化 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;

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 コントリビューターの生の質問スコアを収集します。各ユーザーのスコアを 1 つの配列（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
  )
)

上位のコントリビューターを特定するために、このセグメントでは一般公開の percentiles Python UDF を使用して、正確なスコア分布（25 パーセンタイル、50 パーセンタイル、75 パーセンタイル、95 パーセンタイル）を検索します。この計算負荷の高い 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_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 は Hugging Face Hub に安全にアクセスして、Google T5 トークナイザーパラメータをダウンロードします。トークナイザーインスタンスはグローバル変数にロードされるため、ウォームコンテナは構成をキャッシュし、後続の行はネットワークレイテンシなしで高速なインメモリトークン化を行うことができます。

SELECT 
  id,
  owner_user_id,
  title,
  score_percentiles,
  cleaned_body_preview AS cleaned_body,
  token_ids,
  ARRAY_LENGTH(token_ids) AS token_count
FROM 
  tokenized_data
ORDER BY 
  id ASC;

最後のクエリブロックは、処理済みのデータセットを出力します。生成されたトークンをカウントするためにトークン化 UDF を再度実行するのではなく、BigQuery のネイティブ ARRAY_LENGTH 関数が事前計算された token_ids 配列に直接適用されます。この戦略により、冗長な CPU サイクル、コンテナオペレーション、全体的な実行費用を削減できます。

スロット消費量とマネージド UDF の費用を監査する

BigQuery は、Google Cloud コンソール UI に包括的な費用可視化ダッシュボードを直接ロールアウトしていますが、エンジニアは BigQuery ジョブ ID を使用して、任意のクエリの正確なスロット消費量とマネージドコンテナの実行費用をプログラムで監査できます。

クエリの実行を監査するには、ジョブ ID を見つけます。

BigQuery Studio では、コンソールの下部にある [クエリ履歴] タブに移動して確認できます。
実行したパイプラインクエリをクリックします。
[ジョブ情報] 詳細パネルで、[ジョブ ID] フィールドを見つけます。

純粋なジョブ ID を特定したら、次のクエリの 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, 000 スロットミリ秒 前後です。
external_service_costs: 標準の BigQuery 分析エンジン外で使用されるリソースの内訳を示す配列。
external_service: 値 "MANAGED_ROUTINE_EXECUTION" は、費用がカスタム Python UDF 環境をホストするサーバーレスコンテナ実行に固有のものであることを示します。
slot_ms: 値 "3000" は、Python ロジックを実行するためにウォームコンテナランタイム内で消費された特殊なコンピューティングリソースの正確なミリ秒数を表します。
billing_method: 値 "SERVICES_SKU" は、これらのローカライズされたコンテナ料金が、コンテナの実行時間とメモリオーバーヘッドに基づいて、特殊な BigQuery Services SKU を介して動的に課金されることを示します。米国マルチリージョンの標準コンピューティング料金（スロット時間あたり $0.06）（BigQuery Services の料金ページを参照）では、3,000 スロットミリ秒の純粋な実行費用は（3,000 ミリ秒 / 3,600,000 ミリ秒）* $0.06 = $0.00005 USD と計算され、費用対効果の高いワークフローであることがわかります。

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 サーバーレスランタイムで Python UDF を構築して保護する Codelab が完了しました。

この Codelab では、以下について学びました。

一般公開 UDF を使用してデータを探索する: Stack Overflow データセットで事前コンパイル済みの一般公開 Python UDF を呼び出して、集計された配列に対して数学的なパーセンタイルオペレーションを実行します。
サードパーティパッケージを統合する: 標準の Python ランタイムと beautifulsoup4 ライブラリを利用してカスタムの永続的な UDF をデプロイし、SQL クエリ内で生の HTML タグをネイティブに削除します。
安全な外部接続を構成する: BigQuery Cloud リソース接続を作成して、分離された UDF コンテナにアウトバウンドネットワークアクセスを安全に許可し、認証情報をハードコードせずに外部アセットを取得します。
インメモリキャッシュを使用してローカルトークン化を実装する: Hugging Face transformers ライブラリをインポートして T5 トークナイザーをロードし、グローバル変数を使用して構成ファイルをキャッシュし、ウォームコンテナ内で行を処理します。
実行パフォーマンスと費用を監査する: BigQuery ジョブ ID を使用して、リージョンの INFORMATION_SCHEMA.JOBS ビューをプログラムでクエリし、スロット消費量（total_slot_ms）とコンテナ使用量（external_service_costs）を追跡します。

次のステップ

ベストプラクティスと割り当てを確認する: BigQuery Python UDF の制限ガイドで、実行制限、同時実行、メモリに関する考慮事項について学習します。
費用最適化の詳細: BigQuery の料金ドキュメントで、コンテナ化された UDF 環境と外部接続呼び出しの課金の仕組みについて説明します。
BigQuery 内で AI 搭載アプリケーションを構築する: 基本的なテキスト操作を超えて、エンドツーエンドのマルチモーダルシステムを構築する方法を学びます。BigQuery と Gemini モデルで AI 搭載の車両マーケットプレイスを構築する Codelab を実行して、データウェアハウス内でセマンティック検索と Gemini モデルを直接活用します。

マネージド UDF を使用して BigQuery に Python のパワーをもたらす

1. はじめに

前提条件

学習内容

2. 設定と要件

Cloud Shell の起動

API を有効にして BigQuery データセットを作成する

3. 一般公開 Python UDF を使用してデータの分布を調べる

Python UDF を使用する理由

実行

結果の確認

出力について

4. カスタム UDF を作成してテキストをネイティブにクリーンアップする

Python UDF を使用する理由

5. 外部統合と高度な ML 処理を保護する

Cloud リソース接続を作成する

トークナイザー UDF を作成する

6. エンドツーエンドの前処理パイプラインを実行する

7. 付録: パイプラインの仕組みと実行費用の監査

パイプライン アーキテクチャの内訳

スロット消費量とマネージド UDF の費用を監査する

8. クラウド リソースをクリーンアップする

9. 完了

次のステップ

パイプラインアーキテクチャの内訳

8. クラウドリソースをクリーンアップする