Data type mappings between MaxCompute and Hive, MySQL, Oracle, and Hologres - MaxCompute

When performing MaxCompute data migration or integration operations, you must refer to the data type mapping table to establish mapping relationships for table fields. This ensures that data is correctly stored, processed, and queried across different databases, prevents data loss or corruption, and improves work efficiency. This topic describes the data type mapping relationships between MaxCompute and Hive, Oracle, MySQL, and Hologres.

Data type mappings between MaxCompute and Hive, MySQL, and Oracle

The following table describes the data type mappings between MaxCompute and Hive, Oracle, and MySQL.

Data type of MaxCompute	Data type of Hive	Data type of Oracle	Data type of MySQL
BOOLEAN	BOOLEAN	None Note Oracle supports the BOOLEAN data type starting from version 23C.	None Note `TINYINT(1)` is used instead.
TINYINT	TINYINT	NUMBER(3,0)	TINYINT
SMALLINT	SMALLINT	NUMBER(5,0)	SMALLINT
INT	INT	NUMBER(7,0)	MEDIUMINT
INT	INT	NUMBER(10,0)	INT
BIGINT	BIGINT	NUMBER(20,0)	BIGINT
FLOAT	FLOAT	BINARY_FLOAT Note This data type is supported in Oracle Database 10g and later.	FLOAT
DOUBLE	DOUBLE	BINARY_DOUBLE Note This data type is supported in Oracle Database 10g and later.	DOUBLE
DECIMAL	DECIMAL	NUMBER(P,S)	DECIMAL NUMERIC
STRING	STRING	VARCHAR VARCHAR2 CHAR NCHAR NVARCHAR3	VARCHAR CHAR
VARCHAR	VARCHAR	VARCHAR VARCHAR2 CHAR NCHAR NVARCHAR3	VARCHAR
STRING	CHAR	CHAR	CHAR
BINARY	BINARY	RAW	BINARY VARBINARY
TIMESTAMP	TIMESTAMP	TIMESTAMP WITH TIME ZONE	TIMESTAMP
TIMESTAMP_NTZ	TIMESTAMP Note This data type is supported in Hive 3 and later.	DATETIME	TIMESTAMP
DATE	DATE	DATE	DATE
DATETIME	DATE	DATE	DATETIME
ARRAY	ARRAY	Not supported	Not supported
MAP	`MAP<key,value>`	Not supported	Not supported
STRUCT	STRUCT	Not supported	Not supported
Not supported	UNION	Not supported	Not supported
Not supported	Not supported	BLOB	BLOB

Data type mappings between MaxCompute and Hologres

The following table describes the data type mappings between MaxCompute and Hologres when you create a foreign table sourced from a MaxCompute table in Hologres.

Data type of MaxCompute	Data type of Hologres	Supported version	Description
STRING VARCHAR	TEXT	All Hologres versions	None
BIGINT	INT8	All Hologres versions	None
INT	INT4 INT	All Hologres versions	None
FLOAT	FLOAT4 REAL	All Hologres versions	None
DOUBLE	FLOAT FLOAT8	All Hologres versions	None
BOOLEAN	BOOL	All Hologres versions	None
DATETIME	TIMESTAMP WITH TIME ZONE	All Hologres versions	The DATETIME data type of MaxCompute specifies a date time in UTC+8. The time ranges from 0000-01-01 to 9999-12-31, and is accurate to the millisecond.
DECIMAL	NUMERIC	All Hologres versions	If the precision is not specified for the DECIMAL data type of MaxCompute, the default precision is (38, 18). When you create a table by using IMPORT FOREIGN SCHEMA, the system automatically converts the precision.
TIMESTAMP	TIMESTAMP WITH TIME ZONE	Hologres V0.8 and later	Valid values of the TIMESTAMP data type of MaxCompute are 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999. The timestamp is accurate to the nanosecond. Data of the TIMESTAMPTZ data type of Hologres is accurate to the millisecond. Hologres automatically converts the precision to the millisecond when Hologres reads data.
CHAR(n)	By default, CHAR(n) is used. Hologres also allows you to map CHAR(n) in MaxCompute to TEXT. You need to run `set hg_enable_convert_type_for_foreign_table = true` to set the parameter and modify the field type to TEXT when creating the table.	Hologres V0.8 and later	Entries of the CHAR(n) data type in MaxCompute are character strings with a fixed length of n characters. The maximum value of n is 255. If you insert a character string that is shorter than the required length, spaces are used.
VARCHAR(n)	By default, VARCHAR(n) is used. Hologres also allows you to map VARCHAR(n) in MaxCompute to TEXT. You need to run `set hg_enable_convert_type_for_foreign_table = true` to set the parameter and modify the field type to TEXT when creating the table.	Hologres V0.8 and later	Entries of the VARCHAR(n) data type in MaxCompute are character strings with a variable length of n characters. Valid values of n: 1 to 65535.
DATE	DATE	Hologres V0.8 and later	None
SMALLINT	By default, INT2 is used. Hologres also allows you to map SMALLINT in MaxCompute to INT8. You need to run `set hg_enable_convert_type_for_foreign_table = true` to set the parameter and modify the field type to INT8 when creating the table.	All Hologres versions, in which the data type for Hologres V0.8 is INT4 and the data type for Hologres V0.9 is INT2	None
TINYINT	By default, INT2 is used. Hologres also allows you to map TINYINT in MaxCompute to INT8. You need to run `set hg_enable_convert_type_for_foreign_table = true` to set the parameter and modify the field type to INT8 when creating the table.	All Hologres versions, in which the data type for Hologres V0.8 is INT4 and the data type for Hologres V0.9 is INT2	None
CHAR	Not supported	Not supported	None
ARRAY<INT>	INT4[]	Hologres V0.8 and later	None
ARRAY<BIGINT>	INT8[]	Hologres V0.8 and later	None
ARRAY<FLOAT>	FLOAT4[]	Hologres V0.8 and later	None
ARRAY<DOUBLE>	FLOAT8[]	Hologres V0.8 and later	None
ARRAY<BOOLEAN>	BOOLEAN[]	Hologres V0.8 and later	None
ARRAY<STRING>	TEXT[]	Hologres V0.8 and later	None
BINARY	BYTEA	Hologres V0.9 and later	None
ARRAY<TINYINT>	Not supported	Not supported	None
ARRAY<SMALLINT>	Not supported	Not supported	None

Note

For a MaxCompute table that contains a field whose data type is not supported by Hologres, if you do not specify this field in a query in Hologres, data of other fields can be queried.

References

For more information about the mappings between built-in functions of MaxCompute and built-in functions of Hive, MySQL, and Oracle, see Mappings between built-in functions of MaxCompute and built-in functions of Hive, MySQL, and Oracle.