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 트랜스포머 라이브러리로 로컬 ML 토큰화를 실행하는 방법.
이러한 단계를 단일 고성능 SQL 파이프라인으로 연결하는 방법.

2. 설정 및 요건

Cloud Shell 시작

Google Cloud를 노트북에서 원격으로 실행할 수 있지만, 이 Codelab에서는 Cloud에서 실행되는 명령줄 환경인 Google Cloud Shell을 사용합니다.

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를 사용하면 한 줄의 코드로 숫자 배열에서 정확한 수학적 백분위수를 계산할 수 있습니다.

실행

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는 아웃바운드 네트워크 액세스 권한이 없는 안전한 격리된 컨테이너 내에서 실행됩니다. 공개 Hugging Face 허브에서 사전 학습된 T5 토큰화 도구 파일을 다운로드해야 하므로 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. 엔드 투 엔드 사전 처리 파이프라인 실행

이제 파이프라인의 세 단계가 모두 준비되었으므로 공통 테이블 표현식 (CTE)을 사용하여 단일 SQL 쿼리로 연결할 수 있습니다.

이 파이프라인은 최신 데이터 엔지니어링 워크플로를 나타냅니다.

공개 백분위수 UDF 를 사용하여 활성 사용자와 점수가 가장 높은 질문을 격리합니다.
clean_html UDF 를 사용하여 텍스트에서 원시 HTML 형식을 로컬로 삭제합니다.
캐시된 tokenize 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 기여자의 원시 질문 점수를 수집합니다. 결정적 정렬 순서 (ORDER BY id)를 적용하면서 각 사용자의 점수를 단일 배열 (ARRAY_AGG)로 통합합니다. 데이터 세트는 유효한 통계 기준선을 설정하기 위해 질문이 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 허브에 안전하게 연결하여 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 서비스 SKU를 통해 동적으로 청구됨을 나타냅니다. 슬롯 시간당 $0.06의 표준 미국 멀티 리전 컴퓨팅 가격 책정 (BigQuery 서비스 가격 책정 페이지 참고)에서 3,000 슬롯 밀리초의 순수 실행 비용은 (3,000ms / 3,600,000ms) * $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 모델을 활용합니다.