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 ofBigQuerysessions and know howtorun queries in a session.

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

A session includes support forsession variables,session system variables,multi-statement queries, andmulti-statement transactions.

Before you complete these steps, ensure you have the necessarypermissions towork in a session.

Use system variables in a session

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

• @@dataset_id: The ID of the default dataset in the current project. Thesystem variables@@dataset_project_id and@@dataset_id can be set andused together.
• @@dataset_project_id: The ID of the default project for datasets that areused in the query. If this system variable is not set, or if it is set toNULL, the query-executing project is used. The system variables@@dataset_project_id and@@dataset_id can be set and used together.
• @@query_label: Thejob label toassign 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-dependentSQL 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 inscope for the remaining session. You don't define these variables, but they canbe assigned a new value with theSET statement.

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

Assign a label to a session

You canassign 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 theremaining session. The job label that you assign will appear inaudit logs.

Use variables in a session

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

• To create a session-scoped variable, use theDECLARE statementoutside of aBEGIN...ENDblock.
• To set a session-scoped variable after it has been created, use theSET statement.
• A variable declared inside of aBEGIN...END block is not a session-scopedvariable.
• A session-scoped variable can be referenced inside of aBEGIN...END block.
• A session-scoped variable can be set inside of aBEGIN...END block.

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

Use temporary tables in sessions

A temporary table lets you save intermediate results to a table. Atemporary table is visible at the session level, so you don't need to save ormaintain it in a dataset. It is automatically deleted after asession terminates. You are charged for storage of temporary tables while thesession is active. For more information, seeUse temporary tables in a multi-statement query.

Use temporary functions in sessions

Atemporary function ortemporary aggregate functionis visible at thesession level, so you don't need to save ormaintain it in a dataset. It is automatically deleted after asession terminates.

Work with multi-statement queries in sessions

You can useGoogleSQL multi-statement queriesin a session. A script can include temporary tables and system variables foreach 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.

BEGINTRANSACTION

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

CREATETEMPTABLEFlights(totalINT64)ASSELECT*FROMUNNEST([10,23,3,14,55])ASa;SELECT*FROMFlights;

The following query commits the transaction.

COMMIT

You can find an active transaction that affects theFlights table:

WITHrunning_transactionsAS(SELECTDISTINCTtransaction_idFROM`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHEREcreation_time >TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL7DAY)EXCEPTDISTINCTSELECTtransaction_idFROM`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHEREcreation_time >TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL7DAY)ANDstatement_type="COMMIT_TRANSACTION"ORstatement_type="ROLLBACK_TRANSACTION")SELECTjobs.transaction_idAStransaction_id,project_id,user_email,session_info.session_id,queryFROM`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECTASjobs,running_transactionsWHEREcreation_time >TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL7DAY)ANDdestination_table=("Flights")ANDjobs.transaction_id=running_transactions.transaction_id;

If you want to cancel an ongoing transaction and you have thebigquery.adminrole, you canissue a rollback statement,using the session ID associated with the transaction in the Cloud Shellor with an API call. When yourun the query,using the session ID associated with the transaction, the session ID is shown inthe results.

Example session

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

1. In the Google Cloud console, open a new editor tab andcreate a session.

2. In the editor tab, add the following query:

   CREATETEMPTABLEFlights(totalINT64)ASSELECT*FROMUNNEST([10,23,3,14,55])ASa;SELECT*FROMFlights;

3. Run the query. A temporary table calledFlights is created and all of thedata is returned.

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

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

   SELECT*FROMFlightsLIMIT2;

5. Run the query. The results for two records are returned. Even though youdeleted the earlier query, the information from the query is stored inthe current session.

   +-------+| total |+-------+|    55 ||    23 |+-------+

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

   DECLARExINT64DEFAULT10;SELECTtotal*xAStotal_aFROMFlightsLIMIT2;BEGINSETx=100;SELECTtotal*xAStotal_bFROMFlightsLIMIT2;END;SELECTtotal*xAStotal_cFROMFlightsLIMIT2;

7. Run the query. The session-scoped variablex is used to limit the numberof results returned for theFlights table. Look closely at how scopingaffects this variable when it is declared outside of aBEGIN...ENDstatement, set inside of aBEGIN...END statement, and then referencedoutside of theBEGIN...END statement again.

   +---------+| total_a |+---------+|     550 ||     230 |+---------++---------+| total_b |+---------+|    5500 ||    2300 |+---------++---------+| total_c |+---------+|    5500 ||    2300 |+---------+

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

   SELECTSTRING(TIMESTAMP"2008-12-20 15:30:00+00",@@time_zone)ASdefault_time_zone;SET@@time_zone="America/Los_Angeles";SELECTSTRING(TIMESTAMP"2008-12-20 15:30:00+00",@@time_zone)ASnew_time_zone;

9. Run the query. The session-scoped system variable@@time_zone is used toassign a time zone to a timestamp. The first statement returns atimestamp with the default time zone (in this example,UTC). The nextstatement assigns@@time_zone to a new value. The third statement returnsa 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

• Learn more about how towork with sessions,including how to create, use, terminate, and list your sessions.
• Learn more aboutmulti-statement queries inGoogleSQL.
• Learn more aboutmulti-statement transactions inGoogleSQL.