Writing queries in sessions

This document describes how to write queries in a BigQuery session. It is intended for users who already have a general understanding of BigQuery sessions and know how to run queries in a session.

A session stores state. State created in a session is maintained and usable throughout the entire session. So, if you create a temporary table in one query entry, you can use that temporary table in other query entries for the rest of the session.

A session includes support for session variables, session system variables, multi-statement queries, and multi-statement transactions.

Before you complete these steps, ensure you have the necessary permissions to work in a session.

Use system variables in a session

You can set or retrieve session-level data with the following system variables:

@@dataset_id: The ID of the default dataset in the current project. The system variables @@dataset_project_id and @@dataset_id can be set and used together.
@@dataset_project_id: The ID of the default project for datasets that are used in the query. If this system variable is not set, or if it is set to NULL, the query-executing project is used. The system variables @@dataset_project_id and @@dataset_id can be set and used together.
@@query_label: The job label to assign to the session. The label can be used throughout the entire session, not just for a specific query in the session.
@@session_id: The ID of the current session.
@@time_zone: The default time zone to use in time zone-dependent SQL functions, when a time zone is not specified as an argument.

These system variables can be used at any time during the session and are in scope for the remaining session. You don't define these variables, but they can be assigned a new value with the SET statement.

The maximum size of a variable in a session is 1 MB, and the maximum size of all variables in a session is 10 MB.

Assign a label to a session

You can assign a job label to a session. When you do this, all future queries in the session are assigned to the label. Labels can be used at any time during the session and are in scope for the remaining session. The job label that you assign will appear in audit logs.

Use variables in a session

You can create, set, and retrieve session-level data with variables. Variables can be used at any time during the session and are in scope for the remaining session.

To create a session-scoped variable, use the DECLARE statement outside of a BEGIN...END block.
To set a session-scoped variable after it has been created, use the SET statement.
A variable declared inside of a BEGIN...END block is not a session-scoped variable.
A session-scoped variable can be referenced inside of a BEGIN...END block.
A session-scoped variable can be set inside of a BEGIN...END block.

The maximum size of a variable in a session is 1 MB, and the maximum size of all variables in a session is 10 MB.

Use temporary tables in sessions

A temporary table lets you save intermediate results to a table. A temporary table is visible at the session level, so you don't need to save or maintain it in a dataset. It is automatically deleted after a session terminates. You are charged for storage of temporary tables while the session is active. For more information about temporary tables, see Work with multi-statement queries.

Use temporary functions in sessions

A temporary function is visible at the session level, so you don't need to save or maintain it in a dataset. It is automatically deleted after a session terminates.

Work with multi-statement queries in sessions

You can use GoogleSQL multi-statement queries in a session. A script can include temporary tables and system variables for each script. Session variables and temporary tables are visible to scripts. All top-level variables declared in a script are also session variables.

Run multi-query multi-statement transactions in sessions

You can run multi-statement transactions over multiple queries in a session. For example:

The following query begins a transaction.

BEGIN TRANSACTION

Inside of the transaction, the following query creates a temporary table called Flights and then returns the data in this table. Two statements are included in the query.

CREATE TEMP TABLE Flights(total INT64)  AS SELECT * FROM UNNEST([10,23,3,14,55]) AS a;

SELECT * FROM Flights;

The following query commits the transaction.

COMMIT

You can find an active transaction that affects the Flights table:

WITH running_transactions AS (
  SELECT DISTINCT transaction_id
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    EXCEPT DISTINCT
    SELECT transaction_id FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE
      creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
      AND statement_type = "COMMIT_TRANSACTION"
      OR statement_type = "ROLLBACK_TRANSACTION"
)
SELECT
  jobs.transaction_id AS transaction_id,
  project_id,
  user_email,
  session_info.session_id,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs, running_transactions
  WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND destination_table = ("Flights")
  AND jobs.transaction_id = running_transactions.transaction_id;

If you want to cancel an ongoing transaction and you have the bigquery.admin role, you can issue a rollback statement, using the session ID associated with the transaction in the Cloud Shell or with an API call. When you run the query, using the session ID associated with the transaction, the session ID is shown in the results.

Example session

This is an example of the session workflow in the Google Cloud console:

In the Google Cloud console, open a new editor tab and create a session.

In the editor tab, add the following query:

CREATE TEMP TABLE Flights(total INT64)  AS SELECT * FROM UNNEST([10,23,3,14,55]) AS a;
SELECT * FROM Flights;

Run the query. A temporary table called Flights is created and all of the data is returned.

+-------+
| total |
+-------+
|    55 |
|    23 |
|     3 |
|    14 |
|    10 |
+-------+

Delete the content inside the editor tab and add the following query:
```
SELECT * FROM Flights LIMIT 2;
```
Run the query. The results for two records are returned. Even though you deleted the earlier query, the information from the query is stored in the current session.
```
+-------+
| total |
+-------+
|    55 |
|    23 |
+-------+
```

Delete the content inside the editor tab and add the following query:

DECLARE x INT64 DEFAULT 10;

SELECT total * x AS total_a FROM Flights LIMIT 2;

BEGIN
  SET x = 100;
  SELECT total * x AS total_b FROM Flights LIMIT 2;
END;

SELECT total * x AS total_c FROM Flights LIMIT 2;

Run the query. The session-scoped variable x is used to limit the number of results returned for the Flights table. Look closely at how scoping affects this variable when it is declared outside of a BEGIN...END statement, set inside of a BEGIN...END statement, and then referenced outside of the BEGIN...END statement again.
```
+---------+
| total_a |
+---------+
|     550 |
|     230 |
+---------+

+---------+
| total_b |
+---------+
|    5500 |
|    2300 |
+---------+

+---------+
| total_c |
+---------+
|    5500 |
|    2300 |
+---------+
```