A BigQuery statement comprises a series of tokens. Tokens include identifiers, quoted identifiers, literals, keywords, operators, and special characters. You can separate tokens with whitespace (for example, space, backspace, tab, newline) or comments.
Identifiers are names that are associated with columns, tables, and other database objects. They can be unquoted or quoted.
- Identifiers can be used in path expressions that return a
- Some identifiers are case-sensitive and some are not. For details, see Case Sensitivity.
- Unquoted identifiers must begin with a letter or an underscore character. Subsequent characters can be letters, numbers, or underscores.
- Quoted identifiers must be enclosed by backtick (`) characters.
- Quoted identifiers can contain any character, such as spaces or symbols.
- Quoted identifiers cannot be empty.
- Quoted identifiers have the same escape sequences as string literals.
- A reserved keyword must be a quoted identifier if it is a standalone keyword or the first component of a path expression. It may be unquoted as the second or later component of a path expression.
- Table name identifiers have additional syntax to support dashes (-)
when referenced in
These are valid identifiers:
Customers5 `5Customers` dataField _dataField1 ADGROUP `tableName~` `GROUP`
These path expressions contain valid identifiers:
foo.`GROUP` foo.GROUP foo().dataField list[OFFSET(3)].dataField list[ORDINAL(3)].dataField @parameter.dataField
These are invalid identifiers:
5Customers _dataField! GROUP
5Customers begins with a number, not a letter or underscore.
contains the special character "!" which is not a letter, number, or underscore.
GROUP is a reserved keyword, and therefore cannot be used as an identifier
without being enclosed by backtick characters.
You do not need to enclose table names that include dashes with backticks. These are equivalent:
SELECT * FROM data-customers-287.mydatabase.mytable
SELECT * FROM `data-customers-287`.mydatabase.mytable
A literal represents a constant value of a built-in data type. Some, but not all, data types can be expressed as literals.
String and bytes literals
Both string and bytes literals must be quoted, either with single (
") quotation marks, or triple-quoted with groups of three single
''') or three double (
""") quotation marks.
||Quoted strings enclosed by single (
Quoted strings cannot contain newlines, even when preceded by a backslash (
||Embedded newlines and quotes are allowed without escaping - see fourth example.
A trailing unescaped backslash (
End the string with three unescaped quotes in a row that match the starting quotes.
||Quoted or triple-quoted literals that have the raw string literal prefix (
Backslash characters (
A raw string cannot end with an odd number of backslashes.
Raw strings are useful for constructing regular expressions.
Prefix characters (
B) are optional for quoted or triple-quoted strings, and indicate that the string is a raw/regex string or a byte sequence, respectively. For
b'''abc''' are both interpreted as type bytes. Prefix characters are case insensitive.
Quoted literals with prefixes:
||Quoted or triple-quoted literals that have the bytes literal prefix (
The table below lists all valid escape sequences for representing non-alphanumeric characters in string and byte literals. Any sequence not in this table produces an error.
||Question Mark (
||Double Quote (
||Single Quote (
||Octal escape, with exactly 3 digits (in the range 0–7). Decodes to a single Unicode character (in string literals) or byte (in bytes literals).|
||Hex escape, with exactly 2 hex digits (0–9 or A–F or a–f). Decodes to a single Unicode character (in string literals) or byte (in bytes literals). Examples:
||Unicode escape, with lowercase 'u' and exactly 4 hex digits. Valid only in string literals or identifiers.
Note that the range D800-DFFF is not allowed, as these are surrogate unicode values.
||Unicode escape, with uppercase 'U' and exactly 8 hex digits. Valid only in string literals or identifiers.
The range D800-DFFF is not allowed, as these values are surrogate unicode values. Also, values greater than 10FFFF are not allowed.
Integer literals are either a sequence of decimal digits (0–9) or a hexadecimal
value that is prefixed with "
0x" or "
0X". Integers can be prefixed by "
-" to represent positive and negative values, respectively.
123 0xABC -123
An integer literal is interpreted as an
You can construct NUMERIC literals using the
NUMERIC keyword followed by a floating point value in quotes.
SELECT NUMERIC '0'; SELECT NUMERIC '123456'; SELECT NUMERIC '-3.14'; SELECT NUMERIC '-0.54321'; SELECT NUMERIC '1.23456e05'; SELECT NUMERIC '-9.876e-3';
You can construct
BIGNUMERIC literals using the
BIGNUMERIC keyword followed
by a floating point value in quotes.
SELECT BIGNUMERIC '0'; SELECT BIGNUMERIC '123456'; SELECT BIGNUMERIC '-3.14'; SELECT BIGNUMERIC '-0.54321'; SELECT BIGNUMERIC '1.23456e05'; SELECT BIGNUMERIC '-9.876e-3';
Floating point literals
[+-]DIGITS.[DIGITS][e[+-]DIGITS] [DIGITS].DIGITS[e[+-]DIGITS] DIGITSe[+-]DIGITS
DIGITS represents one or more decimal numbers (0 through 9) and
e represents the exponent marker (e or E).
123.456e-67 .1E4 58. 4e2
Numeric literals that contain either a decimal point or an exponent marker are presumed to be type double.
Implicit coercion of floating point literals to float type is possible if the value is within the valid float range.
There is no literal representation of NaN or infinity, but the following case-insensitive strings can be explicitly cast to float:
- "inf" or "+inf"
Array literals are comma-separated lists of elements
enclosed in square brackets. The
ARRAY keyword is optional, and an explicit
element type T is also optional.
[1, 2, 3] ['x', 'y', 'xy'] ARRAY[1, 2, 3] ARRAY<string>['x', 'y', 'xy'] ARRAY<int64>
A struct literal is a struct whose fields are all literals. Struct literals can be written using any of the syntaxes for constructing a struct (tuple syntax, typeless struct syntax, or typed struct syntax).
Note that tuple syntax requires at least two fields, in order to distinguish it from an ordinary parenthesized expression. To write a struct literal with a single field, use typeless struct syntax or typed struct syntax.
Date literals contain the
DATE keyword followed by a string literal that conforms to the canonical date format, enclosed in single quotation marks. Date literals support a range between the
years 1 and 9999, inclusive. Dates outside of this range are invalid.
For example, the following date literal represents September 27, 2014:
String literals in canonical date format also implicitly coerce to DATE type when used where a DATE-type expression is expected. For example, in the query
SELECT * FROM foo WHERE date_col = "2014-09-27"
the string literal
"2014-09-27" will be coerced to a date literal.
Time literals contain the
TIME keyword and a string literal that conforms to
the canonical time format, enclosed in single quotation marks.
For example, the following time represents 12:30 p.m.:
DATETIME 'YYYY-[M]M-[D]D [[H]H:[M]M:[S]S[.DDDDDD]]'
Datetime literals contain the
DATETIME keyword and a string literal that
conforms to the canonical datetime format, enclosed in single quotation marks.
For example, the following datetime represents 12:30 p.m. on September 27, 2014:
DATETIME '2014-09-27 12:30:00.45'
Datetime literals support a range between the years 1 and 9999, inclusive. Datetimes outside of this range are invalid.
String literals with the canonical datetime format implicitly coerce to a datetime literal when used where a datetime expression is expected.
SELECT * FROM foo WHERE datetime_col = "2014-09-27 12:30:00.45"
In the query above, the string literal
"2014-09-27 12:30:00.45" is coerced to
a datetime literal.
A datetime literal can also include the optional character
is a flag for time and is used as a separator between the date and time. If
you use this character, a space can't be included before or after it.
These are valid:
DATETIME '2014-09-27T12:30:00.45' DATETIME '2014-09-27t12:30:00.45'
TIMESTAMP 'YYYY-[M]M-[D]D [[H]H:[M]M:[S]S[.DDDDDD] [timezone]]'
Timestamp literals contain the
TIMESTAMP keyword and a string literal that
conforms to the canonical timestamp format, enclosed in single quotation marks.
Timestamp literals support a range between the years 1 and 9999, inclusive. Timestamps outside of this range are invalid.
A timestamp literal can include a numerical suffix to indicate the time zone:
TIMESTAMP '2014-09-27 12:30:00.45-08'
If this suffix is absent, the default time zone, UTC, is used.
For example, the following timestamp represents 12:30 p.m. on September 27, 2014 in the default time zone, UTC:
TIMESTAMP '2014-09-27 12:30:00.45'
For more information about time zones, see Time zone.
String literals with the canonical timestamp format, including those with
time zone names, implicitly coerce to a timestamp literal when used where a
timestamp expression is expected. For example, in the following query, the
"2014-09-27 12:30:00.45 America/Los_Angeles" is coerced
to a timestamp literal.
SELECT * FROM foo WHERE timestamp_col = "2014-09-27 12:30:00.45 America/Los_Angeles"
A timestamp literal can include these optional characters:
t: A flag for time. Use as a separator between the date and time.
z: A flag for the default timezone. This cannot be used with
If you use one of these characters, a space can't be included before or after it. These are valid:
TIMESTAMP '2017-01-18T12:34:56.123456Z' TIMESTAMP '2017-01-18t12:34:56.123456' TIMESTAMP '2017-01-18 12:34:56.123456z' TIMESTAMP '2017-01-18 12:34:56.123456Z'
Since timestamp literals must be mapped to a specific point in time, a time zone is necessary to correctly interpret a literal. If a time zone is not specified as part of the literal itself, then BigQuery uses the default time zone value, which the BigQuery implementation sets.
BigQuery represents time zones using strings in the following canonical format, which represents the offset from Coordinated Universal Time (UTC).
'-08:00' '-8:15' '+3:00' '+07:30' '-7'
Time zones can also be expressed using string time zone names from the
tz database. For a less
comprehensive but simpler reference, see the
List of tz database time zones
on Wikipedia. Canonical time zone names have the format
<continent/[region/]city>, such as
TIMESTAMP '2014-09-27 12:30:00 America/Los_Angeles' TIMESTAMP '2014-09-27 12:30:00 America/Argentina/Buenos_Aires'
INTERVAL 'N' datetime_part INTERVAL '[Y]-[M] [D] [H]:[M]:[S].[F]' datetime_part TO datetime_part
datetime_part is one of
Interval literals come in two forms. The first form has a single datetime part. For example:
INTERVAL '5' DAY INTERVAL '0.001' SECOND
The second form allows multiple consecutive datetime parts. For example:
-- 10 hours, 20 minutes, 30 seconds INTERVAL '10:20:30' HOUR TO SECOND -- 1 year, 2 months INTERVAL '1-2' YEAR TO MONTH -- 1 month, 15 days INTERVAL '1 15' MONTH TO DAY -- 1 day, 5 hours, 30 minutes INTERVAL '1 5:30' DAY TO MINUTE
BigQuery follows these rules for case sensitivity:
|Built-in Function names||No|
|User-Defined Function names||Yes|
|Aliases within a query||No|
|Regular expression matching||See Notes||Regular expression matching is case sensitive by default, unless the expression itself specifies that it should be case insensitive.|
Keywords are a group of tokens that have special meaning in the BigQuery language, and have the following characteristics:
- Keywords cannot be used as identifiers unless enclosed by backtick (`) characters.
- Keywords are case insensitive.
BigQuery has the following reserved keywords.
You can optionally use a terminating semicolon (
;) when you submit a query
string statement through an Application Programming Interface (API).
In a request containing multiple statements, you must separate statements with semicolons, but the semicolon is generally optional after the final statement. Some interactive tools require statements to have a terminating semicolon.
You can optionally use a trailing comma (
,) at the end of a column list in a
SELECT statement. You might have a trailing comma as the result of
programmatically creating a column list.
SELECT name, release_date, FROM Books
You can use query parameters to substitute arbitrary expressions. However, query parameters cannot be used to substitute identifiers, column names, table names, or other parts of the query itself. Query parameters are defined outside of the query statement.
Client APIs allow the binding of parameter names to values; the query engine substitutes a bound value for a parameter at execution time.
Query parameters cannot be used in the SQL body of these statements:
CREATE MATERIALIZED VIEW, and
Named query parameters
This example returns all rows where
LastName is equal to the value of the
named query parameter
SELECT * FROM Roster WHERE LastName = @myparam
Positional query parameters
Positional query parameters are denoted using the
Positional parameters are evaluated by the order in which they are passed in.
Positional query parameters cannot be used
alongside named query parameters.
This query returns all rows where
FirstName are equal to the
values passed into this query. The order in which these values are passed in
matters. If the last name is passed in first, followed by the first name, the
expected results will not be returned.
SELECT * FROM Roster WHERE FirstName = ? and LastName = ?
Comments are sequences of characters that the parser ignores. BigQuery supports the following types of comments.
Use a single-line comment if you want the comment to appear on a line by itself.
# this is a single-line comment SELECT book FROM library;
-- this is a single-line comment SELECT book FROM library;
/* this is a single-line comment */ SELECT book FROM library;
SELECT book FROM library /* this is a single-line comment */ WHERE book = "Ulysses";
Use an inline comment if you want the comment to appear on the same line as
a statement. A comment that is prepended with
-- must appear to the
right of a statement.
SELECT book FROM library; # this is an inline comment
SELECT book FROM library; -- this is an inline comment
SELECT book FROM library; /* this is an inline comment */
SELECT book FROM library /* this is an inline comment */ WHERE book = "Ulysses";
Use a multiline comment if you need the comment to span multiple lines. Nested multiline comments are not supported.
SELECT book FROM library /* This is a multiline comment on multiple lines */ WHERE book = "Ulysses";
SELECT book FROM library /* this is a multiline comment on two lines */ WHERE book = "Ulysses";