BigQuery Schema of Exported Logs

BigQuery table schemas for exported logs are based on the structure of the LogEntry type and the contents of the log payloads. Stackdriver Logging also applies some special rules to shorten BigQuery schema field names for audit logs. You can see the table schema by selecting a table with exported log entries in the BigQuery Web UI.

Field naming conventions

There are a few naming conventions that apply to the log entry fields:

• For log entry fields that are part of the LogEntry type, the corresponding BigQuery field names are exactly the same as the log entry fields.

• For any user-supplied fields, letter case is normalized to the lower case but naming is otherwise preserved.

  • For fields in structured payloads, as long as the @type specifier is not present, letter case is normalized to the lower case but naming is otherwise preserved.

    For information on structured payloads where the @type specifier is present, see Payload fields with @type.

The following examples show how these naming conventions are applied :

Log entry field	LogEntry type mapping	BigQuery field name
insertId	insertId	insertId
textPayload	textPayload	textPayload
httpRequest.status	httpRequest.status	httpRequest.status
httpRequest.requestMethod.GET	httpRequest.requestMethod.[ABC]	httpRequest.requestMethod.get
resource.labels.moduleid	resource.labels.[ABC]	resource.labels.moduleid
jsonPayload.MESSAGE	jsonPayload.[ABC]	jsonPayload.message
jsonPayload.myField.mySubfield	jsonPayload.[ABC].[XYZ]	jsonPayload.myfield.mysubfield

The mapping of structured payload fields to BigQuery field names is more complicated when the structured field contains a @type specifier. This is discussed in the section below.

Payload fields with @type

This section discusses special BigQuery schema field names for log entries whose payloads contain type specifiers (@type fields). This includes exported audit log entries held in BigQuery. For example, this section explains why an audit log entry's protoPayload field might be mapped to the BigQuery schema field protopayload_auditlog.

At present, there are two schemas for audit log entries in BigQuery. By two schemas, we mean that certain audit log payload information is presently duplicated in two different BigQuery fields that contain the information in different formats: an older "extended" field name and a newer "compact" field name. On March 1, 2019, the older schema (field names) will be removed. For details, see the Migration to updated schema section below.

Schema naming rules

Payloads in log entries can contain structured data, and that structured data can have nested structured fields. Any structured field can include an optional type specifier in the following format:

@type: type.googleapis.com/[TYPE]

Structured fields that have type specifiers are customarily given BigQuery field names that have a [TYPE] appended to their field name.

For example, the following table shows the mapping of the top-level structured payload fields to BigQuery field names:

Payload	Payload @type	Payload field	BigQuery field name
jsonPayload	(none)	statusCode	jsonPayload.statusCode
jsonPayload	type.googleapis.com/abc.Xyz	statusCode	jsonPayload_abc_xyz.statuscode
protoPayload	(none)	statusCode	protoPayload.statuscode
protoPayload	type.googleapis.com/abc.Xyz	statusCode	protopayload_abc_xyz.statuscode

If jsonPayload or protoPayload contains other structured fields, then those inner fields are mapped as follows:

• If the nested structured field does not have a @type specifier, then its BigQuery field name is the same as the original field name, except it is normalized to lowercase letters.
• If the nested structured field does have a @type specifier, then its BigQuery field name has [TYPE] (respelled) appended to the field name and is normalized to lowercase letters.

There a few exceptions to the preceding rules for fields with type specifiers:

• In App Engine request logs, the payload's name in logs exported to BigQuery is protoPayload, even though the payload has a type specifier. You can see this in the example App Engine logs query in Queries.

• Stackdriver Logging applies some special rules to shorten BigQuery schema field names for audit logs. This is discussed in the Audit log schema fields section below.

• Audit logs that are exported to BigQuery are written in an updated compact format. Some payload information is temporarily duplicated in two different fields. For details, see Migration to updated schema.

Example

This example shows how structured payload fields are named and used when exported to BigQuery.

Assume that a log entry's payload is structured like the following:

jsonPayload: {
  name_a: {
    sub_a: "A value"
  }
  name_b: {
    @type: "type.googleapis.com/google.cloud.v1.SubType"
    sub_b: 22
  }
}

The mapping to BigQuery fields is as follows:

• The fields jsonPayload and name_a are structured, but they do not have @type specifiers. Their BigQuery names are jsonPayload and name_a, respectively.

• The fields sub_a and sub_b are not structured, so their BigQuery names are sub_a and sub_b, respectively.

• The field name_b has a @type specifier, whose [TYPE] is google.cloud.v1.SubType. Therefore, its BigQuery name is name_b_google_cloud_v1_subtype.

In summary, the following 5 BigQuery names are defined for the log entry's payload:

jsonPayload
jsonPayload.name_a
jsonPayload.name_a.sub_a
jsonPayload.name_b_google_cloud_v1_subtype
jsonPayload.name_b_google_cloud_v1_subtype.sub_b

Exported audit log schema fields

If you are not working with audit logs that have been exported to BigQuery, then you can skip this section.

Note: At present, there are two sets of fields for audit log entries in BigQuery. On March 1, 2019, the older "extended" fields will be removed in favor of the newer "compact" fields. If you do not export audit logs to BigQuery, you will not be affected by this change. Users exporting audit logs to BigQuery should examine the changed fields and must update queries that consume them. For more details, see Migration to updated schema.

The older ("extended") audit log audit log payload fields all have type specifiers (@type fields) in Stackdriver. Their corresponding BigQuery field names are specially constructed to avoid them being too long.

The newer ("compact") audit log payload fields, protoPayload.request, protoPayload.response, and protoPayload.metadata, have @type specifiers but are treated as JSON data. That is, their BigQuery schema names are their field names with Json appended to them, and they contain string data in JSON format.

The two sets of audit log payload field names are listed in the following table:

Log entry field	Extended (older) BigQuery field name	Compact (newer) BigQuery field name
protoPayload	protopayload_auditlog	Same
protopayload.metadata	protopayload_auditlog.metadata_*	protopayload_auditlog.metadataJson
protoPayload.serviceData	protopayload_auditlog.servicedata_v1_bigquery Example: protopayload_auditlog.servicedata_v1_bigquery.tableInsertRequest	Same
protoPayload.request	protopayload_auditlog.request_[Vn]_[PROTO_NAME] Example: protopayload_auditlog.request_v2_listlogsinksrequest	protopayload_auditlog.requestJson
protoPayload.response	protopayload_auditlog.response_[Vn]_[PROTO_NAME] Example: protopayload_auditlog.response_v2_listlogsinksresponse	protopayload_auditlog.responseJson

You need to revise your queries to use the newer compact schema (field names) before March 1, 2019, when the older extended schema (field names) will be removed.

Note the following details about audit log naming:

• The serviceData naming rule is specific to audit logs that are generated by BigQuery and that are then exported to BigQuery. Those audit log entries contain a serviceData field that has a @type specifier of:

  type.googleapis.com/google.cloud.bigquery.logging.v1.auditdata
  

• The naming rules for the extended (older) request and response fields assume that their type specifiers follow a common pattern:

  [GOOGLE_SERVICE].[Vn].[PROTO_NAME]
  

  For example, in a request field, the specifier could be:

  google.logging.v2.ListLogSinksRequest
  

  After replacing periods with underscores and changing to lower case, this results in the following customary field name:

  request_google_logging_v2_listlogsinksrequest
  

  The specially constructed schema field name is the following:

  request_v2_listlogsinksrequest
  

  If you encounter a type specifier with a different pattern, then look at some exported log entries to see how the exported field names are shortened.

Example

An audit log entry generated by BigQuery has a field with the following name:

protoPayload.serviceData.tableInsertRequest

If this log entry were then exported to BigQuery, how would the tableInsertRequest field be referenced? Before the name shortening, the corresponding exported field name would be:

protopayload_google_cloud_audit_auditlog.servicedata_google_cloud_bigquery_logging_v1_auditdata.tableInsertRequest

After the name shortening, the same field is referenced in BigQuery tables like this:

protopayload_auditlog.servicedata_v1_bigquery.tableInsertRequest

Migration to updated schema

At present, there are two sets of field names, "extended" and "compact", for audit log entries in BigQuery. On March 1, 2019, the extended sets of field names will be removed. Review your queries and determine whether you need to migrate to the new set of field names:

• If you do not use presently use the request, response, or requestMetadata fields from exported audit logs in any BigQuery queries, then this migration does not affect you. Skip this section.

• If you start using the request, response, or requestMetadata fields for the first time, use the compact (newer) names from the beginning. All audit logs produced for new services will only use the new compact format. Skip this section.

• If you do use the request, response, or requestMetadata fields from exported audit logs in any BigQuery queries, and are using the extended (older) field names, switch to the compact (newer) names as soon as possible. Read this section and see Updating your queries for an example of how to update your queries.

This change affects both Admin Activity and Data Access audit logs, transforming them from the current, extended format, with an arbitrary number of fields, to an updated, compact JSON format. For existing audit logs, Stackdriver Logging already writes the new compact format:

You can see both formats in BigQuery until March 1, 2019, when the extended format will be removed.

Exports of non-audit logs are not affected. Audit log exports to locations other than BigQuery are not affected.

Updating your queries

The table in Audit log schema fields lists the fields that are affected by the change from the extended field name to the compact field name. If you query any of these fields, you need to review your BigQuery queries and revise them as necessary before March 1, 2019, when the older fields will be removed.

To update your queries, you need to use the BigQuery JSON_EXTRACT() method. The @type specifier in those fields should be treated as a description of the content of the JSON data.

As an example, here is how a query selecting one of the createSinkRequest fields might look with the extended field names:

 SELECT protopayload_auditlog.request_v2_createsinkrequest.sink.destination
 as dest FROM ...

After the change to the compact field names, the syntax will look like this:

SELECT JSON_EXTRACT(protopayload_auditlog.requestJson, '$.sink.destination')
as dest FROM ...

Viewing your audit logs

For existing audit logs, Stackdriver Logging is writing both the newer compact format and the older extended format. You can see both formats in BigQuery until March 1, 2019, when the extended format will be removed.

To see your audit logs, select a table with exported log entries in the BigQuery Web UI.