This topic describes the MaxCompute V1.0 data type edition in terms of the definition, supported data types, and differences with other data type editions. The MaxCompute V1.0 data type edition is one of the three data type editions of MaxCompute.

Definition

If the MaxCompute V1.0 data type edition is used in your project, the data types are defined as follows: 
setproject odps.sql.type.system.odps2=false;-- Disables MaxCompute V2.0 data types.
setproject odps.sql.decimal.odps2=false;-- Disables the DECIMAL type in MaxCompute V2.0.
setproject odps.sql.hive.compatible=false;-- Disables Hive-compatible data types.

Scenarios

The MaxCompute V1.0 data type edition is suitable for early MaxCompute projects whose dependent components do not support the MaxCompute V2.0 data type edition.

Date types

Data type Constant Description
BIGINT 100000000000L and -1L The 64-bit signed integer type.

Valid values: -263+1 to 2 63-1.

DOUBLE 3.1415926 1E+7 The 64-bit binary floating point type.
DECIMAL 3.5BD and 99999999999.9999999BD The precise numeric type based on the decimal system.

Valid values of the integer part: -1036+1 to 1036-1. Decimal place: accurate to 10-18. It is a 54-digit value. The integer part consists of 36 digits. The decimal part consists of 18 digits.

STRING "abc", 'bcd', "alibaba", and 'inc' The string type. The maximum length is 8 MB.
DATETIME DATETIME '2017-11-11 00:00:00' The date and time type.

Valid values: 0000-01-01 to 9999-12-31, accurate to the millisecond.
BOOLEAN True and False The BOOLEAN type.

Valid values: True and False.
The data types are described as follows:
  • All the preceding types can contain NULL values.
  • By default, an integer constant is processed as the BIGINT type. If a constant exceeds the value range of the BIGINT type, for example, 1,000,000,000,000,000,000,000,000, the constant is processed as the DOUBLE type. For example, integer constant 1 in SELECT 1 + a; is processed as the BIGINT type.
  • If built-in functions require the MaxCompute V2.0 data type edition, these functions cannot be executed if the MaxCompute V1.0 data type edition is used.
  • The data type of a partitioning column can only be STRING.
  • STRING constants can be combined. For example, abc and xyz can be combined as abcxyz.
  • If a constant is inserted into a field of the DECIMAL type, the expression of the constant must conform to the format in the constant definition, for example, 3.5BD in the following sample code: 
    insert into test_tb(a) values (3.5BD)
  • Time values of the DATETIME type do not include the millisecond component. You can add -dfp in Tunnel commands to display milliseconds in the time values, for example, tunnel upload -dfp 'yyyy-MM-dd HH:mm:ss.SSS'. For more information about Tunnel commands, see Tunnel commands.

Differences between the MaxCompute V1.0 data type edition and other data type editions

  • The behavior of the LIMIT statement in set operations that uses the MaxCompute V1.0 data type edition is different from that uses the MaxCompute V2.0 data type edition.
    Use the SELECT * FROM t1 UNION ALL SELECT * FROM t2 limit 10; statement as an example.
    • If the MaxCompute V1.0 data type edition is used, it is expressed as SELECT * FROM t1 UNION ALL SELECT * FROM ( SELECT * FROM t2 limit 10) t2;.
    • If the MaxCompute V2.0 data type edition is used, it is expressed as SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2 ) t limit 10;.

    This difference applies to the ORDER BY, DISTRIBUTE BY, SORT BY, and CLUSTER BY statements.

  • The parsing of data types in IN expressions that use the MaxCompute V1.0 data type edition is different from that use the MaxCompute V2.0 data type edition.
    Use the a in (1, 2, 3) expression as an example.
    • If the MaxCompute V1.0 data type edition is used, all the values in the parentheses () must be of the same type.
    • If the MaxCompute V2.0 data type edition is used, all the values in the parentheses () can be implicitly converted to the same type.

Complex data types

Data type Definition Constructor
ARRAY
  • ARRAY<BIGINT>
  • ARRAY<STRUCT<a:BIGINT, b:STRING>>
  • ARRAY(1, 2, 3)
  • ARRAY(ARRAY(1, 2), ARRAY(3, 4))
MAP
  • MAP<STRING, STRING>
  • MAP<BIGINT, ARRAY<STRING>>
  • MAP("k1", "v1", "k2", "v2")
  • MAP(1S, ARRAY('a', 'b'), 2S, ARRAY('x', 'y'))
STRUCT
  • STRUCT<'x', BIGINT, 'y', BIGINT>
  • STRUCT<'field1', BIGINT, 'field2', ARRAY<BIGINT>, 'field3', MAP<BIGINT>>
  • NAMED_STRUCT('x', 1, 'y', 2)
  • NAMED_STRUCT('field1', 100L, 'field2', ARRAY(1, 2), 'field3', MAP(1, 100, 2, 200))
Note Complex data types of MaxCompute can be nested. For more information, see ARRAY, MAP, and STRUCT.