Viewing query history in sessions

This document describes how to view the history of a session in BigQuery. It is intended for users who already have a general understanding of BigQuery sessions.

Before you complete these steps, ensure you have the necessary permissions to view the history of a session.

View the history of a session

A session captures your SQL activities within a timeframe. This information is stored in the session's history. Session history lets you track changes you've made in the session. If a job fails or succeeds, it is recorded in the session history so you can go back later and see what you did.

Console

To view the history of a session in the Google Cloud console, you can filter your Personal History or Project History by session ID to view all SQL queries run in a specific session. For information about finding your session ID, see Finding sessions.

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. Expand the Job history section.

  3. Select the type of job history you want to view:

    • To display information of your recent jobs, click Personal history.
    • To display information of recent jobs in your project, click Project history.

  4. Click Filter and then select Session ID.

  5. In the Session ID field, search for the session ID:

    Session ID: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

SQL

To view historical data for a specific session, first get your session ID, then follow these steps:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    SELECT
  *
FROM
  region-us.INFORMATION_SCHEMA.VIEW
WHERE
  session_info.session_id = 'SESSION_ID';

    Replace the following:

    • VIEW: the INFORMATION_SCHEMA view to work with

      Select one of the following views:

      • JOBS_BY_USER: returns only the jobs created by the current user in the current project
      • SESSIONS_BY_USER: returns only the sessions created by the current user in the current project
      • SESSIONS_BY_PROJECT: returns all sessions in the current project
    • SESSION_ID: the ID of the session for which to retrieve historical data

  3. Click Run.

For more information about how to run queries, see Run an interactive query.

Example

The following returns the history for a session that has the session ID CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0. You can replace this session ID with your own.

SELECT
  creation_time, query
FROM
  region-us.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
  session_info.session_id = 'CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0'
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 DAY);

The result is similar to the following:

+---------------------+------------------------------------------------------------------------------------------+
|    creation_time    |                                          query                                           |
+---------------------+------------------------------------------------------------------------------------------+
| 2021-06-01 23:04:26 | SELECT * FROM Purchases;                                                                 |
| 2021-06-01 23:02:51 | CREATE TEMP TABLE Purchases(total INT64) AS SELECT * FROM UNNEST([10,23,3,14,55]) AS a;  |
+---------------------+------------------------------------------------------------------------------------------+

What's next