Extend Hologres with Spark-Compatible Built-in Functions (V3.1+) - Hologres

Hologres supports Spark-compatible functions starting from V3.1 through the hg_spark_funcs extension. This topic covers all supported functions with syntax, parameters, return types, and examples.

Prerequisites

Install the hg_spark_funcs extension as a Superuser before calling any function. Run the following statement once per database—repeat it for each new database you create.

CREATE extension hg_spark_funcs;

The extension can only be created in the hg_spark_funcs schema. You cannot specify a different schema.

Call functions

All functions live in the hg_spark_funcs schema. Use either method below.

Method 1 — Explicit schema prefix (recommended)

Prefix the function name with hg_spark_funcs.:

SELECT hg_spark_funcs.ACOSH(a) FROM public.spark_test;

Method 2 — Set the search path

Add hg_spark_funcs to the session's search path, then call functions without a prefix:

-- Set the search path for this session
SET search_path = 'hg_spark_funcs';
-- Call the function directly
SELECT ACOSH(a) FROM public.spark_test;

For the full Spark function specification, see Spark functions.

Sample data

All examples in this topic use the following table. The parameter x does not accept constant values—it must come from a column.

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

Supported functions

Function	Description
ACOSH	Returns the inverse hyperbolic cosine.
ASINH	Returns the inverse hyperbolic sine
ATANH	Returns the inverse hyperbolic tangent.
BETWEEN	Checks whether a value is within a specified range
BIN	Converts a number to its binary string representation
BIT_COUNT	Returns the number of 1-bits in the binary representation of a number
BITWISE_AND	Performs a bitwise AND operation
BITWISE_NOT	Performs a bitwise NOT operation
BITWISE_OR	Performs a bitwise OR operation
BITWISE_XOR	Performs a bitwise XOR operation
CHECKED_ADD	Adds two numbers and throws an error on overflow
CHECKED_DIVIDE	Divides two numbers and throws an error on overflow
CHECKED_MULTIPLY	Multiplies two numbers and throws an error on overflow
CHECKED_SUBTRACT	Subtracts two numbers and throws an error on overflow
CONTAINS	Checks whether string x contains string y
CONV	Converts a numeric string from one base to another
COSH	Returns the hyperbolic cosine
CRC32	Returns the 32-bit cyclic redundancy check (CRC32) hash value
CSC	Returns the cosecant
ENDSWITH	Checks whether string x ends with string y
EXPM1	Returns exp(x) - 1
FIND_IN_SET	Returns the 1-based position of a string within a comma-separated list
FROM_UNIXTIME	Converts seconds since the UNIX epoch to a formatted timestamp string
HEX	Converts a number to its hexadecimal string representation
HYPOT	Returns the square root of the sum of squares of two values
IN	Checks whether a value exists in an array
ISNAN	Checks whether a value is Not a Number (NaN)
LEVENSHTEIN	Returns the Levenshtein distance (edit distance) between two strings
LOG10	Returns the base-10 logarithm.
LOG1P	Returns the natural logarithm of x + 1
LOG2	Returns the base-2 logarithm.
MIGHT_CONTAIN	Checks whether a value might exist in a Bloom filter
MONOTONICALLY_INCREASING_ID	Generates monotonically increasing, unique 64-bit integers
PMOD	Returns the positive remainder of x divided by y
REMAINDER	Returns the remainder of x divided by y
RINT	Returns the closest integer to a value
SEC	Returns the secant
SHA1	Returns the SHA-1 hash as a hexadecimal string
SHA2	Returns the SHA-2 hash (SHA-224, SHA-256, SHA-384, or SHA-512) as a hexadecimal string.
SHIFTLEFT	Performs a bitwise left shift
SHIFTRIGHT	Performs a bitwise right shift
SINH	Returns the hyperbolic sine
SOUNDEX	Returns the Soundex code of a string
STARTSWITH	Checks whether string x starts with string y
SUBSTRING_INDEX	Returns the substring before the nth occurrence of a separator.
UNHEX	Converts a hexadecimal string to its binary representation
URL_DECODE	Decodes a URL-encoded string
URL_ENCODE	Encodes a string into URL-safe format

Function reference

ACOSH

Returns the inverse hyperbolic cosine of x.

Syntax

ACOSH(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.ACOSH(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT ACOSH(a) FROM public.spark_test;

Result:

acosh
------------------
1.762747174039086

ASINH

Returns the inverse hyperbolic sine of x.

Syntax

ASINH(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.ASINH(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT ASINH(a) FROM public.spark_test;

Result:

      asinh
------------------
 1.8184464592320668

ATANH

Returns the inverse hyperbolic tangent of x.

Syntax

ATANH(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.ATANH(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT ATANH(a) FROM public.spark_test;

Result:

 atanh
-------
   NaN

BETWEEN

Checks whether x is within the closed interval [min, max]. Returns true if min <= x <= max, otherwise false.

BETWEEN is a Hologres keyword. Always use the hg_spark_funcs. prefix when calling this function.

Syntax

hg_spark_funcs.BETWEEN(x, min, max)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The value to check.
`min`	BIGINT	Required. The lower bound of the range (inclusive).
`max`	BIGINT	Required. The upper bound of the range (inclusive).

Return value

BOOLEAN.

Example

SELECT hg_spark_funcs.BETWEEN(a, 2, 4) FROM public.spark_test;

Result:

 between
---------
 t

BIN

Converts a number to its binary string representation.

Syntax

BIN(x)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The number to convert.

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.BIN(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT BIN(a) FROM public.spark_test;

Result:

 bin
-----
 11

BIT_COUNT

Returns the number of 1-bits in the binary representation of a number.

Syntax

BIT_COUNT(x)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The input number.

Return value

INTEGER.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.BIT_COUNT(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT BIT_COUNT(a) FROM public.spark_test;

Result (3 in binary is 11, which has two 1-bits):

 bit_count
-----------
         2

BITWISE_AND

Performs a bitwise AND on x and y.

Syntax

BITWISE_AND(x, y)

Parameters

Parameter	Type	Description
`x`	INTEGER	Required. The first operand.
`y`	INTEGER	Required. The second operand.

Return value

INTEGER.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.BITWISE_AND(a, 2) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT BITWISE_AND(a, 2) FROM public.spark_test;

Result:

 bitwise_and
-------------
           2

BITWISE_NOT

Performs a bitwise NOT on x.

Syntax

BITWISE_NOT(x)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The input value.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.BITWISE_NOT(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT BITWISE_NOT(a) FROM public.spark_test;

Result:

 bitwise_not
-------------
          -4

BITWISE_OR

Performs a bitwise OR on x and y.

Syntax

BITWISE_OR(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The first operand.
`y`	BIGINT	Required. The second operand.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.BITWISE_OR(a, 2) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT BITWISE_OR(a, 2) FROM public.spark_test;

Result:

 bitwise_or
------------
          3

BITWISE_XOR

Performs a bitwise XOR on x and y.

Syntax

BITWISE_XOR(x, y)

Parameters

Parameter	Type	Description
`x`	INTEGER	Required. The first operand.
`y`	INTEGER	Required. The second operand.

Return value

INTEGER.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.BITWISE_XOR(a, 2) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT BITWISE_XOR(a, 2) FROM public.spark_test;

Result:

 bitwise_xor
-------------
           1

CHECKED_ADD

Adds x and y. Throws an error if the result overflows, instead of silently wrapping around.

Syntax

CHECKED_ADD(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The first operand.
`y`	BIGINT	Required. The second operand.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.CHECKED_ADD(a, 2147483647) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT CHECKED_ADD(a, 2147483647) FROM public.spark_test;

Result (overflow triggers an error):

ERROR:  internal error: Run func: spark_checked_add fails with Exception: VeloxUserError

CHECKED_DIVIDE

Divides x by y. Throws an error if the result overflows, instead of silently wrapping around.

Syntax

CHECKED_DIVIDE(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The dividend.
`y`	BIGINT	Required. The divisor.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.CHECKED_DIVIDE(a, 1/2147483647) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT CHECKED_DIVIDE(a, 1/2147483647) FROM public.spark_test;

Result (overflow triggers an error):

ERROR:  internal error: Run func: spark_checked_divide fails with Exception: VeloxUserError

CHECKED_MULTIPLY

Multiplies x and y. Throws an error if the result overflows, instead of silently wrapping around.

Syntax

CHECKED_MULTIPLY(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The first operand.
`y`	BIGINT	Required. The second operand.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.CHECKED_MULTIPLY(a, 2147483647) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT CHECKED_MULTIPLY(a, 2147483647) FROM public.spark_test;

Result (overflow triggers an error):

ERROR:  internal error: Run func: spark_checked_multiply fails with Exception: VeloxUserError

CHECKED_SUBTRACT

Subtracts y from x. Throws an error if the result overflows, instead of silently wrapping around.

Syntax

CHECKED_SUBTRACT(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The minuend.
`y`	BIGINT	Required. The subtrahend.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.CHECKED_SUBTRACT(a, -2147483647) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT CHECKED_SUBTRACT(a, -2147483647) FROM public.spark_test;

Result (overflow triggers an error):

ERROR:  internal error: Run func: spark_checked_subtract fails with Exception: VeloxUserError

CONTAINS

Checks whether string x contains string y. Returns true if it does, otherwise false.

Syntax

CONTAINS(x, y)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The string to search within.
`y`	TEXT	Required. The substring to search for.

Return value

BOOLEAN.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.CONTAINS(a::text, '3') FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT CONTAINS(a::text, '3') FROM public.spark_test;

Result:

 contains
----------
 t

CONV

Converts a numeric string from one base to another.

Syntax

CONV(x, n, m)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The number to convert, represented as a string.
`n`	INTEGER	Required. The source base (for example, `10` for decimal, `2` for binary).
`m`	INTEGER	Required. The target base.

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.CONV(a::text, 10, 2) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT CONV(a::text, 10, 2) FROM public.spark_test;

Result (converts 3 from base 10 to base 2):

 conv
------
 11

COSH

Returns the hyperbolic cosine of x.

Syntax

COSH(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.COSH(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT COSH(a) FROM public.spark_test;

Result:

       cosh
------------------
 10.0676619957778

CRC32

Returns the 32-bit cyclic redundancy check (CRC32) hash value of a binary string.

Syntax

CRC32(x)

Parameters

Parameter	Type	Description
`x`	BYTEA	Required. The binary string to hash.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.CRC32(hg_spark_funcs.bin(a)::bytea) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT CRC32(bin(a)::bytea) FROM public.spark_test;

Result:

   crc32
------------
 3596227959

CSC

Returns the cosecant of x.

Syntax

CSC(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value in radians.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.CSC(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT CSC(a) FROM public.spark_test;

Result:

       csc
------------------
 7.08616739573719

ENDSWITH

Checks whether string x ends with string y. Returns true if it does, otherwise false.

Syntax

ENDSWITH(x, y)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The string to check.
`y`	TEXT	Required. The suffix to look for.

Return value

BOOLEAN.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.ENDSWITH(concat(a, 'hologres'), 'gres') FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT ENDSWITH(concat(a, 'hologres'), 'gres') FROM public.spark_test;

Result:

 endswith
----------
 t

EXPM1

Returns exp(x) - 1, where exp(x) is Euler's number raised to the power of x.

Syntax

EXPM1(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The exponent.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.EXPM1(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT EXPM1(a) FROM public.spark_test;

Result:

      expm1
------------------
 19.0855369231877

FIND_IN_SET

Returns the 1-based position of string x within the comma-separated list y. Returns 0 if x is not found.

Syntax

FIND_IN_SET(x, y)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The string to search for.
`y`	TEXT	Required. A comma-separated list of strings.

Return value

INTEGER.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.FIND_IN_SET(a::text, 'a,b,c,2') FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT FIND_IN_SET(a::text, 'a,b,c,2') FROM public.spark_test;

Result:

 find_in_set
-------------
           4

FROM_UNIXTIME

Converts a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) to a formatted timestamp string.

Syntax

FROM_UNIXTIME(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The number of seconds elapsed since the UNIX epoch (1970-01-01 00:00:00 UTC).
`y`	TEXT	Required. The output format string. Uses Spark datetime pattern syntax (for example, `'yyyy-MM-dd HH:mm:ss'`).

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.FROM_UNIXTIME(a, 'yyyy-MM-dd HH:mm:ss') FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT FROM_UNIXTIME(a, 'yyyy-MM-dd HH:mm:ss') FROM public.spark_test;

Result:

    from_unixtime
---------------------
 1970-01-01 00:00:03

HEX

Converts a number to its hexadecimal string representation.

Syntax

HEX(x)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The number to convert.

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.HEX(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT HEX(a) FROM public.spark_test;

Result:

 hex
-----
 3

HYPOT

Returns the length of the hypotenuse of a right triangle with legs x and y, computed as sqrt(x² + y²).

Syntax

HYPOT(x, y)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The first leg.
`y`	DOUBLE PRECISION	Required. The second leg.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.HYPOT(a, 4) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT HYPOT(a, 4) FROM public.spark_test;

Result (hypot(3, 4) = 5):

 hypot
-------
     5

IN

Checks whether value x exists in array y. Returns true if found, otherwise false.

Syntax

IN(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The value to look for.
`y`	BIGINT[]	Required. The array to search in.

Return value

BOOLEAN.

Example

SELECT hg_spark_funcs.IN(a, ARRAY[2,5,3]) FROM public.spark_test;

Result:

 in
----
 t

ISNAN

Checks whether x is Not a Number (NaN). Returns true if it is NaN, otherwise false.

Syntax

ISNAN(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The value to check.

Return value

BOOLEAN.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.ISNAN(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT ISNAN(a) FROM public.spark_test;

Result:

 isnan
-------
 f

LEVENSHTEIN

Returns the Levenshtein distance (edit distance) between strings x and y—the minimum number of single-character insertions, deletions, or substitutions needed to transform one string into the other.

Syntax

LEVENSHTEIN(x, y)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The first string.
`y`	TEXT	Required. The second string.

Return value

INTEGER.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.LEVENSHTEIN(a::text, 'hologres') FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT LEVENSHTEIN(a::text, 'hologres') FROM public.spark_test;

Result:

 levenshtein
-------------
           8

LOG10

Returns the base-10 logarithm of x.

Syntax

LOG10(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.LOG10(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT LOG10(a) FROM public.spark_test;

Result:

       log10
-------------------
 0.477121254719662

LOG1P

Returns the natural logarithm of x + 1.

Syntax

LOG1P(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.LOG1P(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT LOG1P(a) FROM public.spark_test;

Result:

      log1p
------------------
 1.38629436111989

LOG2

Returns the base-2 logarithm of x.

Syntax

LOG2(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.LOG2(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT LOG2(a) FROM public.spark_test;

Result:

       log2
------------------
 1.58496250072116

MIGHT_CONTAIN

Checks whether value y might exist in the Bloom filter serialized as x. Returns true if the value might be present (possible false positive), false if definitely absent.

Syntax

MIGHT_CONTAIN(x, y)

Parameters

Parameter	Type	Description
`x`	BYTEA	Required. The serialized bytes of a Bloom filter.
`y`	BIGINT	Required. The value to look up in the Bloom filter.

Return value

BOOLEAN.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.MIGHT_CONTAIN(a::text::bytea, 3) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT MIGHT_CONTAIN(a::text::bytea, 3) FROM public.spark_test;

Result:

 might_contain
---------------
 f

MONOTONICALLY_INCREASING_ID

Generates monotonically increasing, unique 64-bit integers. The generated IDs are guaranteed to be monotonically increasing and unique across rows, but are not consecutive.

Syntax

MONOTONICALLY_INCREASING_ID()

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.MONOTONICALLY_INCREASING_ID() FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT MONOTONICALLY_INCREASING_ID() FROM public.spark_test;

Result:

 monotonically_increasing_id
-----------------------------
                           0

PMOD

Returns the positive remainder of x divided by y. Unlike the standard modulo operator, the result is always non-negative.

Syntax

PMOD(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The dividend.
`y`	BIGINT	Required. The divisor.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.PMOD(a, 2) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT PMOD(a, 2) FROM public.spark_test;

Result:

 pmod
------
    1

REMAINDER

Returns the remainder of x divided by y.

Syntax

REMAINDER(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The dividend.
`y`	BIGINT	Required. The divisor.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.REMAINDER(a, 2) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT REMAINDER(a, 2) FROM public.spark_test;

Result:

 remainder
-----------
         1

RINT

Returns the closest integer to x.

Syntax

RINT(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.RINT(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT RINT(a) FROM public.spark_test;

Result:

 rint
------
    3

SEC

Returns the secant of x (the reciprocal of the cosine).

Syntax

SEC(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value in radians.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.SEC(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT SEC(a) FROM public.spark_test;

Result:

        sec
-------------------
 -1.01010866590799

SHA1

Returns the Secure Hash Algorithm 1 (SHA-1) hash of binary string x as a hexadecimal string.

Syntax

SHA1(x)

Parameters

Parameter	Type	Description
`x`	BYTEA	Required. The binary string to hash.

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.SHA1(a::text::bytea) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT SHA1(a::text::bytea) FROM public.spark_test;

Result:

                   sha1
------------------------------------------
 77de68daecd823babbb58edb1c8e14d7106e83bb

SHA2

Returns the SHA-2 hash of binary string x as a hexadecimal string. Returns NULL for unsupported bit lengths.

Syntax

SHA2(x, y)

Parameters

Parameter	Type	Description
`x`	BYTEA	Required. The binary string to hash.
`y`	INTEGER	Required. The hash bit length. Supported values: `224`, `256`, `384`, `512`. Returns NULL for any other value.

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.SHA2(a::text::bytea, 3) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT SHA2(a::text::bytea, 3) FROM public.spark_test;

Result:

sha2
------
NaN

SHIFTLEFT

Shifts x left by y bits.

Syntax

SHIFTLEFT(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The value to shift.
`y`	INTEGER	Required. The number of bit positions to shift left.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.SHIFTLEFT(a, 1) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT SHIFTLEFT(a, 1) FROM public.spark_test;

Result:

 shiftleft
-----------
         6

SHIFTRIGHT

Shifts x right by y bits.

Syntax

SHIFTRIGHT(x, y)

Parameters

Parameter	Type	Description
`x`	BIGINT	Required. The value to shift.
`y`	INTEGER	Required. The number of bit positions to shift right.

Return value

BIGINT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.SHIFTRIGHT(a, 1) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT SHIFTRIGHT(a, 1) FROM public.spark_test;

Result:

 shiftright
------------
          1

SINH

Returns the hyperbolic sine of x.

Syntax

SINH(x)

Parameters

Parameter	Type	Description
`x`	DOUBLE PRECISION	Required. The input value.

Return value

DOUBLE PRECISION.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.SINH(a) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT SINH(a) FROM public.spark_test;

Result:

       sinh
------------------
 10.0178749274099

SOUNDEX

Returns the Soundex code of string x. The Soundex algorithm maps phonetically similar English words to the same code, making it useful for fuzzy name matching.

Syntax

SOUNDEX(x)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The input string.

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.SOUNDEX(concat('holo', a)) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT SOUNDEX(concat('holo', a)) FROM public.spark_test;

Result:

 soundex
---------
 H400

STARTSWITH

Checks whether string x starts with string y. Returns true if it does, otherwise false.

Syntax

STARTSWITH(x, y)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The string to check.
`y`	TEXT	Required. The prefix to look for.

Return value

BOOLEAN.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.STARTSWITH(concat(a, 'hologres'), 'gres') FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT STARTSWITH(concat(a, 'hologres'), 'gres') FROM public.spark_test;

Result:

 startswith
------------
 f

SUBSTRING_INDEX

Returns the substring of x before the nth occurrence of separator y. If n is positive, returns everything to the left of the nth delimiter.

Syntax

SUBSTRING_INDEX(x, y, n)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The source string.
`y`	TEXT	Required. The separator to search for.
`n`	INTEGER	Required. The occurrence count. Positive values count from the left.

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.SUBSTRING_INDEX(concat(a, 'hologres'), 'o', 2) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT SUBSTRING_INDEX(concat(a, 'hologres'), 'o', 2) FROM public.spark_test;

Result (returns the substring before the 2nd 'o' in '3hologres'):

 substring_index
-----------------
 3hol

UNHEX

Converts a hexadecimal string to its binary representation.

Syntax

UNHEX(x)

Parameters

Parameter	Type	Description
`x`	TEXT	Required. The hexadecimal string to convert.

Return value

TEXT.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.UNHEX(a::text) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT UNHEX(a::text) FROM public.spark_test;

Result:

 unhex
-------
 \x03

URL_DECODE

Decodes a URL-encoded string by replacing percent-encoded sequences with their corresponding characters.

Syntax

URL_DECODE(x)

Parameters

Parameter	Type	Description
`x`	VARCHAR	Required. The URL-encoded string to decode.

Return value

VARCHAR.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.URL_DECODE(hg_spark_funcs.URL_ENCODE(CONCAT('www.','Chinese',a,'.com'))) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT URL_DECODE(URL_ENCODE(CONCAT('www.','Chinese',a,'.com'))) FROM public.spark_test;

Result:

 url_decode
---------------
 www.chinese3.com

URL_ENCODE

Encodes a string into URL-safe format by replacing special characters with percent-encoded sequences.

Syntax

URL_ENCODE(x)

Parameters

Parameter	Type	Description
`x`	VARCHAR	Required. The string to encode.

Return value

VARCHAR.

Example

-- Method 1: Explicit schema prefix (recommended)
SELECT hg_spark_funcs.URL_ENCODE(CONCAT('www.','Chinese',a,'.com')) FROM public.spark_test;

-- Method 2: Set the search path
SET search_path = 'hg_spark_funcs';
SELECT URL_ENCODE(CONCAT('www.','Chinese',a,'.com')) FROM public.spark_test;

Result:

        url_encode
-----------------------------
 www.%E4%B8%AD%E6%96%873.com