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 which take columns of the table, and other custom values if needed, as inputs to compute the field.

Computed fields do not modify the database schema

Computed fields are only exposed over the GraphQL API and the database schema is not modified on addition of a computed field. i.e. a computed field cannot be fetched as a table column directly from the database.

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

Console support will be added soon.

CLI

You can add a computed field by updating the metadata > databases > [db-name] > tables > [table_name].yaml file:

- table:
    dataset: hasura
    name: authors
  computed_fields:
    - name: fetch_articles
      definition:
        function:
          dataset: hasura
          name: search_articles
        return_table:
          dataset: hasura
          name: articles
        argument_mapping:
          author_id_arg: author_id
      comment: ''

Apply the Metadata by running:

hasura metadata apply

API

A computed field can be added to a table using the bigquery_add_computed_field Metadata API:

POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bigquery_add_computed_field",
  "args": {
    "source": "<db_name>",
    "table": {
      "name": "author",
      "dataset": "hasura"
    },
    "name": "fetch_articles",
    "definition": {
      "function": {
        "name": "fetch_articles",
        "dataset": "hasura"
      },
      "return_table": {
        "name": "article",
        "dataset": "hasura"
      },
      "argument_mapping": {
        "author_id_arg": "author_id"
      }
    }
  }
}

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.