SESSIONS_BY_USER view

The INFORMATION_SCHEMA.SESSIONS_BY_USER view contains real-time metadata about BigQuery sessions created by the current user in the current project.

Required permissions

To query the INFORMATION_SCHEMA.SESSIONS_BY_USER view, you need the bigquery.jobs.list Identity and Access Management (IAM) permission for the project. Each of the following predefined IAM roles includes the required permission:

  • Project Viewer
  • BigQuery User

For more information about BigQuery permissions, see Access control with IAM.

Schema

When you query the INFORMATION_SCHEMA.SESSIONS_BY_* views, the query results contain one row for each BigQuery session.

The INFORMATION_SCHEMA.SESSIONS_BY_* view has the following schema:

Column name Data type Value
creation_time TIMESTAMP (Partitioning column) Creation time of this session. Partitioning is based on the UTC time of this timestamp.
expiration_time TIMESTAMP (Partitioning column) Expiration time of this session. Partitioning is based on the UTC time of this timestamp.
is_active BOOL Is the session is still active? TRUE if yes, otherwise FALSE.
last_modified_time TIMESTAMP (Partitioning column) Time when the session was last modified. Partitioning is based on the UTC time of this timestamp.
project_id STRING (Clustering column) ID of the project.
project_number INTEGER Number of the project.
session_id STRING ID of the session. For example, bquxsession_1234.
user_email STRING (Clustering column) Email address or service account of the user who ran the session.

Data retention

This view contains currently running sessions and the history of sessions completed in the past 180 days.

Scope and syntax

Queries against this view must include a region qualifier. If you do not specify a regional qualifier, metadata is retrieved from all regions. The following table explains the region scope for this view:

View name Resource scope Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SESSIONS_BY_USER Sessions created by the current user in the specified project. REGION
Replace the following:

  • Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.

  • REGION: any dataset region name. For example, `region-us`.

    • Example

    To run the query against a project other than your default project, add the project ID in the following format: 

    `PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.SESSIONS_BY_USER
     For example, `myproject`.`region-us`.INFORMATION_SCHEMA.SESSIONS_BY_USER.

    The following example lists sessions that were created by the current user:

    SELECT
  session_id,
  creation_time
FROM
  `region-us`.INFORMATION_SCHEMA.SESSIONS_BY_USER
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
ORDER BY
  creation_time DESC;

    The results should look like the following:

    +-------------------------------------------------------------------------+
| session_id                                        | creation_time       |
+-------------------------------------------------------------------------+
| CgwKCmZhbGl1LXRlc3QQARokMGQ5YWWYzZmE0YjhkMDBm     | 2021-06-01 08:04:26 |
| CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZTczwZjA1NDc2 | 2021-05-31 22:43:02 |
+-------------------------------------------------------------------------+