TIBCO Data Virtualization® Adapter Online Help > TDV Google BigQuery Adapter > SQL Compliance > SELECT Statements
 
SELECT Statements
Google BigQuery API Syntax
The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
SELECT Syntax
The following syntax diagram outlines the syntax supported by the Google BigQuery adapter:
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[
ORDER BY
{ <column_reference> [ ASC | DESC ] } [ , ... ]
]
[
LIMIT <expression>
]
} | SCOPE_IDENTITY()
 
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| <literal>
| <sql_function>
 
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR } [ <expression> ]
} [ { AND | OR } ... ]
Examples
Return all columns:
SELECT * FROM publicdata:samples.github_nested
Rename a column:
SELECT "repository.name" AS MY_repository.name FROM publicdata:samples.github_nested
Search data:
SELECT * FROM publicdata:samples.github_nested WHERE repository.name = 'EntityFramework';
The Google BigQuery APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR.
SELECT * FROM publicdata:samples.github_nested WHERE repository.name = 'EntityFramework';
Return the number of items matching the query criteria:
SELECT COUNT(*) AS MyCount FROM publicdata:samples.github_nested
Return the unique items matching the query criteria:
SELECT DISTINCT repository.name FROM publicdata:samples.github_nested
Summarize data:
SELECT repository.name, MAX(repository.watchers) FROM publicdata:samples.github_nested GROUP BY repository.name
See Aggregate Functions for details.
Retrieve data from multiple tables.
SELECT * FROM CRMAccounts INNER JOIN ERPCustomers ON CRMAccounts.BillingState = ERPCustomers.BillingState
See JOIN Queries for details.
Sort a result set in ascending order:
SELECT actor.attributes.email, repository.name FROM publicdata:samples.github_nested ORDER BY repository.name ASC
Aggregate Functions
Examples of Aggregate Functions
Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.
COUNT
Returns the number of rows matching the query criteria.
SELECT COUNT(*) FROM publicdata:samples.github_nested WHERE repository.name = 'EntityFramework'
COUNT(DISTINCT)
Returns the number of distinct, non-null field values matching the query criteria.
SELECT COUNT(DISTINCT actor.attributes.email) AS DistinctValues FROM publicdata:samples.github_nested WHERE repository.name = 'EntityFramework'
AVG
Returns the average of the column values.
SELECT repository.name, AVG(repository.watchers) FROM publicdata:samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name
MIN
Returns the minimum column value.
SELECT MIN(repository.watchers), repository.name FROM publicdata:samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name
MAX
Returns the maximum column value.
SELECT repository.name, MAX(repository.watchers) FROM publicdata:samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name
SUM
Returns the total sum of the column values.
SELECT SUM(repository.watchers) FROM publicdata:samples.github_nested WHERE repository.name = 'EntityFramework'
CORR
Returns the Pearson correlation coefficient of a set of number pairs.
SELECT repository.name, CORR(repository.watchers, repository.size) FROM publicdata:samples.github_nested
COVAR_POP
Computes the population covariance of the values computed by a set of number pairs.
SELECT repository.name, COVAR_POP(repository.watchers, repository.size) FROM publicdata:samples.github_nested
COVAR_SAMP
Computes the sample covariance of the values computed by a set of number pairs.
SELECT repository.name, COVAR_SAMP(repository.watchers, repository.size) FROM publicdata:samples.github_nested
NTH
Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL.
SELECT repository.name, NTH(n, actor.attributes.email) FROM publicdata:samples.github_nested
STDDEV
Returns the standard deviation of the computed values. Rows with a NULL value are not included in the calculation.
SELECT repository.name, STDDEV(repository.watchers) FROM publicdata:samples.github_nested
JOIN Queries
The adapter supports the complete join syntax in Google BigQuery. Google BigQuery supports inner joins, outer joins, and cross joins. The default is inner. Multiple join operations are supported.
SELECT field_1 [..., field_n] FROM
table_1 [[AS] alias_1]
[[INNER|[FULL|RIGHT|LEFT] OUTER|CROSS] JOIN [EACH]
table_2 [[AS] alias_2]
[ON join_condition_1 [... AND join_condition_n]]
]+
Note that the default join is an inner join. The following limitations exist on joins in Google BigQuery:
Cross joins must not contain an ON clause.
Normal joins require that the right-side table must contain less than 8 MB of compressed data. If you are working with tables larger than 8 MB, use the EACH modifier. Note that EACH cannot be used in cross joins.
Projection Functions
ANY_VALUE(expression)
Returns any value from the input or NULL if there are zero input rows. The value returned is non-deterministic, which means you might receive a different result each time you use this function.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to retrieve a value from.
APPROX_COUNT_DISTINCT(expression)
Returns the approximate result for COUNT(DISTINCT expression). The value returned is a statistical estimate-not necessarily the actual value. This function is less accurate than COUNT(DISTINCT expression), but performs better on huge input.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the approximate count distinct on.
APPROX_QUANTILES(expression, number)
Returns the approximate boundaries for a group of expression values, where number represents the number of quantiles to create. This function returns an array of number + 1 elements, where the first element is the approximate minimum and the last element is the approximate maximum.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the approximate quantiles on.
number: The number of quantiles to create.
APPROX_TOP_COUNT(expression, number)
Returns the approximate top elements of expression. The number parameter specifies the number of elements returned.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the approximate top count on.
number: The number of elements to be returned.
APPROX_TOP_SUM(expression, weight, number)
Returns the approximate top elements of expression, based on the sum of an assigned weight. The number parameter specifies the number of elements returned. If the weight input is negative or NaN, this function returns an error.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the approximate top sum on.
weight: The assigned weight.
number: The number of elements to be returned.
ARRAY_AGG(expression)
Returns an ARRAY of expression values.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression values to generate an array from.
ARRAY_CONCAT_AGG(expression1[, expression2][,...])
Concatenates elements from expression of type ARRAY, returning a single ARRAY as a result. This function ignores NULL input arrays, but respects the NULL elements in non-NULL input arrays (an error is raised, however, if an array in the final query result contains a NULL element).
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression1: The first expression to concatenate.
expression2: The first expression to concatenate.
AVG(DISTINCT expression)
Returns the average on non-null values. Each distinct value of expression is aggregated only once into the result.
expression: The expression to use to compute the average.
BIT_AND(numeric_expression)
Returns the result of a bitwise AND operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.
numeric_expression: The numeric expression to perform the bitwise operation.
BIT_COUNT(expression)
The input, expression, must be an integer or BYTES. Returns the number of bits that are set in the input expression. For integers, this is the number of bits in two's complement form.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the bit count operation on.
BIT_OR(numeric_expression)
Returns the result of a bitwise OR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.
numeric_expression: The numeric expression to perform the bitwise operation.
BIT_XOR(numeric_expression)
Returns the result of a bitwise XOR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.
numeric_expression: The numeric expression to perform the bitwise operation.
CORR(numeric_expression, numeric_expression)
Returns the Pearson correlation coefficient of a set of number pairs.
numeric_expression: The first series.
numeric_expression: The second series.
COUNTIF(expression)
Returns the count of TRUE values for expression. Returns 0 if there are zero input rows or expression evaluates to FALSE for all rows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to evaluate.
COVAR_POP(numeric_expression1, numeric_expression2)
Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.
numeric_expression: The first series.
numeric_expression: The second series.
COVAR_SAMP(numeric_expression1, numeric_expression2)
Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.
numeric_expression: The first series.
numeric_expression: The second series.
CURRENT_DATE()
Returns a human-readable string of the current date in the format %Y-%m-%d.
CURRENT_TIME()
Returns a human-readable string of the server's current time in the format %H:%M:%S.
CURRENT_TIMESTAMP()
Returns a TIMESTAMP data type of the server's current time in the format %Y-%m-%d %H:%M:%S.
FIRST(column)
Returns the first sequential value in the scope of the function.
Note: this function is only available when UseLegacySQL=True.
column: Any column expression.
FIRST_VALUE(value_expression)
Returns the value of the value_expression for the first row in the current window frame.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
value_expression: Any value expression
GROUP_CONCAT(string_expression [, separator])
Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, returns a comma-separated string.
Note: this function is only available when UseLegacySQL=True.
string_expression: The string expression to concat.
separator: The separator.
GROUP_CONCAT_UNQUOTED(string_expression [, separator])
Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, BigQuery returns a comma-separated string. Unlike GROUP_CONCAT, this function will not add double quotes to returned values that include a double quote character. For example, the string a"b would return as a"b.
Note: this function is only available when UseLegacySQL=True.
string_expression: The string expression to concat.
separator: The separator.
LAST(column)
Returns the last sequential value in the scope of the function.
Note: this function is only available when UseLegacySQL=True.
column: Any column expression
LAST_VALUE(value_expression)
Returns the value of the value_expression for the last row in the current window frame.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
value_expression: Any value expression
LOGICAL_AND(expression)
Returns the logical AND of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the logical AND on.
LOGICAL_OR(expression)
Returns the logical OR of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the logical OR on.
NEST(expression)
Aggregates all values in the current aggregation scope into a repeated field. For example, the query SELECT x, NEST(y) FROM ... GROUP BY x returns one output record for each distinct x value, and contains a repeated field for all y values paired with x in the query input. The NEST function requires a GROUP BY clause.
Note: this function is only available when UseLegacySQL=True.
expression: Any expression.
NOW()
Returns the current UNIX timestamp in microseconds.
Note: this function is only available when UseLegacySQL=True.
NTH(n, field)
Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL.
Note: this function is only available when UseLegacySQL=True.
n: The nth sequential value.
field: The column name.
NTH_VALUE(value_expression, constant_integer_expression)
Returns the value of value_expression at the Nth row of the current window frame, where Nth is defined by constant_integer_expression. Returns NULL if there is no such row.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
value_expression: Any value expression.
constant_integer_expression: The nth sequential value.
QUANTILES(expression [, buckets])
Computes approximate minimum, maximum, and quantiles for the input expression. NULL input values are ignored. Empty or exclusively-NULL input results in NULL output. The number of quantiles computed is controlled with the optional buckets parameter, which includes the minimum and maximum in the count.
Note: this function is only available when UseLegacySQL=True.
expression: The numeric expression to compute quantiles on.
buckets: The number of buckets.
STDDEV(numeric_expression)
Returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.
numeric_expression: The series to calculate STDDEV on.
STDDEV_POP(numeric_expression)
Computes the population standard deviation of the value computed by numeric_expr.
numeric_expression: The series to calculate STDDEV on.
STDDEV_SAMP(numeric_expression)
Computes the sample standard deviation of the value computed by numeric_expr.
numeric_expression: The series to calculate STDDEV on.
STRING_AGG(expression[, delimiter])
Returns a value (either STRING or BYTES) obtained by concatenating non-null values. If a delimiter is specified, concatenated values are separated by that delimiter; otherwise, a comma is used as a delimiter.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The string expression to concatenate.
delimiter: The delimiter to separate concatenated values.
SUM(DISTINCT expression)
Returns the sum on non-null values. Each distinct value of expression is aggregated only once into the result.
expression: The expression to use to compute the sum.
TOP(column [,max_values][,multiplier])
TOP is a function that is an alternative to the GROUP BY clause. It is used as simplified syntax for GROUP BY ... ORDER BY ... LIMIT .... Generally, the TOP function performs faster than the full ... GROUP BY ... ORDER BY ... LIMIT ... query, but may only return approximate results.
Note: this function is only available when UseLegacySQL=True.
numeric_expression: The series to calculate STDDEV on.
max_values: The maximum number of results to return. Default is 20.
multiplier: A positive integer that increases the value(s) returned by COUNT(*) by the multiple specified.
UNIQUE(expression)
Returns the set of unique, non-NULL values in the scope of the function in an undefined order.
Note: this function is only available when UseLegacySQL=True.
expression: Any expression.
VARIANCE(numeric_expression)
Computes the variance of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.
numeric_expression: The series to calculate VARIANCE on.
VAR_POP(numeric_expression)
Computes the population variance of the values computed by numeric_expr.
numeric_expression: The series to calculate VARIANCE on.
VAR_SAMP(numeric_expression)
Computes the sample variance of the values computed by numeric_expr.
numeric_expression: The series to calculate VARIANCE on.
CAST(expression)
Cast is used in a query to indicate that the result type of an expression should be converted to some other type.
expression: The expression to cast.
SAFE_CAST(expression)
Cast is used in a query to indicate that the result type of an expression should be converted to some other type.SAFE_CAST is identical to CAST, except it returns NULL instead of raising an error.
expression: The expression to cast.
DATE(timestamp)
Returns a human-readable string of a TIMESTAMP data type in the format %Y-%m-%d.
timestamp: The timestamp from which to return the date.
DATE_ADD(timestamp, interval, interval_units)
Adds the specified interval to a TIMESTAMP data type.
timestamp: The timestamp to add the interval to.
interval: The interval amount to add to the timestamp.
interval_units: The interval unit for interval. Possible values include: YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
DATEDIFF(timestamp1, timestamp2)
Returns the number of days between two TIMESTAMP data types. The result is positive if the first TIMESTAMP data type comes after the second TIMESTAMP data type, and otherwise the result is negative.
Note: this function is only available when UseLegacySQL=True.
timestamp1: The first timestamp.
timestamp2: The second timestamp.
DATE_DIFF(timestamp1, timestamp2, date_part)
Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp1: The first timestamp.
timestamp2: The second timestamp.
date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.
DAY(timestamp)
Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the day of the month.
DAYOFWEEK(timestamp)
Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the day of the week.
DAYOFYEAR(timestamp)
Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusively. The integer 1 refers to January 1.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the day of the year.
FORMAT_UTC_USEC(unix_timestamp)
Returns a human-readable string representation of a UNIX timestamp in the format YYYY-MM-DD HH:MM:SS.uuuuuu.
Note: this function is only available when UseLegacySQL=True.
timestamp: The unix timestamp to format.
HOUR(timestamp)
Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the hour as an integer.
MINUTE(timestamp)
Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the minutes as an integer.
MONTH(timestamp)
Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the month as an integer.
MSEC_TO_TIMESTAMP(unix_timestamp)
Converts a UNIX timestamp in milliseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.
timestamp: The unix timestamp to convert.
PARSE_UTC_USEC(date_string)
Converts a date string to a UNIX timestamp in microseconds. date_string must have the format YYYY-MM-DD HH:MM:SS[.uuuuuu]. The fractional part of the second can be up to 6 digits long or can be omitted.
Note: this function is only available when UseLegacySQL=True.
date_string: The date string to convert.
QUARTER(timestamp)
Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the quarter as an integer.
SEC_TO_TIMESTAMP(unix_timestamp)
Converts a UNIX timestamp in seconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to convert.
SECOND(timestamp)
Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusively. During a leap second, the integer range is between 0 and 60, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the second as an integer.
STRFTIME_UTC_USEC(unix_timestamp, date_format_str)
Returns a human-readable date string in the format date_format_str.date_format_str can include date-related punctuation characters (such as / and -) and special characters accepted by the strftime function in C++ (such as %d for day of month).
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to convert.
date_format_str: The date format string.
TIME(timestamp)
Returns a human-readable string of a TIMESTAMP data type, in the format %H:%M:%S.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the time.
TIMESTAMP(date_string)
Convert a date string to a TIMESTAMP data type.
timestamp: The date string to convert.
TIMESTAMP_SECONDS(unix_timestamp)
Interprets INT64_expression as the number of seconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The unix timestamp to convert.
TIMESTAMP_MILLIS(unix_timestamp)
Interprets INT64_expression as the number of milliseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The unix timestamp to convert.
TIMESTAMP_MICROS(unix_timestamp)
Interprets INT64_expression as the number of microseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The unix timestamp to convert.
TIMESTAMP_TO_MSEC(timestamp)
Converts a TIMESTAMP data type to a UNIX timestamp in milliseconds.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp to convert.
TIMESTAMP_TO_SEC(timestamp)
Converts a TIMESTAMP data type to a UNIX timestamp in seconds.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp to convert.
TIMESTAMP_TO_USEC(timestamp)
Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp to convert.
UNIX_DATE(date_string)
Returns the number of days since 1970-01-01.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
date_string: The date string to convert.
UNIX_SECONDS(timestamp)
Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The timestamp to convert.
UNIX_MILLIS(timestamp)
Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The timestamp to convert.
UNIX_MICROS(timestamp)
Returns the number of microseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The timestamp to convert.
USEC_TO_TIMESTAMP(unix_timestamp)
Converts a UNIX timestamp in microseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to convert.
UTC_USEC_TO_DAY(unix_timestamp)
Shifts a UNIX timestamp in microseconds to the beginning of the day it occurs in.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to shift.
UTC_USEC_TO_HOUR(unix_timestamp)
Shifts a UNIX timestamp in microseconds to the beginning of the hour it occurs in.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to shift.
UTC_USEC_TO_MONTH(unix_timestamp)
Shifts a UNIX timestamp in microseconds to the beginning of the month it occurs in.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to shift.
UTC_USEC_TO_WEEK(unix_timestamp, day_of_week)
Returns a UNIX timestamp in microseconds that represents a day in the week of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to shift.
day_of_week: A day of the week from 0 (Sunday) to 6 (Saturday).
UTC_USEC_TO_YEAR(unix_timestamp)
Returns a UNIX timestamp in microseconds that represents the year of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to convert.
WEEK(timestamp)
Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively. Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the week as an integer.
YEAR(timestamp)
Returns the year of a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the year as an integer.
ABS(expression)
Returns the absolute value of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
ACOS(expression)
Returns the arc cosine of the argument.
expression: Any column or literal expression.
ACOSH(expression)
Returns the arc hyperbolic cosine of the argument.
expression: Any column or literal expression.
ASIN(expression)
Returns arcsine in radians.
expression: Any column or literal expression.
ASINH(expression)
Returns the arc hyperbolic sine of the argument.
expression: Any column or literal expression.
ATAN(expression)
Returns arc tangent of the argument.
expression: Any column or literal expression.
ATANH(expression)
Returns the arc hyperbolic tangent of the argument.
expression: Any column or literal expression.
ATAN2(expression1, expression2)
Returns the arc tangent of the two arguments.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
CEIL(expression)
Rounds the argument up to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
COS(expression)
Returns the cosine of the argument.
expression: Any column or literal expression.
COSH(expression)
Returns the hyperbolic cosine of the argument.
expression: Any column or literal expression.
DEGREES(expression)
Returns expression, converted from radians to degrees.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
EXP(expression)
Returns the result of raising the constant "e" - the base of the natural logarithm - to the power of expression.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
FLOOR(expression)
Rounds the argument down to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
LN(expression)
Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
LOG(expression)
Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
LOG2(expression)
Returns the Base-2 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
LOG10(expression)
Returns the Base-10 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
PI()
Returns PI.
Note: this function is only available when UseLegacySQL=True.
POW(expression1, expression2)
Returns the result of raising expression1 to the power of expression2.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
RADIANS(expression)
Returns expression, converted from degrees to radians.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
RAND([expression])
Returns a random float value in the range 0.0 >= value < 1.0. Each int32_seed value always generates the same sequence of random numbers within a given query, as long as you don't use a LIMIT clause. If int32_seed is not specified, BigQuery uses the current timestamp as the seed value.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
ROUND(expression [, integer_digits])
Rounds the argument either up or down to the nearest whole number (or if specified, to the specified number of digits) and returns the rounded value.
expression: Any column or literal expression.
integer_digits: The number of digits to round to.
SIN(expression)
Returns the sine of the argument.
expression: Any column or literal expression.
SINH(expression)
Returns the hyperbolic sine of the argument.
expression: Any column or literal expression.
SQRT(expression)
Returns the square root of the expression.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
TAN(expression)
Returns the tangent of the argument.
expression: Any column or literal expression.
TANH(expression)
Returns the hyperbolic tangent of the argument.
expression: Any column or literal expression.
TRUNC(expression [, integer_digits])
Rounds X to the nearest integer whose absolute value is not greater than Xs. When the integer_digits parameter is specified this function is similar to ROUND(X, N) but always rounds towards zero. Unlike ROUND(X, N) it never overflows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: Any column or literal expression.
integer_digits: The number of digits to round to.
BYTE_LENGTH(str)
Returns the length of the value in bytes, regardless of whether the type of the value is STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to calculate the length on.
CHAR_LENGTH(str)
Returns the length of the STRING in characters.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to calculate the length on.
CONCAT(str1, str2 [, str3] [, ...])
Returns the concatenation of two or more strings, or NULL if any of the values are NULL.
str1: The first string to concatenate.
str2: The second string to concatenate.
str3: The third string to concatenate.
ENDS_WITH(str1, str2)
Takes two values. Returns TRUE if the second value is a suffix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str1: The string to search in.
str2: The string to search for.
FROM_BASE64(string_expr)
Converts the base64-encoded input string_expr into BYTES format. To convert BYTES to a base64-encoded STRING, use TO_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
string_expr: The string to convert from base64 encoding.
FROM_HEX(string_expr)
Converts a hexadecimal-encoded STRING into BYTES format. Returns an error if the input STRING contains characters outside the range (0..9, A..F, a..f). The lettercase of the characters does not matter. To convert BYTES to a hexadecimal-encoded STRING, use TO_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
string_expr: The string to convert from hexadecimal encoding.
INSTR(str1, str2)
Returns the one-based index of the first occurrence of str2 in str1, or returns 0 if str2 does not occur in str1.
Note: this function is only available when UseLegacySQL=True.
str1: The string to search in.
str2: The string to search for.
LEFT(str, numeric_expression)
Returns the leftmost numeric_expr characters of str. If the number is longer than str, the full string will be returned. Example: LEFT('seattle', 3) returns sea.
Note: this function is only available when UseLegacySQL=True.
str: The string to perform the LEFT operation on.
numeric_expression: The number of characters to return.
LENGTH(str)
Returns a numerical value for the length of the string. Example: if str is '123456', LENGTH returns 6.
str: The string to calculate the length on.
LOWER(str)
Returns the original string with all characters in lower case.
str: The string to lower.
LPAD(str1, numeric_expression[, str2])
Pads str1 on the left with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: LPAD('1', 7, '?') returns ??????1.
str1: The string to pad.
numeric_expression: The number of str2 instances to pad.
str2: The pad characters.
LTRIM(str1 [, str2])
Removes characters from the left side of str1. If str2 is omitted, LTRIM removes spaces from the left side of str1. Otherwise, LTRIM removes any characters in str2 from the left side of str1 (case-sensitive).
str1: The string to trim.
str2: The characters to trim from str1.
REPEAT(str, repetitions)
Returns a value that consists of original_value, repeated. The repetitions parameter specifies the number of times to repeat original_value. Returns NULL if either original_value or repetitions are NULL. This function return an error if the repetitions value is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to repeat.
str2: The number of repititions.
REPLACE(str1, str2, str3)
Replaces all instances of str2 within str1 with str3.
str1: The string to search in.
str2: The string to search for.
str3: The string to replace instances of str2.
REVERSE(str)
Returns the reverse of the input STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to reverse.
RIGHT(str, numeric_expression)
Returns the rightmost numeric_expr characters of str. If the number is longer than the string, it will return the whole string. Example: RIGHT('kirkland', 4) returns land.
Note: this function is only available when UseLegacySQL=True.
str: The string to perform the RIGHT operation on.
numeric_expression: The number of characters to return.
RPAD(str1, numeric_expression, str2)
Pads str1 on the right with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: RPAD('1', 7, '?') returns 1??????.
str1: The string to pad.
numeric_expression: The number of str2 instances to pad.
str2: The pad characters.
RTRIM(str1 [, str2])
Removes trailing characters from the right side of str1. If str2 is omitted, RTRIM removes trailing spaces from str1. Otherwise, RTRIM removes any characters in str2 from the right side of str1 (case-sensitive).
str1: The string to trim.
str2: The characters to trim from str1.
SPLIT(str [, delimiter])
Splits a string into repeated substrings. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.
str: The string to split.
delimiter: The delimiter to split the string on. Default delimiter is a comma (,).
STARTS_WITH(str1, str2)
Takes two values. Returns TRUE if the second value is a prefix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str1: The string to search in.
str2: The string to search for.
STRPOS(str1, str2)
Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str1: The string to search in.
str2: The string to search for.
SUBSTR(str, index [, max_len])
Returns a substring of str, starting at index. If the optional max_len parameter is used, the returned string is a maximum of max_len characters long. Counting starts at 1, so the first character in the string is in position 1 (not zero). If index is 5, the substring begins with the 5th character from the left in str. If index is -4, the substring begins with the 4th character from the right in str. Example: SUBSTR('awesome', -4, 4) returns the substring some.
str: The original string.
index: The starting index.
max_len: The maximum length of the return string.
TO_BASE64(string_expr)
Converts a sequence of BYTES into a base64-encoded STRING. To convert a base64-encoded STRING into BYTES, use FROM_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
string_expr: The string to convert to base64 encoding.
TO_HEX(string_expr)
Converts a sequence of BYTES into a hexadecimal STRING. Converts each byte in the STRING as two hexadecimal characters in the range (0..9, a..f). To convert a hexadecimal-encoded STRING to BYTES, use FROM_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
string_expr: The string to convert to hexadecimal encoding.
TRIM(str1[, str2])
Removes all leading and trailing characters that match value2. If value2 is not specified, all leading and trailing whitespace characters (as defined by the Unicode standard) are removed. If the first argument is of type BYTES, the second argument is required. If value2 contains more than one character or byte, the function removes all leading or trailing characters or bytes contained in value2.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str1: The string to trim.
str2: The optional string characters to trim from str1.
UPPER(str)
Returns the original string with all characters in upper case.
str: The string to upper.
JSON_EXTRACT(json, json_path)
Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant. Returns the value in JSON string format.
json: The JSON to select a value from.
json_path: The JSON path of the value contained in json.
JSON_EXTRACT_SCALAR(json, json_path)
Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant, and bracket notation is not supported. Returns a scalar JSON value.
json: The JSON to select a value from.
json_path: The JSON path of the value contained in json.
REGEXP_CONTAINS(str, reg_exp)
Returns TRUE if value is a partial match for the regular expression, regex. You can search for a full match by using ^ (beginning of text) and $ (end of text). If the regex argument is invalid, the function returns an error.
Note: this function is only available when UseLegacySQL=True.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
REGEXP_EXTRACT(str, reg_exp)
Returns the portion of str that matches the capturing group within the regular expression.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
REGEXP_EXTRACT_ALL(str, reg_exp)
Returns an array of all substrings of value that match the regular expression, regex. The REGEXP_EXTRACT_ALL function only returns non-overlapping matches. For example, using this function to extract ana from banana returns only one substring, not two.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
REGEXP_REPLACE(orig_str, reg_exp, replace_str)
Returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str. For example, REGEXP_REPLACE ('Hello', 'lo', 'p') returns Help.
orig_str: The original string to match in the regular expression.
reg_exp: The regular expression to match.
replace_str: The replacement for the matched orig_str in the regular expression.
FORMAT_IP(integer_value)
Converts 32 least significant bits of integer_value to human-readable IPv4 address string.
Note: this function is only available when UseLegacySQL=True.
integer_value: The integer value to convert to an IPv4 address.
PARSE_IP(readable_ip)
Converts a string representing IPv4 address to unsigned integer value. For example, PARSE_IP('0.0.0.1') will return 1. If string is not a valid IPv4 address, PARSE_IP will return NULL.
Note: this function is only available when UseLegacySQL=True.
readable_ip: The IPv4 address to convert to an integer.
NET.IPV4_FROM_INT64(integer_value)
Converts an IPv4 address from integer format to binary (BYTES) format in network byte order. In the integer input, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
integer_value: The integer value to convert to an IPv4 address.
NET.IPV4_TO_INT64(readable_ip)
Converts an IPv4 address from binary (BYTES) format in network byte order to integer format. In the integer output, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255. The output is in the range [0, 0xFFFFFFFF]. If the input length is not 4, this function throws an error. This function does not support IPv6.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
readable_ip: The IPv4 address to convert to an integer.
FARM_FINGERPRINT(expression)
Computes the fingerprint of the STRING or BYTES input using the Fingerprint64 function from the open-source FarmHash library. The output of this function for a particular input will never change.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the fingerprint.
MD5(expression)
Computes the hash of the input using the MD5 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 16 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the hash.
SHA1(expression)
Computes the hash of the input using the SHA-1 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 20 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the hash.
SHA256(expression)
Computes the hash of the input using the SHA-256 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 32 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the hash.
SHA512(expression)
Computes the hash of the input using the SHA-512 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 64 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the hash.
JSON_EXTRACT(json, jsonpath)
Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
json: The JSON document to extract.
jsonpath: The XPath used to select the nodes. The JSONPath must be a string constant. The values of the nodes selected will be returned in a token-separated list.
Predicate Functions
REGEXP_MATCH(str, reg_exp)
Returns true if str matches the regular expression. For string matching without regular expressions, use CONTAINS instead of REGEXP_MATCH.
Note: this function is only available when UseLegacySQL=True.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
CAST(expression)
Cast is used in a query to indicate that the result type of an expression should be converted to some other type.
expression: The expression to cast.
SAFE_CAST(expression)
Cast is used in a query to indicate that the result type of an expression should be converted to some other type.SAFE_CAST is identical to CAST, except it returns NULL instead of raising an error.
expression: The expression to cast.
DATE(timestamp)
Returns a human-readable string of a TIMESTAMP data type in the format %Y-%m-%d.
timestamp: The timestamp from which to return the date.
DATE_ADD(timestamp, interval, interval_units)
Adds the specified interval to a TIMESTAMP data type.
timestamp: The timestamp to add the interval to.
interval: The interval amount to add to the timestamp.
interval_units: The interval unit for interval. Possible values include: YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
DATEDIFF(timestamp1, timestamp2)
Returns the number of days between two TIMESTAMP data types. The result is positive if the first TIMESTAMP data type comes after the second TIMESTAMP data type, and otherwise the result is negative.
Note: this function is only available when UseLegacySQL=True.
timestamp1: The first timestamp.
timestamp2: The second timestamp.
DATE_DIFF(timestamp1, timestamp2, date_part)
Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp1: The first timestamp.
timestamp2: The second timestamp.
date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.
DAY(timestamp)
Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the day of the month.
DAYOFWEEK(timestamp)
Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the day of the week.
DAYOFYEAR(timestamp)
Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusively. The integer 1 refers to January 1.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the day of the year.
FORMAT_UTC_USEC(unix_timestamp)
Returns a human-readable string representation of a UNIX timestamp in the format YYYY-MM-DD HH:MM:SS.uuuuuu.
Note: this function is only available when UseLegacySQL=True.
timestamp: The unix timestamp to format.
HOUR(timestamp)
Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the hour as an integer.
MINUTE(timestamp)
Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the minutes as an integer.
MONTH(timestamp)
Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the month as an integer.
MSEC_TO_TIMESTAMP(unix_timestamp)
Converts a UNIX timestamp in milliseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.
timestamp: The unix timestamp to convert.
PARSE_UTC_USEC(date_string)
Converts a date string to a UNIX timestamp in microseconds. date_string must have the format YYYY-MM-DD HH:MM:SS[.uuuuuu]. The fractional part of the second can be up to 6 digits long or can be omitted.
Note: this function is only available when UseLegacySQL=True.
date_string: The date string to convert.
QUARTER(timestamp)
Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the quarter as an integer.
SEC_TO_TIMESTAMP(unix_timestamp)
Converts a UNIX timestamp in seconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to convert.
SECOND(timestamp)
Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusively. During a leap second, the integer range is between 0 and 60, inclusively.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the second as an integer.
STRFTIME_UTC_USEC(unix_timestamp, date_format_str)
Returns a human-readable date string in the format date_format_str.date_format_str can include date-related punctuation characters (such as / and -) and special characters accepted by the strftime function in C++ (such as %d for day of month).
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to convert.
date_format_str: The date format string.
TIME(timestamp)
Returns a human-readable string of a TIMESTAMP data type, in the format %H:%M:%S.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the time.
TIMESTAMP(date_string)
Convert a date string to a TIMESTAMP data type.
timestamp: The date string to convert.
TIMESTAMP_SECONDS(unix_timestamp)
Interprets INT64_expression as the number of seconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The unix timestamp to convert.
TIMESTAMP_MILLIS(unix_timestamp)
Interprets INT64_expression as the number of milliseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The unix timestamp to convert.
TIMESTAMP_MICROS(unix_timestamp)
Interprets INT64_expression as the number of microseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The unix timestamp to convert.
TIMESTAMP_TO_MSEC(timestamp)
Converts a TIMESTAMP data type to a UNIX timestamp in milliseconds.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp to convert.
TIMESTAMP_TO_SEC(timestamp)
Converts a TIMESTAMP data type to a UNIX timestamp in seconds.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp to convert.
TIMESTAMP_TO_USEC(timestamp)
Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp to convert.
UNIX_DATE(date_string)
Returns the number of days since 1970-01-01.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
date_string: The date string to convert.
UNIX_SECONDS(timestamp)
Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The timestamp to convert.
UNIX_MILLIS(timestamp)
Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The timestamp to convert.
UNIX_MICROS(timestamp)
Returns the number of microseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
timestamp: The timestamp to convert.
USEC_TO_TIMESTAMP(unix_timestamp)
Converts a UNIX timestamp in microseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to convert.
UTC_USEC_TO_DAY(unix_timestamp)
Shifts a UNIX timestamp in microseconds to the beginning of the day it occurs in.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to shift.
UTC_USEC_TO_HOUR(unix_timestamp)
Shifts a UNIX timestamp in microseconds to the beginning of the hour it occurs in.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to shift.
UTC_USEC_TO_MONTH(unix_timestamp)
Shifts a UNIX timestamp in microseconds to the beginning of the month it occurs in.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to shift.
UTC_USEC_TO_WEEK(unix_timestamp, day_of_week)
Returns a UNIX timestamp in microseconds that represents a day in the week of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to shift.
day_of_week: A day of the week from 0 (Sunday) to 6 (Saturday).
UTC_USEC_TO_YEAR(unix_timestamp)
Returns a UNIX timestamp in microseconds that represents the year of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.
unix_timestamp: The unix timestamp to convert.
WEEK(timestamp)
Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively. Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the week as an integer.
YEAR(timestamp)
Returns the year of a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.
timestamp: The timestamp from which to return the year as an integer.
ABS(expression)
Returns the absolute value of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
ACOS(expression)
Returns the arc cosine of the argument.
expression: Any column or literal expression.
ACOSH(expression)
Returns the arc hyperbolic cosine of the argument.
expression: Any column or literal expression.
ASIN(expression)
Returns arcsine in radians.
expression: Any column or literal expression.
ASINH(expression)
Returns the arc hyperbolic sine of the argument.
expression: Any column or literal expression.
ATAN(expression)
Returns arc tangent of the argument.
expression: Any column or literal expression.
ATANH(expression)
Returns the arc hyperbolic tangent of the argument.
expression: Any column or literal expression.
ATAN2(expression1, expression2)
Returns the arc tangent of the two arguments.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
CEIL(expression)
Rounds the argument up to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
COS(expression)
Returns the cosine of the argument.
expression: Any column or literal expression.
COSH(expression)
Returns the hyperbolic cosine of the argument.
expression: Any column or literal expression.
DEGREES(expression)
Returns expression, converted from radians to degrees.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
EXP(expression)
Returns the result of raising the constant "e" - the base of the natural logarithm - to the power of expression.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
FLOOR(expression)
Rounds the argument down to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
LN(expression)
Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
LOG(expression)
Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
LOG2(expression)
Returns the Base-2 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
LOG10(expression)
Returns the Base-10 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
PI()
Returns PI.
Note: this function is only available when UseLegacySQL=True.
POW(expression1, expression2)
Returns the result of raising expression1 to the power of expression2.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
RADIANS(expression)
Returns expression, converted from degrees to radians.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
RAND([expression])
Returns a random float value in the range 0.0 >= value < 1.0. Each int32_seed value always generates the same sequence of random numbers within a given query, as long as you don't use a LIMIT clause. If int32_seed is not specified, BigQuery uses the current timestamp as the seed value.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
ROUND(expression [, integer_digits])
Rounds the argument either up or down to the nearest whole number (or if specified, to the specified number of digits) and returns the rounded value.
expression: Any column or literal expression.
integer_digits: The number of digits to round to.
SIN(expression)
Returns the sine of the argument.
expression: Any column or literal expression.
SINH(expression)
Returns the hyperbolic sine of the argument.
expression: Any column or literal expression.
SQRT(expression)
Returns the square root of the expression.
Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
TAN(expression)
Returns the tangent of the argument.
expression: Any column or literal expression.
TANH(expression)
Returns the hyperbolic tangent of the argument.
expression: Any column or literal expression.
TRUNC(expression [, integer_digits])
Rounds X to the nearest integer whose absolute value is not greater than Xs. When the integer_digits parameter is specified this function is similar to ROUND(X, N) but always rounds towards zero. Unlike ROUND(X, N) it never overflows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: Any column or literal expression.
integer_digits: The number of digits to round to.
BYTE_LENGTH(str)
Returns the length of the value in bytes, regardless of whether the type of the value is STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to calculate the length on.
CHAR_LENGTH(str)
Returns the length of the STRING in characters.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to calculate the length on.
CONCAT(str1, str2 [, str3] [, ...])
Returns the concatenation of two or more strings, or NULL if any of the values are NULL.
str1: The first string to concatenate.
str2: The second string to concatenate.
str3: The third string to concatenate.
ENDS_WITH(str1, str2)
Takes two values. Returns TRUE if the second value is a suffix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str1: The string to search in.
str2: The string to search for.
FROM_BASE64(string_expr)
Converts the base64-encoded input string_expr into BYTES format. To convert BYTES to a base64-encoded STRING, use TO_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
string_expr: The string to convert from base64 encoding.
FROM_HEX(string_expr)
Converts a hexadecimal-encoded STRING into BYTES format. Returns an error if the input STRING contains characters outside the range (0..9, A..F, a..f). The lettercase of the characters does not matter. To convert BYTES to a hexadecimal-encoded STRING, use TO_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
string_expr: The string to convert from hexadecimal encoding.
INSTR(str1, str2)
Returns the one-based index of the first occurrence of str2 in str1, or returns 0 if str2 does not occur in str1.
Note: this function is only available when UseLegacySQL=True.
str1: The string to search in.
str2: The string to search for.
LEFT(str, numeric_expression)
Returns the leftmost numeric_expr characters of str. If the number is longer than str, the full string will be returned. Example: LEFT('seattle', 3) returns sea.
Note: this function is only available when UseLegacySQL=True.
str: The string to perform the LEFT operation on.
numeric_expression: The number of characters to return.
LENGTH(str)
Returns a numerical value for the length of the string. Example: if str is '123456', LENGTH returns 6.
str: The string to calculate the length on.
LOWER(str)
Returns the original string with all characters in lower case.
str: The string to lower.
LPAD(str1, numeric_expression[, str2])
Pads str1 on the left with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: LPAD('1', 7, '?') returns ??????1.
str1: The string to pad.
numeric_expression: The number of str2 instances to pad.
str2: The pad characters.
LTRIM(str1 [, str2])
Removes characters from the left side of str1. If str2 is omitted, LTRIM removes spaces from the left side of str1. Otherwise, LTRIM removes any characters in str2 from the left side of str1 (case-sensitive).
str1: The string to trim.
str2: The characters to trim from str1.
REPEAT(str, repetitions)
Returns a value that consists of original_value, repeated. The repetitions parameter specifies the number of times to repeat original_value. Returns NULL if either original_value or repetitions are NULL. This function return an error if the repetitions value is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to repeat.
str2: The number of repititions.
REPLACE(str1, str2, str3)
Replaces all instances of str2 within str1 with str3.
str1: The string to search in.
str2: The string to search for.
str3: The string to replace instances of str2.
REVERSE(str)
Returns the reverse of the input STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to reverse.
RIGHT(str, numeric_expression)
Returns the rightmost numeric_expr characters of str. If the number is longer than the string, it will return the whole string. Example: RIGHT('kirkland', 4) returns land.
Note: this function is only available when UseLegacySQL=True.
str: The string to perform the RIGHT operation on.
numeric_expression: The number of characters to return.
RPAD(str1, numeric_expression, str2)
Pads str1 on the right with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: RPAD('1', 7, '?') returns 1??????.
str1: The string to pad.
numeric_expression: The number of str2 instances to pad.
str2: The pad characters.
RTRIM(str1 [, str2])
Removes trailing characters from the right side of str1. If str2 is omitted, RTRIM removes trailing spaces from str1. Otherwise, RTRIM removes any characters in str2 from the right side of str1 (case-sensitive).
str1: The string to trim.
str2: The characters to trim from str1.
SPLIT(str [, delimiter])
Splits a string into repeated substrings. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.
str: The string to split.
delimiter: The delimiter to split the string on. Default delimiter is a comma (,).
STARTS_WITH(str1, str2)
Takes two values. Returns TRUE if the second value is a prefix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str1: The string to search in.
str2: The string to search for.
STRPOS(str1, str2)
Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str1: The string to search in.
str2: The string to search for.
SUBSTR(str, index [, max_len])
Returns a substring of str, starting at index. If the optional max_len parameter is used, the returned string is a maximum of max_len characters long. Counting starts at 1, so the first character in the string is in position 1 (not zero). If index is 5, the substring begins with the 5th character from the left in str. If index is -4, the substring begins with the 4th character from the right in str. Example: SUBSTR('awesome', -4, 4) returns the substring some.
str: The original string.
index: The starting index.
max_len: The maximum length of the return string.
TO_BASE64(string_expr)
Converts a sequence of BYTES into a base64-encoded STRING. To convert a base64-encoded STRING into BYTES, use FROM_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
string_expr: The string to convert to base64 encoding.
TO_HEX(string_expr)
Converts a sequence of BYTES into a hexadecimal STRING. Converts each byte in the STRING as two hexadecimal characters in the range (0..9, a..f). To convert a hexadecimal-encoded STRING to BYTES, use FROM_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
string_expr: The string to convert to hexadecimal encoding.
TRIM(str1[, str2])
Removes all leading and trailing characters that match value2. If value2 is not specified, all leading and trailing whitespace characters (as defined by the Unicode standard) are removed. If the first argument is of type BYTES, the second argument is required. If value2 contains more than one character or byte, the function removes all leading or trailing characters or bytes contained in value2.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str1: The string to trim.
str2: The optional string characters to trim from str1.
UPPER(str)
Returns the original string with all characters in upper case.
str: The string to upper.
JSON_EXTRACT(json, json_path)
Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant. Returns the value in JSON string format.
json: The JSON to select a value from.
json_path: The JSON path of the value contained in json.
JSON_EXTRACT_SCALAR(json, json_path)
Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant, and bracket notation is not supported. Returns a scalar JSON value.
json: The JSON to select a value from.
json_path: The JSON path of the value contained in json.
REGEXP_CONTAINS(str, reg_exp)
Returns TRUE if value is a partial match for the regular expression, regex. You can search for a full match by using ^ (beginning of text) and $ (end of text). If the regex argument is invalid, the function returns an error.
Note: this function is only available when UseLegacySQL=True.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
REGEXP_EXTRACT(str, reg_exp)
Returns the portion of str that matches the capturing group within the regular expression.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
REGEXP_EXTRACT_ALL(str, reg_exp)
Returns an array of all substrings of value that match the regular expression, regex. The REGEXP_EXTRACT_ALL function only returns non-overlapping matches. For example, using this function to extract ana from banana returns only one substring, not two.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
REGEXP_REPLACE(orig_str, reg_exp, replace_str)
Returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str. For example, REGEXP_REPLACE ('Hello', 'lo', 'p') returns Help.
orig_str: The original string to match in the regular expression.
reg_exp: The regular expression to match.
replace_str: The replacement for the matched orig_str in the regular expression.
FORMAT_IP(integer_value)
Converts 32 least significant bits of integer_value to human-readable IPv4 address string.
Note: this function is only available when UseLegacySQL=True.
integer_value: The integer value to convert to an IPv4 address.
PARSE_IP(readable_ip)
Converts a string representing IPv4 address to unsigned integer value. For example, PARSE_IP('0.0.0.1') will return 1. If string is not a valid IPv4 address, PARSE_IP will return NULL.
Note: this function is only available when UseLegacySQL=True.
readable_ip: The IPv4 address to convert to an integer.
NET.IPV4_FROM_INT64(integer_value)
Converts an IPv4 address from integer format to binary (BYTES) format in network byte order. In the integer input, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
integer_value: The integer value to convert to an IPv4 address.
NET.IPV4_TO_INT64(readable_ip)
Converts an IPv4 address from binary (BYTES) format in network byte order to integer format. In the integer output, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255. The output is in the range [0, 0xFFFFFFFF]. If the input length is not 4, this function throws an error. This function does not support IPv6.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
readable_ip: The IPv4 address to convert to an integer.
FARM_FINGERPRINT(expression)
Computes the fingerprint of the STRING or BYTES input using the Fingerprint64 function from the open-source FarmHash library. The output of this function for a particular input will never change.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the fingerprint.
MD5(expression)
Computes the hash of the input using the MD5 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 16 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the hash.
SHA1(expression)
Computes the hash of the input using the SHA-1 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 20 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the hash.
SHA256(expression)
Computes the hash of the input using the SHA-256 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 32 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the hash.
SHA512(expression)
Computes the hash of the input using the SHA-512 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 64 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to use to compute the hash.
TIMESTAMP(datetime_expression[, timezone])
Convert a date, datetime, or string to a TIMESTAMP data type.
Note: this function does not support the timezone parameter and requires datetime_expression to be a string when using Legacy SQL (UseLegacySQL=True).
• datetime_expression: The expression to be converted to a timestamp
• timezone: The timezone to be used. If no timezone is specified, the default timezone, UTC, is used