MaxCompute V1.0 Data Type Edition Overview: Supported Types & Limits - MaxCompute - Alibaba Cloud - MaxCompute

Background

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

This edition is intended for early MaxCompute projects whose dependent components do not support the V2.0 data type edition.

Project configuration

A MaxCompute project uses the V1.0 data type edition when all three of the following project properties are set:

setproject odps.sql.type.system.odps2=false; -- Disable MaxCompute V2.0 data types.
setproject odps.sql.decimal.odps2=false;      -- Disable the DECIMAL type in MaxCompute V2.0.
setproject odps.sql.hive.compatible=false;    -- Disable Hive-compatible data types.

Supported data types

Basic data types

All basic data types support NULL values.

Data type	Constant example	Description
BIGINT	`100000000000L` and `-1L`	64-bit signed integer. Range: -2⁶³ + 1 to 2⁶³ - 1.
DOUBLE	`3.14159261E+7`	64-bit binary floating point.
DECIMAL	`3.5BD` and `99999999999.9999999BD`	Exact numeric type. Default precision: `decimal(54,18)`. Precision and scale cannot be adjusted. Integer part range: -10³⁶ + 1 to 10³⁶ - 1. Decimal part: accurate to 10⁻¹⁸. For more information, see DECIMAL data type.
STRING	`"abc"`, `'bcd'`, `"alibaba"`, `'inc'`	String type. Maximum length: 8 MB.
DATETIME	`DATETIME'2017-11-11 00:00:00'`	Date and time type. Range: 0000-01-01 to 9999-12-31. DATETIME query results do not include millisecond precision. To display milliseconds, use the `-dfp` option in the Tunnel command to specify a time format. Example: `tunnel upload -dfp 'yyyy-MM-dd HH:mm:ss.SSS'`. For more information, see Tunnel commands.
BOOLEAN	`True` and `False`	Boolean type. Range: True and False.

Usage notes

constants
- Integer constants: Processed as BIGINT by default. If a constant exceeds the BIGINT range (for example, 1,000,000,000,000,000,000,000,000), it is processed as DOUBLE. For example, 1 in SELECT 1 + a; is BIGINT.
- DECIMAL constants: When inserting a DECIMAL constant into a field, use the BD suffix format. Example:
```
INSERT INTO test_tb(a) VALUES (3.5BD);
```
- STRING concatenation: STRING constants can be concatenated. For example, abc and xyz concatenate to abcxyz.
Partition key columns: Partition key columns of partitioned tables must be of the STRING type.
Built-in functions: Built-in functions that include parameters of MaxCompute V2.0 data types cannot be used under the V1.0 data type edition.

Differences from the MaxCompute V2.0 data type edition

The LIMIT, ORDER BY, DISTRIBUTE BY, SORT BY, and CLUSTER BY operations behave differently between the MaxCompute V1.0 data type edition and the MaxCompute V2.0 data type edition.
The following example uses SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 10;.
Edition
Equivalent statement
MaxCompute V1.0
SELECT * FROM t1 UNION ALL SELECT * FROM (SELECT * FROM t2 LIMIT 10) t2;
MaxCompute V2.0
SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) t LIMIT 10;
The IN expression behaves differently between the V1.0 and V2.0 data type editions.
The following example uses a IN (1, 2, 3).
Edition
Behavior
MaxCompute V1.0
All values in the parentheses must be of the same type.
MaxCompute V2.0
Values in the parentheses can be implicitly converted to the same type.

Complex data types

Complex data types can be nested within each other. For built-in functions related to complex types, see ARRAY, MAP, and STRUCT.

Data type	Definition	Constructor
ARRAY	`ARRAY<BIGINT>`, `ARRAY<STRUCT<a:BIGINT, b:STRING>>`	`ARRAY(1, 2, 3)`, `ARRAY(NAMED_STRUCT('a', 1, 'b', '2'), NAMED_STRUCT('a', 3, 'b', '4'))`
MAP	`MAP<STRING, STRING>`, `MAP<BIGINT, ARRAY<STRING>>`	`MAP("k1", "v1", "k2", "v2")`, `MAP(1L, ARRAY('a', 'b'), 2L, 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))`

Edition	Equivalent statement
MaxCompute V1.0	`SELECT * FROM t1 UNION ALL SELECT * FROM (SELECT * FROM t2 LIMIT 10) t2;`
MaxCompute V2.0	`SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) t LIMIT 10;`

Edition	Behavior
MaxCompute V1.0	All values in the parentheses must be of the same type.
MaxCompute V2.0	Values in the parentheses can be implicitly converted to the same type.