| Appsplus panel macro confluence macro | ||
|---|---|---|
| ||
**Delete Before Publishing**
|
In this article
| Table of Contents | ||||||
|---|---|---|---|---|---|---|
|
Overview
You can use this Snap to...This Snap enables you to perform bulk update or insert (upsert) operations into a BigQuery table from existing tables or any input data stream.
The upsert operation updates existing rows if the specified value exists in the target table and inserts a new row if the specified value does not exist in the target table.
...
Snap Type
The <Snap name> This Snap is a Read/ Write/Parse/Transform/Flow/Format-type Snap that reads/fetches/writes/parses/executes/transforms/calls/creates…
Prerequisites
Valid client ID.
A valid account with the required permissions.
None.
performs a bulk upsert operation.
Prerequisites
Write access for the Google BigQuery Account is required.
Support for Ultra Pipelines
...
Works in Ultra Pipelines.
...
Works in Ultra Pipelines if....
Does not
...
work in Ultra Pipelines.
...
Limitations
...
None.
and Known Issues
None.
Snap Views
Type | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
|---|---|---|---|---|
Input |
Document
BinaryDocument |
|
Mapper
Copy
..
|
| This Snap has exactly one document input view. Input can come from any Snap that can pass a document to the output view, such as Structure or JSON Generator. Pipeline parameters can also be passed for project ID, dataset ID, and table ID, and so on. |
Output | Document |
Binary
Binary or Document
|
| The output is in document view format. The data from the incoming document that is loaded to the destination table is the output from this Snap. It gives the load statistics after the operation is completed The output view contains information about the bulk load details in the temporary table to better understand the flow. This also helps with error handling. The |
output view also lists the number of rows that were updated, modified, or inserted in the target table. | ||||
Error | Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the | |||
pipeline by choosing one of the following options from the When errors occur list under the Views tab:
|
Learn more about |
Snap Settings
...
| data | {"features":["title","icon","rounded","collapsible","border"],"title":"Documenting Fields Based On Data Type/UI Element","titleColor":"#000000","titleColorBG":"#ff5c30","titleSize":"14","titleBold":true,"titleItalic":false,"titleUnderline":false,"lozenge":"Hot stuff","lozengeColor":"#172B4D","lozengeColorBG":"#fff0b3","height":200,"panelPadding":12,"panelColor":"#172B4D","panelColorBG":"#FFFFFF","borderColor":"#ff5c30","borderRadius":3,"borderStyle":"solid","borderWidth":1,"icon":"editor/info","iconPrimary":"#FFFFFF","iconSecondary":"#0052CC","newMacro":false} |
|---|
**Delete Before Publishing**
Choose from the following sentences to document specific field types.
Drop-down lists/Option Buttons (radio buttons):
You must list the LoV and describe them if their meaning isn't apparent. In this case, format the LoV in italics, regular font for the LoV's description. In either case, list the LoVs as a bullet list.
<State what the option should do in this field>. The available options are: <bullet list of LoVs>
Specify the Salesforce API to be used. The available options are:...Select the <category> that you want to use. Available options are...
* Option 1<italicized>. <third person singular form of the verb>
* Option 2<italicized>. <third person singular form of the verb>
Select the API that you want to use. Available options are:
Bulk API. Sends the Snap execution request details as a bulk API call.
REST API. ...
OR
Select one of the three following modes in which the Snap executes:
* Validate & Execute. Performs limited execution of the Snap and generates a data preview during Pipeline validation, then performs full execution of the Snap (unlimited records) during Pipeline runtime.
* Execute only. Performs full execution of the Snap during Pipeline execution without generating preview data.
* Disabled. Disables the Snap and all Snaps downstream from it.
Check boxes:
If selected, <Snap behavior>.
If selected, an empty file is written when the incoming document has no data.If selected, <behavior>. If not selected/Otherwise, <behavior>
Use "If not selected" if the first sentence is long.
If selected, the Snap uses the file path value as is. Otherwise, the Snap uses the file path value in the URL.
If selected, an empty file is written when the incoming document has empty data. If there is no incoming document at the input view of the Snap, no file is written regardless of the value of the property.Select to <action>
Use this if the behavior is binary. Either this or that, where the converse behavior is apparent/obvious.
Select to execute the Pipeline during validation.
Text Fields
Describe what the user shall specify in this field. Additional details, as applicable, in a separate sentence. Include caveats such as the field being conditionally mandatory, limitations, etc.
Enter the name for new account.
Specify the account ID to use to log in to the endpoint.
Required if IAM Role is selected.
Do not use this field if you are using batch processing.
Numeric Text Fields
Describe what the field represents/contains. Additional details, as applicable, in a separate sentence. Include caveats such as the field being conditionally mandatory, limitations, etc. Include special values that impact the field's behavior as a bullet list.
The number of records in a batch.
The number of seconds for which you want the Snap to wait between retries.
The number of seconds for which the Snap waits between retries.
Use the following special values:
* 0: Disables batching.
* 1: Includes all documents in a single request.
Notes in field descriptions
Confluence’s new editor does not allow nesting of most macros inside another macro, especially the Note/Alert/Warning/Info (Panel) macros inside a table macro and Excerpt macros inside Expand or Panel Macro+ macros. So, as a workaround use the Footnotes approach as mentioned below:
...
Assign numbers at the Note locations in the form of follow through phrases like See Note 2 below this table. or such.
...
Snap Settings
| Info |
|---|
|
): Indicates a list that is dynamically populated based on the configuration.Field Name | Field Type |
|---|
Description | |
|---|---|
Label |
Default Value: |
BigQuery Bulk Upsert (Streaming) |
GBQ Load Employee Tables | String |
None.
Specify the name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your |
pipeline. |
Project ID Default Value: N/A | String/Expression |
Sampling Type is Number of records.
Enter the number of records to output.
fieldset Name
Specify advanced parameters that you want to include in the request.
Field 1*
Default Value: <value> or None.
Example: <value>
String
Debug mode check box is not selected.
Field 2
Default Value: <value> or None.
Example: <value>
String
None.
Snap Execution
Default Value:
Example: Validate & Execute
Dropdown list
N/A
/Suggestion | Specify the project ID in which the dataset resides. | |
Dataset ID
Default Value: N/A Example: dataset-12345 | String/Expression/Suggestion | Specify the dataset ID of the destination. |
Table ID
Default Value: N/A | String/Expression/Suggestion | Specify the table ID of the table you are creating. |
Batch size Default value: 1000 | String | The number of records batched per request. If the input has 10,000 records and the batch size is set to 100, the total number of requests would be 100. |
Batch timeout (milliseconds) Default value: 2000 | String | Time in milliseconds after which the batch will be processed (even though it might be less than the specified batch size). Batch timeout value must be set with care. When this limit is reached, the batch will be flushed whether or not all the records in the batch were loaded. |
Batch retry count Default value: 0 | String | The number of times the server should try to load a failed batch. |
Batch retry delay (milliseconds) Default value: 500 | String | The time delay between each retry. |
Snap Execution
Default Value: Validate & Execute | Dropdown list | Select one of the three modes in which the Snap executes. Available options are:
|
Troubleshooting
Error | Reason | Resolution |
|---|
"All table columns have already been selected in the Snap configuration
As stated in the error message itself.
static final String ERR_ALL_COLUMNS_SELECTED_RESOLUTION = "Please make sure that at least " + "one column has not been selected to enable suggestions";
Account validation failed.
The Pipeline ended before the batch could complete execution due to a connection error.
Verify that the Refresh token field is configured to handle the inputs properly. If you are not sure when the input data is available, configure this field as zero to keep the connection always open.
Examples
Excluding Fields from the Input Data Stream
We can exclude the unrequired fields from the input data stream by omitting them in the Input schema fieldset. This example demonstrates how we can use the <Snap Name> to achieve this result:
<screenshot of Pipeline/Snap and description>
...
Key column name is required. | No key column(s) specified for checking for existing entries. | Please enter one or more key column names. |
Key column name is not present in target table. | Incorrect key column(s) specified for checking for existing entries. | Please select one or more key column names from the suggestion box. |
All columns in target table are key columns. | The merge will fail as all columns in the target table are key columns. | Please select one or more (but not all) key column names from the suggestion box. |
Examples
Prerequisite: Write access for the Google BigQuery Account is required.
Upsert customer data from Salesforce to a Google BigQuery table
This example demonstrates how to update or insert (upsert) records in a Google BigQuery table.
...
First, we configure the Salesforce Read Snap with the required details to read customer account data from Salesforce.
In this example, we selected Output Fields for Total, Id, and Name.
...
Upon validation, the Snap prepares the output to pass to the BigQuery Bulk Upsert Snap.
Next, we configure the BigQuery Bulk Upsert Snap to use unique identifiers to update the existing records.
To upsert data based on the Id and Name key columns, we enter Id and Name in the Key column fields.
...
Upon execution, this Snap updates or inserts new records into the Google BigQuery table.
The output shows that 5 records were updated successfully.
...
In this example, we updated the Total for each record (based on the unique identifiers Id and Name selected under Key columns).
The data is updated in the Google BigQuery table, as shown in the BigQuery console.
...
Downloads
| Info |
|---|
|
| Attachments | ||||
|---|---|---|---|---|
|
Snap Pack History
Expand
| title | Click here to expand... |
|---|
| Insert excerpt |
|---|
|
|
Related Content
...
Endpoint Doc Link 1
...
Endpoint Doc Link 2
...
Endpoint Doc Link 3
...
|
...