Import Historical Cloud Logging Logs into BigQuery to Visualize Deployment Counts

At Zenn, we use the number of application deployments as one reference metric for measuring team health.

https://cloud.google.com/blog/ja/products/gcp/using-the-four-keys-to-measure-your-devops-performance

Zenn's applications run on Google Cloud. To visualize deployment counts, it's generally sufficient to set up a log sink that streams Cloud Run events from Cloud Logging to BigQuery. Then you can create charts in Looker Studio, for example.

Challenge: Historical logs cannot be backfilled into BigQuery

However, log sinks only send data from the point of configuration onward; they cannot retroactively send past data. Since we also wanted to aggregate historical deployment counts, we needed a solution. This challenge is also mentioned in the following article:

https://tech.pepabo.com/2024/03/27/glouc-logging-copy/

In summary, while it was a bit involved, we took steps to migrate historical deployment logs to BigQuery and visualize the deployment count. Here is the result visualized in Looker Studio:

How We Did It

1. Configure a log sink
2. Salvage historical logs from Cloud Storage (this takes several hours)
3. Import the generated Cloud Storage files into BigQuery
4. Merge the imported data with the data from the log sink
5. Visualize

First, Set Up a Log Sink - Using Admin Activity Logs

While the main topic here is about historical logs, the first step is to configure a log sink so that data accumulates in a BigQuery table. Otherwise, more and more data will become "historical." There are several ways to identify Cloud Run deployment logs, but we can fine-tune conditions later in BigQuery. I used the following log filter:

logName="projects/your-project-name/logs/cloudaudit.googleapis.com%2Factivity"
protoPayload.serviceName="run.googleapis.com"
protoPayload.@type="type.googleapis.com/google.cloud.audit.AuditLog"

logName="projects/your-project-name/logs/cloudaudit.googleapis.com%2Factivity" is the Admin Activity audit log. It records administrative operations on Google Cloud resources (e.g., IAM configuration changes, enabling/disabling APIs/services). We'll use this to capture Cloud Run deployment operations as well.

protoPayload.serviceName="run.googleapis.com" narrows down the Admin Activity audit logs to Cloud Run only.

protoPayload.@type="type.googleapis.com/google.cloud.audit.AuditLog" is meant to restrict to Cloud Run audit logs, but this is redundant. I discovered this while writing this blog post. It has no negative impact, so I'll proceed with it as is.

Using this filter, set up a log sink to any BigQuery dataset.

Verify Data is Populated in BigQuery

After setting up the log sink, check BigQuery. You should see a table named cloudaudit_googleapis_com_activity with data flowing in.

Narrow down deployment activities

What logs are considered a deployment may vary by team, but I decided to consider a log that invokes ReplaceService as one deployment. ReplaceService is recorded when a Cloud Run service is updated. Try running the following query in BigQuery:

SELECT
  *
FROM
  `your-project-name.dataset.cloudaudit_googleapis_com_activity`
WHERE
  protoPayload_auditlog.serviceName = 'run.googleapis.com'
  AND protoPayload_auditlog.methodName = 'google.cloud.run.v1.Services.ReplaceService'
  AND protoPayload_auditlog.resourceName IN ( 'namespaces/your-project-name/services/backend-user-api',
    'namespaces/your-project-name/services/frontend' )
  AND protoPayload_auditlog.authenticationInfo.principalEmail = '[e-mail]'
ORDER BY
  timestamp DESC;

principalEmail limits the logs to those from deployments by Cloud Build. If the results roughly match the timing when you think a deployment occurred, it's all good. Once you've narrowed it down this much in BigQuery, visualization is practically complete, so let's move on to the method for integrating historical logs.

Salvage historical logs from Cloud Storage

Now that the log sink is configured, we can measure deployment counts going forward. The issue is the past data. Fortunately, the Admin Activity audit logs previously obtained from Cloud Logging are automatically retained by Google Cloud for 400 days as audit logs. They remain in the Cloud Logging log buckets.

https://cloud.google.com/logging/quotas?hl=ja#logs_retention_periods

The strategy was to extract only the necessary audit logs from the log buckets and import them into BigQuery.

gcloud logging read

When you create a Google Cloud project, _Required and _Default log buckets are automatically generated, and generated logs are automatically distributed and stored.

https://docs.cloud.google.com/logging/docs/buckets?hl=ja

First, let's verify that the desired logs actually exist using the gcloud logging read command. Cloud Run Admin Activity audit logs have protoPayload.serviceName="run.googleapis.com". Since they seem to be in the _Required log bucket, filter by this first and confirm that results are returned. Be sure to specify the time period as well.

gcloud logging read 'protoPayload.serviceName="run.googleapis.com" AND timestamp>="2026-01-17T15:00:00Z" AND timestamp<="2026-01-25T14:59:59Z"' \
  --bucket=_Required \
  --location=global \
  --view=_AllLogs \
  --limit=10
    
resource:
  labels:
    configuration_name: ''
    location: asia-northeast1
    project_id: your-project-name
    revision_name: ''
    service_name: backend-api
  type: cloud_run_revision
severity: NOTICE
timestamp: '2026-01-23T04:41:56.929406Z'

If the result is not empty and returns something, it's good. Since it appears that historical logs are in _Required, let's next consider how to extract them from here.

gcloud logging copy

To address the use case of extracting and migrating historical logs, the gcloud logging copy command is available.

https://docs.cloud.google.com/logging/docs/routing/copy-logs?hl=ja

Referring to samples and other resources, I composed the following command:

gcloud logging copy _Required \
storage.googleapis.com/cloudrun-audit-logs-temp \
--location=global \
--log-filter='protoPayload.serviceName="run.googleapis.com"'

• _Required: Specifies the source bucket ID. Since we found the logs in _Required, we specify that.
• storage.googleapis.com/cloudrun-audit-logs-temp: Specifies the destination Cloud Storage bucket. Historical logs will be copied here.
• --location=global: Specifies the location of the source log bucket. _Required is a global location, so we specify global.
• --log-filter: Specifies the filter to narrow down the logs to copy. Here, we limit it to Cloud Run audit logs.

I decided to broadly copy "Cloud Run audit logs" and then extract from the destination as needed. Executing this starts a long-running operation. You can check the running operation as follows:

gcloud logging operations list --location=global \
--operation-filter=request_type=CopyLogEntries \
--project=your-project-name

---
metadata:
  '@type': type.googleapis.com/google.logging.v2.CopyLogEntriesMetadata
  destination: storage.googleapis.com/cloudrun-audit-logs-temp
  progress: 1
  source: projects/your-project-name/locations/global/buckets/_Required
  startTime: '2025-09-10T14:39:00.229469Z'
  state: OPERATION_STATE_RUNNING
  verb: copy
name: projects/your-project-name/locations/global/operations/CAESFC01NDA0ODYwNTIxMzM1MjM5MjA5

This shows that copying to cloudrun-audit-logs-temp is in progress. It takes several hours, so be patient. Upon completion, the logs are copied to the specified target bucket with the filter applied.

Logs copied to the bucket

List of copied files

The logs seem to have been copied successfully. They are copied as JSON files. Each log entry was a single JSON object (newline delimited).

The logs seem to have been copied successfully. They are copied as JSON files, with each log entry being a single JSON object (newline delimited).

Import the generated Cloud Storage files into BigQuery

From here, it's practically a sure thing. We just need to import the log files from Cloud Storage into BigQuery.

https://docs.cloud.google.com/bigquery/docs/loading-data-cloud-storage-json?hl=ja#loading_json_data_into_a_new_table

The information about bq load is scarce, but let's try it first. Since bq load seems to automatically create the destination BigQuery table, there's no need to create one in advance. Defining the schema would also be cumbersome, so we'll use --autodetect. Execute the import as follows:

bq load \
--project_id=your-project-name \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
--ignore_unknown_values \
dataset.cloudrun-audit-logs-temp \
gs://cloudrun-audit-logs-temp/cloudaudit.googleapis.com/*.json 

...↓実行結果↓（エラー）...
Waiting on bqjob_r205defbd78bbf710_0000019bf5acd3ae_1 ... (1s) Current status: DONE   
Error in query string: Error processing job 'your-project-name:bqjob_r205defbd78bbf710_0000019bf5acd3ae_1': Invalid field name
"run.googleapis.com/execution_name". Fields must contain the allowed characters, and be at most 300 characters long. For allowed
characters, please refer to https://cloud.google.com/bigquery/docs/schemas#column_names Table: cloudrun-audit-logs-
temp_d7452328_6073_46d5_9f82_737fb8b02f3e_source

The result is an error. It seems that when trying to save the activity log to BigQuery, key names like run.googleapis.com/execution_name are not valid BigQuery key names. Things are starting to look ominous. Simply filtering the _Required bucket data and importing it into BigQuery seems to have keys that cannot be ingested as schema names. I must retract my statement about it being a sure thing.

What about the schema of the BigQuery data fed by the log sink?

I was stuck, realizing that the historical logs couldn't be directly loaded into BigQuery with bq load as-is. But then I thought, "Wait, what's the format of the Cloud Logging data that's normally transferred via the log sink?" Coincidentally, bq load supports a method where you provide the BigQuery schema as JSON and load data according to it. So I adopted the following strategy:

1. Extract the schema information from the BigQuery table created by the existing log sink.
2. Use that schema information to import the historical logs into BigQuery.

To extract the schema from BigQuery, do the following:

# cloudaudit_googleapis_com_activity is the BigQuery table automatically created by the log sink
bq show --schema --format=prettyjson --project_id=your-project-name \
dataset.cloudaudit_googleapis_com_activity > activity_schema.json

---

less activity_schema.json

[
  {
    "mode": "NULLABLE",
    "name": "logName",
    "type": "STRING"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "type",
        "type": "STRING"
      },
...

Looks good. Now let's execute bq load again using this schema.

 bq load --project_id=your-project-name \
 --source_format=NEWLINE_DELIMITED_JSON \
 --schema=activity_schema.json \
 --ignore_unknown_values \
 dataset.cloudrun-audit-logs-temp gs://cloudrun-audit-logs-temp/cloudaudit.googleapis.com/activity/*.json

The copy was successful, but the following BigQuery query cannot extract data.

SELECT
  *
FROM
  `your-project-name.dataset.cloudrun-audit-logs-temp`
WHERE
  protoPayload_auditlog.serviceName = 'run.googleapis.com'
  AND protoPayload_auditlog.methodName = 'google.cloud.run.v1.Services.ReplaceService'
  AND protoPayload_auditlog.resourceName IN ( 'namespaces/your-project-name/services/backend-user-api',
    'namespaces/your-project-name/services/frontend' )
  AND protoPayload_auditlog.authenticationInfo.principalEmail = '[e-mail]'
ORDER BY
  timestamp DESC;

This is the same query as for the data imported via the log sink. The data source and schema should be the same, so why...

The key name protoPayload_auditlog that exists on the log sink side is missing

The schema activity_schema.json extracted from the log sink's BigQuery table contains a key named protoPayload_auditlog, which is used in the BigQuery query for measuring deployment frequency. However, this key does not exist in the files copied from _Required. Therefore, even after importing with the activity_schema.json schema, protoPayload_auditlog remains empty.

Where did this data go? Let's recall the filter used when copying from _Required.

gcloud logging copy _Required \
storage.googleapis.com/cloudrun-audit-logs-temp \
--location=global \
--log-filter='protoPayload.serviceName="run.googleapis.com"'

It's protoPayload, not protoPayload_auditlog. This means that protoPayload_auditlog is the result of the log sink arbitrarily modifying the key name when streaming data to BigQuery.

You might think "What the heck," but this specification is well-documented.

https://docs.cloud.google.com/logging/docs/export/bigquery?hl=ja#audit-logs

In short, when importing from the _Required copy into BigQuery, the key remains as protoPayload, so we just need to modify the schema to accommodate it. Copy activity_schema.json to create activity_schema_protopayload.json and adjust as follows:

activity_schema_protopayload.json

"mode": "NULLABLE",
-"name": "protopayload_auditlog",
+"name": "protoPayload",
"type": "RECORD"

Now, run bq load again.

 bq load --project_id=your-project-name \
 --source_format=NEWLINE_DELIMITED_JSON \
 --schema=activity_schema_protopayload.json \
 --ignore_unknown_values \
 dataset.cloudrun-audit-logs-temp gs://cloudrun-audit-logs-temp/cloudaudit.googleapis.com/activity/*.json

After ingestion, execute the query. Note that the field is protoPayload.serviceName, not protoPayload_auditlog.serviceName.

SELECT
  *
FROM
  `your-project-name.dataset.cloudrun-audit-logs-temp`
WHERE
  protoPayload.serviceName = 'run.googleapis.com'
  AND protoPayload.methodName = 'google.cloud.run.v1.Services.ReplaceService'
  AND protoPayload.resourceName IN ( 'namespaces/your-project-name/services/backend-user-api',
    'namespaces/your-project-name/services/frontend' )
  AND protoPayload.authenticationInfo.principalEmail = '[e-mail]'
ORDER BY
  timestamp DESC;

We were able to retrieve the Cloud Run ReplaceService event. We finally reached the point of obtaining results from BigQuery using historical logs. It feels like it took an incredibly long time...

Merge the imported data with the data set up by the log sink

We want to combine the ever-increasing data from the log sink with the historical data so that it can be easily graphed in Looker Studio. There are various approaches, but here's what I did:

1. Rename the protopayload column from the _Required historical data to protopayload_auditlog and create a separate table: cloudaudit_googleapis_com_activity_202408_202508
2. Create a view that UNIONs this with the log sink's table
3. Visualize the view in Looker Studio

This way, we aim for the final goal: measuring deployment frequency retroactively. There are many ways to integrate BigQuery data for visualization, so treat this as just one example.

Create a separate table

I considered directly INSERTing the historical data into the log sink's table, but since the log sink seems to be doing various things, I decided to keep it as a separate table. Also, to reduce cognitive load, I kept the protopayload column as protopayload_auditlog. I created the new table with the following query:

CREATE OR REPLACE TABLE `your-project-name.dataset.cloudaudit_googleapis_com_activity_202408_202508` AS
SELECT
  logName,
  resource,  -- resource field as-is
  protopayload AS protopayload_auditlog,  -- rename protopayload to protopayload_auditlog
  textPayload,
  timestamp,
  receiveTimestamp,
  severity,
  insertId,
  httpRequest,
  operation,
  trace,
  spanId,
  traceSampled,
  sourceLocation,
  split,
  errorGroups,
  apphub,
  apphubDestination
FROM `your-project-name.dataset.cloudrun-audit-logs-temp`;

No detailed conditions are specified here, but you can add them if needed (e.g., strictly limiting the date range). Execute to create the new table. The temporary table cloudrun-audit-logs-temp can be deleted afterward.

Integrate historical data and log sink data using a view

The historical data is in cloudaudit_googleapis_com_activity_202408_202508, and the log sink data is in the cloudaudit_googleapis_com_activity table. Creating a view that UNIONs both makes visualization easier. Refer to the following query:

-- Create a view
CREATE OR REPLACE VIEW `your-project-name.dataset.cloudaudit_activity_replace_service_view` AS
SELECT
  *
FROM (
  -- Data from the first table
  SELECT
    *
  FROM
    `your-project-name.dataset.cloudaudit_googleapis_com_activity_202408_202508`
  WHERE
    protoPayload_auditlog.serviceName = 'run.googleapis.com'
    AND protoPayload_auditlog.methodName = 'google.cloud.run.v1.Services.ReplaceService'
    AND protoPayload_auditlog.resourceName IN (
      'namespaces/your-project-name/services/backend',
      'namespaces/your-project-name/services/frontend'
    )
    AND protoPayload_auditlog.authenticationInfo.principalEmail = '[e-mail]'

  UNION ALL

  -- Data from the second table
  SELECT
    *
  FROM
    `your-project-name.dataset.cloudaudit_googleapis_com_activity`
  WHERE
    protoPayload_auditlog.serviceName = 'run.googleapis.com'
    AND protoPayload_auditlog.methodName = 'google.cloud.run.v1.Services.ReplaceService'
    AND protoPayload_auditlog.resourceName IN (
      'namespaces/your-project-name/services/backend',
      'namespaces/your-project-name/services/frontend'
    )
    AND protoPayload_auditlog.authenticationInfo.principalEmail =
'[e-mail]'
)
ORDER BY
  timestamp DESC;

At this point, we filter by the event name considered as a deployment, like protoPayload_auditlog.methodName = 'google.cloud.run.v1.Services.ReplaceService'. This means the record count equals the number of deployments. There may be a more elegant way to write this, but let's prioritize visualization for now.

Visualize in Looker Studio

Once the view is ready, we're almost at the visualization stage.

Looker Studio > Create report > Add data > BigQuery, then specify the view we just created and click "Add".

Then, "Add a chart". Since we want to see the deployment counts for both the backend and frontend, I chose a stacked bar chart.

• Dimension - X-axis: timestamp (datetime) Data type: Year-Month
• Breakdown dimension: resource.labels.service_name
• Metric - Y-axis: Record Count
• Period dimension: protopayload_auditlog.authorizationInfo.resourceAttributes.createTime, Data type: Date
• Sort: timestamp (datetime) ascending

This leads to the chart at the beginning of the article:

We've successfully displayed deployment counts going back to historical data.

Conclusion

Although we encountered quite a few hiccups along the way, this was a record of transferring log data stored in Google Cloud to Cloud Storage and BigQuery, and visualizing it together with data collected by the log sink. In particular, it took time to realize that the key name protoPayload_auditlog was modified by the log sink when saving to BigQuery. I'm confident that I will definitely forget this over time, so I've documented it as a memo. I hope this proves useful to someone.