AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生大数据计算服务 MaxCompute 操作指南 开发参考：SQL 通用参考 普通模式和Hive兼容模式下SQL的差异

普通模式和Hive兼容模式下SQL的差异

更新时间：
产品详情
我的收藏

本文为您介绍MaxCompute中运算符、类型转换和内建函数分别在普通模式和Hive兼容模式下使用的区别。

运算符

  • BITAND（&）

    当输入参数是BIGINT类型的时候，如果BITAND的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，而Hive模式仍然是LONG_MIN。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select cast((a & b) as string) from
values(-9223372036854775807L, -9223372036854775792L) t(a, b);
--返回NULL

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select cast((a & b) as string) from
values(-9223372036854775807L, -9223372036854775792L) t(a, b);
--返回-9223372036854775808

    • Hive

      select cast((-9223372036854775807L & -9223372036854775792L) as string);
--返回-9223372036854775808

  • BITOR（|）

    当输入参数是BIGINT类型的时候，如果BITOR的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，而Hive模式仍然是LONG_MIN。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select cast((a | b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
--返回NULL

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select cast((a | b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
--返回-9223372036854775808

    • Hive

      select cast(-9223372036854775808 as bigint) | 0;
-- 返回-9223372036854775808

  • BITXOR（^）

    当输入参数是BIGINT类型的时候，如果BITXOR的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，而Hive模式仍然是LONG_MIN。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select cast((a ^ b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
--返回NULL

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select cast((a ^ b) as string) from
values(java.lang.Long.MIN_VALUE, 0L) t(a, b);
--返回-9223372036854775808

    • Hive

      select cast(-9223372036854775808 as bigint) ^ 0;
--返回-9223372036854775808

  • EQ（=）

    当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，就认为它们相等；而Hive兼容模式对相等的检查更加严格。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select a = 1.0 from values (1.000000000000001) t(a);
-- 结果是true，因为这两个数足够接近

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select a = 1.0 from values (1.000000000000001) t(a);
-- 结果是false

    • Hive

      select 1.0 = 1.000000000000001 ;
-- 结果是false

  • NEQ（!=）

    当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，就认为它们相等；而Hive兼容模式对相等的检查更加严格。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select a != 1.0 from values (1.000000000000001) t(a);
-- 结果是false，因为这两个数足够接近

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select a != 1.0 from values (1.000000000000001) t(a);
-- 结果是true

    • Hive

      select 1.000000000000001 != 1.0 ;
-- 结果是true

  • GE（>=）

    当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，就认为它们相等这会导致即使第1个输入参数小于第2个参数，但是只要他们足够接近，GE的返回结果也可能是true。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select 1.0 >= a from values (1.000000000000001) t(a);
-- 结果是true，因为这两个数足够接近，认为它们相等

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select 1.0 >= a from values (1.000000000000001) t(a);
-- 结果是false

    • Hive

      select 1.0 >= 1.000000000000001;
-- 结果是false

  • GT（>）

    当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，就认为它们相等这会导致即使第1个输入参数大于第2个参数，但是只要他们足够接近，GT的返回结果也可能是false。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;l
select a > 1.0 from values (1.000000000000001) t(a);
-- 结果是false，因为这两个数足够接近，认为它们相等

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select a > 1.0 from values (1.000000000000001) t(a);
-- 结果是true

    • Hive

      select 1.000000000000001>1.0;
-- 结果是true

  • LE（<=）

    当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，就认为它们相等这会导致即使第1个输入参数大于第2个参数，但是只要他们足够接近，LE的返回结果也可能是true。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select a <= 1.0 from values (1.000000000000001) t(a);
-- 结果是true，因为这两个数足够接近，认为它们相等

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select a <= 1.0 from values (1.000000000000001) t(a);
-- 结果是false

    • Hive

      select 1.000000000000001 <= 1.0 ;
-- 结果是false

  • LT（<）

    当输入参数是DOUBLE类型的时候，普通模式下对相等的检查更加宽松，如果两个输入参数足够接近，就认为它们相等这会导致即使第1个输入参数小于第2个参数，但是只要他们足够接近，LE的返回结果也可能是false。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select 1.0 < a from values (1.000000000000001) t(a);
-- 结果是false，因为这两个数足够接近，认为它们相等

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select 1.0 < a from values (1.000000000000001) t(a);
-- 结果是true

    • Hive

      select 1.0 < 1.000000000000001;
-- 结果是true

  • PLUS（+）

    计算结果超范围时的处理不同，在普通模式时可能会报错，在Hive兼容模式计算结果溢出不会报错，同时为两种模式开启严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (100L + a) from values (9223372036854775807L) t(a);
-- 报错，计算结果溢出

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (100L + a) from values (9223372036854775807L) t(a);
-- 计算结果溢出，但是不会报错

    • Hive

      select (100L + 9223372036854775807L) ;
-- 返回-9223372036854775709，计算结果溢出，但是不会报错

  • MINUS（-）

    计算结果超范围时的处理不同，在普通模式时可能会报错，在Hive兼容模式计算结果溢出不会报错，同时为两种模式开启严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (-100L - a) from values (9223372036854775807L) t(a);
-- 报错，计算结果溢出

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (-100L - a) from values (9223372036854775807L) t(a);
-- 计算结果溢出，但是不会报错

    • Hive

      select (-100L - 9223372036854775807L) ;
-- 返回 9223372036854775709

  • MPL（*）

    计算结果超范围时的处理不同，在普通模式时可能会报错，在Hive兼容模式计算结果溢出不会报错，同时为两种模式开启严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select (a * 9223372036854775807L) from values (9223372036854775807L) t(a);
-- 报错，计算结果溢出

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select (a * 9223372036854775807L) from values (9223372036854775807L) t(a);
-- 计算结果溢出，但是不会报错

    • Hive

      select (9223372036854775807L * 9223372036854775807L) ;
-- 返回1；计算结果溢出，但是不会报错

  • DIV（/）

    计算结果超范围时的处理不同，在普通模式时可能会报错，在Hive兼容模式计算结果溢出不会报错，同时为两种模式开启严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select 1 / a from values (0L) t(a);
-- strict模式下报错

select 1.0 / a from values (0.0) t(a);
-- strict模式下报错

select 1BD / a from values (0BD) t(a);
-- strict模式下报错

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select 1 / a from values (0L) t(a);
-- 返回NULL

select 1.0 / a from values (0.0) t(a);
-- 返回NULL

select 1BD / a from values (0BD) t(a);
-- 返回NULL

    • Hive

      select 1 / 0L;
-- 返回NULL

select 1.0 / 0.0;
-- 返回NULL

select 1BD / 0BD;
-- 返回NULL

类型转换

  • TOBIGINT

    当输入参数不合法时，普通模式下可能会报错，Hive兼容模式返回NULL，同时为两种模式开启即严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as bigint) from values ('hello') t(a);
-- 报错，输入参数不合法

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select cast(a as bigint) from values ('hello') t(a);
-- 返回NULL

    • Hive

      select cast('hello' as bigint) ;
-- 返回NULL

  • TODECIMAL

    当输入参数不合法时，普通模式下可能会报错，Hive兼容模式返回NULL，同时为两种模式开启即严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as decimal) from values ('hello') t(a);
-- 报错，输入参数不合法

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strict.mode=true;
select cast(a as decimal) from values ('hello') t(a);
-- 返回NULL

    • Hive

      select cast('hello' as decimal) ;
-- 返回NULL

  • TODOUBLE

    当输入参数不合法时，普通模式下可能会报错，Hive兼容模式返回NULL，同时为两种模式开启即严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select cast(a as double) from values ('hello') t(a);
-- 报错，输入参数不合法

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select cast(a as double) from values ('hello') t(a);
-- 返回NULL

    • Hive

      select cast('hello' as double) ;
-- 返回NULL

  • TOSMALLINT

    当输入参数不合法或超出范围时，普通模式下可能会报错，Hive兼容模式返回NULL，同时为两种模式开启即严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
set odps.sql.type.system.odps2=true;

select cast(a as smallint) from values ('hello') t(a);
-- 报错，输入参数不合法

select cast(a as smallint) from values (9223372036854775807L) t(a);
-- 报错，数据溢出

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
set odps.sql.type.system.odps2=true;

select cast(a as smallint) from values ('hello') t(a);
-- 返回NULL

select cast(a as smallint) from values (9223372036854775807L) t(a);
-- 数据溢出但是不报错

    • Hive

      select cast('hello' as smallint);
-- 返回NULL

select cast(9223372036854775807L as smallint);
-- 返回-1，数据溢出但是不报错

  • TOTINYINT

    当输入参数不合法或超出范围时，普通模式下可能会报错，Hive兼容模式返回NULL，同时为两种模式开启即严格模式（odps.function.strictmode=true）。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
set odps.sql.type.system.odps2=true;

select cast(a as tinyint) from values ('hello') t(a);
-- 报错，输入参数不合法

select cast(a as tinyint) from values (9223372036854775807L) t(a);
-- 报错，数据溢出

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
set odps.sql.type.system.odps2=true;

select cast(a as tinyint) from values ('hello') t(a);
-- 返回NULL

select cast(a as tinyint) from values (9223372036854775807L) t(a);
-- 数据溢出但是不报错

    • Hive

      select cast('hello' as tinyint) ;
-- 返回NULL

select cast(9223372036854775807L as tinyint) ;
-- 返回-1，数据溢出但是不报错

内建函数

  • ACOS

    取值超出范围[-1,1]时，普通模式和Hive模式的表现不同。普通模式返回NULL，并且可能会报错；而Hive模式返回NAN。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select acos(a) from values(1.5) t(a);
--返回NULL，并且有可能会报错

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select acos(a) from values(1.5) t(a);
--返回NAN

    • Hive

      select acos(1.5);
--返回NAN

  • ASCII

    普通模式ASCII函数返回值类型为BIGINT，Hive兼容模式ASCII函数返回值类型为INT。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select ascii('abcde');
--显示返回值类型是bigint

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select ascii('abcde');
--显示返回值类型是int

    • Hive

      explain select ascii('abcde');
--显示返回值类型是int

  • ASIN

    取值超出范围[-1,1]时，普通模式和Hive模式的表现不同。普通模式返回NULL，并且可能会报错；而Hive模式返回NAN。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select asin(a) from values(1.5) t(a);
--返回NULL，并且有可能会报错

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select asin(a) from values(1.5) t(a);
--返回NAN

    • Hive

      select asin(a) from values(1.5) t(a);
--返回NAN

  • CEIL

    普通模式CEIL函数输入参数类型是DECIMAL的时候，返回值类型为BIGINT；Hive兼容模式CEIL函数输入参数类型是DECIMAL的时候，返回值类型为DECIMAL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select ceil(1.2BD);
--显示返回值类型是bigint

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select ceil(1.2BD);
--显示返回值类型是decimal

    • Hive

      explain select ceil(1.2BD);
--显示返回值类型是decimal(2,0)

  • CHR

    输入参数取值超出范围，普通模式与Hive模式的返回结果不同。普通模式会报错，而Hive兼容模式返回空字符串。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select chr(-100L);
--报错，输入参数不合法

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select chr(-100L);
--返回空字符串

    • Hive

      select chr(-100L);
--返回空字符串

  • CONCAT_WS

    • 输入参数中有NULL，普通模式与Hive模式的返回结果不同。普通模式返回NULL，而Hive兼容模式会忽略输入参数中的NULL。

    • 输入参数中有空数组，如下所示。普通模式下会返回NULL，而Hive兼容模式下会返回空字符串。

      string concat_ws(string <separator>, array<string>  arr)

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select concat_ws(',', 'a', null, 'b');
-- 返回NULL

select concat_ws(',', array());
-- 返回NULL

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select concat_ws(',', 'a', null, 'b');
-- 返回如下值：
+-----+
| _c0 |
+-----+
| a,b |
+-----+

select concat_ws(',', array());
--返回空字符串

    • Hive

      select concat_ws(',', 'a', null, 'b');
-- 返回如下值：
+-----+
| _c0 |
+-----+
| a,b |
+-----+

select concat_ws(',', array());
--返回空字符串

  • COT

    当输入参数为0（或者是其他导致计算结果为无穷大的数）时，普通模式下会返回NULL，并且可能会报错；而Hive兼容模式返回INF。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select cot(a) from values(0.0) t(a);
--返回NULL，并且有可能会报错

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select cot(a) from values(0.0) t(a);
--返回INF

    • Hive不支持此函数。

  • EXP

    当使用EXP函数计算的结果超出输出类型的值域范围时，同时为两种模式开启严格模式（odps.function.strictmode=true），在普通模式下可能会报错，而Hive兼容模式返回INF。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select exp(a) from values (1000L) t(a);
-- 报错 Data overflow

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select exp(a) from values (1000L) t(a);
-- 返回INF

    • Hive

      select exp(1000L) ;
-- 返回INF

  • FIND_IN_SET

    普通模式FIND_IN_SET函数返回值类型为BIGINT，Hive兼容模式FIND_IN_SET函数返回值类型为INT。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select find_in_set('ab', 'abc,hello,ab,c');
--显示返回值类型是bigint

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select find_in_set('ab', 'abc,hello,ab,c');
--显示返回值类型是int

    • Hive

      explain select find_in_set('ab', 'abc,hello,ab,c');
--显示返回值类型是int

  • FLOOR

    普通模式FLOOR函数输入参数类型是DECIMAL的时候，返回值类型为BIGINT；Hive兼容模式FLOOR函数输入参数类型是DECIMAL的时候，返回值类型为DECIMAL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select floor(1.2BD);
--显示返回值类型是bigint

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select floor(1.2BD);
--显示返回值类型是decimal

    • Hive

      explain select floor(1.2BD);
--显示返回值类型是decimal(2,0)

  • FROM_UNIXTIME

    • 普通模式支持如下形式的function signature，不支持指定时间的格式。

      DATETIME FROM_UNIXTIME(BIGINT time)

    • Hive兼容模式支持如下两种形式的function signature，这两种function sigature都返回STRING类型。第1个signature允许指定输出的时间格式，输出的时间格式受SimpleDateFormat的控制，详情请参见SimpleDateFormat。

      STRING FROM_UNIXTIME(BIGINT time, STRING format)

STRING FROM_UNIXTIME(BIGINT time)

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;

select from_unixtime(12345678, 'yyyy-MM-dd HH:mm:ss');
--报错，from_unixtime只允许1个输入参数，不允许2个输入参数

select weekday(from_unixtime(0));
-- 执行成功，from_unixtime返回datetime类型，weekday接受datetime类型的输入参数

    • Hive兼容模式

      set odps.sql.hive.compatible=true;

select from_unixtime(12345678, 'yyyy-MM-dd HH:mm:ss');
--返回结果如下：
+-----+
| _c0 |
+-----+
| 1970-05-24 05:21:18 |
+-----+

select weekday(from_unixtime(0));
-- 执行失败，因为from_unixtime返回string类型，而weekday不接受string类型输入参数。

    • Hive

      select from_unixtime(12345678, 'yyyy-MM-dd HH:mm:ss');
--返回结果如下：
+-----+
| _c0 |
+-----+
| 1970-05-24 05:21:18 |
+-----+

select weekday(from_unixtime(0));
-- hive不支持weekday函数

  • FROM_UTC_TIMESTAMP

    当输入参数超范围时，且同时为两种模式开启严格模式（odps.function.strictmode=true），普通模式返回可能会报错，Hive兼容模式返回NULL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select from_utc_timestamp(1501557840000000, 'UTC');
-- 报错，输入参数超出范围

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select from_utc_timestamp(1501557840000000, 'UTC');
-- 返回NULL

    • Hive

      select from_utc_timestamp(1501557840000000, 'UTC');
-- 报错，FAILED: IllegalArgumentException Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

  • HASH

    普通模式下HASH函数返回BIGINT类型，Hive兼容模式HASH函数下返回INT类型。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select hash(0, 2, 4);
--返回值类型是bigint

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select hash(0, 2, 4);
--返回值类型是int

    • Hive

      explain select hash(0, 2, 4);
--返回值类型是int

  • IS_ENCODING

    判断输入的字符串是否可以从指定的一个字符集from_encoding转为另一个字符集to_encoding。也可以用于判断输入是否为乱码，通常您可以将from_encoding设为UTF-8，to_encoding设为GBK。

    • 普通模式下，输入字符串必须要能够用from_encoding解码成功，并能按照to_encoding编码，结果才能返回false。

    • Hive兼容模式下，输入字符串必须是UTF-8编码，并且需要能同时被from_encoding和to_encoding编码，结果才返回false。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select is_encoding('中文', 'gbk', 'utf-8');
-- 返回false，'中文'是utf-8编码，不能用gbk解码，所以返回false

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select is_encoding('中文', 'gbk', 'utf-8');
-- 返回true，'中文'既可以转换成gbk编码，又可以转换成utf-8编码

    • Hive不支持此语法。

  • INSTR

    输入参数为两个字符串时，普通模式下返回值类型为BIGINT，Hive兼容模式下返回值类型为INT。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select instr('Tech on the net', 'e');
--返回bigint类型

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select instr('Tech on the net', 'e');
--返回int类型

    • Hive

      explain select instr('Tech on the net', 'e');
--返回的是int类型

  • LENGTH

    普通模式下使用LENGTH函数返回值类型为BIGINT，Hive兼容模式下使用LENGTH函数返回值类型为INT。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select length('hello');
--返回bigint类型

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select length('hello');
--返回int类型

    • Hive

      explain select length('hello');
--返回int类型

  • LENGTHB

    普通模式下使用LENGTHB函数返回值类型为BIGINT，Hive兼容模式下使用LENGTHB函数返回值类型为INT。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select lengthb('hello');
--返回bigint类型

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select lengthb('hello');
--返回int类型

    • Hive不支持该函数。

  • LN

    当输入参数超范围时，同时为两种模式开启严格模式（odps.function.strictmode=true），在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select ln(a) from values(-1.0) t(a);
-- 报错，输入参数超出范围

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select ln(a) from values(-1.0) t(a);
-- 返回NULL

    • Hive

      select ln(-1.0) ;
-- 返回NULL

  • LOCATE

    普通模式下使用LOCATE函数返回值类型为BIGINT，Hive兼容模式下使用LOCATE函数返回值类型为INT。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select locate('ab', 'abchelloabc');
--返回bigint类型

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select locate('ab', 'abchelloabc');
--返回int类型

    • Hive

      explain select locate('ab', 'abchelloabc');
--返回的int类型

  • LOG

    当输入参数超范围时，同时为两种模式开启严格模式（odps.function.strictmode=true），在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set oodps.function.strictmode=true;
select log(a, 10) from values(-3.0) t(a);
-- 报错，输入参数超出范围

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select log(a, 10) from values(-3.0) t(a);
-- 返回NULL

    • Hive

      select log(-3.0, 10) ;
-- 返回NULL

  • MOD

    当输入参数不合法时，同时为两种模式开启严格模式（odps.function.strictmode=true），在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select  1L % a from values(0L) t(a);
-- 报错，数据溢出

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select  1L % a from values(0L) t(a);
-- 返回NULL

    • Hive

      select  1L % 0L;
-- 返回NULL

  • PMOD

    当输入参数不合法时，同时为两种模式开启严格模式（odps.function.strictmode=true），在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select pmod(1L, a) from values(0L) t(a);
-- 报错，数据溢出

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select pmod(1L, a) from values(0L) t(a);
-- 返回NULL

    • Hive

      select pmod(1L, 0L) ;
-- 返回NULL

  • POW

    当计算结果溢出时，同时为两种模式开启严格模式（odps.function.strictmode=true），在普通模式下可能会报错，在Hive兼容模式下会返回INF。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strict.mode=true;
select pow(a, 1000L) from values(1000L) t(a);
-- 报错，数据溢出

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strict.mode=true;
select pow(a, 1000L) from values(1000L) t(a);
-- 返回INF

    • Hive

      select pow(1000L, 1000L) ;
-- 返回INF

  • REPEAT

    当REPEAT的个数小于零时，在普通模式下会报错，在Hive兼容模式下会返回空字符串。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select repeat('hi', n) from values (-1L) t(n);
--报错，输入参数超出范围

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select repeat('hi', n) from values (-1L) t(n);
--返回空字符串

    • Hive不支持该函数。

  • REVERSE

    当输入参数中包含中文字符时，在普通模式下按照字节来进行reverse操作，返回值可能有乱码；在Hive兼容模式下按照utf8来处理，不会出现乱码。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select reverse(a) from values ('hello中国world') t(a);
--返回结果中有乱码

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select reverse(a) from values ('hello中国world') t(a);
--返回结果中没有乱码

    • Hive

      select reverse('hello中国world') ;
--返回dlrow国中olleh

  • ROUND

    在普通模式下使用ROUND函数，本质上只支持DOUBLE或DECIMAL两种输入数据类型，其他数据类型会转换为这两种类型；在Hive兼容模式下，支持：DOUBLE、DECIMAL、BIGINT、INT、SMALLINT、TINYINT等数据类型。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select round(a) from values(2L) t(a);
--从执行计划看，输入数据从bigint类型转换成double类型，计算结果是double类型

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select round(a) from values(2L) t(a);
--从执行计划看，输入bigint类型，输出是bigint类型

    • Hive

      explain select round(2L) ;
--从执行计划看，输入bigint类型，输出是bigint类型

  • SIGN

    • 输入参数数据类型为DECIMAL时，普通模式下返回BIGINT类型，Hive兼容模式下返回INT类型。

    • 输入参数数据类型为DOUBLE时，普通模式下如果输入参数的绝对值和0非常接近，则返回值为0。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;

explain select sign(a) from values(2BD) t(a);
--从执行计划看，返回bigint类型

select sign(a) from values (0.000000000000009) t(a);
--返回值是0.0，因为输入参数和0非常紧急

    • Hive兼容模式

      set odps.sql.hive.compatible=true;

explain select sign(a) from values(2BD) t(a);
--从执行计划看，返回int类型

select sign(a) from values (0.000000000000009) t(a);
--返回值是1.0

    • Hive

      explain select sign(2BD);
--从执行计划看，返回int类型

select sign(0.000000000000009) ;
--返回值是1

  • SIZE

    • 普通模式下使用SIZE函数返回值类型为BIGINT，Hive兼容模式下使用SIZE函数返回值类型为INT。

    • 输入参数为NULL时，普通模式下返回NULL，Hive兼容模式返回-1。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
explain select size(array('a','b'));
--从执行计划看，返回bigint类型

select size(a) from values (cast(NULL as array<bigint>)) t(a);
--返回结果是NULL

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
explain select size(array('a','b'));
--从执行计划看，返回int类型

select size(a) from values (cast(NULL as array<bigint>)) t(a);
--返回结果是-1

    • Hive

      explain select size(array('a','b'));
--从执行计划看，返回int类型

  • SPLIT

    当输入参数的分隔符为空字符串时，普通模式下返回空数组，Hive兼容模式下按照UTF-8格式分割输入字符串。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
select split(a, '') from values ('hello中国world') t(a);
--返回空array

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
select split(a, '') from values ('hello中国world') t(a);
--返回[, h, e, l, l, o, 中, 国, w, o, r, l, d, ]

    • Hive

      select split('hello中国world', '') ;
--返回["h","e","l","l","o","中","国","w","o","r","l","d",""]

  • SQRT

    当输入参数小于0时，同时为两种模式开启严格模式（odps.function.strictmode=true），在普通模式下可能会报错，在Hive兼容模式下会返回NULL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select sqrt(a) from values (-100.0) t(a);
-- 报错，输入参数小于0

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select sqrt(a) from values (-100.0) t(a);
-- 返回NULL

    • Hive

      select sqrt(-100.0);
-- 返回NULL

  • SUBSTR

    当输入参数中的起始位置为0时，普通模式下返回空字符串，Hive兼容模式下与起始位置为1时相同。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select substr(a, 0) from values ('hello, world') t(a);
-- 返回空字符串

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select substr(a, 0) from values ('hello, world') t(a);
-- 返回如下结果：
+-----+
| _c0 |
+-----+
| hello, world |
+-----+

    • Hive

      select substr('hello, world', 0);
-- 返回如下结果：
+-----+
| _c0 |
+-----+
| hello, world |
+-----+

  • UNIX_TIMESTAMP

    普通模式下不支持两个输入参数类型为STRING，会报错；Hive兼容模式下支持两个STRING类型的输入参数，如下所示，返回值为BIGINT类型，通过format指定时间格式，时间格式请参见SimpleDateFormat。

    bigint FROM_UNIXTIME(string timeString, String format)

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;
select unix_timestamp(a) from values ('99999-01-01 00:00:00');
-- 报错，输入参数不合法

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;
select unix_timestamp(a) from values ('99999-01-01 00:00:00');
-- 返回NULL

    • Hive

      select unix_timestamp('2022/7/8', 'yyyy/mm/dd');
--返回结果如下：
+------------+
| _c0        |
+------------+
| 1641571620 |
+------------+
select unix_timestamp('99999-01-01 00:00:00') ;
--返回：3093496416000
select unix_timestamp('99999-01-01 00:00:00');
--返回：3093496416000

  • URL_DECODE

    当输入参数不合法时，且同时为两种模式开启严格模式（odps.function.strictmode=true），普通模式返回可能会报错，Hive兼容模式返回NULL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select url_decode(a) from values ('%2') t(a);
-- 报错，输入参数不合法

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select url_decode(a) from values ('%2') t(a);
-- 返回NULL

    • Hive不支持该函数。

  • URL_ENCODE

    当输入参数不合法或者转换失败时，且同时为两种模式开启严格模式（odps.function.strictmode=true），普通模式返回可能会报错，Hive兼容模式返回NULL。

    示例如下。

    • 普通模式

      set odps.sql.hive.compatible=false;
set odps.function.strictmode=true;

select url_encode(a, 'ascii') from values ('示例') t(a);
-- 报错，输入参数不合法

    • Hive兼容模式

      set odps.sql.hive.compatible=true;
set odps.function.strictmode=true;

select url_encode(a, 'ascii') from values ('示例') t(a);
-- 返回NULL

    • Hive不支持该函数。

上一篇：与Hive、MySQL、Oracle内建函数对照表下一篇：运算符
文档推荐