• Big Objects
• SOQL with Big Objects
  • Example: Basic Query
  • Example: Returning Indexed Fields From a PS3 Account
  • Example: Using OpenQuery
• Replicating a Big Object

Big Objects

Big objects let you store and manage massive amounts of data on the Salesforce platform. Big objects have __b appended to their API name. DBAmp supports SOQL with big objects, and some DBAmp stored procedures support big objects as well. For more information, see https://developer.Salesforce.com/docs/atlas.en-us.bigobjects.meta/bigobjects/big_object.htm

NOTE: DBAmp support for big objects is read-only. DBAmp does not support inserting or updating.

SOQL with Big Objects

Example: Basic Query

Salesforce allows only indexed fields on the big object to be queried. For example, this query returns all the indexed fields in the Customer_Interaction__b big object:

SELECT * FROM SALESFORCE_LS.CData.Salesforce.Customer_interaction__b

Example: Returning Indexed Fields From a PS3 Account

This query returns all indexed fields where the game platform is ps3 and the Accountis as specified:

SELECT Game_Platform__c, Account__c, Play_date__c from
SALESFORCE...Customer_Interaction__b

WHERE Game_Platform__c = 'ps3' and Account__c = '0013000001L9BMSAA3'

NOTE: All three fields in the select clause are indexed fields on the Customer_Interaction__b object.

Example: Using OpenQuery

This query uses OPENQUERY to select all of the indexed fields in the Customer_Interaction__b big object:

SELECT * FROM OPENQUERY(SALESFORCE, 'SELECT * FROM Customer_Interaction__b')

Replicating a Big Object

Only the indexed fields of the big objects are stored in the local copy the SF_Replicate or SF_BulkSOQL procedure creates. DBAmp only supports these two procedures.

We recommend using the BulkAPI to create a local copy of a big object. This is because big objects contain millions to billions of records. The BulkAPI performs much better on big objects than does the SOAP API. To use the BulkAPI, choose either SF_Replicate with the bulkapi option or SF_BulkSOQL.

To run SF_Replicate with the bulkapi option to make a local copy, use the following command:

EXEC SF_Replicate '𝘓𝘪𝘯𝘬𝘦𝘥𝘚𝘦𝘳𝘷𝘦𝘳𝘕𝘢𝘮𝘦', '𝘊𝘶𝘴𝘵𝘰𝘮𝘦𝘳_𝘐𝘯𝘵𝘦𝘳𝘢𝘤𝘵𝘪𝘰𝘯__𝘣', 'bulkapi'

where LinkedServerName is the name you gave your linked server during installation and Customer_Interaction__b is the Salesforce.com big object to copy.

To run the SF_BulkSOQL stored procedure to make a local copy, use the command below. Note the use of the relationship field for the Accountobject, Account__r. For more information on using relationship fields, see the Salesforce documentation on big objects:

EXEC SF_BulkSOQL 'SALESFORCE', 'Customer_Interaction__b', '', 'SELECT *,
Account__r.𝘕𝘢𝘮𝘦 FROM Customer_Interaction__b'

NOTE: SF_Replicate using the SOAP API is supported but avoid using it because of large record counts in big objects.