In this blog, I am going to explain the salesforce Async SOQL. Async SOQL you can use for the long-running data-set which will execute on the data set then the data-set result will be passed to object like a pipeline. Async SOQL is a method for running SOQL queries in the background over Salesforce entity data, including subjects, BigObjects, and external objects (accessed via Lightning Connect). Async SOQL is implemented in the form of a Restful API that enables you to run queries in the familiar syntax of the SOQL language. You can run multiple queries in the background, and monitor their completion status.The results of each query are deposited into an object you specify, which can be a sObject or BigObject. As a result, you can subset, join, and create more complex queries that are not subject to timeout limits. For example, let’s say that you want to analyze the years and years of opportunity history data. The results could help you identify which current and future opportunities are more likely to close and give you a better picture of your forecast by reducing your billion rows of data into the manageable size.
Sort of Pipeline
Async SOQL is acting like a sort of data Pipeline. the pipeline is a set of data processing elements connected in series, where the output of one element is the input of the next one. below image shows the differences.
Async SOQL Versus SOQL
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 do not need to do aggregate queries or filtering outside of the index
Run Async SOQL
To Run Async Queries you need to make the POST REST API Call to /services/data/v41.0/async-queries the endpoint with JSON encoded list of the request body.
{
"query": "SELECT Amount ,ClosedDate ,StageName FROM Opportunity ",
"operation": "insert",
"targetObject": "Sales_OrderBO__b",
"targetFieldMap": {"Amount ":"Actual_Cost__c",
"StageName ":"Saleschannel__c ",
"ClosedDate":"Expired_Date__c"
}
}
The response of an Async SOQL query displays the jobID, SOQL command, details of the field mapping, and the target object. It also displays the query status, which can have one of three values: Running, Complete, or Failed.you can use Job Id to track the status of your Async SOQL.
Few Standard Use Cases from Salesforce docs
1.Data Archiving
To archive records for long-term retention in a BigObject, Async SOQL can easily move any number of records from a sObject into a BigObject. The data archive process can be achieved via the following programmatic flow.
1. Define source sObject records.
2. Define target BigObject(s).
3. Define sObject to BigObject field mappings.
4. Use Async SOQL to copy records from sObject to BigObject storage.
5. Conceive and orchestrate the delete process driven by the parent IDs via APIs.
Create a new job by sending a POST request to the following URI. The request body identifies the type of object to be queried, the target object, and supporting mapping. This example archives all cases whose status is closed.
Example request body
{
"jobId": "08PB000000003NS",
"query": "SELECT id, CaseNumber, ClosedDate, ContactId, CreatedById, Description
FROM Case WHERE Status = 'Closed'",
"status": "Complete",
"targetFieldMap": {"id": "originalCaseId__c",
"CaseNumber": "caseNumber__c",
"ClosedDate": "closedDate__c",
"ContactId": "contactId__c",
"CreatedById": "createdById__c",
"Description": "Description__c"
},
"targetObject": "ArchivedClosedCases__b"
}
Example response body
{
"jobId": "08PB000000003NS",
"query": "SELECT id, CaseNumber, ClosedDate, ContactId, CreatedById, Description
FROM Case WHERE Status = 'Closed'",
"status": "Complete",
"targetFieldMap": {"id": "originalCaseId__c",
"CaseNumber": "caseNumber__c",
"ClosedDate": "closedDate__c",
"ContactId": "contactId__c",
"CreatedById": "createdById__c",
"Description": "Description__c"
},
"targetObject": "ArchivedClosedCases__b"
}
2. Customer 360° and Filtering
in this use case, customers load a variety of customer engagement data from external sources into Salesforce BigObjects and then process the data to enrich customer profiles in Salesforce. The idea is to store customer transactions and interactions such as point-of-sale data, orders, and line items in BigObjects, then process and correlate that data with your core CRM data. In the following example, we want to analyze the customer data stored in the Rider record of a car-sharing service. The source BigObject, Rider_Record_b, has a lookup relationship with the Contact object, allowing for an enriched view of the contact’s car riding history
Example request body
{
"query": "SELECT End_Location_Lat__c, End_Location_Lon__c, End_Time__c,
Start_Location_Lat__c, Start_Location_Lon__c, Start_Time__c,
Uber_Type__c, Rider__r.FirstName, Rider__r.LastName,
Rider__r.Email
FROM Rider_Record__b WHERE Star_Rating__c = '5'",
"targetObject": "Rider_Reduced__b",
"targetFieldMap": {"End_Location_Lat__c":"End_Lat__c",
"End_Location_Lon__c":"End_Long__c",
"Start_Location_Lat__c": "Start_Lat__c",
"Start_Location_Lon__c": "Start_Long__c",
"End_Time__c": "End_Time__c",
"Start_Time__c": "Start_Time__c",
"Uber_Type__c": "Uber_Type__c",
"Rider__r.FirstName": "First_Name__c",
"Rider__r.LastName": "Last_Name__c",
"Rider__r.Email": "Rider_Email__c"
}
}
Example response body
{
"jobId": "08PB000000000NA",
"query": "SELECT End_Location_Lat__c, End_Location_Lon__c, End_Time__c,
Start_Location_Lat__c, Start_Location_Lon__c, Start_Time__c,
Uber_Type__c, Rider__r.FirstName, Rider__r.LastName,
Rider__r.Email
FROM Rider_Record__b WHERE Star_Rating__c = '5'",
"status": "Complete",
"targetFieldMap": {"End_Location_Lat__c":"End_Lat__c",
"End_Location_Lon__c":"End_Long__c",
"Start_Location_Lat__c": "Start_Lat__c",
"Start_Location_Lon__c": "Start_Long__c",
"End_Time__c": "End_Time__c",
"Start_Time__c": "Start_Time__c",
"Uber_Type__c": "Uber_Type__c",
"Rider__r.FirstName": "First_Name__c",
"Rider__r.LastName": "Last_Name__c",
"Rider__r.Email": "Rider_Email__c"
},
"targetObject": "Rider_Reduced__b"
}
a.Field Audit Trail
You can use Async SOQL to query archived fields, stored in the FieldHistoryArchive object. You can use the WHERE clause to filter the query by specifying comparison expressions for the FieldHistoryType, ParentId, and CreatedDate fields,
as long as you specify them in that order.
Example Request body
{
"query": "SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue
FROM FieldHistoryArchive WHERE FieldHistoryType = ‘Account’
AND CreatedDate > LAST_MONTH”,
"targetObject": "ArchivedAccounts__b",
"targetFieldMap": {"ParentId": "ParentId__c",
"FieldHistoryType": "FieldHistoryType__c",
"Field": "Field__c",
"Id": "Id__c",
"NewValue": "NewValue__c",
"OldValue": "OldValue__c"
}
}
Example Response
{
"jobId": "07PB000000006PN",
"query": "SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue
FROM FieldHistoryArchive WHERE FieldHistoryType = ‘Account’
AND CreatedDate > LAST_MONTH”,
"status": "Complete",
"targetFieldMap": {"ParentId": "ParentId__c",
11
Async SOQL Use Cases
"FieldHistoryType": "FieldHistoryType__c",
"Field": "Field__c",
"Id": "Id__c",
"NewValue": "NewValue__c",
"OldValue": "OldValue__c"
}
"targetObject": "ArchivedAccounts__b"
}
b.Event Monitoring
Login Forensics and Data Leakage Detection, both currently in pilot, enable you to track who is accessing confidential and sensitive data in your Salesforce Org. You can view information about individual events or track trends in events to swiftly identify unusual behavior and safeguard your company’s data. This is particularly useful for compliance with regulatory and audit requirements.For example, let’s say you want to know everyone who viewed the contact record of your company’s CEO. The key to this query is the CEO’s contact record Id. Let’s say the CEO’s name is Jane Doe, and her Id is 003D000000QYVZ5
Example Request Body
{
"query": "SELECT Soql, SourceIp, Username, EventTime FROM ApiEvent
WHERE RecordInfo Like '%003D000000QYVZ5%'",
"targetObject": "QueryEvents__c",
"targetFieldMap": {"Soql":"QueryString__c","SourceIp":"IPAddress__c",
"Username":"User__c", "EventTime":"EventTime__c",
"UserAgent":"UserAgent__c"
}
}
Example Response
{
"jobId": "05PB000000001PQ",
12
Async SOQL Use Cases
"query": "SELECT Soql, SourceIp, Username, EventTime
FROM ApiEvent WHERE RecordInfo Like '%003D000000QYVZ5%'",
"status": "Complete",
"targetFieldMap": {"Soql":"QueryString__c","SourceIp":"IPAddress__c",
"Username":"User__c", "EventTime":"EventTime__c",
"UserAgent":"UserAgent__c"
},
"targetObject": "QueryEvents__c"
}
Beyond Cloud 