Configuring the BigQuery connector as a target
You can configure the connector to write data to BigQuery data warehouse
Procedure
- On the job design canvas, double-click BigQuery Connector stage icon.
- Select the input link to edit.
- Use one of these two procedures to write data into the BigQuery table:
- Set Generate SQL at runtime to No, and then provide a SQL statement containing the temporary staging table TEMP_EXTERNAL_TABLE to be executed in the User-defined SQL statement property. Also, specify the name of Google cloud storage bucket to be used as a temporary staging area.
- Set Generate SQL at runtime to Yes, then
the following properties need to set:
- Set the name of the schema or dataset in Schema name property.
- Specify the name of the target table in the Table name property.
- Optionally, specify the name of the Google project Id where the table resides, in the Database name field. If it is not specified, the project id used during BigQuery connection will be used to generate the qualified table name.
- Specify the type of table action required to be performed on the table before BigQuery Connector
stage writes the data. The following table lists out the methods you can configure for
Table action:
Table 1. Configure the connector for table action Method Description Append Append creates the table if the table doesn’t exist. If the table already exists, no action is performed. Replace Replace mode drops the existing table and creates a new table with the existing job design schema. Existing table is dropped only when there is no existing streaming buffer attached to the table. - Specify how BigQuery connector stage writes data to BigQuery table. The following table shows
the ways that you can configure the connector for Write mode:
Table 2. Configure the connector for write mode Method Description Insert Insert mode inserts data into specified table using Big Query streaming inserts. Update then Insert Update then Insert mode first updates the table based on the key columns specified and then inserts the data into the table. Update Update mode only updates the table data based on the key columns. Delete Delete mode only deletes the table data based on the key columns Delete then Insert Delete then Insert mode first deletes the table data based on the key columns and the inserts the data into the table - Optionally, you can specify a comma separated list of column names in Key column
names property, when any write mode is selected other than Insert.
Usage - When no value is provided for this property, the primary key columns defined in the column definition of the job will be used as keys during the write mode operations. When key column property is specified, the property takes precedence over the keys defined in the column definition.
- Specify the name of Google cloud storage bucket to be used as a temporary staging area for all write modes other than insert.
- Optionally, the File name prefix for the temporary file to be created under the Google cloud storage bucket can be provided, which will be deleted at the end of the job.
- Click Ok and Save the job.