How to create a view in BigQuery?
Contents
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
|
1 2 3 4 |
CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] view_name [(view_column_name_list)] [OPTIONS(view_option_list)] AS query_expression |
- 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.
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.
|
1 2 |
CREATE VIEW rc_merc_pay_views.merchant_txn AS SELECT * FROM rc_merc_pay_tables.merchant_txn; |
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.
|
1 2 |
CREATE VIEW <project_id>.<dataset_name>.<view_name> AS SELECT * FROM <project_id>.<dataset_name>.<table_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.
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.
|
1 2 3 4 5 6 |
CREATE OR REPLACE VIEW rc_merc_pay_views.merchant_txn AS SELECT merchant_id, merchant_name, merchant_type, txn_date FROM rc_merc_pay_tables.merchant_txn; |
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.
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
- Create table as Select, Create table Copy and Create table Like in BigQuery
- How to create an external table in BigQuery?
- How to add a column to existing table in BigQuery?
- How to add partition to existing table in BigQuery?
