MaxCompute data type version 2.0 - MaxCompute - Alibaba Cloud Documentation Center

This topic describes MaxCompute data type version 2.0, including its definition, use cases, supported data types, and its differences from other data type versions.

Background

Data type version 2.0 is one of the three data type versions available in MaxCompute.

It is suitable for MaxCompute projects that had no existing data before April 2020, provided that all dependent product components support data type version 2.0.

Enabling data type version 2.0

To use data type version 2.0 in a project, set the following properties.

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

Basic data types

Type	Literal	Description
TINYINT	1Y, -127Y	An 8-bit signed integer. Value range: -128 to 127.
SMALLINT	32767S, -100S	A 16-bit signed integer. Value range: -32768 to 32767.
INT	1000, -15645787	A 32-bit signed integer. Value range: -2³¹ to 2³¹-1.
BIGINT	100000000000L, -1L	A 64-bit signed integer. Value range: -2⁶³ to 2⁶³-1.
BINARY	UNHEX('FA34E10293CB42848573A4E39937F479') X'616263'	A binary data type. Maximum length: 8 MB. In the `X'num [...]'` format, `num` is a hexadecimal digit (`0-9` or `A-F`). For example, `X'616263'` represents `abc` because the ASCII code for the character `'a'` is `0x61`, for `'b'` is `0x62`, and for `'c'` is `0x63`. `X'616263'` is semantically equivalent to `unhex('616263')`. If the string has an odd number of digits, the system pads it with a leading `0`. For example, `X'616'` is equivalent to `X'0616'`. You must use single quotes ('). Double quotes (") are not supported. For example, `X"616263"` is not interpreted as a BINARY literal.
FLOAT	3.14F, CAST(3.14159261E+7 AS FLOAT)	A 32-bit binary floating-point type. The FLOAT type may lose precision due to how computers store and perform internal calculations. If high precision is required, convert FLOAT values to the DECIMAL data type.
DOUBLE	3.14D, 3.14159261E+7	A 64-bit binary floating-point type. The DOUBLE type may lose precision due to how computers store and perform internal calculations. If high precision is required, convert DOUBLE values to the DECIMAL data type.
DECIMAL(precision,scale)	3.5BD, 99999999999.9999999BD	An exact numeric type. You can specify the precision (the total number of digits) and the scale (the number of digits to the right of the decimal point). Normal DECIMAL: The precision is in the range [1, 38], and the scale is in the range [0, precision]. DECIMAL256: The precision is in the range [39, 76], and the scale is in the range [0, precision]. For more information, see DECIMAL data type.
VARCHAR(n)	None	A variable-length character type, where n is the length. Value range for n: 1 to 65535.
CHAR(n)	None	A fixed-length character type of length n, with a maximum value of 255. Shorter strings are padded with spaces to the defined length. These trailing spaces are ignored during comparisons.
STRING	"abc", 'bcd', "alibaba", 'inc'	A string type. Maximum length: 8 MB.
DATE	DATE'2017-11-11'	A date type. The format is `yyyy-mm-dd`. Value range: 0001-01-01 to 9999-12-31.
DATETIME	DATETIME'2017-11-11 00:00:00'	A datetime type. Value range: 0001-01-01 00:00:00.000 to 9999-12-31 23:59:59.999, with millisecond precision. DATETIME query results do not display milliseconds. You can use the `-dfp` option in Tunnel commands to specify a format that includes milliseconds, 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'	A timestamp type. Value range: 0001-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999, with nanosecond precision. The TIMESTAMP type is timezone-agnostic. It stores a specific point in time as an offset from the Unix epoch (1970-01-01 00:00:00 UTC). Use built-in functions to perform timezone-aware calculations on TIMESTAMP data. For example, `CAST(<a TIMESTAMP> AS STRING)` converts a TIMESTAMP value to a STRING based on the current session's time zone. TIMESTAMP literals also support special formats that include time zone information, 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'	A timezone-free timestamp type. Value range: 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	A BOOLEAN type. Value range: True or 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 a time interval between two dates or times. It includes two types: INTERVAL_YEAR_MONTH and INTERVAL_DAY_TIME. For more information, see INTERVAL data type.
Blob	None	For more information, see Blob data type and multimodal storage.
VECTOR	[1.1, 2.2, 3.3]	A vector type that consists of a fixed number of FLOAT elements. It represents high-dimensional embedding vectors, such as the semantic features of text or images. The number of dimensions can range from 1 to 4,096. For more information, see VECTOR data type.
GEOGRAPHY	`ST_GEOGPOINT(116.4, 39.9)` `ST_GEOGFROMTEXT('POINT (10 20)')`	Geospatial data type. Used to represent geometric objects on the Earth's surface, such as points (Point), lines (LineString), and polygons (Polygon). Supports composite types including MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection. Coordinates are specified in (longitude, latitude) format in degrees, following the WGS84 geographic coordinate system, and implemented based on the S2 Geometry library. Longitude must be in the range [-180, 180], and latitude must be in the range [-90, 90]. Must be constructed via Geography functions. Literal input is not supported. For more information, see GEOGRAPHY data type.

Notes on data types:

All data types listed above can be NULL.

The INT keyword in SQL is a 32-bit integer.

-- Convert a to a 32-bit integer.
CAST(a AS INT)

Literals
- Integer literals are treated as the INT type by default. For example, in SELECT 1 + a;, the integer literal 1 is processed as an INT. If a literal exceeds the value range of INT but falls within the range of BIGINT, it is treated as a BIGINT. If it exceeds the range of BIGINT, it is treated as a DOUBLE.
- When you insert a literal into a DECIMAL column, the literal must conform to the format specified in its definition. For example, see the 3.5BD literal in the following code.
```
INSERT INTO test_tb(a) VALUES (3.5BD)
```
- STRING literals support concatenation. When two or more string literals are placed next to each other, they are automatically combined into a single string. For example, SELECT 'abc' 'efg' 'ddt'; returns abcefgddt.
Implicit conversion
- Some implicit conversions are disabled. For example, conversions from STRING to BIGINT, STRING to DATETIME, DOUBLE to BIGINT, DECIMAL to DOUBLE, and DECIMAL to BIGINT are disabled because they can cause precision loss or errors. Use the CAST function to perform these conversions explicitly.
- VARCHAR literals can be implicitly converted to STRING literals.
Tables, functions, and UDFs
- Built-in functions that accept data type version 2.0 parameters work as expected.
- The data types in a User-Defined Function (UDF) are resolved and overloaded based on data type version 2.0.
- Partition 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 deep. For more information about related built-in functions, see ARRAY, MAP, STRUCT, or JSON.
The size of a complex data type should not exceed 1 MB. Complex data types larger than 1 MB can cause out-of-memory (OOM) errors during computation.

Type	Definition	Constructor
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 versions

Differences in DML syntax behavior
- Behavior of the LIMIT clause in set operations
  For example, consider the query SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 10;:
  - In data type version 1.0, this is equivalent to SELECT * FROM t1 UNION ALL SELECT * FROM ( SELECT * FROM t2 LIMIT 10) t2;.
  - In data type version 2.0, this is equivalent to SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2 ) t LIMIT 10;.
  The ORDER BY, DISTRIBUTE BY, SORT BY, and CLUSTER BY operations exhibit the same behavior.
- Type resolution for IN expressions
  For example, in the expression a in (1, 2, 3):
  - In data type version 1.0, all values in the list following IN must have the same data type.
  - In data type version 2.0, the values only need to be implicitly convertible to a common data 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.