Spark-compatible functions in Hologres - Hologres - Alibaba Cloud Documentation Center

Starting from V3.1, Hologres supports various Spark-compatible functions by introducing the hg_spark_funcs extension. This topic describes the Spark-compatible functions in Hologres and provides usage examples.

Function	Feature
ACOSH	Calculates the inverse hyperbolic cosine.
ASINH	Calculates the inverse hyperbolic sine.
ATANH	Calculates the hyperbolic tangent.
BETWEEN	Checks whether a specified value x is within the specified range [min, max].
BIN	Calculates the binary value.
BIT_COUNT	Gets the number of bits after binary calculation.
BITWISE_AND	Performs a bitwise logical AND operation.
BITWISE_NOT	Performs a bitwise logical NOT operation.
BITWISE_OR	Performs a bitwise logical OR operation.
BITWISE_XOR	Performs a bitwise logical XOR operation.
CHECKED_ADD	Performs addition. This function reports an error when a numeric overflow occurs, instead of silently overflowing.
CHECKED_DIVIDE	Performs division. This function reports an error when a numeric overflow occurs, instead of silently overflowing.
CHECKED_MULTIPLY	Performs multiplication. This function reports an error when a numeric overflow occurs, instead of silently overflowing.
CHECKED_SUBTRACT	Performs subtraction. This function reports an error when a numeric overflow occurs, instead of silently overflowing.
CONTAINS	Checks whether parameter x contains parameter y.
CONV	Converts a string number from one base (source base) to another base (target base).
COSH	Calculates the hyperbolic cosine.
CRC32	Calculates the CRC32 hash value.
CSC	Calculates the cosecant value.
ENDSWITH	Checks whether the first input parameter ends with the second input parameter.
EXPM1	Calculates the power of Euler's number (the base of natural logarithm) minus 1, which is "exp(x) - 1".
FIND_IN_SET	Finds the position of a specified string in a comma-separated array string and returns its 1-based index value.
FROM_UNIXTIME	Converts the number of seconds (BIGINT type) since the UNIX epoch (1970-01-01 00:00:00 UTC) to the specified time format.
HEX	Converts input data to the hexadecimal format.
HYPOT	Calculates the square root of the sum of squares of two values.
IN	Checks whether a specified value exists in a specified array.
ISNAN	Checks whether data is Not a Number (NaN).
LEVENSHTEIN	Calculates the LEVENSHTEIN distance (edit distance) between two strings, representing the minimum number of single-character edit operations required to transform one string into the other.
LOG10	Calculates the base-10 logarithm of a specified value.
LOG1P	Calculates the natural logarithm of a specified value plus 1.
LOG2	Calculates the base-2 logarithm of a specified value.
MIGHT_CONTAIN	Checks whether a specified value might exist in a Bloom Filter.
MONOTONICALLY_INCREASING_ID	Generates monotonically increasing and unique 64-bit integers. These integers are not consecutive.
PMOD	Calculates the positive remainder of parameter x divided by y.
REMAINDER	Calculates the modulus (remainder) in a division operation.
RINT	Calculates the closest integer.
SEC	Calculates the secant value. This function is a type of trigonometric function, represented as the reciprocal of the cosine of an angle.
SHA1	Calculates the SHA-1 hash value of a string and converts the result to a hexadecimal string.
SHA2	Calculates the SHA-2 hash value of a string (SHA-224, SHA-256, SHA-384, and SHA-512) and converts the result to a hexadecimal string.
SHIFTLEFT	Performs a left shift on the target value.
SHIFTRIGHT	Performs a right shift on the target value.
SINH	Calculates the hyperbolic sine.
SOUNDEX	Generates the Soundex code of a string.
STARTSWITH	Checks whether the first parameter starts with the second parameter.
SUBSTRING_INDEX	Gets the part of a string before the nth occurrence of a separator.
UNHEX	Converts a hexadecimal string to VARBIT.
URL_DECODE	Unescapes the encoded values of a URL.
URL_ENCODE	Escapes through encoding.

Install the extension

Before using Spark functions, you must execute the following statement in a database as a Superuser to install the required extension. This statement only needs to be executed once for each database. If you create a new database, you must execute the statement again.

CREATE extension hg_spark_funcs;

Note

This extension can only be created in the hg_spark_funcs schema. You cannot manually specify another schema.

Sample data

The Spark function parameter x in this topic does not support constant input parameters. All examples in this topic use the following sample data.

CREATE TABLE public.spark_test(a INT);
INSERT INTO public.spark_test VALUES (3);

Spark functions

You can call functions in the hg_spark_funcs schema in one of the following two ways. Method 1 is recommended.

Method 1: Explicitly specify the schema (Recommended)
Call the function by adding the hg_spark_funcs. prefix to its name.
```
SELECT hg_spark_funcs.ACOSH(a) FROM public.spark_test;
```

Method 2: Set the search path
Add hg_spark_funcs to the search path of the current session. Then, you can call the function directly without a prefix.

-- 1. Add hg_spark_funcs to the search path.
SET search_path = 'hg_spark_funcs';
-- 2. Call the function directly.
SELECT ACOSH(a) FROM public.spark_test;

Note

For more information about Spark compatible functions, see Spark functions.