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 件の取引を検索する

次のクエリを使用して、特定の 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. [指標] を 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 の [ジョブ] ページに移動します。
  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」と入力し、[削除] をクリックして確定します。

次のステップ