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 | 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) |
|
STRING | STRING |
|
|
VARCHAR | VARCHAR |
| VARCHAR |
STRING | CHAR | CHAR | CHAR |
BINARY | BINARY | RAW |
|
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 |
| 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 |
| TEXT | All Hologres versions | None |
BIGINT | INT8 | All Hologres versions | None |
INT |
| All Hologres versions | None |
FLOAT |
| All Hologres versions | None |
DOUBLE |
| 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 |
|
CHAR(n) | By default, CHAR(n) is used. Hologres also allows you to map CHAR(n) in MaxCompute to TEXT. You need to run | 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 | 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 | 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 | 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 |
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.