Skip to main content

Prepare function tooltips

ABS

Syntax

Description

Sample usage

(LONG|DOUBLE|DECIMAL)

Returns the absolute value of an expression.

Note: If the result is not negative (x ≥ 0), the result is returned. If the result is negative (x < 0), the negation of the result is returned.

(-7) will return 7

 

ACOS

Syntax

Description

Sample usage

(DOUBLE)

 

Returns the arc cosine of an expression.

 

(.13) will return 1.440427347091751

ASIN

Syntax

Description

Sample usage

(DOUBLE)

 

Returns the arc sine of an expression.

 

(.442) will return 0.45782706881105106

ATAN

Syntax

Description

Sample usage

(DOUBLE)

 

Returns the arc tangent of an expression.

 

(1) will return 0.7853981633974483

CBRT

Syntax

Description

Sample usage

(DOUBLE)

 

Returns the cube root of an expression.

 

(-8) will return -2

CEIL

Syntax

Description

Sample usage

(DOUBLE)

 

Returns the value of an expression rounded up to the nearest integer.

Note: This function never decreases the result value.

(-3.23) will return -3

Input/output (CEIL)

x

CEIL (x)

4.6

5

3.5

4

2.4

3

-1.0

-1

-2.4

-2

-3.5

-3

CONCAT

Syntax

Description

Sample usage

(EXPRESSION, EXPRESSION)

 

Concatenates (chains together) two expressions of identical type.

Note: If either expression is null, the resulting expression is null.

('80', '7') will return 807

 

ConvertDdMonYy

Syntax

Description

Sample usage

(STRING)

Converts string representing a date to ISO format.

('25-DEC-17') will return 2018-12-25

ConvertMmDdYyyy

Syntax

Description

Sample usage

(STRING)

Converts string representing a date to ISO format.

('12/25/2018') will return 2018-12-25

ConvertYYYYMMDD

Syntax

Description

Sample usage

(STRING)

Converts string representing a date to ISO format.

('2018/12/25') will return 2018-12-25

COS

Syntax

Description

Sample usage

(DOUBLE)

Returns the cosine of an expression.

(.55) will return 0.8525245220595057

COSH

Syntax

Description

Sample usage

(DOUBLE)

Returns the hyperbolic cosine of an expression.

(4) will return 27.308232836016487

CurrentDateTimeFormatted

Syntax

Description

Sample usage

(STRING)

Returns a string representing the date of when the Pig job started in a format based on the string argument (defined by the user).

('DD/MM/YYYY') will return 12/25/2018

CurrentDateTimeString

Syntax

Description

Sample usage

No arguments required, use ()

Returns a string representing the date of when the Pig job started in a standard ISO format (e.g., 2015-12-14T17:49:11.686-05:00, representing year, month, day, hours, minutes, seconds, milliseconds, and timezone in that order.)

returns 2018-12-25T17:49:11.686-05:00

DateAddDays

Syntax

Description

Sample usage

(STRING, INTEGER, BOOLEAN)

Returns string representing a date after adding specified number of days (could be any +positive or -negative integer) to the given date. Optional boolean variable indicates whether exception or null will be returned for invalid data input; true=exception, false=null

('2018-12-20', 2, true) will return 2018-12-22 or (REMIT_PYMTDATE, 2, true) will return 2018-12-22 where REMIT_PYMTDATE=2018-12-20 for that record.

DateCompare

Syntax

Description

Sample usage

(STRING, STRING, BOOLEAN)

Returns integer indicating whether the second data is equal, earlier, or later than the first date. 

0 = equal

1 = stringDate1 is after stringDate2

-1 = stringDate1 is before stringDate2

Optional boolean variable indicates whether exception or null will be returned for invalid data input; true=exception, false=null

('2018-12-21', '2018-12-20') will return 1

('2018-12-20', '2018-12-20') will return 0

('2018-12-20', '2018-12-21') will return -1

DateConvertDdMonYy

Syntax

Description

Sample usage

(STRING)

Converts a string representing a date to ISO format.

('25-DEC-2018') will return 2018-12-25

DateConvertMmDdYyyy

Syntax

Description

Sample usage

(STRING)

Converts a string representing a date to ISO format.

('12/25/2018') will return 2018-12-25

DateConvertYYYYMMDD

Syntax

Description

Sample usage

(STRING)

Converts a string representing a date to ISO format.

('2018/12/25') will return 2018-12-25

DateCurrent

Syntax

Description

Sample usage

No arguments required, use ()

Returns a string representing the current date.

returns 2018-02-11

DateDaysDifference

Syntax

Description

Sample usage

(startDate, endDate)

Returns the number of days between two dates.

('2018-12-20', '2018-12-25') returns 5

('2018-12-25', '2018-12-20') returns -5

('2018-01-01', '2018-01-01') returns 0

DateFirstDayOfMonth

Syntax

Description

Sample usage

(STRING)

Returns the first day of the month for the input string representing a date.

 

('2018-12-25') will return 2018-12-01

DateGetWeekdayNumber

Syntax

Description

Sample usage

(STRING)

Returns ISO weekDayNumber (Monday = 1, Tuesday=2, and so on..) for string input representing a date.

('2018-12-25') will return 5 (Friday)

DateIsBetweenExclusive

Syntax

Description

Sample usage

(STRING, STRING, STRING, BOOLEAN)

Returns boolean based on whether the first input date string param is between the 2nd and 3rd date, excluding both boundaries. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

 

('2018-12-21', '2018-12-20', '2018-12-25') will return true

('2018-12-20', '2018-12-20', '2018-12-25') will return false

DateIsBetweenInclusive

Syntax

Description

Sample usage

(STRING, STRING, STRING, BOOLEAN)

Returns boolean based on whether the first input date string param is between the 2nd and 3rd date, including both boundaries. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

('2018-12-21', '2018-12-20', '2018-12-25') will return true

DateIsEqual

Syntax

Description

Sample usage

(STRING, STRING, BOOLEAN)

Returns boolean stating two input dates are equal or not. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

 

('2018-12-21', '2018-12-20') will return false

('2018-12-20', '2018-12-20') will return true

DateIsValidFormat

Syntax

Description

Sample usage

(STRING, STRING, BOOLEAN)

Returns boolean if the specified date input matches the input format, otherwise false. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

('2018-12-22', 'yyyy-MM-dd') will return true

('2018-12-22', 'yyyy/MM/dd') will return false

DateParseFormat

Syntax

Description

Sample usage

(STRING, STRING, BOOLEAN)

Returns ISO date for the specified input date string. Input format string param specified by user (provide the format applied to input string representing a date). Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

('2018/12/22', 'yyyy/MM/dd') will return 2018-12-22

('20181222', 'yyyyMMdd') will return 2018-12-22

DateRollBackwardToWeekDayByWeek

Syntax

Description

Sample usage

(STRING, INTEGER, INTEGER, BOOLEAN)

Returns the date based on requested day (backward) of the week of given date. dayNum = > Monday = 1, Tues = 2 and so on...till  SunDay = 7.  intWeekNum=> lastweek =0, one week before last week = 1, two weeks before last week=2, and so on. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

 

('2019-01-11', 3, 1) will return 2019-01-02 (the Wednesday 10 days before)

DateRollForwardToWeekDay

Syntax

Description

Sample usage

(STRING, INTEGER, BOOLEAN)

Returns the date based on requested day (forward) of the week of given date.dayNum = > Monday = 1, Tues = 2 and so on...till  SunDay = 7.  Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

 ('2019-01-11', 1, false) will return 2019-01-14

DiceCoefficient

Syntax

Description

Sample usage

(STRING, STRING)

Returns the number of tokens (character pairs) common between two strings divided by the total number of tokens.

('frog', 'fog') returns 0.4

DoubleAbs

Syntax

Description

Sample usage

(DOUBLE)

Returns a single numeric value, absolute value of the argument.

(-8.4567890) will return 8.456789

ENDSWITH

Syntax

Description

Sample usage

(STRING, STRING)

Determines if the first argument ends with the string in the second.

 

 

(Major, 'y') will return true for fields ending with 'y' (Psychology, 'y') and false for fields that do not end with 'y' (Accounting, 'y')

EqualsIgnoreCase

Syntax

Description

Sample usage

BOOLEAN (STRING, STRING)

Determines if two strings are equal (ignoring case).

('field_a', 'Field_b') will return false

 ('field_a', 'Field_a') will return true

EXP

Syntax

Description

Sample usage

(DOUBLE)

Returns Euler's number e raised to the power of x.

(.5) will return 1.6487212707001282

FLOOR

Syntax

Description

Sample usage

(DOUBLE)

Returns the value of an expression rounded down to the nearest integer.

Note: This function never increases the result value.

(-3.23) will return -4

(3.23) will return 3

Input/output (FLOOR)

x

 (x)

4.6

4

3.5

3

2.4

2

1.0

1

-1.0

-1

-2.4

-3

-3.5

-4

INDEXOF

Syntax

Description

Sample usage

(STRING, STRING)

Returns the index of the first occurrence of a character in a string, searching forward from a start index.

('banana' , 'b') will return 0

('banana' , 'a') will return 1

('banana' , 'n') will return 2

IS_DOUBLE

Syntax

Description

Sample usage

(STRING)

Returns boolean whether or not the input string is a Double.

('5500.00') will return true

('5500') will return true

('JOB_TITLE') will return false

IS_INTEGER

Syntax

Description

Sample usage

(STRING)

Returns boolean whether or not the input string is an Integer.

 

('Billy') will return false

('9') will return true

('9.1') will return false

IS_LONG

Syntax

Description

Sample usage

(STRING)

Returns boolean whether or not the input string is type long.

(89745463.9876) will return false (if not stored as a long data type)

IS_NULL

Syntax

Description

Sample usage

(STRING|LONG|DOUBLE|DECIMAL)

Returns boolean whether or not the input string is NULL.

Null fields will return true

IS_NULL_OR_EMPTY

Syntax

Description

Sample usage

(STRING)

Returns boolean whether or not the input string is NULL or EMPTY.

Null or empty fields will return true

LAST_INDEX_OF

Syntax

Description

Sample usage

(STRING, STRING)

Returns the index of the last occurrence of a character in a string, searching backward from the end of the string. The index begins at 0.

('madam', 'm') will return 4

LCFIRST

Syntax

Description

Sample usage

(STRING)

Converts the first character in a string to lower case.

('Marlboro') will return marlboro

LevenshteinDistance

Syntax

Description

Sample usage

LevenshteinDistance

Returns the number of changes needed to change one sequence into another, where each change is a single character modification (deletion, insertion, or substitution)

('frog', 'fog') returns 1

LongestCommonSubsequence

Syntax

Description

Sample usage

(STRING, STRING)

Returns the length of the longest subsequence that two strings have in common. Two strings that are entirely different, return a value of 0, and two strings that return a value of the commonly shared length implies that the strings are completely the same in value and position.

('axbyczqrs', 'abcxyzqtv') returns 4

LOG

Syntax

Description

Sample usage

(DOUBLE)

Returns the natural logarithm (base e) of an expression.

 

(4) will return 1.3862943611198906

LOG10

Syntax

Description

Sample usage

(DOUBLE)

Returns the base 10 logarithm of an expression.

(4) will return 0.6020599913279624

LOWER

Syntax

Description

Sample usage

(STRING)

Converts all characters in a string to lower case.

('MARLBORO') will return marlboro

LTRIM

Syntax

Description

Sample usage

(STRING)

Returns a copy of the string with only leading white space removed.

('   Marlboro') will return Marlboro (spaces before the string have been removed)

NextSequence

Syntax

Description

Sample usage

(""schemaName"", "sequenceName")

Returns next value for the desired sequence as per the increment by and last value obtained by select * from podium_core.pd_role_nid_seq; from postgres database.

(""jdbc:postgresql://localhost:5432/podium_md?user=postgres&password=password"", ""pd_role_nid_seq"") will return nextval for seq pd_role_nid_seq

NVL

Syntax

Description

Sample usage

(STRING, STRING)

Returns second string when a null value for first string is encountered. 

(Major,  'n/a') will return 'n/a' if the 'Major' field contains a null value. Otherwise, it would return the field value.

OBFUSCATE

Syntax

Description

Sample usage

(STRING|LONG|DOUBLE|BOOLEAN|DECIMAL, STRING)

Obfuscates a string or number to string value using the obfuscation rule. Obfuscation rules must already be defined in the metadata with the exact rule name.

('Input') will return ('ObfuscatedInput') dependent on obfuscation rule applied.

RANDOM

Syntax

Description

Sample usage

no arguments required, use ()

Returns a pseudo random number (DOUBLE) greater than or equal to 0.0 and less than 1.0.

() will return a different pseudo-randomly generated value (greater than or equal to 0.0 and less than 1) for each record in that field(column). These values take the place of the values in that output column for the field in which this function is entered. 

example of output values:

0.8448849155781958

0.8179221440864705

0.7165316814860472

REGEX_EXTRACT

Syntax

Description

Sample usage

(STRING, STRING, INTEGER)

Matches regular expression and extracts the matched group defined by index parameter.

 

(Client, 'e', 0) returns e in those fields with an e present (the first index)

('Client', 'e', 0) returns e (the e in the string 'client')

REPLACE

Syntax

Description

Sample usage

(STRING, STRING, STRING)

Replaces existing characters in a string with new characters.

 

('marlboro', 'o', 'a') returns marlbara

 

ROUND

Syntax

Description

Sample usage

(DOUBLE)

Returns the value of an expression rounded to an integer.

 

(1.02) will return 1

(-3.2) will return 3

Input/Output (ROUND)

x

ROUND (x)

4.6

5

3.5

4

2.4

2

1.0

1

-1.0

-1

-2.4

-2

-3.5

-3

-4.6

-5

ROUND_TO

Syntax

Description

Sample usage

(DOUBLE|DECIMAL, INTEGER)

Returns the value of an expression rounded to a specified number of decimal digits.

(1234.56, -2) will return 1200.0

RTRIM

Syntax

Description

Sample usage

(STRING)

Returns a copy of a string with only trailing whitespace removed.

('Marlboro   ') will return Marlboro (spaces after the string have been removed)

SIN

Syntax

Description

Sample usage

(DOUBLE)

Returns the sine of an expression.

(.75) will return 0.6816387600233341

SINH

Syntax

Description

Sample usage

(DOUBLE)

Returns the hyperbolic sine of an expression.

(.75) will return 0.82231673193583

SIZE

Syntax

Description

Sample usage

(STRING|INTEGER|LONG|DOUBLE)

Computes the number of elements based on any data type.

SIZE includes NULL values in the size computation.

SIZE is not algebraic.

If the tested object is null, the SIZE function returns null.

(Joe) will return 3

(Petunia) will return 7

(00036) will return 1

(1.2679897) will return 1

SPRINTF

Syntax

Description

Sample usage

(FORMAT, STRING, STRING)

Formats string inputs into a new string based on a printf-style template.

 

('App Version %s %s-%s', 'qdc', TO_STRING(4.2), 'patch') will return "App Version qdc 4.2-patch"

SQRT

Syntax

Description

Sample usage

(DOUBLE)

Returns the positive square root of an expression.

(9) will return 3.0

STARTSWITH

Syntax

Description

Sample usage

(STRING, STRING)

Determines if the first argument starts with the second string.

('llama', 'm') will return false

('marlboro', 'm') will return true

SUBSTRING

Syntax

Description

Sample usage

 (SUBSTRING, STARTINDEX, LENGTH)

Returns a subset of the given string.

StartIndex is zero based.

If passed in string is null, UDF returns null.

If startIndex<0 OR startIndex>[stringlength] return null

If length<0 returns null

If length==0 returns empty string

if length>[string length] return till the last character

 

("abc", 2,1) returns c

("abc",2,0) returns ""

("abc",2,-1) returns NULL

("abc", 2, 5) returns c

(NULL,0,1) returns NULL

("",0,1) returns NULL 

TAN

Syntax

Description

Sample usage

(DOUBLE)

Returns the tangent of an expression.

(4) will return 1.1578212823495777

 

TANH

Syntax

Description

Sample usage

(DOUBLE)

Returns the hyperbolic tangent of an expression.

(4) will return 0.999329299739067

 

TO_DECIMAL

Syntax

Description

Sample usage

(STRING|LONG|DOUBLE)

Casts input to type decimal.

(89745463.9876) will return 89745463.9876

TO_DOUBLE

Syntax

Description

Sample usage

(STRING|LONG|DECIMAL)

Casts input to type double.

('15678') will return 15678.0

TO_INTEGER

Syntax

Description

Sample usage

(STRING|DOUBLE|DECIMAL)

Casts input to type integer.

(1.9) will return 1

TO_LONG

Syntax

Description

Sample usage

(STRING|DOUBLE|DECIMAL)

Casts input to type long.

(89745463.9876) will return 89745463

TO_STRING

Syntax

Description

Sample usage

(LONG|DOUBLE|BOOLEAN|DECIMAL)

Casts input to type string.

(223) will return 223

TRIM

Syntax

Description

Sample usage

(STRING)

Returns a copy of a string with leading and trailing white space removed.

('   Marlboro   ') will return Marlboro (spaces before and after string have been removed)

UCFIRST

Syntax

Description

Sample usage

(STRING)

Returns a string with the first character converted to upper case.

('marlboro') will return Marlboro

UniqueID

Syntax

Description

Sample usage

no arguments required, use ()

Returns strings representing field's unique ID for each record. The ID takes form "taskindex-sequence".

() will return ID values ('0-80', '0-71', '0-106')

 

UPPER

Syntax

Description

Sample usage

(STRING)

Returns a string converted to upper case.

('marlboro') will return MARLBORO