This topic describes the mappings between the built-in functions of MaxCompute and the built-in functions of Hive, MySQL, and Oracle. This way, you can find the built-in functions of MaxCompute that match specific built-in functions of Hive, MySQL, and Oracle.

Date functions

MaxCompute Hive MySQL Oracle
DATEADD N/A N/A N/A
DATE_ADD DATE_ADD DATE_ADD N/A
DATE_SUB DATE_SUB DATE_SUB N/A
DATEDIFF DATEDIFF DATEDIFF MONTHS_BETWEEN
DATEPART N/A DATE_FORMAT EXTRACT (DATETIME)
DATETRUNC TRUNC DATE_FORMAT EXTRACT (DATETIME)
FROM_UNIXTIME FROM_UNIXTIME FROM_UNIXTIME N/A
GETDATE CURRENT_DATE NOW CURRENT_DATE
ISDATE N/A STR_TO_DATE (The return value FALSE indicates that a string cannot be converted into a date value.) N/A
LASTDAY LAST_DAY LAST_DAY LAST_DAY
TO_DATE TO_DATE STR_TO_DATE DATE
TO_CHAR N/A DATE_FORMAT TO_CHAR (DATETIME)
UNIX_TIMESTAMP UNIX_TIMESTAMP UNIX_TIMESTAMP N/A
WEEKDAY N/A WEEKDAY N/A
WEEKOFYEAR WEEKOFYEAR WEEKOFYEAR N/A
ADD_MONTHS ADD_MONTHS ADDDATE ADD_MONTHS
CURRENT_TIMESTAMP CURRENT_TIMESTAMP CURRENT_TIMESTAMP CURRENT_TIMESTAMP
DAY DAY DAY DAY
DAYOFMONTH DAYOFMONTH DAYOFMONTH N/A
EXTRACT EXTRACT EXTRACT EXTRACT
FROM_UTC_TIMESTAMP FROM_UTC_TIMESTAMP N/A N/A
HOUR HOUR HOUR HOUR
LAST_DAY LAST_DAY LAST_DAY N/A
MINUTE MINUTE MINUTE MINUTE
MONTH MONTH MONTH MONTH
MONTHS_BETWEEN MONTHS_BETWEEN TIMESTAMPDIFF MONTHS_BETWEEN
NEXT_DAY NEXT_DAY N/A NEXT_DAY
QUARTER QUARTER QUARTER QUARTER
SECOND SECOND SECOND N/A
TO_MILLIS N/A N/A N/A
YEAR YEAR YEAR N/A
Note The MaxCompute mode is enabled by default. To use the Hive-compatible mode, run one of the following commands: 
-- Switch to the Hive-compatible mode at the project level. 
setproject odps.sql.hive.compatible=True;
-- Switch to the Hive-compatible mode at the session level. 
set odps.sql.hive.compatible=True;

Mathematical functions

MaxCompute Hive MySQL Oracle
ABS ABS ABS ABS
ACOS ACOS ACOS ACOS
ASIN ASIN ASIN ASIN
ATAN ATAN ATAN ATAN
CEIL CEIL CEIL CEIL
CONV CONV CONV N/A
COS COS COS COS
COSH COSH N/A COSH
COT COT COT COT
EXP EXP EXP EXP
FLOOR FLOOR FLOOR FLOOR
LN LN LN LN
LOG LOG LOG LOG
POW POW POW POWER
RAND RAND RAND N/A
ROUND ROUND ROUND ROUND
SIN SIN SIN SIN
SINH SINH N/A SINH
SQRT SQRT SQRT SQRT
TAN TAN TAN TAN
TANH TANH N/A TANH
TRUNC TRUNC TRUNCATE TRUNC
BIN BIN BIN BITAND
CBRT CBRT N/A N/A
CORR CORR CORR CORR
DEGREES DEGREES DEGREES DEGREES
E E N/A N/A
FACTORIAL FACTORIAL N/A N/A
FORMAT_NUMBER FORMAT_NUMBER FORMAT N/A
HEX HEX HEX RAWTOHEX
LOG2 LOG2 LOG2 LOG
LOG10 LOG10 LOG10 LOG
PI PI PI PI
RADIANS RADIANS RADIANS RADIANS
SIGN SIGN SIGN SIGN
SHIFTLEFT SHIFTLEFT << N/A
SHIFTRIGHT SHIFTRIGHT >> N/A
SHIFTRIGHTUNSIGNED SHIFTRIGHTUNSIGNED >>> N/A
UNHEX UNHEX UNHEX HEXTORAW
WIDTH_BUCKET WIDTH_BUCKET N/A WIDTH_BUCKET
Note The MaxCompute mode is enabled by default. To use the Hive-compatible mode, run one of the following commands: 
-- Switch to the Hive-compatible mode at the project level. 
setproject odps.sql.hive.compatible=True;
-- Switch to the Hive-compatible mode at the session level. 
set odps.sql.hive.compatible=True;

Window functions

MaxCompute Hive MySQL Oracle
COUNT COUNT COUNT COUNT
AVG AVG AVG AVG
MAX MAX MAX MAX
MIN MIN MIN MIN
MEDIAN N/A N/A MEDIAN
STDDEV N/A STDDEV STDDEV
STDDEV_SAMP N/A STDDEV_SAMP STDDEV_SAMP
SUM SUM SUM SUM
DENSE_RANK DENSE_RANK DENSE_RANK DENSE_RANK
RANK RANK RANK RANK
LAG LAG LAG LAG
LEAD LEAD LEAD LEAD
PERCENT_RANK PERCENT_RANK PERCENT_RANK PERCENT_RANK
ROW_NUMBER ROW_NUMBER ROW_NUMBER ROW_NUMBER
CLUSTER_SAMPLE N/A N/A N/A
CUME_DIST CUME_DIST CUME_DIST CUME_DIST
NTILE NTILE NTILE NTILE

Aggregate functions

MaxCompute Hive MySQL Oracle
AVG AVG AVG AVG
COUNT COUNT COUNT COUNT
COUNT_IF N/A N/A N/A
MAX MAX MAX MAX
MIN MIN MIN MIN
MEDIAN N/A N/A MEDIAN
STDDEV STDDEV STDDEV STDDEV
STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP
SUM SUM SUM SUM
WM_CONCAT N/A GROUP_CONCAT WM_CONCAT
ANY_VALUE N/A N/A N/A
APPROX_DISTINCT N/A N/A N/A
ARG_MAX N/A N/A N/A
ARG_MIN N/A N/A N/A
COLLECT_LIST COLLECT LIST N/A COLLECT
COLLECT_SET COLLECT SET N/A COLLECT
COVAR_POP COVAR_POP N/A COVAR_POP
COVAR_SAMP COVAR_SAMP N/A COVAR_SAMP
NUMERIC_HISTOGRAM NUMERIC_HISTOGRAM N/A N/A
PERCENTILE PERCENTILE N/A N/A
PERCENTILE_APPROX PERCENTILE_APPROX N/A N/A
VARIANCE/VAR_POP VARIANCE/VAR_POP VAR_POP VARIANCE/VAR_POP
VAR_SAMP VAR_SAMP VAR_SAMP VAR_SAMP
Note The MaxCompute mode is enabled by default. To use the Hive-compatible mode, run one of the following commands: 
-- Switch to the Hive-compatible mode at the project level. 
setproject odps.sql.hive.compatible=True;
-- Switch to the Hive-compatible mode at the session level. 
set odps.sql.hive.compatible=True;

String functions

MaxCompute Hive MySQL Oracle
ASCII ASCII ASCII ASCII
CHAR_MATCHCOUNT N/A N/A N/A
CHR CHR CHAR CHR
CONCAT CONCAT CONCAT CONCAT
ENCODE ENCODE N/A N/A
FIND_IN_SET FIND_IN_SET FIND_IN_SET N/A
FORMAT_NUMBER FORMAT_NUMBER FORMAT N/A
FROM_JSON N/A N/A N/A
GET_JSON_OBJECT GET_JSON_OBJECT JSON_EXTRACT N/A
INSTR INSTR INSTR INSTR
IS_ENCODING N/A N/A N/A
KEYVALUE N/A N/A N/A
LENGTH LENGTH LENGTH LENGTH
LENGTHB LENGTHB LENGTHB LENGTHB
LOCATE LOCATE LOCATE N/A
LTRIM LTRIM LTRIM LTRIM
MD5 MD5 MD5 N/A
PARSE_URL PARSE_URL N/A N/A
PARSE_URL_TUPLE PARSE_URL_TUPLE N/A N/A
REGEXP_COUNT N/A N/A REGEXP_COUNT
REGEXP_EXTRACT REGEXP_EXTRACT N/A N/A
REGEXP_INSTR N/A REGEXP_INSTR REGEXP_INSTR
REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE
REGEXP_SUBSTR N/A REGEXP_SUBSTR REGEXP_SUBSTR
REPEAT REPEAT REPEAT REPEAT
REVERSE REVERSE REVERSE REVERSE
RTRIM RTRIM RTRIM RTRIM
SPACE SPACE SPACE SPACE
SPLIT_PART N/A N/A N/A
SUBSTR SUBSTR SUBSTR SUBSTR
SUBSTRING SUBSTRING SUBSTRING SUBSTR
TO_CHAR N/A N/A N/A
TO_JSON N/A N/A N/A
TOLOWER LOWER LOWER LOWER
TOUPPER UPPER UPPER UPPER
TRIM TRIM TRIM TRIM
URL_DECODE N/A N/A N/A
URL_ENCODE N/A N/A PERCENTILE_CONT
CONCAT_WS CONCAT_WS CONCAT_WS N/A
JSON_TUPLE JSON_TUPLE N/A N/A
LPAD LPAD LPAD LPAD
RPAD RPAD RPAD RPAD
REPLACE REPLACE REPLACE REPLACE
SOUNDEX SOUNDEX SOUNDEX SOUNDEX
SUBSTRING_INDEX SUBSTRING_INDEX SUBSTRING_INDEX N/A
TRANSLATE TRANSLATE N/A TRANSLATE
Note The MaxCompute mode is enabled by default. To use the Hive-compatible mode, run one of the following commands: 
-- Switch to the Hive-compatible mode at the project level. 
setproject odps.sql.hive.compatible=True;
-- Switch to the Hive-compatible mode at the session level. 
set odps.sql.hive.compatible=True;

Other functions

MaxCompute Hive MySQL Oracle
BASE64 BASE64 TO_BASE64 UTL_ENCODE.BASE64_ENCODE
BETWEEN AND expression BETWEEN AND BETWEEN AND BETWEEN AND
CASE WHEN expression CASE WHEN CASE WHEN CASE WHEN
CAST CAST CAST CAST
COALESCE COALESCE COALESCE COALESCE
COMPRESS N/A COMPRESS UTL_COMPRESS.LZ_COMPRESS
CRC32 CRC32 CRC32 N/A
DECODE DECODE N/A DECODE
DECOMPRESS N/A UNCOMPRESS UTL_COMPRESS.LZ_UNCOMPRESS
GET_IDCARD_AGE N/A N/A N/A
GET_IDCARD_BIRTHDAY N/A N/A N/A
GET_IDCARD_SEX N/A N/A N/A
GET_USER_ID CURRENT_USER CURRENT_USER UID
GREATEST GREATEST GREATEST N/A
HASH HASH N/A ORA_HASH
IF IF IF IF
LEAST LEAST LEAST LEAST
MAX_PT N/A N/A N/A
NULLIF NULLIF NULLIF NULLIF
NVL NVL IFNULL N/A
ORDINAL N/A N/A N/A
PARTITION_EXISTS N/A N/A N/A
SAMPLE N/A N/A N/A
SHA SHA SHA N/A
SHA1 SHA1 SHA1 N/A
SHA2 SHA2 SHA2 N/A
SIGN SIGN SIGN SIGN
SPLIT SPLIT SPLIT N/A
STACK STACK N/A N/A
STR_TO_MAP STR_TO_MAP N/A N/A
TABLE_EXISTS N/A N/A N/A
TRANS_ARRAY N/A N/A N/A
TRANS_COLS N/A N/A N/A
UNBASE64 UNBASE64 FROM_BASE64 UTL_ENCODE.BASE64_DECODE
UNIQUE_ID N/A N/A N/A
UUID N/A UUID UID
Note The MaxCompute mode is enabled by default. To use the Hive-compatible mode, run one of the following commands: 
-- Switch to the Hive-compatible mode at the project level. 
setproject odps.sql.hive.compatible=True;
-- Switch to the Hive-compatible mode at the session level. 
set odps.sql.hive.compatible=True;

Complex type functions

Function type MaxCompute Hive MySQL Oracle
ARRAY ALL_MATCH N/A N/A N/A
ANY_MATCH N/A N/A N/A
ARRAY ARRAY N/A N/A
ARRAY_CONTAINS ARRAY_CONTAINS N/A N/A
ARRAY_DISTINCT N/A N/A N/A
ARRAY_EXCEPT N/A N/A N/A
ARRAY_INTERSECT N/A N/A N/A
ARRAY_JOIN N/A N/A N/A
ARRAY_MAX N/A N/A N/A
ARRAY_MIN N/A N/A N/A
ARRAY_POSITION N/A N/A N/A
ARRAY_REMOVE N/A N/A N/A
ARRAY_REDUCE N/A N/A N/A
ARRAY_REPEAT N/A N/A N/A
ARRAY_SORT N/A N/A N/A
ARRAY_UNION N/A N/A N/A
ARRAYS_OVERLAP N/A N/A N/A
ARRAYS_ZIP N/A N/A N/A
CONCAT CONCAT N/A N/A
EXPLODE EXPLODE N/A N/A
FILTER N/A N/A N/A
INDEX [] operator N/A N/A
POSEXPLODE POSEXPLODE N/A N/A
SIZE SIZE N/A N/A
SLICE N/A N/A N/A
SORT_ARRAY SORT_ARRAY N/A N/A
TRANSFORM N/A N/A N/A
ZIP_WITH N/A N/A N/A
MAP EXPLODE EXPLODE N/A N/A
INDEX [] operator N/A N/A
MAP MAP N/A N/A
MAP_CONCAT N/A N/A N/A
MAP_ENTRIES N/A N/A N/A
MAP_FILTER N/A N/A N/A
MAP_FROM_ARRAYS N/A N/A N/A
MAP_FROM_ENTRIES N/A N/A N/A
MAP_KEYS MAP_KEYS N/A N/A
MAP_VALUES MAP_VALUES N/A N/A
MAP_ZIP_WITH N/A N/A N/A
SIZE SIZE N/A N/A
TRANSFORM_KEYS N/A N/A N/A
TRANSFORM_VALUES N/A N/A N/A
STRUCT FIELD . operator N/A N/A
INLINE INLINE N/A N/A
STRUCT STRUCT N/A N/A
NAMED_STRUCT N/A N/A N/A
JSON FROM_JSON N/A N/A N/A
GET_JSON_OBJECT GET_JSON_OBJECT JSON_EXTRACT N/A
JSON_TUPLE JSON_TUPLE N/A N/A
TO_JSON N/A N/A N/A
Note The MaxCompute mode is enabled by default. To use the Hive-compatible mode, run one of the following commands: 
-- Switch to the Hive-compatible mode at the project level. 
setproject odps.sql.hive.compatible=True;
-- Switch to the Hive-compatible mode at the session level. 
set odps.sql.hive.compatible=True;