This topic compares functions built in MaxCompute, MySQL, and Oracle.

Category MaxCompute Hive MySQL Oracle Partition pruning supported in MaxCompute SQL?
Date function DATEDIFF DATEDIFF DATEDIFF MONTHS_BETWEEN
  • Supported in MaxCompute mode
  • Not supported in Hive mode
DATE_ADD DATE_ADD DATE_ADD None
  • Supported in MaxCompute mode
  • Supported in Hive mode
DATEPART None DATE_FORMAT EXTRACT (datetime)
  • Supported in MaxCompute mode
  • Not supported in Hive mode
DATETRUNC TRUNC DATE_FORMAT EXTRACT (datetime)
  • Supported in MaxCompute mode
  • Not supported in Hive mode
FROM_UNIXTIME FROM_UNIXTIME FROM_UNIXTIME None
  • Supported in MaxCompute mode
  • Supported in Hive mode
GETDATE CURRENT_DATE NOW CURRENT_DATE
  • Supported in MaxCompute mode
  • Not supported in Hive mode
ISDATE None STR_TO_DATE (if false is returned, it cannot be converted to DATE) None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
LASTDAY LAST_DAY LAST_DAY LAST_DAY
  • Supported in MaxCompute mode
  • Not supported in Hive mode
TO_DATE TO_DATE STR_TO_DATE() DATE
  • Supported in MaxCompute mode
  • Not supported in Hive mode
TO_CHAR None DATE_FORMAT TO_CHAR (datetime)
  • Supported in MaxCompute mode
  • Supported in Hive mode
UNIX_TIMESTAMP UNIX_TIMESTAMP UNIX_TIMESTAMP None
  • Supported in MaxCompute mode
  • Supported in Hive mode
WEEKDAY None WEEKDAY None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
WEEKOFYEAR WEEKOFYEAR WEEKOFYEAR None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
YEAR YEAR YEAR YEAR
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
QUARTER QUARTER QUARTER QUARTER
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
MONTH MONTH MONTH MONTH
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
DAY DAY DAY DAY
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
DAYOFMONTH DAYOFMONTH DAYOFMONTH None
  • Not supported in MaxCompute mode
  • Supported in Hive mode
HOUR HOUR HOUR HOUR
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
MINUTE MINUTE MINUTE MINUTE
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
CURRENT_TIMESTAMP CURRENT_TIMESTAMP CURRENT_TIMESTAMP CURRENT_TIMESTAMP
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
ADD_MONTHS ADD_MONTHS ADDDATE ADD_MONTHS
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
LAST_DAY LAST_DAY LAST_DAY None
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
NEXT_DAY NEXT_DAY None NEXT_DAY
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
MONTHS_BETWEEN MONTHS_BETWEEN TIMESTAMPDIFF MONTHS_BETWEEN
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
Mathematical function ABS ABS ABS ABS
  • Supported in MaxCompute mode
  • Supported in Hive mode
ACOS ACOS ACOS ACOS
  • Supported in MaxCompute mode
  • Supported in Hive mode
ASIN ASIN ASIN ASIN
  • Supported in MaxCompute mode
  • Supported in Hive mode
ATAN ATAN ATAN ATAN
  • Supported in MaxCompute mode
  • Supported in Hive mode
CEIL CEIL CEIL CEIL
  • Supported in MaxCompute mode
  • Supported in Hive mode
CONV CONV CONV None
  • Supported in MaxCompute mode
  • Supported in Hive mode
COS COS COS COS
  • Supported in MaxCompute mode
  • Supported in Hive mode
COSH COSH None COSH
  • Supported in MaxCompute mode
  • Supported in Hive mode
COT COT COT COT
  • Supported in MaxCompute mode
  • Supported in Hive mode
EXP EXP EXP EXP
  • Supported in MaxCompute mode
  • Supported in Hive mode
FLOOR FLOOR FLOOR FLOOR
  • Supported in MaxCompute mode
  • Supported in Hive mode
LN LN LN LN
  • Supported in MaxCompute mode
  • Supported in Hive mode
LOG LOG LOG LOG
  • Supported in MaxCompute mode
  • Supported in Hive mode
POW POW POW POWER
  • Supported in MaxCompute mode
  • Supported in Hive mode
RAND RAND RAND None
  • Supported in MaxCompute mode
  • Supported in Hive mode
ROUND ROUND ROUND ROUND
  • Supported in MaxCompute mode
  • Supported in Hive mode
SIN SIN SIN SIN
  • Supported in MaxCompute mode
  • Supported in Hive mode
SINH SINH None SINH
  • Supported in MaxCompute mode
  • Supported in Hive mode
SQRT SQRT SQRT SQRT
  • Supported in MaxCompute mode
  • Supported in Hive mode
TAN TAN TAN TAN
  • Supported in MaxCompute mode
  • Supported in Hive mode
TANH TANH None TANH
  • Supported in MaxCompute mode
  • Supported in Hive mode
TRUNC TRUNC TRUNCATE TRUNC
  • Supported in MaxCompute mode
  • Supported in Hive mode
LOG2 LOG2 LOG2 LOG
  • Supported in MaxCompute mode
  • Supported in Hive mode
LOG10 LOG10 LOG10 LOG
  • Supported in MaxCompute mode
  • Supported in Hive mode
BIN BIN BIN BITAND
  • Supported in MaxCompute mode
  • Supported in Hive mode
HEX HEX HEX RAWTOHEX
  • Supported in MaxCompute mode
  • Supported in Hive mode
UNHEX UNHEX UNHEX HEXTORAW
  • Supported in MaxCompute mode
  • Supported in Hive mode
RADIANS RADIANS RADIANS RADIANS
  • Supported in MaxCompute mode
  • Supported in Hive mode
DEGREES DEGREES DEGREES DEGREES
  • Supported in MaxCompute mode
  • Supported in Hive mode
SIGN SIGN SIGN SIGN
  • Supported in MaxCompute mode
  • Supported in Hive mode
E E None None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
PI PI PI PI
  • Supported in MaxCompute mode
  • Not supported in Hive mode
FACTORIAL FACTORIAL None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
CBRT CBRT None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
SHIFTLEFT SHIFTLEFT << None
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
SHIFTRIGHT SHIFTRIGHT >> None
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
SHIFTRIGHTUNSIGNED SHIFTRIGHTUNSIGNED >>> None
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
Window function DENSE_RANK DENSE_RANK DENSE_RANK DENSE_RANK
  • Supported in MaxCompute mode
  • Supported in Hive mode
RANK RANK RANK RANK
  • Supported in MaxCompute mode
  • Supported in Hive mode
LAG LAG LAG LAG
  • Supported in MaxCompute mode
  • Supported in Hive mode
LEAD LEAD LEAD LEAD
  • Supported in MaxCompute mode
  • Supported in Hive mode
PERCENT_RANK PERCENT_RANK PERCENT_RANK PERCENT_RANK
  • Supported in MaxCompute mode
  • Supported in Hive mode
ROW_NUMBER ROW_NUMBER ROW_NUMBER ROW_NUMBER
  • Supported in MaxCompute mode
  • Supported in Hive mode
CLUSTER_SAMPLE None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
NTILE NTILE NTILE NTILE
  • Supported in MaxCompute mode
  • Supported in Hive mode
Aggregate function COUNT COUNT COUNT COUNT
  • Supported in MaxCompute mode
  • Supported in Hive mode
AVG AVG AVG AVG
  • Supported in MaxCompute mode
  • Supported in Hive mode
MAX MAX MAX MAX
  • Supported in MaxCompute mode
  • Supported in Hive mode
MIN MIN MIN MIN
  • Supported in MaxCompute mode
  • Supported in Hive mode
MEDIAN None None MEDIAN
  • Supported in MaxCompute mode
  • Supported in Hive mode
STDDEV STDDEV STDDEV STDDEV
  • Supported in MaxCompute mode
  • Supported in Hive mode
STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP
  • Supported in MaxCompute mode
  • Supported in Hive mode
SUM SUM SUM SUM
  • Supported in MaxCompute mode
  • Supported in Hive mode
WM_CONCAT None GROUP_CONCAT WM_CONCAT
  • Supported in MaxCompute mode
  • Supported in Hive mode
COLLECT_LIST COLLECT LIST None COLLECT
  • Supported in MaxCompute mode
  • Supported in Hive mode
COLLECT_SET COLLECT SET None COLLECT
  • Supported in MaxCompute mode
  • Supported in Hive mode
VARIANCE/VAR_POP VARIANCE/VAR_POP VAR_POP VARIANCE/VAR_POP
  • Supported in MaxCompute mode
  • Supported in Hive mode
VAR_SAMP VAR_SAMP VAR_SAMP VAR_SAMP
  • Supported in MaxCompute mode
  • Supported in Hive mode
COVAR_POP COVAR_POP None COVAR_POP
  • Supported in MaxCompute mode
  • Supported in Hive mode
COVAR_SAMP COVAR_SAMP None COVAR_SAMP
  • Supported in MaxCompute mode
  • Supported in Hive mode
PERCENTILE PERCENTILE None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
String function CHAR_MATCHCOUNT None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
CHR CHR CHAR CHR
  • Supported in MaxCompute mode
  • Supported in Hive mode
CONCAT CONCAT CONCAT CONCAT
  • Supported in MaxCompute mode
  • Supported in Hive mode
GET_JSON_OBJECT GET_JSON_OBJECT JSON_EXTRACT() None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
INSTR INSTR INSTR INSTR
  • Supported in MaxCompute mode
  • Supported in Hive mode
IS_ENCODING None None None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
KEYVALUE None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
LENGTH LENGTH LENGTH LENGTH
  • Supported in MaxCompute mode
  • Supported in Hive mode
LENGTHB LENGTHB LENGTHB LENGTHB
  • Supported in MaxCompute mode
  • Supported in Hive mode
MD5 MD5 MD5 None
  • Supported in MaxCompute mode
  • Supported in Hive mode
REGEXP_EXTRACT REGEXP_EXTRACT None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
REGEXP_INSTR None REGEXP_INSTR REGEXP_INSTR
  • Supported in MaxCompute mode
  • Not supported in Hive mode
REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE
  • Supported in MaxCompute mode
  • Supported in Hive mode
REGEXP_SUBSTR None REGEXP_SUBSTR REGEXP_SUBSTR
  • Supported in MaxCompute mode
  • Not supported in Hive mode
REGEXP_COUNT None None REGEXP_COUNT
  • Supported in MaxCompute mode
  • Not supported in Hive mode
SPLIT_PART None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
SUBSTR SUBSTR SUBSTR SUBSTR
  • Supported in MaxCompute mode
  • Supported in Hive mode
SUBSTRING SUBSTRING SUBSTRING SUBSTR
  • Supported in MaxCompute mode
  • Supported in Hive mode
TOLOWER LOWER LOWER LOWER
  • Supported in MaxCompute mode
  • Supported in Hive mode
TOUPPER UPPER UPPER UPPER
  • Supported in MaxCompute mode
  • Supported in Hive mode
TRIM TRIM TRIM TRIM
  • Supported in MaxCompute mode
  • Supported in Hive mode
LTRIM LTRIM LTRIM LTRIM
  • Supported in MaxCompute mode
  • Supported in Hive mode
RTRIM RTRIM RTRIM LTRIM
  • Supported in MaxCompute mode
  • Supported in Hive mode
REVERSE REVERSE REVERSE REVERSE
  • Supported in MaxCompute mode
  • Supported in Hive mode
SPACE SPACE SPACE SPACE
  • Supported in MaxCompute mode
  • Supported in Hive mode
REPEAT REPEAT REPEAT REPEAT
  • Supported in MaxCompute mode
  • Supported in Hive mode
ASCII ASCII ASCII ASCII
  • Supported in MaxCompute mode
  • Supported in Hive mode
CONCAT_WS CONCAT_WS CONCAT_WS None
  • Supported in MaxCompute mode
  • Supported in Hive mode
LPAD LPAD LPAD LPAD
  • Supported in MaxCompute mode
  • Supported in Hive mode
RPAD RPAD RPAD RPAD
  • Supported in MaxCompute mode
  • Supported in Hive mode
REPLACE None REPLACE REPLACE
  • Supported in MaxCompute mode
  • Supported in Hive mode
SOUNDEX SOUNDEX SOUNDEX SOUNDEX
  • Supported in MaxCompute mode
  • Not supported in Hive mode
SUBSTRING_INDEX SUBSTRING_INDEX SUBSTRING_INDEX None
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
TRANSLATE TRANSLATE None TRANSLATE
  • Not supported in MaxCompute mode
  • Not supported in Hive mode
URL_DECODE None None None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
URL_ENCODE None None PERCENTILE_CONT
  • Supported in MaxCompute mode
  • Not supported in Hive mode
CRC32 CRC32 CRC32 None
  • Supported in MaxCompute mode
  • Supported in Hive mode
Other function CAST CAST CAST CAST
  • Supported in MaxCompute mode
  • Supported in Hive mode
COALESCE COALESCE COALESCE COALESCE
  • Supported in MaxCompute mode
  • Supported in Hive mode
DECODE DECODE None DECODE
  • Supported in MaxCompute mode
  • Not supported in Hive mode
GET_IDCARD_AGE None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
GET_IDCARD_BIRTHDAY None None None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
GET_IDCARD_SEX None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
GREATEST GREATEST GREATEST None
  • Supported in MaxCompute mode
  • Supported in Hive mode
ORDINAL None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
LEAST LEAST LEAST LEAST
  • Supported in MaxCompute mode
  • Supported in Hive mode
MAX_PT None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
UUID None UUID UID
  • Supported in MaxCompute mode
  • Supported in Hive mode
SAMPLE None None None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
IF IF IF IF
  • Supported in MaxCompute mode
  • Supported in Hive mode
CASE WHEN CASE WHEN CASE WHEN CASE WHEN
  • Supported in MaxCompute mode
  • Supported in Hive mode
SPLIT SPLIT SPLIT None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
STR_TO_MAP STR_TO_MAP None None
  • Supported in MaxCompute mode
  • Not supported in Hive mode
EXPLODE EXPLODE None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
MAP MAP None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
MAP_KEYS MAP_KEYS None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
MAP_VALUES MAP_VALUES None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
NVL NVL IFNULL None
  • Supported in MaxCompute mode
  • Supported in Hive mode
ARRAY ARRAY None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
SIZE SIZE None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
ARRAY_CONTAINS ARRAY_CONTAINS None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
POSEXPLODE POSEXPLODE None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
TRANS_ARRAY None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
INLINE INLINE None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
NAMED_STRUCT None None None
  • Supported in MaxCompute mode
  • Supported in Hive mode
Note By default, MaxCompute mode is enabled. To switch to Hive mode, set odps.sql.hive.compatible to True.
--Switch to Hive mode at the project level.
setproject odps.sql.hive.compatible=True;
--Switch to Hive mode at the session level.
set odps.sql.hive.compatible=True;