Using variables in BigQuery view definition

Question

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?

Views are static, so I'm not sure why you want to use a variable in a view (as Gordon points out, this is not possible) but a stored procedure might be more in line with what you are trying to do: cloud.google.com/blog/products/data-analytics/… — Nathan Griffiths
@NathanGriffiths the query I gave is just an example. In reality I need to reference the color red in 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. — flyingL123
If you think the value of color might change over time then a better approach could be to create a stored procedure that accepts color as a parameter and then executes your query. — Nathan Griffiths

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-01-15T20:56:37

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.

Using variables in BigQuery view definition

1 Answers