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 |
|
| DATE_ADD | DATE_ADD | DATE_ADD | None |
|
|
| DATEPART | None | DATE_FORMAT | EXTRACT (datetime) |
|
|
| DATETRUNC | TRUNC | DATE_FORMAT | EXTRACT (datetime) |
|
|
| FROM_UNIXTIME | FROM_UNIXTIME | FROM_UNIXTIME | None |
|
|
| GETDATE | CURRENT_DATE | NOW | CURRENT_DATE |
|
|
| ISDATE | None | STR_TO_DATE (if false is returned, it cannot be converted to DATE) | None |
|
|
| LASTDAY | LAST_DAY | LAST_DAY | LAST_DAY |
|
|
| TO_DATE | TO_DATE | STR_TO_DATE() | DATE |
|
|
| TO_CHAR | None | DATE_FORMAT | TO_CHAR (datetime) |
|
|
| UNIX_TIMESTAMP | UNIX_TIMESTAMP | UNIX_TIMESTAMP | None |
|
|
| WEEKDAY | None | WEEKDAY | None |
|
|
| WEEKOFYEAR | WEEKOFYEAR | WEEKOFYEAR | None |
|
|
| YEAR | YEAR | YEAR | YEAR |
|
|
| QUARTER | QUARTER | QUARTER | QUARTER |
|
|
| MONTH | MONTH | MONTH | MONTH |
|
|
| DAY | DAY | DAY | DAY |
|
|
| DAYOFMONTH | DAYOFMONTH | DAYOFMONTH | None |
|
|
| HOUR | HOUR | HOUR | HOUR |
|
|
| MINUTE | MINUTE | MINUTE | MINUTE |
|
|
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP |
|
|
| ADD_MONTHS | ADD_MONTHS | ADDDATE | ADD_MONTHS |
|
|
| LAST_DAY | LAST_DAY | LAST_DAY | None |
|
|
| NEXT_DAY | NEXT_DAY | None | NEXT_DAY |
|
|
| MONTHS_BETWEEN | MONTHS_BETWEEN | TIMESTAMPDIFF | MONTHS_BETWEEN |
|
|
| Mathematical function | ABS | ABS | ABS | ABS |
|
| ACOS | ACOS | ACOS | ACOS |
|
|
| ASIN | ASIN | ASIN | ASIN |
|
|
| ATAN | ATAN | ATAN | ATAN |
|
|
| CEIL | CEIL | CEIL | CEIL |
|
|
| CONV | CONV | CONV | None |
|
|
| COS | COS | COS | COS |
|
|
| COSH | COSH | None | 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 | None |
|
|
| ROUND | ROUND | ROUND | ROUND |
|
|
| SIN | SIN | SIN | SIN |
|
|
| SINH | SINH | None | SINH |
|
|
| SQRT | SQRT | SQRT | SQRT |
|
|
| TAN | TAN | TAN | TAN |
|
|
| TANH | TANH | None | TANH |
|
|
| TRUNC | TRUNC | TRUNCATE | TRUNC |
|
|
| LOG2 | LOG2 | LOG2 | LOG |
|
|
| LOG10 | LOG10 | LOG10 | LOG |
|
|
| BIN | BIN | BIN | BITAND |
|
|
| HEX | HEX | HEX | RAWTOHEX |
|
|
| UNHEX | UNHEX | UNHEX | HEXTORAW |
|
|
| RADIANS | RADIANS | RADIANS | RADIANS |
|
|
| DEGREES | DEGREES | DEGREES | DEGREES |
|
|
| SIGN | SIGN | SIGN | SIGN |
|
|
| E | E | None | None |
|
|
| PI | PI | PI | PI |
|
|
| FACTORIAL | FACTORIAL | None | None |
|
|
| CBRT | CBRT | None | None |
|
|
| SHIFTLEFT | SHIFTLEFT | << | None |
|
|
| SHIFTRIGHT | SHIFTRIGHT | >> | None |
|
|
| SHIFTRIGHTUNSIGNED | SHIFTRIGHTUNSIGNED | >>> | None |
|
|
| Window function | 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 | None | None | None |
|
|
| NTILE | NTILE | NTILE | NTILE |
|
|
| Aggregate function | COUNT | COUNT | COUNT | COUNT |
|
| AVG | AVG | AVG | AVG |
|
|
| MAX | MAX | MAX | MAX |
|
|
| MIN | MIN | MIN | MIN |
|
|
| MEDIAN | None | None | MEDIAN |
|
|
| STDDEV | STDDEV | STDDEV | STDDEV |
|
|
| STDDEV_SAMP | STDDEV_SAMP | STDDEV_SAMP | STDDEV_SAMP |
|
|
| SUM | SUM | SUM | SUM |
|
|
| WM_CONCAT | None | GROUP_CONCAT | WM_CONCAT |
|
|
| COLLECT_LIST | COLLECT LIST | None | COLLECT |
|
|
| COLLECT_SET | COLLECT SET | None | COLLECT |
|
|
| VARIANCE/VAR_POP | VARIANCE/VAR_POP | VAR_POP | VARIANCE/VAR_POP |
|
|
| VAR_SAMP | VAR_SAMP | VAR_SAMP | VAR_SAMP |
|
|
| COVAR_POP | COVAR_POP | None | COVAR_POP |
|
|
| COVAR_SAMP | COVAR_SAMP | None | COVAR_SAMP |
|
|
| PERCENTILE | PERCENTILE | None | None |
|
|
| String function | CHAR_MATCHCOUNT | None | None | None |
|
| CHR | CHR | CHAR | CHR |
|
|
| CONCAT | CONCAT | CONCAT | CONCAT |
|
|
| GET_JSON_OBJECT | GET_JSON_OBJECT | JSON_EXTRACT() | None |
|
|
| INSTR | INSTR | INSTR | INSTR |
|
|
| IS_ENCODING | None | None | None |
|
|
| KEYVALUE | None | None | None |
|
|
| LENGTH | LENGTH | LENGTH | LENGTH |
|
|
| LENGTHB | LENGTHB | LENGTHB | LENGTHB |
|
|
| MD5 | MD5 | MD5 | None |
|
|
| REGEXP_EXTRACT | REGEXP_EXTRACT | None | None |
|
|
| REGEXP_INSTR | None | REGEXP_INSTR | REGEXP_INSTR |
|
|
| REGEXP_REPLACE | REGEXP_REPLACE | REGEXP_REPLACE | REGEXP_REPLACE |
|
|
| REGEXP_SUBSTR | None | REGEXP_SUBSTR | REGEXP_SUBSTR |
|
|
| REGEXP_COUNT | None | None | REGEXP_COUNT |
|
|
| SPLIT_PART | None | None | None |
|
|
| SUBSTR | SUBSTR | SUBSTR | SUBSTR |
|
|
| SUBSTRING | SUBSTRING | SUBSTRING | SUBSTR |
|
|
| TOLOWER | LOWER | LOWER | LOWER |
|
|
| TOUPPER | UPPER | UPPER | UPPER |
|
|
| TRIM | TRIM | TRIM | TRIM |
|
|
| LTRIM | LTRIM | LTRIM | LTRIM |
|
|
| RTRIM | RTRIM | RTRIM | LTRIM |
|
|
| REVERSE | REVERSE | REVERSE | REVERSE |
|
|
| SPACE | SPACE | SPACE | SPACE |
|
|
| REPEAT | REPEAT | REPEAT | REPEAT |
|
|
| ASCII | ASCII | ASCII | ASCII |
|
|
| CONCAT_WS | CONCAT_WS | CONCAT_WS | None |
|
|
| LPAD | LPAD | LPAD | LPAD |
|
|
| RPAD | RPAD | RPAD | RPAD |
|
|
| REPLACE | None | REPLACE | REPLACE |
|
|
| SOUNDEX | SOUNDEX | SOUNDEX | SOUNDEX |
|
|
| SUBSTRING_INDEX | SUBSTRING_INDEX | SUBSTRING_INDEX | None |
|
|
| TRANSLATE | TRANSLATE | None | TRANSLATE |
|
|
| URL_DECODE | None | None | None |
|
|
| URL_ENCODE | None | None | PERCENTILE_CONT |
|
|
| CRC32 | CRC32 | CRC32 | None |
|
|
| Other function | CAST | CAST | CAST | CAST |
|
| COALESCE | COALESCE | COALESCE | COALESCE |
|
|
| DECODE | DECODE | None | DECODE |
|
|
| GET_IDCARD_AGE | None | None | None |
|
|
| GET_IDCARD_BIRTHDAY | None | None | None |
|
|
| GET_IDCARD_SEX | None | None | None |
|
|
| GREATEST | GREATEST | GREATEST | None |
|
|
| ORDINAL | None | None | None |
|
|
| LEAST | LEAST | LEAST | LEAST |
|
|
| MAX_PT | None | None | None |
|
|
| UUID | None | UUID | UID |
|
|
| SAMPLE | None | None | None |
|
|
| IF | IF | IF | IF |
|
|
| CASE WHEN | CASE WHEN | CASE WHEN | CASE WHEN |
|
|
| SPLIT | SPLIT | SPLIT | None |
|
|
| STR_TO_MAP | STR_TO_MAP | None | None |
|
|
| EXPLODE | EXPLODE | None | None |
|
|
| MAP | MAP | None | None |
|
|
| MAP_KEYS | MAP_KEYS | None | None |
|
|
| MAP_VALUES | MAP_VALUES | None | None |
|
|
| NVL | NVL | IFNULL | None |
|
|
| ARRAY | ARRAY | None | None |
|
|
| SIZE | SIZE | None | None |
|
|
| ARRAY_CONTAINS | ARRAY_CONTAINS | None | None |
|
|
| POSEXPLODE | POSEXPLODE | None | None |
|
|
| TRANS_ARRAY | None | None | None |
|
|
| INLINE | INLINE | None | None |
|
|
| NAMED_STRUCT | None | None | None |
|
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;