Get help for ARIS Process Mining functions and services

ABS

abs(expr) - Calculates and returns the absolute value of the numeric expression (expr ) argument.

Example

abs(-5) = 5

BIGINT

bigint(expr) - Casts the value expr to the target data type bigint (also known as long).

Example

bigint('2') = 2

BROUND

bround(expr, d) - Rounds the value of expr to d decimal places and returns the result. The half-even method is used for rounding.

Examples

bround(2.1) = 2

bround(2.5) = 2

bround(2.9) = 3

CEIL

ceil(expr) - Returns the smallest double value that is greater than or equal to the argument and equal to a mathematical integer.

Examples

ceil(2.1) = 3, ceil(2.9) = 3, ceil(2.0) = 2

COALESCE

coalesce(expr1, expr2, ...) - Returns the first non-null argument if exists. Otherwise, null.

Example

SELECT coalesce(NULL, 1, NULL);

CONCAT

concat(strExpr1, strExpr2, ... strExprN) - Concatenates the string arguments to one single string and returns it.

Examples

concat('abc', 'def') = 'abcdef'

concat('abc', 'def', 'ghi') = 'abcdefghi'

COUNT

count - Returns the number of rows.

Example

select count(*) from FRUIT_AND_VEGETABLE - Returns the number of rows in the table FRUIT_AND_VEGETABLE (here 7)

If it is used with a group by clause, it returns the number of rows in each group:

select count(*) from fruit_and_vegetable group by color - Returns the number of rows with equal value in the color column : (here red | 2, yellow | 3, green | 1, orange | 1)

DATE_FORMAT

date_format - Converts a timestamp into a string using a given date pattern.

For more details, please refer to the Java documentation page.

Example

date_format('2021-06-15', 'y') = 2021

DIV

div - Is used for integer division (x is divided by y). An integer value is returned.

Example

7 div 2 = 3

DOUBLE

double(expr) - Returns the value of expr as a double value.

FIRST

first (expr[, isIgnoreNull]) - Returns the first value of expr for a group of rows. If isIgnoreNull is true, it returns only non-null values.

The function is non-deterministic and may return arbitrary values as it relies on the order of its input. The order of rows at runtime is inherently indeterminate unless they are ordered explicitly, even if the data is fetched directly from a source table. Unless all entries in a set of rows are completely identical, it is recommended to apply an ORDER BY clause before using this function. This ensures consistent and deterministic output.

Example

First we need to order the input explicitly:

select * from FRUIT_AND_VEGETABLE order by number_of_pieces

Assuming we have named the result ORDERED_FRUIT_AND_VEGETABLE, we can now safely use the first function.

select first(fruit_and_vegetable), color, first(number_of_pieces) from ORDERED_FRUIT_AND_VEGETABLE group by color, returns:

Tomato, red, 6

Kiwi, yellow, 7

Orange, orange, 9

Kiwi, green, 12

FLOOR

floor(expr) - Returns the largest double value that is less than or equal to the argument and equal to a mathematical integer.

Examples

floor(2.1) = 2, floor(2.9) = 2, floor(2.0) = 2

FORMAT

${format(<PLACEHOLDER>, '<pattern>')} - When using dynamic parameters or other parameters of the Date data type, such as the "Task start time". You can convert this date into any format using the format function.

For more details, please refer to the Java documentation page.

Example

Parameter placeholder: START_TIME, Pattern: 'yyyyMMdd'

${format(START_TIME, 'yyyyMMdd')}

FROM_UNIXTIME

from_unixtime(unixtime, [fmt]) - Converts the given unix timestamp to the given format and returns the result.

For more details, please refer to the Java documentation page.

Examples

from_unixtime(0) = '1970-01-01 00:00:00'

from_unixtime(0, 'yyyy-MM-dd HH:mm:ss') = '1970-01-01 00:00:00'

from_unixtime(1623934860, 'dd.MM.yyyy HH:mm:ss') = '17.06.2021 13:01:00'

LAST

last (expr[, isIgnoreNull]) - Returns the last value of expr for a group of rows. If isIgnoreNull is true, it returns only non-null values.

The function is non-deterministic and may return arbitrary values as it relies on the order of its input. The order of rows at runtime is inherently indeterminate unless they are ordered explicitly, even if the data is fetched directly from a source table. Unless all entries in a set of rows are completely identical, it is recommended to apply an ORDER BY clause before using this function. This ensures consistent and deterministic output.

Example

First we need to order our input explicitly:

select * from FRUIT_AND_VEGETABLE order by number_of_pieces

Assuming we have named the result ORDERED_FRUIT_AND_VEGETABLE, we can now safely use the last function.

select last(fruit_and_vegetable), color, last(number_of_pieces) from ORDERED_FRUIT_AND_VEGETABLE group by color, returns:

Apple, red, 10

Banana, yellow, 20

Orange, orange, 9

Kiwi, green, 12

LENGTH

length(expr) - Returns 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.

Example

SELECT length('Spark SQL ');

LOWER

lower(str) - Converts the given string to a string with only lowercase characters.

Example

lower('aBcDeF') = 'abcdef'.

LTRIM

ltrim(str) - Removes all leading spaces from the string (str).

Example

ltrim(' abc ') = 'abc '

MAX

max(expr) - Returns the maximum value of expr.

Examples

select max(Number_of_pieces) from FRUIT_AND_VEGETABLE

returns: max = 20

select color, max(Number_of_pieces) from FRUIT_AND_VEGETABLE group by color

returns: green -> max = 5, yellow -> max = 20, red-> max = 10, orange -> max = 9

MIN

min(expr) - return the min value of expr

Examples

select min(Number_of_pieces) from FRUIT_AND_VEGETABLE

returns: min = 5

select color, min(Number_of_pieces) from FRUIT_AND_VEGETABLE group by color

returns: green -> min = 5, yellow -> min = 7, red -> min = 6, orange -> min = 9

POSITION

position(substr IN str) - Returns the position of the first occurrence of substr in str.

Example

SELECT POSITION('bar' IN 'foobarbar');

REGEXP_EXTRACT

regexp_extract(str, regexp[, idx]) - Extract the first string in the str that match the regexp expression and corresponding to the regex group index.

Arguments:

REGEXP_REPLACE

regexp_replace(str, regexp, rep[, position]) - Replaces all substrings of str that match regexp with rep.

ROUND

round(expr, d) - Rounds the value of expr to d decimal places and returns the result. The half-up method it used for rounding.

Examples

round(2.1) = 2.0, round(2.5) = 3.0, round(2.9) = 3.0, round(2.0) = 2.0 .

RTRIM

rtrim(str) - Removes all trailing spaces from the string (str).

Examples

rtrim(' abc ') = ' abc'

SHA2

sha2(expr, bitLength) - Returns a checksum of SHA-2 family as a hex string of expr. SHA-224, SHA-256, SHA-384, and SHA-512 are supported. Bit length of 0 is equivalent to 256.

STRING

string(expr) - Casts the given value of expr to a string.

SUBSTR

substr(str, pos, len?) - Returns a substring of string str starting at position pos with length len or until the end of str if len is not specified.

The pos argument can be negative. In this case the starting position is determined from the right side of the string.

Examples

substr('ARIS Process Mining', 0) = 'ARIS Process Mining' (the whole string is returned)

substr('ARIS Process Mining', 6) = 'Process Mining'

substr('ARIS Process Mining', 6, 7) = 'Process'

substr('ARIS Process Mining', -6) = 'Mining'

substr('ARIS Process Mining', -14, 7) = 'Process'

substr('ARIS Process Mining', -14, 1000) = 'Process Mining'

SUBSTRING_INDEX

substring_index(str, delim, count) - Returns the substring from string str before count occurrences of the delimiter delim . If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. The function substring_index performs a case-sensitive match when searching for delim.

SUM

sum(col) - Adds all values of a group (GROUP BY) and returns the result.

Examples

select sum(Number_of_pieces) from FRUIT_AND_VEGETABLE

returns: sum = 69

select color, sum(Number_of_pieces) from FRUIT_AND_VEGETABLE group by color

returns: green -> min = 5, yellow -> min = 39, red -> min = 16, orange -> min = 9

TIMESTAMP_MICROS

timestamp_micros(micros) - Creates a timestamp from the micros argument, which represents the microseconds since the UTC epoch.

TIMESTAMP_MILLIS

timestamp_millis(millis) - Creates a timestamp from the millis argument, which represents the milliseconds since UTC epoch.

TIMESTAMP_SECONDS

timestamp_seconds(seconds) - Creates a timestamp from the seconds argument, which represents the seconds since UTC epoch.

TO_TIMESTAMP

to_timestamp(timestamp_str, fmt?) - Converts a given timestamp string (timestamp_str) to a timestamp. Optionally, you can specify a fmt pattern as second argument, which defines the format of timestamp_str. If no format is specified, the first argument must use a unique pattern, that is, usually conform to the ISO 8601 standard.

Note that only the following pattern letters are supported: GyMdDhHkKamsSzZX.

For more details, please refer to the Java documentation page.

Examples

to_timestamp('2021-06-17 15:53:00') = 06/17/2021 3:53 PM

to_timestamp('17.06.2021 15:54:00', 'dd.MM.yyyy HH:mm:ss') = 06/17/2021 3:54 PM

TRIM

trim - Removes leading and trailing spaces from a string.

Examples

trim(' abc ') = 'abc'

trim('x', 'xxabcxx') = 'abc'

trim(both 'x' from 'xxabcxx') = 'abc'

trim(leading 'x' from 'xxabcxx') = 'abcxx'

trim(trailng 'x' from 'xxabcxx') = 'xxabc'

UPPER

upper - Converts the given string to a string with only uppercase characters.

Example

upper('aBcDeF') = 'ABCDEF'

YEAR

year(date) - Returns the year component of the date/timestamp.

Example

SELECT year('2016-07-30');

MONTH

month(date) - Returns the month component of the date/timestamp.

Example

SELECT month('2016-07-30');

QUARTER

quarter(date) - Returns the quarter of the year for date, in the range 1 to 4.

Example

SELECT quarter('2016-08-31');

DAY

day(date) - Returns the day of month of the date/timestamp.

Example

SELECT day('2009-07-30');

DAYOFYEAR

dayofyear(date) - Returns the day of year of the date/timestamp.

Example

SELECT dayofyear('2016-04-09');

HOUR

hour(timestamp) - Returns the hour component of the string/timestamp.

Example

SELECT hour('2009-07-30 12:58:59');

MINUTE

minute(timestamp) - Returns the minute component of the string/timestamp.

Example

SELECT minute('2009-07-30 12:58:59');

SECOND

second(timestamp) - Returns the second component of the string/timestamp.

Example

SELECT second('2009-07-30 12:58:59');

WEEKOFYEAR

weekofyear(date) - Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.

Example

SELECT weekofyear('2008-02-20');

WEEKDAY

weekday(date) - Returns the day of the week for date/timestamp (0 = Monday, 1 = Tuesday, ..., 6 = Sunday).

Example

SELECT weekday('2009-07-30');

INNER

LEFT

LEFT OUTER

RIGHT

RIGHT OUTER

FULL

FULL OUTER

AND

AS

ASC

BETWEEN

BOTH

BY

CASE

DESC

DISTINCT

ELSE

END

ESCAPE

EXISTS

FALSE

FOR

FROM

FULL

GROUP

HAVING

IN

IS

JOIN

LEADING

LEFT

LIKE

LIMIT

NOT

NULL

ON

OR

ORDER

OUTER

RIGHT

SELECT

STRING

SUBSTR

TRAILING

TRUE

UNION

USING

WHERE