Console

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

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

   Go to BigQuery

2. Clickadd_boxCompose new query. Anew editor tab opens.

3. ClickMore> Query settings. TheQuery settings panelappears.

4. In theSession management section, clickUse session mode toenable the session mode.

5. InAdditional settings> Data location, select thelocation. After the session is created, all queries in the session arerestricted to this location and the location cannot be changed.

6. ClickSave.

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

bq

Open theCloud Shelland enter the followingbq query command:

bqquery\--nouse_legacy_sql\--create_session[--location'SESSION_LOCATION']\'SQL_STATEMENT'

where:

• SESSION_LOCATION: Bind the session to aphysical location. Restrict all queries inthe 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 thejobs.query method withthe 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 aphysical location. Restrict all queries inthe 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 clickRun.

bq

Open theCloud Shelland enter the followingbq query command:

bqquery\--nouse_legacy_sql\--session_id=SESSION_ID\'SQL_STATEMENT'

where:

• SESSION_ID: Replace this with theID 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.bigqueryrcso 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 thebq query command. If you want to use adifferent session or if a sessionterminates, you must update your.bigqueryrc file.

API

Call thejobs.query method withthe following parameters:

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

where:

• SQL_STATEMENT: The first SQL statement for your session.
• SESSION_ID: TheID 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 theBigQuery page.

   Go to BigQuery

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

   CALLBQ.ABORT_SESSION();

3. Clickplay_circleRun.

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

Console

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

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

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

   Go to BigQuery

2. Click the editor tab that contains the session.

3. InQuery results, clickJob information.

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

   Session ID: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

bq

To run query commands in a session within the Cloud Shell, you need toinclude the session ID in the command. You can get the session ID when youcreate a session or bylisting your sessions.

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

In session: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

API

To pass SQL commands into a session with an API call, you need to includethe session ID in the API call. You can get the session ID when youcreate a session or bylisting your sessions.

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

sessionId: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

Console

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, clickJob history.

4. Select the type of job history:

   • To display information of your recent jobs, clickPersonal history.
   • To display information of recent jobs in your project, clickProjecthistory.

5. In theSession ID column, you can view session IDs for your jobs.

   

SQL

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

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

   Go to BigQuery

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

   SELECTsession_id,MAX(creation_time)ASlast_modified_timeFROMregion-us.INFORMATION_SCHEMA.VIEWWHEREsession_idISNOTNULLANDcreation_time >TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL20DAY)GROUPBYsession_idORDERBYlast_modified_timeDESC;

   Replace the following:

   • VIEW: theINFORMATION_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. Clickplay_circleRun.

For more information about how to run queries, seeRun 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 filteryourPersonal History orProject History by session ID to view allSQL queries run in a specific session.

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, clickJob history.

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

   • To display information of your recent jobs, clickPersonal history.
   • To display information of recent jobs in your project, clickProject history.

5. Clickfilter_listFilter and thenselectSession ID.

6. In theSession ID field, search for the session ID:

   Session ID: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

SQL

To view historical data for a specific session, firstget your session ID, then follow thesesteps:

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

   Go to BigQuery

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

   SELECT*FROMregion-us.INFORMATION_SCHEMA.VIEWWHEREsession_info.session_id='SESSION_ID';

   Replace the following:

   • VIEW: theINFORMATION_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. Clickplay_circleRun.

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

Example

The following returns the history for a session that has the session IDCgwKCmZhbGl1LXRlc3QQARokMDAzYjI0. You can replace this session ID with yourown.

SELECTcreation_time,queryFROMregion-us.INFORMATION_SCHEMA.JOBS_BY_USERWHEREsession_info.session_id='CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0'ANDcreation_time>TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL20DAY);

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