Documentation

Transformation Functions list

Functions are you bricks needed to build the Rules.

You can create rules using functions. This can be a very useful method

This section describes each function in terms of the following.

  • Category
  • Description
  • Function interface
  • Arguments
  • Return type
  • Example
  • Remarks

The following functions are currently supported by data preperation:

length

Category

  • String Function

Description

  • Returns the length of the input string

Function interface

  • length(string_value)

Arguments

  • string_value: the string whose length you want to find.

Return type

  • Integer

Example

  • length(first_name)

if

Category

  • Logical Function

Description

  • Examine the conditional statement and return a value corresponding to TRUE or FALSE.

Function interface

  • if(condition)
  • if(condition, true_value, false_value)

Arguments

  • condition: The condition to check for true / false
  • true_value: The value returned if the conditional statement is true.
  • false_value: The value returned if the conditional statement is false.

Return type

  • Any

Example

  • if(gender==‘male’) : TRUE
  • if(age<18, ‘kid’, ‘adult’) : ‘adult’

Remarks

  • If true_value/false_value does not exist, it returns TURE or FALSE as a result of Boolean type.

isnull

Category

  • Logical Function

Description

  • Determines whether the value of the input column is null. Returns TRUE if null, or FALSE.

Function interface

  • isnull(condition)

Arguments

  • condition: The column to determine if null.

Return type

  • Boolean

Example

  • isnull(telephone) : FALSE

isnan

Category

  • Logical Function

Description

  • Determines if input value is NaN (Not-a-Number). Returns TRUE if NaN, FALSE otherwise.

Function interface

  • isnan(condition)

Arguments

  • condition: The column or formula for which to determine NaN.

Return type

  • Boolean

Example

  • isnan(1000/ratio)

Remarks

  • The result of the condition must be a Double Value.

upper

Category

  • String Function

Description

  • Returns all uppercase letters of the alphabet entered.

Function interface

  • upper(string_value)

Arguments

  • string_value: The string to replace with an uppercase letter.

Return type

  • String

Example

  • upper(last_name)
  • upper(‘Hello world’) : ’HELLO WORLD’

lower

Category

  • String Function

Description

  • Returns all lowercase letters of the entered string.

Function interface

  • lower(string_value)

Arguments

  • string_value: the string you want to replace with lowercase.

Return type

  • String

Example

  • lower(last_name)
  • lower(‘Hello WORLD’) : ’hello world’

trim

Category

  • String Function

Description

  • Returns the spaces before and after the input string.

Function interface

  • trim(string_value)

Arguments

  • string_value: The string to remove whitespace from.

Return type

  • String

Example

  • trim(comment)
  • trim(‘  .   Hi!   ‘) : ‘.   Hi!’

ltrim

Category

  • String Function

Description

  • Remove and return the space before the input string.

Function interface

  • ltrim(string_value)

Arguments

  • string_value: The string to remove whitespace from.

Return type

  • String

Example

  • ltrim(comment)
  • ltrim(‘  .   Hi!   ‘) : ’.   Hi!   ‘

rtrim

Category

  • String Function

Description

  • Returns the space after the input string.

Function interface

  • rtrim(string_value)

Arguments

  • string_value: The string to remove whitespace from.

Return type

  • String

Example

  • rtrim(comment)
  • rtrim(‘  .   Hi!   ‘) : ‘  .   Hi!’

substring

Category

  • String Function

Description

  • Returns part of the input string.

Function interface

  • substring(string_value, begin_index, offset)
  • substring(string_value, begin_index)

Arguments

  • string_value: The string to edit.
  • begin_index: Start index of the part to extract from the target string. The beginning of the string is 0. If you enter a negative number, it goes back to the last character of the string.
  • offset: The length of the string to extract from the target string. If not entered, extracts from begine_index to the end of the string.

Return type

  • String

Example

  • substring(user_id, 0, 5)
  • substring(‘hello world’, 1, 7) : ‘ello w’
  • substring(‘improvado’, -2) : ‘do’

concat

Category

  • String Function

Description

  • Concatenates and returns multiple input strings.

Function interface

  • Concatenate and return multiple input strings.

Arguments

  • string_value (X): String to concatenate. You can enter multiple n items.

Return type

  • String

Example

  • concat(first_name, ‘-‘, last_name) : ‘Jane-Doe’
  • concat(‘1980’, ’02’) : ‘198002’

concat_ws

Category

  • String Function

Description

  • Concatenates multiple input strings and returns a Separator between them.

Function interface

  • concat(separator, stirng_value1, string_value2)

Arguments

  • separator: Separator to insert between strings to be concatenated.
  • string_value (X): String to concatenate. You can enter multiple n items.

Return type

  • String

Example

  • concat_ws(‘, ’, first_name, last_name) : ‘Jane, Doe’
  • concat_ws(‘-‘, ‘010’, ‘1234’, ‘5678’) : ’010-1234-5678’

year

Category

  • Timestamp Function

Description

  • Returns a value corresponding to the year from the entered Timestamp value.

Function interface

  • year(timestamp_value)

Arguments

  • timestamp_value: timestamp

Return type

  • Integer

Example

  • year(birthday)

month

Category

  • Timestamp Function

Description

  • Returns the value corresponding to the month in the entered Timestamp value.

Function interface

  • month(timestamp_value)

Arguments

  • timestamp_value: the timestamp from which you want to extract the month

Return type

  • Integer

Example

  • month(birthday)

day

Category

  • Timestamp Function

Description

  • Returns a value corresponding to day from an entered Timestamp value.

Function interface

  • day(timestamp_value)

Arguments

  • timestamp_value: the timestamp from which you want to extract the day

Return type

  • Integer

Example

  • day(birthday)

hour

Category

  • Timestamp Function

Description

  • Returns a value corresponding to a time from an entered Timestamp value.

Function interface

  • hour(timestamp_value)

Arguments

  • timestamp_value: timestamp from which you want to extract time

Return type

  • Integer

Example

  • hour(last_login)

minute

Category

  • Timestamp Function

Description

  • Returns a value corresponding to minutes from the entered Timestamp value.

Function interface

  • minute(timestamp_value)

Arguments

  • timestamp_value: the timestamp from which you want to extract minutes

Return type

  • Integer

Example

  • minute(last_login)

second

Category

  • Timestamp Function

Description

  • Returns the value corresponding to seconds from the entered Timestamp value.

Function interface

  • second(timestamp_value)

Arguments

  • timestamp_value: the timestamp from which you want to extract seconds

Return type

  • Integer

Example

  • second(last_login)

millisecond

Category

  • Timestamp Function

Description

  • Returns the value corresponding to milliseconds (1/1000 second) from the entered Timestamp value.

Function interface

  • millisecond(timestamp_value)

Arguments

  • timestamp_value: the timestamp from which you want to extract milliseconds

Return type

  • Integer

Example

  • millisecond(last_login)

now

Category

  • Timestamp Function

Description

  • Returns the current time based on the entered Timezone.

Function interface

  • now()
  • now(timezone)

Arguments

  • timzone: The full-name of the timezone for which you want to get the current time.

Return type

  • Integer

Example

  • now()
  • now(‘Asia/Seoul’)

Remarks

  • If no Timezone value is entered, returns the time in UTC.

add_time

Category

  • Timestamp Function

Description

  • Returns the value added or subtracted from the input Timestamp value.

Function interface

  • add_time(timestamp, delta, time_unit)

Arguments

  • timestamp: the original timestamp value being targeted
  • delta: the date / time value to add or subtract
  • time_unit: The unit of date / time to add or subtract (in string). year, month, day, hour, minute, second, millisecond.

Return type

  • Integer

Example

  • add_time(end_date, 10, ‘day’)
  • add_time(end_date, -1, ‘month’)

sum

Category

  • Aggregation Function

Description

  • Returns the sum of the target values.

Function interface

  • sum(target_col)

Arguments

  • target_col: Target column to sum

Return type

  • Double

Example

  • sum(profit)

Remarks

  • Only available for aggregation and window rules.

avg

Category

  • Aggregation Function

Description

  • Returns the average of the target values.

Function interface

  • avg(target_col)

Arguments

  • target_col: Target column to average

Return type

  • Double

Example

  • avg(profit)

Remarks

  • Only available for aggregation and window rules.

max

Category

  • Aggregation Function

Description

  • Returns the largest of the target values.

Function interface

  • max(target_col)

Arguments

  • target_col: Target column to get the maximum value

Return type

  • Double

Example

  • max(profit)

Remarks

  • Only available for aggregation and window rules.

min

Category

  • Aggregation Function

Description

  • Returns the smallest of the target values.

Function interface

  • min(target_col)

Arguments

  • target_col: Target column to get the minimum value

Return type

  • Double

Example

  • min(profit)

Remarks

  • Only available for aggregation and window rules.

count

Category

  • Aggregation Function

Description

  • Returns the number of rows in the target.

Function interface

  • count()

Return type

  • Double

Example

  • count()

Remarks

  • Only available for aggregation and window rules.

math.abs

Category

  • Math Function

Description

  • Returns the absolute value of the entered value.

Function interface

  • math.abs(value)

Arguments

  • value: A number whose absolute value you want to find.

Return type

  • Double

Example

  • math.abs(-10) : 10

math.acos

Category

  • Math Function

Description

  • Returns the arc cosine of the entered value.

Function interface

  • math.acos(value)

Arguments

  • value: The cosine of which you want to find the arc cosine.

Return type

  • Double

Example

  • math.acos(-1) : 3.141592653589793

math.asin

Category

  • Math Function

Description

  • Returns the arc sine of the entered value.

Function interface

  • math.asin(value)

Arguments

  • value: The sine of which you want to find the arc sine, in the range -1 to 1.

Return type

  • Double

Example

  • math.asin(-1) : -1.5707963267948966

math.atan

Category

  • Math Function

Description

  • Returns the arc sine of the entered value.

Function interface

  • math.atan(value)

Arguments

  • value: The sine of which you want to find the arc sine, in the range -1 to 1.

Return type

  • Double

Example

  • math.asin(-1) : -1.5707963267948966

math.cbrt

Category

  • Math Function

Description

  • Returns the cube root of the entered value.

Function interface

  • math.cbrt(value)

Arguments

  • value: The number whose cube root you want to find.

Return type

  • Double

Example

  • math.cbrt(5) : 1.709975946676697

math.ceil

Category

  • Math Function

Description

  • Returns the value rounded up to be a multiple of day.

Function interface

  • math.ceil(value)

Arguments

  • value: The number you want to round to one’s place.

Return type

  • Double

Example

  • math.ceil(15.142) : 16

math.cos

Category

  • Math Function

Description

  • Returns the cosine of the entered value.

Function interface

  • math.cos(value)

Arguments

  • value: the radian angle to get the cosine of

Return type

  • Double

Example

  • math.cos(45) : 0.5253219888177297

math.cosh

Category

  • Math Function

Description

  • Returns the hyperbolic cosine of the entered value.

Function interface

  • math.cosh(value)

Arguments

  • value: The number whose hyperbolic cosine is to be obtained.

Return type

  • Double

Example

  • math.cosh(9) : COSH(9) => 4051.5420254925943

math.exp

Category

  • Math Function

Description

  • Returns the natural logarithm of e raised to the power of the input value.

Function interface

  • math.exp(value)

Arguments

  • value: The number of times to want to log the natural logarithm e.

Return type

  • Double

Example

  • math.exp(4) : 54.598150033144236

math.expm1

Category

  • Math Function

Description

  • Returns the natural logarithm e, multiplied by the value entered, minus one.

Function interface

  • math.expm1(value)

Arguments

  • value: The number of times to want to log the natural logarithm e.

Return type

  • Double

Example

  • math.expm1(4) : 53.598150033144236

math.getExponent

Category

  • Math Function

Description

  • Returns the largest of exp values ​​that satisfy 2exp <= N for the entered value N.

Function interface

  • math.getExponent(value)

Arguments

  • value: The number corresponding to N when looking for an exp value that satisfies 2exp <= N.

Return type

  • Double

Example

  • math.getExponent(9) : 3

math.round

Category

  • Math Function

Description

  • Returns the value rounded to the ones place.

Function interface

  • math.round(value)

Arguments

  • value: the number to be rounded to

Return type

  • Double

Example

  • math.round(14.2) : 14

math.signum

Category

  • Math Function

Description

  • Returns the sign of the entered value.

Function interface

  • math.signum(value)

Arguments

  • value: the number to extract the sign of

Return type

  • Double

Example

  • math.signum(-24) : -1

Remarks

  • If the number entered is 1, it is 1, 0 is 0, and -1 if it is negative.

math.sin

Category

  • Math Function

Description

  • Returns the sine of the entered value.

Function interface

  • math.sin(value)

Arguments

  • value: the radian angle for which you want to find the sine

Return type

  • Double

Example

  • math.sin(90) : 0.8939966636005579

math.sinh

Category

  • Math Function

Description

  • Returns the hyperbolic sine of the entered value.

Function interface

  • math.sinh(value)

Arguments

  • value: the number whose hyperbolic sine is to be obtained

Return type

  • Double

Example

  • math.sinh(1) : 1.1752011936438014

math.sqrt

Category

  • Math Function

Description

  • Returns the square root of the entered value.

Function interface

  • math.sqrt(value)

Arguments

  • value: the number whose square root you want to find

Return type

  • Double

Example

  • math.sqrt(4) : 2

math.tan

Category

  • Math Function

Description

  • Returns the tangent of the entered value.

Function interface

  • math.tan(value)

Arguments

  • value: the radian angle for the tangent value

Return type

  • Double

Example

  • math.tan(10) : 0.6483608274590866

math.tanh

Category

  • Math Function

Description

  • Returns the hyperbolic tangent of the entered value.

Function interface

  • math.tanh(value)

Arguments

  • value: The angle to get the hyperbolic tangent of.

Return type

  • Double

Example

  • math.tanh(4) : 0.999329299739067

time_diff

Category

  • Timestamp Function

Description

  • Calculates and returns the difference between two input Timestamp values ​​in milliseconds.

Function interface

  • time_diff(timestamp1, timestamp2)

Arguments

  • timestamp1:C = B - A the timestamp of A
  • timestamp1: C = B - A, the timestamp of B

Return type

  • Double

Example

  • time_diff(order_date, shipped_date)

Remarks

  • result value = timestamp2 - timestamp1

timestamp

Category

  • Timestamp Function

Description

  • Create a new Timestamp value.

Function interface

  • timestamp(value, format)

Arguments

  • value: Date/Time value to create as timestamp value.
  • format: The time format of the value value.

Return type

  • Timestamp

Example

  • timestamp(‘2011-01-01’, ‘yyyy-MM-dd’) : 2011-01-01T00:00:00.000Z

row_number

Category

  • Window Function

Description

  • Generates serial numbers of rows arranged in order in the partition.

Function interface

  • row_number()

Return type

  • Long

Example

  • row_number()

Remarks

  • Only available with Window Rule.

rolling_sum

Category

  • Window Function

Description

  • Returns the sum of the values ​​of the specified number of rows before and after within the partition.

Function interface

  • rolling_sum(target_col, before, after)

Arguments

  • target_col: Target column name to sum.
  • before: Number of preceding rows to sum.
  • after: The number of trailing rows to sum.

Return type

  • Long/Double

Example

  • rolling_sum (profit, 3, 3): Combines profits for a total of seven rows, including three rows before and after the same partition.

Remarks

  • Only available with Window Rule.

rolling_avg

Category

  • Window Function

Description

  • Returns the average of the values ​​of the specified number of rows before and after in the partition.

Function interface

  • rolling_avg(target_col, before, after)

Arguments

  • target_col: The target column name for which you want to average.
  • before: The number of preceding rows to average.
  • after: number of trailing rows to average.

Return type

  • Long/Double

Example

  • rolling_avg (profit, 3, 3): average of 7 rows’ profits including 3 rows before and after the same partition

Remarks

  • Only available with Window Rule.

lag

Category

  • Window Function

Description

  • Returns the value of the row that is earlier than the specified number in the partition.

Function interface

  • lag(target_col, before)

Arguments

  • target_col: Target column name.
  • before: A number that specifies how far back to return the current row.

Return type

  • Long/Double

Example

  • lag (profit, 2): Returns the profit value of the row above 2 lines in the same partition. If there is no value above line 2, it returns null.

Remarks

  • Only available with Window Rule.

lead

Category

  • Window Function

Description

  • Returns the value of Row after the specified number within the partition.

Function interface

  • lead(target_col, after)

Arguments

  • target_col: Target column name.
  • after: A number that specifies how far behind the current row to return.

Return type

  • Long/Double

Example

  • lead (profit, 2): returns the profit value of a row below 2 lines in the same partition. If there is no value under line 2, it returns null.

Remarks

  • Only available with Window Rule.

ismismatched

Category

  • Logical Function

Description

  • Returns whether the Value of the specified column matches a specific Column Type.

Function interface

  • ismismatched(target_col, column_type)

Arguments

  • target_col: Column name to check type.
  • column_type: Type to check for match. (Type as string) String, Boolean, Timestamp, Long, Double

Return type

  • Boolean

Example

  • ismismatched (birth_date, timestamp): false if the value of the row is timestamp, true otherwise.

contains

Category

  • String Function

Description

  • Returns whether the Value of the specified column contains a specific string.

Function interface

  • contains(target_col, search_word)

Arguments

  • target_col: The column name to search for a string.
  • search_word: The string to search for in the column.

Return type

  • Boolean

Example

  • contains (name, ‘son’): True if name contains son. ‘Micheal Jackson’, ‘Son Heung Min’, etc.

startswith

Category

  • String Function

Description

  • Returns whether the Value of the specified column starts with a specific string.

Function interface

  • startswith(target_col, search_word)

Arguments

  • target_col: The column name to search for a string.
  • search_word: The string to search for in the column.

Return type

  • Boolean

Example

  • startswith (name, ‘kim’): True if name starts with ‘kim’. Kim Chul-soo, Kim Soo-ji, etc.

endswith

Category

  • String Function

Description

  • Returns whether the Value of the specified column ends a specific string.

Function interface

  • endswith(target_col, search_word)

Arguments

  • target_col: The column name to search for a string.
  • search_word: The string to search for in the column.

Return type

  • Boolean

Example

  • endswith (customer_code, ‘M’): True if customer_code ends with M ‘1340M’, ‘0020M’, etc.

No items found.
No items found.