I have a simple query that runs successfully, but when I introduce a variable into the query, I am unable to save a view using that query. For example:
SELECT * FROM mytable WHERE color = 'red';
This query runs fine. Then:
DECLARE color STRING DEFAULT 'red';
SELECT * FROM mytable WHERE color = color;
This query also runs fine. Then in the BigQuery UI I click to "Save view", but I get an error saying Unexpected keyword DECLARE. Why is that?
As explained in the documentation:
BigQuery views are subject to the following limitations:
- You cannot reference query parameters in views.
What you want to do is not allowed. A view is limited to a single SELECT statement.
If you're interested in using a variable more as a named constant than something you expect to be provided dynamically via query parameters, you do have a couple options, even within a view:
WITH constants AS (
SELECT
'red' AS color
)
SELECT *
FROM mytable
WHERE fillColor = (select color from constants);
... or, more succinctly (but using a more arcane cross-join syntax):
WITH constants AS (
SELECT
'red' AS color
)
SELECT *
FROM mytable, constants
WHERE fillColor = color;
I've found this kind of thing useful in date-snapshotted data warehouses, for example:
WITH constants AS (
SELECT
DATE_SUB(CURRENT_DATE("America/Los_Angeles"), INTERVAL 1 DAY) AS latest_ds
)
SELECT *
FROM modelSnapshots, constants
WHERE ds = latest_ds;
redin a few places throughout the query, so I thought using a variable would allow me to edit it in one place in case it ever needs to change.