くらげになりたい。

くらげのようにふわふわ生きたい日曜プログラマなブログ。趣味の備忘録です。

トップ > GCP > BigQueryでGA/Firestore/CloudRunアクセスログを分析する

BigQueryでGA/Firestore/CloudRunアクセスログを分析する

GCP BigQuery Firebase GoogleAnalytics

開発しているWebサービスでいろいろ分析するために、
BigQueryの設定をしたときの備忘録。

全部BigQueryに連動しておけば、SQLでいろいろ分析できるね(´ω｀)

構成としてはこんな感じ。

f:id:wannabe-jellyfish:20211011154440p:plain

GoogleAnalitics -> BigQuery

GoogleAnaliticsはFirebaseのものを利用しているので、
Firebaseコンソールの「プロジェクト設定 > 統合 > BigQuery」でOK。

f:id:wannabe-jellyfish:20211011154542p:plain

f:id:wannabe-jellyfish:20211011154756p:plain

BigQueryには日毎にテーブルが作成される。

f:id:wannabe-jellyfish:20211011154807p:plain

CloudRunアクセスログ -> BigQuery

Cloud Runのログは、Cloud Loggingに出力されている。
Cloud Loggingでシンクを作成するとBigQueryにデータを渡せるので、設定しておく。

f:id:wannabe-jellyfish:20211011154914p:plain

  • シンクの詳細
    • シンク名: (好きな名前) 例) cloud-run-to-bq
  • シンクの宛先
  • シンクに含めるログの選択
    • (条件を記載する)

GETリクエストのみの場合はこんな感じ。

logName="projects/<my-project>/logs/run.googleapis.com%2Frequests" AND
resource.labels.service_name="<my-service-name>" AND 
httpRequest.requestMethod="GET"

BigQueryには日毎にテーブルが作成される。

f:id:wannabe-jellyfish:20211011155212p:plain

Firestore -> BigQuery

GoogleAnaliticsやCloudRunアクセスログの内容と、
Firestoreの情報を突き合わせたい場合があるので、
Firestoreの中身もBigQueryに連携しておく。

FirestoreからBigQueryへの連携は、
Export Collections to BigQueryという拡張機能を使うと便利。

この拡張機能は1つのパスしか対応していないので、
複数のパスを対象にする場合は、パスごとに拡張機能をインストールする必要がある。

拡張機能のインストール

まずはインストール。
・Export Collections to BigQuery

設定するのは以下の項目。連携先のデータセットIDなどが必要なため、
先にBigQuery側のデータセットを作成しておく必要がある。

  • Cloud Functions location
    • Cloud Funcitonのロケーション
    • 例) lowa (us-central1)
  • BigQuery Dataset location
    • BigQueryのロケーション
    • 例) United States (multi-regional)
  • Collection path
    • 連携したいコレクションのパス
  • Detaset ID
  • Table ID:
    • 連携先のBigQueryのテーブルID
  • BigQuery SQL table partitioning option

インストールが完了して、設定したFirestoreのパスに変更があると、同期する感じ。

以下のテーブルができているので、そこでデータを確認できる

  • <table_id>_raw_changelog ... 変更のログ
  • <table_id>_raw_latest ... 最新の状態

設定前のデータをBigQueryにインポートする

この拡張機能は、変更が発生しないとBigQueryに送られないので、
設定前のデータをBigQueryにインポートする必要がある。

インポートするスクリプトが用意されているので、それを使ってインポートする。
・extensions/IMPORT_EXISTING_DOCUMENTS

# 認証
$ gcloud auth application-default login

# インポートスクリプトの実行
$ npx @firebaseextensions/fs-bq-import-collection
...
---------------------------------------------------------
Finished importing 299 Firestore rows to BigQuery
---------------------------------------------------------

インポートスクリプトの実行すると、設定などを聞かれるので答えていけばOK

  • What is your Firebase project ID?
    • プロジェクトIDを入力
  • What is the path of the the Cloud Firestore Collection you would like to import from?
    • Firestoreのパスを入力
  • Would you like to import documents via a Collection Group query?
    • パスがコレクショングループかどうか。
    • コレクショングループではないならNoを選択
  • What is the ID of the BigQuery dataset that you would like to use?
  • How many documents should the import stream into BigQuery at once?
    • 1度にインポートするドキュメント数を入力
  • Where would you like the BigQuery dataset to be located?
    • BigQueryのロケーションを入力
  • Would you like to run the import across multiple threads?
    • このスクリプトの実行をマルチスレッドで行うかどうか

FirestoreのJSONデータを分割して参照する

この拡張機能では、ドキュメントデータがdata項目にまるっと吐き出される。
JSONのままだといろいろめんどくさいので各項目に分割する。

これもスクリプトが用意されているので、実行すればOK。

・extensions/GENERATE_SCHEMA_VIEWS

Step 1: Create a schema file

まずは、どんなテーブルにするかのスキーマファイルを作成する。

スキーマファイルは、こんな感じ。
書き方については、How to configure schema filesを参照。

// test_schema.json
{
  "fields": [
    {
      "name": "name", // field name
      "type": "string" // value type
    },
    {
      "name": "age",
      "type": "number",
    }
  ]
}
Step 2: Set up credentials

次にgcloudの認証をする。実施済みなら不要。

# 認証
$ gcloud auth application-default login
Step 3: Run the script

最後にスクリプトの実行。
プロジェクトIDやBigQueryのデータセットIDなどを指定して実行する。

$ npx @firebaseextensions/fs-bq-schema-views \
  --non-interactive \
  --project=<PROJECT_ID> \
  --dataset=<BIGQUERY_DATASET_ID> \
  --table-name-prefix=<BIGQUERY_TABLE_ID> \
  --schema-files=<SCHEMA_FILE_PATH>

以下のテーブルができているので、そこでデータを確認できる

  • <BIGQUERY_TABLE_ID>_schema_<SCHEMA_FILE_BASE_NAME>_changelog ... 変更のログ
  • <BIGQUERY_TABLE_ID>_schema_<SCHEMA_FILE_BASE_NAME>_latest ... 最新の状態

BigQueryで分析する

分析に必要な情報は集まったので、あとはクエリを書いたり、
データポータルと連携すれば、いろいろ分析できるように。

ここからはクエリを書くときなどの小ネタ。

リクエストURLから一部を取得(REGEXP_EXTRACT)

リクエストURLから一部を取得する場合は、REGEXP_EXTRACTを使えばOK

・REGEXP_EXTRACT | 標準 SQL の文字列関数

SELECT 
  REGEXP_EXTRACT(httpRequest.requestUrl, r'https://.+?/([^/?]+.*$)') as path, 
FROM `my-project.server_request.run_googleapis_com_requests_*`

timestampをJSTに変換(STRING)

・STRING | 標準 SQL のタイムスタンプ関数

SELECT 
  STRING(timestamp, 'Asia/Tokyo') as jst,  
FROM `my-project.server_request.run_googleapis_com_requests_*`

前日のテーブルを対象にする

・_TABLE_SUFFIX を使用した特定のテーブル範囲のスキャン

SELECT 
  ...
FROM `my-project.server_request.run_googleapis_com_requests_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", CURRENT_DATE('Asia/Tokyo') - 1)

スケジュールされたクエリで日毎にテーブルを作成する

・パラメータ テンプレート構文 | 宛先テーブル

日本の場合、-15hとすると前日の日時を取得できる。

run_googleapis_com_requests_{run_time-15h|"%Y%m%d"}

SSSAPIではβ版ユーザを募集しています!!

GoogleスプレッドシートをサクッとAPI化するサービスを開発してます！
β期間中は最上位プランが無料なので、この機会にぜひぜひお試しください(´ω｀)

■SSSAPI
https://sssapi.app

f:id:wannabe-jellyfish:20210824124009p:plain

参考にしたサイト様