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 versus DATE | TIMESTAMP | If a column in the source initially uses the TIMESTAMP type but changes to DATE, the target converts it to TIMESTAMP. |
| SMALLINT versus BOOLEAN | SMALLINT | A conflict between SMALLINT and BOOLEAN types in the source column will lead to the target column adopting the SMALLINT type. |
| INTEGER versus (SMALLINT / BOOLEAN) | INTEGER | If a column in the source changes from INTEGER to SMALLINT or BOOLEAN, the target promotes it to INTEGER. |
| BIGINT versus (INTEGER / SMALLINT / BOOLEAN) | BIGINT | If a source column changes from BIGINT to SMALLINT, BOOLEAN, or INTEGER, the target elevates it to BIGINT. |
| DECIMAL versus (INTEGER / BIGINT / SMALLINT / BOOLEAN) | DECIMAL | If a column in the source is INTEGER or DECIMAL, the target transforms it into DECIMAL. |
| DOUBLE PRECISION versus (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 versus (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 versus (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 versus ALL DATATYPES | VARCHAR | If a source column has type VARCHAR and changes to INTEGER, the target column keeps the VARCHAR type. |
| OBJECT versus 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.
