Salesforce translators

You can use the Salesforce translator to run SELECT, DELETE, INSERT, UPSERT, and UPDATE operations against a Salesforce.com account.

salesforce

The translator, known by the type name salesforce, provides Salesforce API 34.0 support. The translator must be used with the corresponding Salesforce resource adapter of the same API version. Salesforce API version 22.0 support has been removed.

salesforce-34

The translator, known by the type name of salesforce-34, provides Salesforce API 34.0 support. The translator must be used with the corresponding Salesforce resource adapter of the same API version.

salesforce-41

The translator, known by the type name of salesforce-41, provides Salesforce API 41.0 support. Due to an earlier issue the 41 translator is actually based upon the 45 api, which is still compatible with version 41.0. The translator must be used with the corresponding Salesforce resource adapter of the same API version.

Note	The default URL for a Salesforce source may change from release to release. Especially if you are relying on metadata import it is recommended that the Salesforce URL is configured on the source. The URL will contain an explicit API version which means the imported metadata will remain consistent.

Table 1. Execution properties
Name	Description	Default
MaxBulkInsertBatchSize	Batch Size to use to insert bulk inserts. This will also be used for bulk updates and deletes if greater than 5000.	2048
UseBulk	Use bulk processing by default. If true bulk processing will be used when possible without the need for a hint.	false
UseHardDelete	Use Hard Delete. If true directs a bulk delete to be performed as a hard delete.	false
SupportsGroupBy	Enables `GROUP BY` Pushdown. Set to false to have Teiid process group by aggregations, such as those returning more than 2000 rows which error in SOQL.	true
MaxFromGroups	Set to a value greater than 2 to allow for pushdown of addition joins. Beyond the first join only additional joins (left outer or inner) to parents of the initial table in the join are allowed. Note that more than a single join is a new feature, which may not work in all circumstances.	2

The Salesforce translator can import metadata.

Table 2. Import properties
Property Name	Description	Required	Default
NormalizeNames	If the importer should attempt to modify the object/field names so that they can be used unquoted.	false	true
excludeTables	A case-insensitive regular expression that when matched against a table name will exclude it from import. Applied after table names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter.	false	n/a
includeTables	A case-insensitive regular expression that when matched against a table name will be included during import. Applied after table names are retrieved from source.	false	n/a
importStatstics	Retrieves cardinalities during import using the REST API explain plan feature.	false	false
ModelAuditFields	Add Audit Fields To Model. This includes CreatedXXX, LastModifiedXXX, and SystemModstamp fields.	false	false

NOTE： When both includeTables and excludeTables patterns are present during the import, the includeTables pattern matched first, then the excludePatterns will be applied.

Note	If you need connectivity to an API version other than what is built in, you may try to use an existing connectivity pair, but in some circumstances - especially accessing a later remote api from an older Java API - this is not possible and results in what appears to be hung connections.

Extension metadata properties

Salesforce is not relational database, however Teiid provides ways to map Saleforce data into relational constructs like Tables and Procedures. You can define a foreign table using DDL in Teiid VDB, which maps to Salesforce’s SObject. At runtime, to interpret this table back to a SObject, Teiid decorates or tags this table definition with additional metadata. For example, a table is defined as in the following example:

CREATE FOREIGN TABLE Pricebook2 (
	Id string,
	Name string,
	IsActive boolean,
	IsStandard boolean,
	Description string,
	IsDeleted boolean)
	OPTIONS (
	  UPDATABLE 'TRUE',
	  "teiid_sf:Supports Query" 'TRUE');

In the preceding example, the property in the OPTIONS clause with the property "teiid_sf:Supports Query" set to TRUE indicates that you can run SELECT commands against this table. The following table lists the metadata extension properties that can be used in a Salesforce schema.

Property Name Description Required Default Applies To

Property Name	Description	Required	Default	Applies To
Supports Query	You can run `SELECT` commands against the table.	false	true	Table
Supports Retrieve	You can retrieve the results of `SELECT` commands run against the table.	false	true	Table

Supports Query

You can run SELECT commands against the table.

false

true

Table

Supports Retrieve

You can retrieve the results of SELECT commands run against the table.

false

true

Table

SQL processing

Salesforce does not provide the same set of functionality as a relational database. For example, Salesforce does not support arbitrary joins between tables. However, working in combination with the Teiid Query Planner, the Salesforce connector can use nearly all of the SQL syntax capabilities in Teiid. The Salesforce Connector executes SQL commands by "pushing down" the command to Salesforce whenever possible, depending on the available capabilities. Teiid will automatically provide additional database functionality when the Salesforce Connector does not explicitly enable use of a given SQL construct. In cases where certain SQL capabilities cannot be pushed down to Salesforce, Teiid will push down the capabilities that it can, and fetch a set of data from Salesforce. Then, Teiid will evaluate the additional capabilities, creating a subset of the original data set. Finally, Teiid will pass the result to the client.

If you issue queries with a GROUP BY clause, and you receive a Salesforce error that indicates that queryMore is not supported, you can either add limits, or set the execution property SupportsGroupBy to false.

SELECT array_agg(Reports) FROM Supervisor where Division = 'customer support';

Neither Salesforce, nor the Salesforce Connector support the array_agg() scalar. however, both are compatible with the CompareCriteriaEquals query, so the connector transforms the query that it receives into this query to Salesforce.

SELECT Reports FROM Supervisor where Division = 'customer support';

The array_agg() function will be applied by the Teiid Query Engine to the result set returned by the connector.

In some cases, multiple calls to the Salesforce application will be made to process the SQL that is passed to the connector.

DELETE From Case WHERE Status = 'Closed';

The API in Salesforce to delete objects can delete by object ID only. In order to accomplish this, the Salesforce connector will first execute a query to get the IDs of the correct objects, and then delete those objects. So the above DELETE command will result in the following two commands.

SELECT ID From Case WHERE Status = 'Closed';
DELETE From Case where ID IN (<result of query>);

NOTE： The Salesforce API DELETE call is not expressed in SQL, but the above is an equivalent SQL expression.

It’s useful to be aware of incompatible capabilities, in order to avoid fetching large data sets from Salesforce and making you queries as performant as possible. For information about the SQL constructs that you can push down to Salesforce, see Compatible SQL capabilities.

Selecting from multi-select picklists

A multi-select picklist is a field type in Salesforce that can contain multiple values in a single field. Query criteria operators for fields of this type in SOQL are limited to EQ, NE, includes and excludes. For the Salesforce documentation about how to select from multi-select picklists, see Querying Multi-select Picklists

Teiid SQL does not support the includes or excludes operators, but the Salesforce connector provides user-defined function definitions for these operators that provide equivalent functionality for fields of type multi-select. The definition for the functions is:

boolean includes(Column column, String param)
boolean excludes(Column column, String param)

For example, take a single multi-select picklist column called Status that contains all of these values.

current
working
critical

For that column, all of the below are valid queries:

SELECT * FROM Issue WHERE true = includes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = excludes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = includes (Status, 'current;working, critical' );

EQ and NE criteria will pass to Salesforce as supplied. For example, these queries will not be modified by the connector.

SELECT * FROM Issue WHERE Status = 'current';
SELECT * FROM Issue WHERE Status = 'current;critical';
SELECT * FROM Issue WHERE Status != 'current;working';

Selecting all objects

You can use the Salesforce connector to call the queryAll operation from the Salesforce API. The queryAll operation is equivalent to the query operation with the exception that it returns data about all current and deletedobjects in the system.

The connector determines if it will call the query or queryAll operation via reference to the isDeleted property present on each Salesforce object, and modeled as a column on each table generated by the importer. By default this value is set to false when the model is generated and thus the connector calls query. Users are free to change the value in the model to true, changing the default behavior of the connector to be queryAll.

The behavior is different if isDeleted is used as a parameter in the query. If the isDeleted column is used as a parameter in the query, and the value is true, then the connector calls queryAll.

select * from Contact where isDeleted = true;

If the isDeleted column is used as a parameter in the query, and the value is false, then the connector that performs the default behavior will call the query.

select * from Contact where isDeleted = false;

Selecting updated objects

If the option is selected when importing metadata from Salesforce, a GetUpdated procedure is generated in the model with the following structure:

GetUpdated (ObjectName IN string,
    StartDate IN datetime,
    EndDate IN datetime,
    LatestDateCovered OUT datetime)
returns
    ID string

See the description of the GetUpdated operation in the Salesforce documentation for usage details.

Selecting deleted objects

If the option is selected when importing metadata from Salesforce, a GetDeleted procedure is generated in the model with the following structure:

GetDeleted (ObjectName IN string,
    StartDate IN datetime,
    EndDate IN datetime,
    EarliestDateAvailable OUT datetime,
    LatestDateCovered OUT datetime)
returns
    ID string,
    DeletedDate datetime

See the description of the GetDeleted operation in the Salesforce documentation for usage details.

Relationship queries

Unlike a relational database, Salesforce does not support join operations, but it does have support for queries that include parent-to-child or child-to-parent relationships between objects. These are termed Relationship Queries. You can run Relationship Queries in the SalesForce connector through Outer Join syntax. The translator will compensate if an inner join is used.

SELECT Account.name, Contact.Name from Contact LEFT OUTER JOIN Account
on Contact.Accountid = Account.id

This query shows the correct syntax to query a SalesForce model with to produce a relationship query from child to parent. It resolves to the following query to SalesForce.

SELECT Contact.Account.Name, Contact.Name FROM Contact

select Contact.Name, Account.Name from Account Left outer Join Contact
on Contact.Accountid = Account.id

This query shows the correct syntax to query a SalesForce model with to produce a relationship query from parent to child. It resolves to the following query to SalesForce.

SELECT Account.Name, (SELECT Contact.Name FROM
Account.Contacts) FROM Account

See the description of the Relationship Queries operation in the SalesForce documentation for limitations.

Bulk insert queries

You can also use bulk insert statements in the SalesForce translator by using JDBC batch semantics or SELECT INTO semantics. The batch size is determined by the execution property MaxBulkInsertBatchSize, which can be overridden in the vdb file. The default value of the batch is 2048. The bulk insert feature uses the async REST based API exposed by Salesforce for execution for better performance.

Bulk selects

When querying tables with more than 10,000,000 records, or if experiencing timeouts with just result batching, Teiid can issue queries to Salesforce using the bulk API. When using a bulk select, primary key (PK) chunking is enabled if it is compatible with the query.

The use of the bulk api requires a source hint in the query if UseBulk is not specified:

SELECT /*+ sh salesforce:'bulk' */ Name ... FROM Account

Where salesforce is the source name of the target source.

If primary key chunking can be used the default chunk size is 100,000 records.

A bulk hint may be supplied with SELECT, UPDATE, or DELETE - bulk processing will be used automatically with INSERTS when possible.

Note	This feature is only supported in the Salesforce API version 28 or higher.

If UseHardDelete is not specified a delete statement may also use a hint to specify hard delete:

DELETE /*+ sh salesforce:'bulk hardDelete' */ FROM Account WHERE Name LIKE 'S%'

Compatible SQL capabilities

You can use the following SQL capabilities with the Salesforce Connector. These SQL constructs will be pushed down to Salesforce.

SELECT command
INSERT Command
UPDATE Command
DELETE Command
NotCriteria
OrCriteria
CompareCriteriaEquals
CompareCriteriaOrdered
IsNullCritiera
InCriteria
LikeCriteria - Can be used for String fields only.
RowLimit
Basic Aggregates
OuterJoins with join criteria KEY

OrderBy as implemented by Salesforce is not fully supported by Teiid. You may optionally enable support on the translator, but you may receive an exception or unexpected results vs when ordering is performed in Teiid.

Native Queries

Salesforce procedures may optionally have native queries associated with them. For more information, see Parameterizable native queries in Translators. The operation prefix (select;, insert;, update;, delete; - see below for more) must be present in the native-query, but it will not be issued as part of the query to the source.

Example DDL for a Salesforce native procedure

CREATE FOREIGN PROCEDURE proc (arg1 integer, arg2 string) OPTIONS ("teiid_rel:native-query" 'search;SELECT ... complex SOQL ... WHERE col1 = $1 and col2 = $2')
returns (col1 string, col2 string, col3 timestamp);

Direct query procedure

This feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable direct query procedures, set the execution property called SupportsDirectQueryProcedure to true. For more information, see Override the execution properties in as_translators.adoc.

Tip	By default the name of the procedure that executes the queries directly is called native. For information about how to change the default name, see Override the execution properties in as_translators.adoc.

The Salesforce translator provides a procedure to execute any ad-hoc SOQL query directly against the source without Teiid parsing or resolving. Since the metadata of this procedure’s results are not known to Teiid, they are returned as an object array. ARRAYTABLE can be used construct tabular output for consumption by client applications. Teiid exposes this procedure with a simple query structure as follows:

Select example

SELECT x.* FROM (call sf_source.native('search;SELECT Account.Id, Account.Type, Account.Name FROM Account')) w,
 ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS x

from the above code, the "search" keyword followed by a query statement.

Note

The SOQL is treated as a parameterized native query so that parameter values may be inserted in the query string properly. For more information, see Parameterizable native queries in Translators. The results returned by search may contain the object Id as the first column value regardless of whether it was selected. Also queries that select columns from multiple object types will not be correct.

Delete Example

SELECT x.* FROM (call sf_source.native('delete;', 'id1', 'id2')) w,
 ARRAYTABLE(w.tuple COLUMNS "updatecount" integer) AS x

form the above code, the "delete;" keyword followed by the ids to delete as varargs.

Create example

SELECT x.* FROM
 (call sf_source.native('create;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

form the above code, the "create" or "update" keyword must be followed by the following properties. Attributes must be matched positionally by the procedure variables - thus in the example attribute two will be set to 2.

Property Name	Description	Required
type	Table Name	Yes
attributes	comma separated list of names of the columns	no

Property Name

Description

Required

type

Table Name

Yes

attributes

comma separated list of names of the columns

The values for each attribute is specified as separate argument to the "native" procedure.

Update is similar to create, with one more extra property called "id", which defines identifier for the record.

Update example

SELECT x.* FROM
 (call sf_source.native('update;id=pk;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

Tip	By default the name of the procedure that executes the queries directly is called native, however you can add set an override execution property in the DDL file to change it.

JCA resource adapter

The resource adapter for this translator is provided through Salesforce data sources. For configuration information, see Salesforce Data Sources in the Administrator’s Guide.

Salesforce translators

Salesforce translators

results matching ""

No results matching ""