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 current 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 do not need to provide the session ID to run a new query inside a session in the 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 editor tabs are enabled and that you've run at least one query in the session.

  1. In the Cloud Console, go to the BigQuery page.

    Go to BigQuery

  2. Click on the editor tab that contains the session.

  3. In Query results, click Job information.

  4. In the Job information list, look for a line that looks like this:

    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

Run the following query to produce a list of your three most recent sessions. Active and terminated sessions are included.

SELECT
  session_info.session_id,
  MAX(creation_time) AS last_modified_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
  WHERE session_info.session_id IS NOT null
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 DAY)
  GROUP BY session_info.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 |
| CgwKCmZhbGl1LXRlc3QQY2MzLTg4ZDEtYzVhOWZiYmM5NzZk  | 2021-04-07 22:31:21 |
+-------------------------------------------------------------------------+

where:

  • session_id: The ID of the session.
  • last_modified_time: The date and time when the session was last modified.

Get a list of your active sessions

Run the following query to produce a list of active sessions that have not terminated in the last 24 hours.

SELECT
  session_info.session_id,
  MAX(creation_time) AS last_modified_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
  WHERE
   session_info.session_id IS NOT null
   AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  GROUP BY session_info.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 |
+-------------------------------------------------------------------------+

where:

  • session_id: The ID of the session.
  • last_modified_time: The date and time when the session was last modified.

What's next