ABS

Returns the absolute value of the given input

SYNTAX

abs(input)

• input
  • Type: number
  • Description: The attribute to return absolute value for

OUTPUT

number

EXAMPLE

SELECT abs(number_value) as absolute_value

ADD

Returns the sum of two integers.

SYNTAX

add(integer1, integer2)

• integer1
  • Type: int
  • Description: The first integer to add
• integer2
  • Type: int
  • Description: The second integer to add

OUTPUT

int

EXAMPLE

SELECT add(apples, oranges) AS total_fruit

ARRAY

Returns an array with the given elements.

SYNTAX

array()

OUTPUT

{'type': 'array', 'elementType': 'any'}

EXAMPLES

SELECT array(opening_act, headliner) AS bands

SELECT array(...some_array) AS identical

ARRAY_CONCAT

Converts all values in an array into strings and then concatenates them together into a single string.

SYNTAX

array_concat(array)

• array
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: The array whose values will be concatenated into a single string

OUTPUT

string

EXAMPLE

SELECT array_concat(example_arrays) AS concatenated_arrays

ATOF

Converts a string to a floating point value. (ASCII-to-Float)

SYNTAX

atof(string_to_convert)

• string_to_convert
  • Type: string
  • Description: The string to convert to a floating point value.

OUTPUT

double

EXAMPLE

SELECT atof(example_strings) AS example_strings_as_double;

ATOI

Converts a string to an integer value. (ASCII-to-Integer)

SYNTAX

atoi(string_to_convert)

• string_to_convert
  • Type: string
  • Description: The string to convert to an integer value.

OUTPUT

int

EXAMPLE

SELECT atoi(example_strings) AS example_strings_as_int;

CEIL

Returns the smallest integer that is greater than or equal to the given number.

SYNTAX

ceil(input)

• input
  • Type: number
  • Description: The input to compute the ceiling for.

OUTPUT

long

EXAMPLES

SELECT ceil(input_value) AS ceiling

SELECT ceil(input_value) AS ceiling

COALESCE

Returns the first input that is not null, or null if all inputs are null.

SYNTAX

coalesce()

OUTPUT

any

EXAMPLES

SELECT coalesce(firstName1, firstName2) AS nonNullName

SELECT coalesce(...ranked_names) AS nonNullName

CONCAT

Concatenates string values. If any of the arguments to concat is null, it returns null.

SYNTAX

concat()

OUTPUT

string

EXAMPLES

SELECT concat(first_name, last_name) AS combined

SELECT concat(...string_array) AS combined

DATEDIFF

Gets the difference in days between the start and end date. The result will be negative if the end date occurs before the start date. More precisely, this function counts the number of 24-hour periods between the start and end dates.

SYNTAX

datediff(endDate, startDate)

• endDate
  • Type: string
  • Description: The end date
• startDate
  • Type: string
  • Description: The start date

OUTPUT

long

EXAMPLE

SELECT start, "end", datediff("end", start) AS diff

DATETIME_ERROR

Tries to convert the input to a datetime, returning the conversion error, if any.

SYNTAX

datetime_error(input)

• input
  • Type: string
  • Description: The input to try to convert.

OUTPUT

string

EXAMPLE

SELECT datetime_error(date) as error

DATETIME_TO_ISO

Formats a datetime string to the ISO_LOCAL_DATE_TIME format using the specified patterns. Patterns are matched in sequence, and the function will return the first matched pattern. If no patterns are matched, this function returns null.

SYNTAX

datetime_to_iso(datetime_string, patterns)

• datetime_string
  • Type: string
  • Description: A datetime string
• patterns
  • Type: {'type': 'array', 'elementType': 'string'}
  • Description: An array of patterns for parsing the input

OUTPUT

string

EXAMPLES

SELECT datetime_to_iso(datetime, patterns_array) AS datetimes_as_iso

SELECT datetime_to_iso(datetime, patterns_array) AS datetimes_as_iso

DATE_AND_TIME_TO_ISO

Formats a date and time string to ISO datetime format using the specified patterns. Patterns are matched in sequence, and the function will return the first matched pattern. If no patterns are matched, this function returns null.

SYNTAX

date_and_time_to_iso(date_string, time_string, patterns)

• date_string
  • Type: string
  • Description: The string representing the date
• time_string
  • Type: string
  • Description: The string representing the time
• patterns
  • Type: {'type': 'array', 'elementType': 'string'}
  • Description: The patterns used for matching the date and time to the ISO format

OUTPUT

string

EXAMPLES

SELECT date_and_time_to_iso(dates, times, patterns_arrays) AS datetimes_as_iso

SELECT date_and_time_to_iso(dates, times, patterns_arrays) AS datetimes_as_iso

DAYOFMONTH

Gets the day-of-month field of a date.

SYNTAX

dayofmonth(datetime)

• datetime
  • Type: string
  • Description: The timestamp to get the day of month from

OUTPUT

int

EXAMPLES

SELECT dayOfMonth(date) AS day

SELECT dayOfMonth(date) AS day, datetime_error(date) as error

DECIMAL

Converts a numeric value to double type.

SYNTAX

decimal(input)

• input
  • Type: number
  • Description: The value to convert

OUTPUT

double

EXAMPLE

SELECT decimal(integer_value) as double_value

DISTINCT

Returns an array of all distinct items from the given array converted to strings.

SYNTAX

distinct(array)

• array
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: The array to remove duplicates from

OUTPUT

{'type': 'array', 'elementType': 'string'}

EXAMPLE

SELECT distinct(array) as distinctified

DURATION_TO_ISO

Converts to a given duration in the specified time unit to the ISO-8601 format. If minimal is true, the format is PnYnMnDTnHnMnS, which stands for 'Period: number of Years, number of Months, number of Days, Time: number of Hours, number of Minutes, number of Seconds.' If minimal is false, only the significant units are shown.

SYNTAX

duration_to_iso(duration, time_unit, minimal)

• duration
  • Type: double
  • Description: The positive duration value to convert to iso
• time_unit
  • Type: string
  • Description: A three letter string representing the unit of the duration. Valid options are: 'SEC', 'MIN', 'HRS', 'DAY', 'WKS'
• minimal
  • Type: bool
  • Description: Determines whether the duration is returned with only the significant values

OUTPUT

string

EXAMPLES

SELECT duration_to_iso(duration, time_unit, minimal) as isofied_duration

SELECT duration_to_iso(duration, time_unit, minimal) as isofied_duration

EQUALS_IGNORE_CASE

Case-insensitively checks whether two values are equal.

SYNTAX

equals_ignore_case(str1, str2)

• str1
  • Type: string
  • Description: The first value to compare
• str2
  • Type: string
  • Description: The second value to compare

OUTPUT

bool

EXAMPLE

SELECT equals_ignore_case(family_member, state) as is_equal

EXTRACT

Matches a string against a Java regular expression, and returns the specified group. The group 0 refers to the entire pattern. Indexes of individual groups start at 1. Returns null if no match is found, the group number or regular expression is invalid, or any of the input, regular expression, or group is null, the result is null.

SYNTAX

extract(input, regex, group_index)

• input
  • Type: string
  • Description: The string to be matched
• regex
  • Type: string
  • Description: The Java regular expression specifying the pattern to extract
• group_index
  • Type: int
  • Description: The index of the group in the regular expression to extract

OUTPUT

string

EXAMPLES

SELECT extract('2001: A Space Odyssey', '(\\d+):\\s?([\\w\\s]+)', group_idx) AS result

SELECT extract(input, regex, group_index) as result

SELECT extract('invalid regex', regex, 1) as result

EXTRACT_ALL

Matches a string against a Java regular expression, and returns results for all groups. Returns null if no match is found, the regular expression is invalid, or either of the input or regular expression is null.

SYNTAX

extract_all(input, regex)

• input
  • Type: string
  • Description: The string to be matched
• regex
  • Type: string
  • Description: The Java regular expression specifying the pattern to extract

OUTPUT

{'type': 'array', 'elementType': 'string'}

EXAMPLE

SELECT extract_all(input, regex) as result

FLOOR

Returns the greatest integer that is smaller than or equal to the given number.

SYNTAX

floor(input)

• input
  • Type: number
  • Description: The input to compute the floor for.

OUTPUT

long

EXAMPLE

SELECT floor(input_value) AS floored_value

GET

Return the value in the array at the specified index. If the index is invalid, return null. An index is invalid if it is negative, not an integer, or greater than or equal to the size of the array it is applied to.

SYNTAX

get(data, index)

• data
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: An array of values that you can index into.
• index
  • Type: int
  • Description: The index to apply to the data argument. Indexes are integers between 0 (inclusive) and the length of the array (exclusive).

OUTPUT

any

EXAMPLE

SELECT get(col, idx) AS new_col

GREATEST

Returns the greatest value of the list of values, skipping null values. It will return null if and only if all parameters are null. String characters are ranked from least to greatest in the following order:!"'#$%&()*+-,./:;<=>?_@[\]^{|}~0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz

SYNTAX

greatest()

OUTPUT

any

EXAMPLES

SELECT branch, greatest(sales_jan, sales_feb, sales_mar) AS q1_highest_month_sales

SELECT greatest(...string_array) AS greatest_string

SELECT greatest(...array_array) AS greatest_array

HASH

Returns a number, called a hash code, based on the input data. The hash code is always the same given the same inputs and is practically guaranteed to be different given different inputs.

SYNTAX

hash()

OUTPUT

long

EXAMPLES

SELECT hash(A, B) AS result

SELECT hash(A, B) AS result

SELECT hash(A, B) = hash(array(A, B)) AS "hash(elements) = hash(array)", hash(A, B) = hash({A, B}) AS "hash(elements) = hash(struct)"

HEX

Returns the hexadecimal value of the argument.

SYNTAX

hex(input)

• input
  • Type: int
  • Description: The input value.

OUTPUT

string

EXAMPLE

SELECT hex(input_value) AS hex_value

INTEGER

Converts a numeric value to int type.

SYNTAX

integer(input)

• input
  • Type: number
  • Description: The value to convert

OUTPUT

int

EXAMPLE

SELECT integer(double_value) as integer_value

ISNULL

Returns true if and only if the argument is null.

SYNTAX

isnull(input)

• input
  • Type: any
  • Description: The input value.

OUTPUT

bool

EXAMPLES

SELECT isnull(input_value) AS is_it_null

SELECT isnull(input_value) AS is_it_null

SELECT isnull(input_value) AS is_it_null

ISO_DIFFERENCE_DAYS

Returns the number of days between two ISO date-time strings rounded down to the nearest integer. If the first day comes after the second day, the result will be negative.

SYNTAX

iso_difference_days(date1, date2)

• date1
  • Type: string
  • Description: The first date in the difference
• date2
  • Type: string
  • Description: The second date in the difference

OUTPUT

int

EXAMPLE

SELECT iso_difference_days(date1, date2) AS date_difference

ISO_DATETIME_ADD

Adds an amount of time to a datetime string in ISO format.

SYNTAX

iso_datetime_add(datetime, duration, time_unit)

• datetime
  • Type: string
  • Description: A datetime string formatted to ISO-8601 duration format PnYnMnDTnHnMnS
• duration
  • Type: double
  • Description: Time unit, must be whole number if unit is 'YRS' or 'MTH'
• time_unit
  • Type: string
  • Description: A 3 letter string representing the unit of the duration. Valid options are: 'SEC', 'MIN', 'HRS', 'DAY', 'WKS', 'YRS', 'MTH'

OUTPUT

string

EXAMPLES

SELECT iso_datetime_add(datetime, duration, time_unit) AS new_datetime

SELECT iso_datetime_add(datetime, duration, time_unit) AS new_datetime

IS_NOT_NULL

Returns false if the input is equal to null, and true if it is not equal to null.

SYNTAX

is_not_null(input)

• input
  • Type: any
  • Description: The input to check

OUTPUT

bool

EXAMPLES

SELECT is_not_null(string_col) as string_not_null

SELECT is_not_null(string_array_col) as array_not_null

IS_NOT_EMPTY

Returns true if the input is not null and not equal to the empty string. Returns false if the input is equal to the empty string. Returns null if the input is null.

SYNTAX

is_not_empty(input)

• input
  • Type: string
  • Description: The input to check

OUTPUT

bool

EXAMPLE

SELECT is_not_empty(string_col) as not_empty

JACCARD_INDEX

Computes the Jaccard similarity index between two sets. It is calculated as the intersection of the two sets divided by the union of the two sets.

SYNTAX

jaccard_index(a, b)

• a
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: The first set
• b
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: The second set

OUTPUT

double

EXAMPLE

SELECT jaccard_index(set_a, set_b) AS similarity

LEAST

Returns the smallest value of the list of values, skipping null values. It will return null if and only if all parameters are null. String characters are ranked from least to greatest in the following order:!"'#$%&()*+-,./:;<=>?_@[\]^{|}~0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz

SYNTAX

least()

OUTPUT

any

EXAMPLES

SELECT branch, least(sales_jan, sales_feb, sales_mar) AS q1_lowest_month_sales

SELECT least(...string_array) AS least_string

SELECT least(...array_array) AS least_array

LEN

Returns the number of non-null, non-empty, non-whitespace items in an array.

SYNTAX

len(array)

• array
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: The array to count items of

OUTPUT

int

EXAMPLE

SELECT len(array) AS array_length

LENGTH

Returns the length, in characters, of the given string value

SYNTAX

length(input)

• input
  • Type: string
  • Description: The value to measure

OUTPUT

int

EXAMPLE

SELECT movie, length(movie) as num_characters

LOWER

Converts a string to lowercase. Applies to all characters.

SYNTAX

lower(input)

• input
  • Type: string
  • Description: The string to convert to lowercase

OUTPUT

string

EXAMPLE

SELECT lower(string) as lowercase_string

MATCHES

Matches a string against a Java regular expression. Returns true if the regular expression matches the entire input string, false if it does not, and null if the regular expression is invalid or if either of the input or regular expression is null.

SYNTAX

matches(regex, input)

• regex
  • Type: string
  • Description: The Java regular expression specifying the pattern to match
• input
  • Type: string
  • Description: The string to be matched

OUTPUT

bool

EXAMPLE

SELECT matches(regex, input) as result

MD5

Returns a hash value, according to the MD5 algorithm.

SYNTAX

md5(input)

• input
  • Type: string
  • Description: The input value.

OUTPUT

string

EXAMPLE

SELECT md5(input_value) AS md5_hash

MONTH

Gets the month field of a date.

SYNTAX

month(datetime)

• datetime
  • Type: string
  • Description: The timestamp to get the month of

OUTPUT

int

EXAMPLES

SELECT month(date) AS month

SELECT month(date) AS month, datetime_error(date) as error

MONOTONICALLY_INCREASING_ID

Returns a monotonically increasing identifier. The identifiers are guaranteed to be unique but not sequential. The results of this function are non-deterministic, different runs on the same dataset could produce different results.

SYNTAX

monotonically_increasing_id()

OUTPUT

long

EXAMPLE

SELECT monotonically_increasing_id() AS id, *

NUM

Returns the first numeric element of an array parsed as a double.

SYNTAX

num(array)

• array
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: The array to find the first numeric element of

OUTPUT

double

EXAMPLE

SELECT num(array) AS first_num_from_array

PARSE_JSON_ARRAY

Parses strings as JSON-encoded arrays of strings, returning the decoded values. All results are combined into a single multivalue, with no deduplication. Inputs that cannot be parsed as a JSON array are ignored. Values in arrays that are not strings are ignored.

SYNTAX

parse_json_array(json_array)

• json_array
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: A multivalue containing JSON-encoded arrays of string

OUTPUT

{'type': 'array', 'elementType': 'string'}

EXAMPLE

SELECT parse_json_array(json_array) AS parsed

POW

Returns the value of the first argument raised to the power of the second argument

SYNTAX

pow(base, exponent)

• base
  • Type: number
  • Description: The base of the exponential expression
• exponent
  • Type: number
  • Description: The exponent of the exponential expression

OUTPUT

double

EXAMPLE

SELECT pow(base, exponent) as result

RAND

Returns a random value within the range 0.0 (inclusive) to 1.0 (exclusive).

SYNTAX

rand()

OUTPUT

double

EXAMPLE

SELECT row_number, rand() as random_double

REPLACE

Replaces all occurrences of the target in the given string with the replacement.

SYNTAX

replace(string, target, replacement)

• string
  • Type: string
  • Description: The string to replace parts of
• target
  • Type: string
  • Description: The substring to replace in the string
• replacement
  • Type: string
  • Description: The string to replace the target with

OUTPUT

string

EXAMPLES

SELECT replace(string, target, replacement) AS replaced

SELECT replace('Hi my name is {name}!', '{name}', names) AS introductions

REPLACE_ALL

Replaces all matches of the target regex in the given string with the replacement.

SYNTAX

replace_all(string, target_regex, replacement)

• string
  • Type: string
  • Description: The string to replace parts of
• target_regex
  • Type: string
  • Description: The regex to replace in the string
• replacement
  • Type: string
  • Description: The string to replace the target with

OUTPUT

string

EXAMPLE

SELECT replace_all(string, target_regex, replacement) AS replaced

RINT

Returns the double value that is closest in value to the argument and is equal to a mathematical integer.

SYNTAX

rint(input)

• input
  • Type: number
  • Description: The input value

OUTPUT

double

EXAMPLE

SELECT rint(input_value) as rounded_value

ROUND

Returns the closest Long value to the argument.

SYNTAX

round(input)

• input
  • Type: number
  • Description: The input value

OUTPUT

long

EXAMPLE

SELECT round(input_value) as rounded_value

RUNTIME_DATE

Returns a string representing the date in ISO Format at the time the function is executed for each value. Each row may have a different runtime date.

SYNTAX

runtime_date()

OUTPUT

string

EXAMPLE

SELECT row_number, runtime_date() as date

RUNTIME_DATETIME

Returns a string representing the date and time in ISO Format at the time the function is executed for each value. Each row may have a different runtime date and time.

SYNTAX

runtime_datetime()

OUTPUT

string

EXAMPLE

SELECT row_number, runtime_datetime() as datetime

SHA1

Returns a hash value, according to the SHA-1 algorithm.

SYNTAX

sha1(input)

• input
  • Type: string
  • Description: The input value to be hashed.

OUTPUT

string

EXAMPLE

SELECT sha1(value) AS sha1_hashified

SIZE

Returns the number of elements in a given array

SYNTAX

size(input)

• input
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: The array in which to count elements

OUTPUT

int

EXAMPLE

SELECT collection, size(members) as num_members

SORT_ARRAY

Sorts the input array in ascending order, according to the natural ordering of the array elements. This will filter out null elements.

SYNTAX

sort_array(input)

• input
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: An array of values to be sorted.

OUTPUT

{'type': 'array', 'elementType': 'any'}

EXAMPLES

SELECT sort_array(unsorted_array) AS sorted_array

SELECT sort_array(unsorted_array) AS sorted_array

SELECT sort_array(unsorted_array) AS sorted_array

SELECT sort_array(unsorted_array) AS sorted_array

SPLIT

Splits a string into an array of strings based on where the provided regex matches.

SYNTAX

split(string, regex)

• string
  • Type: string
  • Description: The string to split
• regex
  • Type: string
  • Description: The regex to split by when matched

OUTPUT

{'type': 'array', 'elementType': 'string'}

EXAMPLE

SELECT split(string, regex) as result

STRIP_ACCENTS

Removes diacritics from a string.

SYNTAX

strip_accents(string)

• string
  • Type: string
  • Description: The string to strip the accents from

OUTPUT

string

EXAMPLE

SELECT strip_accents(with_accents) as no_accents

STR

Converts any input into a string

SYNTAX

str(some_object)

• some_object
  • Type: any
  • Description: The object to convert to a string

OUTPUT

string

EXAMPLES

SELECT str(booleans) AS stringified

SELECT str(int) AS stringified

SELECT str(double) AS stringified

SELECT str(string) AS stringified

STR_FORMAT

Returns a string using the specified format template with the inserted string arguments

SYNTAX

str_format(format_template, objects_to_insert)

• format_template
  • Type: string
  • Description: A string detailing how and where the string arguments should be formatted and inserted
• objects_to_insert
  • Type: {'type': 'array', 'elementType': 'any'}
  • Description: An array of objects to format and insert into the template

OUTPUT

string

EXAMPLES

SELECT str_format(templates, integer_values) AS format_result

SELECT str_format(templates, double_values) as format_result

SELECT str_format(templates, string_values) as format_result

SELECT str_format(time_format, array(1350933383321L)) AS time_formatted

STR_JOIN

Concatenates all strings in an array into a single string, inserting the delimiter between each item.

SYNTAX

str_join(delimiter, string_array)

• delimiter
  • Type: string
  • Description: The string to insert between each item in the array
• string_array
  • Type: {'type': 'array', 'elementType': 'string'}
  • Description: The array of string to join into a single string

OUTPUT

string

EXAMPLE

SELECT str_join(delimiter, string_values) AS concatenated_array

SUBSTR

Removes the portion of the string before the start_index. If start_index is positive or zero, it indexes the string starting from the beginning; if negative, it indexes the string starting from the end (so -1 gives you the last character of the string, -2 the last two characters and so on). The function returns the substring starting from the indexed position and continuing to the end of the string, or null if the indexed position is past the end of the string

SYNTAX

substr(string, start_index)

• string
  • Type: string
  • Description: The string to get the substring of
• start_index
  • Type: int
  • Description: The index to start the substring at

OUTPUT

string

EXAMPLE

SELECT substr(string, start_index) as substrings

SUBSTR2

Returns the part of the given string between the start_index and end_index. The values start_index and end_index may be either zero or positive (indexing from the start of the string) or negative (indexing from the end). Together, start_index (inclusive) and end_index (exclusive) determine the range of the string. If the range is empty then null is returned.

SYNTAX

substr2(string, start_index, end_index)

• string
  • Type: string
  • Description: The string to get the substring of
• start_index
  • Type: int
  • Description: The index to start the substring at
• end_index
  • Type: int
  • Description: The index to end the substring at

OUTPUT

string

EXAMPLE

SELECT substr2(string, start_index, end_index) as substrings

TAMR_ID

Computes a unique key from a string source ID and string entity ID. This key is identical to the one generated for the unified dataset based on origin_source_name and origin_entity_id.

SYNTAX

tamr_id(source_id, entity_id)

• source_id
  • Type: string
  • Description: the source identifier
• entity_id
  • Type: string
  • Description: the entity identifier

OUTPUT

string

EXAMPLE

SELECT *, tamr_id(source_id, entity_id) as tamr_id

TO_BOOLEAN

Attempts to convert a value of any type to a Boolean. If conversion fails, it returns null. Error messages for conversion failures can be accessed with to_boolean_error().

SYNTAX

to_boolean(input)

• input
  • Type: any
  • Description: the input value

OUTPUT

bool

EXAMPLES

SELECT to_boolean(string_col) as converted

SELECT to_boolean(double_col) as converted

SELECT to_boolean(string_arr_col) as converted

TO_BOOLEAN_ERROR

If converting the given value to a Boolean using to_boolean() would result in an error, it returns the error; otherwise returns null.

SYNTAX

to_boolean_error(input)

• input
  • Type: any
  • Description: the input value

OUTPUT

string

EXAMPLES

SELECT to_boolean(string_col) as converted, to_boolean_error(string_col) as error

SELECT to_boolean(string_arr_col) as converted, to_boolean_error(string_arr_col) as error

TO_DOUBLE

Attempts to convert a value of any type to a Double. If conversion fails, it returns null. Error messages for conversion failures can be accessed with to_double_error().

SYNTAX

to_double(input)

• input
  • Type: any
  • Description: The input value

OUTPUT

double

EXAMPLES

SELECT to_double(long_col) as converted

SELECT to_double(float_col) as converted

SELECT to_double(string_col) as converted

SELECT to_double(bool_col) as converted

SELECT to_double(string_arr_col) as converted

TO_DOUBLE_ERROR

If converting the given value to a Double using to_double() would result in an error, it returns the error; otherwise returns null.

SYNTAX

to_double_error(input)

• input
  • Type: any
  • Description: the input value

OUTPUT

string

EXAMPLES

SELECT to_double(string_col) as converted, to_double_error(string_col) as error

SELECT to_double(string_arr_col) as converted, to_double_error(string_arr_col) as error

TO_INT

Attempts to convert a value of any type to an Integer. If conversion fails, it returns null. Error messages for conversion failures can be accessed with to_int_error().

SYNTAX

to_int(value)

• value
  • Type: any
  • Description: The input value

OUTPUT

int

EXAMPLES

SELECT to_int(double_col) as converted

SELECT to_int(long_col) as converted

SELECT to_int(bool_col) as converted

SELECT to_int(string_col) as converted

SELECT to_int(string_arr_col) as converted

TO_INT_ERROR

If converting the given value to an Integer using to_int() would result in an error, it returns the error; otherwise returns null.

SYNTAX

to_int_error(input)

• input
  • Type: any
  • Description: the input value

OUTPUT

string

EXAMPLES

SELECT to_int(string_col) as converted, to_int_error(string_col) as error

SELECT to_int(string_arr_col) as converted, to_int_error(string_arr_col) as error

SELECT to_int(double_col) as converted, to_int_error(double_col) as error

TO_LONG

Attempts to convert a value of any type to a Long. If conversion fails, it returns null. Error messages for conversion failures can be accessed with to_long_error().

SYNTAX

to_long(value)

• value
  • Type: any
  • Description: the input value

OUTPUT

long

EXAMPLES

SELECT to_long(double_col) as converted

SELECT to_long(bool_col) as converted

SELECT to_long(string_col) as converted_str

SELECT to_long(string_arr_col) as converted

TO_LONG_ERROR

If converting the given value to a Long using to_long() would result in an error, it returns the error; otherwise returns null.

SYNTAX

to_long_error(input)

• input
  • Type: any
  • Description: the input value

OUTPUT

string

EXAMPLES

SELECT to_long(string_col) as converted, to_long_error(string_col) as error

SELECT to_long(string_arr_col) as converted, to_long_error(string_arr_col) as error

SELECT to_long(double_col) as converted, to_long_error(double_col) as error

TO_STRING

Attempts to convert a value of any type to a String. If conversion fails, it returns null. Error messages for conversion failures can be accessed with to_string_error().

SYNTAX

to_string(value)

• value
  • Type: any
  • Description: the input value

OUTPUT

string

EXAMPLES

SELECT to_string(long_col) as converted

SELECT to_string(double_col) as converted

SELECT to_string(boolean_col) as converted

SELECT to_string(array_col) as converted

TO_STRING_ERROR

If converting the given value to a String using to_string() would result in an error, it returns the error; otherwise returns null.

SYNTAX

to_string_error(input)

• input
  • Type: any
  • Description: the input value

OUTPUT

string

EXAMPLE

SELECT to_string(int_arr_col) as converted, to_string_error(int_arr_col) as error

TRIM

Trim the spaces from both ends for the specified string.

SYNTAX

trim(input)

• input
  • Type: string
  • Description: The value to trim

OUTPUT

string

EXAMPLE

SELECT trimmable, trim(trimmable) as trimmed

TYPEOF

Returns the Spark type of the given object as a string.

SYNTAX

typeof(object)

• object
  • Type: any
  • Description: The object to find the type of

OUTPUT

string

EXAMPLES

SELECT typeof(int) AS type

SELECT typeof(double) AS type

SELECT typeof(boolean) AS type

SELECT typeof(string) AS type

SELECT typeof(arrays) AS type