How to create a view in BigQuery?

Posted on by

Views in BigQuery

A view is a virtual table. It logically represents subsets of data from one or more tables. There are different ways to create views in BigQuery

  • Using Google Cloud Console
  • Using bq command-line tool’s bq mk command
  • Calling the tables.insert API method
  • Using Google cloud client libraries
  • Submitting CREATE VIEW statement

In this tutorial, we will create a view using CREATE VIEW data definition language (DDL) statement.

Syntax of Create View Statement in BigQuery

  • OR REPLACE – It is an optional argument. It is used to update the existing views. It cannot be appear with IF NOT EXISTS.
  • IF NOT EXISTS – It is an optional argument. If the given view name is already exist, CREATE view statement has no effect. It cannot appear with OR REPLACE.
  • view_name – It is a name of the view which we want to create in BigQuery.
  • view_column_name_list – It is an optional argument. The column name of the view can be defined using this list. These column names must be unique. If we don’t specify the column names, it will take the column names from the underlying SQL query.
  • view_option_list – It is an optional argument. It allows us to define the view options such as description, expiration time and label.
  • query_expression – It is a select query which defines the contents of the view.

Example 1: Create views in BigQuery

Suppose we have a table merchant_txn in the dataset rc_merc_pay_tables as below. Let’s create a view for this table.

Create view example in BigQuery
Create view example in BigQuery

To maintain the views in a separate dataset, we have created a new dataset with the name of rc_merc_pay_views. The below CREATE VIEW statement is going to create a view merchant_txn in the newly created dataset.

CREATE VIEW statement in BigQuery
CREATE VIEW statement in BigQuery

In this example, we have used the table name as view name. But we can specify any names for views. Also if we run the CREATE VIEW statement outside of cloud console, we need to specify the project id along with data set name.

Now we can query the data from the view rc_merc_pay_views.merchant_txn. As we shown below, it fetched all the columns from the underlying table. Similarly we can create a view with specific columns of the table.

Newly created view in BigQuery
Newly created view in BigQuery

Example 2: Replacing a views in BigQuery

Let’s say that we don’t want to show the Average transaction detail to other users. In that case, we can create a view with specific columns.

In this example, we will replace the existing view rc_merc_pay_views.merchant_txn with specific columns.

CREATE OR REPLACE VIEW in BigQuery
CREATE OR REPLACE VIEW in BigQuery

As we shown above, we wrote the CREATE OR REPLACE VIEW statement to update the existing view rc_merc_pay_views.merchant_txn. Also the select query of the view statement doesn’t have the column Avg_txn_per_day. Let’s query the view to check the data.

CREATE OR REPLACE VIEW example in BigQuery
CREATE OR REPLACE VIEW example in BigQuery

Now the Average transaction detail of merchant is not visible in the view. To hide the sensitive data on a table, we can create a view in BigQuery.

Recommended Articles