bigtable-sql - Genai Toolbox

About

A bigtable-sql tool executes a pre-defined SQL statement against a Bigtable instance.

GoogleSQL

Bigtable supports SQL queries. The integration with Toolbox supports googlesql dialect, the specified SQL statement is executed as a data manipulation language (DML) statements, and specified parameters will inserted according to their name: e.g. @name.

{{<notice note>}} Bigtable's GoogleSQL support for DML statements might be limited to certain query types. For detailed information on supported DML statements and use cases, refer to the Bigtable GoogleSQL use cases. {{</notice>}}

Compatible Sources

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.

yaml

kind: tool
name: search_user_by_id_or_name
type: bigtable-sql
source: my-bigtable-instance
statement: |
  SELECT
    TO_INT64(cf[ 'id' ]) as id,
    CAST(cf[ 'name' ] AS string) as name,
  FROM
    mytable
  WHERE
    TO_INT64(cf[ 'id' ]) = @id
    OR CAST(cf[ 'name' ] AS string) = @name;
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: name
    type: string
    description: Name 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.

yaml

kind: tool
name: list_table
type: bigtable-sql
source: my-bigtable-instance
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

field	type	required	description
type	string	true	Must be "bigtable-sql".
source	string	true	Name of the source the SQL should execute on.
description	string	true	Description of the tool that is passed to the LLM.
statement	string	true	SQL statement to execute on.
parameters	parameters	false	List of parameters that will be inserted into the SQL statement.
templateParameters	templateParameters	false	List of templateParameters that will be inserted into the SQL statement before executing prepared statement.

Advanced Usage

Bigtable Studio is a useful to explore and manage your Bigtable data. If you're unfamiliar with the query syntax, Query Builder lets you build a query, run it against a table, and then view the results in the console.
Some Python libraries limit the use of underscore columns such as _key. A workaround would be to leverage Bigtable Logical Views to rename the columns.