Skip to main content

BigQuery Lens as data source

Overview​

The DoiT console provides a pre-built dashboard BigQuery Lens that sets up an audit log sink for all your BigQuery jobs. The audit logs record activities in BigQuery such as creating or deleting a table, purchasing slots, or running a load job.

You can use the BigQuery Lens audit log sink as the data source for your own analysis about BigQuery activities.

Required Permission
  • Cloud Analytics

Build reports using BigQuery Lens data​

To build a report using data from the BigQuery Lens audit log sink:

  1. Make sure you have already set up BigQuery Lens.

  2. Open a report. Select Change at the top of the left-hand sidebar.

    Update data source

  3. Select BigQuery Lens as the data source.

  4. You can choose to Create a new report or Update the current one. Note that updating the report will lose all its current configurations.

Metrics and dimensions​

Cloud Analytics reports use different sets of metrics and dimensions for billing data and BigQuery Lens data.

Metrics​

The basic metrics and extended metrics for BigQuery Lens data utilize fields of the jobStatistics object in BigQuery AuditData.

Basic metrics​

  • Cost: Processed bytes (totalBilledBytes) converted to TiB multiplied by the on-demand scan price.

  • Usage: Processed bytes (totalBilledBytes) converted to TiB, adjusted by the job's CPU usage.

Extended metrics​

  • Slots Used: The total number of slot-ms consumed divided by the query duration: totalSlotMs ÷ (endTime − startTime).

  • Total Slots Ms: The total number of slot-ms consumed by the query job. It maps to totalSlotMs.

  • Total Load Output Bytes: Total bytes loaded for an import job. It maps to totalLoadOutputBytes.

  • Total Tables Processed: Total number of unique tables referenced in the query. It maps to totalTablesProcessed.

  • Total Billed Bytes: Processed bytes, adjusted by the job's CPU usage. It maps to totalBilledBytes.

  • Total Processed Bytes: Total bytes processed for a job. It maps to totalProcessedBytes.

Dimensions​

Below are the available standard dimensions when the report's data source is BigQuery Lens:

  • Event name: Name of the event. It maps to jobCompletedEvent.eventName in BigQuery AuditData.

  • Job Status: State of a job: PENDING, RUNNING, or DONE. It maps to jobStatus.state in BigQuery AuditData.

  • Project/Account name: Human-readable Google Cloud project name of the completed job. It maps to jobName.projectId in BigQuery AuditData.

  • Query Priority: Priority given to the query: QUERY_INTERACTIVE or QUERY_BATCH. It maps to jobConfiguration.query.queryPriority in BigQuery AuditData.

  • Region: Location of the completed job. It maps to jobName.location in BigQuery AuditData.

  • Reservation: Reservation name or unreserved for on-demand resource usage. It maps to JobStatistics.reservation in BigQuery AuditData.

  • Resource: URI for the referenced resource. For example, a table created by using an insert job reports the resource URI of the table. It maps to protoPayload.resourceName in BigQueryAuditMetadata messages.

  • Statement Type: Type of the statement. Example: SELECT, INSERT, CREATE_TABLE, CREATE_MODEL. It maps to jobConfiguration.query.statementType in BigQuery AuditData.

  • Caller IP: IP address of the caller. It maps to requestMetadata.callerIp in audit logs.

  • User: The email address of the authenticated user (or service account on behalf of third party principal) making the request. It maps to authenticationInfo.principalEmail in audit logs.

Examples​

Usage by user​

Usage-by-user

Hourly heavy queries by job id​

Usage-by-user

See also​

Last updated on