MaxCompute V2.0 data type edition - MaxCompute - Alibaba Cloud Documentation Center

This topic describes the definition, use cases, supported data types, and differences from other data type editions for the MaxCompute V2.0 data type edition.

Background

The MaxCompute V2.0 data type edition is one of three data type editions in MaxCompute.

This edition is suitable for MaxCompute projects that do not contain data generated before April 2020 and whose dependent product components support the V2.0 data type edition.

Enable the V2.0 data type edition

To use the V2.0 data type edition for a project, configure the following data type properties:

setproject odps.sql.type.system.odps2=true; -- Enable the MaxCompute V2.0 data type edition.
setproject odps.sql.decimal.odps2=true; -- Enable the DECIMAL V2.0 data type.
setproject odps.sql.hive.compatible=false; -- Disable Hive-compatible mode.

Basic data types

Type	Constant example	Description
TINYINT	1Y, -127Y	8-bit signed integer. Valid values: -128 to 127.
SMALLINT	32767S, -100S	16-bit signed integer. Valid values: -32768 to 32767.
INT	1000, -15645787	32-bit signed integer. Valid values: -2³¹ to 2³¹-1.
BIGINT	100000000000L, -1L	64-bit signed integer. Valid values: -2⁶³+1 to 2⁶³-1.
BINARY	UNHEX('FA34E10293CB42848573A4E39937F479') X'616263'	Binary data type. Maximum length: 8 MB. In the `X'num [...]'` format, `num` is a hexadecimal digit that can be `0-9` or `A-F`. For example, `X'616263'` represents `abc` because the ASCII code of `a` is `0x61`, the ASCII code of `b` is `0x62`, and the ASCII code of `c` is `0x63`. `X'616263'` is semantically equivalent to `unhex('616263')`. If the hexadecimal string has an odd number of digits, `0` is prepended. For example, `X'616'` is equivalent to `X'0616'`. Single quotes must be used. Double quotes are not supported. For example, `X"616263"` is not interpreted as a BINARY constant.
FLOAT	3.14F, CAST(3.14159261E+7 AS FLOAT)	32-bit binary floating point. FLOAT values are subject to precision loss due to how floating-point values are stored and computed. If high precision is required, convert the FLOAT data type to the DECIMAL type.
DOUBLE	3.14D, 3.14159261E+7	64-bit binary floating point. DOUBLE values are subject to precision loss due to how floating-point values are stored and computed. If high precision is required, convert the DOUBLE data type to the DECIMAL type.
DECIMAL(precision,scale)	3.5BD, 99999999999.9999999BD	Exact numeric type based on the decimal system. Supports setting precision (the maximum number of digits) and scale (the number of digits to the right of the decimal point). Regular DECIMAL: precision ranges from [1,38], and scale ranges from [0, precision]. DECIMAL256: precision ranges from [39,76], and scale ranges from [0, precision]. For more information, see DECIMAL data type.
VARCHAR(n)	N/A	Variable-length character type. n specifies the length. Valid values of n: 1 to 65535.
CHAR(n)	N/A	Fixed-length character type. n specifies the length. Maximum value of n: 255. Strings shorter than n are padded with spaces, but trailing spaces are not included in comparisons.
STRING	"abc", 'bcd', "alibaba", 'inc'	String type. Maximum length: 8 MB.
DATE	DATE'2017-11-11'	Date type. Format: `yyyy-mm-dd`. Valid values: 0001-01-01 to 9999-12-31.
DATETIME	DATETIME'2017-11-11 00:00:00'	Date and time type. Valid values: 0001-01-01 00:00:00.000 to 9999-12-31 23:59:59.999. Precision: millisecond. DATETIME values do not include the millisecond component in query results. Use the `-dfp` parameter in Tunnel commands to specify millisecond-level display. For example, `tunnel upload -dfp 'yyyy-MM-dd HH:mm:ss.SSS'`. For more information about Tunnel commands, see Tunnel commands.
TIMESTAMP	TIMESTAMP'2017-11-11 00:00:00.123456789'	Timestamp type. Valid values: 0001-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999. Precision: nanosecond. The TIMESTAMP type is time zone independent. In any time zone, the TIMESTAMP type stores an offset from Epoch (UTC 1970-01-01 00:00:00). Built-in functions can be used to perform time zone-related calculations on TIMESTAMP data. For example, `CAST(<a TIMESTAMP> AS STRING)` converts TIMESTAMP data to the STRING type based on the current time zone. TIMESTAMP constants with time zone-specific formats are supported, such as: TIMESTAMP '2025-02-25T12:09:35' , TIMESTAMP '2025-02-25 12:09:35+07:00', and TIMESTAMP '2025-02-25 12:09:35Z'.
TIMESTAMP_NTZ	TIMESTAMP_NTZ '2017-11-11 00:00:00.123456789'	Timestamp type independent from time zones. Valid values: 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999. For more information about the TIMESTAMP_NTZ data type, see TIMESTAMP_NTZ data type.
BOOLEAN	True, False	BOOLEAN type. Valid values: True, False.
INTERVAL	INTERVAL '2021' YEAR INTERVAL '1' DAY INTERVAL '2000-1' YEAR TO MONTH INTERVAL '-1 23:59:59.999' DAY TO SECOND	A data type that represents time periods and is used to express the time interval between two dates or times. Includes two subtypes: INTERVAL_YEAR_MONTH and INTERVAL_DAY_TIME. For more information, see INTERVAL data type.
Blob	N/A	For more information, see Blob data type and multimodal storage.

Usage notes for data types:

All data types listed above support NULL values.
The INT keyword in SQL refers to the 32-bit integer type.
```
-- Convert a to a 32-bit integer.
CAST(a AS INT)
```
Constants
- Integer constants default to the INT type. For example, the integer constant 1 in SELECT 1 + a; is treated as INT. If a constant exceeds the INT range but fits within the BIGINT range, it is treated as BIGINT. If it exceeds the BIGINT range, it is treated as DOUBLE.
- When inserting constants into DECIMAL fields, the constant format must match the format specified in the constant definition. For example, the 3.5BD in the following sample code.
```
INSERT INTO test_tb(a) VALUES (3.5BD)
```
- STRING constants support concatenation. Two or more adjacent string literals are automatically concatenated. For example, SELECT 'abc' 'efg' 'ddt'; returns abcefgddt.
Implicit conversion
- Some implicit type conversions are disabled. For example, STRING->BIGINT, STRING->DATETIME, DOUBLE->BIGINT, DECIMAL->DOUBLE, and DECIMAL->BIGINT may cause precision loss or errors. Use the CAST function to explicitly convert data types that are disabled for implicit conversion.
- VARCHAR constants can be implicitly converted to STRING constants.
Tables, functions, and UDFs
- Built-in functions that use V2.0 data types can be used in the V2.0 data type edition.
- Data types in UDFs are parsed and overloaded based on V2.0 data types.
- Partition key columns support the STRING, VARCHAR, CHAR, TINYINT, SMALLINT, INT, and BIGINT data types.

Complex data types

Complex data types in MaxCompute can be nested up to 20 levels. For information about related built-in functions, see ARRAY, MAP, STRUCT, or JSON.
The maximum size of complex data type values is recommended to be no more than 1 MB. Values exceeding this limit may cause out-of-memory (OOM) errors during computation.

Type	Definition	Construction
ARRAY	`ARRAY<INT>` `ARRAY<STRUCT<a:INT, b:STRING>>`	`ARRAY(1, 2, 3)` `ARRAY(STRUCT(1, '2'), STRUCT(3, '4'))`
MAP	`MAP<STRING, STRING>` `MAP<SMALLINT, ARRAY<STRING>>`	`MAP("k1", "v1","k2","v2")` `MAP(1S, ARRAY("a", "b"), 2S, ARRAY('z','y'))`
STRUCT	`STRUCT<X:INT, Y:INT>` `STRUCT<FIELD1:BIGINT, FIELD2:ARRAY<INT>, FIELD3:MAP<INT, INT>>`	`NAMED_STRUCT('x', 1,'y',2)` `NAMED_STRUCT('field1',100L,'field2', ARRAY(1, 2),'field3',MAP(1,100, 2, 200))`
JSON	`JSON`	`JSON '123'`

Differences from other data type editions

DML syntax behavior differences
- The LIMIT clause behaves differently in set operations.
  For example, for SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 10;:
  - V1.0 data type edition: SELECT * FROM t1 UNION ALL SELECT * FROM ( SELECT * FROM t2 LIMIT 10) t2;.
  - V2.0 data type edition: SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2 ) t LIMIT 10;.
  The ORDER BY, DISTRIBUTE BY, SORT BY, and CLUSTER BY clauses also exhibit the same behavior.
- Type resolution in IN expressions differs.
  For example, for a in (1, 2, 3):
  - V1.0 data type edition: all values in the parentheses after IN must be of the same type.
  - V2.0 data type edition: values in the parentheses after IN can be of different types as long as they support implicit type conversion to a common type.
- INSERT type conversion rules
  - In Hive-compatible mode, if a source data type can be explicitly converted to the target column type, MaxCompute automatically inserts a conversion function and runs it.
  - In V1.0 and V2.0 modes, the source type must be implicitly convertible; otherwise an error is returned.
    The following succeeds in Hive-compatible mode but fails in V1.0 and V2.0 modes:
```
CREATE TABLE t (a BIGINT);
INSERT INTO TABLE SELECT 1.5;
```
Operator and function behavioral differences across data type editions
- For the +, -, *, /, and POW operators:
  - Hive-compatible:Returns the initial value
  - V1.0 and V2.0:Returns an error; in other modes, NULL is returned
- For the >, >=, =, <, and <= operators applied to DOUBLE values:
  - Hive-compatible:Compares all digits directly
  - V1.0 and V2.0:Compares only the first 15 digits to the right of the decimal point; remaining digits are ignored
- For the &, |, and ^ operators:
  - Hive-compatible:Same type as the input
  - V1.0 and V2.0:Always BIGINT
- For function like:LENGTH,LENGTHB,FIND_IN_SET,INSTR,SIZE,HASH,SIGN
  - Hive-compatible:Returns a INT value.
  - V1.0 and V2.0:Returns a BIGINT value.
- FLOOR,CEIL
  - Hive-compatible: If the input parameter is of the DECIMAL type, a DECIMAL value is returned.
  - V1.0 and V2.0: If the input parameter is of the DECIMAL type, a BIGINT value is returned.
- FROM_UNIXTIME
  - Hive-compatible:Returns a STRING value.
  - V1.0 and V2.0:Returns a DATETIME value.
- CONCAT_WS
  - Hive-compatible:NULL input strings are ignored; remaining strings are concatenated.
  - V1.0 and V2.0:If any input string is NULL, NULL is returned.
- FIND_IN_SET
  - Hive-compatible:an empty string matches the tail of the search string:
```
find_in_set("","")    -- Returns 1
find_in_set("", "a,") -- Returns 2
```
  - V1.0 and V2.0:An empty string is treated as unmatched and 0 is returned.
- REGEXP_(EXTRACT/REPLACE)
  - Hive-compatible:Java regular expression specifications
  - V1.0 and V2.0:MaxCompute specifications
- SUBSTR
```
STRING SUBSTR(STRING <string>, BIGINT <start_position>[, BIGINT <length>])
```
  start_position: Required. BIGINT type. The default start position is 1.
  - Hive-compatible data type edition: When start_position is 0, the behavior is the same as when the start position is 1.
  - 1.0 and 2.0 data type editions: When start_position is 0, an empty string is returned.
  - BigQuery-compatible data type edition: When start_position is 0, the behavior is the same as when the start position is 1.
  - BigQuery-compatible data type edition: When start_position is less than the negative length of the string, the behavior is the same as when the start position is 1.