BigQuery schema for 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 view 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, go to 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 following section.

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.

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.

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

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.

The 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	BigQuery field name
protoPayload	protopayload_auditlog
protopayload.metadata	protopayload_auditlog.metadataJson
protoPayload.serviceData	protopayload_auditlog.servicedata_v1_bigquery Example: protopayload_auditlog.servicedata_v1_bigquery.tableInsertRequest
protoPayload.request	protopayload_auditlog.requestJson
protoPayload.response	protopayload_auditlog.responseJson

Note that the serviceData naming convention is specific to audit logs that are generated by BigQuery and that are then exported from Stackdriver Logging 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.

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

Viewing your audit logs

To view your audit logs using the BigQuery web UI , select a table with your exported log entries.