Work with sessions

Console

In the Google Cloud console, each session is assigned to an editor tab.

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

   Go to BigQuery

2. Click add_boxCompose new query. A new editor tab opens.

3. Click More > Query settings. The Query settings panel appears.

4. In the Session management section, click Use session mode to enable the session mode.

5. In Additional settings > Data location, select the location. After the session is created, all queries in the session are restricted to this location and the location cannot be changed.

6. Click Save.

7. Write a query in the editor tab and run it. The new session is created after this first query is run.

bq

Open the Cloud Shell and enter the following bq query command:

bq query \
--nouse_legacy_sql \
--create_session
[--location 'SESSION_LOCATION'] \
'SQL_STATEMENT'

where:

• SESSION_LOCATION: Bind the session to a physical location. Restrict all queries in the session to this location. Optional.
• SQL_STATEMENT: The first SQL statement for your session.

Your session ID is returned with the results of the query.

API

Call the jobs.query method with the following parameters:

{
  "query": "SQL_STATEMENT",
  "createSession": true,
  ["location": "SESSION_LOCATION"]
}

where:

• SQL_STATEMENT: The first SQL statement for your session.
• SESSION_LOCATION: Bind the session to a physical location. Restrict all queries in the session to this location. Optional.

The response body is similar to the following:

{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "job_123"
  },
  "statistics": {
    "sessionInfo": {
      "sessionId": "CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ"
    }
  }
}

Console

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

   Go to BigQuery

2. Click the editor tab that contains the session.

3. Add your query to the session and click Run.

bq

Open the Cloud Shell and enter the following bq query command:

bq query \
--nouse_legacy_sql \
--session_id=SESSION_ID \
'SQL_STATEMENT'

where:

• SESSION_ID: Replace this with the ID of the session you want to work with.
• SQL_STATEMENT: A SQL statement to run in your session.

The results of the query are followed by your session ID.

If you are going to run lots of queries with the Cloud Shell, you can add your session ID to [query] in .bigqueryrc so that you don't need to copy and paste the session ID into each command.

This is what a session ID looks like in .bigqueryrc:

[query]
--session_id=CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

After you've added the session ID to .bigqueryrc, you can omit the --session_id flag from the bq query command. If you want to use a different session or if a session terminates, you must update your .bigqueryrc file.

API

Call the jobs.query method with the following parameters:

{
  "query": "SQL_STATEMENT",
  "connectionProperties": [{
    "key": "session_id",
    "value": "SESSION_ID"
  }]
}

where:

• SQL_STATEMENT: The first SQL statement for your session.
• SESSION_ID: The ID of the session.

Console

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

   Go to BigQuery

2. Find the editor tab that contains your session and close it. The session is terminated.

SQL

Do the following to terminate your session:

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

   Go to BigQuery

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

   CALL BQ.ABORT_SESSION();

3. Click play_circle Run.

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

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 listing your sessions.

When you create a session with the Cloud Shell, the session ID that is returned is similar to the following:

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 listing your sessions.

When you create a session with an API call, the session ID in the response looks similar to the following:

sessionId: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

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.

   

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 play_circle Run.

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

The result is similar to the following:

+-------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------+

Console

To view the history of a session in the Google Cloud console, you can filter your Personal History or Project History by session ID to view all SQL queries run in a specific session.

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

   Go to BigQuery

2. Expand the Job history section.

3. Select the type of job history you want to view:

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

4. Click filter_list Filter and then select Session ID.

5. In the Session ID field, search for the session ID:

   Session ID: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ
   

SQL

To view historical data for a specific session, first get your session ID, then follow these steps:

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

   Go to BigQuery

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

   SELECT
     *
   FROM
     region-us.INFORMATION_SCHEMA.VIEW
   WHERE
     session_info.session_id = 'SESSION_ID';

   Replace the following:

   • VIEW: the INFORMATION_SCHEMA view to work with

     Select one of the following views:

     • 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 for which to retrieve historical data

3. Click play_circle Run.

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

Example

The following returns the history for a session that has the session ID CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0. You can replace this session ID with your own.

SELECT
  creation_time, query
FROM
  region-us.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
  session_info.session_id = 'CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0'
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 DAY);

The result is similar to the following:

+---------------------+------------------------------------------------------------------------------------------+
|    creation_time    |                                          query                                           |
+---------------------+------------------------------------------------------------------------------------------+
| 2021-06-01 23:04:26 | SELECT * FROM Purchases;                                                                 |
| 2021-06-01 23:02:51 | CREATE TEMP TABLE Purchases(total INT64) AS SELECT * FROM UNNEST([10,23,3,14,55]) AS a;  |
+---------------------+------------------------------------------------------------------------------------------+