Introduction

When objects have lots of (e.g. 800) attributes, the SOQL for these objects can hit URL length limits. This page documents an approach to handle this limit, by splitting attributes into multiple batches, without the knowledge of users

Use case(s)

I have a Salesforce object with over 800 attributes. When I send a SOQL query for this object with all attributes in it to the Salesforce APIs, I hit URL length limits. To get around this situation, I have wrapped my code for querying Salesforce into a script that retrieves data for an object in batches, and then joins the batches together. This process is extremely manual, custom, one-off and error prone, and therefore extremely hard to manage and maintain. I would like the ability to for the Salesforce plugins in CDAP to batch the attributes before submitting the SOQL to Salesforce, and handle this grunt work automatically.

User Storie(s)

Plugin Type

This is not a new plugin, but enhancements to existing plugins.

Configurables

This section defines properties that are configurable for this plugin. 

Design / Implementation Tips

• Tip #1
• Tip #2

Design

Approach(s)

1. In CDAP Salesforce Source Batch plugin user specifies sObject (ex: Opportunity). Using Salesforce describe functionality, we receive all fields which size exceeds and generate SOQL 
   1. length of the generated SOQL does not exceed 20,000 [1] characters then we will use standard approach based on Bulk API. Details: Salesforce Batch Source.
   2. length of the generated SOQL exceeds 20,000 characters [1]
   . In this case
   1. then we will use 2 APIs (SOAP and Bulk) to solve length problem:
   • • Fetch all IDs
   for
   • • [2] for provided sObject using Bulk API (generate: select id from sObject).
     • Create chunks by 2,000 ids and query all fields using SOAP API (2,000 is a limit of records exposed by SOAP API [
   2
   • • 3]).
     • Make retrieve() request for each chunk.
     • Save to sink or expose to further transformations in CDAP.
2. In CDAP Salesforce Source Batch plugin user specifies SOQL (ex: select  id, type … from Opportunity where type = ‘ABC’) which length
   
   1. length of the SOQL does not exceed 20,000 characters then we will use standard approach based on Bulk API. Details: Salesforce Batch Source.
   2. length of the SOQL exceeds 20,000 characters
   . In this case
   1. then we
   also
   1. will use 2 APIs (SOAP and Bulk) to solve length problem:
   • • Parse given SOQL query and extract all fields in SELECT clause and replace them with id field (ex: select  id, type … from Opportunity where type = ‘ABC' -> select id from Opportunity where type = ‘ABC').
     • Fetch all IDs

1. • • [2] produced by generated SOQL query (ex: select id from Opportunity where type = ‘ABC’).
     • Create chunks by 2,000 ids and query fields extracted from initial SOQL query using SOAP APl.
     • Make retrieve() request for each chunk.
     • Save to sink or expose to further transformations in CDAP.

[1]   https://developer.salesforce.com/docs/atlas.en-us.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_soslsoql.htm
[2]   https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/system_fields.htm
[3] https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_calls_retrieve.htm

Properties

Security

Limitation(s)

To add wide sObject query support SOAP API will be used but it has some limitations described below.

• Single SOAP API retrieve() call can contain a maximum of 2000 records [1] while Bulk API batch can contain a maximum of 10,000 records. [2]

[1] https://developer.salesforce.com/docs/atlas.en-us.218.0.api.meta/api/sforce_api_calls_retrieve.htm
[2] https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/asynch_api_concepts_limits.htm

Future Work

• Some future work – HYDRATOR-99999
• Another future work – HYDRATOR-99999

Test Case(s)

• Test case #1
• Test case #2

Sample Pipeline

Please attach one or more sample pipeline(s) and associated data. 

Pipeline #1

Pipeline #2