Force.com CLI bulk query returns nothing

Question

I have submitted a simple query using the Force.com CLI like so:

will@mint ~ $ force bulk query CallRecord__c "select Account__c, CallFlow__c from CallRecord__c"
Query Submitted
To retrieve query status use
force bulk query status 750F0000002ok5PIAQ 751F0000004QRtfIAG

To retrieve query data use
force bulk query retrieve 750F0000002ok5PIAQ 751F0000004QRtfIAG

Seems simple enough, so I periodically checked its progress until it was completed:

will@mint ~ $ force bulk query status 750F0000002ok5PIAQ 751F0000004QRtfIAG
Id          751F0000004QRtfIAG
JobId           750F0000002ok5PIAQ
State           Completed
CreatedDate         2015-07-08T21:27:22.000Z
SystemModstamp      2015-07-08T22:02:10.000Z
NumberRecordsProcessed  11313805

But then when I try to retrieve the results, I get nothing:

will@mint ~ $ force bulk query retrieve 750F0000002ok5PIAQ 751F0000004QRtfIAG | tee out.txt

will@mint ~ $ cat out.txt

will@mint ~ $ 

Well, I guess not nothing, but a newline is not the expected results. Am I misunderstanding how bulk queries work, or am I just using force incorrectly?

Answer 1

There are three parts involved here:

1. The AsyncApiJob Id - 750 keyprefix - The Job
2. The AsyncApiBatch Id - 751 keyprefix - The Batch
3. The AsyncApiQueryResult Id - 752 keyprefix - The Batch Result

You appear to have the first two parts, but not the AsyncApiQueryResult Id that will be used to retrieve the actual CSV.

In terms or raw bulk API calls you need to get the AsyncApiQueryResult Id with:

GET baseURI/job/750x00000000014/batch/751x00000000030/result

This will return XML that contains the AsyncApiQueryResult Ids for each batch.

<result-list xmlns="http://www.force.com/2009/06/asyncapi/dataload">
  <result>752x000000000F1</result>
</result-list>

Then you can use this final Id to Get the actual CSV:

GET baseURI/job/750x00000000014/batch/751x00000000030/result/752x000000000F1

I'm aware your question was specifically about the Force.com CLI tool, but thought it might be useful to know about the AsyncApiQueryResult Ids. I'll see if I can figure out how this manifests in the CLI tool.

How are your Go skills? It looks like on line 124 it is calling getBulkQueryResults(args2, args3) to retrieve that batch results.

Then by line 162 it is extracting the bytes from the results ids.

I also found a merged pull request from the 30th of May by mickle00 - Support for force bulk query retrieve to retrieve multiple result files. Fixes #140 #173. Looks like it used to directly assign to 'data' using the first result Id. I suspect this might have broken something.

Answer 2

As Daniel Ballinger's answer and the comments point out, there was a bug in the force cli.

But as of v0.23.0, there's a new --wait option for the bulk query command that let's avoid polling for the job to finish:

$ force bulk --wait query CallRecord__c "select Account__c, CallFlow__c from CallRecord__c" | tee out.txt

Answer 3

2023 Update

Using Salesforce CLI's new "SF" commands simplifies things a lot.

Query:

sf data query -q "SELECT Id FROM Account LIMIT 1" -b --wait 0

That query will return a message similar to this one:

Run "data query resume -i 7504N00000hfIhvQAE -o [email protected]" to get the latest status and results.

Monitor job completion status however you prefer. Once the job is complete, use this command to download all the results:

sf data query resume -i 7504N00000hfIhvQAE -r "csv" > myCsv.csv

Where "7504N00000hfINRQA2" is your job Id. The '-r "csv" > myCsv.csv' portion of this command pipes the results into a csv file.

Source: https://developer.salesforce.com/docs/atlas.en-us.sfdx_cli_reference.meta/sfdx_cli_reference/cli_reference_data_commands_unified.htm#cli_reference_data_query_resume_unified