Data type mappings for OSS foreign tables
When creating OSS foreign tables in AnalyticDB for PostgreSQL, declare the column data type that corresponds to each field in your source file. This page lists the supported mappings for ORC, Parquet, and Avro formats.
If a source field uses an unsupported type, the import fails and returns an error.
ORC
| ORC type | AnalyticDB for PostgreSQL type | Notes |
|---|---|---|
| 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[] | One-dimensional arrays only. |
| LIST(INT) | int4[] | One-dimensional arrays only. |
| LIST(LONG) | int8[] | One-dimensional arrays only. |
| LIST(FLOAT) | float4[] | One-dimensional arrays only. |
| LIST(DOUBLE) | float8[] | One-dimensional arrays only. |
Parquet
Basic types
The following mappings apply when the Parquet field has no logical type annotation.
| Parquet physical type | AnalyticDB for PostgreSQL type |
|---|---|
| BOOLEAN | bool |
| INT32 | int4 |
| INT64 | int8 |
| INT96 | timestamp |
| FLOAT | float4 |
| DOUBLE | float8 |
| BYTE_ARRAY | bytea |
| FIXED_LEN_BYTE_ARRAY | bytea |
Logical types
The following mappings apply when the Parquet field carries a logical type annotation.
| Parquet logical type | AnalyticDB for PostgreSQL type | Notes |
|---|---|---|
| STRING | text | |
| DATE | date | |
| TIMESTAMP | timestamp | |
| TIME | time | |
| INTERVAL | interval | |
| DECIMAL | numeric | |
| INT(8) and INT(16) | int2 | |
| INT(32) | int4 | |
| INT(64) | int8 | |
| UINT(8/16/32/64) | int8 | |
| JSON | json | |
| BSON | jsonb | |
| UUID | uuid | |
| ENUM | text |
Complex types
| Parquet complex type | AnalyticDB for PostgreSQL type | Notes |
|---|---|---|
| LIST\<T\> | T[] | T must be a basic type. For example, LIST\<DATE\> maps to date[]. Nesting LIST with LIST, MAP, or STRUCT is not supported. |
| 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.
Use basic data types where possible for better query performance.
Avro
Avro data types map to column data types in AnalyticDB for PostgreSQL. The Avro RECORD type maps to a data table.
Only the types listed in this table are supported.
| Avro type (main type, logical type, and element type) | AnalyticDB for PostgreSQL type | Notes |
|---|---|---|
| BOOLEAN | bool | |
| INT | int4 and int2 | |
| INT + DATE | date | |
| LONG | int8 | |
| LONG + TIMESTAMP | timestamp and timestamptz | |
| LONG + TIME | time | |
| FLOAT | float | |
| DOUBLE | float8 | |
| BYTES | bytea | |
| BYTES + DECIMAL (precision, scale) | numeric (precision, scale) | |
| STRING | char, varchar, text, and numeric (without specifying precision or scale) | |
| ARRAY + INT | int4[] and int2[] | One-dimensional arrays only. |
| ARRAY + LONG | int8[] | One-dimensional arrays only. |
| ARRAY + FLOAT | float[] | One-dimensional arrays only. |
| ARRAY + DOUBLE | float8[] | One-dimensional arrays only. |