FarragoMedSalesforcePlugin
Contents |
Introduction
Eigenbase includes a Salesforce.com (SFDC) web service foreign data wrapper plugin implementing SQL/MED (Management of External Data) access to SFDC data. The SFDC Web Services API allows for access to an organization's information, in the form of objects. From the Eigenbase perspective, each SFDC object is a single table. All SFDC objects can be accessed via a foreign schema.
Objects are determined by the organization (adding custom objects) as well as the logged in user's access permissions (subtracting non-visible objects); therefore, the set of all objects is not necessarily the same across or within organizations.
Usage
The SFDC wrapper is accessed via the standard SQL/MED interface.
- First, a data wrapper is defined. A data wrapper represents a program module for accessing external data.
- Second, a foreign server is defined. In this case, a server describes a particular user login to SFDC, and it corresponds to a single schema.
- Finally, objects may be accessed in one of two ways:
- One can create a foreign table, corresponding to the metadata for a single object
- One can import the metadata for the entire foreign schema (or selected subsets), based on the objects available to that user
Typical LucidDB use cases include:
- Extracting objects in bulk from Salesforce and loading them into column store tables
- Selectively querying individual object definitions directly from Salesforce via Eigenbase SQL
Packaging
The SFDC connector is not redistributed in binary form since the build depends on proprietary WSDL from Salesforce. Currently, it is necessary to build it yourself unless you are using some commercial distribution of Eigenbase.
Here are the steps for building it:
- Follow the Farrago build instructions, but including //open/dev/extensions in your Perforce client view
- If you don't already have one, get a Salesforce developer account
- Visit login.salesforce.com
- Click Setup
- Click Develop->API under App Setup
- Generate partner wsdl, saving it to extensions/conn/sfdc/partner.wsdl
- Repeat for enterprise.wsdl
- Click Reset Security Token and get the new token from the notification email it will send you
- In extensions/conn/sfdc, copy build.properties.tmpl to build.properties
- In build.properties,
- edit username and username_test to match your Salesforce developer account name
- edit password and password_test to match your Salesforce developer account passwords, including the security token suffixes
- From extensions/conn/sfdc:
- ant clean
- ant compile
- ant dist
- ant test
(Note that the Salesforce website content changes periodically, but you should be able to figure out the corresponding steps above on the latest site.)
In order to be able to run queries from sqlline, perform the following additional steps:
- ant jarSfdcPluginWithDeps
- cd plugin
- rm MedSfdc.jar
- mv MedSfdcComplete.jar MedSfdc.jar
SFDC Foreign Data Wrapper Definition
Create Foreign Data Wrapper Syntax
The Salesforce foreign data wrapper (named SALESFORCE) is created by the build steps above, so you don't need to do it yourself.
SFDC Foreign Server
The following options may be specifed at the server definition:
| Option | Default | Description |
|---|---|---|
| USER_NAME | username to use to login to SFDC | |
| PASSWORD | password to use to login to SFDC | |
| VARCHAR_FIELD_EXTRA_PRECISION* | 128 | extra precision to add to all SFDC object VARCHAR fields |
* Note: SFDC Wrapper truncates all VARCHAR field data to precision 256. The VARCHAR_FIELD_EXTRA_PRECISION option can be used to increase the VARCHAR precision for all fields.
Create Foreign Server Syntax
CREATE SERVER foreign_server_name FOREIGN DATA WRAPPER SALESFORCE OPTIONS ( USER_NAME 'username', PASSWORD 'passwd', VARCHAR_FIELD_EXTRA_PRECISION 'precision')
Example:
CREATE SERVER sfdc_server FOREIGN DATA WRAPPER SALESFORCE OPTIONS ( USER_NAME 'joe@mac.com', PASSWORD 'sekritjcD8RmGCoXDk6gjmA8Zt3EgX' ); SELECT COUNT(*) FROM sfdc_server.sfdc."Account";
SFDC Foreign Table
You can define SFDC foreign tables that pertain to a specific SFDC foreign server. A foreign table is a schema object, meaning you must create it under a schema. The required table option is OBJECT, which is the name of the object in SFDC (accessible to the user whose credentials were supplied to CREATE SERVER).
Create Foreign Table Syntax
CREATE FOREIGN TABLE table_name SERVER foreign_server_name OPTIONS ( OBJECT 'object_name')
Example:
CREATE SCHEMA SFDC_OBJECTS; CREATE FOREIGN TABLE sfdc_objects.accounts( "Id" varchar(17), "Name" varchar(25), "Type" varchar(25), "ParentId" varchar(25), "BillingStreet" varchar(100), "Sic" varchar(50) ) SERVER sfdc_server OPTIONS ( object 'Account' ); SELECT COUNT(*) FROM sfdc_objects.accounts;
SFDC Foreign Schema
The metadata from an SFDC foreign server can be imported into a local schema in the Eigenbase catalog, using the fixed foreign schema name 'SFDC'.
Import Foreign Schema Syntax
IMPORT FOREIGN SCHEMA SFDC
[import_qualification]
FROM SERVER foreign_server_name
INTO local_schema_name
Where:
import_qualification ::=
LIMIT TO | EXCEPT
(table_name_list) | TABLE_NAME LIKE expression
table_name_list ::= table_name [ { comma table_name } ...]
Example:
CREATE SCHEMA imported_sfdc_objects;
IMPORT FOREIGN SCHEMA SFDC LIMIT TO ("Account", "Opportunity")
FROM SERVER sfdc_server
INTO imported_sfdc_objects;
SELECT COUNT(*) FROM imported_sfdc_objects."Account";
Query Optimization
The plugin is capable of pushing down both projections and filters:
0: jdbc:farrago:> explain plan for select "Id", "Name" from sfdc_server.sfdc."Account" where "Type" = 'Customer - Channel';
+--------------------------------------------------------------------------------------------------------------------------------------
| column0 |
+--------------------------------------------------------------------------------------------------------------------------------------
| FennelToIteratorConverter |
| FennelReshapeRel(projection=[[0, 1]], ... |
| IteratorToFennelConverter |
| FarragoJavaUdxRel(invocation=[CAST(SFDC_QUERY(CAST('select Id, Name, Type from Account where Type = ''Customer - Channel'''):...
+---------------------------------------------------------------------------------------------------------------------------------------
You can see the actual SOQL issued in the invocation attribute of the FarragoJavaUdxRel node in the plan.
Change Data Capture
Deleted Objects
Syntax to get the deleted records of an SFDC object:
SELECT * FROM objectname_deleted where "DeleteStamp" between TIMESTAMP'yyyy-mm-dd HH:MM:SS' and TIMESTAMP'yyyy-mm-dd HH:MM:SS' Where: the starting TIMESTAMP must be within the last thirty (30) days.
The output table will have two columns, "Id" and "DeleteStamp", where "Id" is the ID of the record that was deleted between the two specified timestamps, and "DeleteStamp" is the timestamp of when the record was deleted.
Updated Objects
Most objects have a "SystemModstamp" field, so a user can filter on this to get the changed data.
