Amazon Athena

SELECT Statements

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 SQL engine of the provider:

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> ]

[ UNION [ ALL ] <select_statement> ]

[

ORDER BY

<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

]

[

LIMIT <expression>

[

{ OFFSET | , }

<expression>

]

} | SCOPE_IDENTITY()

<expression> ::=

| <column_reference>

| @ <parameter>

| ?

| COUNT( * | { [ DISTINCT ] <expression> } )

| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )

| NULLIF ( <expression> , <expression> )

| COALESCE ( <expression> , ... )

| CASE <expression>

WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]

[ ELSE { <expression> | NULL } ]

END

| <literal>

| <sql_function>

<search_condition> ::=

{

<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]

} [ { AND | OR } ... ]

Examples

Return all columns:
SELECT * FROM [AwsDataCatalog].[sampledb].Customers
Rename a column:
SELECT [TotalDue] AS MY_TotalDue FROM [AwsDataCatalog].[sampledb].Customers
Cast a column's data as a different data type:
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM [AwsDataCatalog].[sampledb].Customers
Search data:
SELECT * FROM [AwsDataCatalog].[sampledb].Customers WHERE CustomerId = '12345'
The Amazon Athena APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, ANY, ALL, EXISTS, NOT EXISTS, CONTAINS, NOT CONTAINS, BETWEEN, AND, OR.
SELECT * FROM [AwsDataCatalog].[sampledb].Customers WHERE CustomerId = '12345';
Return the number of items matching the query criteria:
SELECT COUNT(*) AS MyCount FROM [AwsDataCatalog].[sampledb].Customers
Return the number of unique items matching the query criteria:
SELECT COUNT(DISTINCT TotalDue) FROM [AwsDataCatalog].[sampledb].Customers
Return the unique items matching the query criteria:
SELECT DISTINCT TotalDue FROM [AwsDataCatalog].[sampledb].Customers
Summarize data:
SELECT TotalDue, MAX(AnnualRevenue) FROM [AwsDataCatalog].[sampledb].Customers GROUP BY TotalDue
See Aggregate Functions below for details.
Retrieve data from multiple tables.
SELECT Orders.OrderDate, Customers.ContactName FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
See JOIN Queries below for details.
Sort a result set in ascending order:
SELECT Name, TotalDue FROM [AwsDataCatalog].[sampledb].Customers ORDER BY TotalDue ASC
Restrict a result set to the specified number of rows:
SELECT Name, TotalDue FROM [AwsDataCatalog].[sampledb].Customers LIMIT 10
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
SELECT * FROM [AwsDataCatalog].[sampledb].Customers WHERE CustomerId = @param

JOIN Queries

In Amazon Athena, INNER and OUTER joins are supported.

The following examples use tables in the Northwind database to show how to join Amazon Athena tables:

The query below returns the ContactName and the OrderDate of every customer who has an order:

SELECT Orders.OrderDate, Customers.ContactNameFROM CustomersINNER JOIN OrdersON Customers.CustomerID=Orders.CustomerID

Projection Functions

GREATEST(value1 [, value2])

Returns the largest of the provided values.

value1: The first value to compare.
value2: The second value to compare.

LEAST(value1 [, value2])

Returns the smallest of the provided values.

value1: The first value to compare.
value2: The second value to compare.

COALESCE(expr1 [, expr2 [, ...]])

Returns the value of the first non-null expression. The remaining expressions are not evaluated. All input expressions must be implicitly coercible to a common supertype.

expr1: Any expression
expr2: Any expression

NULLIF(expression, expression_to_match)

Returns NULL if expression = expression_to_match is true, otherwise returns expression. expression and expression_to_match must be implicitly coercible to a common supertype; equality comparison is done on coerced values.

expression: Any expression
expression_to_match: Any expression to be matched

CAST(expression AS type)

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.
type: The type to cast the expression to.

TRY_CAST(expression AS type)

Like CAST, but returns NULL if the cast fails.

expression: The expression to cast.
type: The type to cast the expression to.

TYPEOF(expr)

Returns the name of the type of the provided expression

expr: An expression.

ABS(expression)

Returns the absolute value of the argument.

expression: Any column or literal expression.

CBRT(expression)

Returns the cube root of expression.

expression: 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.

CEILING(expression)

Synonym for CEIL function.

expression: Any column or literal expression.

DEGREES(expression)

Returns expression, converted from radians to degrees.

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.

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.

FROM_BASE(string, radix)

Returns the value of string interpreted as a base-radix number.

string: A string column or literal expression.
radix: An optional radix.

LN(expression)

Returns the natural logarithm of the argument.

expression: Any column or literal expression.

LOG(expression, base)

Returns the natural logarithm of the argument.

expression: Any column or literal expression.
base: The base to use when performing the logarithm.

LOG2(expression)

Returns the Base-2 logarithm of the argument.

expression: Any column or literal expression.

LOG10(expression)

Returns the Base-10 logarithm of the argument.

expression: Any column or literal expression.

MOD(n, m)

Returns the modulus (remainder) of n divided by m.

n: Any column or literal expression.
m: Any column or literal expression.

PI()

Returns PI.

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.

POWER(expression1, expression2)

Synonym of POW function.

expression1: Any column or literal expression.
expression2: Any column or literal expression.

RADIANS(expression)

Returns expression, converted from degrees to radians.

expression: Any column or literal expression.

RANDOM([expression])

Returns a pseudo-random number in the range 0.0 >= value < expression. If expression is not specified, the value is 1.0.

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.

SIGN(expression)

Returns the signum function of expression, that is: 0 if the argument is 0, 1 if the argument is greater than 0, -1 if the argument is less than 0.

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.

TO_BASE(expression, radix)

Returns the base-radix representation of expression.

expression: Any column or literal expression.
radix: An optional radix.

TRUNCATE(expression)

Returns expression rounded to integer by dropping digits after decimal point.

expression: Any column or literal expression.

ACOS(expression)

Returns the arc cosine of the argument.

expression: Any column or literal expression.

ASIN(expression)

Returns arcsine in radians.

expression: Any column or literal expression.

ATAN(expression)

Returns arc 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.

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.

SIN(expression)

Returns the sine of the argument.

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.

BIT_AND(x, y)

Returns the bitwise AND of x and y in 2's complement representation.

x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.

BIT_COUNT(expression, bits)

Count the number of bits set in expression (treated as bits-bit signed integer) in 2's complement representation.

expression: The expression to perform the bit count operation on.
bits: The number of bits.

BIT_NOT(x)

Returns the bitwise NOT of x in 2's complement representation.

x: A numeric expression to perform the bitwise operation.

BIT_OR(x, y)

Returns the bitwise OR of x and y in 2's complement representation.

x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.

BIT_XOR(x, y)

Returns the bitwise XOR of x and y in 2's complement representation.

x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.

CHR(n)

Returns the Unicode code point n as a single character string.

n: Unicode code point value.

CODEPOINT(expression)

Returns the Unicode code point of the only character of expression.

expression: Any column or literal expression.

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.

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(str)

Removes characters from the left side of str1.

str1: The string to trim.

REPLACE(original_value, from_value, to_value)

Replaces all instances of from_value within original_value with to_value.

original_value: The string to search in.
from_value: The string to search for.
to_value: The string to replace instances of from_value.

REVERSE(str)

Returns the reverse of the input STRING or BYTES.

str: The string to reverse.

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(str)

Removes trailing characters from the right side of str1.

str: The string to trim.

SPLIT(str, delimiter [, limit])

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 (,).
limit: The maximum number of values to return.

SPLIT_PART(str, delimiter, index)

Splits str on delimiter and returns the field index. Field indexes start with 1. If the index is larger than than the number of fields, then null is returned.

str: The string to split.
delimiter: The delimiter to split the string on. Default delimiter is a comma (,).
index: The index to return.

STRPOS(str1, str2)

Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.

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.

TRIM(str)

Removes leading and trailing whitespace from str.

str: The string to trim.

UPPER(str)

Returns the original string with all characters in upper case.

str: The string to upper.

TO_UTF8(str)

Encodes str into a UTF-8 varbinary representation.

str: The string to convert to UTF-8

FROM_UTF8(expression [, replace])

Decodes a UTF-8 encoded string from binary. Invalid UTF-8 sequences are replaced with replace. The replacement string replace must either be a single character or empty (in which case invalid characters are removed).

expression:
replace: The string to replace invalid UTF-8 sequences.

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.

string_expr: The string to convert to base64 encoding.

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.

string_expr: The string to convert from base64 encoding.

TO_BASE64URL(string_expr)

Encodes binary into a base64 string_expr representation using the URL safe alphabet.

string_expr: The string to convert from base64 encoding.

FROM_BASE64URL(string_expr)

Decodes binary data from the base64 encoded string_expr using the URL safe alphabet.

string_expr: The string to convert from 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.

string_expr: The string to convert to hexadecimal 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.

string_expr: The string to convert from hexadecimal encoding.

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.

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.

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.

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.

expression: The expression to use to compute the hash.

CURRENT_DATE()

Returns the current date as of the start of the query.

CURRENT_TIME()

Returns the current time as of the start of the query.

CURRENT_TIMESTAMP()

Returns the current timestamp as of the start of the query.

CURRENT_TIMEZONE()

Returns the current time zone in the format defined by IANA (e.g., America/Los_Angeles) or as fixed offset from UTC (e.g., +08:35)

FROM_ISO8601_TIMESTAMP(str)

Parses the ISO 8601 formatted str into a timestamp with time zone.

str: Any column or literal expression.

FROM_ISO8601_DATE(str)

Parses the ISO 8601 formatted str into a date.

str: Any column or literal expression.

FROM_UNIXTIME(unixtime [, str])

Returns the UNIX timestamp unixtime as a timestamp with time zone using str for the time zone.

unixtime: A UNIX timestamp.
str: A timezone.

FROM_UNIXTIME(unixtime, hours, minutes)

Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone offset.

unixtime: A UNIX timestamp.
hours: Hours portion of timezone offset.
minutes: Minutes portion of timezone offset.

LOCALTIME()

Returns the current time as of the start of the query.

LOCALTIMESTAMP()

Returns the current timestamp as of the start of the query.

NOW()

This is an alias for current_timestamp.

TO_ISO8601(expression)

Formats expression as an ISO 8601 string. expression can be date, timestamp, or timestamp with time zone.

expression: A date or timestamp expression.

TO_UNIXTIME(timestamp)

Returns timestamp as a UNIX timestamp.

timestamp: A timestamp value.

DATE_TRUNC(date, date_part)

Truncates the date to the specified granularity.

date: The date to truncate.
date_part: The date part. Supported values are: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR.

DATE_ADD(unit, value, timestamp)

Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value.

unit: The unit to add.
value: The interval value.
timestamp: A timestamp expression.

DATE_DIFF(date1, date2, 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.

date1: The first date.
date2: The second date.
date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.

DATE_FORMAT(date_expr, format_string)

Formats the date_expr according to the specified format_string.

date_expr: The date to format.
format_string: The format string used to format the date_expr.

DATE_PARSE(date_string, format_string)

Uses a format_string and a string representation of a date to return a DATE object.

date_string: The date string to parse.
format_string: The format string used to parse the date_string.

FORMAT_DATETIME(date_expr, format_string)

Formats date_expr as a string using format_string.

date_expr: The date to format.
format_string: The format string used to format the date_expr.

PARSE_DATETIME(date_string, format_string)

Parses string into a timestamp with time zone using format.

date_string: The date string to parse.
format_string: The format string used to parse the date_string.

DAY(timestamp)

Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.

timestamp: The timestamp from which to return the day of the month.

DAY_OF_MONTH(timestamp)

Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.

timestamp: The timestamp from which to return the day of the month.

DAY_OF_WEEK(timestamp)

Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.

timestamp: The timestamp from which to return the day of the week.

DAY_OF_YEAR(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.

timestamp: The timestamp from which to return the day of the year.

DOW(timestamp)

Alias for DAY_OF_WEEK.

timestamp: The timestamp from which to return the day of the week.

DOY(timestamp)

Alias for DAY_OF_YEAR.

timestamp: The timestamp from which to return the day of the year.

HOUR(timestamp)

Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.

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.

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.

timestamp: The timestamp from which to return the month as an integer.

QUARTER(timestamp)

Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.

timestamp: The timestamp from which to return the quarter as an integer.

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.

timestamp: The timestamp from which to return the second as an integer.

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.

timestamp: The timestamp from which to return the week as an integer.

WEEK_OF_YEAR(timestamp)

Alias for WEEK function.

timestamp: The timestamp from which to return the week as an integer.

YEAR(timestamp)

Returns the year of a TIMESTAMP data type.

timestamp: The timestamp from which to return the year as an integer.

YEAR_OF_WEEK(timestamp)

Returns the year of the ISO week from timestamp.

timestamp: The timestamp from which to return the year as an integer.

YOW(timestamp)

Alias for YEAR_OF_WEEK.

timestamp: The timestamp from which to return the year as an integer.

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.

str: The string to match in the regular expression.
reg_exp: The regular expression to match.

REGEXP_LIKE(orig_str, reg_exp)

Evaluates the regular expression pattern and determines if it is contained within orig_str. This function is similar to the LIKE operator, except that the pattern only needs to be contained within orig_str, rather than needing to match all of string. In other words, this performs a contains operation rather than a match operation. You can match the entire string by anchoring the pattern using ^ and $: SELECT regexp_like('1a 2b 14m', '\d+b'); -- true

orig_str: The original 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.

JSON_ARRAY_CONTAINS(json, value)

Determine if value exists in json (a string containing a JSON array).

json: The JSON to search.
value: The value to search for.

JSON_ARRAY_GET(json, index)

Returns the element at the specified index into the json_array. The index is zero-based.

json: The JSON to select a value from.
index: The index to retrieve within the array.

JSON_ARRAY_LENGTH(1)

Returns the array length of json (a string containing a JSON array).

json: The JSON to calculate the length of.

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.

JSON_FORMAT(json)

Returns json as a string

json: The JSON to format.

JSON_PARSE(json)

Parse string as a json.

json: The JSON string to parse.

JSON_SIZE(json, json_path)

Like json_extract(), but returns the size of the value. For objects or arrays, the size is the number of members, and the size of a scalar value is zero.

json: A JSON expression.
json_path: The JSON path of the value contained in json.

ARRAY_AGG(expression)

Returns an array created from the input expression elements.

expression: Any column or literal expression.

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.

APPROX_DISTINCT(expression [, e])

Returns the approximate number of distinct input values. This function provides an approximation of count(DISTINCT x). Zero is returned if all input values are null. This function should produce a standard error of no more than e, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. The current implementation of this function requires that e be in the range: [0.01150, 0.26000].

expression: The expression to perform the approximate count distinct on.
e: The error distribution.

CORR(numeric_expression1, numeric_expression2)

Returns the Pearson correlation coefficient of a set of number pairs.

numeric_expression1: The first series.
numeric_expression2: The second series.

COVAR_POP(numeric_expression1, numeric_expression2)

Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.

numeric_expression1: The first series.
numeric_expression2: The second series.

COVAR_SAMP(numeric_expression1, numeric_expression2)

Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.

numeric_expression1: The first series.
numeric_expression2: The second series.

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)

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_SAMP on.

VARIANCE(numeric_expression)

Alias for VAR_SAMP.

numeric_expression: The series to calculate VARIANCE on.

VARIANCE_POP(numeric_expression)

Alias for VAR_POP.

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.

Predicate Functions

GREATEST(value1 [, value2])

Returns the largest of the provided values.

value1: The first value to compare.
value2: The second value to compare.

LEAST(value1 [, value2])

Returns the smallest of the provided values.

value1: The first value to compare.
value2: The second value to compare.

COALESCE(expr1 [, expr2 [, ...]])

Returns the value of the first non-null expression. The remaining expressions are not evaluated. All input expressions must be implicitly coercible to a common supertype.

expr1: Any expression
expr2: Any expression

NULLIF(expression, expression_to_match)

expression: Any expression
expression_to_match: Any expression to be matched

CAST(expression AS type)

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.
type: The type to cast the expression to.

TRY_CAST(expression AS type)

Like CAST, but returns NULL if the cast fails.

expression: The expression to cast.
type: The type to cast the expression to.

TYPEOF(expr)

Returns the name of the type of the provided expression

expr: An expression.

ABS(expression)

Returns the absolute value of the argument.

expression: Any column or literal expression.

CBRT(expression)

Returns the cube root of expression.

expression: 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.

CEILING(expression)

Synonym for CEIL function.

expression: Any column or literal expression.

DEGREES(expression)

Returns expression, converted from radians to degrees.

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.

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.

FROM_BASE(string, radix)

Returns the value of string interpreted as a base-radix number.

string: A string column or literal expression.
radix: An optional radix.

LN(expression)

Returns the natural logarithm of the argument.

expression: Any column or literal expression.

LOG(expression, base)

Returns the natural logarithm of the argument.

expression: Any column or literal expression.
base: The base to use when performing the logarithm.

LOG2(expression)

Returns the Base-2 logarithm of the argument.

expression: Any column or literal expression.

LOG10(expression)

Returns the Base-10 logarithm of the argument.

expression: Any column or literal expression.

MOD(n, m)

Returns the modulus (remainder) of n divided by m.

n: Any column or literal expression.
m: Any column or literal expression.

PI()

Returns PI.

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.

POWER(expression1, expression2)

Synonym of POW function.

expression1: Any column or literal expression.
expression2: Any column or literal expression.

RADIANS(expression)

Returns expression, converted from degrees to radians.

expression: Any column or literal expression.

RANDOM([expression])

Returns a pseudo-random number in the range 0.0 >= value < expression. If expression is not specified, the value is 1.0.

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.

SIGN(expression)

Returns the signum function of expression, that is: 0 if the argument is 0, 1 if the argument is greater than 0, -1 if the argument is less than 0.

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.

TO_BASE(expression, radix)

Returns the base-radix representation of expression.

expression: Any column or literal expression.
radix: An optional radix.

TRUNCATE(expression)

Returns expression rounded to integer by dropping digits after decimal point.

expression: Any column or literal expression.

ACOS(expression)

Returns the arc cosine of the argument.

expression: Any column or literal expression.

ASIN(expression)

Returns arcsine in radians.

expression: Any column or literal expression.

ATAN(expression)

Returns arc 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.

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.

SIN(expression)

Returns the sine of the argument.

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.

BIT_AND(x, y)

Returns the bitwise AND of x and y in 2's complement representation.

x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.

BIT_COUNT(expression, bits)

Count the number of bits set in expression (treated as bits-bit signed integer) in 2's complement representation.

expression: The expression to perform the bit count operation on.
bits: The number of bits.

BIT_NOT(x)

Returns the bitwise NOT of x in 2's complement representation.

x: A numeric expression to perform the bitwise operation.

BIT_OR(x, y)

Returns the bitwise OR of x and y in 2's complement representation.

x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.

BIT_XOR(x, y)

Returns the bitwise XOR of x and y in 2's complement representation.

x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.

CHR(n)

Returns the Unicode code point n as a single character string.

n: Unicode code point value.

CODEPOINT(expression)

Returns the Unicode code point of the only character of expression.

expression: Any column or literal expression.

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.

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(str)

Removes characters from the left side of str1.

str1: The string to trim.

REPLACE(original_value, from_value, to_value)

Replaces all instances of from_value within original_value with to_value.

original_value: The string to search in.
from_value: The string to search for.
to_value: The string to replace instances of from_value.

REVERSE(str)

Returns the reverse of the input STRING or BYTES.

str: The string to reverse.

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(str)

Removes trailing characters from the right side of str1.

str: The string to trim.

SPLIT(str, delimiter [, limit])

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 (,).
limit: The maximum number of values to return.

SPLIT_PART(str, delimiter, index)

Splits str on delimiter and returns the field index. Field indexes start with 1. If the index is larger than than the number of fields, then null is returned.

str: The string to split.
delimiter: The delimiter to split the string on. Default delimiter is a comma (,).
index: The index to return.

STRPOS(str1, str2)

Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.

str1: The string to search in.
str2: The string to search for.

SUBSTR(str, index [, max_len])

str: The original string.
index: The starting index.
max_len: The maximum length of the return string.

TRIM(str)

Removes leading and trailing whitespace from str.

str: The string to trim.

UPPER(str)

Returns the original string with all characters in upper case.

str: The string to upper.

TO_UTF8(str)

Encodes str into a UTF-8 varbinary representation.

str: The string to convert to UTF-8

FROM_UTF8(expression [, replace])

expression:
replace: The string to replace invalid UTF-8 sequences.

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.

string_expr: The string to convert to base64 encoding.

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.

string_expr: The string to convert from base64 encoding.

TO_BASE64URL(string_expr)

Encodes binary into a base64 string_expr representation using the URL safe alphabet.

string_expr: The string to convert from base64 encoding.

FROM_BASE64URL(string_expr)

Decodes binary data from the base64 encoded string_expr using the URL safe alphabet.

string_expr: The string to convert from base64 encoding.

TO_HEX(string_expr)

string_expr: The string to convert to hexadecimal encoding.

FROM_HEX(string_expr)

string_expr: The string to convert from hexadecimal encoding.

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.

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.

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.

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.

expression: The expression to use to compute the hash.

CURRENT_DATE()

Returns the current date as of the start of the query.

CURRENT_TIME()

Returns the current time as of the start of the query.

CURRENT_TIMESTAMP()

Returns the current timestamp as of the start of the query.

CURRENT_TIMEZONE()

Returns the current time zone in the format defined by IANA (e.g., America/Los_Angeles) or as fixed offset from UTC (e.g., +08:35)

FROM_ISO8601_TIMESTAMP(str)

Parses the ISO 8601 formatted str into a timestamp with time zone.

str: Any column or literal expression.

FROM_ISO8601_DATE(str)

Parses the ISO 8601 formatted str into a date.

str: Any column or literal expression.

FROM_UNIXTIME(unixtime [, str])

Returns the UNIX timestamp unixtime as a timestamp with time zone using str for the time zone.

unixtime: A UNIX timestamp.
str: A timezone.

FROM_UNIXTIME(unixtime, hours, minutes)

Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone offset.

unixtime: A UNIX timestamp.
hours: Hours portion of timezone offset.
minutes: Minutes portion of timezone offset.

LOCALTIME()

Returns the current time as of the start of the query.

LOCALTIMESTAMP()

Returns the current timestamp as of the start of the query.

NOW()

This is an alias for current_timestamp.

TO_ISO8601(expression)

Formats expression as an ISO 8601 string. expression can be date, timestamp, or timestamp with time zone.

expression: A date or timestamp expression.

TO_UNIXTIME(timestamp)

Returns timestamp as a UNIX timestamp.

timestamp: A timestamp value.

DATE_TRUNC(date, date_part)

Truncates the date to the specified granularity.

date: The date to truncate.
date_part: The date part. Supported values are: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR.

DATE_ADD(unit, value, timestamp)

Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value.

unit: The unit to add.
value: The interval value.
timestamp: A timestamp expression.

DATE_DIFF(date1, date2, date_part)

date1: The first date.
date2: The second date.
date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.

DATE_FORMAT(date_expr, format_string)

Formats the date_expr according to the specified format_string.

date_expr: The date to format.
format_string: The format string used to format the date_expr.

DATE_PARSE(date_string, format_string)

Uses a format_string and a string representation of a date to return a DATE object.

date_string: The date string to parse.
format_string: The format string used to parse the date_string.

FORMAT_DATETIME(date_expr, format_string)

Formats date_expr as a string using format_string.

date_expr: The date to format.
format_string: The format string used to format the date_expr.

PARSE_DATETIME(date_string, format_string)

Parses string into a timestamp with time zone using format.

date_string: The date string to parse.
format_string: The format string used to parse the date_string.

DAY(timestamp)

Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.

timestamp: The timestamp from which to return the day of the month.

DAY_OF_MONTH(timestamp)

Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.

timestamp: The timestamp from which to return the day of the month.

DAY_OF_WEEK(timestamp)

Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.

timestamp: The timestamp from which to return the day of the week.

DAY_OF_YEAR(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.

timestamp: The timestamp from which to return the day of the year.

DOW(timestamp)

Alias for DAY_OF_WEEK.

timestamp: The timestamp from which to return the day of the week.

DOY(timestamp)

Alias for DAY_OF_YEAR.

timestamp: The timestamp from which to return the day of the year.

HOUR(timestamp)

Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.

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.

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.

timestamp: The timestamp from which to return the month as an integer.

QUARTER(timestamp)

Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.

timestamp: The timestamp from which to return the quarter as an integer.

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.

timestamp: The timestamp from which to return the second as an integer.

WEEK(timestamp)

timestamp: The timestamp from which to return the week as an integer.

WEEK_OF_YEAR(timestamp)

Alias for WEEK function.

timestamp: The timestamp from which to return the week as an integer.

YEAR(timestamp)

Returns the year of a TIMESTAMP data type.

timestamp: The timestamp from which to return the year as an integer.

YEAR_OF_WEEK(timestamp)

Returns the year of the ISO week from timestamp.

timestamp: The timestamp from which to return the year as an integer.

YOW(timestamp)

Alias for YEAR_OF_WEEK.

timestamp: The timestamp from which to return the year as an integer.

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)

str: The string to match in the regular expression.
reg_exp: The regular expression to match.

REGEXP_LIKE(orig_str, reg_exp)

orig_str: The original 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.

JSON_ARRAY_CONTAINS(json, value)

Determine if value exists in json (a string containing a JSON array).

json: The JSON to search.
value: The value to search for.

JSON_ARRAY_GET(json, index)

Returns the element at the specified index into the json_array. The index is zero-based.

json: The JSON to select a value from.
index: The index to retrieve within the array.

JSON_ARRAY_LENGTH(1)

Returns the array length of json (a string containing a JSON array).

json: The JSON to calculate the length of.

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.

JSON_FORMAT(json)

Returns json as a string

json: The JSON to format.

JSON_PARSE(json)

Parse string as a json.

json: The JSON string to parse.

JSON_SIZE(json, json_path)

Like json_extract(), but returns the size of the value. For objects or arrays, the size is the number of members, and the size of a scalar value is zero.

json: A JSON expression.
json_path: The JSON path of the value contained in json.

ARRAY_AGG(expression)

Returns an array created from the input expression elements.

expression: Any column or literal expression.

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.

APPROX_DISTINCT(expression [, e])

expression: The expression to perform the approximate count distinct on.
e: The error distribution.

CORR(numeric_expression1, numeric_expression2)

Returns the Pearson correlation coefficient of a set of number pairs.

numeric_expression1: The first series.
numeric_expression2: The second series.

COVAR_POP(numeric_expression1, numeric_expression2)

Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.

numeric_expression1: The first series.
numeric_expression2: The second series.

COVAR_SAMP(numeric_expression1, numeric_expression2)

Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.

numeric_expression1: The first series.
numeric_expression2: The second series.

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)

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_SAMP on.

VARIANCE(numeric_expression)

Alias for VAR_SAMP.

numeric_expression: The series to calculate VARIANCE on.

VARIANCE_POP(numeric_expression)

Alias for VAR_POP.

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.

PreviousActive Directory NextJSON

Last updated 1 year ago