The MaxCompute V2.0 data type edition is one of the three data type editions of MaxCompute. This topic describes the MaxCompute V2.0 data type edition in terms of its definition, supported data types, and differences between this edition and other data type editions.
Definition
setproject odps.sql.type.system.odps2=true; -- Enable MaxCompute V2.0 data types.
setproject odps.sql.decimal.odps2=true; -- Enable the DECIMAL data type in MaxCompute V2.0.
setproject odps.sql.hive.compatible=false; -- Disable Hive-compatible data types.
Scenarios
The MaxCompute V2.0 data type edition is suitable for scenarios in which your project does not contain data generated before April 2020 and depends on components that support the MaxCompute V2.0 data type edition.
Basic data types
Data type | Constant example | Description |
---|---|---|
TINYINT | 1Y and -127Y | The 8-bit signed integer type.
Valid values: -128 to 127. |
SMALLINT | 32767S and -100S | The 16-bit signed integer type.
Valid values: -32768 to 32767. |
INT | 1000 and -15645787 | The 32-bit signed integer type.
Valid values: -231 to 231 -1. |
BIGINT | 100000000000L and -1L | The 64-bit signed integer type.
Valid values: -263 + 1 to 263 -1. |
BINARY | unhex('FA34E10293CB42848573A4E39937F479') | A binary number. The maximum length is 8 MB. |
FLOAT | cast(3.14159261E+7 as float) | The 32-bit binary floating point type. |
DOUBLE | 3.14159261E+7 | The 64-bit binary floating point type. |
DECIMAL(precision,scale) | 3.5BD and 99999999999.9999999BD | The precise numeric type based on the decimal system.
If the two parameters are not specified, the default expression of this data type
is Note
|
VARCHAR(n) | None | The variable-length character type, in which n specifies the length.
Valid values: 1 to 65535. |
CHAR(n) | None | The fixed-length character type, in which n specifies the length. The maximum value is 255. If the length does not reach the specified value, extra spaces are automatically filled but are not involved in the comparison. |
STRING | "abc", 'bcd', "alibaba", and 'inc' | The string type. The maximum length is 8 MB. |
DATE | DATE'2017-11-11' | The DATE type, in the format of yyyy-mm-dd .
Valid values: 0000-01-01 to 9999-12-31. |
DATETIME | DATETIME'2017-11-11 00:00:00' | The DATETIME type.
Valid values: 0000-01-01 00:00:00.000 to 9999-12-31 23:59:59.999, accurate to the millisecond. |
TIMESTAMP | TIMESTAMP'2017-11-11 00:00:00.123456789' | The TIMESTAMP type.
Valid values: 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999, accurate to the nanosecond. Note The timestamp is independent of time zones. In any time zone, the timestamp stores
a date offset value from Epoch (UTC 1970-01-01 00:00:00). You can use built-in functions
to calculate data of the TIMESTAMP type based on time zones. For example, you can
use the
cast(<a timestamp> as string) function to convert data of the TIMESTAMP type into the STRING type based on the
current time zone.
|
BOOLEAN | True and False | The BOOLEAN type.
Valid values: True and False. |
- All the preceding data types support NULL values.
- The INT keyword in an SQL statement refers to the 32-bit integer type.
-- Convert a into a 32-bit integer. cast(a as INT)
- By default, an integer constant is processed as the INT type. For example, integer
constant 1 in
SELECT 1 + a;
is processed as the INT type. If a constant exceeds the value range of the INT type but does not exceed the value range of the BIGINT type, the constant is processed as the BIGINT type. If the constant exceeds the value range of the BIGINT type, the constant is processed as the DOUBLE type. - Implicit conversions
- Some implicit conversions are disabled. If the data type is converted from STRING to BIGINT, from STRING to DATETIME, from DOUBLE to BIGINT, from DECIMAL to DOUBLE, or from DECIMAL to BIGINT, precision may be reduced, or errors may occur. You can use the CAST function to force the data type conversions.
- VARCHAR constants can be implicitly converted into STRING constants.
- Tables, built-in functions, and user-defined functions (UDFs)
- Built-in functions that require the MaxCompute V2.0 data type edition can be run.
- The data types defined in UDFs are parsed and overloaded based on the MaxCompute V2.0 data type edition.
- The data type of a partition key column can be STRING, VARCHAR, CHAR, TINYINT, SMALLINT, INT, or BIGINT.
- Constants of the STRING type can be concatenated. For example, abc and xyz can be concatenated 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. Example:
3.5BD
in the following sample code:insert into test_tb(a) values (3.5BD)
- Values of the DATETIME type do not include the millisecond component. You can add
-dfp
to Tunnel commands to specify the time format that is accurate to the millisecond, such astunnel upload -dfp 'yyyy-MM-dd HH:mm:ss.SSS'
. For more information about Tunnel commands, see Tunnel commands.
Complex data types
Data type | Definition | Constructor |
---|---|---|
ARRAY |
|
|
MAP |
|
|
STRUCT |
|
|
- Complex data types of MaxCompute can be nested. For more information about the related built-in functions, see ARRAY, MAP, or STRUCT.
- We recommend that the size of complex data types of MaxCompute not exceed 1 MB. If the size of complex data types of MaxCompute exceeds 1 MB, an out of memory (OOM) error may occur during calculation.
Differences between the MaxCompute V2.0 data type edition and other data type editions
- DML execution rules are different.
- The execution rules of LIMIT statements in set operations are different.
In this example, the
SELECT * FROM t1 UNION ALL SELECT * FROM t2 limit 10;
statement is used.- If the MaxCompute V1.0 data type edition is used, the statement 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, the statement is expressed as
SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2 ) t limit 10;
.
This difference also applies to the ORDER BY, DISTRIBUTE BY, SORT BY, and CLUSTER BY clauses.
- If the MaxCompute V1.0 data type edition is used, the statement is expressed as
- The parsing of data types in IN expressions is different.
In this example, the
a in (1, 2, 3)
expression is used.- If the MaxCompute V1.0 data type edition is used, all values enclosed in parentheses () must be of the same type.
- If the MaxCompute V2.0 data type edition is used, all values enclosed in parentheses () can be implicitly converted into the same type.
- The conversion rules for INSERT statements are different.
- Hive-compatible data type edition: If a source data type can be explicitly converted into the data type of a table, MaxCompute automatically inserts a conversion function and runs it.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: A source
data type must be implicitly converted into the data type of a table. Otherwise, an
error is returned.
-- The following operations succeed in Hive-compatible mode but fail in other modes: create table t (a bigint); insert into table select 1.5;
- The execution rules of LIMIT statements in set operations are different.
- Function behavior is different.
+
,-
,*
,/
, and POW function- Hive-compatible data type edition: If the data exceeds the value range of a data type, the initial value is returned.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: If the data exceeds the value range of a data type, an error is returned. In other modes, the value null is returned.
>
,>=
,=
,<
, and<=
- Hive-compatible data type edition: The values of the DOUBLE type are directly compared.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: If the values of the DOUBLE type are compared, they are considered the same if the first 15 digits to the right of the decimal point are the same. Other digits after the decimal point are not compared.
- Bitwise operators:
&
,|
, and^
- Hive-compatible data type edition: A value of the same data type as the input parameter is returned.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: A value of the BIGINT type is returned.
- LENGTH, LENGTHB, FIND_IN_SET, INSTR, SIZE, HASH, and SIGN functions
- Hive-compatible data type edition: A value of the INT type is returned.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: A value of the BIGINT type is returned.
- FLOOR and CEIL
- Hive-compatible data type edition: If the input parameter is of the DECIMAL type, a value of the DECIMAL type is returned.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: If the input parameter is of the DECIMAL type, a value of the BIGINT type is returned.
- ROUND
- Hive-compatible data type edition: If the input parameter is of the TINYINT, SMALLINT, INT, or BIGINT type, a value of the same data type as the input parameter is returned.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: If the input parameter is of the TINYINT, SMALLINT, INT, or BIGINT type, a value of the DATETIME type is returned.
- FROM_UNIXTIME
- Hive-compatible data type edition: A value of the STRING type is returned.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: A value of the DATETIME type is returned.
- CONCAT_WS
- Hive-compatible data type edition: If a connected input string is NULL, the string is ignored.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: If a connected input string is NULL, NULL is returned.
- FIND_IN_SET
- Hive-compatible data type edition: An empty string is considered the matching of the
tail of the string.
-- Hive-compatible mode find_in_set("","") 1 is returned. find_in_set("", "a,") 2 is returned.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: An empty string is considered unmatched, and 0 is returned.
- Hive-compatible data type edition: An empty string is considered the matching of the
tail of the string.
- REGEXP_(EXTRACT/REPLACE)
- Hive-compatible data type edition: The REGEXP schema complies with the specifications of Java regular expressions.
- MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition: The REGEXP schema complies with MaxCompute specifications.
- SUBSTR
string substr(string <str>, bigint <start_position>[, bigint <length>])
start_position: required. A value of the BIGINT type. The default value is 1.
- Hive-compatible data type edition: If start_position is set to 0, the return value is the same as that when this parameter is set to 1.
- MaxCompute V1.0 and MaxCompute V2.0 data type editions: If start_position is set to 0, null is returned.