![[Google Cloud Documentation]](https://www.gstatic.com/devrel-devsite/prod/v0f3a5b8b76e4a120372a69044e02756e703b6140f9f1812c6e4a0407b19864b7/clouddocs/images/lockup_full_color.svg){kind=link}
![[]](https://docs.cloud.google.com/_static/clouddocs/images/icons/products/bigquery-color.svg){kind=link}
SESSIONS_BY_PROJECT view
The INFORMATION_SCHEMA.SESSIONS_BY_PROJECT view contains real-time
metadata about all BigQuery sessions in the current project.
INFORMATION_SCHEMA.SESSIONS and
INFORMATION_SCHEMA.SESSIONS_BY_PROJECT are synonymous and
can be used interchangeably.
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:
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. |
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. |
For stability, we recommend that you explicitly list columns in your information schema queries instead of
using a wildcard (SELECT *). Explicitly listing columns prevents queries from
breaking if the underlying schema changes.
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 |
-
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 use a region qualifier to query
INFORMATION_SCHEMAviews. The location of the query execution must match the region of theINFORMATION_SCHEMAview.
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_PROJECTFor 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 | +--------------+ | abc@xyz.com | +--------------+ | def@xyz.com | +--------------+