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 |
+---------+
Delete the content inside the editor tab and add the following query:
SELECT STRING(TIMESTAMP "2008-12-20 15:30:00+00", @@time_zone) AS default_time_zone;
SET @@time_zone = "America/Los_Angeles";
SELECT STRING(TIMESTAMP "2008-12-20 15:30:00+00", @@time_zone) AS new_time_zone;
Run the query. The session-scoped system variable
@@time_zone
is used to
assign a time zone to a timestamp. The first statement returns a
timestamp with the default time zone (in this example,
UTC
). The next
statement assigns
@@time_zone
to a new value. The third statement returns
a timestamp with the new time zone.
+-------------------------------+
| default_time_zone |
+-------------------------------+
| 2008-12-20 15:30:00+00 |
+-------------------------------+
+-------------------------------+
| new_time_zone |
+-------------------------------+
| 2008-12-20 07:30:00-08 |
+-------------------------------+
What's next