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 do not 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. Scroll to the left to view Session ID.

    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_info.session_id,
  MAX(creation_time) AS last_modified_time
FROM region-us.INFORMATION_SCHEMA.VIEW
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;

    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
    • SESSION_ID: the ID of the session
    • LAST_MODIFIED_TIME: the date and time when the session was last modified

  3. Click Run.

For more information about how to run queries, see Running interactive queries.

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

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
  • SESSION_ID: the ID of the session
  • LAST_MODIFIED_TIME: the date and time when the session was last modified

What's next