Expression Evaluator Processor

This processor is responsible for performing transformation operations on an incoming dataset, e.g., replace, format, trim, uppercase, lowercase, etc. It uses spark expression language for preparing transformation query.

Expression Evaluator Configuration

To add an Expression Evaluator processor into your pipeline, drag the processor to the canvas and right-click on it to configure.

Field	Description
Expression	Provide SQL expression using SparkSQL functions, that evaluates to a column. Evaluated value can be updated to an existing column or can be assigned to a new column. Note: Enclose string literal/constant values inside single quotes e.g. ‘John’, ‘Suite 130 Los Gatos, CA 95032, US’.
Validate	Validate the expressions applied on the column.

Field

Description

Expression

Provide SQL expression using SparkSQL functions, that evaluates to a column. Evaluated value can be updated to an existing column or can be assigned to a new column.

Note: Enclose string literal/constant values inside single quotes e.g. ‘John’, ‘Suite 130 Los Gatos, CA 95032, US’.

Validate

Validate the expressions applied on the column.

All the columns of the schema are populated and you can apply any transformation to it. The functions that can be applied are all listed in a table to the right.

Refer to the Expression Evaluator section in the topic Data Preparation Processors.

Enlisted Functions

ABS

Field	Description
Description	Computes the absolute value.
Parameters	arg0: The column for which absolute value to be calculated.
Returns	Returns the computed absolute value.
Throws	ApplicationException
Example	abs(77.76) will return 77.76

ACOS

Field	Description
Description	Computes the cosine inverse of the given value; the returned angle is in the range 0.0 through pi.
Parameters	arg0: The column for which cosine inverse to be calculated.
Returns	Returns the computed cosine inverse in the range 0.0 through pi.
Throws	ApplicationException
Example	acos(0.45) will return 1.104031001096478

ADD_MONTHS

Field	Description
Description	Computes the date that is ‘arg1’ after ‘arg0’
Parameters	arg0: The date to which months to be added. arg1: No of months to be added.
Returns	Returns the date that is numMonths after startDate
Throws	ApplicationException
Example	add_months(“2009-03-01”,2) will return “2009-05-01”

ARRAY

Field	Description
Description	Returns an array with the given elements
Parameters	arg0: The given columns to create array column
Returns	Returns an array with the given elements
Throws	ApplicationException
Example	array(1, 2, 3) will return [1,2,3]

ARRAY_CONTAINS

Field	Description
Description	Returns TRUE if the array contains value.
Parameters	arg0:An array column arg1:A value to be checked
Returns	A boolean true/false
Throws	ApplicationException
Example	We have taken column1.colors as [“black”,“red”] array_contains(@{column.schema.column1.colrs},“red”) will return true

ARRAY_DISTINCT

Field	Description
Description	Removes duplicate values from the array
Parameters	arg0: The given array column
Returns	Returns the array with duplicate values removed
Throws	ApplicationException
Example	array_distinct(array(1, 2, 3, null, 3)) will return [1,2,3,null]

ARRAY_EXCEPT

Field	Description
Description	Returns an array of the elements in array1 but not in array2, without duplicates
Parameters	arg0: First array column arg1: Second array column
Returns	Returns an array of the elements in array1 but not in array2, without duplicates
Throws	ApplicationException
Example	array_except(array(1, 2, 3), array(1, 3, 5)) will return [2]

ARRAY_INTERSECT

Field	Description
Description	Performs intersection of array1 and array2, without duplicates.
Parameters	arg0: First array column arg1: Second array column
Returns	Returns an array of the elements in the intersection of array1 and array2, without duplicates
Throws	ApplicationException
Example	array_intersect(array(1, 2, 3), array(1, 3, 5)) will return [1,3]

ARRAY_JOIN

Field	Description
Description	Concatenates the elements of the given array using the delimiter and an optional string to replace nulls. If no value is set for nullReplacement, any null value is filtered
Parameters	arg0: array column arg1: delimiter arg2: nullReplacement
Returns	Returns the concatenated array
Throws	ApplicationException
Example	array_join(array(‘hello’, null ,‘world’), ’ ‘, ‘,’) will return hello , world

ARRAY_MAX

Field	Description
Description	Returns the maximum value in the array. NULL elements are skipped
Parameters	arg0: The array column
Returns	Returns the maximum value in the array. NULL elements are skipped
Throws	ApplicationException
Example	array_max(array(1, 20, null, 3)) will return 20

ARRAY_MIN

Field	Description
Description	Returns the minimum value in the array. NULL elements are skipped
Parameters	arg0: The array column
Returns	Returns the minimum value in the array. NULL elements are skipped
Throws	ApplicationException
Example	array_min(array(1, 20, null, 3)) will return 1

ARRAY_POSITION

Field	Description
Description	Returns the (1-based) index of the first element of the array as long
Parameters	arg0: The array column arg1: The position
Returns	Returns the (1-based) index of the first element of the array as long
Throws	ApplicationException
Example	array_position(array(3, 2, 1), 1) will return 3

array_remove

Field	Description
Description	Remove all elements that equal to element from array.
Parameters	arg0: The array column. arg1: The position.
Returns	Returns the array with elements removed.
Throws	ApplicationException
Example	array_remove(array(1, 2, 3, null, 3), 3) will return [1,2,null]

array_repeat

Field	Description
Description	Returns the array containing element count times.
Parameters	arg0: The array column. arg1: The count.
Returns	Returns the array containing element count times.
Throws	ApplicationException
Example	array_repeat(‘123’, 2) will return [“123”,“123”]

array_sort

Field	Description
Description	Sorts the input array in ascending order. The elements of the input array must be orderable. Null elements will be placed at the end of the returned array.
Parameters	arg0: The array column.
Returns	Returns the sorted array.
Throws	ApplicationException
Example	array_sort(array(‘b’, ’d’, null, ‘c’, ‘a’)) will return [“a”,“b”,“c”,“d”,null]

array_union

Field	Description
Description	Returns an array of the elements in the union of array1 and array2, without duplicates.
Parameters	arg0: The first array column. arg1: The second array column.
Returns	Returns an array of the elements in the union of array1 and array2, without duplicates.
Throws	ApplicationException
Example	array_union(array(1, 2, 3), array(1, 3, 5)) will return [1,2,3,5]

arrays_overlap

Field	Description
Description	Returns true if a1 contains at least a non-null element present also in a2. If the arrays have no common element and they are both non-empty and either of them contains a null element null is returned, false otherwise.
Parameters	arg0: The first array column. arg1: The second array column.
Returns	Returns true or false.
Throws	ApplicationException
Example	arrays_overlap(array(1, 2, 3), array(3, 4, 5)) will return true

arrays_zip

Field	Description
Description	Returns a merged array of structs in which the N-th struct contains all N-th values of input arrays.
Parameters	arg0: The Columns to be zipped.
Returns	Returns a merged array of structs in which the N-th struct contains all N-th values of input arrays.
Throws	ApplicationException
Example	arrays_zip(array(1, 2, 3), array(2, 3, 4)) Will return [{“0”:1,“1”:2},{“0”:2,“1”:3},{“0”:3,“1”:4}]

ascii

Field	Description
Description	Computes the numeric value of the first character of the string column, and returns the result as an int column.
Parameters	arg0: The string for which the first character’s numeric value to be calculated.
Returns	Returns the ascii value as an int column.
Throws	ApplicationException
Example	ascii(“An apple”) will return 65

asin

Field	Description
Description	Computes the sine inverse of the given value; the returned angle is in the range -pi/2 through pi/2
Parameters	arg0: The column for which sine inverse to be calculated.
Returns	Returns the computed sine inverse in the range -pi/2 through pi/2.
Throws	ApplicationException
Example	asin(0.45) will return 0.4667653256984187

atan

Field	Description
Description	Computes the tangent inverse of the given value.
Parameters	arg0: The column for which tangent inverse to be calculated
Returns	Returns the computed the tangent inverse.
Throws	ApplicationException
Example	atan(0.45) will return 0.42285391621948626

atan2

Field	Description
Description	Computes the angle theta from the conversion of rectangular coordinates (arg0, arg1) to polar coordinates (arg1, theta).
Parameters	arg0: The x rectangular coordinate. arg1: The y rectangular coordinate.
Returns	Returns the computed angle theta.
Throws	ApplicationException
Example	atan2(12, 71.21) will return 1.403849169952035

base64

Field	Description
Description	Converts the argument from a binary bin to a base 64 string.
Parameters	arg0: The Column to be converted to base64 string.
Returns	Returns the base64 string.
Throws	ApplicationException
Example	base64(‘Spark SQL’) will return U3BhcmsgU1FM

bigint

Field	Description
Description	Casts the value expr to the target data type bigint.
Parameters	arg0:The column or expression.
Returns	Returns the bigint column or value.
Throws	ApplicationException
Example	SELECT bigint(column); will cast column to bigint

bin

Field	Description
Description	Returns the string representation of the long value expr represented in binary
Parameters	arg0: The numerical Column to be converted to represented in binary.
Returns	Returns the binary representation.
Throws	ApplicationException
Example	bin(13) will return 1101

boolean

Field	Description
Description	Casts the value expr to the target data type boolean.
Parameters	arg0:The column or expression.
Returns	Returns the boolean column or value.
Throws	ApplicationException
Example	SELECT boolean(expr); will cast expression to boolean

bround

Field	Description
Description	Computes the value of the column arg0 rounded to 0 decimal places with HALF_EVEN round mode.
Parameters	arg0: The column for which value rounded to 0 decimal places with HALF_EVEN round mode to be calculated.
Returns	Returns the computed value.
Throws	ApplicationException
Example	bround(71.21) will return 71.0

cast

Field	Description
Description	Casts the value expr to the target data type
Parameters	arg0:The column or expression.
Returns	Returns the targeted type
Throws	ApplicationException
Example	SELECT cast(‘10’ as int); will cast 10 to int

cbrt

Field	Description
Description	Computes the cube-root of the given value.
Parameters	arg0: The column for which cube-root to be calculated
Returns	Returns the computed cube-root.
Throws	ApplicationException
Example	cbrt(80.89) will return 4.324789202233814

ceil

Field	Description
Description	Computes the ceiling of the given value.
Parameters	arg0: The column for which ceiling to be calculated.
Returns	Returns the computed ceiling.
Throws	ApplicationException
Example	ceil(77.76) will return 4.2682720044742055

ceiling

Field	Description
Description	Computes the ceiling of the given value.
Parameters	arg0: The column for which ceiling to be calculated.
Returns	Returns the computed ceiling.
Throws	ApplicationException
Example	SELECT ceiling(-0.1); will return 0

char

Field	Description
Description	Converts the ASCII value to equivalent character. If n is larger than 256 the result is equivalent to chr(n % 256).
Parameters	arg0: The ASCII value.
Returns	Returns the ASCII character having the binary equivalent to expr.
Throws	ApplicationException
Example	SELECT char(65); will return A

char_length

Field	Description
Description	Calculates the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.
Parameters	arg0: The string column or expression.
Returns	Returns the character length of string data or number of bytes of binary data.
Throws	ApplicationException
Example	SELECT char_length(‘Spark SQL ‘); will return 10

chr

Field	Description
Description	Converts the ASCII character having the binary equivalent to expr. If n is larger than 256 the result is equivalent to chr(n % 256)
Parameters	arg0: The ASCII value.
Returns	Returns the ASCII character having the binary equivalent to expr.
Throws	ApplicationException
Example	SELECT chr(65); will return A

coalesce

Field	Description
Description	Returns the first non-null argument if exists. Otherwise, null.
Parameters	arg0:columns representing expressions.
Returns	Returns the first non-null argument if exists. Otherwise, null.
Throws	ApplicationException
Example	coalesce(NULL, 1, NULL) will return 1

concat

Field	Description
Description	Concatenates multiple input string columns together into a single string column.
Parameters	arg0: The String columns to be concatenated.
Returns	Returns the concatenated string as a single string column.
Throws	ApplicationException
Example	concat(“format”,“string”) will return “formatstring”

concat_ws

Field	Description
Description	Concatenates multiple input string columns together into a single string column, using the given separator.
Parameters	arg0: The separator to be used. arg1: The String columns to be concatenated.
Returns	Returns the concatenated strings using the given separator as a single string column.
Throws	ApplicationException
Example	concat_ws("-",“format”,“string”) will return “format-string”

conv

Field	Description
Description	Converts a number from a given base to another.
Parameters	arg0:A number/String number column arg1:Integer value of base from which a number is to be converted arg2:Integer value of base to which a number is to be converted
Returns	A string value.
Throws	ApplicationException
Example	We have taken column1 as ‘258’ conv(@{column.schema.column1},10,2) will return 100000010

cos

Field	Description
Description	Computes the cosine of the given value.
Parameters	arg0: The column for which cosine to be calculated.
Returns	Returns the computed cosine.
Throws	ApplicationException
Example	cos(76.56) will return 0.3977126102073901

cosh

Field	Description
Description	Computes the hyperbolic cosine of the given value.
Parameters	arg0: The column for which hyperbolic cosine to be calculated.
Returns	Returns the computed hyperbolic cosine.
Throws	ApplicationException
Example	cosh(71.21) will return -0.5004897466536994

crc32

Field	Description
Description	Computes a cyclic redundancy check value for string.
Parameters	arg0:A string argument
Returns	bigint value
Throws	Application Exception
Example	We have taken column1 as ‘ABC’ crc32(@{column.schema_id.column1}) will return 2743272264

current_date

Field	Description
Description	Computes the current date as a date column.
Parameters	-
Returns	Returns the current date as a date column
Throws	ApplicationException
Example	current_date() will return the current date.

current_timestamp

Field	Description
Description	Computes the current timestamp as a timestamp column.
Parameters	-
Returns	Returns the current timestamp as a timestamp column.
Throws	ApplicationException
Example	current_timestamp() will return the current timestamp.

date_add

Field	Description
Description	Computes the date that is ‘arg1’ days after start date
Parameters	arg0: The date to which days to be added. arg1: No of days to be added.
Returns	Returns the computed date.
Throws	ApplicationException
Example	date_add(“2009-03-01”,2) will return “2009-03-03”

date_format

Field	Description
Description	Converts a date/timestamp/string to a value of string in the format specified by the date format given by the second argument.
Parameters	arg0: The date/timestamp/string to be converted. arg1: The format to which the date/timestamp/string to be converted.
Returns	Returns the converted string.
Throws	ApplicationException
Example	date_format(“2009-03-01”,“MM-dd-yyyy”) will return “03-01-2009”

date_sub

Field	Description
Description	Computes the date that is ‘arg1’ days before start date.
Parameters	arg0: The date to which days to be substracted. arg1: No of days to be substracted.
Returns	Returns the computed date.
Throws	ApplicationException
Example	date_sub(“2009-03-02”,1) will return “2009-03-01”

date_trunc

Field	Description
Description	Returns timestamp ts truncated to the unit specified by the format model fmt. fmt should be one of [“YEAR” “YYYY” “YY” “MON” “MONTH” “MM” “DAY” “DD” “HOUR” “MINUTE” “SECOND” “WEEK” “QUARTER”]
Parameters	arg0: The fmt format. arg1: The ts timestamp.
Returns	Returns timestamp ts truncated to the unit specified by the format model fmt.
Throws	ApplicationException
Example	date_trunc(‘YEAR’, ‘2015-03-05T09:32:05.359’) will return 2015-01-01 00:00:00

datediff

Field	Description
Description	Computes the number of days from ‘arg0’ date to ‘arg1’ date.
Parameters	arg0: The end date. arg1: The start date.
Returns	Returns the computed number of days.
Throws	ApplicationException.
Example	datediff(“2009-03-01”,“2009-02-27”) will return 2

day

Field	Description
Description	Extracts and returns the day of month of the given date/timestamp.
Parameters	arg0: The column or expression.
Returns	Returns the day of month of the date/timestamp.
Throws	ApplicationException
Example	SELECT day(‘2009-07-30’); will return 30.

dayofmonth

Field	Description
Description	Extracts the day of the month as an integer from a given date/timestamp/string.
Parameters	arg0: The date/timestamp/string from which the day of month to be extracted.
Returns	Returns the extracted day as an integer.
Throws	ApplicationException
Example	dayofmonth(“2009-03-01”) will return 1

dayofweek

Field	Description
Description	Returns the day of the week for date/timestamp (1 = Sunday).
Parameters	arg0: The date column.
Returns	Returns the day of the week for date/timestamp
Throws	ApplicationException
Example	Dayofweek(‘2009-07-30’) will return 5

dayofyear

Field	Description
Description	Extracts the day of the year as an integer from a given date/timestamp/string.
Parameters	arg0: The date/timestamp/string from which the day of year to be extracted.
Returns	Returns the extracted day as an integer.
Throws	ApplicationException
Example	dayofyear(“2017-12-15”) will return 349

decimal

Field	Description
Description	Casts the value expr to the target data type decimal.
Parameters	arg0:The column or expression.
Returns	Returns the decimal column or value.
Throws	ApplicationException
Example	SELECT decimal(column); will cast column to decimal

decode

Field	Description
Description	Decodes the first argument using the second argument character set.
Parameters	arg0:Column to be decoded. arg1: The charset.
Returns	Returns the decoded column.
Throws	ApplicationException
Example	decode(encode(‘abc’, ‘utf-8’), ‘utf-8’) will return abc

degrees

Field	Description
Description	Converts an angle measured in radians to an approximately equivalent angle measured in degrees.
Parameters	arg0: The column for which the equivalent angle measured in degrees to be calculated.
Returns	Returns the converted angle measured in degrees.
Throws	ApplicationException.
Example	degrees(71.21) will return 4080.0324066707394

dense_rank

Field	Description
Description	Computes the rank of a value in a group of values. Unlike the function rank, dense_rank will not produce gaps in the ranking sequence.
Parameters	arg0:Not applicable
Returns	The calculated dense rank.
Throws	ApplicationException.
Example	select dense_rank() OVER (order by col) will return 1,2,3,4…

element_at

Field	Description
Description	For Array Column Returns element of array at given (1-based) index. If index < 0, accesses elements from the last to the first. For map column,Returns NULL if the index exceeds the length of the array.
Parameters	arg0:Array or map column. arg1: index or keyt.
Returns	Returns the element.
Throws	ApplicationException.
Example	element_at(array(1, 2, 3), 2) will return 2 and element_at(map(1, ‘a’, 2, ‘b’), 2) will return b

encode

Field	Description
Description	Encodes the first argument using the second argument character set.
Parameters	arg0:Column to be encoded. arg1: The charset.
Returns	Returns the encoded column.
Throws	ApplicationException.
Example	encode(‘abc’, ‘utf-8’) will return abc

exp

Field	Description
Description	Computes the exponential of the given value.
Parameters	arg0: The column for which exponential to be calculated.
Returns	Returns the computed exponential.
Throws	ApplicationException.
Example	exp(0.78) will return 2.18147220308578

explode

Field	Description
Description	Separates the elements of array expr into multiple rows, or the elements of map expr into multiple rows and columns.
Parameters	arg0: The expr Column.
Returns	Returns the exploded column.
Throws	ApplicationException.
Example	explode(array(10, 20)) will return 10, 20 in a new column.

explode_outer

Field	Description
Description	Separates the elements of array expr into multiple rows, or the elements of map expr into multiple rows and columns.
Parameters	arg0: The expr Column.
Returns	Returns the exploded column.
Throws	ApplicationException.
Example	explode_outer(array(10, 20)) will return 10, 20

expm1

Field	Description
Description	Computes the exponential of the given value minus one.
Parameters	arg0: The column for which exponential minus one to be calculated.
Returns	Returns the computed value.
Throws	ApplicationException.
Example	expm1(0.23) will return 0.2586000151807663

expr

Field	Description
Description	Parses the expression string into the column that it represents.
Parameters	arg0: The expression string to be parsed.
Returns	Returns the parsed expression string.
Throws	ApplicationException.
Example	expr(“colA”, “colB as newName”) will return two columns colA and newName

factorial

Field	Description
Description	Computes the factorial of the given value.
Parameters	arg0: The column for which factorial to be calculated.
Returns	Returns the computed factorial.
Throws	ApplicationException.
Example	Factorial(11) will return 39916800

flatten

Field	Description
Description	Transforms an array of arrays into a single array.
Parameters	arg0: The array of array Column.
Returns	Returns the flatten array.
Throws	ApplicationException.
Example	flatten(array(array(1, 2), array(3, 4))) will return [1,2,3,4]

float

Field	Description
Description	Casts the value expr to the target data type float.
Parameters	arg0:The column or expression.
Returns	Returns the float column or value.
Throws	ApplicationException
Example	SELECT float(column); will cast column to float

floor

Field	Description
Description	Computes the floor of the given value.
Parameters	arg0: The column for which floor to be calculated.
Returns	Returns the computed floor.
Throws	ApplicationException.
Example	floor(71.21) will return 71

format_number

Field	Description
Description	Formats numeric column arg0 to a format like ‘#,###,###.##’, rounded to arg1 decimal places, and returns the result as a string column.
Parameters	arg0: The column to be formated. arg1: The integer specifying the decimal places to be used for rounding.
Returns	Returns the formated result as a string column.
Throws	ApplicationException.
Example	format_number(7120.12, 1) will return 7,120.1

format_string

Field	Description
Description	Formats the arguments in printf-style and returns the result as a string column.
Parameters	arg0: The pintf style format. arg1: The columns to be formatted.
Returns	Returns the formated arguments as a string column.
Throws	ApplicationException.
Example	We have taken column1 as “cow” , column2 as “moon” and column3 as 2 format_string(“the %s jumped over the %s, %d times”,@{column.schema.column1},@{column.schema.column2},@{column.schema.column3}) will return “the cow jumped over the moon 2 times”

from_json

Field	Description
Description	Returns a struct value with the given jsonStr and schema.
Parameters	arg0:The Json string column. arg1: The schema column. arg2: The properties map.
Returns	Returns the struct value.
Throws	ApplicationException.
Example	from_json(’{“a”:1, “b”:0.8}’, ‘a INT, b DOUBLE’) will return {“a”:1, “b”:0.8} and from_json(’{“time”:“26/08/2015”}’, ’time Timestamp’, map(’timestampFormat’, ‘dd/MM/yyyy’))will return {“time”:“2015-08-26 00:00:00.0”}

from_unixtime

Field	Description
Description	Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the given format.
Parameters	arg0: The number of seconds from unix epoch. arg1: The format for timestamp in current system timezone to which conversion has to be done.
Returns	Returns the converted string.
Throws	ApplicationException.
Example	from_unixtime(1255033470,“yyyy-dd-MM”) will return 2009-09-10

from_utc_timestamp

Field	Description
Description	Given a timestamp like ‘2017-07-14 02:40:00.0’, interprets it as a time in UTC, and renders that time as a timestamp in the given time zone.
Parameters	arg0:timestamp column. arg1: Timezone column.
Returns	Returns the timestamp.
Throws	ApplicationException.
Example	from_utc_timestamp(‘2016-08-31’, ‘Asia/Seoul’) will return 2016-08-31 09:00:00

get_json_object

Field	Description
Description	Extracts a json object from path.
Parameters	arg0:The json txt column. arg1: the path.
Returns	Returns the extracted json object.
Throws	ApplicationException.
Example	get_json_object(’{“a”:“b”}’, ‘$.a’) will return b

greatest

Field	Description
Description	It gives the greatest value of the list of values.This function takes at least 2 parameters.
Parameters	arg0:A column from the schema arg1:A column from the schema
Returns	A Column.
Throws	ApplicationException.
Example	We have taken column1 as ‘258’, column2 as ‘259’ greatest(@{column.schema.column1},@{column.schema.column2}) will return column2’s values

hash

Field	Description
Description	Returns a hash value of the arguments.
Parameters	arg0:The columns for which hash to be calculated.
Returns	Returns a hash value of the arguments.
Throws	ApplicationException.
Example	hash(‘Spark’, array(123), 2) will return -1321691492

hex

Field	Description
Description	If the argument is an INT, hex returns the number as a STRING in hexadecimal format. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING.
Parameters	arg0:A int/string column
Returns	A string value.
Throws	ApplicationException.
Example	We have taken column1 as 258, hex(@{column.schema.column1}) will return 102

hour

Field	Description
Description	Extracts the hours as an integer from a given date/timestamp/string.
Parameters	arg0: The date/timestamp/string from which the hours to be extracted.
Returns	Returns the extracted hours as an integer.
Throws	ApplicationException.
Example	hour(“2017-12-15 11:02:03”) will return 11

hypot

Field	Description
Description	Computes sqrt(arg0^2^ + arg1^2^) without intermediate overflow or underflow.
Parameters	arg0: Will be used while computing sqrt. arg1: Will be used while computing sqrt.
Returns	Returns the computed sqrt(arg0^2^ + arg1^2^).
Throws	ApplicationException.
Example	hypot(71.21, 10.5) will return 71.97995533209642

ifnull

Field	Description
Description	Returns expr2 if expr1 is null, or expr1 otherwise.
Parameters	arg0:The first column expression. arg1:The second column expression.
Returns	Returns the binary.
Throws	ApplicationException.
Example	SELECT nvl(NULL, array(‘2’)); will return [“2”]

initcap

Field	Description
Description	Computes a new string column by converting the first letter of each word to uppercase.
Parameters	arg0: The input string.
Returns	Returns the converted string column.
Throws	ApplicationException.
Example	initcap(“apple”) will return “Apple”

input_file_name

Field	Description
Description	Returns the name of the file being read, or empty string if not available.
Parameters	-
Returns	Returns the name of the file being read, or empty string if not available.
Throws	ApplicationException.
Example	input_file_name() - will return the name of the file being read

instr

Field	Description
Description	Locate the position of the first occurrence of given substring in the given string column.
Parameters	arg0: The string column in which the location to be determined. arg1: The substring for which the position to be determined.
Returns	Returns the position of the first occurrence of substring.
Throws	ApplicationException.
Example	instr(“apple”,“le”) will return 4

int

Field	Description
Description	Casts the value expr to the target data type int.
Parameters	arg0:The column or expression.
Returns	Returns the int column or value.
Throws	ApplicationException.
Example	SELECT int(column); will cast column to int

isnan

Field	Description
Description	Returns true if the input is Not a Number.
Parameters	arg0:A column whose values needs to be checked
Returns	A boolean true if the value is not a number
Throws	Application Exception.
Example	We have taken column1 as ‘abc’ isnan(@{column.schema.column1}) will return true

isnotnull

Field	Description
Description	Checks if the given expression is not null.
Parameters	arg0:The column or expression.
Returns	Returns true if expr is not null, or false otherwise.
Throws	ApplicationException.
Example	SELECT isnotnull(1); will return true.

isnull

Field	Description
Description	Returns true if a is NULL and false otherwise.
Parameters	arg0:A column whose values needs to be checked
Returns	A boolean true if the value is null.
Throws	Application Exception.
Example	We have taken column1 as ‘abc’ isnull(@{column.schema.column1}) will return false

lag

Field	Description
Description	Returns the value of input at the offsetth row before the current row in the window. The default value of offset is 1 and the default value of default is null. If the value of input at the offsetth row is null, null is returned. If there is no such offset row (e.g., when the offset is 1, the first row of the window does not have any previous row), default is returned.
Parameters	arg0:The input - a string expression to evaluate offset rows before the current row. arg1:The offset- an int expression which is rows to jump back in the partition. arg1:The default- a string expression which is to use when the offset row does not exist.
Returns	Returns the value of input at the offsetth row before the current row in the window.
Throws	ApplicationException.
Example	select lag(col, 1) OVER (order by col)

last_day

Field	Description
Description	Given a date column, returns the last day of the month which the given date belongs to.
Parameters	arg0: The date from which last day of month to be extracted.
Returns	Returns the computed last day.
Throws	ApplicationException.
Example	last_day(“2017-12-15”) will return “2017-12-31”

last_value

Field	Description
Description	Returns the last value of expr for a group of rows. If isIgnoreNull is true, returns only non-null values.
Parameters	arg0:The column or expression. arg1:The isIgnoreNull.
Returns	Returns the last value of expr.
Throws	ApplicationException.
Example	SELECT last_value(col) FROM VALUES (10), (5), (20) AS tab(col); will return 20

lcase

Field	Description
Description	Converts str with all characters changed to lowercase.
Parameters	arg0:The column or expression.
Returns	Returns str with all characters changed to lowercase.
Throws	ApplicationException
Example	SELECT lcase(‘SparkSql’); will return sparksql

lead

Field	Description
Description	Returns the value of input at the offsetth row after the current row in the window. The default value of offset is 1 and the default value of default is null. If the value of input at the offsetth row is null, null is returned. If there is no such an offset row (e.g., when the offset is 1, the last row of the window does not have any subsequent row), default is returned.
Parameters	arg0:The input- a string expression to evaluate offset rows after the current row. arg1:The offset- an int expression which is rows to jump ahead in the partition. arg1:The default- a string expression which is to use when the offset is larger than the window. The default value is null.
Returns	Returns the value of input at the offsetth row before the current row in the window.
Throws	ApplicationException
Example	select lead(col, 1) OVER (order by col)

least

Field	Description
Description	It gives the least value of the list of values.This function takes at least 2 parameters.
Parameters	arg0:A column from the schema arg1:A column from the schema
Returns	A column.
Throws	ApplicationException
Example	We have taken column1 as ‘258’, column2 as ‘259’ least(@{column.schema.column1},@{column.schema.column2}) will return column1’s values

length

Field	Description
Description	Computes the length of a given string or binary column.
Parameters	arg0: The string for which the length to be determined.
Returns	Returns the computed length.
Throws	ApplicationException
Example	length(“apple”) will return 5

levenshtein

Field	Description
Description	Computes the Levenshtein distance of the two given string columns.
Parameters	arg0: The first string column from which the Levenshtein distance from the second string column to be determined. arg1: The second string column.
Returns	Returns the computed Levenshtein distance.
Throws	ApplicationException
Example	levenshtein(“kitten”, “sitting”) will return 3

like

Field	Description
Description	str like pattern - Returns true if str matches pattern, null if any arguments are null, false otherwise.
Parameters	arg0:A string expression. arg1:The pattern string which is matched.
Returns	Returns true, false or null.
Throws	ApplicationException
Example	SELECT ‘%SystemDrive%UsersJohn’ like ‘%SystemDrive%Users%’; will return true

ln

Field	Description
Description	Computes the natural logarithm of the given value.
Parameters	arg0: The column for which natural logarithm to be calculated.
Returns	Returns the computed natural logarithm.
Throws	ApplicationException
Example	ln(20) will return 2.995732273553991

locate

Field	Description
Description	Locate the position of the first occurrence of given substring in a string column, after the given position.
Parameters	arg0: The String for which the location to be determined. arg1: The string in which the location to be determined. arg2: The position after which the location to be determined.
Returns	Returns the position of the first occurrence of substring.
Throws	ApplicationException
Example	locate(“apple”,“An apple”,1) will return 3

log

Field	Description
Description	Computes the natural logarithm of the given value.
Parameters	arg0: The column for which natural logarithm to be calculated.
Returns	Returns the computed natural logarithm.
Throws	ApplicationException
Example	log(20) will return 2.995732273553991

log10

Field	Description
Description	Computes the logarithm with base 10 of the given value.
Parameters	arg0: The column for which logarithm to be calculated.
Returns	Returns the computed logarithm with base 10.
Throws	ApplicationException
Example	log10(10) will return 1

log1p

Field	Description
Description	Computes the natural logarithm of the given value plus one.
Parameters	arg0: The column for which natural logarithm plus one to be calculated.
Returns	Returns the computed natural logarithm plus one.
Throws	ApplicationException
Example	log1p(20) will return 3.044522437723423

log2

Field	Description
Description	Computes the logarithm with base 2 of the given value.
Parameters	arg0: The column for which logarithm to be calculated.
Returns	Returns the computed logarithm with base 2.
Throws	ApplicationException
Example	log2(2) will return 1

lower

Field	Description
Description	Converts a string column to lower case.
Parameters	arg0: The string column to be converted to lower case.
Returns	Returns the converted string.
Throws	ApplicationException
Example	lower(“APple”) will return “apple”

lpad

Field	Description
Description	Left-pad the string column with the given string, to a given length.
Parameters	arg0: The string column to be left-padded. arg1: The length for the padding. arg2: The string to used for left-pad.
Returns	Returns the Left-padded string.
Throws	ApplicationException
Example	lpad(“SQL Tutorial”, 20, “ABC”) will return “ABCABCABSQL Tutorial”

ltrim

Field	Description
Description	Removes the leading string contains the characters from the trim string.
Parameters	arg0:the trim string characters to trim, the default value is a single space. arg1: a string expression.
Returns	Returns the trimed string.
Throws	ApplicationException
Example	ltrim(‘Sp’,‘SsparkSQLS’)will return ArkSQLS

map

Field	Description
Description	Creates a map with the given key/value pairs.
Parameters	arg0:The columns for key and value.
Returns	returns the map.
Throws	ApplicationException
Example	map(1.0, ‘2’, 3.0, ‘4’) will return {1.0:“2”,3.0:“4”}

map_concat

Field	Description
Description	Returns the union of all the given maps
Parameters	arg0:The map columns.
Returns	Returns the union of all the given maps
Throws	ApplicationException
Example	map_concat(map(1, ‘a’, 2, ‘b’), map(2, ‘c’, 3, ’d’)) will return {1:“a”,2:“c”,3:“d”}

map_from_arrays

Field	Description
Description	Creates a map with a pair of the given key/value arrays. All elements in keys should not be null.
Parameters	arg0:Array of keys. arg1:Array of values.
Returns	Returns the map.
Throws	ApplicationException
Example	map_from_arrays(array(1.0, 3.0), array(‘2’, ‘4’)) will return {1.0:“2”,3.0:“4”}

map_from_entries

Field	Description
Description	Returns a map created from the given array of entries.
Parameters	arg0:Array of entries.
Returns	Returns the map.
Throws	ApplicationException
Example	map_from_entries(array(struct(1, ‘a’), struct(2, ‘b’))) will return {1:“a”,2:“b”}

map_keys

Field	Description
Description	Returns an unordered array containing the keys of the map.
Parameters	arg0:Map column.
Returns	Returns the array.
Throws	ApplicationException
Example	map_keys(map(1, ‘a’, 2, ‘b’)) will return [1,2]

map_values

Field	Description
Description	Returns an unordered array containing the values of the map.
Parameters	arg0:Map column.
Returns	Returns the array.
Throws	ApplicationException
Example	map_values(map(1, ‘a’, 2, ‘b’)) will return [“a”,“b”]

md5

Field	Description
Description	Calculates an MD5 128-bit checksum for the string.
Parameters	arg0:A string column
Returns	The value is returned as a string of 32 hex digits, or NULL if the argument was NULL.
Throws	ApplicationException
Example	md5(@{column.schema.column1}) will give you an output ‘902fbdd2b1df0c4f70b4a5d23525e932’ if value of column1 is ‘ABC’.

minute

Field	Description
Description	Extracts the minutes as an integer from a given date/timestamp/string.
Parameters	arg0: The date/timestamp/string from which the minutes to be extracted.
Returns	Returns the extracted minutes as an integer.
Throws	ApplicationException
Example	minute(“2017-12-15 11:02:03”) will return 2

mod

Field	Description
Description	Calculated the remainder of the given expressions.
Parameters	arg0:The first column expression. arg1:The second column expression.
Returns	Returns the remainder after expr1/expr2.
Throws	ApplicationException
Example	SELECT MOD(2, 1.8); will return 0.2

monotonically_increasing_id

Field	Description
Description	A column expression that generates monotonically increasing 64-bit integers.
Parameters	-
Returns	Monotonically increasing integers.
Throws	ApplicationException
Example	monotonically_increasing_id() will return rows as 0,1,2…

month

Field	Description
Description	Extracts the month as an integer from a given date/timestamp/string.
Parameters	arg0: The date/timestamp/string from which the month to be extracted.
Returns	Returns the extracted month as an integer.
Throws	ApplicationException
Example	month(“2017-12-15 11:02:03”) will return 12

months_between

Field	Description
Description	If timestamp1 is later than timestamp2, then the result is positive. If timestamp1 and timestamp2 are on the same day of month, or both are the last day of month, time of day will be ignored. Otherwise, the difference is calculated based on 31 days per month, and rounded to 8 digits unless roundOff=false.
Parameters	arg0:timestamp1 column. arg1:timestamp2 column. arg2:roundoff boolean.
Returns	Returns the months difference.
Throws	ApplicationException
Example	months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’, false) will return 3.9495967741935485

nanvl

Field	Description
Description	Returns expr1 if it’s not NaN, or expr2 otherwise.
Parameters	arg0:expr1 column. arg1:expr2 column.
Returns	Returns expr1 if it’s not NaN, or expr2 otherwise.
Throws	ApplicationException
Example	nanvl(cast(‘NaN’ as double), 123) will return 123.0

negative

Field	Description
Description	Returns the negated value of expr.
Parameters	arg0:The column or expression.
Returns	The negated value.
Throws	ApplicationException
Example	SELECT negative(1); will return -1

next_day

Field	Description
Description	Given a date column, returns the first date which is later than the value of the date column that is on the specified day of the week.
Parameters	arg0: The date later which the first date for a particular day of week has to determined. arg1: The day of week.
Returns	Returns the computed first date.
Throws	ApplicationException
Example	next_day(“2017-12-15”,“friday”) will return “2017-12-22”

not

Field	Description
Description	Perform logical not of given column.
Parameters	arg0:Given boolean column.
Returns	Returns logical not of given column.
Throws	ApplicationException
Example	not(false) will return true.

now

Field	Description
Description	Returns the current timestamp at the start of query evaluation.
Parameters	arg0:Not applicable.
Returns	The current timestamp
Throws	ApplicationException
Example	SELECT now(); will return 2020-06-26 15:09:37

nullif

Field	Description
Description	Returns null if expr1 equals to expr2, or expr1 otherwise.
Parameters	arg0:The first column expression. arg1:The second column expression.
Returns	Returns the binary.
Throws	ApplicationException
Example	SELECT nullif(2, 2); will return NULL

nvl

Field	Description
Description	Returns expr2 if expr1 is null, or expr1 otherwise.
Parameters	arg0:The first column expression. arg1:The second column expression.
Returns	Returns the binary.
Throws	ApplicationException
Example	SELECT nvl(NULL, array(‘2’)); will return [“2”]

nvl2

Field	Description
Description	Returns expr2 if expr1 is not null, or expr3 otherwise.
Parameters	arg0:The first column expression. arg1:The second column expression. arg1:The third column expression.
Returns	Returns the binary.
Throws	ApplicationException
Example	SELECT nvl2(NULL, 2, 1); will return 1

parse_url

Field	Description
Description	Extracts a part from a URL
Parameters	arg0:The URL. arg1:The part to extract. arg1:The key.
Returns	Returns the binary.
Throws	ApplicationException
Example	SELECT parse_url(‘http://spark.apache.org/path?query=1’, ‘HOST’) will return spark.apache.org

percent_rank

Field	Description
Description	Computes the percentage ranking of a value in a group of values.
Parameters	arg0:Not applicable.
Returns	Returns percentage ranking.
Throws	ApplicationException
Example	select percent_rank() OVER (order by col) will return 1,2,3,4….

pi

Field	Description
Description	Returns pi.
Parameters	arg0:Not applicable.
Returns	Returns pi.
Throws	ApplicationException
Example	SELECT pi(); will return 3.141592653589793

pmod

Field	Description
Description	Computes the positive value of arg0 mod arg1.
Parameters	arg0: The dividend. arg1: The divisor.
Returns	Returns the computed positive value of arg0 mod arg1.
Throws	ApplicationException
Example	pmod(19, 0.78) will return 0.2800007

posexplode

Field	Description
Description	Separates the elements of array expr into multiple rows with positions, or the elements of map expr into multiple rows and columns with positions.
Parameters	arg0: The array expression.
Returns	Returns multiple rows and columns.
Throws	ApplicationException
Example	SELECT posexplode(array(10,20)); will return 0 10 1 20

posexplode_outer

Field	Description
Description	Separates the elements of array expr into multiple rows with positions, or the elements of map expr into multiple rows and columns with positions.
Parameters	arg0: The array expression.
Returns	Returns multiple rows and columns.
Throws	ApplicationException
Example	SELECT posexplode(array(10,20)); will return 0 10 1 20

position

Field	Description
Description	Returns the position of the first occurrence of substr in str after position pos. The given pos and return value are 1-based.
Parameters	arg0:The substr. arg1:The str. arg1:The pos.
Returns	Returns the position.
Throws	ApplicationException
Example	SELECT position(‘bar’, ‘foobarbar’); will return 4

pow

Field	Description
Description	Computes the value of the first argument raised to the power of the second argument.
Parameters	arg0: The base. arg1: The exponent.
Returns	Returns the computed value.
Throws	ApplicationException
Example	pow(20, 2) will return 400

pow_left_arg_double

Field	Description
Description	Computes the value of the first argument raised to the power of the second argument.
Parameters	arg0: The base. arg1: The exponent.
Returns	Returns the computed value.
Throws	ApplicationException
Example	pow_left_arg_double(20, 2) will return 400

pow_right_arg_double

Field	Description
Description	Computes the value of the first argument raised to the power of the second argument.
Parameters	arg0: The base. arg1: The exponent.
Returns	Returns the computed value.
Throws	ApplicationException
Example	pow_right_arg_double(20, 2) will return 400

quarter

Field	Description
Description	Extracts the quarter as an integer from a given date/timestamp/string.
Parameters	arg0: The column for which quarter to be calculated.
Returns	Returns the extracted quarter as an integer.
Throws	ApplicationException
Example	quarter(“2017-12-22 01:12:00”) will return 4

radians

Field	Description
Description	Converts an angle measured in degrees to an approximately equivalent angle measured in radians.
Parameters	arg0: The column for which equivalent angle measured in radians to be calculated.
Returns	Returns the converted angle measured in radians.
Throws	ApplicationException
Example	radians(20) will return 0.3490658503988659

rand

Field	Description
Description	Generate a random column with independent and identically distributed (i.i.d.) samples from 0.0 to 1.0.
Parameters	-
Returns	Returns the generated column.
Throws	ApplicationException
Example	rand() will return a new column with independent and identically distributed (i.i.d.) samples from 0.0 to 1.0.

randn

Field	Description
Description	Generate a column with independent and identically distributed (i.i.d.) samples from the standard normal distribution.
Parameters	-
Returns	Returns the generated column.
Throws	ApplicationException
Example	randn() will return a new column with independent and identically distributed (i.i.d.) samples from the standard normal distribution.

rank

Field	Description
Description	Computes the rank of a value in a group of values. The result is one plus the number of rows preceding or equal to the current row in the ordering of the partition. The values will produce gaps in the sequence.
Parameters	arg0:Not applicable.
Returns	The calculated rank.
Throws	ApplicationException
Example	select rank() OVER (order by col) will return 1,2,3,4…

regexp_extract

Field	Description
Description	Extract a specific group matched by a Java regex, from the specified string column.
Parameters	arg0: The string column from which the group to be extracted. arg1: The string specifying the regex. arg2: The regex group id.
Returns	Returns the extracted group.
Throws	ApplicationException
Example	regexp_extract(“foothebar”,“foo(.*?)(bar)”, 2) will return “bar”

regexp_replace

Field	Description
Description	Replace all substrings of the specified string value that match regexp with the given replacement.
Parameters	arg0: The string column from which substrings to be replaced. arg1: The pattern to be used. arg2: The replacement.
Returns	Returns the string after replacement of substrings that match regexp with rep.
Throws	ApplicationException
Example	regexp_replace(“foobar”, “oo

repeat

Field	Description
Description	Repeats each value in select column n times.
Parameters	arg0:A column needs to be repeated arg1:Integer value representing no of times arg0 is to be repeated
Returns	A repeated value.
Throws	ApplicationException
Example	We have taken column1 as ‘str’ repeat(@{column.schema.column1},2) will return ‘strstr’

replace

Field	Description
Description	Replaces all occurrences of search with replace.
Parameters	arg0:str - A string expression. arg1:search - a string expression. If search is not found in str, str is returned unchanged. arg1:replace - a string expression. If replace is not specified or is an empty string, nothing replaces the string that is removed from str.
Returns	Returns the replaced string.
Throws	ApplicationException
Example	SELECT replace(‘ABCabc’, ‘abc’, ‘DEF’); will return ABCDEF

reverse

Field	Description
Description	Reverses the string column and returns it as a new string column.
Parameters	arg0: The string column to be reversed.
Returns	Returns the reversed string column.
Throws	ApplicationException
Example	reverse(“apple”) will return “elppa”

rint

Field	Description
Description	Computes the double value that is closest in value to the argument and is equal to a mathematical integer.
Parameters	arg0: The column for which double value to be calculated.
Returns	Returns the computed value.
Throws	ApplicationException
Example	rint(80.89) will return 81.0

round

Field	Description
Description	Computes the value of the column arg0 rounded to 0 decimal places.
Parameters	arg0: The column for which value rounded to 0 decimal places to be calculated.
Returns	Returns the computed value.
Throws	ApplicationException
Example	round(80.89) will return 81.0

row_number

Field	Description
Description	Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition.
Parameters	arg0:Not applicable.
Returns	The row number.
Throws	ApplicationException
Example	select row_number() OVER (order by col) will return 1,2,3,4….

rpad

Field	Description
Description	Right-pad the string column with the given string, to a given length.
Parameters	arg0: The string column to be right-padded. arg1: The length for the padding. arg2: The string to used for right-pad.
Returns	Returns the Right-padded string.
Throws	ApplicationException
Example	rpad(“SQL Tutorial”, 20, “ABC”) will return “SQL TutorialABCABCAB”

rtrim

Field	Description
Description	Trim the spaces from right end for the specified string value.
Parameters	arg0: The string column from which right spaces to be trimmed.
Returns	Returns the trimmed string value.
Throws	ApplicationException
Example	rtrim(“apple “) will return “apple”

schema_of_json

Field	Description
Description	Returns schema in the DDL format of JSON string.
Parameters	arg0:Given json string column
Returns	Returns schema of the json.
Throws	ApplicationException
Example	schema_of_json(’[{“col”:0}]’) will return array>

second

Field	Description
Description	Extracts the seconds as an integer from a given date/timestamp/string.
Parameters	arg0: The date/timestamp/string from which the seconds to be extracted.
Returns	Returns the seconds as an integer.
Throws	ApplicationException
Example	second(“2017-12-15 11:02:03”) will return 3

sequence

Field	Description
Description	Generates an array of elements from start to stop (inclusive), incrementing by step. The type of the returned elements is the same as the type of argument expressions.Supported types are: byte, short, integer, long, date, timestamp.The start and stop expressions must resolve to the same type. If start and stop expressions resolve to the ‘date’ or ’timestamp’ type then the step expression must resolve to the ‘interval’ type, otherwise to the same type as the start and stop expressions.
Parameters	arg0:start - an expression. The start of the range. arg1:stop - an expression. The end the range (inclusive). arg2:step - an optional expression. The step of the range. By default step is 1 if start is less than or equal to stop, otherwise -1. For the temporal sequences it’s 1 day and -1 day respectively. If start is greater than stop then the step must be negative, and vice versa.
Returns	Returns the sequence
Throws	ApplicationException
Example	sequence(to_date(‘2018-01-01’), to_date(‘2018-03-01’), interval 1 month) will return [2018-01-01,2018-02-01,2018-03-01]

sha1

Field	Description
Description	Calculates the SHA-1 digest for string and returns the value as a hex string
Parameters	arg0:A string column
Returns	A hex string.
Throws	ApplicationException
Example	We have taken column1 as ‘ABC’ sha1(@{column.schema.column1}) will return ‘3c01bdbb26f358bab27f267924aa2c9a03fcfdb8’

sha2

Field	Description
Description	Calculates the SHA-2 family of hash functions of a string value and returns the value as a hex string. NumBits controls the number of bits in the message digest.
Parameters	arg0:A string column arg1:one of 224, 256, 384, or 512.
Returns	A hex string
Throws	ApplicationException
Example	We have taken column1 as ‘Sam’ sha2(@{column.schema.column1},256) will return ‘4ecde249d747d51d8..’

shiftLeft

Field	Description
Description	Bitwise left shift, Shifts a b positions to the left.
Parameters	arg0:A number column arg1:An integer column
Returns	If the given value is a long value, this function will return a long value else it will return an integer value
Throws	ApplicationException
Example	We have taken column1 as ‘258’ shiftLeft(@{column.schema.column1},2) will return 1032

shiftRight

Field	Description
Description	Bitwise right shift, Shifts a b positions to the right.
Parameters	arg0:A number column arg1:An integer column
Returns	If the given value is a long value, this function will return a long value else it will return an integer value
Throws	ApplicationException
Example	We have taken column1 as ‘258’ shiftRight(@{column.schema.column1},2) will return 64

shiftRightUnsigned

Field	Description
Description	Bitwise unsigned right shift, Shifts a b positions to the right.
Parameters	arg0:A number column arg1:An integer column
Returns	If the given value is a long value, this function will return a long value else it will return an integer value
Throws	ApplicationException
Example	We have taken column1 as ‘258’ shiftRightUnsigned(@{column.schema.column1},2) will return 64

shuffle

Field	Description
Description	Returns a random permutation of the given array.
Parameters	arg0: array column.
Returns	Returns a random permutation of the given array.
Throws	ApplicationException
Example	shuffle(array(1, 20, null, 3))will return [20,null,3,1]

sign

Field	Description
Description	Returns -1.0, 0.0 or 1.0 as expr is negative, 0 or positive.
Parameters	arg0:Not applicable.
Returns	Returns 1.0, 0.0 or 1.0.
Throws	ApplicationException
Example	SELECT signum(40); will return 1.0

signum

Field	Description
Description	Computes the signum of the given value.
Parameters	arg0: The column for which signum to be calculated.
Returns	Returns the computed signum.
Throws	ApplicationException
Example	signum(20) will return 1.0

sin

Field	Description
Description	Computes the sine of the given value.
Parameters	arg0: The column for which sine to be calculated.
Returns	Returns the computed sine.
Throws	ApplicationException
Example	sin(20) will return 0.9129452507276277

sinh

Field	Description
Description	Computes the hyperbolic sine of the given value.
Parameters	arg0: The column for which hyperbolic sine to be calculated.
Returns	Returns the computed hyperbolic sine.
Throws	ApplicationException
Example	sinh(20) will return 2.4258259770489514E8

size

Field	Description
Description	Returns the size of an array or a map. The function returns -1 if its input is null and spark.sql.legacy.sizeOfNull is set to true. If spark.sql.legacy.sizeOfNull is set to false, the function returns null for null input. By default, the spark.sql.legacy.sizeOfNull parameter is set to true.
Parameters	arg0: array or map column.
Returns	Returns the size
Throws	ApplicationException
Example	size(array(‘b’, ’d’, ‘c’, ‘a’)) will return 4

slice

Field	Description
Description	Subsets array x starting from index start (or starting from the end if start is negative) with the specified length.
Parameters	arg0:array column. arg1:start index. arg2:end index
Returns	Returns an array.
Throws	ApplicationException
Example	slice(array(1, 2, 3, 4), 2, 2) will return [2,3]

smallint

Field	Description
Description	Casts the value expr to the target data type smallint.
Parameters	arg0:The column or expression.
Returns	Returns the smallint column or value.
Throws	ApplicationException
Example	SELECT smallint(column); will cast column to smallint

sort_array

Field	Description
Description	Sorts the input array in ascending or descending order according to the natural ordering of the array elements. Null elements will be placed at the beginning of the returned array in ascending order or at the end of the returned array in descending order.
Parameters	arg0:array column. arg1:boolean flag for order.
Returns	Returns the sorted array.
Throws	ApplicationException
Example	sort_array(array(‘b’, ’d’, null, ‘c’, ‘a’), true) will return [null,“a”,“b”,“c”,“d”]

soundex

Field	Description
Description	Returns Soundex code of the string.
Parameters	arg0:String column.
Returns	Returns Soundex code of the string.
Throws	ApplicationException
Example	soundex(‘Miller’) will return M460

spark_partition_id

Field	Description
Description	Returns the current partition id.
Parameters	arg0:Not applicable.
Returns	The partition id.
Throws	ApplicationException
Example	select spark_partition_id(); will return 1

split

Field	Description
Description	Splits str around occurrences that match regex
Parameters	arg0: str string column. arg1:the regex string.
Returns	Returns the splits.
Throws	ApplicationException
Example	split(‘oneAtwoBthreeC’, ‘[ABC]’)will return [“one”,“two”,“three”,””]

sqrt

Field	Description
Description	Computes the square root of the specified float value.
Parameters	arg0: The column for which square root to be calculated.
Returns	Returns the computed square root.
Throws	ApplicationException
Example	sqlSqrt(20) will return 4.47213595499958

string

Field	Description
Description	Casts the value expr to the target data type string.
Parameters	arg0:The column or expression.
Returns	Returns the string column or value.
Throws	ApplicationException
Example	SELECT string(column); will cast column to string

struct

Field	Description
Description	Creates a struct with the given field values.
Parameters	arg0:columns using which the struct will be created.
Returns	returns the struct column
Throws	ApplicationException
Example	-

substr

Field	Description
Description	Substring starts at given position and is of given length when given argument is String type or returns the slice of byte array that starts at given position in byte and is of given length when given argument is Binary type
Parameters	arg0: The String column from which substring to be extracted. arg1: The start position for the substring. arg2: The end position for the substring.
Returns	Returns the result substring.
Throws	ApplicationException
Example	substring(“foo bar”,4,6) will return “bar”

substring

Field	Description
Description	Substring starts at given position and is of given length when given argument is String type or returns the slice of byte array that starts at given position in byte and is of given length when given argument is Binary type
Parameters	arg0: The String column from which substring to be extracted. arg1: The start position for the substring. arg2: The end position for the substring.
Returns	Returns the result substring.
Throws	ApplicationException
Example	substring(“foo bar”,4,6) will return “bar”

substring_index

Field	Description
Description	Computes the substring from given string before given count occurrences of the given delimiter.
Parameters	arg0: The String column from which substring to be extracted. arg1: The delimiter. arg2: The count occurrences for the delimiter.
Returns	Returns the result substring.
Throws	ApplicationException
Example	substring_index(“www.xyz.com”,”.",2) will return “www.xyz”

tan

Field	Description
Description	Computes the tangent of the given value.
Parameters	arg0: The column for which tangent to be calculated.
Returns	Returns the computed tangent.
Throws	ApplicationException
Example	tan(20) will return 2.237160944224742

tanh

Field	Description
Description	Computes the hyperbolic tangent of the given value.
Parameters	arg0: The column for which hyperbolic tangent to be calculated.
Returns	Returns the computed hyperbolic tangent.
Throws	ApplicationException
Example	tanh(20) will return 1.0

timestamp

Field	Description
Description	Casts the value expr to the timestamp type.
Parameters	arg0: The string column or expression.
Returns	Returns the date column or value.
Throws	ApplicationException
Example	SELECT date(‘2020-06-10 02:12:45’); will return 2020-06-10 02:12:45 as timestamp type.

tinyint

Field	Description
Description	Casts the value expr to the target data type tinyint.
Parameters	arg0:The column or expression.
Returns	Returns the tinyint column or value.
Throws	ApplicationException
Example	SELECT tinyint(column); will cast column to tinyint

toDegrees

Field	Description
Description	Returns the angle measured in radians to an approximately equivalent angle measured in degrees.
Parameters	arg0:A column for which degree to be calculated
Returns	A double value.
Throws	ApplicationException
Example	We have taken column1 as 3.14159, toDegrees(@{column.schema.column1}) will return 180

toRadians

Field	Description
Description	Returns the angle measured in degrees to an approximately equivalent angle measured in radians.
Parameters	arg0:A column for which radians to be calculated
Returns	A double value.
Throws	ApplicationException
Example	We have taken column1 as 180, toRadians(@{column.schema.column1}) will return 3.14159

to_date

Field	Description
Description	Parses the date_str expression with the fmt expression to a date. Returns null with invalid input. By default, it follows casting rules to a date if the fmt is omitted.
Parameters	arg0: str date_str column column. arg1:the format string.
Returns	returns the formatted date.
Throws	ApplicationException
Example	to_date(‘2016-12-31’, ‘yyyy-MM-dd’) will return 2016-12-31

to_json

Field	Description
Description	Returns a JSON string with a given struct value
Parameters	arg0:struct column. arg1:additional options map.
Returns	Returns a JSON string with a given struct value
Throws	ApplicationException
Example	to_json(named_struct(’time’, to_timestamp(‘2015-08-26’, ‘yyyy-MM-dd’)), map(’timestampFormat’, ‘dd/MM/yyyy’)) will return {“time”:“26/08/2015”}

to_timestamp

Field	Description
Description	Parses the timestamp expression with the fmt expression to a timestamp. Returns null with invalid input. By default, it follows casting rules to a timestamp if the fmt is omitted.
Parameters	arg0:The timestamp column. arg1:The format string.
Returns	Returns the formated timestamp.
Throws	ApplicationException
Example	to_timestamp(‘2016-12-31’, ‘yyyy-MM-dd’) will return 2016-12-31 00:00:00

to_utc_timestamp

Field	Description
Description	Given a timestamp like ‘2017-07-14 02:40:00.0’, interprets it as a time in the given time zone, and renders that time as a timestamp in UTC
Parameters	arg0:The timestamp column. arg1:The timezone column.
Returns	Returns the timestamp.
Throws	ApplicationException
Example	to_utc_timestamp(‘2016-08-31’, ‘Asia/Seoul’) will return 2016-08-30 15:00:00

transform

Field	Description
Description	Transforms elements in an array using the function.
Parameters	arg0:The array expression. arg1:The function to apply on each element of the array expression.
Returns	Returns the transformed array.
Throws	ApplicationException
Example	SELECT transform(array(1, 2, 3), x -> x + 1); will return [2,3,4]

translate

Field	Description
Description	Translate any character in the given string by a given character in given replaceString.
Parameters	arg0: The string column in which the translation to be done. arg1: The matching character. arg2: The replacement charater.
Returns	Returns the translated string.
Throws	ApplicationException
Example	translate(“The foo bar”,“f”,“t”) will return “The too bar”

trim

Field	Description
Description	Remove the leading and trailing trimStr characters from str
Parameters	arg0:The trimStr String column. arg1:The str string.
Returns	Returns the trimed string
Throws	ApplicationException
Example	trim(‘SL’, ‘SsparkSQLS’) will return parkSQ

trunc

Field	Description
Description	Computes the date truncated to the unit specified by the format.
Parameters	arg0: The date to be truncated. arg1: The format for truncation.
Returns	Returns truncated date.
Throws	ApplicationException
Example	trunc(“2017-12-15”,“YEAR”) will return “2017-01-01”

ucase

Field	Description
Description	Converts str with all characters changed to uppercase.
Parameters	arg0:The column or expression.
Returns	Returns str with all characters changed to uppercase.
Throws	ApplicationException
Example	SELECT lcase(‘SparkSql’); will return SPARKSQL

unbase64

Field	Description
Description	Converts the argument from a base 64 string str to a binary.
Parameters	arg0:The base 64 String column.
Returns	Returns the unbase64 of string.
Throws	ApplicationException
Example	unbase64(‘U3BhcmsgU1FM’) will return Spark SQL

unhex

Field	Description
Description	Converts hexadecimal expr to binary.
Parameters	arg0:The hexadecimal column.
Returns	Returns the binary.
Throws	ApplicationException
Example	decode(unhex(‘537061726B2053514C’), ‘UTF-8’) will return Spark SQL

unix_timestamp

Field	Description
Description	Convert time string with given pattern (refer to the topic, Customizing Formats) to Unix time stamp (in seconds), return null if fail.
Parameters	arg0: The time string to be converted. arg1: The format of the time string.
Returns	Returns the converted Unix time stamp (in seconds), return null if fail.
Throws	ApplicationException
Example	unix_timestamp(“2017-12-15 11:56”,“yyyy-MM-dd hh:mm”) will return 1513339008

upper

Field	Description
Description	Converts a string column to upper case.
Parameters	arg0: The string column to be converted o upper case.
Returns	Returns the converted string column.
Throws	ApplicationException
Example	upper(“aPPle”) will return “APPLE”

uuid

Field	Description
Description	Returns an universally unique identifier (UUID) string. The value is returned as a canonical UUID 36-character string
Parameters	arg0:Not applicable.
Returns	Returns the uuid.
Throws	ApplicationException
Example	SELECT uuid(); will return 12ee-419a-ac70-88c948edd439

weekday

Field	Description
Description	Extracts and returns the day of week of the given date/timestamp.
Parameters	arg0: The column or expression.
Returns	Returns the day of week of the date/timestamp.
Throws	ApplicationException
Example	SELECT day(‘2009-07-30’); will return 3.

weekofyear

Field	Description
Description	Extracts the week number as an integer from a given date/timestamp/string.
Parameters	arg0: The date/timestamp/string from which the week to be extracted.
Returns	Returns the converted Unix time stamp (in seconds), return null if fail.
Throws	ApplicationException
Example	weekofyear(“2017-12-15 11:02:03”) will return 50

xpath

Field	Description
Description	Extracts and returns a string array of values within the nodes of xml that match the XPath expression.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns the array of strings.
Throws	ApplicationException
Example	SELECT xpath(’b1b2b3c1c2’,‘a/b/text()’); will return [‘b1’,‘b2’,‘b3’]

xpath_boolean

Field	Description
Description	Returns true if the XPath expression evaluates to true, or if a matching node is found.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns true or false
Throws	ApplicationException
Example	SELECT xpath_boolean(’1’,‘a/b’); will return true

xpath_double

Field	Description
Description	Evaluates given xpath expression and returns double value.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns a double value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.
Throws	ApplicationException
Example	SELECT xpath_double(’12’, ‘sum(a/b)’); will return 3.0

xpath_float

Field	Description
Description	Evaluates given xpath expression and returns float value.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns a float value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.
Throws	ApplicationException
Example	SELECT xpath_float(’12’, ‘sum(a/b)’); will return 3.0

xpath_int

Field	Description
Description	Evaluates given xpath expression and returns integer value.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns a integer value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.
Throws	ApplicationException
Example	SELECT xpath_int(’12’, ‘sum(a/b)’); will return 3

xpath_long

Field	Description
Description	Evaluates given xpath expression and returns long value.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns a long value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.
Throws	ApplicationException
Example	SELECT xpath_long(’12’, ‘sum(a/b)’); will return 3

xpath_number

Field	Description
Description	Evaluates given xpath expression and returns double value.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns a double value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.
Throws	ApplicationException
Example	SELECT xpath_number(’12’, ‘sum(a/b)’); will return 3.0

xpath_short

Field	Description
Description	Evaluates given xpath expression and returns short integer value.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns a short integer value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.
Throws	ApplicationException
Example	SELECT xpath_short(’12’, ‘sum(a/b)’); will return 3

xpath_string

Field	Description
Description	Extracts and returns the text contents of the first xml node that matches the XPath expression.
Parameters	arg0:The string xml. arg1:The xpath expression.
Returns	Returns the string content.
Throws	ApplicationException
Example	SELECT xpath_string(’bcc’,‘a/c’); will return cc

year

Field	Description
Description	Extracts the year as an integer from a given date/timestamp/string.
Parameters	arg0: The date/timestamp/string from which the year to be extracted.
Returns	Returns the extracted year as an integer.
Throws	ApplicationException
Example	year(“2017-12-15 11:02:03”) will return 2017

zip_with

Field	Description
Description	Merges the two given arrays, element-wise, into a single array using function. If one array is shorter, nulls are appended at the end to match the length of the longer array, before applying function.
Parameters	arg0:The first array. arg1:The second array. arg1:The function to apply while merging.
Returns	Returns the merged array.
Throws	ApplicationException
Example	SELECT zip_with(array(1, 2, 3), array(‘a’, ‘b’, ‘c’), (x, y) -> (y, x)); will return [{“y”:“a”,“x”:1},{“y”:“b”,“x”:2},{“y”:“c”,“x”:3}]

zulu Time Format

Field	Description
Description	Returns the UTC date of input date column or value in given output date format.
Parameters	Date: String date value or date column. inputDateFormat: Date format of the input date. outputDateFormat: Date format of the outdate date.
Returns	UTC date in output date format in string.
Throws	ApplicationException.
Example	zuluTimeFormat(columnName1,“yyyy-MM-dd HH:mm:ss”,“yyyy/MM/dd HHmmss”) will return UTC date in format “yyyy/MM/dd HHmmss”. Note: If user is using column name then the column should be of string type.

If you have any feedback on Gathr documentation, please email us!

Expression Evaluator Processor

Expression Evaluator Configuration #

Enlisted Functions #

ABS #

ACOS #

ADD_MONTHS #

ARRAY #

ARRAY_CONTAINS #

ARRAY_DISTINCT #

ARRAY_EXCEPT #

ARRAY_INTERSECT #

ARRAY_JOIN #

ARRAY_MAX #

ARRAY_MIN #

ARRAY_POSITION #

array_remove #

array_repeat #

array_sort #

array_union #

arrays_overlap #

arrays_zip #

ascii #

asin #

atan #

atan2 #

base64 #

bigint #

bin #

boolean #

bround #

cast #

cbrt #

ceil #

ceiling #

char #

char_length #

chr #

coalesce #

concat #

concat_ws #

conv #

cos #

cosh #

crc32 #

current_date #

current_timestamp #

date_add #

date_format #

date_sub #

date_trunc #

datediff #

day #

dayofmonth #

dayofweek #

dayofyear #

decimal #

decode #

degrees #

dense_rank #

element_at #

encode #

exp #

explode #

explode_outer #

expm1 #

expr #

factorial #

flatten #

float #

floor #

format_number #

format_string #

from_json #

from_unixtime #

from_utc_timestamp #

get_json_object #

greatest #

hash #

hex #

hour #