In addition to Unified Functions, DLC supports Presto built-in functions.
How to Enable Presto Built-in Function Applications
Method 1: Configuring Functions for the Data Engine in Data Exploration
1. Log in to the DLC Console and select the service region.
2. Enter Data Exploration and select the data engine. When the engine kernel is Presto, you can select the parameter USEHIVEFUNCTION in the advanced configuration. Set this parameter to false to use Presto built-in functions for SQL tasks with this data engine.
Caution
In the current query session, all query tasks using this data engine can use Presto built-in functions.
Method 2: Adding Parameters in the SQL Statement
If you want a specific SQL task to call Presto built-in functions, you can add configuration information in the SQL task.
Example:
Return the binary value by concatenating b1, ..., bN.
substr(b, start)
varbinary
Return the binary value extracted from b starting at position start. If start > 0, the extraction starts from the beginning; if start < 0, the extraction starts from the end.
substr(b, start, length)
varbinary
Return the binary value of length length extracted from b starting at position start. If start > 0, the extraction starts from the beginning; if start < 0, the extraction starts from the end.
to_base64(b)
varchar
Convert binary data b to a base64 string.
from_base64(string)
varbinary
Convert a base64 encoded string to binary data.
to_base64url(b)
varchar
Use URL-safe characters to convert binary data b to a base64 string.
from_base64url(string)
varbinary
Use URL-safe characters to convert a base64 encoded string to binary data.
from_base32(string)
varbinary
Convert a base32 encoded string to binary data.
to_base32(b)
varchar
Convert binary data b to a base32 string.
to_hex(b)
varchar
Convert binary data b to a hexadecimal string.
from_hex(string)
varbinary
Convert a hexadecimal encoded string to binary data.
lpad(b, size, padb)
varbinary
Return binary data with padb concatenated to the left of b until the length reaches size.
rpad(b, size, padb)
varbinary
Return binary data with padb concatenated to the right of b until the length reaches size.
crc32(b)
bigint
Calculate the cyclic redundancy check value of the expression using the CRC32 algorithm.
md5(b)
varbinary
Calculate the MD5 value of binary b.
sha1(b)
varbinary
Calculate the SHA-1 value of binary b.
sha256(b)
varbinary
Calculate the SHA-256 value of binary b.
sha512(b)
varbinary
Calculate the SHA-512 value of binary b.
xxhash64(b)
varbinary
Calculate the XXHash64 value of binary b.
Bitwise Functions
Function Name
Return Value
Function Feature Description
bit_count(x, bits)
bigint
Return the position of the bit in x.
bitwise_and(x, y)
bigint
Return the bitwise AND of x and y.
bitwise_not(x)
bigint
Return the bitwise NOT of x.
bitwise_or(x, y)
bigint
Return the bitwise OR of x and y.
bitwise_xor(x, y)
bigint
Return the bitwise XOR of x and y.
bitwise_left_shift(value, shift)
Same as input
Return the value of value left-shifted by shift bits.
For example: bitwise_left_shift(TINYINT '7', 2) --> 28
bitwise_right_shift(value, shift)
Same as input
Return the value of value right-shifted by shift bits.
For example: bitwise_right_shift(TINYINT '7', 2) -->1
String Functions
Function Name
Return Value
Function Feature Description
chr(n)
varchar
Return the Unicode character for n.
codepoint(string)
integer
Return the Unicode value of the string.
For example: codepoint('0') -->48
concat(string1, ..., stringN)
varchar
Return the concatenated string of string1, ..., stringN.
length(string)
bigint
Return the length of the string.
hamming_distance(string1, string2)
bigint
Return the number of differing characters at corresponding positions in two strings of the same length.
For example: hamming_distance(abc, art) -->2
levenshtein_distance(string1, string2)
bigint
Return the minimum number of edits (insertions, deletions, substitutions) required to convert one string into another.
For example: levenshtein_distance('ab', 'abcde') -->3
lower(string)
varchar
Return the string converted to lowercase.
upper(string)
varchar
Return the string converted to uppercase.
ltrim(string)
varchar
Delete leading spaces from the string.
rtrim(string)
varchar
Return the string with trailing spaces deleted.
trim(string)
varchar
Return the string with all spaces deleted.
replace(string, search)
varchar
Delete all instances of the search character from the string.
replace(string, search, replace)
varchar
Replace the search string with the replace string.
For example: replace('abcavc', ‘a', ‘c') -->'cbccvc'
reverse(string)
varchar
Return the string with its characters in reverse order.
For example: reverse('abc') -->'cba'
lpad(string, size, padstring)
varchar
Concatenate the left side of the string with a padstring until the string reaches the specified size. If size is less than the length of the string, the string is truncated to the specified size.
rpad(string, size, padstring)
varchar
Concatenate the right side of the string with a padstring until the string reaches the specified size. If size is less than the length of the string, the string is truncated to the specified size.
split(string, delimiter)
array
Return an array of substrings split by the delimiter.
split(string, delimiter, limit)
array
Return an array of substrings split by the delimiter with a limit on the number of splits. If limit > 0, the last element contains the rest of the string.
For example: select split('ab-cd-efg', '-', 2) -->['ab','cd-efg']
split_part(string, delimiter, index)
varchar
Return the substring at the specified index from the array split by delimiter. The index starts at 1. If index > array length, return null.
For example: select split_part('ab-cd-efg', '-', 2) -->'cd'
Return a map obtained by splitting the string using entryDelimiter and keyValueDelimiter. If there are duplicate keys, the value for each key is determined by the specified function.
For example: split_to_map('a:1,b:2;a:3',‘,' , ':’, (k, v1, v2) -> v1)-->{'a':'1','b':'2'}
substr(string, start)
varchar
Return the substring from the specified start position in the string. The position starts at 1. If start <0, counting starts from the end of the string.
substr(string, start, length)
varchar
Return the substring of the specified length starting from the start position in the string. The position starts at 1. If start <0, counting starts from the end of the string.
position(substring IN string)
bigint
Return the position of the first occurrence of substring in the string. The position starts at 1. If the position is not found, 0 is returned.
strpos(string, substring)
bigint
Return the position of the first occurrence of substring in the string from left to right. The position starts at 1. If the position is not found, 0 is returned.
For example: strpos('abcdefg', 'a') -->1
strpos(string, substring, instance)
bigint
Return the position of the instance-th occurrence of substring in the string from left to right. If instance >0, the position starts at 1. If the position is not found, 0 is returned.
For example: strpos('abcaefg', 'ab',2) -->0
strrpos(string, substring)
bigint
Return the position of the first occurrence of substring in the string from right to left. The position starts at 1. If the position is not found, 0 is returned.
For example: strpos('abcdefg', 'a') -->7
strrpos(string, substring, instance)
bigint
Return the position of the instance-th occurrence of substring in the string from right to left. If instance >0, the position starts at 1. If the position is not found, 0 is returned.
For example: strpos('abcaefg', 'a',2) -->0
to_utf8(string)
varbinary
Convert the string to a UTF-8 encoded binary number.
from_utf8(binary)
varchar
Convert the binary number to a UTF-8 encoded string, replacing invalid characters with the Unicode character U+FFFD.
from_utf8(binary, replace)
varchar
Convert the binary number to a UTF-8 encoded string, replacing invalid characters with the replace.
Date and Time Functions
Function Name
Return Value
Function Feature Description
current_date
date
Return the current date.
current_time
time
Return the current time with time zone.
current_timestamp
timestamp
Return the current timestamp with time zone.
current_timezone()
varchar
Return the current time zone.
date(x)
date
Return the current date.
last_day_of_month(x)
date
Return the last day of the current month.
from_unixtime(unixtime)
timestamp
Return the Unix timestamp.
For example: from_unixtime(1475996660) -->2016-10-09 15:04:20.000
to_unixtime(timestamp)
double
Return the Unix timestamp of the timestamp.
from_unixtime(unixtime, string)
timestamp
Return the timestamp from Unixtime, with the string specifying the time zone.
For example: from_unixtime(1617256617,'Asia/Shanghai') -->2021-04-01 13:56:57.000 Asia/Shanghai
from_iso8601_timestamp(string)
timestamp
Convert the string to a timestamp in ISO 8601 format.
from_iso8601_date(string)
date
Convert the string to a date in ISO 8601 format.
localtime
time
Return the current time.
localtimestamp
timestamp
Return the current timestamp.
now()
timestamp
Return the current timestamp with time zone.
to_iso8601(x)
varchar
Return the ISO 8601 formatted string of x, where x can be a date, timestamp, or timestamp with time zone.
date_trunc(unit, x)
Same as input
Return the time x truncated to the specified unit, which can be second, minute, hour, day, week, month, quarter, or year.
For example: date_trunc('hour', TIMESTAMP '2020-03-17 02:09:30') --> '2020-03-17 02:00:00'
date_add(unit, value, timestamp)
Same as input
Return a new timestamp calculated by adding or subtracting the specified interval. A value >0 means adding, while a value <0 means subtracting.
The unit can be millisecond, second, minute, hour, day, week, month, quarter, or year.
For example: date_add('hour', 2, cast ('2023-07-20 20:22:22.022' as timestamp) -->'2023-07-20 22:22:22.022'
date_diff(unit, timestamp1, timestamp2)
bigint
Return the time difference between two timestamps in the specified unit. The unit can be millisecond, second, minute, hour, day, week, month, quarter, or year.
For example: date_diff('hour', cast ('2023-07-01 22:22:22' as timestamp) , cast ('2023-07-03 22:22:22' as timestamp) -->48
extract(field FROM x)
bigint
Return the time extracted from x based on the field. The field can be year, quarter, month, week, day, dow, doy, yow, hour, minute, second, timezone_hour, timezone_minute, day_of_week, day_of_year, or day_of_month.
For example: extract(year from current_date) 2023
day(x)
bigint
Return the day of the month for x.
day_of_month(x)
bigint
Return the day of the month for x.
day_of_week(x)
bigint
Return the day of the week for x, ranging from 1 to 7.
day_of_year(x)
bigint
Return the day of the year for x.
dow(x)
bigint
Return the day of the week for x. Same as day_of_week(x)
doy(x)
bigint
Return the day of the year for x. Same as day_of_year(x)
hour(x)
bigint
Return the hour of the day for x, ranging from 0 to 23.
millisecond(x)
bigint
Return the millisecond within the second for x.
minute(x)
bigint
Return the minute of the hour for x.
month(x)
bigint
Return the month of the year for x.
quarter(x)
bigint
Return the quarter of the year for x.
second(x)
bigint
Return the second of the minute for x.
week(x)
bigint
Return the week of the year for x.
week_of_year(x)
bigint
Return the week of the year for x.
year(x)
bigint
Return the year for x.
Array Functions
Function Name
Return Value
Function Feature Description
array_distinct(x)
array
Delete duplicate occurrences of the value x from the array.
array_intersect(x, y)
array
Return the distinct elements in the intersection of x and y.
array_union(x, y)
array
Return the distinct elements in the union of x and y.
array_except(x, y)
array
Return the distinct elements that are in x but not in y. (Difference)
array_join(x, delimiter, null_replacement)
varchar
Concatenate the elements of the array x using the delimiter, replacing null values with null_replacement. null_replacement is an optional character.
array_max(x)
x
Return the maximum value in the input array.
array_min(x)
x
Return the minimum value in the input array.
array_position(x, element)
bigint
Return the position (index) of the first occurrence of element in the array x (return 0 if not found).
array_remove(x, element)
array
Delete all elements in the array x that are equal to element.
array_sort(x)
array
Return the sorted array x. The elements of x must be sortable. Null elements will be placed at the end of the returned array.
array_sort(array(T), function(T, T, int))
array(T)
Return the array sorted according to the comparison function.
For example: array_sort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))) -->[5, 3, 2, 2, 1]
arrays_overlap(x, y)
Boolean value
Check if arrays x and y have any common non-null elements.
cardinality(x)
bigint
Return the cardinality (size) of the array.
concat(array1, array2, ..., arrayN)
array
Concatenate array1, array2, ... and arrayN.
contains(x, element)
Boolean value
Check if the array x contains the element
element_at(array(E)，index)
E
Return the element at the index position in the array. If index > 0, counting starts from the left; if index < 0, counting starts from the right.
filter(array(T), funciton(T, boolean))
array(T)
Return a new array consisting of elements for which the function returns true.
For example: filter( array[5, -6, NULL, 7], x -> x > 0); --> [5, 7]
repeat(element，count)
array
Count the number of times the element occurs.
reverse(x)
array
Return the array x with elements in reverse order.
sequence(start, stop, n)
array(bigint )
Generate a sequence of integers from start to stop with step size n, which is optional and defaults to 1. If start is less than or equal to stop, increment by 1; otherwise, increment by -1.
shuffle(x)
array
Shuffle the elements of the array.
slice(x, start, length)
array
Return a slice of the array x starting from start position with a length of length.
transform(array(T), function(T, U))
array(U)
Return a new array where each element T in the array is transformed to U by the function.
For example: transform(array [5, 6], x -> x + 1); -- [6, 7]
JSON Functions
Function Name
Return Value
Function Feature Description
is_json_scalar(json)
boolean
Check if a JSON is a JSON number, JSON string, true, false, or null.
For example: is_json_scalar('[1, 2, 3]') -->false
json_array_contains(json, value)
boolean
Check if a value exists in a JSON (a string containing a JSON array).
For example: json_array_contains('[1, 2, 3]', 2) -->true
json_array_length(json)
bigint
Return the length of a JSON array (a string containing a JSON array).
For example: json_array_length('[1, 2, 3]') --> 3
json_extract(json, json_path)
json
Evaluate a JSONPath-like expression json_path on a JSON (a string containing a JSON array) and return the result as JSON.
For example: json_extract('{"log":{"file":{"path":"/etc/nginx/logs/access.log"},"offset":19991212}}', '$.log.file.path')--> "/etc/nginx/logs/access.log"
json_extract_scalar(json, json_path)
varchar
Similar to json_extract(), but the result is returned as a string instead of a JSON string. The value referenced by json_path must be a boolean, number, or string.
For example: json_extract_scalar('{"log":{"file":{"path":"/etc/nginx/logs/access.log"},"offset":19991212}}', '$.log.file.path') -->/etc/nginx/logs/access.log
json_format(json)
varchar
Return JSON text serialized from the input JSON value. This is the inverse function of json_parse().
For example: json_format(JSON '[1, 2, 3]') -->[1,2,3]
json_parse(string)
json
Return a JSON value deserialized from the input JSON text. This is the inverse function of json_format().
For example: json_parse('[1, 2, 3]') -->[1,2,3]
json_size(json, json_path)
bigint
Similar to json_extract(), but the size of the value is returned. For objects or arrays, the size is the number of members. For scalar values, the size is zero.
For example: json_size('{"x": [1, 2, 3]}', '$.x') -->3
