MaxCompute:Hive-compatible data type edition

Type overview

Integer types: TINYINT (8-bit), SMALLINT (16-bit), INT (32-bit), BIGINT (64-bit)

Floating-point types: FLOAT (32-bit), DOUBLE (64-bit), DECIMAL(precision,scale) (exact numeric)

String types: STRING, VARCHAR(n), CHAR(n)

Date and time types: DATE, DATETIME, TIMESTAMP

Other types: BOOLEAN, BINARY

Complex types: ARRAY, MAP, STRUCT (all nestable)

Basic data types

Integer types

Integer constants use type suffixes: Y for TINYINT, S for SMALLINT, L for BIGINT. Unsuffixed integer literals default to INT. If the value exceeds the INT range but fits in BIGINT, it is treated as BIGINT. If it exceeds the BIGINT range, it is treated as DOUBLE.

Floating-point types

DECIMAL constants use the BD suffix. When inserting into a DECIMAL column:

insert into test_tb(a) values (3.5BD);

To extend the DECIMAL scale range from 0–18 to 0–38:

set odps.sql.decimal2.extended.scale.enable=true;

String types

String constants can be concatenated directly. For example, abc and xyz combine to abcxyz.

Date and time types

DATETIME values do not include the millisecond component. To include milliseconds when uploading data with Tunnel, use the -dfp flag:

tunnel upload -dfp 'yyyy-MM-dd HH:mm:ss.SSS'

For more information, see Tunnel commands.

TIMESTAMP is timezone-independent, but functions such as cast(<a timestamp> as string) display values based on the current session time zone.

Other types

Partition key columns

The data type of a partition key column can be STRING, VARCHAR, CHAR, TINYINT, SMALLINT, INT, or BIGINT.

NULL values

All basic data types support NULL values.

Implicit conversions

The following conversions are disabled because they can reduce precision or cause errors:

VARCHAR constants can be implicitly converted to STRING.

Complex data types

MaxCompute supports three complex data types in the Hive-compatible edition. Complex types can be nested.

For built-in functions that work with complex types, see ARRAY, MAP, and STRUCT.

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;

Arithmetic operator and overflow behavior

For the +, -, *, /, and POW operators:

DOUBLE comparison behavior

For the >, >=, =, <, and <= operators applied to DOUBLE values:

Bitwise operator return types

For the &, |, and ^ operators:

Function return type differences

CONCAT_WS behavior with NULL inputs

FIND_IN_SET behavior with empty strings

In Hive-compatible mode, an empty string matches the tail of the search string:

find_in_set("","")    -- Returns 1
find_in_set("", "a,") -- Returns 2

In V1.0 and V2.0 modes, an empty string is treated as unmatched and 0 is returned.

REGEXP specification

SUBSTR start position behavior

string substr(string <str>, bigint <start_position>[, bigint <length>])

Partition pruning limitations

Specific functions do not support partition pruning in Hive-compatible mode. For the full list, see Mappings between built-in functions of MaxCompute and built-in functions of Hive, MySQL, and Oracle.

User-defined functions (UDFs)

In Hive-compatible mode, UDF data types are parsed and overloaded based on Hive-compatible data types. Built-in functions that require the MaxCompute V2.0 data type edition are also available.