Google BigQuery mapping columns
Type mapping
While extracting your data, the system matches BigQuery data types to Data Integration data types. If a data type is not supported, the system automatically maps it as a String type.
The mapping of BigQuery data types to Data Integration-compatible types are available in the table below:
| Data Integration Type | BigQuery Type |
|---|---|
| STRING | STRING |
| INTERGER | INTERGER |
| FLOAT | FLOAT |
| BOOLEAN | BOOLEAN |
| TIMESTAMP | TIMESTAMP |
| DATE | DATE |
| TIME | TIME |
| RECORD | STRING |
| SHORTDECIMAL | |
| (up to 9 decimal places) | NUMERIC |
| LONGDECIMAL | |
| (more than 9 decimal places) | BIGNUMERIC |
Scenarios of data type conflicts
When a datatype mismatch occurs, for example, if the source table changes the datatype of an existing column, the platform determines the datatype of the corresponding column in the target table using specific rules. These rules ensure that the most comprehensive datatype to preserve data integrity and prevent information loss.
Examples of data type conflicts
The table outlines scenarios of data type conflicts and the resolution rules. The Inferred Type column represents the chosen data type in the target table when a conflict arises.
| Conflicting Types | Inferred Type | Example Scenario |
|---|---|---|
| TIMESTAMP vs. DATE | TIMESTAMP | If a column in the source initially uses the TIMESTAMP type but changes to DATE, the target converts it to TIMESTAMP. |
| SMALLINT vs. BOOLEAN | SMALLINT | A conflict between SMALLINT and BOOLEAN types in the source column will lead to the target column adopting the SMALLINT type. |
| INTEGER vs. (SMALLINT / BOOLEAN) | INTEGER | If a column in the source changes from INTEGER to SMALLINT or BOOLEAN, the target promotes it to INTEGER. |
| BIGINT vs. (INTEGER / SMALLINT / BOOLEAN) | BIGINT | If a source column changes from BIGINT to SMALLINT, BOOLEAN, or INTEGER, the target elevates it to BIGINT. |
| DECIMAL vs. (INTEGER / BIGINT / SMALLINT / BOOLEAN) | DECIMAL | If a column in the source is INTEGER or DECIMAL, the target transforms it into DECIMAL. |
| DOUBLE PRECISION vs. (INTEGER / BIGINT / SMALLINT / BOOLEAN) | DOUBLE PRECISION | If there is a conflict between DOUBLE PRECISION and SMALLINT in the source column, the target column will adopt DOUBLE PRECISION. |
| REAL vs. (DOUBLE PRECISION / DECIMAL / INTEGER / BIGINT / SMALLINT / BOOLEAN) | REAL | If a column of DOUBLE PRECISION type changes to REAL type in the source, it remains as REAL in the target. |
| FLOAT vs. (INTEGER / BIGINT / SMALLINT / DECIMAL / REAL / DOUBLE PRECISION / BOOLEAN) | FLOAT | If a source column starts as FLOAT and changes to INTEGER, the target keeps it as FLOAT. |
| VARCHAR vs. ALL DATATYPES | VARCHAR | If a source column has type VARCHAR and changes to INTEGER, the target column retains the VARCHAR type. |
| OBJECT vs. ALL DATATYPES | OBJECT | If a source column starts as OBJECT and later changes to DATE or INTEGER, the target treats it as OBJECT. |
VARCHAR and OBJECT have higher priority and overrides other data types in cases of conflict.
