BigQuery: Create Relationships

Introduction

A relationship from one table or Native Query to another can be created by defining a link between a column of the table to a column of the other table.

Typically, relationships are defined using foreign-key constraints. Because BigQuery doesn't support a notion of primary or foreign keys, we can instead model this relationship using manual object and array relationships.

Using manual relationships

Say we created two tables, authors(id, name) and articles(id, title, content, rating, author_id).

Let us now connect these tables to enable nested queries using manual relationships.

Add an object relationship

Each article has one author. This is an object relationship.

Console

In the Console, select the articles table and navigate to the Relationships tab, then click Configure under Add a new relationship manually.

Add an object relationship named author for the articles table as shown here:

API

You can create an object relationship by using the bigquery_create_object_relationship Metadata API:

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

{
  "type": "bigquery_create_object_relationship",
  "args": {
    "source": "bigquery",
    "table": {
      "dataset": "<dataset_name>",
      "name": "articles"
    },
    "name": "author",
    "using": {
      "manual_configuration": {
        "remote_table": {
          "dataset": "<dataset_name>",
          "name": "authors"
        },
        "column_mapping": {
          "author_id": "id"
        }
      }
    }
  }
}

If we now click the API link along the top navigation bar, we should be able to see GraphiQL.

If we run the following query, we can see that we've now added an author object relationship under the bigquery_articles table:

GraphiQL

Query Variables

Request Headers

Documentation Explorer

⚲

No Schema Available

Note

The query field will be of the format <dataset_name>_<table_name>.

Add an array relationship

For each author, there are many possible related articles. If we wanted to establish the articles for each author, we would return an array. We model this in the Hasura Console using an array relationship (we can also think of this as being a one-to-many relationship between authors and articles).

Console

In the Console, select the authors table and navigate to the Relationships tab, then click Configure under Add a new relationship manually.

API

You can create an array relationship by using the bigquery_create_array_relationship Metadata API:

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

{
  "type": "bigquery_create_array_relationship",
  "args": {
    "source": "bigquery",
    "table": {
      "dataset": "hasura",
      "name": "authors"
    },
    "name": "articles",
    "using": {
      "manual_configuration": {
        "remote_table": {
          "dataset": "hasura",
          "name": "articles"
        },
        "column_mapping": {
          "id": "author_id"
        }
      }
    }
  }
}

If we now click the API link along the top navigation bar, we should be able to see GraphiQL.

If we run the following query, we can see that we've now added an articles array relationship under the bigquery_authors table:

GraphiQL

Query Variables

Request Headers

Documentation Explorer

⚲

No Schema Available

Note

The query field will be of the format <dataset_name>_<table_name>.

Tracking relationships between tables and Native Queries

As mentioned in the Introduction section above, a relationship from a table to a Native Query can only be set up manually.

API

Given a table named articles and an existing Native Query named get_author, we can set up a relationship between the two.

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

{
  "type": "bulk",
  "args": [
    {
      "type": "bigquery_create_object_relationship",
      "args": {
        "source": "<db_name>",
        "table": "articles",
        "name": "author",
        "using": {
          "manual_configuration": {
            "remote_native_query": "get_author",
            "column_mapping": {
              "id": "author_id"
            }
          }
        }
      }
    }
  ]
}