BULK API cURL commands to GET 100k records from Account

Question

curl "https://xxx.salesforce.com/services/data/v35.0/query/?q=SELECT+Id,+Name,+Phone+FROM+Account" -H "Authorization: Bearer <access token> "

But this only fetching 2000 records. I need all 1Lakhs records to be fetched. is there anyway?

Answer 1

According to the docs here, there is a way to do this without the new Bulk API 2.0. I've verified this method will eventually return all results 2000 records at a time.

If the initial query returns only part of the results, the end of the response will contain a field called nextRecordsUrl. For example, you might find this attribute at the end of your query:

"nextRecordsUrl" : "/services/data/v20.0/query/01gD0000002HU6KIAW-2000"

In such cases, request the next batch of records and repeat until all records have been retrieved. These requests use nextRecordsUrl, and do not include any parameters.

Example usage for retrieving the remaining query results

curl https://yourInstance.salesforce.com/services/data/v20.0/query/01gD0000002HU6KIAW-2000 -H "Authorization: Bearer token"

---

For the current preferred method using the new Bulk API 2.0 on larger queries, here's a curl write up that follows the documentation here since the walkthrough there is using workbench and a session id instead of the Bearer token.

Please note that according to the docs,

This resource is available in API version 47.0 and later.

Make sure you are using the correct API version number in the url you are passing or you will get a resource not available error.

To start we Create the query job

If you read the walk through you notice that they use an xml file named create-job.xml to pass in query parameters. We can also use a json file with query parameters.

[request.json file]

{
  "operation" : "query",
  "query":"SELECT Id, Name, Phone FROM Account",
  "contentType":"CSV"
}

Then the curl command to setup the bulk query would be

curl https://yourInstance.salesforce.com/services/data/v47.0/jobs/query -H "Authorization: Bearer <token>" -H "Content-Type: application/json; charset=UTF-8" -H "Accept: application/json" -d "@request.json" 

This will return a response with the job id - which will be required to access the results (shown below)

The next step would be to Retrieve the results

The first part here is pretty straightforward. Run the following command to retrieve the first set of results

curl -i https://yourInstance.salesforce.com/services/data/v47.0/jobs/query/<jobid>/results -H "Authorization: Bearer <token>" -H "X-PrettyPrint:1"

Please note the '-i' parameter here is required for larger queries that are processed in multiple batches. That parameter will give you details in the beginning of the response that will include a variable named 'Sforce-Locator'. In my example the query is about 300K records and the locator value is 'Sforce-Locator: 250041'.

So for the next curl call to get the next batch I use

curl -i https://yourInstance.salesforce.com/services/data/v47.0/jobs/query/<jobid>/results?locator=250041 -H "Authorization: Bearer <token>" -H "X-PrettyPrint:1"

And so on until the Sforce-Locator is null.

Hope this helps!

Answer 2

Use the PK Chunking request header to enable automatic primary key (PK) chunking for a bulk query job. PK chunking splits bulk queries on very large tables into chunks based on the record IDs, or primary keys, of the queried records.

If you have PK chunking enabled for the query then the batch size can be from the chunk size 100,000, and the maximum size is 250,000

How to enable PK Chunking

Using a command-line window, execute the following cURL command to create a job with PK chunking enabled.

curl -H "X-SFDC-Session: sessionId" -H "Content-Type: application/xml; charset=UTF-8" -H "Sforce-Enable-PKChunking: true" -d @create-job.xml https://instance.salesforce.com/services/async/42.0/job

instance is the portion of the <serverUrl> element, and sessionId is the <sessionId> element that you noted in the login response.

Salesforce recommends that you enable PK chunking when querying tables with more than 10 million records or when a bulk query consistently times out. For the purposes of this example, if you’re querying significantly fewer records, set chunkSize to a number smaller than the number of records you’re querying. For example, Sforce-Enable-PKChunking: chunkSize=1000. This way, you get to see PK chunking in action, and the query is split into multiple batches.

Please refer below link for more details

https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/asynch_api_code_curl_walkthrough_pk_chunking.htm

https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/async_api_headers_enable_pk_chunking.htm