Skip to main content
Version: v2.x

BigQuery: Computed Fields

What are computed fields?

Computed fields are virtual fields that are dynamically computed and can be queried along with a table's columns. Computed fields are computed upon request. They are computed by executing user-defined SQL functions using other columns of the table and other custom inputs if needed.

Note

Computed fields are only exposed over the GraphQL API and the BigQuery database schema is not modified on addition of a computed field.

Supported SQL functions

Currently, only table-valued functions, which return a table, can be added as computed fields. Support for computed fields that return a scalar type is being tracked in this GitHub issue.

Creating a table-valued function

A table-valued function can be created using the CREATE TABLE FUNCTION SQL statement. BigQuery infers the returning table type based on the RETURNS TABLE<..> syntax present in the SQL statement.

  • If RETURNS TABLE is included, it specifies the custom schema of the return table as a comma-separated list of column name and data type pairs. While adding such functions as computed fields, it is not required to provide return_table in the definition as BigQuery provides enough information about the returning table schema to Hasura.

    Example:

    CREATE TABLE FUNCTION `google_project.hasura_test.fetch_articles`(a_id INT64, search STRING)
    RETURNS TABLE<id INT64, title STRING, content STRING> AS (
    ( SELECT t.id, t.title, t.content
    FROM hasura_test.article t
    WHERE t.author_id = a_id AND (t.title like `search` or t.content like `search`)
    )
    );
  • If RETURNS TABLE is absent, BigQuery infers the returning table from the query statement present in the function body, implicitly. While adding such functions as computed fields, it is required to provide return_table in the definition as BigQuery does not provide any information about the returning table to Hasura.

    Example:

    CREATE TABLE FUNCTION `google_project.hasura_test.fetch_articles`(a_id INT64, search STRING) AS (
    ( SELECT t.*
    FROM hasura_test.article t
    WHERE t.author_id = a_id AND (t.title like `search` or t.content like `search`)
    )
    );
    Note

    Table functions without RETURNS TABLE should return all columns, possibly by using the* operator, to avoid any execution exceptions when a column that is not being returned is included in the selection set of computed field.

Adding computed fields to a table

Console support will be added soon.

Computed fields permissions

Access control to computed fields depends on the presence of the RETURNS TABLE clause in the creating computed field SQL statement.

If RETURNS TABLE

  • is present, then the function returns a custom table schema. Select permissions are managed through computed_fields in the permission definition.
  • is absent, then the permissions set on return_table are respected.