SugarCRM

API and OAuth Information

Connect via SQL Editor

Query SugarCRM

API and OAuth2 Information

You are able to remotely connect to your SugarCRM platform, by connecting through its API.

SugarCRM REST API Details

Sugar uses a two-legged OAuth2 for authentication. You simply need to do a POST to /rest/<version>/oauth2/token with the following parameters:

grant_type	String	Type of request. Available grant types are "password" and "refresh_token".
client_id	String	The client_id of "sugar" will automatically create an OAuth Key in the system and can be used for "password" authentication. The client_id of "support_portal" will create an OAuth Key if the portal system is enabled and will allow for portal authentication. Other client_id's can be created by the administrator in the OAuthKeys section in the Administration section and can be used in the future for additional grant types, if the client secret is filled in, it will be checked to validate the use of the client id.
client_secret	String	The client's secret key.
username	String	The username of the user authenticating to the system.
password	String	The plaintext password the user authenticating to the system.
platform	String	Defaults to "base" allows you to have custom meta-data per platform. If using a value other than "base", you should make sure it is registered using the Platform extension or configure an API platform in Administration panel.

Connect via SQL Editor

In Zetaris, you can structure this POST request in the following way, through the Schema Store (SQL Editor):

Step 1: Create the authorisation post request, and save it, so it can be referenced.

UPSERT AUTH sugar_crm REQUEST(

endpoint "https://{site_url}/rest/v10/oauth2/token",

method "post",

http_encoding "JSON"

) HEADER (

  Content-Type "application/json"

) BODY (

  grant_type "password",

  client_id "sugar",

  client_secret "{client_secret}",

  username "{username}",

  password "{password}",

  platform "base"

);

Step 2: Call the table and its details, with the saved authorisation embedded in the body:

CREATE LIGHTNING REST TABLE Contacts FROM sugar_crm REQUEST(

endpoint "https://{site_url}/rest/v10/Contacts?max_num=${rec_count}",

method "get",

response_type "json",

http_encoding "URLENCODED",

rec_count "4700"

) HEADER (

Cache-Control "no-cache",

OAuth-Token "${access_token}"

) BODY ()

AUTH BY sugar_crm;

Query SugarCRM

If you have followed the above steps, the table, Contacts, is now created.

The incoming structure of the connection might be in JSON form, in which case, you will need to flatten the structure to a compatible data frame, using either the flatten or explode commands. An example is shown below:

select a.* from (

select explode(records) a from SUGAR_CRM.CONTACTS)

Once the JSON has been expanded, it can be called like any other table through

• Query Builder
• Virtual Data Pipeline
• Virtual Data Mart