本頁面由 Cloud Translation API 翻譯而成。

使用支援 AI 的 BigQuery DataFrames 套件，從結構化和非結構化資料中取得洞察資料

1. 總覽

在本實驗室中，您將使用 BigQuery Studio 中的 Python 筆記本，透過 BigQuery DataFrames 和 Python 取得資料洞察。運用 Google 的生成式 AI 技術，分析及視覺化非結構化文字資料。

您將建立 Python 筆記本，將公開的客戶申訴資料庫分類並加以總結。這項功能可用於任何非結構化文字資料。

目標

在本研究室中，您將瞭解如何執行下列工作：

在 BigQuery Studio 中啟用及使用 Python 筆記本
使用 BigQuery DataFrames 套件連結至 BigQuery
使用 BigQuery ML 和 Vertex AI 中的文字嵌入端點，從非結構化文字資料建立嵌入項目
使用 BigQuery ML 建立嵌入式資料叢集
透過 BigQuery ML 使用 LLM 匯總叢集

2. 需求條件

Chrome 或 Firefox 等瀏覽器
已啟用計費功能的 Google Cloud 專案

事前準備

如要按照本程式碼研究室中的操作說明進行操作，您需要具備已啟用 BigQuery Studio 的 Google Cloud 專案，以及已連結的帳單帳戶。

在 Google Cloud 控制台的專案選取器頁面中，選取或建立 Google Cloud 專案
請確認 Google Cloud 專案已啟用計費功能。瞭解如何檢查專案是否已啟用計費功能
按照操作說明啟用 BigQuery Studio 以便管理素材資源。

準備 BigQuery Studio

建立空白筆記本，並將其連線至執行階段。

前往 Google Cloud 控制台中的「BigQuery Studio」。
按一下「+」按鈕旁的「▼」。
選取「Python 筆記本」。
關閉範本選取器。
選取「+ 程式碼」建立新的程式碼儲存格。
從程式碼儲存格安裝最新版的 BigQuery DataFrames 套件。請輸入下列指令。
```
%pip install --upgrade bigframes --quiet
```
點選 🞂? 按鈕或按下 Shift + Enter 鍵，執行程式碼儲存格。

3. 讀取公開資料集

在新的程式碼單元中執行下列指令，初始化 BigQuery DataFrames 套件：

import bigframes.pandas as bpd

bpd.options.bigquery.ordering_mode = "partial"

注意：在本教學課程中，我們會使用實驗性的「部分排序模式」，搭配類似 pandas 的篩選功能，可讓查詢更有效率。部分需要嚴格排序或索引的 pandas 功能可能無法運作。

消費者申訴資料庫

消費者申訴資料庫是透過 Google Cloud 的公開資料集計畫在 BigQuery 上提供。這是一組消費者金融產品和服務的申訴資料，由美國消費者金融保護局收集。

在 BigQuery 中查詢 bigquery-public-data.cfbp_complaints.complaint_database 資料表，以便分析消費者申訴資料庫。使用 bigframes.pandas.read_gbq() 方法，根據查詢字串或資料表 ID 建立 DataFrame。

在新的程式碼儲存格中執行下列程式碼，建立名為「feedback」的 DataFrame：

feedback = bpd.read_gbq(
    "bigquery-public-data.cfpb_complaints.complaint_database"
)

瞭解 DataFrame 的基本資訊

使用 DataFrame.peek() 方法下載少量資料樣本。

執行此儲存格：

feedback.peek()

預期的輸出內容：

  date_received                  product ... timely_response  consumer_disputed complaint_id  
0    2014-03-05  Bank account or service ...            True              False       743665   
1    2014-01-21  Bank account or service ...            True              False       678608   
2    2020-12-31          Debt collection ...            True               <NA>      4041190   
3    2014-02-12          Debt collection ...            True              False       714350   
4    2015-02-23          Debt collection ...            True              False      1251358

注意：如果您想將資料樣本視覺化，head() 需要排序，而且通常效率不如 peek()。

就像使用 pandas 一樣，您可以使用 DataFrame.dtypes 屬性查看所有可用欄和相應的資料類型。這些會以與 pandas 相容的方式公開。

執行此儲存格：

feedback.dtypes

預期的輸出內容：

date_received                   date32[day][pyarrow]
product                              string[pyarrow]
subproduct                           string[pyarrow]
issue                                string[pyarrow]
subissue                             string[pyarrow]
consumer_complaint_narrative         string[pyarrow]
company_public_response              string[pyarrow]
company_name                         string[pyarrow]
state                                string[pyarrow]
zip_code                             string[pyarrow]
tags                                 string[pyarrow]
consumer_consent_provided            string[pyarrow]
submitted_via                        string[pyarrow]
date_sent_to_company            date32[day][pyarrow]
company_response_to_consumer         string[pyarrow]
timely_response                              boolean
consumer_disputed                            boolean
complaint_id                         string[pyarrow]
dtype: object

DataFrame.describe() 方法會查詢 DataFrame 中的部分基本統計資料。由於這個 DataFrame 不含數值欄，因此會顯示非空值計數和不重複值數目的摘要。

執行此儲存格：

# Exclude some of the larger columns to make the query more efficient.
feedback.drop(columns=[
  "consumer_complaint_narrative",
  "company_public_response",
  "company_response_to_consumer",
]).describe()

預期的輸出內容：

         product  subproduct    issue  subissue  company_name    state ... timely_response  consumer_disputed  complaint_id
count    3458906     3223615  3458906   2759004       3458906  3417792 ...         3458906             768399       3458906
nunique       18          76      165       221          6694       63 ...               2                  2       3458906

4. 探索資料

在深入查看實際的申訴前，請先使用 DataFrame 上的類似 pandas 的方法，以視覺化方式呈現資料。

以視覺化方式呈現 DataFrame

我們有幾種內建的圖表方法，例如 DataFrame.plot.hist()。由於這個 DataFrame 主要包含字串和布林值資料，我們可以先進行一些匯總，進一步瞭解各資料欄。

計算各州收到的申訴案件數量。

complaints_by_state = (
  feedback.groupby(
    "state", as_index=False,
  ).size()
  .rename(columns={"size": "total_complaints"})
  .sort_values(by="total_complaints", ascending=False)
)

使用 DataFrame.to_pandas() 方法將其轉換為 pandas DataFrame。

complaints_pd = complaints_by_state.head(10).to_pandas()

在下載的 DataFrame 上使用 pandas 視覺化方法。

complaints_pd.plot.bar(x="state", y="total_complaints")

長條圖：加州是投訴最多的州

與其他資料集彙整

你先前查看的是各州的申訴案件，但這會遺漏重要的背景資訊。某些州的人口較多，與人口資料集彙整，例如美國人口調查局的美國社區問卷調查和 bigquery-public-data.geo_us_boundaries.states 資料表。

us_states = bpd.read_gbq("bigquery-public-data.geo_us_boundaries.states")
us_survey = bpd.read_gbq("bigquery-public-data.census_bureau_acs.state_2020_5yr")

# Ensure there are leading 0s on GEOIDs for consistency across tables.
us_states = us_states.assign(
    geo_id=us_states["geo_id"].str.pad(2, fillchar="0")
)

us_survey = us_survey.assign(
    geo_id=us_survey["geo_id"].str.pad(2, fillchar="0")
)

美國社區調查會根據 GEOID 識別各州。彙整州/省表格，以便根據兩個字母州/省代碼取得人口資料。

pops = us_states.set_index("geo_id")[["state"]].join(
  us_survey.set_index("geo_id")[["total_pop"]]
)

接著，將這份資料與投訴資料庫彙整，比較人口與投訴數量。

complaints_and_pops = complaints_by_state.set_index("state").join(
    pops.set_index("state")
)

建立散布圖，比較各州人口與申訴數量。

(
  complaints_and_pops
  .to_pandas()
  .plot.scatter(x="total_pop", y="total_complaints")
)

散布圖比較人口與申訴案件

比較人口數與申訴數時，有幾個州似乎是異常值。讀者可以透過點標籤繪製圖表，找出這些點。同樣地，您也可以提出一些假設，說明為何會出現這種情況 (例如不同的客層、金融服務公司數量不同等)，並進行測試。

5. 計算嵌入

重要的資訊通常隱藏在非結構化資料 (例如文字、音訊或圖片) 中。在這個範例中，申訴資料庫中的大部分實用資訊都包含在申訴內容中。

AI 和傳統技術 (例如情緒分析、字袋和 word2vec) 可以擷取一些非結構化資料的量化資訊。最近，與 LLM 密切相關的「向量嵌入」模型可建立一系列浮點數，代表文字的語意資訊。

選取資料庫的子集

執行向量嵌入模型會比其他作業耗用更多資源。為降低成本和配額問題，請在本教學課程的其餘部分中選取部分資料。

import bigframes.pandas as bpd

bpd.options.bigquery.ordering_mode = "partial"

feedback = bpd.read_gbq(
    "bigquery-public-data.cfpb_complaints.complaint_database"
)

# Note: if not using ordering_mode = "partial", you must specify these in read_gbq
# for these to affect query efficiency.
# feedback = bpd.read_gbq(
#    "bigquery-public-data.cfpb_complaints.complaint_database",
#     columns=["consumer_complaint_narrative"],
#     filters= [
#         ("consumer_complaint_narrative", "!=", ""),
#         ("date_received", "==", "2022-12-01")])

feedback.shape

2022-12-01 提交的申訴案件約為 1,000 件，而資料庫總列數則接近 350 萬 (請使用 feedback.shape 進行檢查)。

只選取 2022-12-01 的資料，且只選取 consumer_complaint_narrative 欄。

import datetime

feedback = feedback[
    # Filter rows by passing in a boolean Series.
    (feedback["date_received"] == datetime.date(2022, 12, 1))
    & ~(feedback["date_received"].isnull())
    & ~(feedback["consumer_complaint_narrative"].isnull())
    & (feedback["consumer_complaint_narrative"] != "")
    & (feedback["state"] == "CA")

    # Uncomment the following if using free credits for a workshop.
    # Billing accounts with free credits have limited Vertex AI quota.
    # & (feedback["product"] == "Mortgage")
][
    # Filter columns by passing in a list of strings.
    ["consumer_complaint_narrative"]
]

feedback.shape

pandas 的 drop_duplicates 方法需要列的總排序，因為它會嘗試選取第一個或最後一個符合的列，並保留與該列相關聯的索引。

請改為透過呼叫 groupby 方法來匯總資料，以便移除重複的資料列。

feedback = (
  feedback.groupby("consumer_complaint_narrative", as_index=False)
  .size()
)[["consumer_complaint_narrative"]]

feedback.shape

生成嵌入

BigQuery DataFrames 會透過 TextEmbeddingGenerator 類別產生嵌入向量。這項功能是根據 BigQuery ML 中的 ML.GENERATE_EMBEDDING 方法，呼叫 Vertex AI 提供的文字嵌入模型。

from bigframes.ml.llm import TextEmbeddingGenerator

embedding_model = TextEmbeddingGenerator(
    model_name="text-embedding-004"
)
feedback_embeddings = embedding_model.predict(feedback)

來看看嵌入資料的樣貌。這些向量代表文字嵌入模型所理解的文字語意。

feedback_embeddings.peek()

預期的輸出內容：

                        ml_generate_embedding_result  \
0  [ 7.36380890e-02  2.11779331e-03  2.54309829e-...   
1  [-1.10935252e-02 -5.53950183e-02  2.01338865e-...   
2  [-7.85628427e-03 -5.39347418e-02  4.51385677e-...   
3  [ 0.02013054 -0.0224789  -0.00164843  0.011354...   
4  [-1.51684484e-03 -5.02693094e-03  1.72322839e-...

這些向量具有許多維度。請查看單一嵌入向量：

feedback_embeddings["ml_generate_embedding_result"].peek().iloc[0]

嵌入產生作業會在「部分成功」合約下運作。這表示部分資料列可能有錯誤，因此無法產生嵌入項目。錯誤訊息會顯示在 'ml_generate_embedding_status' 欄中。空白表示沒有錯誤。

篩選嵌入資料，只保留沒有發生錯誤的資料列。

mask = feedback_embeddings["ml_generate_embedding_status"] == ""
valid_embeddings = feedback_embeddings[mask]
valid_embeddings.shape

6. 使用文字嵌入建立叢集

接下來，請使用 k-means 對嵌入資料進行分群。在本示範中，請使用任意數量的群組 (亦即中心點)。實際工作環境的解決方案應使用 Silhouette 方法等技巧調整重心數量。

from bigframes.ml.cluster import KMeans

num_clusters = 5
cluster_model = KMeans(n_clusters=num_clusters)
cluster_model.fit(valid_embeddings["ml_generate_embedding_result"])

clusters = cluster_model.predict(valid_embeddings)
clusters.peek()

移除所有嵌入失敗。

mask = clusters["ml_generate_embedding_status"] == ""
clusters = clusters[mask]

查看每個中心點的留言分布情形。

clusters.groupby("CENTROID_ID").size()

7. 摘要叢集

提供與每個中心點相關的幾則評論，並要求 Gemini 摘要投訴內容。提示工程是一個新興領域，但網路上有許多優秀範例，例如 https://www.promptingguide.ai/。

from bigframes.ml.llm import GeminiTextGenerator

preamble = "What is the main concern in this list of user complaints:"
suffix = "Write the main issue using a formal tone."

# Now let's sample the raw comments and get the LLM to summarize them.
prompts = []
for centroid_id in range(1, num_clusters + 1):
  cluster = clusters[clusters["CENTROID_ID"] == centroid_id]
  comments = "\n".join(["- {0}".format(x) for x in cluster.content.peek(40)])
  prompts.append("{}:\n{}\n{}".format(preamble, comments, suffix))

prompt_df = bpd.DataFrame(prompts)
gemini = GeminiTextGenerator(model_name="gemini-1.5-flash-001")
issues = gemini.predict(X=prompt_df, temperature=0.0)
issues.peek()

使用 Gemini 根據摘要撰寫報告。

from IPython.display import display, Markdown

prompt = "Turn this list of issues into a short, concise report:"
for value in issues["ml_generate_text_llm_result"]:
  prompt += "- {}".format(value)
prompt += "Using a formal tone, write a markdown text format report."

summary_df = bpd.DataFrame(([prompt]))
summary = gemini.predict(X=summary_df, temperature=0.0)

report = (summary["ml_generate_text_llm_result"].values[0])
display(Markdown(report))

8. 清理

如果您已為本教學課程建立新的 Google Cloud 專案，可以刪除專案，避免系統針對建立的資料表或其他資源收取額外費用。

9. 恭喜！

您已使用 BigQuery DataFrames 分析結構化和非結構化資料。您已瞭解 Google Cloud 的公開資料集、BigQuery Studio 中的 Python 筆記本、BigQuery ML、Vertex AI，以及 BigQuery Studio 的自然語言至 Python 功能。太棒了！

後續步驟

請嘗試在筆記本中產生 Python 程式碼。BigQuery Studio 中的 Python 筆記本採用 Colab Enterprise 技術。提示：我發現要求協助產生測試資料相當實用。
在 GitHub 上探索 BigQuery DataFrames 範例筆記本。
建立在 BigQuery Studio 中執行筆記本的排程。
部署含有 BigQuery DataFrames 的遠端函式，將第三方 Python 套件與 BigQuery 整合。