Scripting and routinesScripting

Sessions in BigQuery

A BigQuery session keeps temp tables, variables, temporary functions and settings like @@time_zone between separate queries, for up to 24 hours of inactivity. How to start one, what carries over, and when a script is the better choice.

Normally every BigQuery query starts from nothing: a temp table, a variable or a temporary function exists only inside the query or script that created it. A session changes that. Queries that run in the same session share temporary tables, variables, temporary functions and system settings, so you can work step by step the way you would in a database shell, and a transaction can span several queries.

Starting a session

In the console, turn on “Use session mode” in a query tab’s settings; every query in that tab then runs in its session. With bq, create a session with the first query and pass its ID to the next ones:

bq query --nouse_legacy_sql --create_session \
  "CREATE TEMP TABLE carts AS SELECT 1 AS id, 'open' AS status"
# prints: In session: <session id>

bq query --nouse_legacy_sql --session_id=<session id> \
  "SELECT * FROM _SESSION.carts"

Through the API, set createSession on the first job and pass the session ID in connectionProperties on the next ones.

What carries over

We ran each of these as a separate query in one session:

First query	Later query	Result
`CREATE TEMP TABLE carts AS ...`	`SELECT * FROM _SESSION.carts`	the table’s rows
`DECLARE x INT64 DEFAULT 5`	`SELECT x`	`5`
`CREATE TEMP FUNCTION dbl(x INT64) AS (x * 2)`	`SELECT dbl(21)`	`42`
`SET @@time_zone = 'Asia/Tokyo'`	`SELECT DATE(TIMESTAMP '2026-01-01 20:00:00 UTC')`	`2026-01-02`

Temp tables live in a hidden dataset. You can write _SESSION.carts or just carts; the _SESSION qualifier always means a temp table, so it can’t be confused with a table of the same name in the default dataset.

@@time_zone changes every function that converts a timestamp without an explicit zone, for the rest of the session. See DATE, DATETIME, TIMESTAMP and time zones. @@session_id returns the current session’s ID.

How long a session lasts

A session ends after 24 hours without a query.
It also ends 7 days after it was created, active or not.
CALL BQ.ABORT_SESSION(); ends it immediately. Any query with that session ID afterwards fails with ”… has expired and is no longer available”.

When the session ends, its temp tables are deleted. While it lasts, they are billed as active storage.

Who can use it

You can work only in sessions you created. An admin (bigquery.jobs.update) can end another user’s session with CALL BQ.ABORT_SESSION('session id'). Every query in a session runs in the location where the session was created.

Limits that matter

You can’t run two queries in the same session at the same time.
A session variable can be at most 1 MB, and all of a session’s variables together 10 MB.
Session jobs show up in job history with their session ID (session_info.session_id in INFORMATION_SCHEMA.JOBS), which is how you find everything a session ran. INFORMATION_SCHEMA.SESSIONS_BY_USER lists your sessions.

Session or script

Use a script when the steps are known in advance: it is one job, runs top to bottom, and nothing else can interfere with its state. Use a session for interactive work, where you look at the result of one step before writing the next, or when a tool sends several separate queries that need shared state, like a temp table created by one and read by the next.

In Querylab.io, several statements separated by semicolons in one tab run as one script, with a result per statement and temp tables available to the later ones (Multi-statement queries).