Error messagesBigQuery

BigQuery error messages

23 BigQuery error messages, each with what it means, the smallest query that reproduces it and how to fix it. Every message was produced on BigQuery, so the text matches what you see.

Each page below starts with the exact text BigQuery prints, produced by running the query on the page. Then it explains what BigQuery is objecting to, gives the smallest query that fails the same way, and lists the fixes. Most of them also say which variants of the message exist and which similar-looking errors mean something else.

Looking for a short overview instead? Common BigQuery errors covers the most frequent ones on one page.

Syntax

Syntax error: Unexpected identifier BigQuery says "Unexpected identifier" when a word turns up where a statement had to start.
Syntax error: Unexpected keyword BigQuery found a reserved word where a name, an expression or a clause had to be.
Syntax error: Expected X but got Y BigQuery was in the middle of a construct and the next token was not one it could accept.
Syntax error: Expected keyword MODULE or keyword PROTO This is what BigQuery says when the first word of your query is import.
Syntax error: WHERE not supported after FROM query A query that starts with FROM is a pipe query, and a bare WHERE cannot follow it.
Syntax error: Expected end of input but got keyword BigQuery read a complete statement and then found more text.

Names

Not found: Table was not found in location BigQuery says a table was not found only when it was willing to look.
Not found: Dataset was not found in location The location at the end of this message is where the job ran, not where the dataset is.
Unrecognized name BigQuery found a name that is not a column, alias or table in scope at that point of the query.
Table "x" must be qualified with a dataset BigQuery reads a table name with no dataset as a table in the default dataset, and there is none.

Grouping

SELECT list expression references column which is neither grouped nor aggregated Once a query has a GROUP BY, every column has to be either grouped or inside an aggregate.

Types

No matching signature for operator BigQuery will not compare a number to a string, not even a string literal.
Cannot access field on a value with type ARRAY You wrote array.field, but a dot only reaches into a STRUCT, and the column is a repeated one.

Subqueries

Scalar subquery produced more than one element A subquery used as a single value returned two or more rows.

Data values

Bad int64 value A CAST turned a string into a number, date or timestamp and one value did not fit.
Failed to parse input string A PARSE_DATE, PARSE_TIMESTAMP, PARSE_DATETIME or PARSE_TIME call got a string that does not fit its format.
division by zero: X / 0 The / operator fails the whole query when one row has a divisor of 0.

DML

UPDATE/MERGE must match at most one source row for each target row A row of the table being updated matched more than one row of the source, so BigQuery has no way to choose which value to write.

Cost

Cannot query over table without a filter over columns that can be used for partition elimination The table has require_partition_filter set, so BigQuery refuses any query that cannot prune partitions before reading.
Quota exceeded: Your project exceeded quota for free query bytes scanned You used the 1 TiB of free monthly query processing and the account has no paid billing attached.

Resources

Resources exceeded during query execution: The query could not be executed in the allotted memory A single worker ran out of memory because of the shape of the query, which has nothing to do with how many bytes it reads.
Response too large to return The result of the query is larger than the response size BigQuery allows, which the quotas page puts at 10 GB compressed.

Permissions

Access Denied: User does not have permission to query table BigQuery sends this one message for a missing permission, a misspelled dataset, a dataset in the wrong case and a project that does not exist.