SQL Support for Cloud Apps

How It Works

banner

Simple queries are directly compiled to web API calls. These API calls are executed by the cloud data source, providing the best performance.

More complex queries are split in parts, and parts of the query are executed against the cloud data source. Our SQL engine processes the results and applies advanced SQL features, used in the query. Returned data are presented to the user.

Query Cloud Data

Immediate Data Access

With SyncMonster it’s very easy to select cloud data. SyncMonster allows you to quickly select a cloud object, specify filtering and sorting conditions in the query editor in order to get exactly the data you want, execute the query, and retrieve your data.

For example, you can use the SQL query on the screenshot to get all Salesforce customer accounts, ordered by their names.

SELECT Name FROM Account 
WHERE Type LIKE 'Customer%'
ORDER BY Name

SELECT Name FROM Account 
WHERE Type LIKE
'Customer%'
ORDER BY Name

Support for Advanced SQL Features

SyncMonster SQL client is not limited to simple cases like the previous one. SyncMonster allows users to create complex SELECT queries with aggregations, SQL functions, etc. to get the data in the format you need — summarized, aggregated, or detailed. For example, look at the SQL query shown in the figure to the left — the query returns total sale amounts grouped in years.

Use the full power of SQL to retrieve your data from the cloud in exactly the form you need it. Make it more efficient to retrieve and analyze your data so your decision-making will be more effective in turn.

SELECT  
 strftime('%Y', CreatedDate) AS Year,  
 SUM(Amount) AS Total
FROM Opportunity
GROUP BY strftime('%Y', CreatedDate)

SELECT  
 strftime('%Y',
 CreatedDate) AS Year,  
 SUM(Amount) AS Total
FROM Opportunity
GROUP BY strftime('%Y',
CreatedDate)

Manage Cloud Data

SyncMonster fully supports the syntax of INSERT, UPDATE, and DELETE SQL statements for cloud sources. This makes SyncMonster a perfect tool to perform mass data update operations on cloud data sources, since you can easily update or delete multiple records with a single update or delete statement.

INSERT

You can use an INSERT statement to quickly create a new record with only the necessary fields filled. The RETURNING clause is supported in INSERT statements. The sample INSERT statement in the figure will create a new Salesforce contact.

 INSERT INTO Contact 
	(FirstName, LastName, Phone, Email) 
VALUES (
	 'John',
	 'Smith',
	 '(650) 450-8820',
	 'johnsmith@devart.com'
	)

 INSERT INTO Contact 
	(FirstName, LastName,
   Phone, Email) 
VALUES (
	 'John',
	 'Smith',
	 '(650) 450-8820',
	 'johnsmith@devart.com'
	)

UPDATE

With UPDATE statements you can perform mass cloud data updates. SyncMonster allows you to preview changes before applying, so the operation is safe. The UPDATE statement in the figure increases all prices in a specific Salesforce Pricebook2 by 5%

 UPDATE PricebookEntry
SET UnitPrice = UnitPrice*1.05
WHERE Pricebook2ID = '01sA0000000Tq1aIAC'

 UPDATE PricebookEntry
SET UnitPrice = 
UnitPrice*1.05
WHERE Pricebook2ID =
'01sA0000000Tq1aIAC'

DELETE

With a single DELETE statement you can easily delete all the records that satisfy the specified WHERE condition. ID value is not required. The DELETE statement in the figure deletes all Salesforce contacts not assigned to accounts.

DELETE FROM Contact
WHERE AccountID IS NULL

Cloud SQL Support in SyncMonster Products

Currently SyncMonster supports SQL to cloud applications in the Query product. SyncMonster Query supports querying cloud data with SELECT statements and managing data via DML statements with changes preview. It allows viewing the returned data in the browser in a grid or as charts and exporting data to various file formats.

Work with Cloud Data Just Like with Databases