Sample SQL queries for Trace

You can use sample SQL queries in Observability Analytics to analyze your Cloud Trace data, identify latency outliers, and calculate span performance percentiles across your services.

These examples demonstrate how to filter, group, and aggregate spans stored in the _AllSpans view. If you haven't written queries in Observability Analytics, then first see Query and analyze traces.

SQL language support

Queries used in the Observability Analytics page support GoogleSQL functions with some exceptions.

The following SQL commands aren't supported for SQL queries issued by using the Observability Analytics page:

DDL and DML commands
Javascript user-defined functions
BigQuery ML functions
SQL variables

The following are supported only when you query a linked BigQuery dataset by using the BigQuery Studio and Looker Studio pages, or by using the bq command-line tool:

Javascript user-defined functions
BigQuery ML functions
SQL variables

Best practices

To set the time range of your query, we recommend that you use the time-range selector. For example, to view the data for the past week, select Last 7 days from the time-range selector. You can also use the time-range selector to specify a start and end time, specify a time to view around, and change time zones.

If you include a start_time field in the WHERE clause, then the time-range selector setting isn't used. The following example illustrates how to filter by timestamp:

-- Matches trace spans whose start_time is within the most recent 1 hour.
WHERE start_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

For more information about how to filter by time, see Time functions and Timestamp functions.

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Observability API.

Roles required to enable APIs

To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

Enable the API

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Observability API.

Roles required to enable APIs

Enable the API

To get the permissions that you need to load the Observability Analytics page, write, run, and save private queries on your trace data, ask your administrator to grant you the following IAM roles:
- Observability View Accessor (roles/observability.viewAccessor) on the observability views that you want to query. This role supports IAM conditions, which let you restrict the grant to a specific view. If you don't attach a condition to the role grant, then the principal can access all observability views.
- Observability Analytics User (roles/observability.analyticsUser) on your project. This role contains the permissions required to save and run private queries, and to run shared queries.
For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

How to use the queries on this page

In the Google Cloud console, go to the Observability Analytics page:
Go to Observability Analytics

If you use the search bar to find this page, then select the result whose subheading is Logging.
In the Query pane, click the SQL, and then copy and paste a query into the SQL query pane.

The following shows the format of the FROM cause for querying the _AllSpans view:
```
FROM `PROJECT_ID.LOCATION._Trace.Spans._AllSpans`
```
The FROM clause contains the following fields:
- PROJECT_ID: The identifier of the project.
- LOCATION: The location of the observability bucket.
- _Trace is the name of the observability bucket
- Spans is the name of the dataset.
- _AllSpans is the name of the view.
Note: The delimiters for the path are period characters, (.). If a path element like the Google Cloud project contains a period character, then ensure every path field is wrapped by single backquotes, (`).

To use the queries shown in this document on the BigQuery Studio page or to use the bq command-line tool, then edit the FROM clause and enter the path to the linked BigQuery dataset. For example, to query the _AllSpans view on the linked BigQuery dataset named my_linked_dataset that is in the project myproject, the path is `myproject.my_linked_dataset._AllSpans`.

Common use cases

This section lists several common use cases that might help you create your custom queries.

Show all trace data

To query the _AllSpans view, run the following query:

-- Display all data.
SELECT *
FROM `PROJECT_ID.LOCATION._Trace.Spans._AllSpans`
-- Limit to 10 entries.
LIMIT 10

Show common span information

To show common span information, like the start time and duration, run the following query:

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.LOCATION._Trace.Spans._AllSpans`
LIMIT 10

To learn more, see Conditional expressions.

Show 50th and 99th percentiles of the span latency

To show the 50th and 99th percentiles of the latency for each rpc service, run the following query:

SELECT
  -- Compute 50th and 99th percentiles for each service
  STRING(attributes['rpc.service']) || '/' || STRING(attributes['rpc.method']) AS rpc_service_method,
  APPROX_QUANTILES(duration_nano, 100)[OFFSET(50)] AS duration_nano_p50,
  APPROX_QUANTILES(duration_nano, 100)[OFFSET(99)] AS duration_nano_p99
FROM
  `PROJECT_ID.LOCATION._Trace.Spans._AllSpans`
WHERE
  -- Matches spans whose kind field has a value of 2 (SPAN_KIND_SERVER).
  kind = 2
GROUP BY rpc_service_method

For more information about the enumeration, see the OpenTelemetry: SpanKind documentation.

To view the results graphically, you might create a chart with the dimension set to rpc_service_method. You might add two measures, one for the average of the duration_nano_p50 value, and the other for the average of the duration_nano_p99 field.

Filter trace entries

To apply a filter to your query, add a WHERE clause. The syntax that you use in this clause depends on the data type of the field. This section provides several examples for different data types.

Filter by string data types

The field name is stored as a String.

To analyze only those spans where name is specified, use the following clause:
```
-- Matches spans that have a name field.
WHERE name IS NOT NULL
```
To analyze only those spans where name has the value "POST", use the following clause:
```
-- Matches spans whose name is POST.
WHERE STRPOS(name, "POST") > 0
```
To analyze only those spans where name contains the value "POST", use the LIKE operator along with wildcards:
```
-- Matches spans whose name contains POST.
WHERE name LIKE "%POST%"
```

Filter by integer data types

The field kind is an integer, which can take values between zero and five:

To analyze only those spans where kind is specified, use the following clause:
```
-- Matches spans that have field named kind.
WHERE kind IS NOT NULL
```
To analyze spans whose kind value is either one or two, use the following clause:
```
-- Matches spans whose kind value is 1 or 2.
WHERE kind IN (1, 2)
```

Filter by RECORD data types

Some fields in the trace schema have a data type of RECORD. These fields can either store one or more data structures, or they store repeated entries of the same data structure.

Filter by status or status code

The status field is an example of a field whose data type is RECORD. This field stores one data structure, with members labeled code and message.

To only analyze spans when the status.code field has a value of 1, add the following clause:
```
-- Matches spans that have a status.code field that has a value of 1.
WHERE status.code = 1
```
The status.code field is stored as an integer.

To analyze spans where the status field isn't EMPTY, add the following clause:

-- Matches spans that have status field. When the status field exists, it
-- must contain a subfield named code.
-- Don't compare status to NULL, because this field has a data type of RECORD.
WHERE status.code IS NOT NULL

Filter by events or links

The events and links fields are stored with a data type of RECORD, but these are repeated fields.

To match spans that have at least one event, use the following clause:

-- Matches spans that have at least one event. Don't compare events to NULL.
-- The events field has data type of RECORD and contains a repeated fields.
WHERE ARRAY_LENGTH(events) > 0

To match spans that have an event whose name field has the value of message, use the following clause:

WHERE
  -- Exists is true when any event in the array has a name field with the
  -- value of message.
  EXISTS(
    SELECT 1
    FROM UNNEST(events) AS ev
    WHERE ev.name = 'message'
  )

Filter by JSON data types

The attributes field is of type JSON. Each individual attribute is a key-value pair.

To analyze only those spans where attributes is specified, use the following clause:

-- Matches spans where at least one attribute is specified.
WHERE attributes IS NOT NULL

To analyze only those spans where the attribute key named component has a value of "proxy", use the following clause:

-- Matches spans that have an attribute named component with a value of proxy.
WHERE attributes IS NOT NULL
      AND JSON_VALUE(attributes, '$.component') = 'proxy'

You can also use a LIKE statement along with wildcards to perform a contains test:

-- Matches spans that have an attribute named component whose value contains proxy.
WHERE attributes IS NOT NULL
      AND JSON_VALUE(attributes, '$.component') LIKE '%proxy%'

Group and aggregate trace data

This section illustrates how you can group and aggregate spans. If you don't specify a grouping but do specify an aggregation, one result is printed because SQL treats all entries that satisfy the WHERE clause as one group.

Every SELECT expression must be included in the group fields or be aggregated.

Group spans by start time

To group data by start time, use the function TIMESTAMP_TRUNC, which truncates a timestamp to a specified granularity like HOUR:

SELECT
  -- Truncate the start time to the hour. Count the number of spans per group.
  TIMESTAMP_TRUNC(start_time, HOUR)

Sample SQL queries for Trace Stay organized with collections Save and categorize content based on your preferences.

SQL language support

Best practices

Before you begin

How to use the queries on this page

Common use cases

Show all trace data

Show common span information

Show 50th and 99th percentiles of the span latency

Filter trace entries

Filter by string data types

Filter by integer data types

Filter by RECORD data types

Filter by status or status code

Filter by events or links

Filter by JSON data types

Group and aggregate trace data

Group spans by start time

Sample SQL queries for Trace