import Thumbnail from '@site/src/components/Thumbnail'; import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem';

BigQuery: Extend Schema with Views

What are views?

BigQuery Views can be used to expose the results of a custom query as a virtual table. Views are not persisted physically i.e. the query defining a view is executed whenever data is requested from the view.

Hasura GraphQL Engine lets you expose views over the GraphQL API to allow querying them using both queries and subscriptions just like regular tables.

Creating views {#bigquery-create-views}

Views can be created using SQL which can be run in the Hasura Console:

• Head to the Data -> SQL section of the Hasura Console
• Enter your create view SQL statement
• Hit the Run button

1. Create a migration manually and add your create view SQL statement to the up.sql file. Also, add an SQL statement to the down.sql file that reverts the previous statement.

2. Apply the Migration and Metadata by running:

   bashCopy

   hasura migrate apply
   

You can add a view by using the bigquery_run_sql schema API:

POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bigquery_run_sql",
  "args": {
    "source": "<db_name>",
    "sql": "<create view statement>"
  }
}

Tracking views

Views can be present in the underlying BigQuery database without being exposed over the GraphQL API. In order to expose a view over the GraphQL API, it needs to be tracked.

While creating views from the Data -> SQL page, selecting the Track this checkbox will expose the new view over the GraphQL API right after creation.

You can track any existing views in your database from the Data -> Schema page:

1. To track the view and expose it over the GraphQL API, edit the tables.yaml file in the metadata directory as follows:

   yamlCopy

   - table:
       dataset: my_data
       name: author
   - table:
       dataset: my_data
       name: article
   - table:
       dataset: my_data
       name: <name of view>
   

2. Apply the Metadata by running:

   bashCopy

   hasura metadata apply
   

To track the view and expose it over the GraphQL API, make the following API call to the bigquery_track_table Metadata API:

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

{
  "type":"bigquery_track_table",
  "args": {
    "source": "<db_name>",
    "dataset": "my_data",
    "name": "<name of view>"
  }
}

Use cases

Views are ideal solutions for retrieving some derived data based on some custom business logic.

Let's look at a few example use cases for views:

Example: Group by and then aggregate

Sometimes we might want to fetch some data derived by aggregating (avg, min, max, etc.) over a group of rows in a table.

Let’s say we want to fetch the average article rating for each author in the following schema:

my_data.author(id integer, name string, city string, email string, phone string, address string)

my_data.article(id integer, title string, content string, rating integer, author_id integer)

A view that averages the rating of articles for each author can be created using the following SQL query:

CREATE VIEW my_data.author_average_rating AS (
  SELECT author_id, avg(rating) avg_rating
    FROM my_data.article
    GROUP BY author_id
)

Example: Hide certain fields of a table

Sometimes we might have some sensitive information in a table which we wouldn't want to expose.

Let's say, we want to expose the following author table without the fields email, phone and address:

author(id integer, name string, city string, email string, phone string, address string)

A view that only exposes the non-sensitive fields of the author table can be created using the following SQL query:

CREATE VIEW my_data.author_public AS (
  SELECT id, name, city
    FROM author
)