tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Data Lake Compute
    Documentation
    Download PDF

    Comparison of Hive Functions

    Last updated: 2024-08-07 17:34:50
    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
      
      
      
      
      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 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy