文档

普通模式和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_encodingto_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不支持该函数。

  • 本页导读 (1)