Help & Documentation>Data Lake Compute

Other Functions

Last updated: 2024-01-12 10:31:58

On this page:

FIELD

Function Syntax:
FIELD(<val> T, <val1> T, <val2> T, ...)
Supported Engine: Presto
Usage: Returns the index of 'val' in the list of val1, val2... If not found, 0 is returned.
Supports all primitive types, comparing parameters using str.equals(x). If 'val' is NULL, the return value is 0.
Return Type: Integer.
Example:
select field('world', 'say', 'hello', 'world');
 3

COALESCE

Function Syntax:

COALESCE(<expr1> T, <expr2> T)
Supported Engines: SparkSQL, Presto.
Usage: Returns the first non-null argument if one exists. Otherwise, it returns null.
Return Type: Integer.
Example:
> SELECT coalesce(NULL, 1, NULL);
 1

EXPLODE

Function Syntax:

EXPLODE(<expr> array<T>|map<K, V>)
Supported Engine: SparkSQL
Usage: Splits the elements of an 'expr' of array type into multiple rows, or splits an 'expr' of map type into multiple rows and columns. The default column name 'col' is used for array elements, or 'key' and 'value' for map elements.
Return Type: row(col T) | row(key K, value V)
Example:
SELECT explode(array(10, 20));
 10
 20

EXPLODE_OUTER

Function Syntax:

EXPLODE_OUTER(<expr> array<T>|map<K, V>)
Supported Engine: SparkSQL
Usage: Splits the elements of an 'expr' of array type into multiple rows, or splits an 'expr' of map type into multiple rows and columns. The default column name 'col' is used for array elements, or 'key' and 'value' for map elements.
Return Type: row(col T) | row(key K, value V)
Example:
SELECT explode_outer(array(10, 20));
 10
 20

GREATEST

Function Syntax:

GREATEST(<expr1> T, <expr2> T, ...>)
Supported Engines: SparkSQL, Presto.
Usage: Returns the maximum value among all parameters, skipping null values.
Return Type: T.
Example:
> SELECT greatest(10, 9, 2, 4, 3);
 10

IF

Function Syntax:

IF(<expr1> boolean, <expr2> T, <expr3> U)
Supported Engines: SparkSQL, Presto.
Usage: If the evaluation of expr1 is true, then expr2 is returned; otherwise, expr3 is returned.
Return Type: T|U
Example:
> SELECT if(1 < 2, 'a', 'b');
 a

INLINE

Function Syntax:

INLINE(a array<struct<f1:T1,...,fn:Tn>>)
Supported Engine: SparkSQL
Usage: Decomposes the structure array into a table. By default, column names such as col1, col2, etc. are used.
Return Type: row(T1, ..., Tn)
Example:
> SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
 1  a
 2  b

INLINE_OUTER

Function Syntax:

INLINE_OUTER(a array<struct<f1:T1,...,fn:Tn>>)
Supported Engine: SparkSQL
Usage: Decomposes the structure array into a table. By default, column names such as col1, col2, etc. are used.
Return Type: row(T1, ..., Tn)
Example:
> SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
 1  a
 2  b

IN

Function Syntax:

<expr1> IN(<expr2> T, <expr3> T, ...)
Supported Engines: SparkSQL, Presto.
Usage: Returns "true" if 'expr1' equals any 'exprn'.
Return Type: boolean.
Example:
> SELECT 1 in(1, 2, 3);
 true
> SELECT 1 in(2, 3, 4);
 false

ISNAN

Function Syntax:

ISNAN(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Returns true if 'expr' is NaN, otherwise returns false.
Return Type: boolean.
Example:
> SELECT isnan(cast('NaN' as double));
 true

IFNULL

Function Syntax:

IFNULL(<expr1> T, <expr2> U)
Supported Engine: SparkSQL
Usage: If 'expr1' is null, 'expr2' is returned. Otherwise, 'expr1' is returned.
Return Type: T|U
Example:
> SELECT ifnull(NULL, array('2'));
 ["2"]

ISNULL

Function Syntax:

ISNULL(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Returns true if 'expr' is null, otherwise returns false.
Return Type: boolean.
Example:
> SELECT isnull(1);
 false

ISNOTNULL

Function Syntax:

ISNOTNULL(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Returns true if 'expr' is not null, otherwise returns false.
Return Type: boolean.
Example:
> SELECT isnotnull(1);
 true

LEAST

Function Syntax:

LEAST(<expr1> T, <expr2> T, ...)
Supported Engines: SparkSQL, Presto.
Usage: Returns the minimum value among all parameters, skipping null.
Return Type: T.
Example:
> SELECT least(10, 9, 2, 4, 3);
 2

NANVL

Function Syntax:

NANVL(<expr1> T, <expr2> U)
Supported Engines: SparkSQL, Presto.
Usage: nanvl(expr1, expr2). If expr1 is not NaN, it returns expr1. Otherwise, it returns expr2.
Return Type: T|U
Example:
> SELECT nanvl(cast('NaN' as double), 123);
 123.0

NULLIF

Function Syntax:

NULLIF(<expr1> T, <expr2> U)
Supported Engines: SparkSQL, Presto.
Usage: If expr1 equals expr2, it returns null. Otherwise, it returns expr1.
Return Type: T.
Example:
> SELECT nullif(2, 2);
 NULL

NVL

Function Syntax:

NVL(<expr1> T, <expr2> U)
Supported Engines: SparkSQL, Presto.
Usage: If 'expr1' is null, 'expr2' is returned. Otherwise, 'expr1' is returned.
Return Type: T|U
Example:
> SELECT nvl(NULL, array('2'));
 ["2"]

NVL2

Function Syntax:

NVL2(<expr1> T1, <expr2> T2, <expr3> T3)
Supported Engines: SparkSQL, Presto.
Usage: If expr1 is not null, it returns expr2. Otherwise, it returns expr3.
Return Type: T2|T3
Example:
> SELECT nvl2(NULL, 2, 1);
 1

POSEXPLODE

Function Syntax:

POSEXPLODE(<expr> array<T>|map<K, V>)
Supported Engine: SparkSQL
Usage: Splits the elements of an array type 'expr' into multiple rows, or splits a map type 'expr' into multiple rows and columns. The position is indicated by the column name 'pos', the default column name 'col' is used for array elements, and 'key' and 'value' are used for map elements.
Return Type: row(pos integer, col T)|row(row integer, key K, value V)
Example:
> SELECT posexplode(array(10,20));
 0  10
 1  20

POSEXPLODE_OUTER

Function Syntax:

POSEXPLODE_OUTER(<expr> array<T>|map<K, V>)
Supported Engine: SparkSQL
Usage: Splits the elements of an array type 'expr' into multiple rows, or splits a map type 'expr' into multiple rows and columns. The position is indicated by the column name 'pos', the default column name 'col' is used for array elements, and 'key' and 'value' are used for map elements.
Return Type: row(pos integer, col T)|row(row integer, key K, value V)
Example:
> SELECT posexplode_outer(array(10,20));
 0  10
 1  20

STACK

Function Syntax:

STACK(<n> integer, <expr0> T0, ..., <expr1> T1)
Supported Engine: SparkSQL
Usage: STACK(n, expr1, ..., exprk) - Splits expr1, ..., exprk into n rows. By default, column names such as col0, col1, etc. are used.
Return Type: row(col0 T0, ..., coln Tn)
Example:
> SELECT stack(2, 1, 2, 3);
 1  2
 3  NULL

ASSERT_TRUE

Function Syntax:

ASSERT_TRUE(<expr> boolean)
Supported Engines: SparkSQL, Presto.
Usage: Throws an exception if 'expr' is not true.
Return Type: boolean.
Example:
> SELECT assert_true(0 < 1);
 NULL

RAISE_ERROR

Function Syntax:

RAISE_ERROR(<error> string)
Supported Engines: SparkSQL, Presto.
Usage: Throws an exception for 'expr'.
Return Type: string
Example:
> SELECT raise_error('custom error message');
 java.lang.RuntimeException
 custom error message

SPARK_PARTITION_ID

Function Syntax:

SPARK_PARTITION_ID()
Supported Engine: SparkSQL
Usage: Returns the current partition ID.
Return Type: Integer.
Example:
> SELECT spark_partition_id();
 0

INPUT_FILE_NAME

Function Syntax:

INPUT_FILE_NAME()
Supported Engine: SparkSQL
Usage: Returns the name of the file being read. If not available, an empty string is returned.
Return Type: string
Example:
> SELECT input_file_name();

INPUT_FILE_BLOCK_START

Function Syntax:

INPUT_FILE_BLOCK_START()
Supported Engine: SparkSQL
Usage: Returns the starting offset of the block being read. If unavailable, -1 is returned.
Return Type: Integer.
Example:
> SELECT input_file_block_start();
 -1

INPUT_FILE_BLOCK_LENGTH

Function Syntax:

INPUT_FILE_BLOCK_LENGTH()
Supported Engine: SparkSQL
Usage: Returns the length of the block being read. If unavailable, -1 is returned.
Return Type: Integer.
Example:
> SELECT input_file_block_length();
 -1

MONOTONICALLY_INCREASING_ID

Function Syntax:

MONOTONICALLY_INCREASING_ID()
Supported Engine: SparkSQL
Usage: Returns a monotonically increasing 64-bit integer. The generated ID is guaranteed to be monotonically increasing and unique, but not consecutive. The current implementation places the partition ID in the top 31 bits, with the lower 33 bits representing the record number within each partition. Assuming there are fewer than 1 billion partitions in the data frame and fewer than 8 billion records in each partition. This function is indeterminate, as its result depends on the partition ID.
Return Type: bigint
Example:
> SELECT monotonically_increasing_id();
 0

CURRENT_DATABASE

Function Syntax:

CURRENT_DATABASE()
Supported Engine: SparkSQL
Usage: Returns the current database.
Return Type: string
Example:
> SELECT current_database();
 default

CURRENT_CATALOG

Function Syntax:

CURRENT_CATALOG()
Supported Engine: SparkSQL
Usage: Returns the current catalog.
Return Type: string
Example:
> SELECT current_catalog();
 spark_catalog

CURRENT_USER

Function Syntax:

CURRENT_USER()
Supported Engines: SparkSQL, Presto.
Usage: Returns the current user.
Return Type: string
Example:
> SELECT current_user();

REFLECT

Function Syntax:

REFLECT(<class> string, <method> string[, <arg1> T1[, <arg2> T2, ...]])
Supported Engines: SparkSQL, Presto.
Usage: Calls a method with reflection.
Return Type: string
Example:
> select reflect('java.lang.Math', 'abs', -1);
 1

JAVA_METHOD

Function Syntax:

JAVA_METHOD(<class> string, <method> string[, <arg1> T1[, <arg2> T2, ...]])
Supported Engines: SparkSQL, Presto.
Usage: Calls a method with reflection.
Return Type: string
Example:
> select JAVA_METHOD('java.lang.Math', 'abs', -1);
 1

VERSION

Function Syntax:

VERSION()
Supported Engines: SparkSQL, Presto.
Usage: Returns the engine version.
Return Type: string
Example:
> select VERSION()
 3.0.0 rce61711a5fa54ab34fc74d86d521ecaeea6b072a

TYPEOF

Function Syntax:

TYPEOF(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Returns the data type of 'expr'.
Return Type: string
Example:
> SELECT typeof(1);
 int
> SELECT typeof(array(1));
 array<int>

CAST

Function Syntax:

CAST(<expr> AS <type>)
Supported Engines: SparkSQL, Presto.
Usage: Converts 'expr' into 'type' type.
Return Type: <type>
Example:
> SELECT cast('10' as int);
 10

BOOLEAN

Function Syntax:

BOOLEAN(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Converts 'expr' into a Boolean type.
Return Type: boolean.
Example:
> SELECT boolean(1);
 true

BIGINT

Function Syntax:

BIGINT(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Enforces type conversion to BIGINT.
Return Type: bigint
Example:
> select bigint(0);
 0

BINARY

Function Syntax:

BINARY(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Enforces type conversion to BINARY.
Return Type: binary.
Example:
> select binary(65);
 A

DOUBLE

Function Syntax:

DOUBLE(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Enforces type conversion to "double".
Return Type: double.
Example:
select double(1);
 1.0

FLOAT

Function Syntax:

FLOAT(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Enforces type conversion to float.
Return Type: Float.
Example:
> select float(1);
 1.0

INT

Function Syntax:

INT(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Enforces type casting to integer.
Return Type: Integer.
Example:
> select int(1.0);
 1

SMALLINT

Function Syntax:

SMALLINT(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Enforces conversion to SMALLINT type.
Return Type: Smallint.
Example:
select typeof(smallint(1));
 smallint

STRING

Function Syntax:

STRING(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Force type conversion to string.
Return Type: string
Example:
> select typeof(string(1));
 string

TINYINT

Function Syntax:

TINYINT(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Casts forcefully to type 'tinyint'.
Return Type: Tinyint.
Example:
> select typeof(tinyint(1));
 tinyint

DECIMAL

Function Syntax:
DECIMAL(<expr> T)
Supported Engines: SparkSQL, Presto.
Usage: Enforces type conversion to decimal.
Return Type: Decimal
Example:
> select typeof(decimal(1));
 decimal(10, 0)

中国站 - English