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 console, the editor tab with the session must exist. If you deleted the editor tab, you can still retrieve the session history with SQL.

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

    Go to BigQuery

  2. Click on the editor tab that contains the session.

  3. Click Query History.

SQL

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

  1. In the 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 Running interactive queries.

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