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.
Expand the Job history section.
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 and 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.
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 withSelect one of the following views:
JOBS_BY_USER
: returns only the jobs created by the current user in the current projectSESSIONS_BY_USER
: returns only the sessions created by the current user in the current projectSESSIONS_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; |
+---------------------+------------------------------------------------------------------------------------------+