Batch class on External objects

Overview

As you know, we use lightning connect to integrate external databases with Salesforce. You can pull data from legacy systems such as SAP, Microsoft and Oracle in real time, without making a copy of the data in Salesforce. Salesforce Connect maps data tables in external systems to external objects in your org. Read more about Lightning Connect          

Sometime there is a requirement to copy the data in the External object to some custom object in your Org. As there is no way you can write trigger or Apex managed sharing on the External Object, so that you can move the same data to the custom object.

The only way is to write batch class on the external object, schedule it on daily basis to copy data to the custom objects. Previously, only Iterable<sObject> was supported. When you use Iterable<sObject> you cannot query more than 2000 records in iterable class and you get below error:

Error : First Error : Inline query has too many rows for direct assignment, Use FOR loop

To resolve above error you have to use SOQL in the FOR loop and populate the list.

After Winter 18, we can use Database.QueryLocator to access external objects from batch Apex. Using a QueryLocator object it bypasses the limit for the total number of records retrieved by SOQL queries.You must enable Request Row Counts on the external data source, and each response from the external system must include the total row count of the result set. Batch size specified in the scope parameter of Database.executeBatch. Default is 200 records.

Batch Class using both Iterable<sObject> & Database.QueryLocator:
global class ExternalobjectBatch implements Database.Batchable<sObject>{

    //constructor
    global ExternalobjectBatch(){
        System.debug('### ExternalobjectBatch()');
    }

    //Start method using Iterable<SObject>
    global Iterable<SObject> start(Database.BatchableContext BC){
       System.debug('### ExternalobjectBatch start()');
        return [select Id from DEV_Innovation__OrderDetails__x order by id];
   }

    /* Start method using Database.QueryLocator
    global Database.QueryLocator start(Database.BatchableContext BC){
        System.debug('### ExternalobjectBatch start()');
        return Database.getQueryLocator('select Id from DEV_Innovation__OrderDetails__x order by id');
    }
*/
    
    //Execute method
    global void execute(Database.BatchableContext BC, List<sObject> scope) {
        DEV_Innovation__OrderDetails__x product1 = (DEV_Innovation__OrderDetails__x)scope.get(0);
        System.debug('### ExternalobjectBatch length: ' + scope.size() + ' id: '+product1.id);
    }

    //Finish method
    global void finish(Database.BatchableContext BC){
        System.debug('### ExternalobjectBatch: finish() called');
    }

}

The above batch works on the Iterable<SObject>. To run it with query locator, comment the start method for Iterable<SObject> and un-comment the start method for Database.QueryLocator.


To Run the batch run "Database.executeBatch(new ExternalobjectBatch());" in anonymous window.

Hope this helps :)

References:

Release Notes
Lightning Connect
Batch Apex