Bulk Query

Introduction

In this blog, I am going show how to use the Salesforce Bulk API query features to fetch a large amount of data from the salesforce.Use the bulk query to efficiently query large data sets and reduce the number of API requests. A bulk query can retrieve up to 15 GB of data, divided into 15 1-GB files. The data formats supported are CSV, XML, and JSON.When a bulk query is processed, Salesforce attempts to execute the query. If the query doesn’t execute within the standard 2-minute timeout limit, the job fails and a QUERY_TIMEOUT error is returned. In this case, rewrite a simpler query and resubmit the batch. If the query succeeds, Salesforce attempts to retrieve the results. If the results exceed the 1-GB file size limit or take longer than 10 minutes to retrieve, the completed results are cached and another attempt is made. After 15 attempts, the job fails and the error message “Retried more than fifteen times” is returned. In this case, consider using the PK Chunking header to split the query results into smaller chunks. If the attempts succeed, the results are returned and stored for seven days. The following flowchart depicts how bulk queries are processed.

 

Walk Through a Bulk Query Sample

Step 1: Log In Using the SOAP API

The Bulk API doesn’t provide a login operation, so you must use SOAP API to log in.Create a text file called login.txt containing the following text and replace the username and password

Run the following command from the cURL command line

Which will return the an XML response that includes <sessionId> and <serverUrl> elements. We are going to use the sessionId for the subsequent request authentication.

Step 2.Create a Job

Create a file called create-job.xml containing the following text.

Using a command-line window, execute the following cURL command to create a job. Replace the session Id which you got in step 1.

You will get the response which will return job information.

Step 3: Add a Batch to the Job

Create a file called query.txt to contain the SOQL query statement. Bulk Query will fetch the data based on this Query

Using a command-line window, execute the following cURL command to add a batch to the job. Replace JobId which you hot in step 2

You will get the batch Id and batch information as response shown below.

Step 4: Check the Status of the Job and Batch

Using a command-line window, execute the following cURL command to check the job status.

You will get the below response along with the job status

Using a command-line window, execute the following cURL command to check the batch status.

Which will return the batch status

Step 5: Retrieve the Results

Using the command-line window, execute the following cURL command to retrieve the batch result list.

Salesforce returns an XML response with data such as the following.

Using the command-line window, execute the following cURL command to retrieve the results of the query in the result.csv file .

Step 6: Close the Job

Create a file called close-job.xml containing the following text.

Using a command-line window, execute the following cURL command to close the job.

Salesforce returns an XML response with data such as the following.