Connection Modes
You can configure a Google BigQuery connection to use one of the following connection modes:
- Simple mode
- If you use simple mode, Google BigQuery Connector flattens each field within the Record data type field as a separate field in the field mapping.
- Hybrid mode
- If you use hybrid mode, Google BigQuery Connector displays all the top-level fields in the Google BigQuery table including Record data type fields. Google BigQuery Connector displays the top-level Record data type field as a single field of the String data type in the field mapping.
- Complex mode
- If you use complex mode, Google BigQuery displays all the columns in the Google BigQuery table as a single field of the String data type in the field mapping.
Connection Mode Example
Google BigQuery Connector reads and writes the Google BigQuery data based on the connection mode that you configure for the Google BigQuery connection.
You have a Customers table in Google BigQuery that contains primitive fields and the Address field of the Record data type. The Address field contains two primitive sub-fields, City and State, of the String data type.
The following image shows the schema of the Customers table in Google BigQuery:
The following table shows the Customers table data in Google BigQuery:
ID | Name | Address.City | Address.State | Mobile | Totalpayments |
|---|
14 | John | LOS ANGELES | CALIFORNIA | +1-9744884744 | 18433.90 |
| | | | +1-8267389993 | |
29 | Jane | BOSTON | MANHATTAN | +1-8789390309 | 28397.33 |
| | | | +1-9876553784 | |
| | | | +1-8456437848 | |
Simple Mode
If you use simple connection mode, Google BigQuery Connector flattens each field within the Record data type field as a separate field in the Field Mapping tab.
The following table shows two separate fields, Address_City and Address_State, for the respective sub-fields within the Address Record field in the Customers table:
ID | Name | Address_City | Address_State | Mobile | Totalpayments |
|---|
14 | John | LOS ANGELES | CALIFORNIA | +1-9744884744 | 18433.90 |
14 | John | LOS ANGELES | CALIFORNIA | +1-8267389993 | 18433.90 |
29 | Jane | BOSTON | MANHATTAN | +1-8789390309 | 28397.33 |
29 | Jane | BOSTON | MANHATTAN | +1-9876553784 | 28397.33 |
29 | Jane | BOSTON | MANHATTAN | +1-8456437848 | 28397.33 |
The following image shows the Address.State and Address.City fields in the
Field Mapping tab of a Data Synchronization task:
Hybrid Mode
If you use hybrid connection mode, Google BigQuery Connector displays all the top-level fields in the Google BigQuery table including Record data type fields. Google BigQuery Connector displays the top-level Record data type field as a single field of the String data type in the Field Mapping tab.
The following image shows the Address field in the
Field Mapping tab of a Data Synchronization task:
Complex Mode
If you use complex connection mode, Google BigQuery Connector displays all the columns in the Google BigQuery table as a single field of the String data type in the Field Mapping tab.
The following image shows the STRING_DATA field in the
Field Mapping tab of a Data Synchronization task:
Rules and Guidelines for Google BigQuery Connection Modes
Simple Mode
Consider the following rules and guidelines when you configure a Google BigQuery connection to use simple connection mode:
- •You cannot create a Google BigQuery target table that contains repeated columns using the Create Target option.
- •If the Google BigQuery source table contains repeated columns, you cannot configure data filters for these columns.
- •If the Google BigQuery table contains more than one repeated column, you cannot preview data.
- •If the Google BigQuery target table contains repeated columns, you cannot configure update and delete operations for these columns.
- •You cannot configure upsert operations for columns of the Record data type and repeated columns.
- •When you read data from a Google BigQuery source, you must not map more than one repeated column in a single mapping. You must create multiple mappings for each repeated column.
Hybrid Mode
Consider the following rules and guidelines when you configure a Google BigQuery connection to use hybrid connection mode:
- •You cannot preview data.
- •You cannot create a Google BigQuery target table using the Create Target option.
- •If the Google BigQuery source table contains columns of the Record data type and repeated columns, you cannot configure data filters for these columns.
- •You cannot configure update, upsert, and delete operations for columns of the Record data type and repeated columns.
- •You must select JSON (Newline Delimited) format as the data format of the staging file under the advanced target properties.
- •You cannot use CSV format as the data format of the staging file. The following CSV formatting options in the advanced target properties are not applicable:
- - Allow Quoted Newlines
- - Field Delimiter
- - Allow Jagged Rows
Complex Mode
Consider the following rules and guidelines when you configure a Google BigQuery connection to use complex connection mode:
- •You cannot preview data.
- •You cannot create a Google BigQuery target table using the Create Target option.
- •When you configure a Google BigQuery source connection to use complex connection mode, you cannot configure data filters for the source.
- •You cannot configure update, upsert, and delete operations.
- •You must select JSON (Newline Delimited) format as the data format of the staging file under the advanced target properties.
- •You cannot use CSV format as the data format of the staging file. The following CSV formatting options in the advanced target properties are not applicable:
- - Allow Quoted Newlines
- - Field Delimiter
- - Allow Jagged Rows
- •You cannot use key range partitioning for Google BigQuery sources.
