notebook

都内でWEB系エンジニアやってます。

Cloud LoggingからログデータをBigQueryへExportする

GoogleCloudPlatform BigQuery CloudLogging Terraform

ログからBigQueryにExportできるのは知っていたのでとりあえず使ってみようかと思って触ってみたら思った以上に簡単だった

Terraformで設定を定義する

Terraformで表すとこんな感じ

  • main.tf
data "google_client_config" "current" {
}
  • iam.tf
locals {
  logging_sa_roles = [
    "roles/logging.logWriter",
    "roles/bigquery.dataEditor",
    "roles/bigquery.jobUser",
  ]
}
resource "google_project_iam_member" "logging_service_account_role" {
  count  = length(local.logging_sa_roles)
  role   = element(local.logging_sa_roles, count.index)
  member = google_logging_project_sink.sample_bq_sink.writer_identity
}
  • logging.tf
resource "google_logging_project_sink" "sample_bq_sink" {
  name                   = "to-bigquery"
  destination            = "bigquery.googleapis.com/projects/${data.google_client_config.current.project}/datasets/logging"
  filter                 = "severity >= INFO"
  unique_writer_identity = true
  bigquery_options {
    use_partitioned_tables = true
  }
}

主要な設定はこれだけ

今回datasetは手動でloggingという名前で作成した

filterに関しては今の設定だとプロジェクトによっては結構な量のログが貯まるはずなので適切なクエリを書く必要がある

クエリは次のスクショのようにCloud Loggingで書くクエリをかけばOK

f:id:swfz:20210907032941p:plain

また、オプションでテーブルパーティションの設定が可能なのでとりあえず設定してみた

unique_writer_identityをtrueにすると専用のサービスアカウントが発行される

で、発行されたサービスアカウントに対してCloud Loggingの書き込み権限とBigQueryへの権限を与えることでExportできるようになる

ちなみに、権限がないと次のようなエラーメールが来る(ぱっとみで結構びっくりしたw)

f:id:swfz:20210907032947p:plain

テーブル名について

ログ用の BigQuery スキーマ  |  Cloud Logging  |  Google Cloud

cloud.google.com

にあるように

Cloud Logging から受信するデータの BigQuery テーブル スキーマは、LogEntry タイプの構造とログエントリのペイロードの内容に基づいています

とのこと

なのでログの内容によってスキーマが変わる

スキーマが変わるので対象テーブルも変わる

ある程度分類された状態のテーブルが作成されるよう

いくつか見てみる

f:id:swfz:20210907032953p:plain

f:id:swfz:20210907032958p:plain

f:id:swfz:20210907033004p:plain

実際のログ

  • Workflows
 {
    "logName": "projects/project-111111/logs/Workflows",
    "resource": {
      "type": "workflows.googleapis.com/Workflow",
      "labels": {
        "workflow_id": "collect_social_metrics",
        "location": "us-central1",
        "resource_container": "467341840626"
      }
    },
    "textPayload": null,
    "jsonPayload": {
      "result": "{\"body\":{\"can_comment\":0,\"entries\":[{\"can_comment\":0,\"stars\":[],\"uri\":\"https://swfz.hatenablog.com/entry/2021/07/15/091520\"}]},\"code\":200,\"headers\":{\"Alt-Svc\":\"h3=\\\":443\\\"; ma=2592000,h3-29=\\\":443\\\"; ma=2592000,h3-T051=\\\":443\\\"; ma=2592000,h3-Q050=\\\":443\\\"; ma=2592000,h3-Q046=\\\":443\\\"; ma=2592000,h3-Q043=\\\":443\\\"; ma=2592000,quic=\\\":443\\\"; ma=2592000; v=\\\"46,43\\\"\",\"Cache-Control\":\"private\",\"Content-Length\":\"118\",\"Content-Type\":\"application/json; charset=utf-8\",\"Date\":\"Sat, 04 Sep 2021 22:02:57 GMT\",\"Etag\":\"W/\\\"76-KZDW/4am7jfTMu9Z9C4SBzkcaus\\\"\",\"Function-Execution-Id\":\"h4s9323zn8u6\",\"Server\":\"Google Frontend\",\"X-Cloud-Trace-Context\":\"6a1c530e61cd7efc839caa80aec3a537\",\"X-Powered-By\":\"Express\"}}",
      "endtime": "2021-09-04T22:02:57.456317462Z",
      "starttime": "2021-09-04T22:02:55.027218307Z",
      "workflowrevisionid": "000001-2b2",
      "argument": "{\"api_url\":\"https://s.hatena.com/entry.json?uri=\",\"function_url\":\"https://asia-northeast1-project-111111.cloudfunctions.net/collect-api-response\",\"media\":\"hatena_star\",\"target_url\":\"https://swfz.hatenablog.com/entry/2021/07/15/091520\"}",
      "name": "projects/467341840626/locations/us-central1/workflows/collect_api_response/executions/6c2e90c7-3705-4e23-b625-3a4020bc8a76",
      "state": "SUCCEEDED"
    },
    "timestamp": "2021-09-04 22:02:57.751926 UTC",
    "receiveTimestamp": "2021-09-04 22:02:57.751926 UTC",
    "severity": "INFO",
    "insertId": "170tnxyfhly1t2",
    "httpRequest": null,
    "labels": {
      "execution_id": "12818a8a-6917-4b57-aea9-92f30da0d6ba",
      "revision_id": "000020-ca8"
    },
    "operation": null,
    "trace": null,
    "spanId": null,
    "traceSampled": null,
    "sourceLocation": null
  },
  {
    "logName": "projects/project-111111/logs/Workflows",
    "resource": {
      "type": "workflows.googleapis.com/Workflow",
      "labels": {
        "workflow_id": "collect_social_metrics",
        "location": "us-central1",
        "resource_container": "467341840626"
      }
    },
    "textPayload": null,
    "jsonPayload": {
      "result": "{\"body\":{\"can_comment\":0,\"entries\":[{\"can_comment\":0,\"stars\":[{\"name\":\"user1\",\"quote\":\"\"}],\"uri\":\"https://swfz.hatenablog.com/entry/2021/05/12/094004\"}]},\"code\":200,\"headers\":{\"Alt-Svc\":\"h3=\\\":443\\\"; ma=2592000,h3-29=\\\":443\\\"; ma=2592000,h3-T051=\\\":443\\\"; ma=2592000,h3-Q050=\\\":443\\\"; ma=2592000,h3-Q046=\\\":443\\\"; ma=2592000,h3-Q043=\\\":443\\\"; ma=2592000,quic=\\\":443\\\"; ma=2592000; v=\\\"46,43\\\"\",\"Cache-Control\":\"private\",\"Content-Length\":\"152\",\"Content-Type\":\"application/json; charset=utf-8\",\"Date\":\"Sat, 04 Sep 2021 22:03:24 GMT\",\"Etag\":\"W/\\\"98-CJtaDenvbPebbCy3t58DEwzgKic\\\"\",\"Function-Execution-Id\":\"h4s9glurt512\",\"Server\":\"Google Frontend\",\"X-Cloud-Trace-Context\":\"f292bd5d842e4d94b908ae9443c85f6c\",\"X-Powered-By\":\"Express\"}}",
      "endtime": "2021-09-04T22:03:25.018470624Z",
      "starttime": "2021-09-04T22:03:22.706962189Z",
      "workflowrevisionid": "000001-2b2",
      "argument": "{\"api_url\":\"https://s.hatena.com/entry.json?uri=\",\"function_url\":\"https://asia-northeast1-project-111111.cloudfunctions.net/collect-api-response\",\"media\":\"hatena_star\",\"target_url\":\"https://swfz.hatenablog.com/entry/2021/05/12/094004\"}",
      "name": "projects/467341840626/locations/us-central1/workflows/collect_api_response/executions/9940d435-6400-4268-94bf-2518be0d7407",
      "state": "SUCCEEDED"
    },
    "timestamp": "2021-09-04 22:03:25.462748 UTC",
    "receiveTimestamp": "2021-09-04 22:03:25.462748 UTC",
    "severity": "INFO",
    "insertId": "ftidfkfiqtyix",
    "httpRequest": null,
    "labels": {
      "execution_id": "12818a8a-6917-4b57-aea9-92f30da0d6ba",
      "revision_id": "000020-ca8"
    },
    "operation": null,
    "trace": null,
    "spanId": null,
    "traceSampled": null,
    "sourceLocation": null
  },

Workflowsの中でsys.logを使ってログに出力した内容がログに残っている

  • workflows_googleapis_com_executions_system
  {
    "logName": "projects/memo-272607/logs/workflows.googleapis.com%2Fexecutions_system",
    "resource": {
      "type": "workflows.googleapis.com/Workflow",
      "labels": {
        "location": "us-central1",
        "workflow_id": "collect_api_response",
        "resource_container": "467341840626"
      }
    },
    "textPayload": null,
    "jsonpayload_type_executionssystemlog": {
      "state": "SUCCEEDED",
      "_type": "type.googleapis.com/google.cloud.workflows.type.ExecutionsSystemLog",
      "activitytime": "2021-09-02T21:05:23Z",
      "start": null,
      "success": {
        "result": "{\"body\":{\"can_comment\":0,\"entries\":[{\"can_comment\":0,\"stars\":[{\"name\":\"user1\",\"quote\":\"\"},{\"name\":\"user1\",\"quote\":\"\"}],\"uri\":\"https://swfz.hatenablog.com/entry/2021/04/21/201050\"}]},\"code\":200,\"headers\":{\"Alt-Svc\":\"h3=\\\":443\\\"; ma=2592000,h3-29=\\\":443\\\"; ma=2592000,h3-T051=\\\":443\\\"; ma=2592000,h3-Q050=\\\":443\\\"; ma=2592000,h3-Q046=\\\":443\\\"; ma=2592000,h3-Q043=\\\":443\\\"; ma=2592000,quic=\\\":443\\\"; ma=2592000; v=\\\"46,43\\\"\",\"Cache-Control\":\"private\",\"Content-Length\":\"247\",\"Content-Type\":\"application/json; charset=utf-8\",\"Date\":\"Thu, 02 Sep 2021 21:05:23 GMT\",\"Etag\":\"W/\\\"f7-Mq964aqPdlx1MYn0MavCvmmXuXY\\\"\",\"Function-Execution-Id\":\"2aa13rumbwcc\",\"Server\":\"Google Frontend\",\"X-Cloud-Trace-Context\":\"ddbee2b120593aa47774d98755d00529\",\"X-Powered-By\":\"Express\"}}"
      }
    },
    "timestamp": "2021-09-02 21:05:23.248009 UTC",
    "receiveTimestamp": "2021-09-02 21:05:23.728262 UTC",
    "severity": "INFO",
    "insertId": "m76omkbdl",
    "httpRequest": null,
    "labels": {
      "workflows_googleapis_com_execution_id": "d83b57a9-71c3-4945-aa5f-29182dde8a41",
      "workflows_googleapis_com_revision_id": "000001-2b2"
    },
    "operation": null,
    "trace": null,
    "spanId": null,
    "traceSampled": null,
    "sourceLocation": null
  },
  {
    "logName": "projects/project-111111/logs/workflows.googleapis.com%2Fexecutions_system",
    "resource": {
      "type": "workflows.googleapis.com/Workflow",
      "labels": {
        "location": "us-central1",
        "workflow_id": "collect_api_response",
        "resource_container": "467341840626"
      }
    },
    "textPayload": null,
    "jsonpayload_type_executionssystemlog": {
      "state": "SUCCEEDED",
      "_type": "type.googleapis.com/google.cloud.workflows.type.ExecutionsSystemLog",
      "activitytime": "2021-09-04T13:04:23Z",
      "start": null,
      "success": {
        "result": "{\"body\":{\"can_comment\":0,\"entries\":[{\"can_comment\":0,\"stars\":[{\"name\":\"user2\",\"quote\":\"\"},{\"name\":\"user3\",\"quote\":\"\"}],\"uri\":\"https://swfz.hatenablog.com/entry/2019/03/03/233624\"}]},\"code\":200,\"headers\":{\"Alt-Svc\":\"h3=\\\":443\\\"; ma=2592000,h3-29=\\\":443\\\"; ma=2592000,h3-T051=\\\":443\\\"; ma=2592000,h3-Q050=\\\":443\\\"; ma=2592000,h3-Q046=\\\":443\\\"; ma=2592000,h3-Q043=\\\":443\\\"; ma=2592000,quic=\\\":443\\\"; ma=2592000; v=\\\"46,43\\\"\",\"Cache-Control\":\"private\",\"Content-Length\":\"548\",\"Content-Type\":\"application/json; charset=utf-8\",\"Date\":\"Sat, 04 Sep 2021 13:04:22 GMT\",\"Etag\":\"W/\\\"224-J5ZVB4QAp8AiZVHU21k2PLzLtO8\\\"\",\"Function-Execution-Id\":\"9jsu1z3z8qtr\",\"Server\":\"Google Frontend\",\"X-Cloud-Trace-Context\":\"98b1d1f055a7157a4825f8397210c9cc\",\"X-Powered-By\":\"Express\"}}"
      }
    },
    "timestamp": "2021-09-04 13:04:23.045602 UTC",
    "receiveTimestamp": "2021-09-04 13:04:23.522617 UTC",
    "severity": "INFO",
    "insertId": "1t7mn4uc11n",
    "httpRequest": null,
    "labels": {
      "workflows_googleapis_com_execution_id": "d3570c39-be29-4901-ab8a-e91b101793bd",
      "workflows_googleapis_com_revision_id": "000001-2b2"
    },
    "operation": null,
    "trace": null,
    "spanId": null,
    "traceSampled": null,
    "sourceLocation": null
  },

こちらはWorkflowsの実行単位のログのように見える

どちらもWorkflowsではてなのAPIをたたいてその結果をログに残す、Workflowsの結果として返すよう実装しているためこのようなログになっている

まとめ

こんな簡単にExportできるのならとりあえず入れておいても良いくらいなのでは?と思った

実行結果やエラー内容など後で調べるとか集計するなど用途はいくらでもあるはずなので

今回試したリポジトリとファイルは下記に置いた

terraform-sample/google/logging-to-bigquery at master · swfz/terraform-sample

github.com