System variables reference

BigQuery supports the following system variables for multi-statement queries or within sessions . You can use system variables to set or retrieve information during query execution, similar to user-defined procedural language variables .

Name	Type	Read and write or read-only	Description
`@@current_job_id`	`STRING`	Read-only	Job ID of the currently executing job. In the context of a multi-statement query, this returns the job responsible for the current statement, not the entire multi-statement query.
`@@dataset_id`	`STRING`	Read and write	ID of the default dataset in the current project. This ID is used when a dataset is not specified for a project in the query. You can use the `SET` statement to assign `@@dataset_id` to another dataset ID in the current project. The system variables `@@dataset_project_id` and `@@dataset_id` can be set and used together.
`@@dataset_project_id`	`STRING`	Read and write	ID of the default project that's used when one is not specified for a dataset used in the query. If `@@dataset_project_id` is not set, or if it is set to `NULL` , the query-executing project ( `@@project_id` ) is used. You can use the `SET` statement to assign `@@dataset_project_id` to another project ID. The system variables `@@dataset_project_id` and `@@dataset_id` can be set and used together.
`@@last_job_id`	`STRING`	Read-only	Job ID of the most recent job to execute in the current multi-statement query, not including the current one. If the multi-statement query contains `CALL` statements, this job may have originated in a different procedure.
`@@location`	`STRING`	Read and write	The location in which to run the query. `@@location` can only be set to a string literal with a valid location . A `SET @@location` statement must be the first statement in a query. An error occurs if there is a mismatch between `@@location` and another location setting for the query. You can improve the latency of queries that set `@@location` by using optional job creation mode . You can use the `@@location` system variable inside of SQL UDFs and table functions .
`@@project_id`	`STRING`	Read-only	ID of the project used to execute the current query. In the context of a procedure, `@@project_id` refers to the project that is running the multi-statement query, not the project which owns the procedure.
`@@query_label`	`STRING`	Read and write	Query label to associate with query jobs in the current multi-statement query or session. If set in a query, all subsequent query jobs in the script or session will have this label. If not set in a query, the value for this system variable is `NULL` . For an example of how to set this system variable, see Associate jobs in a session with a label .
`@@reservation`	`STRING`	Read and write	[ Preview ]. Specifies the reservation where the job is run. Must be in the following format: `projects/ project_id /locations/ location /reservations/ reservation_id` . The location of the reservation must match the location the query is running in.
`@@row_count`	`INT64`	Read-only	If used in a multi-statement query and the previous statement is DML, specifies the number of rows modified, inserted, or deleted, as a result of that DML statement. If the previous statement is a MERGE statement, `@@row_count` represents the combined total number of rows inserted, removed, and deleted. This value is `NULL` if not in a multi-statement query.
`@@script.bytes_billed`	`INT64`	Read-only	Total bytes billed so far in the currently executing multi-statement query job. This value is `NULL` if not in the job.
`@@script.bytes_processed`	`INT64`	Read-only	Total bytes processed so far in the currently executing multi-statement query job. This value is `NULL` if not in the job.
`@@script.creation_time`	`TIMESTAMP`	Read-only	Creation time of the currently executing multi-statement query job. This value is `NULL` if not in the job.
`@@script.job_id`	`STRING`	Read-only	Job ID of the currently executing multi-statement query job. This value is `NULL` if not in the job.
`@@script.num_child_jobs`	`INT64`	Read-only	Number of currently completed child jobs. This value is `NULL` if not in the job.
`@@script.slot_ms`	`INT64`	Read-only	Number of slot milliseconds used so far by the script. This value is `NULL` if not in the job.
`@@session_id`	`INT64`	Read-only	ID of the session that the current query is associated with.
`@@time_zone`	`STRING`	Read and write	The default time zone to use in time zone-dependent SQL functions, when a time zone is not specified as an argument. `@@time_zone` can be modified by using a `SET` statement to any valid time zone name. At the start of each script, `@@time_zone` begins as “UTC”.

For backward compatibility, expressions used in an OPTIONS or FOR SYSTEM TIME AS OF clause default to the America/Los_Angeles time zone, while all other date/time expressions default to the UTC time zone. If @@time_zone has been set earlier in the multi-statement query, the chosen time zone will apply to all date/time expressions, including OPTIONS and FOR SYSTEM TIME AS OF clauses.

In addition to the system variables shown previously, you can use EXCEPTION system variables during execution of a multi-statement query. For more information about the EXCEPTION system variables, see the procedural language statement BEGIN...EXCEPTION .

Examples

You don't create system variables, but you can override the default value for some of them:

  SET 
  
 @@ 
 dataset_project_id 
  
 = 
  
 'MyProject' 
 ;

The following query returns the default time zone:

  SELECT 
  
 @@ 
 time_zone 
  
 AS 
  
 default_time_zone 
 ;

 +-------------------+
| default_time_zone |
+-------------------+
| UTC               |
+-------------------+

You can use system variables with DDL and DML queries. For example, here are a few ways to use the system variable @@time_zone when creating and updating a table:

  BEGIN 
  
 CREATE 
  
 TEMP 
  
 TABLE 
  
 MyTempTable 
  
 AS 
  
 SELECT 
  
 @@ 
 time_zone 
  
 AS 
  
 default_time_zone 
 ; 
 END 
 ;

  CREATE 
  
 OR 
  
 REPLACE 
  
 TABLE 
  
 MyDataset 
 . 
 MyTable 
 ( 
 default_time_zone 
  
 STRING 
 ) 
  
 OPTIONS 
  
 ( 
 description 
  
 = 
  
 @@ 
 time_zone 
 );

  UPDATE 
  
 MyDataset 
 . 
 MyTable 
 SET 
  
 default_time_zone 
  
 = 
  
 @@ 
 time_zone 
 WHERE 
  
 TRUE 
 ;

There are some places where system variables can't be used in DDL and DML queries. For example, you can't use a system variable as a project name, dataset, or table name. The following query produces an error when you include the @@dataset_id system variable in a table path:

  BEGIN 
  
 CREATE 
  
 TEMP 
  
 TABLE 
  
 @@ 
 dataset_id 
 . 
 MyTempTable 
  
 ( 
 id 
  
 STRING 
 ); 
 END 
 ;

For more examples of how you can use system variables in multi-statement queries, see Set a variable .

For examples of how you can use system variables in sessions, see Example session .