把 LLM 跑在 BigQuery，又不讓帳單隨使用者膨脹

背景

我替一個媒體集團維護社群分析 pipeline，裡面有兩個地方用到 LLM。第一個是批次分類器：從 Facebook、Instagram、Threads、YouTube 抓進來的每則貼文與留言，都會在 dbt 裡被打上一個情緒標籤和一個主題標籤，然後像查任何一張表那樣被查。第二個是互動式的——Streamlit dashboard 上幾顆「AI 觀點」按鈕，使用者點了才會去摘要當天的負評、或本週的輿情。

兩個都用同一種方式呼叫 Gemini：透過 BigQuery 的 AI.GENERATE，直接寫在 SQL 裡。不用註冊 remote model、不用另外架推論服務——模型就是你在 query 裡呼叫的一個函式。而這個方便，也差點讓我走進一個會隨使用者人數變大的帳單。

那天花了 $20

建置期間，某一天的 Vertex AI 帳單來到大約 $20，而其他每天都接近零。二十塊不是什麼危機，但一個我當下說不出原因的數字突然跳起來，就值得追。它很乾淨地拆成那兩個功能——而這兩個的成本形狀完全不同。一個是有界的、放著一直跑基本上免費；另一個則是每多一個人打開 dashboard 就變大一點。

為什麼一開始就用 AI.GENERATE

把分類器放進倉儲，意味著標籤就住在資料旁邊。分析師直接查 fct_post_topics 和 fct_comments_sentiment，不用部署、也不用顧一個 Python 服務。模型呼叫就是一段 SQL：

select
post_id,
trim(AI.GENERATE(
  concat(
    '你是財經媒體的內容分類員。把以下貼文分到一個主題，只回下列其中一個中文詞：',
    '證券／金融／科技／產業／房市／國際／兩岸／政經／理財／生活／其他。貼文：',
    substr(post_text, 0, 600)
  ),
  endpoint => '{{ ai_endpoint() }}'
).result) as raw_topic
from to_classify
      dbt model：fct_post_topics（節錄、prompt 已去識別化）
    

這裡面有兩個小東西很值得。標籤不是通用的情緒分桶——它們是一個財經編輯台實際在跑的版面路線（證券、金融、科技、房市、兩岸……）。我一開始試過通用的「財經／非財經」切法，結果 ~70% 的貼文全落在同一桶、毫無鑑別度。另外文字截到 600 字：判情緒和主題不需要整篇全文，這個上限也讓 token 成本維持誠實。

花掉一個下午的陷阱

endpoint 必須是完整的 locations/global 路徑。如果只給短的模型名，BigQuery 會拿 dataset 自己的 region 去解析——我這邊是 asia-east1——而那裡根本沒有這個 publisher model，於是你會拿到一個裡面沒什麼有用資訊的 404。我把它收進一支 macro，換模型版本就只是改一行：

{% macro ai_endpoint(model='gemini-2.5-flash') %}
{{ return('https://aiplatform.googleapis.com/v1/projects/' ~ target.project
 ~ '/locations/global/publishers/google/models/' ~ model) }}
{% endmacro %}
      macros/ai_endpoint.sql
    

成本形狀 #1：批次分類器會自己收斂

一篇貼文發出去之後主題不會變，一則留言的情緒也不會變。所以沒理由去重分類已經做過的東西。這些 model 是 incremental 的，會走到 LLM 的只有還沒打過標籤的那些列：

to_classify as (
select * from posts
{% if is_incremental() %}
where post_id not in (select post_id from {{ this }})
{% endif %}
)
      只分類從來沒分類過的列
    

這讓穩定狀態下的成本變得很小。每天跑只付當天新增的貼文和留言；沒有新資料時重跑成本是零，因為走到 AI.GENERATE 的列數是零。唯一一筆大帳單是史上第一次 build，會把整個存量一次分類完——大約 6.6 萬則留言，花了大概 $0.30。之後這個工作就既無聊又便宜，而這正是你希望一個每日工作該有的樣子。

成本形狀 #2：dashboard 按鈕不會

互動按鈕剛好相反。每點一次就是一次 Gemini 呼叫。所以帳單會隨「多少人用 dashboard × 每人點幾次」變大——而我做這東西的初衷，本來就是要讓全集團都來用。粗估一下，100 個人每天各點 10 次，大約是 每月 $72、而且還在往上爬，因為登入的人會越來越多。這就是那個 $20 那天背後的形狀，放著不管只會更糟。

修法：照內容快取，不是照使用者

讓這件事變便宜的關鍵性質是：每顆按鈕的 prompt 都是用當天的資料組出來的。每個點「摘要今天負評」的使用者，產生的都是一模一樣的 prompt 字串，所以他們理應拿到一模一樣的答案。沒理由一天付超過一次。

所以我把 prompt 雜湊起來、把結果快取住。某個 prompt 當天第一個點的人會呼叫 Gemini、並把答案寫進一張用 SHA256(prompt) 當 key 的 BigQuery 表；之後的人全部讀那一列。兩層，因為 dashboard 跑在 Cloud Run 上、可能不只一個 instance：

@st.cache_data(ttl=3600)                    # L1：in-memory，每個 Cloud Run instance 各一份
def ai_generate(prompt: str) -> str:
  h = hashlib.sha256(prompt.encode("utf-8")).hexdigest()

  cached = ai_cache_lookup(h)             # L2：BigQuery，跨 instance、持久
  if cached is not None:
      return cached

  result = run_gemini(prompt)             # AI.GENERATE(@p, endpoint => "…global…")
  ai_cache_insert(h, result)              # 寫回，給下一個讀的人
  return result
      reporting/dashboards/ui.py — ai_generate（節錄）
    

現在成本跟使用者人數完全脫鉤了。它被框在「按鈕數 × 可選的日期區間 × 期間天數」——大約是 每月 $1 這個量級，不管用的是兩個人還是兩百個人。隔天底層資料變了，prompt 就變、雜湊就變，快取自然 miss、重新生成。沒有任何 invalidation 邏輯要寫錯。

三個讓它能放著不管的細節

快取壞掉不能弄垮頁面。讀和寫都包了起來，任何失敗——表不見、權限、streaming 打嗝——都會掉回一次正常生成、或乾脆被忽略。快取是一條支線；支線斷了，絕不能把主線拖下水。

prompt 走 query parameter，不是字串串接。只有 endpoint 是 literal（AI.GENERATE 規定如此）；prompt 本身以 bound 的 @p 參數帶進去，所以被使用者影響的文字沒辦法改寫 query。

舊列會被掃掉。一支每月排程 query 會刪掉超過 90 天的快取列——它們再也不會被命中（key 是內容雜湊），所以純粹是整理。只刪舊列也順便避開了 BigQuery 的限制：streaming insert 進來的列，落地後大約 30 分鐘內不能對它 DML。

這次學到什麼