BigQuery と Looker を使用して、Bigtable でクレジット カードの取引データを分析、可視化する

1. 概要

この Codelab では、Bigtable に書き込まれるクレジット カード取引データのストリームの分析について説明します。Bigtable change streams to BigQuery テンプレートを使用してデータをリアルタイムでエクスポートする方法について学習します。次に、変更ストリーム ログをクエリする方法と、データを再フォーマットして Looker を使用してダッシュボードを作成する方法について説明します。

この Codelab は、Bigtable、コマンドライン ツールの使用、イベント ストリーミング サービスに精通している技術ユーザーを対象としています。

426dc59200875a20.png

この Codelab では、次の方法について説明します。

  • 変更ストリームを有効にして Bigtable テーブルを作成する。
  • BigQuery データセットを作成します。
  • Dataflow テンプレート Bigtable change streams to BigQuery. をデプロイする
  • BigQuery でイベント ストリームに対してクエリを実行する。
  • Looker でイベント ストリームを可視化します。

次の図は、デプロイするシステムのアーキテクチャを示しています。

336e940307d0e0a.png

2. プロジェクトの設定

  1. Google Cloud コンソールの [プロジェクト セレクタ] ページで、Google Cloud プロジェクトを選択または作成します。

eb5309715175de69.png

  1. Google Cloud プロジェクトに対して課金が有効になっていることを確認します。
  2. この事前入力された API 有効化ページにアクセスして、Dataflow、Bigtable、BigQuery、Looker、Cloud Storage に必要な API を有効にします。

3. BigQuery データセットを作成する

この Codelab で後ほど BigQuery を使用してデータを分析します。データ パイプラインの出力に使用するデータセットを作成する手順は次のとおりです。

  1. Google Cloud コンソールで [BigQuery] ページに移動します。
  2. [エクスプローラ] ペインでプロジェクト名を見つけて、オーバーフロー メニュー（プロジェクト ID の横にある縦に 3 つ並んだ点）をクリックします。
  3. [データセットを作成] をクリックします。

122bc1a411b8dc63.png

  1. [データセットを作成] パネルで、次の操作を行います。
  • [データセット ID] に bigtable_bigquery_tutorial を入力します。
  • 残りのデフォルト設定はそのままにします。
  • [データセットを作成] をクリックします。

4. 変更ストリームを有効にして Bigtable テーブルを作成する

Bigtable は、低レイテンシで水平方向にスケーラブルな NoSQL データベース サービスです。金融データの提供が一般的なユースケースの 1 つです。ここでは、クレジット カード取引を保存できるテーブルを作成します。Bigtable は、世界中のトランザクションの高スループット書き込みを処理でき、そのデータをリアルタイムの不正行為検出に使用することもできます。

  1. Google Cloud コンソールで、Bigtable インスタンス ページに移動します。
  2. このチュートリアルで使用しているインスタンスの ID をクリックします。使用可能なインスタンスがない場合は、近くのリージョンに任意の名前でインスタンスを作成します。その他はすべてデフォルト構成を使用できます。

a2e8de7b66dc42e.png

  1. 左側のナビゲーション パネルで [テーブル] をクリックします。
  2. [テーブルを作成] をクリックします。

9c267c00f93747c4.png

  • テーブルに retail-database という名前を付けます。
  • transactions という名前の列ファミリーを追加します。
  • [変更ストリームを有効にする] を選択します。
  • ガベージ コレクション ポリシーと保持期間はデフォルト値のままにします。
  • [作成] をクリックします。

696cd1399c354816.png

5. データ パイプラインを初期化して変更ストリームを取得する

Bigtable のパフォーマンスは、ポイント読み取りと行範囲スキャンに最適ですが、テーブル全体の分析では、サービング容量と CPU リソースに負荷がかかる可能性があります。BigQuery はテーブル全体の分析に最適であるため、ここで使用するソリューションは二重書き込みです。つまり、データを Bigtable と BigQuery に書き込むことになります。これは一般的な手法です。Bigtable change streams to BigQuery Dataflow テンプレートを使用するため、追加のコーディングは必要ありません。データが Bigtable に書き込まれると、パイプラインは BigQuery に変更レコードを書き込みます。データが BigQuery に保存されたら、Bigtable でサービング データのパフォーマンスに影響を与えることなく、データセット全体に対して最適化されたクエリを実行できます。

f4cd9d8faf10ce77.png

  1. Bigtable の [テーブル] ページで、テーブル retail-database を見つけます。
  2. [変更ストリーム] 列で、[接続] をクリックします。
  3. [Dataflow で接続] ダイアログで、[BigQuery] を選択します。
  4. [Dataflow ジョブを作成] をクリックします。
  5. 表示されたパラメータ フィールドに、パラメータ値を入力します。省略可能なパラメータを指定する必要はありません。
  • Cloud Bigtable アプリケーション プロファイル ID を default に設定します。
  • BigQuery データセットを bigtable_bigquery_tutorial に設定します。
  1. [ジョブを実行] をクリックします。
  2. ジョブ ステータスが「開始中」または「実行中」になるまで待ってから、次に進みます。ジョブがキューに追加されてから 5 分ほどかかります。ページが自動的に更新されます。このテンプレートではストリーミング ジョブが作成されるため、ジョブを手動で停止するまで、Bigtable に書き込まれた新しいデータを継続的に処理できます。

a04908b37c6fe96b.png

6. Bigtable にデータを書き込む

次に、1 年間のクレジット カード取引を Bigtable テーブルに書き込みます。このデータセットの例には、クレジット カード番号、販売者名と ID、金額などの情報が含まれています。実際のクレジット カード処理アプリケーションでは、トランザクションが発生するたびに、このデータがリアルタイムでデータベースにストリーミングされます。

  1. Cloud コンソールの右上にあるボタンをクリックして、Cloud Shell を開きます。

f6395329b04ecb64.png

  1. プロンプトが表示されたら、プロンプトを承諾して Cloud Shell を承認します。
  2. データセットをダウンロードします。
gsutil cp gs://cloud-bigtable-public-datasets/change-streams-tutorial/cc-transactions-2023.csv .
  1. コマンドラインで環境変数を設定する
PROJECT_ID=your-project-id
BIGTABLE_INSTANCE_ID=your-bigtable-instance-id
  1. cbt CLI を使用して、クレジット カード トランザクションの数を retail-database テーブルに書き込みます。
cbt -instance=$BIGTABLE_INSTANCE_ID -project=$PROJECT_ID import \
retail-database cc-transactions-2023.csv  column-family=transactions

出力は次のようになります。

Done importing 10000 rows.

7. BigQuery で変更ログを表示する

  1. Google Cloud コンソールで [BigQuery] ページに移動します。
  2. [エクスプローラ] ペインで、プロジェクトとデータセット bigtable_bigquery_tutorial を開きます。
  3. テーブル retail-database_changelog をクリックします。テーブルが存在しない場合は、データセットを更新する必要があります。
  4. 変更ログを表示するには、[プレビュー] をクリックします。

aa97ff01f944832.png

クエリを試す

これで、このデータセットに対していくつかのクエリを実行して、分析情報を取得できます。SQL クエリはここで提供されていますが、BigQuery で Bigtable 変更ログをクエリするドキュメントでは、変更ログデータのクエリを作成する方法について詳しく説明しています。

1 つの取引を検索する

特定のトランザクションのデータを検索するには、次のクエリを使用します。

SELECT *
FROM `bigtable_bigquery_tutorial.retail-database_changelog`
WHERE row_key="3034-442694-3052#2023-03-03T14:50:46.824Z"

書き込まれた各列が BigQuery で個別の行に変換されていることがわかります。

各カテゴリの購入数を確認する

次のクエリを使用して、カテゴリごとの購入数をカウントします。

SELECT value as category, count(*) as `number of sales`
FROM `bigtable_bigquery_tutorial.retail-database_changelog`
WHERE column="category"
GROUP BY category

データを再フォーマットする

各トランザクションを 1 つの BigQuery テーブル行に再構築するには、データをピボットしてその結果を新しいテーブルに保存します。これは、クエリを実行しやすい形式です。

CREATE VIEW bigtable_bigquery_tutorial.retail_transactions_view AS (
    SELECT *, parse_numeric(amount) as sales_dollars FROM (
      SELECT row_key, timestamp, column, value
      FROM `bigtable_bigquery_tutorial.retail-database_changelog`
      )
      PIVOT (
      MAX(value)
      FOR column in ("merchant", "amount", "category", "transaction_date")
    )
)

Looker ダッシュボードを作成する

  1. [ビューに移動] をクリックして、新しいビューの詳細ページに移動します。
  2. [エクスポート] をクリックします。
  3. [Looker Studio で調べる] を選択します。

bb45482e9101b0f.png

8. ダッシュボードにグラフを追加する

情報をグラフ化して、簡単に把握し、レポートとして共有できるようになりました。ダッシュボードに次の 3 つのグラフを追加します。

  • 取引金額の推移
  • 販売者ごとの合計取引数
  • カテゴリごとのトランザクションの割合

426dc59200875a20.png

ページを設定する

  1. 既存のグラフをそれぞれクリックして、削除アイコンをクリックして削除します。
  2. ページの右側にある [プロパティ] を選択して、グラフデータを変更します。

グラフを追加する

取引金額の推移

  1. [グラフを追加] をクリックして、時系列グラフを作成します。
  2. [ディメンション] を transaction_date に設定します。
  1. [Metric] を sales_dollars に設定します。

372bdf2a2bcdb817.png

販売者ごとの合計トランザクション数

  1. [グラフを追加] をクリックして表を作成します。
  2. [ディメンション] を merchant に設定します。
  3. [指標] を sales_dollars に設定します。

カテゴリごとのトランザクションの割合

  1. [グラフを追加] をクリックして、円グラフを作成します。
  2. [ディメンション] を category に設定します。
  3. [指標] を sales_dollars に設定します。

リアルタイムの変更を確認する

グラフの値をじっくり確認します。表内の特定の販売者または特定のカテゴリをクリックすると、すべてのグラフでその特定の値がフィルタされ、より詳細な分析情報を確認できます。次に、データを追加して、このグラフがリアルタイムでどのように更新されるかを確認します。

  1. Cloud Shell に戻ります。
  2. 2 つ目のデータセットをダウンロードして書き込みます。
gsutil cp gs://cloud-bigtable-public-datasets/change-streams-tutorial/cc-transactions-2024-jan.csv .

cbt -instance=$BIGTABLE_INSTANCE_ID -project=$PROJECT_ID import \
retail-database cc-transactions-2024-jan.csv  column-family=transactions
  1. Looker ダッシュボードに戻り、Ctrl+Shift+E コマンドでデータを更新するか、[ビュー] メニューの [データの更新] をクリックします。これで、2024 年 1 月の情報がグラフに表示されます。

作成できるグラフや指標には、この他にもさまざまな種類があります。詳細については、Looker のドキュメントをご覧ください。

9. クリーンアップ

このチュートリアルで使用したリソースについて、Google Cloud アカウントに課金されないようにするには、リソースを含むプロジェクトを削除するか、プロジェクトを維持して個々のリソースを削除します。

変更ストリーム パイプラインを停止する

  1. Google Cloud コンソールで、Dataflow Jobs ページに移動します。
  2. ジョブリストからストリーミング ジョブを選択します。
  3. ナビゲーションで、[停止] をクリックします。
  4. [ジョブの停止] ダイアログで [キャンセル] を選択し、[ジョブの停止] をクリックします。

Bigtable リソースを削除する

このチュートリアル用に Bigtable インスタンスを作成した場合は、そのインスタンスを削除するか、作成したテーブルをクリーンアップできます。

  1. Google Cloud コンソールで、Bigtable インスタンス ページに移動します。
  2. このチュートリアルで使用しているインスタンスの ID をクリックします。
  3. 左側のナビゲーション パネルで [テーブル] をクリックします。
  4. retail-database テーブルを見つけます。
  5. [編集] をクリックします。
  6. [変更ストリームを有効にする] をオフにします。
  7. [保存] をクリックします。
  8. テーブルのオーバーフロー メニューを開きます。
  9. [削除] をクリックし、テーブル名を入力して確定します。
  10. 省略可: このチュートリアル用に新しいインスタンスを作成した場合は、インスタンスを削除します。

BigQuery データセットの削除

  1. Google Cloud コンソールで [BigQuery] ページに移動します。
  2. [エクスプローラ] パネルで、データセット bigtable_bigquery_tutorial を見つけてクリックします。
  3. [削除] をクリックして「delete」と入力し、[削除] をクリックして確定します。

次のステップ