tencent cloud

All product documents
Data Lake Compute
DocumentationData Lake ComputeSQL StatementSuperSQL StatementFunctionsComparison of Hive Functions
Comparison of Hive Functions
Download PDF
Last updated: 2025-03-07 15:52:31
Comparison of Hive Functions
Last updated: 2025-03-07 15:52:31
Download PDF
DLC has comprehensive support for Hive functions, allowing you to easily upgrade from Hive to DLC and take advantage of more powerful data lake features.
There are some slight differences between DLC unified functions and Hive functions. The specific function comparison table is as follows:
For details on Hive functions, see the Apache Hive official website LanguageManual UDF.

Mathematical Functions

For Hive mathematical functions, see the Apache Hive official website MathematicalFunctions.
For DLC mathematical functions, see the Tencent Cloud official website documentation Mathematical Functions.
Hive 3.1 Function Name
Function Feature Description
DLC Function Name
Difference Description
Usage Reference
round
Rounding to the nearest integer
No differences
select round(1.23);
round
Rounding to a specified number of decimal places
No differences
select round(1.234,2);
bround
Rounding in HALF_EVEN mode
In the DLC function, bround requires two parameters, with the second parameter specifying the number of decimal places to retain. Setting the second parameter to 0 has the same effect as bround(double a) in Hive.
select bround(1.237,0);
bround
Rounding in HALF_EVEN mode, retaining the specified number of decimal places
No differences
select bround(1.237,2);
floor
Rounding down
No differences
select floor(1.23);
ceil, ceiling
Rounding up
No differences
select ceil(1.93);
select ceiling(1.13);
rand
Return a random number between 0 and 1. If a seed is specified, it returns a stable sequence of random numbers.
No differences
select rand();
select rand(5);
exp
e raised to the power of a
No differences
select exp(1);
select exp(2.4);
ln
Natural logarithm function
No differences
select ln(2.4);
log10
Base-10 logarithm function
No differences
select log10(2.4);
log2
Base-2 logarithm function
No differences
select log2(2.4);
log
Logarithm function
No differences
select log(2,4);
pow, power
Power function
No differences
select pow(2,4);
sqrt
Square root function
No differences
select sqrt(4);
bin
Binary function
No differences
select bin(4);
hex
Hexadecimal function
No differences
select hex(10);
unhex
Hexadecimal to string conversion
The result needs to be decoded in DLC Spark functions.
select decode(unhex('3141'), 'UTF-8');
conv
Base conversion
No differences
select conv(2,10,2);
abs
Absolute value
No differences
select abs(-1);
pmod
Modulus
No differences
select pmod(5,3);
sin
Trigonometric function sin, with a in radians
No differences
select sin(3.14);
asin
Trigonometric function arc sin, with a in radians
No differences
select asin(0.5);
cos
Trigonometric function cos, with a in radians
No differences
select cos(3.14);
acos
Trigonometric function arc cos, with a in radians
No differences
select acos(1);
tan
Trigonometric function tan, with a in radians
No differences
select tan(3.14);
atan
Trigonometric function arc tan, with a in radians
No differences
select atan(1);
degrees
Radians to degrees
No differences
select degrees(3.14);
radians
Degrees to radians
No differences
select radians(180);
positive
Return a.
No differences
select positive(-1);
negative
Return -a.
negative
No differences
select negative(1);
sign
If a is positive, it returns 1.0; if a is negative, it returns -1.0.
If a is 0, then it returns 0.0.
No differences
select sign(1.12);
e
Return the natural constant e.
No differences
select e();
pi
Return the value of pi.
No differences
select pi();
factorial
Factorial
No differences
select factorial(5);
cbrt
Cube root
No differences
select cbrt(27);
shiftleft
Left shift
No differences
select shiftleft(3,1);
shiftright
Right shift
No differences
select shiftright(3,1);
shiftrightunsigned
Unsigned right shift
No differences
select shiftrightunsigned(3,1);
greatest
Return the maximum value.
When there are null values among the parameters, the DLC function ignores null values and returns the maximum value excluding nulls, whereas the Hive function returns null.
select greatest(1,2,3.3);
least
Return the minimum value.
When there are null values among the parameters, the DLC function ignores null values and returns the minimum value excluding nulls, whereas the Hive function returns null.
select least(1,2,3.3);
width_bucket
Bucket value. Create num_buckets + 1 buckets of equal size based on min_value/max_value, and return the bucket number where the current value resides.
No differences
select width_bucket(10,1,20,2);

Collection Functions

DLC collection functions are identical to Hive collection functions.
For Hive collection functions, see the Apache Hive official website CollectionFunctions.
For DLC aggregate functions, see the Tencent Cloud official documentation CollectionFunctions.
Hive 3.1 Function Name
Function Feature Description
DLC Function Name
Difference Description
Usage Reference
size
Return the size of a map/array.
No differences
select size(str_to_map('a:1,b:2'));
map_keys
Return the list of keys in a map.
No differences
select map_keys(str_to_map('a:1,b:2'));
map_values
Return the list of values in a map.
No differences
select map_values(str_to_map('a:1,b:2'));
array_contains
Check if the array contains value.
No differences
select array_contains(split('a,b',','),'a');
sort_array
Sort the elements in the array in ascending order.
No differences
select sort_array(split('1,3,2',','));

Type Conversion Functions

Hive 3.1 Function Name
Function Description
DLC Function Name
Difference Description
Usage Reference
binary
Convert the input parameter to a binary array.
Hive 3.1 only supports input parameters of type string and binary, while DLC supports input parameters of type string, int, long, and binary.
In Hive 3.2, the output parameter is converted to a string for display, whereas DLC's output parameter is a binary array and is not converted to a string.
select binary('testString')
select binary(1) (not supported in HIVE)
select binary(inputCol) from inputTable (not supported in HIVE)
cast
Force the input parameter to be converted to the specified type.
An error is thrown if the given expression or value cannot be forcefully converted to the specified type. For example, a string-to-long conversion error: Cast function cannot convert value of type VARCHAR(65536) to type LONG.
In Hive 3.1, failed force conversions return NULL, whereas in DLC, an error is reported.
select cast('10' as int)select cast(inputCol as int) from inputTable

Time Functions

Hive 3.1 Function Name
Function Feature Description
DLC Corresponding Function Name
Difference Description
Usage Reference
from_unixtime
Convert a numeric Unix time (representing the number of seconds since 1970-01-01 00:00:00 UTC) to a string in the specified format.
The default output time format is yyyy-MM-dd HH:mm:ss, and the timezone is the system-defined timezone.
No differences
select from_unixtime(12458456)
select from_unixtime(100, 'yyyyMMdd HH:mm:ssZ')
unix_timestamp
Specify a time, or default to the current time, and return the number of seconds from 1970-01-01 00:00:00 UTC to the specified time.
No differences
select unix_timestamp()
select unix_timestamp('2023-04-12 00:00:00')
select unix_timestamp('2023-04-12', 'yyyy-MM-dd')
to_date
Specify a time and return the date of that time.
For example, the date for 2023-04-12 13:14:20 is 2023-04-12.
to_date
The to_date function in HIVE can only accept input in the specified format yyyy-MM-dd HH:mm:ss. For any other formats, NULL is returned.
DLC can accept an additional parameter to specify the data format, making the input time format more flexible.
select to_date('2023-04-12 19:41:23')
select to_date('20230412 19:41', 'yyyy-MM-dd HH:mm') (not supported in HIVE)
select to_date('20230412 19:41', 'yyyyMMdd HH:mm') (not supported in HIVE)
year
Specify a time and return the year of that time.
For example, the year for 2023-04-12 13:14:20 is 2023.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select year('2023-04-12 19:41:25')
select year('2023-04') (not supported in HIVE)
quarter
Specify a time and return the quarter of that time.
For example, the quarter for 2023-04-12 13:14:20 is 2.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select quarter('2023-04-12 19:41')
select quarter('2023-04') (not supported in HIVE)
month
Specify a time and return the month of that time.
For example, the month for 2023-04-12 13:14:20 is 4.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
When the month exceeds 12 in the time format, HIVE calculates the difference with the last month and takes the remainder, whereas DLC returns NULL.
select month('2023-04-12 19:41')
select month('2023-04') (not supported in HIVE)
day/dayofmonth
Specify a time and return the day of that time.
For example, the day for 2023-04-12 13:14:20 is the 12th day of the month.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
When the day exceeds the number of days in the month in the time format, HIVE takes the remainder of the number of days in the month, whereas DLC returns NULL.
select day('2023') (not supported in HIVE)
select dayofmonth('2023-04-12 19:41')
select day('2023-04-12 19:41')
hour
Specify a time and return the hour of that time.
For example, the hour for 2023-04-12 13:14:20 is the 13th hour.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
When the hour exceeds 23 in the time format, HIVE takes the remainder of 23, whereas DLC returns NULL.
select hour('2023-04-12 19:41')
select hour('2023-04') (not supported in HIVE)
minute
Specify a time and return the minute of that time.
For example, the minute for 2023-04-12 13:14:20 is the 14th minute.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
When the minute exceeds 59 in the time format, HIVE takes the remainder of 60, whereas DLC returns NULL.
select minute('2023-04-12 00:41')
select minute('2023-04') (not supported in HIVE)
second
Specify a time and return the second of that time.
For example, the second for 2023-04-12 13:14:20 is the 20th second.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
When the second exceeds 59 in the time format, HIVE takes the remainder of 60, whereas DLC returns NULL.
select second('2023-04-12 00:41:24')
select second('2023-04') (not supported in HIVE)
weekofyear
Specify a time and return the week of the year for that time.
For example, 2023-04-12 13:14:20 is the 15th week of 2023.
Note that the first Monday of the year is considered the start of the first week. If the date is before the first Monday of the year, it is considered the 52nd week of the previous year. For example, if 2023-01-01 is a Sunday, weekofyear('2023-01-01') returns 52 (the 52nd week of the previous year), while weekofyear('2023-01-02') returns 1 (the first week of the current year).
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select weekofyear('2023-04-12 00:41:25')
select weekofyear('2023-04') (not supported in HIVE)
extract
extract(field from source) extracts the specified field from the input source time or interval.
HIVE and DLC versions currently do not support adjusting precision.
When the DATE or TIMESTAMP identifier is explicitly specified, if the date or time format does not match, DLC throws an error, whereas HIVE returns 0.
When HIVE calculates INTERNAL intervals, if the field is not present in the source, it automatically calculates the remainder, while DLC throws an error. For example, when the year field is extracted from an interval of 24 months, HIVE returns 2 years, while DLC throws an error stating the field does not exist.
select extract(DAY FROM DATE '2023-04-12')
select extract(second from TIMESTAMP '2023-04-12 00:41:25')
select extract(month from interval '23-1' YEAR TO MONTH)
select extract(month from interval '23' MONTH)
select extract(year from interval '23' MONTH) (not supported in DLC)
select extract(hour from interval '23 13:23:34.34784' DAY TO SECOND)
datediff
Calculate the number of days between startDate and endDate. If startDate is later than endDate, a negative number is returned.
If any input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select datediff('2021', '2022-10') (not supported in HIVE)
select datediff('2023-04-13', '2022-04-13 11:00:00')
from_utc_timestamp
Given a timestamp in UTC, convert it to the time in the specified time zone timeZone.
If any input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
HIVE returns the time as a string in the format of yyyy-MM-dd HH:mm:ss.
DLC returns the UTC standard representation, for example, for the Shanghai time zone, it is yyyy-MM-ddTHH:mm:ss+08:00.
select from_utc_timestamp('2023-04', 'Asia/Seoul')
select from_utc_timestamp('2023-04-12 15:00:00', 'Asia/Shanghai')
to_utc_timestamp
Given a timestamp in the time zone timeZone, convert it to the UTC standard time timestamp.
If any input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
HIVE returns the time as a string in the format of yyyy-MM-dd HH:mm:ss.
DLC returns the UTC standard representation, for example, for the Shanghai time zone, it is yyyy-MM-ddTHH:mm:ss+08:00.
select to_utc_timestamp('2023-04-12 15:00:00', 'Asia/Shanghai')
select to_utc_timestamp('2023-04', 'Asia/Shanghai') (not supported in HIVE)
date_add
Date addition: Given a startDate date, return the date after adding numDays days.
numDays can be negative.
If any input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select date_add('2023-04-12 15:00:00', 1)
select date_add('2023-04', -1) (not supported in HIVE)
date_sub
Date subtraction: Given a startDate date, return the date after subtracting numDays days.
numDays can be negative.
If any input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select date_sub('2023-04-12 15:00:00', 1)
select date_sub('2023-04', -1) (not supported in HIVE)
current_date
Return the current date.
No differences
select current_date()
current_timestamp
Return the current time.
No differences
select current_timestamp()
add_months
Calculate the date after adding a specified number of months to a given date (or time).
If the input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select add_months('2023-04-12 15:00:00', 1)
select add_months('2023-04', -1) (not supported in HIVE)
last_day
Calculate the last day of the month for a given date.
If the input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select last_day('2023-04-12 15:00:00')
select last_day('2023-04') (not supported in HIVE)
next_day
Calculate the date of the first day_of_week after the specified date, where day_of_week can be any day from Monday to Sunday.
If any input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select next_day('2023-04-12 15:00:00')
select next_day('2023-04') (not supported in HIVE)
trunc
Calculate the first day of the specified date truncated to the given format (e.g., year, quarter, month, week, etc.), such as the first day of the quarter, month, or week.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
HIVE does not support truncation to WEEK.
select trunc('2023-04-12 15:00:00', 'MONTH')
select trunc('2023-04', 'YEAR') (not supported in HIVE)
-
Calculate the time of the first day of the specified date truncated to the given format (e.g., year, quarter, month, week, etc.), such as midnight on the first day of the quarter. Unlike the trunc method, date_trunc returns the time.
HIVE does not support this function, see the trunc function.
select date_trunc('MM', '2023-04-12 15:00:00')
select date_trunc('SECOND', '2023-04-12 15:00:60')
months_between
Calculate the number of months between date1 and date2. If date1 < date2, a negative number is returned.
Note that for the calculation, each month is estimated as 31 days (i.e., the denominator is 31). The numerator is the actual time difference between the two dates, precise to the millisecond. The final result is a decimal.
If any input is NULL, NULL is returned.
HIVE does not support input in yyyy or yyyy-MM formats but supports other time or date formats.
DLC supports input in yyyy or yyyy-MM formats.
select months_between('2023-04-12 15:00:00', '2023-04-12 15:00:00')
select months_between('2023-04', '2023-04-12 15:00:00') (not supported in HIVE)
date_format
According to the specified formatting template fmt (see Datetime Patterns for Formatting and Parsing), format the entered date.
If any input is NULL, NULL is returned.
When the date or time format is incorrect, or the fmt template format is incorrect, HIVE returns NULL, whereas DLC throws an error.
select date_format('2023-04-12 15:00:00', 'y')
select date_format('2023-04-12', 'yyyy-MM-dd HH:mm')

Conditional Functions

Hive 3.1 Function Name
Function Feature Description
DLC Function Name
Difference Description
Usage Reference
if
Return valueTrue if the condition is true; otherwise, return valueFalseOrNull.
When the condition is NULL, HIVE treats the condition as false, whereas DLC throws an error.
select if(1<2, 12, 'false')
isnull
Return true if a is NULL; otherwise, return false.
No differences
select isnull('false')
isnotnull
Return true if a is not NULL; otherwise, return false.
No differences
select isnotnull('false')
nvl
If the value is NULL, return the second parameter.
No differences
select nvl(NULL, 'false')
select nvl(1, 'false')
coalesce
Return the first non-NULL parameter.
coalesce
No differences
select coalesce(NULL, 'false')
case/when
Match evaluation
case/when
When the types of the match selection are different, HIVE only supports implicit conversion between integers and strings, whereas DLC also supports implicit conversion for boolean types, etc.
select CASE 'c' WHEN 'a' THEN 1 WHEN 'b' THEN 2 ELSE 0 END;
select CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
nullif
Return null if the parameters a = b; otherwise, return a.
HIVE does not support the first parameter being NULL, whereas DLC supports any parameter being NULL.
select nullif(1, 'test');
select nullif(NULL, 'test'); (not supported in HIVE)
assert_true
Throw an exception if the condition is not true; otherwise, return null.
No differences
select assert_true(1>0)

String Functions

Hive 3.1 Function Name
Function Feature Description
DLC Corresponding Function Name
Difference Description
Usage Reference
ascii
Return the numerical value of the first string in str.
No differences
select ascii('222');
base64
Convert the parameter from binary to a base64 string.
No differences
select base64('tencent');
character_length
Return the number of UTF-8 characters contained in str.
No differences
select char_length(binary('tencent'));
chr
Return a binary character equivalent to A.
No differences
select chr(65);
concat
Return a string or byte sequence that concatenates the parameters passed as strings or bytes in order.
No differences
select concat('Spark', 'SQL');
context_ngrams
Given a contextual N-grams, return the top k contextual N-grams from a set of tokenized sentences.
No differences
-
concat_ws
Return a string separated by sep.
concat_ws
No differences
select concat_ws(' ', 'tencent', 'dlc');
decode
Decode the first parameter into a string using the provided character sets (one of US-ASCII, ISO-8859-1, UTF-8, UTF-16BE, UTF-16LE, and UTF-16). If any parameter is NULL, the result is also NULL.
No differences
select decode(encode('abc', 'utf-8'), 'utf-8');
elt




Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback

Contact Us

Contact our sales team or business advisors to help your business.

Contact Us
Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 available.

Submit a Ticket
7x24 Phone Support
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon