Defining and Inserting Data Into Big Objects

Introduction

Big objects allow us to store and manage large amounts of data in Salesforce. We can extract data from other objects or bring data from external systems and archive them into big objects.

Big objects can store data up to millions or billions of records.

We can create up to 100 big objects in an org.

Big objects are supported in Unlimited, Enterprise, Performance, and Developer editions up to 1 million records. Additional records capacity can be availed as an add-on license.

Defining Big Objects

We can define big objects either using the Metadata API or in the Setup.

To create a new Big Object, go to Setup and search for Big Object in the quick find box. Then click on New and give the name for the big object.

All big objects are suffixed by “__b” at the end of the API Name. Lets consider we have a big object called Orders, the API name will Orders__b.

We can create custom fields of the following types in a big object.

• Lookup Relationship
• Date/ Time
• Email
• Number
• Phone
• Text
• Text Area (Long)
• URL

Before we can use the big object, we have to create an Index that will act as a composite primary key. This index will be used for querying and filtering the big object data.

Considerations for creating an Index for a big object

• Only the required custom fields can be included while creating an index.
• Up to a maximum of 5 fields can be included in the index.
• The total character length across all fields included in an Index cannot exceed 100.
• URL and Long Text Area fields cannot be included in the Index.
• Once we create an Index, we cannot modify or delete it. If we need to make any modifications we have to delete the big object and create it again.
• We have to specify the index position in the order in which we want to use the filters in the query. For example, we have to specify the Index position as 1 for the most commonly used filter in the query.

To create an Index, click on New on the Index section and select the custom fields that you want to include in the Index and specify the Index Position and Index Direction and click on Save.

The option to create a new index is only available when you have marked at least one custom field as Required.

Now that we have defined the Index, we can insert data into the Big Object.

Inserting data into Big Object through Apex

We can insert records into the big object using the Database.insertImmediate() method.

Inserting a second record with the same index but different data will be considered as an upsert operation and the data will be overwritten with the new data for that index.

Consider the below example in which we have a big object called Orders__b and an Index which includes the fields Order_Number__c, Status__c, Type__c.

The below code will create a new record in the Orders__b big object with the specified values.

Orders__b order = new Orders__b();
order.Order_Number__c='12345678';
order.Status__c = 'Draft';
order.Type__c = 'Upgrade';
order.Quantity__c = 5;
order.Order_Amount__c = 20000;
database.insertImmediate(order);

Let’s consider we create another record with the same index value but different data.

Orders__b order = new Orders__b();
order.Order_Number__c='12345678';
order.Status__c = 'Draft';
order.Type__c = 'Upgrade';
order.Quantity__c = 10;
order.Order_Amount__c = 35000;
database.insertImmediate(order);

The above code will not insert a new record but it will update the existing data with the new data as the key already exists.

Querying Big Objects

We can query the data in the big objects by building an index query starting from the first field defined in the index.

There must not be any gaps between the first and last field in the query as explained below.

In the Orders__b Big Object, we have Order_Number__c in the 1st position and Status__c and Type__c in the 2nd and 3rd position of the index respectively.

The below query will return the correct data as we have specified all three fields of the index.

SELECT Quantity__c, Order_Number__c, Status__c, Type__c, Order_Start_Date__c, Order_Amount__c FROM Orders__b WHERE Order_Number__c='12345678' AND Status__c='Draft' AND Type__c='Upgrade'

Whereas the below query will throw an error as it has a gap between the first and the last index.

SELECT Quantity__c, Order_Number__c, Status__c, Type__c, Order_Start_Date__c, Order_Amount__c FROM Orders__b WHERE Order_Number__c='12345678' AND Type__c='Upgrade'

Considerations While Querying Big Objects

• We cannot include the !=, LIKE, NOT IN, EXCLUDES, and INCLUDES operators in the query.
• Aggregate functions are not allowed.
• To retrieve a list of records, we must not include the Id field in the query. Including the Id field in the query will return results that have an empty Id.
• We can use =, <, >, <=, or >=, or IN operators on the last field in your query. Any prior fields in your query can use only the = operator.