This topic describes data type mappings between three object formats (ORC, Parquet, and Avro) and AnalyticDB for PostgreSQL.
Data type mappings between ORC and AnalyticDB for PostgreSQL
Data type of ORC | AnalyticDB for PostgreSQL data type |
BOOLEAN | bool |
SHORT | int2 |
INT | int4 |
LONG | int8 |
FLOAT | float4 |
DOUBLE | float8 |
DECIMAL | numeric |
CHAR | char |
STRING | text |
BINARY | bytea |
TIMESTAMP | timestamp |
DATE | date |
LIST(SHORT) | int2[] |
LIST(INT) | int4[] |
LIST(LONG) | int8[] |
LIST(FLOAT) | float4[] |
LIST(DOUBLE) | float8[] |
The LIST data type in ORC can be converted to only a one-dimensional array in AnalyticDB for PostgreSQL.
Data type mappings between Parquet and AnalyticDB for PostgreSQL
Basic data types
The following table describes the data type mappings that apply when Parquet objects do not contain logical type data:
Data type of Parquet | AnalyticDB for PostgreSQL data type |
BOOLEAN | bool |
INT32 | int4 |
INT64 | int8 |
INT96 | timestamp |
FLOAT | float4 |
DOUBLE | float8 |
BYTE_ARRAY | bytea |
FIXED_LEN_BYTE_ARRAY | bytea |
The following table describes the data type mappings that apply when Parquet objects contain logical type data:
Data type of Parquet | AnalyticDB for PostgreSQL data type |
STRING | text |
DATE | date |
TIMESTAMP | timestamp |
TIME | time |
INTERVAL | interval |
DECIMAL | numeric |
INT(8) and INT(16) | int2 |
INT(32) | int4 |
INT(64) | int8json |
UINT(8/16/32/64) | int8 |
JSON | json |
BSON | jsonb |
UUID | uuid |
ENUM | text |
Complex data types
Data type of Parquet | AnalyticDB for PostgreSQL data type |
LIST<T> | T[] Note
|
MAP | Not supported |
STRUCT | Not supported |
During the data import process, the system automatically performs type conversion. If unsupported types are encountered, the import operation will fail and return an error message.
For better query performance, we recommend using basic data types.
Data type mappings between Avro and AnalyticDB for PostgreSQL
Avro data types map to column data types in AnalyticDB for PostgreSQL, and the Avro RECORD data type maps to a data table in AnalyticDB for PostgreSQL.
Data type of Avro (including the main type, logical type, and element type) | AnalyticDB for PostgreSQL data type |
BOOLEAN | bool |
INT | int4 and int2 |
INT and DATE | date |
LONG | int8 |
LONG and TIMESTAMP | timestamp and timestamptz |
LONG and TIME | time |
FLOAT | float |
DOUBLE | float8 |
BYTES | bytea |
BYTES and DECIMAL (precision, scale) | numeric (precision, scale) |
STRING | char, varchar, text, and numeric (without specifying precision or scale) |
ARRAY and INT | int4[] and int2[] |
ARRAY and LONG | int8[] |
ARRAY and FLOAT | float[] |
ARRAY and DOUBLE | float8[] |
The ARRAY data type in Avro can be converted to only a one-dimensional array in AnalyticDB for PostgreSQL.
Only the Avro data types listed in the preceding table are supported.