bigquery-sql

About

A bigquery-sql tool executes a pre-defined SQL statement. It’s compatible with the following sources:

• bigquery

The behavior of this tool is influenced by the writeMode setting on its bigquery source:

• allowed (default) and blocked: These modes do not impose any restrictions on the bigquery-sql tool. The pre-defined SQL statement will be executed as-is.
• protected: This mode enables session-based execution. The tool will operate within the same BigQuery session as other tools using the same source, allowing it to interact with temporary resources like TEMP tables created within that session.

GoogleSQL

BigQuery uses GoogleSQL for querying data. The integration with Toolbox supports this dialect. The specified SQL statement is executed, and parameters can be inserted into the query. BigQuery supports both named parameters (e.g., @name) and positional parameters (?), but they cannot be mixed in the same query.

Example

Note: This tool uses parameterized queries to prevent SQL injections. Query parameters can be used as substitutes for arbitrary expressions. Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.

tools:  # Example: Querying a user table in BigQuery  search_users_bq:  kind: bigquery-sql  source: my-bigquery-source  statement: |  SELECT  id,  name,  email  FROM  `my-project.my-dataset.users`  WHERE  id = @id OR email = @email;  description: |  Use this tool to get information for a specific user.  Takes an id number or a name and returns info on the user.   Example:  {{  "id": 123,  "name": "Alice",  }}  parameters:  - name: id  type: integer  description: User ID  - name: email  type: string  description: Email address of the user 

Example with Template Parameters

Note: This tool allows direct modifications to the SQL statement, including identifiers, column names, and table names. This makes it more vulnerable to SQL injections. Using basic parameters only (see above) is recommended for performance and safety reasons. For more details, please check templateParameters.

tools:  list_table:  kind: bigquery-sql  source: my-bigquery-source  statement: |  SELECT * FROM {{.tableName}};  description: |  Use this tool to list all information from a specific table.  Example:  {{  "tableName": "flights",  }}  templateParameters:  - name: tableName  type: string  description: Table to select from 

Reference