Data type mappings ensure that when data is exported from OceanBase AI Database to formats such as Parquet, ORC, MaxCompute (ODPS), and CSV, the original data types are accurately mapped to the corresponding target format data types. OceanBase AI Database uses a clear mapping table to associate various data types with the data types supported by Parquet, ORC, and MaxCompute (ODPS), thereby ensuring that data is not lost or overloaded during export and retains its semantics.
Note
For CSV files, the exported data is of the string type.
Export to Parquet format
Parquet Physical Type |
Parquet logical types |
Hive data types |
Data types in user tenants |
Remarks |
|---|---|---|---|---|
| INT32 | INT(8,TRUE) | TINYINT | TINYINT | |
| INT32 | INT(16,TRUE) | SMALLINT | SMALLINT | |
| INT32 | INT(32,TRUE) | INT | INT | |
| INT64 | INT(64,TRUE) | BIGINT | BIGINT | |
| INT32 | INT(8,FALSE) | TINYINT (NULL if out of range) | TINYINT UNSIGNED | |
| INT32 | INT(16,FALSE) | SMALLINT (NULL if out of range) | SMALLINT UNSIGNED | |
| INT32 | INT(32,FALSE) | INT (NULL if out of range) | INT UNSIGNED | |
| INT64 | INT(64,FALSE) | BIGINT (NULL if out of range) | BIGINT UNSIGNED | |
| FLOAT | NONE | FLOAT | FLOAT | |
| DOUBLE | NONE | DOUBLE | DOUBLE | |
| FIXED_LEN_BYTE_ARRAY | DECIMAL | DECIMAL | DECIMAL, DECIMAL UNSIGNED | The precision and scale must be specified. |
| BYTE_ARRAY | STRING | CHAR | CHAR, BINARY | All string types in Parquet are encoded in UTF-8. |
| BYTE_ARRAY | STRING | VARCHAR | VARCHAR, VARBINARY | |
| BYTE_ARRAY | STRING | STRING | TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB | |
| INT64 | TIMESTAMP(is_adjusted_to_utc=true, parquet::LogicalType::TimeUnit::MICROS) | TIMESTAMP | TIMESTAMP | |
| INT64 | TIMESTAMP(is_adjusted_to_utc=false, parquet::LogicalType::TimeUnit::MICROS) | TIMESTAMP | DATETIME | |
| INT32 | DATE | DATE | DATE | |
| INT64 | TIME | \ | TIME | |
| INT32 | INT(8,FALSE) | \ | YEAR |
Export to ORC format
ORC type |
Hive data types |
Data types in user tenants |
|---|---|---|
| BYTE | TINYINT | TINYINT |
| SHORT | SMALLINT | SMALLINT |
| INT | INT | INT |
| LONG | BIGINT | BIGINT |
| FLOAT | FLOAT | FLOAT |
| DOUBLE | DOUBLE | DOUBLE |
| DECIMAL | DECIMAL | DECIMAL |
| CHAR | CHAR | CHAR |
| VARCHAR | VARCHAR | VARCHAR |
| STRING | STRING | TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT |
| BINARY | BINARY | TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB/BINARY/VARBINARY |
| DATE | DATE | DATE |
| TIMESTAMP | TIMESTAMP | DATETIME/TIMESTAMP |
Export to MaxCompute (ODPS) format
MaxCompute (ODPS) data types |
Data types in user tenants |
Remarks |
|---|---|---|
| BOOLEAN | TINYINT (0 indicates false and 1 indicates true) | - |
| TINYINT | TINYINT | - |
| SMALLINT | SMALLINT | - |
| INT | INT | - |
| BIGINT | BIGINT | - |
| FLOAT | FLOAT | Users are not allowed to specify the precision; otherwise, an error will be reported. |
| DOUBLE | DOUBLE | Users are not allowed to specify the precision; otherwise, an error will be reported. |
| DECIMAL | DECIMAL(M,D) | You must explicitly make M and D consistent with P and S on MaxCompute (ODPS). Otherwise, an error is reported at execution time. |
| CHAR (up to 255 bytes, with trailing spaces) | CHAR (0 to 255) | The user-specified length must be greater than or equal to the length of the corresponding column on MaxCompute (ODPS). Otherwise, an error will be reported during execution. |
| VARCHAR (up to 65,535) | VARCHAR (length can be specified as 0 to 262144) | The user-specified length must be greater than or equal to the length of the corresponding column on MaxCompute (ODPS). Otherwise, an error will be reported during execution. |
| STRING (up to 8 MB) | VARCHAR/TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT (MEDIUMTEXT is a TEXT type with a maximum length of 16,777,215 bytes, including the character set.) | You need to know the maximum length of a STRING data type in MaxCompute (ODPS) and determine the corresponding data type for mapping based on this information. If the data type length in OceanBase AI Database is insufficient to accommodate the data in MaxCompute (ODPS), an error will be reported during execution. |
| BINARY (up to 8 MB) | VARBINARY/TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB (BLOB values are treated as binary strings (byte strings), with a binary character set and collation. Comparison and sorting are based on the numerical values of the bytes in the column. MEDIUMBLOB is the BLOB type with a maximum length of 16,777,215 bytes.) | You need to know the maximum length of binary data in MaxCompute (ODPS) to determine which type to map it to. If the type length in OceanBase AI Database is insufficient to accommodate the data from MaxCompute (ODPS), an error will be reported during execution. |
| TIMESTAMP (stores time in UTC with 9 decimal places of nanoseconds, but displays it according to the current time zone) | TIMESTAMP | Only TIMESTAMP(6) is supported; others are not. The maximum precision for the TIMESTAMP type is six decimal places, so precision loss occurs. |
| TIMESTAMP_NTZ (stores UTC time with 9 decimal places in nanoseconds, completely independent of time zone) | DATETIME | Only DATETIME(6) is supported; others are not. TIMESTAMP is time zone-dependent, so it is not supported. |
| DATE (format: yyyy-mm-dd, value range: 0001-01-01 to 9999-12-31) | DATE | Only DATE is supported; other types are not supported. |
| DATETIME (accurate to milliseconds, with three decimal places; value range: 0001-01-01 00:00:00.000 to 9999-12-31 23:59:59.999) | DATETIME | Only DATETIME(3) and later formats with a specified number of decimal places, such as DATETIME(4), DATETIME(5), and DATETIME(6), are supported. Other formats are not supported. |
| ARRAY | ARRAY | For more information, see the Mapping between OceanBase AI Database's ARRAY type and ODPS section below. |
| MAP | Not supported | Not supported |
| STRUCT | Not supported | Not supported |
| JSON | JSON | Not supported |
Mapping between the ARRAY type in OceanBase AI Database and the type in ODPS
The following table describes the mapping between the ARRAY type supported by OceanBase AI Database and the types supported by ODPS:
OceanBase AI Database |
ODPS |
|---|---|
| ARRAY(tinyint) | ARRAY<tinyint> |
| ARRAY(tinyint) | ARRAY<boolean> |
| ARRAY(smallint) | ARRAY<smallint> |
| ARRAY(int) | ARRAY<int> |
| ARRAY(bigint) | ARRAY<bigint> |
| ARRAY(float) | ARRAY<float> |
| ARRAY(double) | ARRAY<double> |
| ARRAY(varchar) | ARRAY<varchar> |
| ARRAY(varchar) | ARRAY<string> |
| ARRAY(array()) | ARRAY<array<>> |