Functions

StreamAnalytix provides different kind of functions like Date, Lookup, String, Math, Array and Miscellaneous functions. Using these functions, you can directly apply computations on the dataset columns and get desired result.

The functions takes columns and/or literals as arguments.

In order to pass column “A” of a dataset “D” as a function argument, pass for example

@{column.D.A}

There are some exceptions where you need to pass the column name directly.

Following are the functions that take the column name directly.

expr(A)

rename_column(A)

In order to pass a “string literal” as argument to function pass it in double quotes for example

"IST"

In order to pass a “number literal” to a function, pass it as it is.

For example, 2 or 1.345

If we have a dataset named schemaName having a string/date/timestamp column date holding the value 2009-03-01 for a particular row, then

add_months@{column.schemaName.date},2) will return 2009-05-01

Note:

All the examples presented in this guide have column values instead of the entire expression i.e. @{column.schemaName.columnName}

add_months@{column.schemaName.date},2) will return 2009-05-01

add_months is a function, @{column.schemaName.date} is an expression and 2009-03-01 is the value of the expression.

add_months(@{column.schemaName.date},2) will be shown as:

add_months(2009-03-01,2)

A detailed description of these functions is given below:

Date Functions

Enables to perform calculations involving dates.

add_months

Calculates the new date by adding the given number of months. 

Input Parameters: Column arg0, int arg1.

• arg0 (required) – The string/date/timestamp column to which months are to be added.

• arg1 (required)– Number of months to be added.

Use Case:

 

Example

add_months("2009-03-01",2) will return "2009-05-01"

current_date

Computes the current date as a date column.

Input Parameters: There are no function arguments.

Use Case:

 

Example

current_date() will return the current date.

If used on 2018-06-15 will return 2018-06-15

current_timestamp

Computes the current timestamp as a timestamp column.

Input Parameters: There are no function arguments.

Use Case:

 

Example

current timestamp() will return the current timestamp.

date_add

Adds the specified number of months in ‘arg1’ to a date time value in ‘arg0’.

Input Parameters: Column arg0, int arg1

• arg0 (required) – The string/date/timestamp column to which days to be added.

• arg1 (required)– Number of days to be added..

Use Case:

 

Example

date_add("2009-03-01",2) will return "2009-03-03".

date_format

Converts a date/timestamp/string column 'arg0' to a value of string in the format specified by the date format given by the  'arg1'.

Input Parameters: Column arg0, String arg1

• arg0 (required) – The date/timestamp/string column to be converted.

• arg1 (required)– The format to which the date/timestamp/string to be converted..

Use Case:

 

Example

date_format("2009-03-01","MM-dd-yyyy") will return "03-01-2009".

date_sub

Computes the date that is 'arg1' days before 'arg0'.

Input Parameters: Column arg0, int arg1

• arg0 (required) – The string/date/timestamp column from which days to be sub­tracted.

• arg1 (required)– Number of days to be subtracted.

Use Case:

 

Example

date_format("2009-03-01","MM-dd-yyyy") will return "03-01-2009".

datediff

Calculates the difference between the dates 'arg1' and 'arg0' date and returns the number of days. 

Input Parameters: Column arg0, Column arg1

• arg0 (required) – The end string/date/timestamp column.

• arg1 (required)– The start string/date/timestamp column.

Use Case:

 

Example

datediff("2009-03-01","2009-02-27") will return 2.

dayofyear

Extracts the day of the year as an integer from a given date/timestamp/string.

Input Parameters: Column arg0

• arg0 (required) – The date/timestamp/string from which the day of year to be extracted.

Use Case:

 

Example

dayofyear("2017-12-15") will return 349

unix_timestamp

Convert time string with given pattern https://docs.oracle.com/javase/tutorial/i18n/for­mat/simpleDateFormat.html to Unix time stamp (in seconds), returns null if fails.

Input Parameters: Column arg0,Object arg1

• arg0 (required) – The string/date/timestamp column to be converted.

• arg1 (required)– The string/date/timestamp format.

Use Case:

 

Example

unix_timestamp("2017-12-15 11:56","yyyy-MM-dd hh:mm") will return 1513339008.

default_unix_timestamp

Gets current Unix timestamp in seconds.

Input Parameters: There are no function arguments.

Use Case:

 

Example

default_unix_timestamp() will return current time in long format.

from_utc_timestamp

Given a timestamp, which corresponds to a certain time of day in UTC, returns another timestamp that corresponds to the same time of day in the given timezone.

Input Parameters: Column arg0, Object arg1

• arg0 (required) – The string/date/timestamp column in UTC.

• arg1 (required) – The timezone to which the timestamp has to converted..

Use Case:

 

Example

from_utc_timestamp("2017-12-15 11:40",IST) will return "2017-12-15 17:10"

hour

Extracts the hours as an integer from a given date/timestamp/string.

Input Parameters: Column arg0

• arg0 (required) – The date/timestamp/string column from which the hours to be extracted..

Use Case:

 

Example

date_format("2009-03-01","MM-dd-yyyy") will return "03-01-2009".

last_day

The date/timestamp/string column, returns the last day of the month which the given date belongs to.

Input Parameters: Column arg0

• arg0 (required) – The date/timestamp/string column from which last day of month to be extracted...

Use Case:

 

Example

last_day("2017-12-15") will return "2017-12-31".

Lookup Functions

These functions help to look up data beyond the application flow.

lookupHBase

It is used to fetch the records from Hbase table based on the following input param­eters.

Input parameters: String tableName,String rowID, String columnFamily, int resul­tRow, String resultColumnName.

• tableName (required): Hbase Table name with name space as string type.

• rowID (required): String representation of unique key for record.

• columnFamily (required): Column family name is in string format.

• resultRow (required): Result row number.

• resultColumnName (required): Result column name.

Configuration Parameters: Below are the configuration parameters of lookupHBase function.

• ConnectionName (required): Hbase cluster connection name.

• RowidCaching (required): Caching will be applied on the basis of row id..

.

Use Case:

 

 

Example:

lookupHBase("tableName","rowID","columnFamily",1,"age") will return value of age from row 1 of result for given rowId and columnFamily.

lookupRDBMS

It is used to fetch the record from RDBMS table. The application supports database lookup for Postgres, MySQL and Oracle.

Input Parameters: String sqlQuery, int resultRow, String resultColumnName

• sqlQuery(required) – string type , select sql query specific to database.

• resultRow (required): Result row number.

• resultColumnName (required): Result column name.

Configuration Parameters:

• ConnectionName(required)– Complete jdbc url specific to database.

• FunctionReturnType (required)– Return type of the function based on the result column being queried.

Use Case:

 

 

Example:

lookupRDBMS("select * from tableName",1,"age") will return value of age for row 1 of query result.

lookupES

Returns  the result with respect to query from ElasticSearch index, outputs the given column for given row.

Input Parameters: String stringQuery, int resultRow, String resultColumnName

• stringQuery(required) – string Elastic search query.

• resultRow (required): Result row number

• resultColumnName (required): Result column name.

Configuration Parameters:

• ConnectionName(required)– Elastic search connection name.

• indexName(required)- Elastic search index name.

• indexType(required)- Elastic search index type.

• functionReturnType (required)– Return type of the function based on the result column being queried..

Use Case: 

 

 

Example:

  lookupES("{\'query\':{\'match_all\':{}}}",1,"age") will return row 1 and column named age from search result of ES index.

lookupWS

This method is used to get the response of web service. StreamAnalytix supports REST based web service.

Input Parameters: There is no input parameter.

Configuration Parameters:

• endpoint(required) : the end point of url .

• methodType(required): web service method like GET, POST, etc.

• Content-type(required): the content type like application/JSON,etc.

• wsparams(required): the web service parameters.

Use Case:

 

 

lookupSOLR

Returns the result, with respect to query, from Solr Index.

Input Parameters: String queryString, int resultRow, String resultColumnName

• queryString (required): Solr search query.

• resultRow (required): Result row number.

• resultColumnName (required): Result column name.

Configuration Parameters:

• connectionName(required)- Solr connection name.

• indexName(required)- Solr index name.

Use Case:

 

 

Example:

lookupSOLR(''*:*'',1,”age”) will return you search result from Solr index for row 1 and column age of query result.

lookupHDFSPATH

Returns the boolean result, with respect to path exits or not, from HDFS.

Input Parameters: 

• hdfsPath (required) – Path on HDFS.

Configuration Parameters: 

• ConnectionName(required)– HDFS Connection name.

• FunctionReturnType (required)– Return type of the function based on the result column being queried.

Use Case:

 

 

Example:

lookupHDFSPATH(''hdfsPath'') will return whether the path exists or not on HDFS.

lookupHBase64

Returns the Base64 encoded result, with respect to row and column-family passed as argument to the function, from HBase table, outputs the given column for given row.

Input parameters: String tableName,String rowID, String columnFamily, int resul­tRow, String resultColumnName

•    tableName (required): Hbase Table name with name space as string type.

• rowID (required): String representation of unique key for record.

• columnFamily (required): Column family name is in string format.

• resultRow (required): Result row number.

• resultColumnName (required): Result column name.

Configuration Parameters: Below are the configuration parameters of lookupHBase function.

• ConnectionName(required)– Hbase Connection name.

• RowIdCaching (required)– Caching will be applied on the basis of row id..

Use Case:

 

 

Example:

lookupHBase64("tableName","rowID","columnFamily",1,"age") will return value of age from row 1  of result for given rowId and columnFamily.

String Functions

Ascii

Computes the numeric value of the first character of the string column, and returns the result as an int column.

Input Parameters: Column arg0

• arg0(required) - The string column for which the first character's numeric value to be calculated.

Use Case:

 

Example:

ascii("An apple") will return 65

Concat

Concatenates multiple input string columns together into a single string column.

Input Parameters: Column... arg0

arg0 (required) - The String columns to be concatenated.

Use Case:

 

Example:

concat("format","string") will return "formatstring"

Concat_ws

Concatenates multiple input string columns together into a single string column, using the given separator.

Input Parameters: Object arg0, Column... arg1

• arg0(required) - The separator to be used.    

• arg1 (required) - The String columns to be concatenated.

 

Example:

concat_ws("-","format","string") will return "format-string".

Expr

Parses the expression string into the column that it represents.

Input Parameters: Object arg0

• arg0 (required) - The expression string to be parsed.

Use Case:

 

Example:

expr("colA * 3") will return 6 if the value of column 'colA' is 2 for particular row.

Format_string

Formats the arguments in printf-style and returns the result as a string column.

Input Parameters: Object arg0, Column arg1

• arg0 (required) - The printf style format.

• arg1 (required)  - The columns to be formatted.

 

Example:

format_string("the %s jumped over the %s, %d times","cow","moon",2) will return "the cow jumped over the moon 2 times".

Initcap

Computes a new string column by converting the first letter of each word to upper­case.

Input Parameters: Column arg0

• arg0 (required) - The input string column.

Use Case:

 

Example:

initcap("apple") will return "Apple")

Instr

Locate the position of the first occurrence of given substring in the given string col­umn.

Input Parameters: Column arg0, Object arg1

• arg0 (required) - The string column in which the location to be determined.

• arg1 (required) - The substring for which the position to be determined.

 

Example:

 instr("apple","le") will return 4

Length

Computes the length of a given string or binary column.

Input Parameters: Column arg0

arg0 (required) - The string column for which the length to be determined.

Use Case:

 

Example:

length("apple") will return 5

Levenshtein

Computes the Levenshtein distance of the two given string columns.

Input Parameters: Column arg0, Column arg1

• arg0 (required) - The first string column from which the Levenshtein distance from the second string column to be determined.

• arg1 (required) - The second string column.

Use Case:

 

Example:

levenshtein("kitten", "sitting") will return 3

Locate

 Locate the position of the first occurrence of given substring in a string column, after the given position.

Input Parameters: Object arg0, Column arg1, int arg2

• arg0 (required) - The String  for which the location to be determined.

• arg1 (required) - The String column in which the location to be determined.

• arg2 (required) - The position after which the location to be determined.

Use Case:

 

Example:

locate("apple","An apple",1) will return 3

Lower

Converts a string column to lower case.

Input Parameters: Column arg0

• arg0 (required) - The string column to be converted to lower case.

Use Case:

 

Example :

lower("APple") will return "apple"

Lpad

Left-pad the string column with the given string, to a given length.

Input Parameters: Column arg0, int arg1, Object arg2

• arg0 (required) - The string column to be left-padded.

• arg1 (required) - The length for the padding.

• arg2 (required) - The string to use for left-pad.

Use Case:

 

Example:

lpad("SQL Tutorial", 20, "ABC") will return "ABCABCABSQL Tutorial"

Ltrim

Trim the spaces from left end for the specified string column.

Input Parameters: Column arg0

• arg0 (required) - The string column from which left spaces to be trimmed.

Use Case:

 

Example:

ltrim(" apple") will return "apple"

Regexp_extract

Extracts a specific group matched by a Java regex, from the specified string column.

Arguments: Column arg0, Object arg1, int arg2

• arg0 (required) - The string column from which the group to be extracted.

• arg1 (required) - The string specifying the regex.

• arg2 (required) - The regex group id.

Use Case:

 

Example:

regexp_extract("foothebar","foo(.*?)(bar)", 2) will return "bar"

Regexp_replace

Replace all substrings of the specified string column that match regexp with the given replacement.

Input Parameters: Column arg0, Object arg1, Object arg2

arg0 (required) - The string column from which substrings to be replaced.

arg1 (required) - The pattern to be used.

arg2 (required) - The replacement.

Use Case:

 

Example:

regexp_replace("foobar", "oo|ar", "") will return "fb"

Rename_column

Rename the given column.

Input Parameters: Object arg0

arg0 (required) - The column name to be renamed.

Use Case:

 

Example:

rename_column("age") will rename "age" to given output column name.

Repeat

Repeats each value in 'arg0' column 'arg1' times.

Input Parameters: Column arg0,int arg1

arg0 (required) - A column needs to be repeated

arg1 (required) - Integer value representing no of times arg0 is to be repeated.

Use Case:

 

Example:

repeat("str",2) will return 'strstr'

Reverse

Reverses the string column and returns it as a new string column.

Input Parameters: Column arg0

arg0 (required) - The string column to be reversed.

Use Case:

 

Example:

reverse("apple") will return "elppa"

Rpad

Right-pad the string column with the given string, to a given length.

Input Parameters: Column arg0, int arg1, Object arg2

• arg0 (required) - The string column to be right-padded.

• arg1 (required) - The length for the padding.

• arg2 (required) - The string to use for right-pad.

Use Case:

 

Example:

rpad("SQL Tutorial", 20, "ABC") will return "SQL TutorialABCABCAB"

Rtrim

Trim the spaces from right end for the specified string column.

Input Parameters: Column arg0

arg0 (required) - The string column from which right spaces to be trimmed.

Use Case:

 

Example:

rtrim("apple ") will return "apple"

Substring

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.

Input Parameters: Column arg0, int arg1, int arg2

• arg0 (required) - The String column from which substring to be extracted.

• arg1 (required) - The start position for the substring.

• arg2 (required) - The end position for the substring.

Use Case:

 

Example:

substring("foo bar",4,6) will return "bar"

Substring_index

Computes the substring from given string column before given count occurrences of the given delimiter.

Input Parameters: Column arg0, Object arg1, int arg2

• arg0 (required) - The String column from which substring to be extracted.

• arg1 (required) - The delimiter.

• arg2 (required) - The count occurrences for the delimiter.

Use Case:

 

Example:

substring_index("www.xyz.com",".",2) will return www.xyz

Translate

Translate any character in the given string by a given character in given replaceS­tring.

Input Parameters: Column arg0, Object arg1, Object arg2

• arg0 (required) - The string column in which the translation to be done.

• arg1 (required) - The matching character.

• arg2 (required) - The replacement character.

Use Case:

 

Example:

translate("The foo bar","f","t") will return "The too bar"

Trim

Trim the spaces from both ends for the specified string column.

Input Parameters: Column arg0

arg0 (required) - The string column from which spaces to be trimmed.

Use Case:

 

Example:

trim(" An apple ") will return "An apple"

Upper

Converts a string column to upper case.

Input Parameters: Column arg0

arg0 (required) - The string column to be converted to upper case.

Use Case:

 

Example:

upper("aPPle") will return "APPLE"

Math Function

Abs

Computes the absolute value.

Input Parameters: Column arg0

arg0 (required) - The column for which absolute value to be calculated.

Use Case:

 

Example:

abs(77.76) will return 77.76

Acos

Computes the cosine inverse of the given value; the returned angle is in the range 0.0 through pi.

Input Parameters: Column arg0

arg0 (required) - The column for which cosine inverse to be calculated.

Use Case:

 

Example:

acos(0.45) will return 1.104031001096478

Asin

Computes the sine inverse of the given value; the returned angle is in the range -pi/2 through pi/2.

Input Parameters: Column arg0

arg0 (required) - The column for which sine inverse to be calculated.

Use Case:

 

Example:

asin(0.45) will return 0.4667653256984187

Atan

Computes the tangent inverse of the given value.

Input Parameters: Column arg0

arg0 (required) - The column for which tangent inverse to be calculated.

Use Case:

 

Example:

atan(0.45) will return 0.42285391621948626

Atan2

Computes the angle theta from the conversion of rectangular coordinates (arg0, arg1) to polar coordinates (arg1, theta).

Input Parameters: Column arg0, Column arg1

• arg0 (required) - The column for the x rectangular coordinate.

• arg1 (required) - The column for the y rectangular coordinate.

Use Case:

 

Example:

atan2(12, 71.21) will return 1.403849169952035

Atan2_left_arg_double

Computes the angle theta from the conversion of rectangular coordinates (arg0, arg1) to polar coordinates (arg1, theta).

Input Parameters: double arg0, Column arg1

• arg0 (required) - The x rectangular coordinate.

• arg1 (required) - The column for the y rectangular coordinate.

Use Case:

 

Example:

atan2_left_arg_double(12, 71.21) will return 1.403849169952035

Atan2_right_arg_double

Computes the angle theta from the conversion of rectangular coordinates (arg0, arg1) to polar coordinates (arg1, theta).

Input Parameters: Column arg0, double arg1

• arg0 (required) - The column for the x rectangular coordinate.

• arg1 (required) - The y rectangular coordinate.

Use Case:

 

Example:

atan2_right_arg_double(12, 71.21) will return 1.403849169952035

Bround

Computes the value of the column arg0 rounded to 0 decimal places with HALF_EVEN round mode.

Input Parameters: Column arg0

arg0 (required) - The column for which value rounded to 0 decimal places with HALF_EVEN round mode to be calculated.

Use Case:

 

Example:

bround(71.21) will return 71.0

Bround_with_scale

Round the value of arg0 to arg1 decimal places with HALF_EVEN round mode if arg1 is greater than or equal to 0 or at integral part when arg1 is less than 0.

Input Parameters: Column arg0, int arg1

• arg0 (required) - The column for which value rounded to 0 decimal places with HALF_EVEN round mode to be calculated.

• arg1 (required) - The scale to use

Use Case:

 

Example:

bround_with_scale(71.21, 1) will return 71.2

Cbrt

Computes the cube-root of the given column.

Input Parameters: Column arg0

arg0 (required) - The column for which cube-root to be calculated.

Use Case:

 

Example:

cbrt(80.89) will return 4.324789202233814

Ceil

Computes the ceiling of the given column.

Input Parameters: Column arg0

• arg0 (required) - The column for which ceiling to be calculated.

Use Case:

 

Example:

ceil(77.76) will return 4.2682720044742055

Conv

Converts a number column from a given base to another.

Input Parameters: Column arg0,Column args1,Column args2

• arg0 (required) - A number/String number column

• arg1 (required) - Integer column of base from which a number is to be converted

• arg2 (required) - Integer column of base to which a number is to be converted.

Use Case:

 

Example:

conv(258,10,2) will return 100000010

Cos

Computes the cosine of the given value.

Input Parameters: Column arg0

• arg0 (required) - The column for which cosine to be calculated.

Use Case:

 

Example:

cos(76.56) will return 0.3977126102073901

Cosh

Computes the hyperbolic cosine of the given value.

Input Parameters: Column arg0

• arg0 (required) -The column for which hyperbolic cosine to be calculated.

Use Case:

 

Example:

cos(76.56) will return 0.3977126102073901

Degrees

Converts an angle measured in radians to an approximately equivalent angle mea­sured in degrees.

Input Parameters: Column arg0

• arg0 (required) -The column for which the equivalent angle measured in degrees to be calculated.

Use Case:

 

Example:

degrees(71.21) will return 4080.0324066707394

Exp

Computes the exponential of the given value.

Input Parameters: Column arg0

• arg0 (required) -The column for which exponential to be calculated.

Use Case:

 

Example:

exp(0.78) will return 2.18147220308578

Expm1

Computes the exponential of the given value minus one.

Input Parameters: Column arg0

• arg0 (required) -The column for which exponential minus one to be calculated.

Use Case:

 

Example:

expm1(0.23) will return 0.2586000151807663

Factorial

Computes the factorial of the given value.

Input Parameters: Column arg0

• arg0 (required) -The column for which factorial to be calculated.

Use Case:

 

Example:

factorial(11) will return 39916800

Floor

Computes the floor of the given column.

Input Parameters: Column arg0

• arg0 (required) -The column for which floor to be calculated.

Use Case:

 

Example:

floor(71.21) will return 71

Format_number

Formats numeric column arg0 to a format like '#,###,###.##', rounded to arg1 deci­mal places, and returns the result as a string column.

Input Parameters: Column arg0, int arg1

• arg0 (required) -The column to be formated.

• arg1 (required) -The integer specifying the decimal places to be used for rounding.

Use Case:

 

Example:

format_number(7120.12, 1) will return 7,120.1

Greatest

It gives the greatest value of the list of values. This function takes at least 2 parame­ters.

Input Parameters: Column arg0,Column args1,...

• arg0 (required) -A column from the schema

• arg1 (required) -A column from the schema

Use Case:

 

Example:

greatest(258,259) will return 259

Hex

If the argument is an INT, hex returns the number as a STRING in hexadecimal for­mat. Otherwise if the number is a STRING, it converts each character into its hexa­decimal representation and returns the resulting STRING.

Input Parameters: Column arg0

• arg0 (required) -A int/string column

Use Case:

 

Example:

hex(258) will return 102

Hypot

Computes sqrt(arg0^2^ + arg1^2^) without intermediate overflow or underflow.

Input Parameters:  Column arg0, Column arg1

• arg0 (required) -Will be used while computing sqrt.

• arg1 (required) -Will be used while computing sqrt.

Use Case:

 

Example:

hypot(71.21, 10.5) will return 71.97995533209642

Hypot_left_arg_double

Computes sqrt(arg0^2^ + arg1^2^) without intermediate overflow or underflow.

Input Parameters:  double arg0, Column arg1

• arg0 (required) -Will be used while computing sqrt.

• arg1 (required) -Will be used while computing sqrt.

Use Case:

 

Example:

hypot_left_arg_double(71.21, 10.5) will return 71.97995533209642

Hypot_right_arg_double

Computes sqrt(arg0^2^ + arg1^2^) without intermediate overflow or underflow.

Input Parameters:  Column arg0, double arg1

• arg0 (required) -Will be used while computing sqrt.

• arg1 (required) -Will be used while computing sqrt.

Use Case:

 

Example:

hypot_right_arg_double(71.21, 10.5) will return 71.97995533209642

Least

It gives the least value of the list of values. This function takes at least 2 parameters.

Input Parameters:  Column arg0,Column args1,...

• arg0 (required) - A column from the schema.

• arg1 (required) - A column from the schema.

Use Case:

 

Example:

least(258,259) will return 259

Log

Computes the natural logarithm of the given value.

Input Parameters: Column arg0

• arg0 (required) - The column for which natural logarithm to be calculated.

Use Case:

 

Example:

log(20) will return 2.995732273553991

Log1p

Computes the natural logarithm of the given value plus one.

Input Parameters: Column arg0

• arg0 (required) - The column for which natural logarithm plus one to be calculated.

Use Case:

 

Example:

log1p(20) will return 3.044522437723423

Log_with_base

Computes the first argument-base logarithm of the second argument.

Input Parameters: double arg0, Column arg1

• arg0 (required) - The base to used.

• arg1 (required) - The column for which logarithm to be calculated.

Use Case:

 

Example:

log_with_base(10, 20) will return 1.301029995663981

Negate

Computes the Unary minus, i.e. -(arg0)

Input Parameters: Column arg0

• arg0 (required) - The column for which unary minus to be calculated.

Use Case:

 

Example:

negate(20) will return -20

Pmod

Computes the positive value of arg0 mod arg1.

Input Parameters: Column arg0, Column arg1

• arg0 (required) - The dividend.

• arg1 (required) - The divisor.

Use Case:

 

Example:

pmod(19, 0.78) will return 0.2800007

Pow

Computes the value of the first argument raised to the power of the second argu­ment.

Input Parameters: Column arg0, Column arg1

• arg0 (required) - The base.

• arg1 (required) - The exponent.

Use Case:

 

Example:

pow(20, 2) will return 400

Pow_left_arg_double

Computes the value of the first argument raised to the power of the second argu­ment.

 Input Parameters:  double arg0, Column arg1

• arg0 (required) - The base.

• arg1 (required) - The exponent.

Use Case:

 

Example:

pow_left_arg_double(20, 2) will return 400

Pow_right_arg_double

Computes the value of the first argument raised to the power of the second argu­ment.

Input Parameters:  Column arg0, double arg1

• arg0 (required) - The base.

• arg1 (required) - The exponent.

Use Case:

 

Example:

pow_right_arg_double(20, 2) will return 400

Radians

Converts an angle measured in degrees to an approximately equivalent angle mea­sured in radians.

Input Parameters:  Column arg0

• arg0 (required) - The column for which equivalent angle measured in radians to be calculated.

Use Case:

 

Example:

radians(20) will return 0.3490658503988659

Rand

Generate a random column with independent and identically distributed (i.i.d.) sam­ples from 0.0 to 1.0.

Input Parameters: No input arguments.

Use Case:

 

Example:

rand() will return a new column with independent and identically distributed (i.i.d.) samples from 0.0 to 1.0.

Rand_with_seed

Generate a random column with independent and identically distributed (i.i.d.) sam­ples from 0.0 to 1.0.

Input Parameters: long arg0

• arg0 (required) - The seed

Use Case:

 

Example:

rand_with_seed(2) will return a new column with independent and identically distrib­uted (i.i.d.) samples from 0.0 to 1.0.

Randn

Generate a column with independent and identically distributed (i.i.d.) samples from the standard normal distribution.

Input Parameters: No input arguments.

Use Case:

 

Example:

randn() will return a new column with independent and identically distributed (i.i.d.) samples from the standard normal distribution.

Randn_with_seed

Generate a column with independent and identically distributed (i.i.d.) samples from the standard normal distribution.

 Input Parameters: long arg0

• arg0 (required) - The seed.

Use Case:

 

Example:

randn_with_seed(2) will return a new column with independent and identically dis­tributed (i.i.d.) samples from the standard normal distribution.

Rint

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

Input Parameters: Column arg0

• arg0 (required) - The column for which double value to be calculated.

Use Case:

 

Example:

rint(80.89) will return 81.0

Round

Computes the value of the column arg0 rounded to 0 decimal places.

Input Parameters: Column arg0

arg0 (required) - The column for which value rounded to 0 decimal places to be cal­culated.

Use Case:

 

Example:

round(80.89) will return 81.0

Round_with_scale

Computes the value of arg0 to scale decimal places if arg1 is greater than or equal to 0 or at integral part when arg1 is less than 0.

 Input Parameters: Column arg0, int arg1

• arg0 (required) - The column for which value rounded to 0 decimal places to be calculated.

• arg0 (required) - The seed.

Use Case:

 

Example:

round_with_scale(80.89,1) will return 80.9

Signum

Computes the signum of the given value.

Input Parameters: Column arg0

• arg0 (required) - The column for which signum to be calculated.

Use Case:

 

Example:

signum(20) will return 1.0

Sin

Computes the sine of the given value.

Input Parameters: Column arg0

• arg0 (required) - The column for which sine to be calculated.

Use Case:

 

Example:

sin(20) will return 0.9129452507276277

Sinh

Computes the hyperbolic sine of the given value.

Input Parameters: Column arg0

• arg0 (required) - The column for which sine to be calculated.

Use Case:

 

Example:

sinh(20) will return 2.4258259770489514E8

SqlSqrt

Computes the square root of the specified float value.

Input Parameters: Column arg0

• arg0 (required) - The column for which square root to be calculated.

Use Case:

 

Example:

sqlSqrt(20) will return 4.47213595499958

Tan

Computes the tangent of the given value.

Input Parameters: Column arg0

arg0 (required) - The column for which tangent to be calculated.

Use Case:

 

Example:

tan(20) will return 2.237160944224742

Tanh

Computes the hyperbolic tangent of the given value.

Input Parameters: Column arg0

• arg0 (required) - The column for which hyperbolic tangent to be calculated.

Use Case:

 

Example:

tanh(20) will return 1.0

ToDegrees

Computes the angle measured in radians to an approximately equivalent angle mea­sured in degrees.

Input Parameters: Column arg0

• arg0 (required) - The column for which degree to be calculated

Use Case:

 

Example:

toDegrees(3.14159) will return 180

ToRadians

Computes the angle measured in degrees to an approximately equivalent angle measured in radians.

Input Parameters: Column arg0

• arg0 (required) - A column for which radians to be calculated.

Use Case:

 

Example:

toRadians(180) will return 3.14159

Array Functions

Array_contains

Returns TRUE if the array contains value.

Input Parameters: Column arg0, Object arg1

• arg0 (required) - An array column.

• arg1 (required) - A value to be checked.

Use Case:

 

Example:

array_contains(["black","red"] ,"red") will return true

Miscellaneous Functions

BitwiseNOT

Computes bitwise NOT of the given integer.

Input Parameters: Column arg0

• arg0 (required) - An integer or a double column.

Use Case:

 

Example:

bitwiseNOT(7) will return 8

Crc32

Computes a cyclic redundancy check value for string.

Input Parameters: Column arg0

• arg0 (required) - A string column.

Use Case:

 

Example:

crc32("ABC") will return 2743272264

Isnan

Returns true if the input is Not a Number.

Input Parameters: Column arg0

• arg0 (required) - A column whose values needs to be checked

Use Case:

 

Example:

isnan("abc") will return true

Isnull

Returns true if “a” is NULL and false otherwise.

Input Parameters: Column arg0

• arg0 (required) - A column whose values needs to be checked

Use Case:

 

Example:

isnull("abc") will return false

Lit

Creates a Column of literal value.

Input Parameters: Object arg0

• arg0 (required) - A constant value.

Use Case:

 

Example:

lit(1) will return a column having 1 in all the rows of the column.

Md5

Calculates an MD5 128-bit checksum for the string.

Input Parameters: Column arg0

• arg0 (required) - A string column

Use Case:

 

Example:

md5('ABC') will give you an output '902fbdd2b1df0c4f70b4a5d23525e932'.

Monotonically_increasing_id

A column expression that generates monotonically increasing 64-bit integers.

Input Parameters: No input arguments.

Example:

monotonically_increasing_id() will return rows as 0,1,2...

Sha1

Calculates the SHA-1 digest for string and returns the value as a hex string

Input Parameters: Column arg0

• arg0 (requird) - A string column

Use Case:

 

Example:

sha1("ABC") will return '3c01bdbb26f358bab27f267924aa2c9a03fcfdb8'

Sha2

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.

Input Parameters: Column arg0,int numBits

• arg0 (required) - A string column

• arg1 (required) - one of 224, 256, 384, or 512.

Use Case:

 

Example:

sha2("Sam",256) will return '4ecde249d747d51d8..'

ShiftLeft

Bitwise left shift, Shifts a b positions to the left.

Input Parameters: Column arg0,int numBits

• arg0 (required) - A number column

• arg1 (required) - An integer column.

Use Case:

 

Example:

shiftLeft(258,2) will return 1032

ShiftRight

Bitwise right shift, Shifts a b positions to the right.

Input Parameters: Column arg0,int numBits

• arg0 (required) - A number column

• arg1 (required) - An integer column.

Use Case:

 

Example:

shiftRight(258,2) will return 64

ShiftRightUnsigned

Bitwise unsigned right shift, Shifts a b positions to the right.

Input Parameters: Column arg0,int numBits

• arg0 (required) - A number column

• arg1 (required) - An integer column.

Use Case:

 

Example:

shiftRightUnsigned(258,2) will return 64