About analytics views

This document describes analytics views and when you might want to create them. It also describes the differences between analytics views and saved queries.

Overview

An analytics view is a resource you can query. That is, after you define an analytics view, you can write queries where you specify your analytics view in the FROM clause.

The primary benefit of using analytics views is that you aren't constrained to querying the data in the format an API or system defines. An analytics view lets you define a schema, which lets you simplify your queries or reduce the time you spend writing queries. The query in an analytics view can do things like retain only specific fields, rename fields, move nested fields to a top-level column, and perform grouping and aggregation.

Analytics views aren't materialized views. An analytics view isn't a precomputed view that periodically caches query results. Therefore, querying an analytics view is equivalent to querying the views that are listed in the analytics view's query.

Analytics views are more powerful than saved queries, which are queries that you run on the Log Analytics page and then save. You can rerun a saved query, but you can't query the results of a saved query.

Types of analytics views

There are two types of analytics views: user-defined and system-defined:

User-defined analytics views are any analytics views that you create. You can query, edit, and delete user-defined analytics views.
System-defined analytics views are analytics views created by Google Cloud services. You can query system-defined analytics views. However, you can't edit or delete them.

For information about listing the analytics views in your Google Cloud project, see List analytics views.

Location of analytics views

The location of an analytics view is determined by the location of the resources it queries. For example, if an analytics view queries a log view or observability view that is in the global location, then the location of the analytics view must also be global. When you use the Google Cloud console to create an analytics view, the location is set automatically.

Example use cases

The primary benefit of an analytics view is that you aren't constrained to querying the data in the format the system defines. An analytics view lets you define a schema, which lets you simplify your queries or reduce the time you spend writing queries.

Query for network details

You want to analyze network performance by using VPC Flow Logs. You need to analyze the overall networking performance and as well as identify specific networks, IP addresses, and hosts. This information is stored within nested fields in the json_payload field of a log entry.

To simplify your analysis queries, you create the following analytics view and name it network_details:

SELECT
  -- Create a column for each network field.
  JSON_VALUE(resource.labels.subnetwork_name) subnetwork_name,
  JSON_VALUE(json_payload.src_instance.vm_name) vm_name,
  JSON_VALUE(json_payload.connection.src_ip) as src_ip,
  JSON_VALUE(json_payload.connection.src_port) as src_port,
  JSON_VALUE(json_payload.connection.dest_ip) as dest_ip,
  JSON_VALUE(json_payload.connection.dest_port) as dest_port,
  CAST(JSON_VALUE(json_payload.bytes_sent) as INT64) as bytes_sent,
  CAST(JSON_VALUE(json_payload.packets_sent) as INT64) as packets_sent
FROM
  -- Query one log view.
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
WHERE
  -- Only query the VPC Flow Logs log.
  log_id = "compute.googleapis.com/vpc_flows"
  AND SEARCH(json_payload.reporter, "SRC")

Before you use the query, set values of the following variables:

PROJECT_ID: The identifier of the project.
LOCATION: The location of the log view.
BUCKET_ID: The name or ID of the log bucket.
LOG_VIEW_ID: The identifier of the log view, which is limited to 100 characters and can include only letters, digits, underscores, and hyphens.

When you want to analyze the network performance, you query your analytics view. For example, if you are only interested in the name of the instance and the amount of data sent, then you might write the following query:

SELECT vm_name, bytes_sent, packets_sent,
FROM `analytics_view.my_project.global.network_details`
ORDER BY bytes_sent DESC
LIMIT 100

Base query for API latency analysis

Several teams need weekly performance data to complete their analysis. Instead of each team generating the weekly data, you create an analytics view that extracts the weekly data. Other teams can query the analytics view and then perform their analysis.

The following query illustrates how to use SQL to compute the minimum, maximum, and average request latency:

SELECT week, MIN(took_ms) as min, MAX(took_ms) AS max, AVG(took_ms) AS avg
FROM (
  SELECT TIMESTAMP_TRUNC(timestamp, WEEK) AS week,
  CAST( JSON_VALUE(json_payload, '$."http.resp.took_ms"') AS INT64) as took_ms
  FROM
    -- Query one log view.
    `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
  WHERE json_payload IS NOT NULL
    AND SEARCH(labels,"frontend")
    AND JSON_VALUE(json_payload.message) = "request complete"
  ORDER BY took_ms DESC, timestamp ASC
)
GROUP BY week ORDER BY week

Extract trace service name

Suppose that you want to write queries that analyze your trace data, and you either want to display or group by a synthesized service name. The query for your analytics view determines the service name for a span by using the COALESCE function, which evaluates a list of expressions, in order, and returns the value for the first expression that doesn't evaluate to NULL:

SELECT
  start_time,
  -- Set the value of service name based on the first non-null value in the list.
  COALESCE(
    JSON_VALUE(resource.attributes, '$."service.name"'),
    JSON_VALUE(attributes, '$."service.name"'),
    JSON_VALUE(attributes, '$."g.co/gae/app/module"')) AS service_name,
  name AS span_name,
  duration_nano,
  status.code AS status,
  trace_id,
  span_id
FROM
  `PROJECT_ID.us._Trace.Spans._AllSpans`
LIMIT 10

Before you use the query, replace PROJECT_ID with the ID of your Google Cloud project.

Join trace and log data by using the trace ID

When you analyze an application, you might want to correlate your log and trace data. One option is to create an analytics view that performs the join and reports select fields.

The following query joins log and trace data by using the span and trace IDs:

SELECT
  T.trace_id,
  T.span_id,
  T.name,
  T.start_time,
  T.duration_nano,
  L.log_name,
  L.severity,
  L.json_payload,
  L.text_payload,
  L.proto_payload,
  L.insert_id
FROM
  `PROJECT_ID.us._Trace.Spans._AllSpans` AS T
JOIN
  `PROJECT_ID.us.BUCKET_ID.LOG_VIEW_ID` AS L
ON
  -- Join log and trace data by both the span ID and trace ID.
  -- Don't join only on span ID, this field isn't globally unique.
  T.span_id = L.span_id
  -- A regular expression is required because the storage format of the trace ID
  -- differs between a log view and a trace view.
  AND T.trace_id = REGEXP_EXTRACT(L.trace, r'/([^/]+)$')