Hologres:Presto compatible functions

Function

Description

Example

Result

array_cum_sum

Outputs an array where the nth position is the sum of the first n positions of the input array. The result is null if any of the first n positions contains null.

select array_cum_sum(b) from public.presto_test;

{1,3,6,8}

array_has_duplicates

Checks if there are duplicate elements in the array.

select array_has_duplicates(b) from public.presto_test;

t

array_join

Joins array elements into a string using the given separator.

select array_join(b,';') from public.presto_test;

1;2;3;2

beta_cdf

Calculates the cumulative distribution function (CDF) of the beta distribution.

select beta_cdf(2,a,0.5) from public.presto_test;

0.6875

binomial_cdf

Calculates the CDF of the binomial distribution.

select binomial_cdf(6,0.5,a) from public.presto_test;

0.65625

bit_count

Calculates the Hamming weight (number of 1s) within the specified bit width after binary calculation.

select bit_count(a, 32) from public.presto_test;

2

bitwise_and

Performs a bitwise AND calculation.

select bitwise_and(a, 2) from public.presto_test;

2

bitwise_arithmetic_shift_right

Performs a bitwise arithmetic right shift by n bits.

select bitwise_arithmetic_shift_right(a,1) from public.presto_test;

1

bitwise_left_shift

Performs a bitwise left shift by n bits.

select bitwise_left_shift(a,1) from public.presto_test;

6

bitwise_logical_shift_right

Performs a bitwise logical right shift by n bits.

select bitwise_logical_shift_right(a,1,32) from public.presto_test;

1

bitwise_not

Performs a bitwise NOT calculation.

select bitwise_not(a) from public.presto_test;

-4

bitwise_or

Performs a bitwise OR calculation.

select bitwise_or(a, 2) from public.presto_test;

3

bitwise_right_shift

Performs a bitwise right shift by n bits.

select bitwise_right_shift(a,1) from public.presto_test;

1

bitwise_right_shift_arithmetic

Performs a bitwise arithmetic right shift by n bits.

select bitwise_right_shift_arithmetic(a,1) from public.presto_test;

1

bitwise_shift_left

Performs a bitwise left shift by n bits.

select bitwise_shift_left(a,1,32) from public.presto_test;

6

bitwise_xor

Performs a bitwise XOR calculation.

select bitwise_xor(a, 2) from public.presto_test;

1

cauchy_cdf

Calculates the CDF of the Cauchy distribution.

select beta_cdf(2,a,0.5) from public.presto_test;

0.6875

chi_squared_cdf

Calculates the CDF of the chi-squared distribution.

select chi_squared_cdf(a, 4) from public.presto_test;

0.7385358700508894

clamp

Checks if the input value is within the specified range. If it is, that value is returned. Otherwise, the nearest range value is returned.

select clamp(a,2,9) from public.presto_test;

3

codepoint

Returns the Unicode code point of a single character.

select codepoint(c) from public.presto_test;

65

cosh

Calculates the hyperbolic cosine of a given value.

select cosh(a) from public.presto_test;

10.067661995777765

crc32

Calculates the CRC32 hash value of the input data.

select crc32(bin(a)::bytea) from public.presto_test;

3596227959

ends_with

Checks if the first parameter ends with the second parameter.

select ends_with(concat(a, 'hologres'), 'gres') from public.presto_test;

t

eq

Checks if two values are equal.

select eq(c,'A') from public.presto_test;

t

f_cdf

Calculates the CDF of the F distribution.

select f_cdf(2,a,0.5) from public.presto_test;

0.350480947161671

from_base

Interprets a string as a number in the given base and returns the decimal value.

select from_base(d,2) from public.presto_test;

18

from_base64

Decodes a Base64 encoded string into its original binary form.

select from_base64(e) from public.presto_test;

\x48656c6c6f20576f726c6421

from_base64url

Decodes a Base64 encoded string into binary data using the URL-safe Base64 character set.

select from_base64url(e) from public.presto_test;

\x48656c6c6f20576f726c6421

from_big_endian_32

Decodes a 32-bit big-endian two's complement into a bigint value.

select from_big_endian_32(f::BYTEA) from public.presto_test;

1

from_big_endian_64

Decodes a 64-bit big-endian two's complement into a bigint value.

select from_big_endian_64(g::BYTEA) from public.presto_test;

1

from_hex

Decodes a hexadecimal encoded string into raw binary data.

select from_hex(h) from public.presto_test;

\x48656c6c6f

from_ieee754_32

Decodes 32-bit big-endian binary data into an IEEE 754 single-precision floating-point number.

select from_ieee754_32(i::BYTEA) from public.presto_test;

3.14159012

from_ieee754_64

Decodes 64-bit big-endian binary data into an IEEE 754 single-precision floating-point number.

select from_ieee754_64(j::BYTEA) from public.presto_test;

3.141592653589793

gamma_cdf

Calculates the CDF of the gamma distribution.

select gamma_cdf(2,a,0.5) from public.presto_test;

0.012437987627616913

gt

Returns true if x > y and false otherwise, for string comparisons.

select gt('B' ,c) from public.presto_test;

t

gte

Returns true if x ≥ y and false otherwise, for string comparisons.

select gte('A' ,c) from public.presto_test;

t

hamming_distance

Returns the number of positions where characters differ.

select hamming_distance(e,'SGVsBG7gV39ybGQh') from public.presto_test;

3

hmac_md5

Calculates the hash-based message authentication code using the MD5 algorithm and the given key for the input binary.

select hmac_md5(k::BYTEA,'secret_key') from public.presto_test;

\x52460d22ec7e402dc8c62aeda51ec920

hmac_sha1

Calculates the hash-based message authentication code using the SHA1 algorithm and the given key for the input binary.

select hmac_sha1(k::BYTEA,'secret_key') from public.presto_test;

\xf8b6b3ee753fe1d8052cf317b0b4606089c85b19

hmac_sha256

Calculates the hash-based message authentication code using the SHA256 algorithm and the given key for the input binary.

select hmac_sha256(k::BYTEA,'secret_key') from public.presto_test;

\x0f0d2e10ec2bdf21bbdf490fd103820089879277261e9aa53ce3f8ecfd46b687

hmac_sha512

Calculates the hash-based message authentication code using the SHA512 algorithm and the given key for the input binary.

select hmac_sha512(k::BYTEA,'secret_key') from public.presto_test;

\x99376d305f3c2e729e60eb1e096fc364b8564452c4089b509c5d0d6e63608a43f14643a82880f156bf7df5ce32ba6bc35e36980772f9199b1cf43793e9bbd545

inverse_beta_cdf

Calculates the inverse CDF of the beta distribution.

select inverse_beta_cdf(2,a,0.6875) from public.presto_test;

0.5

inverse_cauchy_cdf

Calculates the inverse CDF of the Cauchy distribution.

select inverse_cauchy_cdf(2,a,0.5) from public.presto_test;

2

inverse_laplace_cdf

Calculates the inverse CDF of the Laplace distribution.

select inverse_laplace_cdf(a,1,0.5) from public.presto_test;

3

inverse_normal_cdf

Calculates the inverse CDF of the normal distribution with the given mean and standard deviation.

select inverse_normal_cdf(a,1,0.5) from public.presto_test;

3

inverse_weibull_cdf

Calculates the inverse CDF of the Weibull distribution.

select inverse_weibull_cdf(2,a,0.5) from public.presto_test;

2.497663833473093

is_finite

Checks if a value is finite.

select is_finite(a) from public.presto_test;

t

is_infinite

Checks if a value is infinite.

select is_infinite(a) from public.presto_test;

f

is_json_scalar

Checks if a value is a JSON scalar value. The input value must be a JSON scalar value.

select is_json_scalar(l) from public.presto_test;

t

is_nan

Checks if a numeric value is Not a Number (NaN).

select is_nan(a) from public.presto_test;

f

json_array_contains

Checks if a value is in a JSON array.

select json_array_contains(u,2) from public.presto_test;

t

json_extract_scalar

Returns the value at the specified path from a JSON string. The input value must be a JSON scalar value.

select json_extract_scalar(m,'$[1]') from public.presto_test;

2

json_size

Calculates the size of a JSON value. The input value must be a JSON scalar value.

select json_size(n,'$.x') from public.presto_test;

2

laplace_cdf

Calculates the CDF of the Laplace distribution.

select laplace_cdf(0,1,a) from public.presto_test;

0.9751064658160681

levenshtein_distance

Returns the Levenshtein distance (edit distance, the minimum number of single-character edit operations required to transform one string into another) between two strings.

select levenshtein(a::text, 'hologres') from public.presto_test;

8

log10

Calculates the base-10 logarithm of the given value.

select log10(a) from public.presto_test;

0.47712125471966244

log2

Calculates the base-2 logarithm of the given value.

select log2(a) from public.presto_test;

1.584962500721156

lt

Checks if the first value is less than the second value.

select lt('ABCDA',c) from public.presto_test;

f

lte

Checks if the first value is less than or equal to the second value.

select lte('A',c) from public.presto_test;

t

minus

Calculates the difference between two numbers.

select minus(a,1) from public.presto_test;

2

negate

Returns the opposite number of the given value.

select negate(a) from public.presto_test;

-3

neq

Checks if two values are equal.

select neq('A',c) from public.presto_test;

f

normal_cdf

Calculates the CDF of the normal distribution with the given mean and standard deviation.

select normal_cdf(0,1,a) from public.presto_test;

0.9986501019683699

normalize

Converts a string to NFC normalized form.

select normalize(o) from public.presto_test;

café

poisson_cdf

Calculates the CDF of the Poisson distribution.

select poisson_cdf(1,a) from public.presto_test;

0.9810118431238462

regexp_split

Splits a string using a regular expression.

select regexp_split(p,',') from public.presto_test;

{"apple","banana","cherry"}

remove_nulls

Removes NULL values from an array.

select remove_nulls(b) from public.presto_test;

{1,2,3,2}

secure_rand

Returns a cryptographically secure random double-precision floating-point number.

select secure_rand() from public.presto_test;

0.22077085443234523

secure_random

Returns a cryptographically secure random double-precision floating-point number.

select secure_random() from public.presto_test;

0.3414298654539425

sha1

Calculates the SHA-1 hash value of a string and converts the result to a hexadecimal string.

select sha1(a::text::bytea) from public.spark_test;

77de68daecd823babbb58edb1c8e14d7106e83bb

spooky_hash_v2_32

Calculates the SpookyHashV2 32-bit hash value of the input binary data.

select spooky_hash_v2_32(r::BYTEA) from public.presto_test;

\x4a6ed4f7

spooky_hash_v2_64

Calculates the SpookyHashV2 64-bit hash value of the input binary data.

select spooky_hash_v2_64(r::BYTEA) from public.presto_test;

\x2d89595e4a6ed4f7

strrpos

Returns the position of the last occurrence of a substring in a string.

select STRRPOS(r,'A') from public.presto_test;

5

tanh

Calculates the hyperbolic tangent.

select tanh(a) from public.presto_test;

0.9950547536867305

to_base

Converts an integer to a string representation in the specified base.

select to_base(a, 2) from public.presto_test;

11

to_base64

Encodes binary data into a Base64 formatted string.

select to_base64(r::BYTEA) from public.presto_test;

QUJDREE=

to_base64url

Encodes binary data into a Base64 formatted string using the URL-safe character set.

select to_base64url(r::BYTEA) from public.presto_test;

QUJDREE=

to_big_endian_32

Encodes an integer as a 32-bit two's complement and stores it as binary data in big-endian order.

select to_big_endian_32(a) from public.presto_test;

\x00000003

to_big_endian_64

Encodes an integer as a 64-bit two's complement and stores it as binary data in big-endian order.

select to_big_endian_64(a) from public.presto_test;

\x0000000000000003

to_ieee754_32

Encodes a single-precision floating-point number into 32-bit IEEE 754 standard format binary data, and stores it as a 4-byte binary type in big-endian order.

select to_ieee754_32(a::REAL) from public.presto_test;

\x40400000

to_ieee754_64

Encodes a double-precision floating-point number into 64-bit IEEE 754 standard format binary data, and stores it as an 8-byte binary type in big-endian order.

select to_ieee754_64(a::FLOAT ) from public.presto_test;

\x4008000000000000

trim

Removes a character set.

select trim(r,'A') from public.presto_test;

BCD

trim_array

Removes n elements from the end of an array.

select trim_array(b,2) from public.presto_test;

{1,2}

truncate

Truncates x to n decimal places.

select truncate(t,2) from public.presto_test;

3.14

url_decode

Unescapes the encoded values of a URL.

select url_decode(url_encode(concat('www.','中文',a,'.com'))) from public.presto_test;

www.中文3.com

url_encode

Escapes values by encoding them.

select url_encode(concat('www.','中文',a,'.com')) from public.presto_test;

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

url_extract_fragment

Extracts the fragment identifier from a URL.

select url_extract_fragment(concat('https://www.example.com:',a,'080/products/shirt?color=red&size=XL#discount-section')) from public.presto_test;

discount-section

url_extract_host

Extracts the domain name from a URL.

select url_extract_host(concat('https://www.example.com:',a,'080/products/shirt?color=red&size=XL#discount-section')) from public.presto_test;

www.example.com

url_extract_parameter

Extracts the value of the first parameter named 'name' from the query string in a URL.

select url_extract_parameter(concat('https://www.example.com:',a,'080/products/shirt?color=red&size=XL#discount-section'),'color') from public.presto_test;

red

url_extract_path

Extracts the path from a URL.

select url_extract_path(concat('https://www.example.com:',a,'080/products/shirt?color=red&size=XL#discount-section')) from public.presto_test;

/products/shirt

url_extract_port

Extracts the port number from a URL.

select url_extract_port(concat('https://www.example.com:',a,'080/products/shirt?color=red&size=XL#discount-section')) from public.presto_test;

3088

url_extract_protocol

Extracts the protocol from a URL.

select url_extract_protocol(concat('https://www.example.com:',a,'080/products/shirt?color=red&size=XL#discount-section')) from public.presto_test;

https

url_extract_query

Extracts the query parameters from a URL.

select url_extract_query(concat('https://www.example.com:',a,'080/products/shirt?color=red&size=XL#discount-section')) from public.presto_test;

color=red&size=XL

weibull_cdf

Calculates the CDF of the Weibull distribution.

select weibull_cdf(2,a,1.5) from public.presto_test;

0.22119921692859512

wilson_interval_lower

Returns the lower bound of the Wilson confidence interval for a Bernoulli trial process at confidence level z.

select wilson_interval_lower(a,20,1.96) from public.presto_test;

0.05236779195949585

wilson_interval_upper

Returns the upper bound of the Wilson confidence interval for a Bernoulli trial process at confidence level z.

select wilson_interval_upper(a,20,1.96) from public.presto_test;

0.3604232958869574

word_stem

Returns the stem form of the word in the specified language, where 'en' is for English and 'zn' is for Chinese.

select word_stem(s, 'en') from public.presto_test;

run

xxhash64

Calculates the XXHash64 hash value of the input binary data and returns a 64-bit binary result.

select xxhash64(r::BYTEA) from public.presto_test;

\x616f621d4581935f