Data types-Dataphin(Dataphin)-阿里云帮助中心

Dataphin uses MaxCompute V2.0 as its compute engine. This topic describes the MaxCompute V2.0 data types that Dataphin supports.

Limitations

The data type system version 2.0 is intended for:

MaxCompute projects created after April 2020 that contain no pre-existing data.
Environments where all dependent product components are also compatible with the version 2.0 type system.

Basic data types

Data type	Constant example	Description
TINYINT	1Y and -127Y	8-bit signed integer. Valid values: -128 to 127.
SMALLINT	32767S and -100S	16-bit signed integer. Valid values: -32768 to 32767.
INT	1000 and -15645787	32-bit signed integer. Valid values: -2³¹ to 2³¹ -1.
BIGINT	100000000000 L and -1L	64-bit signed integer. Valid values: -2⁶³ + 1 to 2⁶³ -1.
BINARY	unhex('FA34E10293CB42848573A4E39937F479') X'616263'	Binary data type. The size limit is 8 MB. Note `num` in `X'num [...]'` is a hexadecimal value, which can be `0 to 9` or `A to F`. For example, `X'616263'` represents `abc` because `a` is `0x61`, `b` is `0x62`, and `c` is `0x63` in ASCII. `X'616263'` is semantically equivalent to `unhex('616263')`. If the length of the string is not an even number, the system adds `0` in front of the string. For example, `X'616'` is equivalent to `X'0616'`. You must enclose the string in single quotation marks (') instead of double quotation marks ("). For example, `X"616263"` is not interpreted as a constant of the BINARY type.
FLOAT	3.14F and cast(3.14159261E+7 as float)	32-bit binary floating-point number. Note A loss of precision occurs when data of the FLOAT type is calculated due to the computer storage and internal computing logic. In scenarios where high precision is required, you can convert data of the FLOAT type into data of the DECIMAL type.
DOUBLE	3.14D and 3.14159261E+7	64-bit binary floating-point number. Note A loss of precision occurs when data of the DOUBLE type is calculated due to the computer storage and internal computing logic. In scenarios where high precision is required, you can convert data of the DOUBLE type into data of the DECIMAL type.
DECIMAL(precision,scale)	3.5BD and 99999999999.9999999BD	The precise numeric type based on the decimal system. The default expression of this data type is `decimal(38,18)`. You can specify the precision and scale values. precision: indicates the maximum number of digits in a value. Valid values: `1 to 38`. scale: indicates the number of digits to the right of the decimal point in a value. Valid values: `0 to 18`. If a higher scale is needed, you can run the `set odps.sql.decimal2.extended.scale.enable=true;` command to extend the range of scale values. After you set this flag parameter to true, the range of scale values becomes 0 to 38. Note DECIMAL of the old and new editions cannot exist in the same table. The Hive-compatible data type edition is enabled by running the `setproject odps.sql.hive.compatible=true;` command. In this case, if the number of digits to the right of the decimal point in a value of the `Decimal(precision,scale)` type exceeds the value of the scale parameter during Tunnel-based data uploads or SQL operations, the value is rounded. If the integer part exceeds the limit, no error is reported, but the input data becomes NULL values. If the `odps.sql.decimal.tostring.trimzero` parameter is set to `true`, trailing zeros after the decimal point are removed. If this parameter is set to `false`, trailing zeros after the decimal point are retained. The default value is `true`. This parameter takes effect only when data is read from a table. This parameter does not take effect on static values.
VARCHAR(n)	No default value	The variable-length character type, in which n specifies the length. Valid values: 1 to 65535.
CHAR(n)	No default value	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 `yyyy-mm-dd` format. Valid values: 0001-01-01 to 9999-12-31.
DATETIME	DATETIME'2017-11-11 00:00:00'	The DATETIME type. Valid values: 0001-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: 0001-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 perform time zone-related computing on the data of the TIMESTAMP type. For example, you can use `cast(<a timestamp> as string)` to convert data of the TIMESTAMP type into the STRING type based on the current time zone.
TIMESTAMP_NTZ	TIMESTAMP_NTZ '2017-11-11 00:00:00.123456789'	TIMESTAMP data type, which is independent from time zones. Valid values: 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999.
BOOLEAN	True and False	The BOOLEAN type. Valid values: True and False.
INTERVAL	INTERVAL '2021' YEAR INTERVAL '1' DAY INTERVAL '2000-1' YEAR TO MONTH INTERVAL '-1 23:59:59.999' DAY TO SECOND	The INTERVAL type represents a time period and is used to express the interval between two dates or times. It includes two types: INTERVAL_YEAR_MONTH and INTERVAL_DAY_TIME.

General rules and behavior:

All data types are nullable.
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
- Certain implicit conversions (e.g., STRING to BIGINT, STRING to DATETIME, DOUBLE to BIGINT, DECIMAL to DOUBLE, DECIMAL to BIGINT) are disabled to prevent potential data loss or errors. Use the CAST function for explicit conversions.
- Constants of the VARCHAR type can be implicitly converted into the STRING type.
Tables, Functions, and UDFs
- Built-in functions that use 2.0 data types operate correctly in the 2.0 data type system.
- UDFs are resolved and overloaded based on the 2.0 data type system.
- Partition columns support STRING, VARCHAR, CHAR, TINYINT, SMALLINT, INT, and BIGINT data types.
MaxCompute automatically concatenates multiple adjacent string literals. For example, select 'abc' 'efg' 'ddt'; returns abcefgddt.
When inserting a constant into a DECIMAL field, the constant's format must match its definition. For example:
```
INSERT INTO test_tb(a) VALUES (3.5BD)
```
DATETIME queries do not display milliseconds by default. To display milliseconds, use the -dfp flag in Tunnel commands, such as tunnel 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	`array<int>` `array<struct<a:int, b:string>>`	`array(1, 2, 3)` `array(array(1, 2), array(3, 4))`
MAP	`map<string, string>` `map<smallint, array<string>>`	`map("k1", "v1", "k2", "v2")` `map(1S, array('a', 'b'), 2S, array('x', '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))`

Note

Complex data types of MaxCompute can be nested. For more information about the related built-in functions, see ARRAY, MAP, or STRUCT.