Save a query as a view
A query you run again and again can have a name. A view is a saved query with a name, and other queries read it as if it were a table.
How it works
CREATE VIEW saves a query under a name and stores no rows. When a query reads the view, BigQuery puts the saved SQL into that query and runs the whole thing.
CREATE OR REPLACE VIEW learn.shop.name AS SELECT ...saves the query. A view lives in a dataset, so its name starts with the dataset. WithOR REPLACE, running the script again changes the view. A plainCREATE VIEWfails withAlready Existswhen the name is taken.- Name every table in the saved query with its dataset, as in
learn.shop.orders, and give every output column a name withAS. Then read the view in aFROMlike a table: filter it, join it, group it. - The view holds no rows. Each read runs the saved SQL on the tables as they are at that moment, so a row added to a table shows in the next read. A table made with
CREATE TABLE ... AS SELECTis a copy of one moment and does not change. - A view has no size of its own. A query on a view is billed for the tables that its SQL reads, as if you had written that SQL in the query yourself. Counting the rows of a view still runs its SQL, and a join reads the join columns of every table in it. BigQuery bills each table a query reads for at least 10 MiB, however small the table is.
- A view can read tables and other views that are stored in a dataset. It cannot read a temporary table.
- A view is read-only.
INSERT,UPDATEandDELETEon a view fail withis not allowed for this operation because it currently has type VIEW. To change what a view returns, replace it with a new query.
Common mistake
A report that must not change after you send it needs a saved table. A view would show every later change to the tables.
Worked example
CREATE OR REPLACE VIEW learn.shop.pricey_products AS
SELECT product_id, product_name, price
FROM `learn.shop.products`
WHERE price >= 200;
SELECT product_name, price
FROM learn.shop.pricey_products
ORDER BY price DESC;The first statement saves the query. Nothing is computed and no rows are stored, so the statement is over at once.
The last query reads the view in its FROM. BigQuery runs the saved SELECT with its WHERE and returns the three products that cost 200 or more.