Start tracking your progress
Trailhead Home
Trailhead Home

Query Big Objects

Learning Objectives

After completing this unit, you’ll be able to:
  • Use standard SOQL to query big objects.
  • Use Async SOQL to query big objects.
  • List the pros and cons of using Async SOQL over standard SOQL.

SOQL and Async SOQL

Big objects can be queried using SOQL or Async SOQL. Async SOQL uses a subset of SOQL commands. And it was designed from the ground up to handle the massive volume of data that can be kept within a big object. Because Async SOQL queries are run asynchronously, you don’t have to worry about queries timing out. Async SOQL queries run in the background, and can be run over Salesforce entity data, standard objects, custom objects, and big objects. Async SOQL is implemented via the Chatter REST API.

Generally, you want to use Async SOQL over standard SOQL when you’re dealing with large amounts of data. If you only need a small dataset from a big object, or if you need the results immediately, use standard SOQL. Async SOQL also works on standard and non-big custom objects, but support for those objects is in pilot.

Remember that while custom big objects are included with every license, Async SOQL is included only with the licensing of additional big object capacity.

Use standard SOQL when:
  • You want to display the results in the UI without having the user wait for results.
  • You want results returned immediately for manipulation within a block of Apex code.
  • You know that the query will return a small amount of data.
Use Async SOQL when:
  • You are querying against millions of records.
  • You want to ensure that your query completes.
  • You don’t need to do aggregate queries or filtering outside of the index.

Async SOQL use case.

In a nutshell, Async SOQL lets you take the millions of records in a big object and extract relevant data into a manageable, working dataset.

How to Use SOQL to Query Big Objects

If you’re already familiar with using SOQL, you’ll feel right at home here. There are a few things to keep in mind when using standard SOQL with big objects that are different than using SOQL with non-big objects. For SOQL queries with big objects, you have to build your query starting from the first field defined in the index, without skipping any fields between the first and last field in the query. So for example, if your index defines three fields, you can’t create a query using only the first and third fields.

You can use these comparison operators =, <, >, <=, >=, or IN on the last field in your query. Any prior fields in your query can only use the = operator. The !=, LIKE, NOT IN, EXCLUDES, and INCLUDES operators aren’t valid in any query involving big objects.

The following queries assume that you have a big object in which the index is defined by Account__c, Game_Platform__c, and Play_Date__c.

This query specifies all three fields in the index. In this case, the filter on Play_Date__c can be a range.
SELECT Account__c, Game_Platform__c, Play_Date__c
FROM Customer_Interaction__b
WHERE Account__c='001R000000302D3' AND Game_Platform__c='PC' AND Play_Date__c=2017-09-06T00:00:00Z
This query doesn’t work because of a gap in the query where Game_Platform__c should be.
SELECT Account__c, Game_Platform__c, Play_Date__c
FROM Customer_Interaction__b
WHERE Account__c='001R000000302D3' AND Play_Date__c=2017-09-06T00:00:00Z

How to Use Async SOQL to Query Big Objects

There are two main ways to use Async SOQL to get a manageable dataset out of a big object. The first is to use filtering. You can use filtering to extract a small subset of your big object data into a custom object. You can then use it in your reports, dashboards, or other nifty analytic tool.

The other way to create a manageable dataset is through coarse aggregations. These are the aggregate functions supported by Async SOQL: AVG(field), COUNT(field), COUNT_DISTINCT(field), SUM(field), MIN(field), MAX(field). These aggregate functions give you much finer control over what data is extracted from the big object.

Forming Your Query

Using Async SOQL, we query our custom big object Customer_Interaction__b, and direct the results to our target object TargetObject__c. We extract the account and in-game purchase information from a specific date from our custom big object to our target object, which we can then use for reports and analysis.

Required Fields
Name Type Description
query String Specifies the parameters for the SOQL query you want to execute.
targetObject String A standard object, custom object, external object, or big object into which to insert the results of the query.
targetFieldMap Map<String, String>

Defines how to map the fields in the query result to the fields in the target object.

When defining the targetFieldMap parameter, make sure that the field type mappings are consistent. If the source and target fields don’t match, these considerations apply.
  • Any source field can be mapped onto a target text field.
  • If the source and target fields are both numerical, the target field must have the same or greater number of decimal places than the source field. If not, the request fails. This behavior is to ensure that no data is lost in the conversion.
  • If a field in the query result is mapped more than once, even if mapped to different fields in the target object, only the last mapping is used.
Example URI
https://yourInstance.salesforce.com/services/data/v41.0/async-queries/

POST Request Body

{ 
   "query": "SELECT Account__c, In_Game_Purchase__c FROM Customer_Interaction__b WHERE Play_Date__c=2017-09-06T00:00:00Z",
   
   "operation": "insert",
   
   "targetObject": "Customer_Interaction_Analysis__c", 
        
   "targetFieldMap": {"Account__c":"Account__c",
                      "In_Game_Purchase__c":"Purchase__c"
                      },
   "targetValueMap": {"$JOB_ID":"BackgroundOperationLookup__c",
                      "Copy fields from source to target":"BackgroundOperationDescription__c"
                     }
}
In this query, we’re taking the account and in-game purchase information from our customer interaction big object from a specific date, 2017-09-06, and sending that data into a custom object we’ve created, Customer_Interaction_Analysis__c. Because we’re using Async SOQL and not standard SOQL, we can filter without worrying about including the other indexed fields. This new custom object is populated with all the account and in-game purchase information from that date. And from there we can start to do some analysis and try to pinpoint why our game is so astonishingly successful.

POST Response Body

The response body includes the query’s jobId, the status of your query, and any relevant messages.
{ 
   "jobId": "08PD000000003kiT", 
   
   "message": "",
    
   "query": "SELECT Account__c, In_Game_Purchase__c FROM Customer_Interaction__b WHERE Play_Date__c=2017-09-06T00:00:00Z",  
    
   "status": "New",
     
   "targetObject": "Customer_Interaction_Analysis__c", 
     
   "targetFieldMap": {"Account__c":"Account__c", 
                      "In_Game_Purchase__c":"Purchase__c"
                     },
   "targetValueMap": {"$JOB_ID":"BackgroundOperationLookup__c",
                      "Copy fields from source to target":"BackgroundOperationDescription__c"
                     } 
}

Tracking the Status of Your Query

To track the status of a query, specify its jobID with an HTTP GET request. The query’s status is returned in the status field. If you don’t specify a jobID, we return the status of all your queries. The status can be:
  • Canceled—The job was canceled before it could be run.
  • Success—The job was successfully completed.
  • Failed—The job failed after the system submitted it or because the request exceeded the Async SOQL limits. The message field provides details on the reason for failure.
  • Running—The job is running successfully, and the org hasn’t exceeded any limits.
  • Scheduled—The new job has been created and scheduled, but is not yet running.

You can also cancel a query using an HTTP DELETE request by specifying its jobId. Canceling a completed query has no effect.

Remember, Async SOQL is done asynchronously. That means that your query can take a while to complete. Like we mentioned earlier, if time is of the essence and if your dataset is small enough, use standard SOQL. Alternatively, we can dream up a less popular fictional game to use in these examples instead, but where’s the fun in that?

Example URI
https://yourInstance.salesforce.com/services/data/v41.0/async-queries/08PD000000003kiT
Example GET Response
{
"jobId": "08PD000000003kiT",
"message": "",
"query": "SELECT Account__c, In_Game_Purchase__c FROM Customer_Interaction__b WHERE Play_Date__c=2017-09-06T00:00:00Z",  
"status": "Success",
"targetObject": "Customer_Interaction_Analysis__c",
"targetFieldMap": {"Account__c":"Account__c",
"In_Game_Purchase__c":"Purchase__c" } 
}
This response shows that our query is successfully completed. Woohoo! Time to get your reports and dashboards ready!

Handling Errors

Two different types of errors can occur during the execution of an Async SOQL query.
  • An error in the query execution
  • One or more errors writing the results into the target big object

Submitting an invalid query and exceeding one of the Async SOQL limits are examples of execution problems. Another example is when a query causes a problem with the underlying infrastructure. For these errors, the response body includes a status of Failed. The message parameter provides more information on the cause of the failure.

Other times, the query executes successfully but encounters an error while attempting to write the results to the target object. Because of the volume of data involved, capturing every error is inefficient. Instead, subsets of the errors generated are captured in the BackgroundOperationResult object and retained for seven days. You can query this object with the Async SOQL query jobId to filter the errors for the specific Async SOQL query. Async SOQL job info is retained for a year.

Wrapping It Up

Now you know everything that’s necessary to use custom big objects in your own org. Billions of records are now at your command, muahahaha.

We’ve only scratched the surface of Async SOQL. Be sure to check out the resources section to see all the standard SOQL commands supported by Async SOQL and more use case examples. You’ll also find information on the optional fields you can use in your POST requests. Also check out our Analytics Data Integration Basics module for ideas on how to set up analytics for the data you extract from your big objects. Think of all the nifty graphs and dashboards you can make with all that data!

retargeting