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

  1. 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.

  2. 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

  3. Verify that billing is enabled for your Google Cloud project.

  4. 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

  5. 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

  6. Verify that billing is enabled for your Google Cloud project.

  7. 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

    8.

  8. 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

  1. 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.

  2. 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.

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: