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 プロジェクト

始める前に

この Codelab の手順に沿って操作するには、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 には数値列が含まれていないため、null 以外の値の数と一意の値の数の概要が表示されます。

このセルを実行します。

# 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 で識別されます。states テーブルと結合して、2 文字の州コードで人口を取得します。

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. エンベディングを計算する

重要な情報は、テキスト、音声、画像などの非構造化データに隠されていることがよくあります。この例では、苦情データベースの有用な情報の多くが、苦情のテキストコンテンツに含まれています。

感情分析、単語のバッグ、word2vec などの AI と従来の手法を使用すると、非構造化データから定量的な情報を抽出できます。最近では、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 月 1 日に送信された苦情は約 1,000 件ですが、データベース全体の行数は約 350 万件です（feedback.shape で確認）。

2022 年 12 月 1 日のデータと 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 クラスを介してエンベディングベクトルを生成します。これは、Vertex AI が提供するテキストエンベディングモデルを呼び出す BigQuery ML の ML.GENERATE_EMBEDDING メソッドに基づいています。

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 平均法を使用してエンベディングをクラスタリングします。このデモでは、任意の数のグループ（重心）を使用します。本番環境品質のソリューションでは、シルエット法などの手法を使用して、重心の数を調整する必要があります。

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 と統合します。

AI 対応の BigQuery DataFrames パッケージを使用して、構造化データと非構造化データから分析情報を取得

1. 概要

目標

2. 必要なもの

始める前に

BigQuery Studio を準備する

3. 一般公開データセットを読み取る

消費者苦情データベース

DataFrame に関する基本情報を確認する

4. データを詳しく確認する

DataFrame を可視化する

他のデータセットと結合する

5. エンベディングを計算する

データベースのサブセットを選択する

エンベディングを生成する

6. テキスト エンベディングを使用してクラスタリングする

7. クラスタを要約する

8. クリーンアップ

9. 完了

次のステップ

6. テキストエンベディングを使用してクラスタリングする