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 Cloud 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 Cloud 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, get your session ID, and then run the following query.

SELECT *
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE session_info.session_id = 'SESSION_ID'

where:

  • SESSION_ID: The ID of the session for which to retrieve historical data.

Example

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

SELECT creation_time, project_id, user_email, job_id, 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 query produces results that look similar to this:

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