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 .

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

Go to BigQuery
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 .
Click Filter then select Session ID .

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:

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

Go to BigQuery
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
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

Finding sessions