List of Functions Supported by a Transformation Flow (in a File Space)

AVG

Returns the arithmetical mean of the expression. This function can also be used as a window function.

CORR

Computes the Pearson product momentum correlation coefficient between two columns. This function can also be used as a window function.

COUNT

Counts the number of rows returned by a query. This function can also be used as a window function.

NTH_VALUE

Returns the value of an element at a specific position in an expression. This function can also be used as a window function.

FIRST_VALUE

Returns the value of the first element of an expression. This function can also be used as a window function.

MAX

Returns the maximum value of the expression. This function can also be used as a window function.

MEDIAN

Finds the statistical median of an input expression with a numeric data type. This function can also be used as a window function.

MIN

Returns the minimum value of the expression. This function can also be used as a window function.

STDDEV

Returns the standard deviation of the given expression as the square root of the VAR function. This function can also be used as a window function.

STDDEV_POP

Returns the standard deviation of a given expression as the square root of the VAR_POP function.

STDDEV_SAMP

Returns the standard deviation of the given expression as the square root of VAR_SAMP function.

SUM

Returns the sum of the expression. This function can also be used as a window function.

VAR_POP

Returns the population variance of an expression.

VAR_SAMP

Returns the sample variance of an expression.

ABS

Returns the absolute value of a numeric argument.

ACOS

Returns the arc-cosine, in radians, of a numeric argument between -1 and 1.

ASIN

Returns the arc-sine, in radians, of a numeric argument.

ATAN

Returns the arc-tangent, in radians, of a numeric argument.

ATAN2

Returns the arc-tangent, in radians, of the ratio of two numbers.

CEIL

Returns the first integer that is greater than or equal to the specified value.

COS

Returns the cosine of the angle, in radians, for the specified argument.

COSH

Computes the hyperbolic cosine of the specified argument.

COT

Computes the cotangent of a specified number.

EXP

Returns the result of the base of the natural logarithms e raised to the power of the specified argument.

FLOOR

Returns the largest integer that is not greater than the specified numeric argument.

LN

Returns the natural logarithm of a number.

LOG

Returns the natural logarithm of a specified number and base.

MOD

Returns the remainder of a specified number divided by a specified divisor.

POWER

Calculates a specified base number raised to the power of a specified exponent.

RAND

Returns a pseudo-random DOUBLE value.

ROUND

Rounds the specified argument to the specified amount of places after the decimal point.

SIGN

Returns the sign (positive or negative) of the specified numeric argument.

SIN

Returns the sine of an angle expressed in radians.

SINH

Returns the hyperbolic sine of an angle expressed in radians.

SQRT

Returns the square root of the specified argument.

TAN

Returns the tangent of a specified number, where the argument is an angle expressed in radians.

TANH

Returns the hyperbolic tangent of the specified numeric argument.

ADD_MONTHS

Computes the specified date plus the specified number of months.

CURRENT_DATE

Returns the current local system date.

CURRENT_TIMESTAMP

Returns the current local system timestamp information.

DAYOFMONTH

Returns the day of the month for the specified date.

DAYOFYEAR

Returns an integer representation of the day of the year for the specified date.

EXTRACT

Returns the requested portion of a specified date.

HOUR

Returns an integer representation of the hour portion of the specified time.

LAST_DAY

Returns the date of the last day of the month that contains the specified date.

MINUTE

Returns an integer representation of the minute for the specified time.

MONTH

Returns the number of the month from the specified date.

MONTHS_BETWEEN

Computes the number of months between two dates.

NOW

Returns the current timestamp.

SECOND

Returns the second portion of a specified time.

WEEKDAY

Returns the day of the week for the specified date.

YEAR

Returns the year number of a specified date.

ASCII

Returns the integer ASCII value of the first character in a specified string.

CHAR

Returns the character that has the ASCII value of the specified number.

INITCAP

Converts the first character of each word in a specified string to uppercase and converts remaining characters to lowercase.

LCASE

Converts all characters in a string to lowercase.

LEFT

Returns the specified number of characters or bytes of a string, starting from the left side.

LENGTH

Returns the number of characters in a string.

LOCATE

Returns the position of a substring within a string.

LOWER

Converts all characters in a string to lowercase.

LPAD

Left-pads a string with spaces, or a specified pattern, to make a string of a specified number of characters in length.

LTRIM

Returns a string, trimmed of all leading spaces.

REPLACE

Searches within a string for all occurrences of a specified string and replaces them with another specified string.

RIGHT

Returns the specified number of characters/bytes of a string, starting from the right side.

RPAD

Right-pads a string with spaces or a specified pattern to make a string that is a specified number of characters in length.

RTRIM

Returns a string trimmed of all trailing spaces.

SOUNDEX

Converts alphabet characters into a sound code that represents their sound.

SUBSTRING

Returns a substring from an input value, starting from a specified position within the input value.

TRIM

Returns a string after removing leading and trailing spaces.

UCASE

Converts all characters in the specified string to uppercase.

UPPER

Converts all characters in a string to uppercase.

COALESCE

Returns the first non-NULL expression from a specified list.

CONCAT

Returns a combined string consisting of two specified strings.

CURRENT_SCHEMA

Returns a string containing the current schema name.

CURRENT_USER

Returns the current user name at the current statement context.

GREATEST

Returns the greatest value among the specified arguments.

GROUPING

Determines whether a specified column is used in grouping.

GROUPING_ID

Returns an integer value to identify which grouping set each row belongs to.

IFNULL

Returns the first non-NULL input expression.

LEAST

Returns the lesser value of two specified arguments.

NULLIF

Determines whether two expressions are equal.

WIDTH_BUCKET

Returns the bucket number that has been assigned to the result of a specified expression.

CAST

Returns the value of an expression converted to a supplied data type.

CUME_DIST

Returns the relative rank of a row.

DENSE_RANK

Performs the same ranking operation as the RANK function, except that rank numbering does not skip when ties are found.

LAG

Returns the value of the offset rows before the current row.

LEAD

Returns the offset of rows after the current row. The <offset> should be non-negative and its default is 1.

NTILE

Distributes rows into a specified number of buckets and assigns the bucket number starting from 1 to each row in the bucket.

PERCENT_RANK

Calculates the percentage of values that are either less than or greater than the current value, per the ORDER BY specification for the window.

RANK

Returns rank of a row within a partition, starting from 1.

ROW_NUMBER

Sequentially numbers the rows within a partition of a result set, with the first row of each partition assigned as 1.

SELECT Queries

SELECT statements only

Top-level statement must always be a SELECT query. DDL and DML statements are not supported.

FROM Clause

Tables, views, subqueries, table-valued functions

Supports fully-qualified names (catalog.db.table) and aliases.

Joins

INNER, LEFT [OUTER], RIGHT [OUTER], FULL [OUTER], CROSS, SEMI, ANTI, NATURAL

Join conditions using ON or USING clauses.

Filtering

WHERE clause with boolean expressions

Supports operators including =, <, <=, <>, IN, BETWEEN, LIKE, and IS NULL.

Sorting and Pagination

ORDER BY, SORT BY, LIMIT, OFFSET

Supports ASC/DESC sorting and NULLS FIRST/LAST positioning.

Grouping and Aggregation

GROUP BY, HAVING

Supports standard aggregate functions such as COUNT, SUM, AVG, MIN, and MAX.

Window Functions

func(...) OVER (...)

Supports PARTITION BY, ORDER BY, and ROWS/RANGE frame specifications.

Expressions

Arithmetic operators, comparisons, logical operators

Supports nested expressions with parentheses.

Functions

Built-in scalar and aggregate functions

Examples include ABS, ROUND, COALESCE, and ROW_NUMBER.

Identifiers

Unquoted or back-ticked identifiers

Literals

String, numeric, boolean, NULL

PIVOT and UNPIVOT

Pivot and unpivot operations

Transforms row data into columns and columns into rows.

Subqueries

Subqueries in FROM or WHERE clauses

Supports nested SELECT statements.

Set Operators

UNION, INTERSECT, MINUS

Combines two input relations into a single result set.

Inline Tables

VALUES clause

Creates a temporary table using a VALUES clause.

Hints

PARTITION HINTS, JOIN TYPE HINTS

Provides suggestions to Spark SQL for execution plan generation.