Using a Google BigQuery V2 connection in a process
You can perform create, read, update, and delete (CRUD) operations on Google BigQuery tables by using a Google BigQuery V2 connection in your process. Use these operations to manage data in Google BigQuery tables directly from Application Integration workflows.
Guidelines
Consider the following guidelines when you work with Google BigQuery V2 connections in a process:
•Ensure that you have the appropriate permissions to perform operations on the Google BigQuery tables.
•Use process fields to pass dynamic values to queries and operations.
Read data from a Google BigQuery table
You can use a SQL WHERE clause within an Assignment step to read data from a Google BigQuery table. You can filter the data, select specific fields, and assign the query results to process output fields that you use in subsequent steps. After you create a Google BigQuery V2 connection, you can validate, test, and save the connection. You can then publish the Google BigQuery V2 connection and click the Metadata tab to view the generated process objects for the connection.
Perform the following sample steps to read data from a Google BigQuery table:
1In the Process Designer, open the process.
2Create a process output field to store the query results.
For example, create a User object output field.
3Add an Assignment step to the process.
4Click the Assignments tab.
5In the Field column, select the output field you created.
6In the Assignment column, enter the SQL query WHERE clause. For example, id = {$input.userid}
7Save the assignment.
For example, to read a user with ID 78798, use the query id = 78798. The result is stored in the output field and can be used in subsequent process steps.
Write data to a Google BigQuery table
You can write data to a Google BigQuery table by inserting new records or updating existing records.
To insert new records, you use a Create step and map process fields to the table fields. The Create step writes the data to the table when the process runs.
To update existing records, you first query the row that you want to update and then use an Assignment step to modify specific field values. Use this approach to change one or more fields in a row without affecting other fields.
Note:
You can perform update operations only on tables that have primary keys. Ensure that you have custom keys set up for the tables that you want to update.
Insert data into a Google BigQuery table
You can insert new records into a Google BigQuery table by using a Create step.
Perform the following sample steps to insert data into a Google BigQuery table:
1In the Process Designer, open the process.
2Add a Create step to the process.
3In the Create step, click the Object field and select Google BigQuery V2 connection.
4In the Connection field, select the Google BigQuery V2 connection.
5In the Type field, select the object type that corresponds to your Google BigQuery table.
For example, select User if you want to insert data into the User table.
6Map process fields to the table fields.
For example:
- Map {$input.userid} to the id field
- Map userName to the name field
- Map userAge to the age field
7Save the step.
Application Integration writes data to the Google BigQuery table when the process runs.
To insert a new user with ID 456, name "John Smith", and age 30, map the following process fields to the table fields:
•The userId (value: 456) to the table field id
•The process field userName (value: "John Smith") to the table field name
•The process field userAge (value: 30) to the table field age
Update data in a Google BigQuery table
You can update existing data in a Google BigQuery table by first querying the row and then modifying the field values in an Assignment step.
Perform the following sample steps to update data in a Google BigQuery table:
1Query the row that you want to update.
In an Assignment step, use a SQL query to retrieve the row and assign it to a temporary process field.
For example, to query a user with a specific ID, use id={$input.userid}.
2Assign the query result to a process field such as $temp.user.
3Add another Assignment step to update the field value.
4In the Field column, select the field that you want to update by navigating through the temporary process field.
For example, select $temp.user > name to update the user name.
5In the Assignment column, enter the new value or map a process field that contains the new value.
For example, enter newUserName to update the name field.
6Save the assignment.
The table row is updated with the new value when the process executes.
Delete data from a Google BigQuery table
You can delete records from a Google BigQuery table by first querying the row and then use a Delete step to remove it.
Perform the following sample steps to delete data from a Google BigQuery table:
1Query the row that you want to delete.
2In an Assignment step, use a SQL query to retrieve the row and assign it to a temporary process field.
For example, use id={$input.userid} and assign the query result to a process field such as $temp.user.
3Add a Service step.
aOn the Service tab, select System Service as the service type. Select Delete Object as the action.
bOn the Input Fields tab, select the connection that contains the object to delete. For example, Google BigQueryV2 connection.
Similarly, enter the Object Type and Object ID.
For example, select $temp.user.
4Save the step.
Guidelines
Consider the following guidelines when you delete a table using Google BigQuery V2 connections in a process:
•For delete operations, tables must have primary keys defined.
•Always query the row before deleting to ensure you are deleting the correct record.
•Verify the row before you run the delete operation. The operation is permanent.