SESSIONS_BY_PROJECT view

The INFORMATION_SCHEMA.SESSIONS_BY_PROJECT view contains real-time metadata about all BigQuery sessions in the current project.

Required permissions

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

• Project Owner
• BigQuery Admin

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:

Note: The underlying data is partitioned by the creation_time column and clustered by project_id and user_email.

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.
principal_subject	STRING	(Clustering column) Principal identifier of the user who ran the job.
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_PROJECT	Project level	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.

Note: You must specify the location to query the INFORMATION_SCHEMA views and the location of the query execution must match the region of the INFORMATION_SCHEMA view.

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_PROJECT

For example, `myproject`.`region-us`.INFORMATION_SCHEMA.SESSIONS_BY_PROJECT. The following example lists all users or service accounts that created sessions for a given project within the last day:

SELECT
  DISTINCT(user_email) AS user
FROM
  `region-us`.INFORMATION_SCHEMA.SESSIONS_BY_PROJECT
WHERE
  is_active = true
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

Note: INFORMATION_SCHEMA view names are case-sensitive.

The result is similar to the following:

+--------------+
| user         |
+--------------+
| [e-mail]  |
+--------------+
| [e-mail]  |
+--------------+