Salesforce Bulk Query

In this article

Overview

You can use this Snap to perform SOQL queries using Salesforce Bulk API 2.0.

To use newer Salesforce objects, use the latest API versions where those objects are available.

Prerequisites

Valid Salesforce OAuth 2 account with Read permissions.

Support for Ultra Pipelines

Works in Ultra Pipelines.

In Ultra Pipelines, set the Batch Size as 1 for the Snap to process one document/record at a time.

Limitations

  • This Snap performs batch processing; a batch of input documents is processed for each HTTP request sent to Salesforce. Values of all the expression-enabled fields must remain constant during the Snap execution or validation. Hence, all expression fields can support Pipeline parameters only when they are expression-enabled. The input data parameters are not supported for expression fields; for example, $serviceVersion.
  • Can execute up to 10,000 query jobs in a 24-hour rolling window.

  • Can store up to 1TB of query results in a 24-hour rolling window.

  • Supports Salesforce Bulk Query service version 47.0 and above.
  • SOQL queries do not support the following commands:

    • COUNT

    • ROLLUPSUM

    • GROUP BY CUBE

    • OFFSET

    • Nested SOQL queries

Snap Views

TypeFormatNumber of ViewsExamples of Upstream and Downstream SnapsDescription
Input Document
  • Min: 0
  • Max: 1
  • Mapper Snap
  • Filter
  • Router
A document containing values required for the bulk query.
OutputDocument
  • Min: 1
  • Max: 1
  • Document to Binary
  • Salesforce Upsert

A document containing the results of the query performed by the Snap.

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 while running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are:

  • Stop Pipeline Execution: Stops the current pipeline execution when the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the rest of the records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

  • Asterisk (*): Indicates a mandatory field.

  • Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon (): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon (): Indicates that you can add fields in the field set.

  • Remove icon (): Indicates that you can remove fields from the field set.


FieldField TypeDescription

Label*

DefaultValue:Salesforce Bulk Query
Example
:Salesforce Bulk Query

String

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.


Service Version*

Default Value:52.0 





Example
:41.0

String/Expression/Suggestion

Specify the version number associated with the Salesforce service you want to connect to. Alternatively, click the Suggestion  icon to fetch the list of versions and select the desired version.

SOQL Query*

DefaultValue: None
Example
:"select FirstName, LastName from " + $object + " order by Name LIMIT 5"

"SELECT Name FROM Account"
"SELECT Id,Name,Phone FROM Account WHERE Id='" + $Id + "'and
Name='" + $name + "'"

String

Specify the query in SOQL format. You can enter a JavaScript expression that is evaluated each time the Snap is executed. As in the second example below, on each document received from the input view, the % symbols will be replaced with the value pointed to by the JSON path $.Id in the input data.

Using quotes in the WHERE clause

  • Do not use quotes for field names in the WHERE clause. Using quotes results in an error.
  • Use only single quotes for values in the WHERE clause, as using double quotes results in an error.
  • The above rules do not apply when using SnapLogic expressions; you can use quotes for the field names and values as applicable. 

Max Records Per Set*

Default Value:10000
Example
:5000

Integer/Expression

Specify the number of records you want the Snap to fetch in a single set.


Maximum threads


Default Value: 10
Example: 9

Integer/Expression

Specify the maximum number of threads to download query results simultaneously in multiple threads. The Snap uses the thread pool to download the query results.

  • The query results are downloaded only if the Maximum Records Per Set value is greater than or equal to 10000.

  • If you expect the query results to be large, we recommend that you increase the values of the Max Records Per Set and Maximum threads for optimal performance.

  • This field does not support upstream values for Maximum threads. However, it supports values from pipeline parameters.

Minimum value: 1
Maximum value: 200

Polling Interval*

Default Value5
Example
:10

Integer/Expression

Specify the number of seconds you want the Snap to wait between two queries. At each polling interval, the Snap checks the Bulk API query batch processing status.

Minimum value: 1
Maximum value: 60

Polling Timeout*

Default Value:3000
Example
:300

Integer/Expression

Specify the number of seconds into the query batch execution before the query fails if it receives no response. If the timeout occurs while waiting for the completion of the query batch execution, the Snap throws a SnapExecutionException.

Minimum value: 10 


Maximum Retry Attempts


Default 
Value
5
Example
1
Integer/Expression

Specify the maximum number of retry attempts the Snap must make in case of a network failure. 

Minimum value: 1

Retry Interval (seconds)

Default Value:3
Example
:3

Integer/Expression

Specify the minimum number of seconds the Snap must wait before attempting recovery from a network failure.

Minimum value: 0

Include Deleted Records

Expression/Checkbox

Select this checkbox to include deleted records in the query result. This feature is supported in Bulk API version 47.0 or later.

This field supports input values from the upstream Snap. However, it does not support pipeline parameters.

  • When you select the checkbox and enable the expression, the string value is displayed as true:

  • When you deselect the checkbox and disable the expression, the string value is displayed as false:

Match Data Type

Default ValueDeselected

Checkbox

Select this checkbox to match the data types of the bulk query job results. On selecting this checkbox, the Snap converts string values to the corresponding data types if the original data type is one of Boolean, Integer, Double, Currency, and Percent. 

The Salesforce response for null values is independent of the selection of the Match Data Type checkbox.

Display null or empty values as null


Default ValueDeselected

Checkbox

Select this checkbox to display empty string or null data as null in the output preview.

When you deselect this checkbox, the Snap displays an empty string or null data as blank in the output preview.

For a better understanding, refer to this Example.

The output in the Salesforce application remains unaffected. Regardless of the state of the checkbox (selected or deselected), the Salesforce record and its corresponding output continue to appear empty, as shown below: 

Object Type


Default Value
: N/A
Example: String

String/Expression

Appears on selecting the Match Data Type checkbox.

Specify the object type to match. Alternatively, select an object type from the suggestions list to match the data type.

This Snap does not support Net Zero Cloud Salesforce objects.

Snap Execution

DefaultValueValidate & Execute
Example
:Execute only

String

Select one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap and generates a data preview during Pipeline validation. Subsequently, it 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 that are downstream from it.


Troubleshooting

ErrorReasonResolution
Invalid value: 201

The Maximum thread value is larger than the required maximum, which is 200.

Provide 200 or a value lesser than 200 (maximum value) in the Maximum threads field.

Temporary Files

During execution, data processing on Snaplex nodes occurs principally in-memory as streaming and is unencrypted. When larger datasets are processed that exceeds the available compute memory, the Snap writes Pipeline data to local storage as unencrypted to optimize the performance. These temporary files are deleted when the Snap/Pipeline execution completes. You can configure the temporary data's location in the Global properties table of the Snaplex's node properties, which can also help avoid Pipeline errors due to the unavailability of space. For more information, see Temporary Folder in Configuration Options

Examples

Querying for Contacts in Salesforce Using Upstream Parameters in the Bulk Query Snap

The Salesforce Bulk Query Snap enables you to retrieve multiple data values from your Salesforce database using a simple query, where you can provide all the details associated with your request in the Snap. This Snap also enables you to use values derived from upstream Snaps. In this example, you query your Salesforce database for contact data using values from an upstream Snap. 

Download this Pipeline

Understanding the Pipeline

  1. Build the above Pipeline using the JSON Generator and Salesforce Bulk Query Snaps.

  2. Configure the JSON Generator Snap to send Contact as the type of data that you want to retrieve.


  3. Add the details associated with your bulk query request in the Salesforce Bulk Query Snap. This must include your account credentials.

    In this example, you are looking to receive the first and last names of the first five contacts in your Salesforce database.

  4. Save the Pipeline and validate it. Successful validation displays the data you requested.

Download this Pipeline.

Downloads

Important steps to successfully reuse Pipelines

  1. Download and import the pipeline into the SnapLogic application.
  2. Configure Snap accounts as applicable.
  3. Provide pipeline parameters as applicable.

  File Modified

File Salesforce_BulkQuery_Example.slp

Jan 24, 2020 by Rakesh Chaudhary


Related Content

Snap Pack History

 Click to view/expand
Release Snap Pack VersionDateType  Updates

May 2024

main26341

 

Stable

Updated and certified against the current SnapLogic Platform release.

February 2024436patches25626 LatestEnhanced the Salesforce Bulk Create, Bulk Delete, Bulk Query, Bulk Update, and Salesforce Bulk Upsert Snaps to include the complete error details under the original object that contains the copies of the failed input records.
February 2024436patches25192 Latest

Fixed an issue with the Salesforce Mutual Authentication Account where the 8443 port was not used while authorizing the account. Now, the access token request uses the 8443 port for authorizing the Mutual Authentication Account.

Breaking change

Prior to 436patches25192, the Salesforce Mutual Authentication Account supported default Login URLs such as https://login.salesforce.com and https://test.salesforce.com. From 436patches25192, this account no longer supports the default Login URLs—your existing pipelines using the Mutual Authentication Account with the default Login URLs might break.

To prevent this breaking change, you must provide a valid Salesforce Login URL as part of your account configuration.

February 2024main25112 StableEnhanced the Salesforce Bulk Query Snap with expression support for the Include Deleted Records checkbox.
November 2023435patches24747 LatestFixed an issue that caused the input stream to not close properly, which resulted in stale connections.
November 2023435patches24368 Latest

Enhanced the performance of the Salesforce Bulk Query Snap with a new Maximum threads field. This field enables you to download query results simultaneously in multiple threads.

November 2023435patches24006 Latest

Enhanced the Salesforce Read Snap by adding expression enablement to the following checkboxes:

  • Use PK chunking if supported

  • Include Deleted Records

November 2023main23721 StableUpdated and certified against the current SnapLogic Platform release.
August 2023434patches23646 LatestFixed an issue where a long-running pipeline containing the Salesforce Subscriber Snap would suddenly stop responding.
August 2023434patches22537 Latest

Improved the Salesforce Bulk Upsert Snap error messages by limiting the length of the reason displayed with the error.

August 2023main22460 StableUpdated and certified against the current SnapLogic Platform release.
May 2023433patches21367 Latest
  • The Salesforce SOQL Snap now honors the selection of the Match Data Type checkbox when the value entered for Batch Size is greater than 50,000.

  • The Salesforce Read Snap now honors the selection of the Match Data Type checkbox if the Use PK chunking if supported checkbox is also selected.

May 2023

main21015 

Stable

Upgraded with the latest SnapLogic Platform release.

February 2023432patches20586 Latest

Fixed an issue with the Salesforce SOQL and Salesforce Read Snaps where the Match Data Type checkbox selection was not honored if the Salesforce API field was set to Bulk API.

February 2023432patches20393 Latest
February 2023432patches20145 LatestFixed an issue with the Salesforce Read Snap that was causing an error for the Time data type.
February 2023432patches20087  Latest

The Salesforce Create, Update, and Upsert Snaps now include an Apply active assignment rules checkbox so you can control whether to use assignment rules for the selected object

February 2023

432patches20008

 Latest
  • Fixed an issue with the Salesforce Read Snap where using the bulk API request would fail due to column specification.
  • Added support to use PK chunking on any batch size with the bulk API request and to automatically disable PK chunking if it is known that it cannot be done

February 2023main19844 StableUpgraded with the latest SnapLogic Platform release.
November 2022main18944 Stable Upgraded with the latest SnapLogic Platform release.
October 2022430patches18769 Latest
  • The Salesforce Bulk Query Snap now includes the following checkboxes:

    • Match Data Type: Select this checkbox to match the data types of the bulk query job results.

    • Include Deleted Records: Select this checkbox to include deleted records in the query result.

  • The Salesforce Delete Snap in REST API no longer fails with NPE if a new OAuth2 account is used.

  • The Salesforce Read Snap now stops gracefully on a forced stop (without throwing cryptic errors).

  • The Salesforce Update and Salesforce Upsert Snaps no longer fail with the message "Error occurred while executing request to Salesforce.com" when a redirection is needed.

September 2022430patches18125 Latest

The Salesforce Bulk Create, Salesforce Bulk Delete, and Salesforce Bulk Update Snaps are now working as expected without displaying the error Error while processing Data and without returning 301-error while processing the data.

September 2022430patches18036 Latest

The Salesforce Read Snap now correctly parses the 2-byte UTF-8 characters in Windows OS in the PK chunking mode.

August 2022main17386 Stable
  • Updates in Salesforce Subscriber Snap:

    • CometD version upgrade from 5.0.9 to 7.0.6 (latest).

    • The simplified logging provides useful diagnostic information without logging any sensitive data.

    • The Snap asynchronously makes callbacks or listens to the CometD API to identify and respond to specific events.

    • The Snap processes the most recent message so that it can send that replay ID if there is a need to resubscribe. If the server responds with an invalid replay ID, the Snap automatically sets the Replay ID value to -2 to get all available messages.

  • You can select the Null Setting with Bulk API checkbox in the Salesforce Update, Salesforce Create, and Salesforce Upsert Snaps to format null values in the input document.

4.29 Patch

429patches16708 Latest
  • Updates in Salesforce Subscriber Snap:
    • Upgraded the Cometd version from 5.0.9 to the latest 7.0.6 version.
    • Fixed an issue where the Snap reported an error and stopped only at the next resubscribe attempt when it encountered the daily limit exceeded error. Now, the Snap displays the error and stops immediately when it encounters the daily limit exceeded error.
    • Simplified the logging that provides useful diagnostic information without logging any sensitive data.
    • Enhanced the Snap design to make the callbacks or listeners asynchronously by the Cometd API when there is a specific event, and the Snap responds accordingly.
    • Enhanced the tracking of replay ID of the most recent message processed so that Snap can send that replay ID if there is a need to resubscribe. If the server responds that the replay ID is invalid, Snap automatically sets the Replay ID value to -2 to get all available messages.
  • Made the Null Setting with Bulk API checkbox in the Salesforce Update, Salesforce Create, and Salesforce Upsert Snaps visible, to format a null value.

4.29main15993 StableUpgraded with the latest SnapLogic Platform release.
4.28 Patch428patches14355 

Latest

Fixed an issue with Salesforce Snaps where the Service Version could not be retrieved from SFDC due to a blocked URL, failing the Snap execution.

4.28main14627