Finding sessions

This document describes how to get a session ID in BigQuery. It is intended for users who already have a general understanding of BigQuery sessions and have created a session.

Before you complete these steps, ensure you have the necessary permissions to get the ID of a session.

Get the ID of your active session

In some situations, you need to reference a session to continue working within it. For example, if you are working with the Cloud Shell, you must include the session ID each time you run a command for that session.

Console

You don't need to provide the session ID to run a new query inside a session in the Google Cloud console. You can just continue working in the editor tab that contains the session. However, if you would like to reference your session in the Cloud Shell or an API call, you need to know the ID for the session you created in the console.

Before you complete these steps, make sure that that you have run at least one query in an active session.

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

    Go to BigQuery

  2. Click the editor tab that contains the session.

  3. In Query results, click Job information.

  4. In the Job information list, search for the session ID:

    Session ID: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

bq

To run query commands in a session within the Cloud Shell, you need to include the session ID in the command. You can get the session ID when you create a session or by retrieving a list of sessions.

When you create a session with the Cloud Shell, the session ID that is returned looks like this:

In session: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

API

To pass SQL commands into a session with an API call, you need to include the session ID in the API call. You can get the session ID when you create a session or by retrieving a list of sessions.

When you create a session with an API call, the session ID in the response looks like this:

sessionId: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

Get a list of your active and inactive sessions

To get session IDs of active and inactive sessions, follow these steps:

Console

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

    Go to BigQuery

  2. Select the type of job history:

    • To display information of your recent jobs, click Personal history.
    • To display information of recent jobs in your project, click Project history.

  3. In the Session ID column, you can view session IDs for your jobs.

    Session ID in job history

SQL

To get a list of your three most recent sessions including the active and terminated sessions, run the following query in the editor tab:

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

    Go to BigQuery

  2. In the query editor, enter the following statement:

    SELECT
  session_id,
  MAX(creation_time) AS last_modified_time
FROM region-us.INFORMATION_SCHEMA.VIEW
WHERE
  session_id IS NOT NULL
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 DAY)
GROUP BY session_id
ORDER BY last_modified_time DESC;

    Replace the following:

    • VIEW: the INFORMATION_SCHEMA view:
      • 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

  3. Click Run.

For more information about how to run queries, see Run an interactive query.

The query produces results that look similar to this:

+-------------------------------------------------------------------------+
| session_id                                        | last_modified_time  |
+-------------------------------------------------------------------------+
| CgwKCmZhbGl1LXRlc3QQARokMGQ5YWWYzZmE0YjhkMDBm     | 2021-06-01 23:04:26 |
| CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZTczwZjA1NDc2 | 2021-05-30 22:43:02 |
| CgwKCmZhbGl1LXRlc3QQY2MzLTg4ZDEtYzVhOWZiYmM5NzZk  | 2021-04-07 22:31:21 |
+-------------------------------------------------------------------------+

Get a list of your active sessions

To get a list of active sessions that have not terminated in the last 24 hours, run the following query:

SELECT
  session_id,
  MAX(creation_time) AS last_modified_time
FROM `region-us`.INFORMATION_SCHEMA.VIEW
WHERE
  session_id IS NOT NULL
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND is_active
GROUP BY session_id
ORDER BY last_modified_time DESC;

The query produces results that look similar to this:

+-------------------------------------------------------------------------+
| session_id                                        | last_modified_time  |
+-------------------------------------------------------------------------+
| CgwKCmZhbGl1LXRlc3QQARokMGQ5YWWYzZmE0YjhkMDBm     | 2021-06-01 23:04:26 |
| CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZTczwZjA1NDc2 | 2021-05-30 22:43:02 |
+-------------------------------------------------------------------------+

Replace the following:

  • VIEW: the INFORMATION_SCHEMA view:
    • 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

What's next