System procedures reference

BigQuery supports the following system procedures, which can be used similarly to user-created stored procedures .

BQ.ABORT_SESSION

Syntax

  CALL 
  
 BQ 
 . 
 ABORT_SESSION 
 
 ([ 
 session_id 
 ]); 

Description

Terminates your current session.

You can optionally specify the session ID , which lets you terminate a session if the system procedure isn't called from that session.

For more information, see Terminating sessions .

BQ.JOBS.CANCEL

Syntax

  CALL 
  
 BQ 
 . 
 JOBS 
 . 
 CANCEL 
 
 ( 
 job 
 ); 

Description

Cancels a running job.

Specify the job as a string with the format '[project_id.]job_id' . If you run this system procedure from a different project than the job, then you must include the project ID. You must run the procedure in the same location as the job.

For more information, see Canceling a job .

BQ.CANCEL_INDEX_ALTERATION

Syntax

  CALL 
  
 BQ 
 . 
 CANCEL_INDEX_ALTERATION 
 
 ( 
 table_name 
 , 
  
 index_name 
 ); 

Description

Cancels a user-initiated rebuild of a vector index .

Specify the name of the table as a string with the format '[project_id.]dataset.table' and the index name as a string. If you run this system procedure from a different project than the table, then you must include the project ID.

You must run this procedure in the same location as the indexed table. To set the location of your query, see Specify locations .

Example

 CALL 
  
 BQ 
 . 
 CANCEL_INDEX_ALTERATION 
 ( 
 'my_project.my_dataset.indexed_table' 
 , 
  
 'my_index' 
 ); 

BQ.REFRESH_EXTERNAL_METADATA_CACHE

Syntax

  CALL 
  
 BQ 
 . 
 REFRESH_EXTERNAL_METADATA_CACHE 
 
 ( 
 table_name 
  
 [, 
  
 [ 
 subdirectory_uri 
 , 
  
 … 
 ]]); 

Description

Refreshes the metadata cache of a Google Cloud Lakehouse table or an object table. This procedure fails if you run it against a table that has the metadata caching mode set to AUTOMATIC .

To run this system procedure, you need the bigquery.tables.update and bigquery.tables.updateData permissions.

Specify the name of the table as a string with the format '[project_id.]dataset.table' . If you run this system procedure from a different project than the table, then you must include the project ID.

For Lakehouse tables, you can optionally specify one or more subdirectories of the table data directory in Cloud Storage in the format 'gs://table_data_directory/subdirectory/.../' . This lets you refresh only the table metadata from those subdirectories and thereby avoid unnecessary metadata processing.

Examples

To refresh all of the metadata for a table:

 CALL 
  
 BQ 
 . 
 REFRESH_EXTERNAL_METADATA_CACHE 
 ( 
 'myproject.test_db.test_table' 
 ) 

To selectively refresh the metadata for a Lakehouse table:

 CALL 
  
 BQ 
 . 
 REFRESH_EXTERNAL_METADATA_CACHE 
 ( 
 'myproject.test_db.test_table' 
 , 
  
 [ 
 'gs://source/uri/sub/path/d1/*' 
 , 
  
 'gs://source/uri/sub/path/d2/*' 
 ]) 

Limitation

• Metadata cache refresh is not supported for tables referenced by linked datasets over external datasets.
• Metadata cache refresh shouldn't be used in a Multi-statement transaction .

BQ.REFRESH_MATERIALIZED_VIEW

Syntax

  CALL 
  
 BQ 
 . 
 REFRESH_MATERIALIZED_VIEW 
 
 ( 
 view_name 
 ); 

Description

Refreshes a materialized view.

Specify the name of the materialized view as a string with the format '[project_id.]dataset.table' . If you run this system procedure from a different project than the materialized view, then you must include the project ID.

For more information, see Manual refresh .

BQ.SHOW_GRAPH_EXPAND_SCHEMA

Syntax

  CALL 
  
 BQ 
 . 
 SHOW_GRAPH_EXPAND_SCHEMA 
 
 ( 
 graph_name 
 , 
  
 output_schema 
 ); 

Description

Populates the output_schema variable that you provide with the schema of the table returned by calling the GRAPH_EXPAND TVF on graph_name .

Specify the name of the graph as a string with the format '[project_id.]dataset.graph' . Each column returned by the GRAPH_EXPAND TVF represents as property in the graph. The output includes the name, type, and mode for each column. If the property has a description or synonyms defined on it, then those appear in a description field for the column. If the property defines a measure, then the output includes "is_measure":true for that field.

Examples

 DECLARE 
  
 schema 
  
 STRING 
 ; 
 CALL 
  
 BQ 
 . 
 SHOW_GRAPH_EXPAND_SCHEMA 
 ( 
 'my_project.my_dataset.my_graph' 
 , 
  
 schema 
 ); 
 SELECT 
  
 schema 
 ; 

The output looks similar to the following:

{
  "fields":[
    {
      "name":"Department_dept_name",
      "type":"STRING",
      "mode":"NULLABLE",
      "description":
        "{\"description\":\"The name of the academic department\",
          \"synonyms\":[\"division\"]}"
    },
    {
      "name":"Department_budget",
      "type":"FLOAT",
      "mode":"NULLABLE"
    },
    {
      "name":"Department_total_budget",
      "type":"FLOAT",
      "mode":"NULLABLE",
      "is_measure":true
    }
  ]
}