Red Hat Summit이 콘텐츠는 선택한 언어로 제공되지 않습니다.
13.25. Salesforce Translator
13.25.1. Salesforce Translator
링크 복사링크가 클립보드에 복사되었습니다!
The Salesforce translator supports the SELECT, DELETE, INSERT and UPDATE operations against a Salesforce.com account.
The Salesforce translator is implemented by the
org.teiid.translator.salesforce.SalesForceExecutionFactory class and known by the translator type name salesforce.
Note
The resource adapter for this translator is provided by configuring the
salesforce data source in the JBoss EAP instance. See the Red Hat JBoss Data Virtualization Administration and Configuration Guide for more configuration information.
13.25.2. Salesforce Translator: Execution Properties
링크 복사링크가 클립보드에 복사되었습니다!
| Name | Description | Default |
|---|---|---|
| ModelAuditFields | Audit Model Fields | false |
| MaxBulkInsertBatchSize | Batch size to use when inserting in bulk | 2048 |
| 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 |
The Salesforce translator can import metadata.
| 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 |
When both includeTables and excludeTables patterns are present during the import, the includeTables pattern matched first, then the excludePatterns will be applied.
13.25.3. Salesforce Translator: 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 JBoss Data Virtualization Query Planner, the Salesforce connector supports nearly all of the SQL syntax supported by JBoss Data Virtualization.
The Salesforce Connector executes SQL commands by pushing down the command to Salesforce whenever possible, based on the supported capabilities. JBoss Data Virtualization will automatically provide additional database functionality when the Salesforce Connector does not explicitly provide support for a given SQL construct. In these cases, the SQL construct cannot be pushed downto the data source, so it will be evaluated in JBoss Data Virtualization, in order to ensure that the operation is performed.
In cases where certain SQL capabilities cannot be pushed down to Salesforce, JBoss Data Virtualization will push down the capabilities that are supported, and fetch a set of data from Salesforce. Then, JBoss Data Virtualization will evaluate the additional capabilities, creating a subset of the original data set. Finally, JBoss Data Virtualization will pass the result to the client.
SELECT sum(Reports) FROM Supervisor where Division = 'customer support';
SELECT sum(Reports) FROM Supervisor where Division = 'customer support';
Neither Salesforce nor the Salesforce Connector support the sum() scalar function, but they do support CompareCriteriaEquals, so the query that is passed to Salesforce by the connector will be transformed to this query.
SELECT Reports FROM Supervisor where Division = 'customer support';
SELECT Reports FROM Supervisor where Division = 'customer support';
The sum() scalar function will be applied by the JBoss Data Virtualization Query Engine to the result set returned by the connector.
In some cases multiple calls to the Salesforce application will be made to support the SQL passed to the connector.
DELETE From Case WHERE Status = 'Closed';
DELETE From Case WHERE Status = 'Closed';
The API in Salesforce to delete objects only supports deleting by ID. 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>);
SELECT ID From Case WHERE Status = 'Closed';
DELETE From Case where ID IN (<result of query>);
The Salesforce API DELETE call is not expressed in SQL, but the above is an SQL equivalent expression.
It is useful to be aware of unsupported capabilities, in order to avoid fetching large data sets from Salesforce and making your queries perform as well as possible.
13.25.4. Salesforce Translator: Multi-Select Picklists
링크 복사링크가 클립보드에 복사되었습니다!
A multi-select pick list is a field type in Salesforce that can contain multiple values in a single field. Query criteria operators for fields of this type in Salesforce Object Query Language (SOQL) are limited to EQ, NE, includes and excludes. The full Salesforce documentation for selecting from mullti-select pick lists can be found at Querying Mulit-select Picklists.
JBoss Data Virtualization 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 are:
boolean includes(Column column, String param) boolean excludes(Column column, String param)
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' );
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';
SELECT * FROM Issue WHERE Status = 'current';
SELECT * FROM Issue WHERE Status = 'current;critical';
SELECT * FROM Issue WHERE Status != 'current;working';13.25.5. Salesforce Translator: Selecting All Objects
링크 복사링크가 클립보드에 복사되었습니다!
The Salesforce connector supports calling 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 deleted objects 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' the connector will call queryAll.
select * from Contact where isDeleted = true;
select * from Contact where isDeleted = true;
If the isDeleted column is used as a parameter in the query, and the value is 'false' the connector performing the default behavior will call the query.
select * from Contact where isDeleted = false;
select * from Contact where isDeleted = false;13.25.6. Salesforce Translator: 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:
See the description of the GetUpdated operation in the Salesforce documentation for usage details.
13.25.7. Salesforce Translator: 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:
See the description of the GetDeleted operation in the Salesforce documentation for usage details.
13.25.8. Salesforce Translator: Relationship Queries
링크 복사링크가 클립보드에 복사되었습니다!
Salesforce does not support joins like a relational database, but it does have support for queries that include parent-to-child or child-to-parent relationships between objects. These are termed Relationship Queries. The SalesForce connector supports Relationship Queries through Outer Join syntax.
SELECT Account.name, Contact.Name from Contact LEFT OUTER JOIN Account on Contact.Accountid = Account.id
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.Account.Name, Contact.Name FROM Contactselect Contact.Name, Account.Name from Account Left outer Join Contact on Contact.Accountid = Account.id
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
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.
13.25.9. Salesforce Translator: Bulk Insert Queries
링크 복사링크가 클립보드에 복사되었습니다!
SalesForce translator also supports bulk insert statements 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.xml 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.
13.25.10. Salesforce Translator: Supported Capabilities
링크 복사링크가 클립보드에 복사되었습니다!
The following are the connector capabilities supported by the Salesforce Connector. These SQL constructs will be pushed down to Salesforce.
- SELECT command
- INSERT Command
- UPDATE Command
- DELETE Command
- CompareCriteriaEquals
- InCriteria
- LikeCriteria - Supported for String fields only.
- RowLimit
- AggregatesCountStar
- NotCriteria
- OrCriteria
- CompareCriteriaOrdered
- OuterJoins with join criteria KEY
13.25.11. Salesforce Translator: Native Queries
링크 복사링크가 클립보드에 복사되었습니다!
Salesforce procedures may optionally have native queries associated with them. See Section 13.7, “Parameterizable Native Queries”. The operation prefix (for example, select;, insert;, update;, delete; - see the native procedure logic below) must be present in the native query, but it will not be issued as part of the query to the source.
Example 13.11. Example DDL for a SF 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);
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);
13.25.12. Salesforce Translator: Native Procedure
링크 복사링크가 클립보드에 복사되었습니다!
Warning
This feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable this feature, override the translator property called "SupportsNativeQueries" to true. See Section 13.6, “Override Execution Properties”.
SalesForce translator provides a procedure with name native that gives ability to execute any ad hoc native Salesforce queries directly against the source without any JBoss Data Virtualization parsing or resolving. The metadata of this procedure's execution results are not known to JBoss Data Virtualization, and they are returned as object array. User can use an ARRAYTABLE construct ( Section 3.6.9, “Nested Tables: ARRAYTABLE”) to build a tabular output for consumption by client applications. JBoss Data Virtualization exposes this procedure with a simple query structure as below.
13.25.13. Salesforce Translator Example: Select
링크 복사링크가 클립보드에 복사되었습니다!
Example 13.12. Select Example
SELECT x.* FROM (call pm1.native('search;SELECT Account.Id, Account.Type, Account.Name FROM Account')) w,
ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS x
SELECT x.* FROM (call pm1.native('search;SELECT Account.Id, Account.Type, Account.Name FROM Account')) w,
ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS x
In the above code, the "search" keyword is followed by a query statement.
Note
The Salesforce Object Query Language (SOQL) is treated as a parameterized native query so that parameter values may be inserted in the query string properly. See Section 13.7, “Parameterizable Native Queries”.
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.
13.25.14. Salesforce Translator Example: Delete
링크 복사링크가 클립보드에 복사되었습니다!
Example 13.13. Delete Example
SELECT x.* FROM (call pm1.native('delete;', 'id1', 'id2')) w,
ARRAYTABLE(w.tuple COLUMNS "updatecount" integer) AS x
SELECT x.* FROM (call pm1.native('delete;', 'id1', 'id2')) w,
ARRAYTABLE(w.tuple COLUMNS "updatecount" integer) AS x
In the above code, the "delete;" keyword is followed by the ids to delete as varargs.
13.25.15. Salesforce Translator Example: Create and Update
링크 복사링크가 클립보드에 복사되었습니다!
Example 13.14. Create Example
SELECT x.* FROM
(call pm1.native('create;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM
(call pm1.native('create;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
In 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
| |
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.
Example 13.15. Update Example
SELECT x.* FROM
(call pm1.native('update;id=pk;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM
(call pm1.native('update;id=pk;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
Important
By default the name of the procedure that executes the queries directly is called native, however user can set override execution property
vdb.xml file to change it.
'%20d='M201.5%20305.5c-13.8%200-24.9-11.1-24.9-24.6%200-13.8%2011.1-24.9%2024.9-24.9%2013.6%200%2024.6%2011.1%2024.6%2024.9%200%2013.6-11.1%2024.6-24.6%2024.6zM504%20256c0%20137-111%20248-248%20248S8%20393%208%20256%20119%208%20256%208s248%20111%20248%20248zm-132.3-41.2c-9.4%200-17.7%203.9-23.8%2010-22.4-15.5-52.6-25.5-86.1-26.6l17.4-78.3%2055.4%2012.5c0%2013.6%2011.1%2024.6%2024.6%2024.6%2013.8%200%2024.9-11.3%2024.9-24.9s-11.1-24.9-24.9-24.9c-9.7%200-18%205.8-22.1%2013.8l-61.2-13.6c-3-.8-6.1%201.4-6.9%204.4l-19.1%2086.4c-33.2%201.4-63.1%2011.3-85.5%2026.8-6.1-6.4-14.7-10.2-24.1-10.2-34.9%200-46.3%2046.9-14.4%2062.8-1.1%205-1.7%2010.2-1.7%2015.5%200%2052.6%2059.2%2095.2%20132%2095.2%2073.1%200%20132.3-42.6%20132.3-95.2%200-5.3-.6-10.8-1.9-15.8%2031.3-16%2019.8-62.5-14.9-62.5zM302.8%20331c-18.2%2018.2-76.1%2017.9-93.6%200-2.2-2.2-6.1-2.2-8.3%200-2.5%202.5-2.5%206.4%200%208.6%2022.8%2022.8%2087.3%2022.8%20110.2%200%202.5-2.2%202.5-6.1%200-8.6-2.2-2.2-6.1-2.2-8.3%200zm7.7-75c-13.6%200-24.6%2011.1-24.6%2024.9%200%2013.6%2011.1%2024.6%2024.6%2024.6%2013.8%200%2024.9-11.1%2024.9-24.6%200-13.8-11-24.9-24.9-24.9z'/%3e%3c/svg%3e){kind=small}