Google BigQuery Connectors

Back Next

Google BigQuery V2 targets in mappings

To write data to a Google BigQuery target, configure a Google BigQuery object as the Target transformation in a mapping.

Specify the name and description of Google BigQuery target. Configure the target and advanced properties for the target object in mappings.

The following table describes the target properties that you can configure for a Google BigQuery target:

Property	Description
Connection	Name of the Google BigQuery V2 target connection. Select a target connection or click New Parameter to define a new parameter for the target connection. If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option when you create a parameter. When the task runs, the agent uses the parameters from the file that you specify in the task advanced session properties.
Target Type	Type of the Google BigQuery target objects available. You can write data to a single or multiple Google BigQuery target objects. You can also parameterize the object.
Parameter	Select an existing parameter for the target object or click New Parameter to define a new parameter for the target object. The Parameter property appears only if you select Parameter as the target type. If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option when you create a parameter. When the task runs, the agent uses the parameters from the file that you specify in the task advanced session properties. Does not apply when you perform a data driven operation.
Object	Name of the Google BigQuery target object based on the target type selected.
Create New at Runtime	Creates a target table at runtime in Google BigQuery . The target table can also contain clustered columns. Enter a name for the target object and path for the target object and select the source fields that you want to use. By default, all source fields are used. You must specify a valid dataset ID for the Path attribute. The target name can contain alphanumeric characters. You cannot use special characters in the file name except the underscore character (_). You cannot parameterize the target at runtime. For more information about how to create a target table with clustered columns, see Clustering order.
Operation	You can select one the following operations: Insert Update Upsert (Update or Insert) Delete Data Driven If you use complex connection mode, you cannot configure update, upsert, and delete operations.
Data Driven Condition	Flags rows for an insert, update, delete, or reject operation based on the data driven expression you specify. You must specify the data driven condition for non-CDC sources. For CDC sources, you must leave the field empty as the rows in the CDC source tables are already marked with the operation types. Appears only when you select Data Driven as the operation type.
Update Columns	Specifies the temporary primary key columns to update, upsert or delete target data. If the Google BigQuery target does not include a primary key column, and the mapping performs an update, upsert, or delete task operation, click Add to add a temporary key. You can select multiple columns. By default, no columns are specified.

The following table describes the advanced properties that you can configure for a Google BigQuery target:

Property	Description
Target Dataset ID	Optional. Overrides the Google BigQuery dataset name that you specified in the connection.
UpdateMode	Determines the mode that the Secure Agent uses to update rows in the Google BigQuery target. You can select one of the following modes: Update As Update. The Secure Agent updates all rows flagged for update if the entries exist. Update Else Insert. The Secure Agent first updates all rows flagged for update if the entries exist in the target. If the entries do not exist, the Secure Agent inserts the entries. Default is Update as Update. Not applicable when you perform a data driven operation.
Enable Data Driven¹	Implements data driven operation to honor flagged rows for an insert, update, delete, or reject operation based on the data driven condition. Select this option when you select Data Driven as the target operation.
Enable Merge	Implements the Merge query to perform an update, upsert, delete or data driven operation on a Google BigQuery target table. If you select the Enable Data Driven property, you must select this option. Default is disabled.
Use Default Column Values	Applicable when the selected data format for the staging file is CSV when the mapping contains unconnected ports. Includes the default column values for the unconnected port from the staging file to create the target. This is applicable when you have defined the default constraint value in the Google BigQuery source column. When you do not enable this option, the agent creates a target only with the connected ports. The agent populates null or empty strings for unconnected ports.
Update Override¹	Optional. Overrides the update SQL statement that the Secure Agent generates to update the Google BigQuery target. To use the update override query, you must set the Operation property to Update and the UpdateMode property to Update As Update . Use the following format to define an update override query: UPDATE `<project_name>.<dataset_name>.<table_name>` as <alias_name> SET <alias_name>.<col_name1>=:<temp_table>.<col_name1>, <alias_name>.<col_name2>=:<temp_table>.<col_name2> FROM <dataset_name>.:<temp_table> WHERE <conditional expression> For example, UPDATE `project1.custdataset.cust_table1` as ab SET ab.fld_str=:custtemp.fld_str, ab.fld_int=:custtemp.fld_int FROM custdataset.:custtemp WHERE ab.fld_string_req = :custtemp.fld_string_req Not applicable when you perform a data driven operation.
Target Table Name	Optional. Overrides the Google BigQuery target table name that you specified in the Target transformation. If you specify an update override query, Google BigQuery V2 Connector ignores this property.
Target Staging Dataset¹	Optional. Overrides the Google BigQuery staging dataset name that you specified in the connection and the Target Dataset ID target advanced property.
Create Disposition	Specifies whether Google BigQuery V2 Connector must create the target table if it does not exist. You can select one of the following values: Create if needed. If the table does not exist, Google BigQuery V2 Connector creates the table. Create never. If the table does not exist, Google BigQuery V2 Connector does not create the table and displays an error message. Create disposition is applicable only when you perform an insert operation on a Google BigQuery target.
Write Disposition	Specifies how Google BigQuery V2 Connector must write data in bulk mode if the target table already exists. You can select one of the following values: Write append. If the target table exists, Google BigQuery V2 Connector appends the data to the existing data in the table. Write truncate. If the target table exists, Google BigQuery V2 Connector overwrites the existing data in the table. Write empty. If the target table exists and contains data, Google BigQuery V2 Connector displays an error and does not write the data to the target. Google BigQuery V2 Connector writes the data to the target only if the target table does not contain any data. Write disposition is applicable for bulk mode. Write disposition is applicable only when you perform an insert operation on a Google BigQuery target.
Write Mode	Specifies the mode to write data to the Google BigQuery target. You can select one of the following modes: Bulk. Google BigQuery V2 Connector first writes the data to a staging file in Google Cloud Storage. When the staging file contains all the data, Google BigQuery V2 Connector loads the data from the staging file to the BigQuery target. Google BigQuery V2 Connector then deletes the staging file unless you configure the task to persist the staging file. Streaming¹. Google BigQuery V2 Connector directly writes data to the BigQuery target. Google BigQuery V2 Connector writes the data into the target row by row. CDC¹. Applies only when you capture changed data from a CDC source. In CDC mode, Google BigQuery V2 Connector captures changed data from any CDC source and writes the changed data to a Google BigQuery target table. Default is Bulk mode. Streaming mode is not applicable when you perform a data driven operation.
Streaming Template Table Suffix¹	Specify the suffix to add to the individual target tables that Google BigQuery V2 Connector creates based on the template target table. This property applies to streaming mode. If you select the Enable Merge option, Google BigQuery V2 Connector ignores this property. Streaming mode is not applicable when you perform a data driven operation.
Rows per Streaming Request¹	Specifies the number of rows that Google BigQuery V2 Connector streams to the BigQuery target for each request. Default is 500 rows. The maximum row size that Google BigQuery V2 Connector can stream to the Google BigQuery target for each request is 10 MB. This property applies to streaming mode. Streaming mode is not applicable when you perform a data driven operation.
Staging file name	Name of the staging file that Google BigQuery V2 Connector creates in the Google Cloud Storage before it loads the data to the Google BigQuery target. This property applies to bulk mode.
Data Format of the staging file	Specifies the data format of the staging file. You can select one of the following data formats: Avro¹ JSON (Newline Delimited). Supports flat and record data with nested and repeated fields. Parquet¹ CSV¹. Supports flat data. In a .csv file, columns of the Timestamp data type are represented as floating point numbers that cause the milliseconds value to differ. Only JSON format is applicable for mappings in advanced mode. This property applies to bulk and CDC mode. Avro and parquet format is not applicable when you perform a data driven operation.
Persist Staging File After Loading	Indicates whether Google BigQuery V2 Connector must persist the staging file in the Google Cloud Storage after it writes the data to the Google BigQuery target. You can persist the staging file if you want to archive the data for future reference. By default, Google BigQuery V2 Connector deletes the staging file in Google Cloud Storage. This property applies to bulk mode.
Enable Staging File Compression¹	Select this option to compress the size of the staging file before Google BigQuery writes the data to the Google Cloud Storage and decompress the staging file before it loads the data to the Google BigQuery target. You can enable staging file compression to reduce cost and transfer time.
Job Poll Interval in Seconds¹	The number of seconds after which Google BigQuery V2 Connector polls the status of the write job operation. Default is 10.
Number of Threads for Uploading Staging file¹	The number of files that Google BigQuery V2 Connector must create to upload the staging file in bulk mode.
Local Stage File Directory¹	Specifies the directory on your local machine where Google BigQuery V2 Connector stores the files temporarily before writing the data to the staging file in Google Cloud Storage. This property applies to bulk mode. This property is not applicable when you use a serverless runtime environment.
Allow Quoted Newlines¹	Indicates whether Google BigQuery V2 Connector must allow the quoted data sections with newline character in a .csv file.
Field Delimiter¹	Indicates whether Google BigQuery V2 Connector must allow field separators for the fields in a .csv file.
Quote Character¹	Specifies the quote character to skip when you write data to Google BigQuery. When you write data to Google BigQuery and the source table contains the specified quote character, the task fails. Change the quote character value to a value that does not exist in the source table. Default is double quotes.
Allow Jagged Rows¹	Indicates whether Google BigQuery V2 Connector must accept the rows without trailing columns in a .csv file.
Pre SQL¹	SQL statement that you want to run before writing data to the target. For example, if you want to select records from the database before you write the records into the table, specify the following pre-SQL statement: SELECT * FROM 'api-project-80697026669.EMPLOYEE.RegionNation' LIMIT 1000
Pre SQL Configuration¹	Specify a pre-SQL configuration. For example, DestinationTable:PRESQL_TGT2, DestinationDataset:EMPLOYEE, FlattenResults:False, WriteDisposition:WRITE_TRUNCATE, UseLegacySql:False
Post SQL¹	SQL statement that you want to run after writing the data into the target. For example, if you want to update records in a table after you write the records into the target table, specify the following post-SQL statement: UPDATE [api-project-80697026669.EMPLOYEE.PERSONS_TGT_DEL] SET phoneNumber.number =1000011, phoneNumber.areaCode=100 where fullname='John Doe'
Suppress post SQL on Error¹	Indicates whether the Secure Agent must abort the post-SQL query execution in case the task fails to write data to the Google BigQuery target table due to errors. Default is disabled.
Post SQL Configuration¹	Specify a post-SQL configuration. For example, DestinationTable:POSTSQL_SRC, DestinationDataset:EMPLOYEE, FlattenResults:True, UseLegacySQL:False
Truncate target table	Truncates the Google BigQuery target table before loading data to the target. Default is disabled.
Allow Duplicate Inserts	Indicates that the Secure Agent can insert duplicate rows into the Google BigQuery target. Applicable only when you perform a data driven operation and DD_INSERT is specified in the data driven condition. Default is not selected.
Disable Duplicate Update Rows¹	Determines if multiple incoming rows attempt to update the same target row, the Secure Agent must process only one of the incoming rows and ignore the rest of the incoming rows. Select this option to configure the mapping to process only one of the incoming rows and ignore the rest of the incoming rows. Default is disabled.
Forward Rejected Rows	Applicable only when you configure DD_REJECT constant in the data driven condition to reject all the rows. Otherwise, this property is not applicable for Google BigQuery V2 Connector.
Label¹	You can assign a label for the transformation to organize and filter the associated jobs in the Google Cloud Platform Log Explorer. For more information about labels and their usage requirements, see Assign a label to the transformations.
Billing Project ID	The project ID for the Google Cloud project that is linked to an active Google Cloud Billing account where the Secure Agent runs query and load jobs. If you omit the project ID here, the Secure Agent runs query and load jobs in the Google Cloud project corresponding to the Project ID value specified in the Google BigQuery V2 connection.
¹Doesn't apply to mappings in advanced mode.