BigQuery Create View

In BigQuery, a view is just a stored SQL query. A View does not store any data and are built on top of tables. When the view is queried, the underlying SQL is executed and the results are returned to the user. When the data in the table changes, the data in the view also automatically changes to reflect the same

Create view example

    CREATE OR REPLACE VIEW `myproject.mydataset.newview`
    AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

Creating a view with options

    CREATE OR REPLACE VIEW `myproject.mydataset.newview`
    OPTIONS(
      expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
      friendly_name="newview",
      description="a view that expires in 2 days",
      labels=[("org_unit", "development")]
    )
    AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

Creating a view only if the view doesn't exist

    CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview`
    AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

Explanation

After running the above CREATE VIEW query in the project myProject, you can run the query SELECT * FROM myProject.myDataset.myView. Regardless of the project you choose to run this SELECT query, the referenced table anotherDataset.myTable is always resolved against project myProject.

Syntax reference

{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW}
[[project_name.]dataset_name.]view_name [(view_column_name_list)]
[OPTIONS(view_option_list)]
AS query_expression